There are several SQL statements in subscriptions module are not compatible with PostgreSQL. This is a very common mistake often been seen on codes developped on MySQL.
In postgres, while using "GROUP BY" clauses, you must either use an aggregate function or include them in "GROUP BY" list on all the fields been selected. Otherwise postgres does not know which value to display on those not-specified fields. MySQL allows this non-standard query and returns values by its own decision (which I don't know the rule).
Here is a patch against 4.6.0 version of the subscription module which includes all not-aggregated fields in "GROUP BY" clauses.

CommentFileSizeAuthor
subscriptions.module.patch1.16 KBpatrickslee

Comments

dziemecki’s picture

dziemecki commented

I can patch, but I don't have a psg environments, so I need a volunteer to test it before I release it. In fact, I need a volunteer for a whole slew of psg stuff. Anyone care to step forward?

hystrix’s picture

hystrix commented

I can test PostgreSQL stuff.

Grabbed the latest CVS
subscriptions.module,v 1.37 2006/03/28

On importing of subscriptions.pgsq

Received the following output:

DROP TABLE
CREATE TABLE
DROP TABLE
NOTICE: CREATE TABLE will create implicit sequence "subscriptions_holding_rid_seq" for serial column "subscriptions_holding.rid"
CREATE TABLE
COMMENT
COMMENT
ERROR: column "type" of relation "subscriptions_holding" does not exist
COMMENT
ERROR: column "uid" of relation "subscriptions_holding" does not exist

And then enabling the module (with the above GROUP BY patch in place) produces the following error:

* warning: pg_query(): Query failed: ERROR: duplicate key violates unique constraint "system_pkey" in /home/hystrix/awg/public_html/includes/database.pgsql.inc on line 84.
* user warning: query: UPDATE system SET description = 'Allows users to subscribe to nodes and taxonomy terms.', name = 'subscriptions', bootstrap = 0, filename = 'modules/subscriptions/subscriptions.module' WHERE filename = 'modules/subscriptions.module' in /home/hystrix/awg/public_html/includes/database.pgsql.inc on line 103.
* warning: pg_query(): Query failed: ERROR: duplicate key violates unique constraint "system_pkey" in /home/hystrix/awg/public_html/includes/database.pgsql.inc on line 84.
* user warning: query: UPDATE system SET description = 'Allows users to subscribe to nodes and taxonomy terms.', name = 'subscriptions', bootstrap = 0, filename = 'modules/subscriptions/subscriptions.module' WHERE filename = 'modules/subscriptions.module' in /home/hystrix/awg/public_html/includes/database.pgsql.inc on line 103.

hystrix’s picture

hystrix commented

Forgot to mention this was on: Drupal 4.7-beta6 against PostgreSQL 8.0.4

dziemecki’s picture

dziemecki commented

Good catch. Unfortunately, I have not way to fix that, without a psg db. Any ideas?

dziemecki’s picture

dziemecki commented
Assigned: patrickslee » dziemecki

ERROR: column "type" of relation "subscriptions_holding" does not exist

There were some errors in the comments section. This was corrected in 4.6-head versions. The group by error will be addressed shortly.
dziemecki’s picture

dziemecki commented
Status: Needs review » Fixed

Integrated and forward-ported patrickslee's patch. Works for me.

dziemecki’s picture

dziemecki commented
Status: Fixed » Closed (fixed)

No complaints for a week. Closing.