Hi,

I have set up two lokal testpages to see whether I can configure the core forum to my needs. The one is running on PostgresQL, the other on MySQL.

When I install the Forum Access Module on the PostgresQL-page I get error messages when viewing the forum (see below). On the MySQL - page everything is working fine. Unfortunately, the live page will be running on PostgresQL...
Does Forum Access does not support PostgresQL?

I would be very grateful for any answers!!!

Thanks, Hannes

ERROR:

# warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "ON" LINE 1: SELECT r.tid, COUNT( DISTINCT ON (n.nid) n.nid) AS topic_cou... ^ in C:\xampp\htdocs\drupal57\includes\database.pgsql.inc on line 125.
# user warning: query: SELECT r.tid, COUNT( DISTINCT ON (n.nid) n.nid) AS topic_count, SUM(l.comment_count) AS comment_count FROM node n INNER JOIN node_comment_statistics l ON n.nid = l.nid INNER JOIN term_node r ON n.nid = r.nid WHERE n.status = 1 AND n.type = 'forum' GROUP BY r.tid in C:\xampp\htdocs\drupal57\includes\database.pgsql.inc on line 144.

a.s.o.

CommentFileSizeAuthor
#4 database.pgsql_.zip4.79 KBbfo

Comments

bfo’s picture

bfo commented

Have a look at this thread

http://drupal.org/node/128846

I had the same issue, I got round it by installing the patch mentioned in the thread (i comment in the thread which one i used) and then used the Taxonomy access control module to control access to my forums, however this module may also work fine after the patch.

slooodge’s picture

slooodge commented

Hi bfo,

thanks for your quick response!
Unfortunately, I am an absolute beginner with drupal and what I have understood from the thread did not work for me...

I have replaced the following lines:

function db_distinct_field($table, $field, $query) {
$field_to_select = 'DISTINCT ON ('. $table .'.'. $field .") $table.$field";
// (? $query = preg_replace('/(SELECT.*)(?:'. $table .'\.|\s)(? $query = preg_replace('/(ORDER BY )(?!'.$table.'\.'.$field.')/', '\1'."$table.$field, ", $query);
return $query;
}

With theses:

function db_distinct_field($table, $field, $query) {
if (!preg_match('/FROM\s+\S+\s+AS/si', $query)
&& !preg_match('/DISTINCT\s+ON\s*\(\s*('. $table .'\s*\.\s*)?'. $field .'\s*\)/si', $query)
&& preg_match('/(.*FROM\s+)(.*?\s)(\s*(WHERE|GROUP|HAVING|ORDER|LIMIT|FOR).*)/Asi', $query, $m)) {
$query = $m[1];
$query .= preg_replace('/([\{\w+\}]+)\s+(' . $table . ')\s/Usi', '(SELECT DISTINCT ON (' . $field . ') * FROM \1) \2 ', $m[2]);
$query .= $m[3];
}

return $query;
}

But no effect can be seen. The posts still don't show and the error messages are still there.
I have tried your patch as well but no effect either...

Do I need to change anything else?
Or do you think that d6 works better eventhough the modules are not yet equipped for d6?
Is drupal otherwise working fine with postgresql?

Sorry, I feel a bit helpless because I am just starting with Drupal and was hoping that things work as they should...
I would be very grateful if you could help me if you can!

Best, Hannes

salvis’s picture

salvis
he/his
commented
Component: User interface » Code
Status: Active » Postponed (maintainer needs more info)

Just to be sure, you did modify includes/database.pgsql.inc, right?

And you replaced db_distinct_field() with the one from http://drupal.org/node/128846#comment-678398, and it hasn't helped?

@bfo: Nice to see you again :-) and thanks for the pointer!

The query that causes the error is in core, just like db_distinct_field(). If the patch makes TAC work, it should also make FA work, and vice versa.

@slooodge: There is a bit of a problem with PostgreSQL: many core and contrib developers as well as the large majority of users runs on MySQL and there is a chronic shortage of testing on PostgreSQL. Most of us try to fix PostgreSQL-specific issues as they are reported, but they rarely are reported and thus it takes much longer to get the same reliability under PostgreSQL as under MySQL.

If you want to help, then please do the following: locate the db_rewrite_sql() call inside the forum_get_forums() function in modules/forum/forum.module. Put

watchdog('debug', "BEFORE: $sql");

above it and 

watchdog('debug', "AFTER: $sql");

below it. Do whatever causes the trouble, and you should see the two debug messages show up in your log. Report the full text of them. Do this both with the original db_distinct_field() function and the replacement one. That should let us see what is happening...

bfo’s picture

bfo commented
StatusFileSize
new database.pgsql_.zip4.79 KB

slooodge

I don't know what you changed but whatever it was hopefully you took a copy before making the change if so put the site back to how it was and then replace your

includes/database.pgsql.inc file with the one I've attached. (NOTE I've had to zip the file for it to be accepted)

As for Drupal with postgres, I will admit it would be far easier to deal with if I was using mysql as the majority of modules are written by people who know mysql and do the best they can with postgres, most developers I've found will try and do there best if you report and issue.

It's definately worth letting a developer know if you find an issue with their module with postgres even if you manage to fix it yourself.

I have to use postgres as my other half objects to mysql and it's his server I host my websites off, it would probably be much easier to use mysql if you can but theres nothing major thats been a problem with using postgres, things just sometimes take a little longer to get working if a bug is found.

slooodge’s picture

slooodge commented

Hi bfo,

thanks for the file - it worked!!!
I just realised what the problem was - I have two installations, one running on postgreSQL one on MySQL and I was modifying the wrong includes/database.pgsql.inc... So, it was totally my fault and your patch works just fine!
Fortunately, I made a copy of the file.

And thanks for your opinion on running drupal on PostgreSQL. Unfortunately, I can only use PostreSQL for this site and I have to install a lot more modules - which worries me a bit...

I'll keep you informed ;-)

Thanks again for the quick and good support! Hannes

slooodge’s picture

slooodge commented

Salvis,

thanks for your support, I have used bfo's file and the forum/module is running now!

Best, Hannes

salvis’s picture

salvis
he/his
commented
Category: bug » support
Status: Postponed (maintainer needs more info) » Fixed

Great, thanks bfo and slooodge.

I've taken this back to the core issues queue at http://drupal.org/node/128846#comment-738418 — hopefully we'll get some action there...

salvis’s picture

salvis
he/his
commented
Title: Problems with running Forum Access on PostgresQL » PLEASE REVIEW!!! — Bug in Drupal Core 5&6 when running on PostgreSQL
Category: support » task
Status: Fixed » Needs review

No, this is not a Forum Access bug, and the patch is not a Forum Access patch (and it's not in this issue!).

It's a bug in the PostgreSQL-specific code of Drupal core; it bites you when you run Forum Access, but it may also bite you with any other modules or even without any contributed modules at all.

If you have any interest in running Drupal on PostgreSQL, then please do your fellow PostgreSQL users a favor, go to http://drupal.org/node/128846#comment-738418, and review that patch, so that it can get committed.

You can test it against Drupal 5 or 6.

salvis’s picture

salvis
he/his
commented

Updated patch in http://drupal.org/node/128846#comment-761250.

PostgreSQL users: please go there and review the patch, so that it can get committed!

salvis’s picture

salvis
he/his
commented
Title: PLEASE REVIEW!!! — Bug in Drupal Core 5&6 when running on PostgreSQL » PLEASE REVIEW!!! — PostgreSQL bug in Drupal Core 5&6

This is the twin of #249008: PostgreSQL bug in Drupal Core 5 not fixed until 5.9 in the ACL queue.

PostgreSQL users: please go to #128846: Rewritten PostgreSQL queries fail and review the patch in #70 (#69 for D6), so that it can get committed!

salvis’s picture

salvis
he/his
commented
Title: PLEASE REVIEW!!! — PostgreSQL bug in Drupal Core 5&6 » PLEASE REVIEW! — PostgreSQL bug in Drupal Core 5&6

(make title fit on issues list)

salvis’s picture

salvis
he/his
commented
Title: PostgreSQL bug in Drupal Core 5 not fixed until 5.9 » PLEASE REVIEW! — PostgreSQL bug in Drupal Core 5&6
Status: Patch (to be ported) » Fixed

This issue has been fixed in Drupal 5.8 and 6.3 core.

Please upgrade your Drupal installation.

salvis’s picture

salvis
he/his
commented
Title: PLEASE REVIEW! — PostgreSQL bug in Drupal Core 5&6 » PostgreSQL bug in Drupal Core 5 not fixed until 5.9
Status: Needs review » Patch (to be ported)

Oops, it turns out that the patch made it into 6.3, but it was too late for 5.8. This means for Drupal 5, it won't be fixed until the future 5.9.

If you need it fixed now, you should be able to apply the patch in http://drupal.org/node/128846#comment-805250 to 5.8. Let us know if it works for you.

I'll keep this open for reference.

salvis’s picture

salvis
he/his
commented
Title: PLEASE REVIEW! — PostgreSQL bug in Drupal Core 5&6 » PostgreSQL bug in Drupal Core 5 not fixed until 5.9

Ok, the patch is in 5.9. I can't test it myself because I don't have pgsql, but I think it's finally fixed now.

Anonymous’s picture

Anonymous (not verified) commented
Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for two weeks with no activity.