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.
| Comment | File | Size | Author |
|---|---|---|---|
| #4 | subscriptions_pgsql_groupby.1405990.4.patch | 1.74 KB | salvis |
Comments
Comment #1
salvisThank 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?
Comment #2
PMunn commentedNot 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',
Comment #3
salvisWe'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.
Comment #4
salvisPlease try this patch.
Comment #5
salvisPatch 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.
Comment #6
salvis@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.
Comment #7
salvisWell, I've committed this anyway, because the worst that can happen is that it breaks pgsql, which seems to be broken anyway.
Comment #9
liam morlandTagging