[drupal-devel] [task] Postgres updates for 4.6

[Anonymous]

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:   Anonymous
 Status:       patch

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.


Anonymous



Previous comments:
------------------------------------------------------------------------

February 22, 2005 - 15: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 - 15: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 - 06:05 : Dries

I committed this patch.  No marking this 'fixed' yet.


------------------------------------------------------------------------

February 24, 2005 - 17: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 - 03: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 - 15: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.