Come together with the global Drupal community in Rotterdam, 28 Sept – 1 Oct 2026. Sessions, contribution, connection, and Early Bird savings until 8 June.Ok,
I have tried a few things with the 4.6 release on a test database, I'm currently running it on PHP 4.3.11, but I'll switch to PHP 5 once I'm confident the other stuff besides drupal runs as well.
Some of the PostgreSQL problems I noticed:
block.module:
The function block_list produces the atrocious select:
function block_list($region, $regions = array('left' => 0, 'right' => 1, 'all' => '0, 1'))
where the main problem is that the string '0, 1' doesn't quite work. Replace that by '0', '1' and the problem is gone.
Anoter nice one in triggered by viewing the front page where the expaned SQL statement can produce something like:
"SELECT DISTINCT(n.nid) FROM node n INNER JOIN node_access na ON na.nid= n.nid WHERE (na.grant_view = 1 AND CONCAT(na.realm, na.gid) IN ('all0','node_privacy_byrole_role1','node_privacy_byrole_user0')) AND n.promote = 1 AND n.status = 1 ORDER BY n.created DESC LIMIT 15 OFFSET 0"
doesn't work in postgres. Since the DISTINCT is not really needed (can we have two identical node ids in the node table? no, since it's a serial in postgres), I suggest dumping it.
Basically, as proven before, I can and do debug postgres problems, but due to time constraints I can't do it all the time.
| Comment | File | Size | Author |
|---|---|---|---|
| #9 | node.module.4.7.2.postgresql_0.patch | 663 bytes | PMunn |
| #6 | node.module.4.7.2.postgresql.patch | 441 bytes | PMunn |
| #2 | node.module_6.patch | 526 bytes | mousse-man |
Comments
Comment #1
mousse-man commentedI have found when the problem occurs, and it's only when somebody tries to subscribe to my site via RSS, like when I use Firefox and I subscribe to the RSS feed of my site, the error occurs.
But from which components the select statement is constructed is (at least for the moment) beyond me.
Comment #2
mousse-man commentedI have found the culprit for the problem, see included patch.
The select distinct on the node is not needed at all for the simple reason that the node id (field nid) is unique in both Postgres and MySQL due to the fact that it's a serial.
Comment #3
mousse-man commentedComment #4
mousse-man commentedComment #5
Cvbge commentedThe first bug ('all' => '0, 1') is fixed in 4-6.
I can't reproduce the second bug with DRUPAL-4-6. I can subscribe to rss feeds with opera without problems (i.e. I click on RSS link in the address bar and I get list of items without errors)...
Is this bug still present, and if yest how to reproduce it?
OTOH I agree that there's no need to DISTINCT(n.nid) as it's primary key.
Comment #6
PMunn commentedI can reproduce the second bug readily, and have produced an updated patch based on the above patch for node.module for 4.7.2. All it really did was change the line number it gets applied to from then to now.
How to reproduce it on a postgresql system:
-Log out of drupal.
-Open another tab to the url to your drupal site plus an ?q=rss.xml parameter on the end. I see xml in the browser.
-Click on groups on the home page. Bang, error occurs.
I'd initially altered the sql to work with pgsql by including the order by term in the select, but if the DISTINCT is prevented, modifying the SQL is not required.
Comment #7
drummWhat error exactly occurs?
There can be multiple of the same nid with the revision system.
Does this need the organic groups module to reporduce.
Comment #8
PMunn commentedYes it occurs when the og module is installed and when it is used with postgresql support (via my other patch for that). It's a PostgreSQL-generated error.
From my drupal logs:
pg_query(): Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list in /www/paulmunn.com/html/drupal-4.7.2/includes/database.pgsql.inc on line 84.
The query is very close or identical to the one above. It's basically complaining that the n.created isn't in the select list when the DISTINCT is used.
Comment #9
PMunn commentedThis patch modifies the node.module to put n.created in the select list, leaving the DISTINCT in the query. This fixes the problem for postgresql.
Comment #10
drummLooks like this was committed to HEAD.
Comment #11
(not verified) commented