Issue status update for http://drupal.org/node/17747 Project: Drupal Version: cvs Component: postgresql database Category: tasks Priority: critical Assigned to: adrian Reported by: adrian Updated by: Dries Status: patch Wouldn't $user->uid always be NULL or zero? The $user object has not been initialized at that point. Dries Previous comments: ------------------------------------------------------------------------ February 22, 2005 - 17:12 : adrian Attachment: http://drupal.org/files/issues/postgres_updates_inc.diff (8.71 KB) Here are the updates for updates.inc for the postgres port of drupal 4.6 I am still in the process of working through all the new functionality and making sure it all works, and I also need to finish testing the new installs (database schema) although I am 99% sure they are correct. More patches will probably be forthcoming, but I need to run to catch my plane to belgium. See you guys soon. ------------------------------------------------------------------------ February 22, 2005 - 17:36 : Steven By the way, here's a note to keep tabs on a search issue: Search stores scores and totals for words. These are all integers. But in the search results query, I use SUM(score/total) and it is intended that the division is done without rounding, i.e. with floating point numbers. MySQL automatically does this, but I believe PGSQL will truncate the result back to an integer. This is bad as every value of score/total is 0 < x < 1 and would thus be truncated to zero. Of course we could change the column types to floats instead, but I'm not sure this is a good idea. ------------------------------------------------------------------------ February 23, 2005 - 08:05 : Dries I committed this patch. No marking this 'fixed' yet. ------------------------------------------------------------------------ February 24, 2005 - 19:38 : adrian Attachment: http://drupal.org/files/issues/postgres_patch.diff (20.22 KB) Here is an updated patch that has been adequately tested (imo at least). I haven't managed to get any more errors from search module, and everything seems to be working fine. =) ------------------------------------------------------------------------ February 27, 2005 - 05:56 : Steven The following change seems wrong to me: - $query = "SELECT i.type, i.sid, i.word, SUM(i.score/t.count) AS score FROM {search_index} i $join INNER JOIN {search_total} t ON i.word = t.word WHERE $where GROUP BY i.type, i.sid ORDER BY score DESC"; + $query = "SELECT i.type, i.sid, i.word, SUM(i.score/t.count) AS score FROM {search_index} i $join INNER JOIN {search_total} t ON i.word = t.word WHERE $where GROUP BY i.type, i.sid, i.word ORDER BY score DESC"; This will cause the search scores to be calculated and returned per word rather than as a sum of all matching words. The i.word field that is being selected is actually unnecessary. It is cruft from during development. I think the following will work: $query = "SELECT i.type, i.sid, SUM(i.score/t.count) AS score FROM {search_index} i $join INNER JOIN {search_total} t ON i.word = t.word WHERE $where GROUP BY i.type, i.sid ORDER BY score DESC"; ------------------------------------------------------------------------ February 27, 2005 - 17:26 : Dries I committed part of this patch: Removed a duplicate line from the changes to update.inc. Excluded the session.inc changes: they did not make sense to me. Excluded the search related changes in the node and search module. According to Steven these are not correct. ------------------------------------------------------------------------ February 27, 2005 - 23:38 : Anonymous Warning: pg_query(): Query failed: ERROR: null value in column "uid" violates not-null constraint in /Users/adrian/dev/cleanroom/includes/database.pgsql.inc on line 45 Fatal error: ERROR: null value in column "uid" violates not-null constraint query: INSERT INTO sessions (sid, hostname, timestamp) values('5b1ab8b0fad9d3811c0fdb818424dd44', '127.0.0.1', 1109538099) in /Users/adrian/dev/cleanroom/includes/database.pgsql.inc on line 62 --- The uid column is required, and one can't even go to the update page without it being part of the sql query. Hence there's no way to even set a default on the column, or specify in a message that the user needs to do so herself. Will we ever have a session where there is no UID? This was removed in a patch, but was never tested in postgres. I'll roll another patch in a bit. It seems there was a typo in my updates.inc patch too.