Issue status update for http://drupal.org/node/20255 Post a follow up: http://drupal.org/project/comments/add/20255 Project: Drupal Version: cvs Component: throttle.module Category: bug reports Priority: normal Assigned to: Anonymous Reported by: Anonymous Updated by: Jeremy@kerneltrap.org Status: patch (ready to be committed) Agreed, the GROUP BY and ORDER BY are both unnecessary. Please commit. Jeremy@kerneltrap.org Previous comments: ------------------------------------------------------------------------ Sun, 10 Apr 2005 09:05:22 +0000 : Anonymous I set up Drupal from CVS yesterday, using PostgreSQL 8.0 as database. viewing pages as anonymous user often generates the following warning: warning: pg_query(): Query failed: ERROR: column "sessions.timestamp" must appear in the GROUP BY clause or be used in an aggregate function in /www/htdocs/includes/database.pgsql.inc on line 45.always followed by: user error: query: SELECT COUNT(DISTINCT(uid)) AS count FROM sessions WHERE timestamp >= 1113122734 AND uid != 0 GROUP BY uid ORDER BY timestamp DESC in /www/htdocs/includes/database.pgsql.inc on line 62. ------------------------------------------------------------------------ Sun, 10 Apr 2005 18:18:17 +0000 : Anonymous okay, after some time playing around in drupal, I could near the problem down a bit: the message appeared about every 10th time... and my auto-throttle-probability was set to 10%. Setting the probability to 50%, and the mentioned warnings came with that percentage. so the error seems to be in the throttle module. anyway, looking through the support issues, similar warnings ("xyz must appear in the GROUP BY") happen in several places when pgsql is used. Maybe the problem could be solved somewhere general instead of in each module, or there should be at least a note regarding this on the module developers handbook ( http://drupal.org/node/1395 ), as I assume this is some MySQL-specific notation. ------------------------------------------------------------------------ Thu, 14 Apr 2005 13:37:34 +0000 : r11r At my work, we ran into the same problem when doing a migration from Oracle to Postgres. The problem, I think, is that Postgres orders its results after the select statement not before so that it's impossible for it to order the results on a column you didn't select. I'm told we mostly ended up rewriting the queries to avoid this problem but given the information below, there might be a way around it without changing the queries too much. Here are a couple of links potentially relating to this same error: http://archives.postgresql.org/pgsql-general/2004-02/msg01199.php [1] http://www.postgresql.org/docs/7.4/static/queries-select-lists.html#QUERIES-... [2] [1] http://archives.postgresql.org/pgsql-general/2004-02/msg01199.php [2] http://www.postgresql.org/docs/7.4/static/queries-select-lists.html#QUERIES-... ------------------------------------------------------------------------ Fri, 15 Apr 2005 13:16:14 +0000 : adrian We always change the queries. From the link you posted : The DISTINCT ON clause is not part of the SQL standard and is sometimes considered bad style because of the potentially indeterminate nature of its results. With judicious use of GROUP BY and subqueries in FROM the construct can be avoided, but it is often the most convenient alternative. Adding the fields in the GROUP BY is the only way to have the sql work for both mysql and postgres. I'm gonna make a patch for this now. ------------------------------------------------------------------------ Mon, 22 Aug 2005 14:13:12 +0000 : Uwe Hermann Adrian, any updates on this? ------------------------------------------------------------------------ Mon, 29 Aug 2005 14:56:29 +0000 : Cvbge Attachment: http://drupal.org/files/issues/drupal-head-throttle-20255.diff (856 bytes) Attached patch should fix the error. I've removed the ORDER BY (sorting does not have any impact on COUNT) and GROUP BY (we already have DISTINCT). Please test. ------------------------------------------------------------------------ Mon, 29 Aug 2005 21:08:08 +0000 : Cvbge I believe there's a problem with throttling by registered users. The code from throttle.module: ------------------------------------------------------------------------ Mon, 29 Aug 2005 21:12:49 +0000 : Cvbge Currently throttling by registered users does not work. I've explained it nicely and project.module or drupal have eaten my explanation without any warning. The attached post fixes postgres' GROUP BY problem and also fixes throttling problem. ------------------------------------------------------------------------ Mon, 29 Aug 2005 21:22:06 +0000 : Cvbge There. I've even tested the patch and it works. +1 ;)