PostgreSQL install, Drupal 6.22 (and previous versions).

This is apparently quite similar on 7.x (https://drupal.org/node/1348764) But I'm seeing it on 6x.-1.4.

Error:
query: INSERT INTO subscriptions_queue (uid, name, language, module, field, value, author_uid, send_interval, digest, last_sent, load_function, load_args, is_new, suspended) SELECT u.uid, u.name, u.language, s.module, s.field, s.value, s.author_uid, s.send_interval, su.digest, COALESCE(sls.last_sent, 0), 'subscriptions_content_node_load', '4699', '0', suspended FROM subscriptions s INNER JOIN subscriptions_user su ON s.recipient_uid = su.uid INNER JOIN users u ON su.uid = u.uid INNER JOIN term_node tn ON s.value = CAST(tn.tid AS VARCHAR) LEFT JOIN subscriptions_last_sent sls ON su.uid = sls.uid AND s.send_interval = sls.send_interval WHERE s.module = 'node' AND s.field = 'tid' AND s.author_uid IN (2, -1) AND tn.nid = 4699 AND s.send_updates = 1 GROUP BY u.uid, tn.nid in /(path)/sites/all/modules/subscriptions/subscriptions.module on line 332.

with followup error message:

pg_query(): Query failed: ERROR: column "s.module" must appear in the GROUP BY clause or be used in an aggregate function LINE 3: SELECT u.uid, u.name, u.language, s.module, s.fiel... ^ in /(path)/includes/database.pgsql.inc on line 138.

So I dug around for the Group By and found the subscriptions_taxonomy.module line 36 has this:

'groupby' => 'GROUP BY u.uid, tn.nid',

Changing it to:
'groupby' => 'GROUP BY u.uid, tn.nid, s.module, s.field, s.value, s.author_id, s.send_interval',

Eliminates the error.

Anything short of those fields generates an error asking for the missing fields to be in the Group By.

I can't tell if this breaks the SQL or anything based on the fact that I don't have a user subscribed to that taxonomy right now, I don't think, but it has stopped the error and is not generating new ones. It was just an error I wanted to get rid of.

Comments

salvis’s picture

Thank you for your report and analysis!

Pretty amazing that we haven't hit this before...

Is this a reasonable construct for PostgreSQL? or does it kill performance? What about MySQL?

PMunn’s picture

Not really sure if this is a good sign or not, but after rebuilding my site from a backup I noticed new errors when updating certain posts. First, the s.author_id should have been s.author_uid. And then additional columns needed to be added. I've updated the same line mentioned previously with the following:

'groupby' => 'GROUP BY u.uid, tn.nid, s.module, s.field, s.value, s.author_uid, s.send_interval, su.digest, sls.last_sent, su.suspended',

salvis’s picture

We're discussing this same thing in #1348764: PostgreSQL: PDOexception error when creating or saving nodes for D7 right now, but let's keep this open for D6.

salvis’s picture

Status: Active » Needs review
StatusFileSize
new1.74 KB

Please try this patch.

salvis’s picture

Version: 6.x-1.4 » 6.x-1.x-dev

Patch is against the current -dev version.

We need confirmation that taxonomy subscriptions and some other type of subscriptions (content type, blog) still work, i.e. that creating a node will trigger the notification.

We need this for both PostgreSQL and and non-PostgreSQL.

salvis’s picture

@PMunn and all other PostgreSQL users:

I'm getting ready to make a new release. This will NOT go in if you don't review it.

salvis’s picture

Status: Needs review » Fixed

Well, I've committed this anyway, because the worst that can happen is that it breaks pgsql, which seems to be broken anyway.

Status: Fixed » Closed (fixed)

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

liam morland’s picture

Issue tags: +PostgreSQL

Tagging