1

Topic: postgres ERROR: column "msgs.policy" must appear in the GROUP

==== REQUIRED BASIC INFO OF YOUR IREDMAIL SERVER ====
- iRedMail version (check /etc/iredmail-release): 1.3.1 PGSQL edition
- downloadable installer
- Linux/BSD distribution name and version: BSD 11.3 U4.1
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): PGSQL
- Web server (Apache or Nginx): Nginx
- Manage mail accounts with iRedAdmin-Pro? iRedAdmin-Pro
- [IMPORTANT] Related original log or error message is required if you're experiencing an issue.
====
I got this error in /var/log/messages

Aug 22 19:35:57 mail postgres[53690]: [6-1] 2020-08-22 19:35:57.145 CEST [53690] ERROR:  column "msgs.policy" must appear in the GROUP BY clause or be used in an aggregate function at character
Aug 22 19:35:57 mail postgres[53690]: [6-2] 2020-08-22 19:35:57.145 CEST [53690] STATEMENT:
Aug 22 19:35:57 mail postgres[53690]: [6-3]                                     -- Get top 10 senders.
Aug 22 19:35:57 mail postgres[53690]: [6-4]                                     SELECT COUNT(msgs.mail_id) AS total,
Aug 22 19:35:57 mail postgres[53690]: [6-5]                                            sender.email_raw AS mail,
Aug 22 19:35:57 mail postgres[53690]: [6-6]                                            msgs.policy AS policy
Aug 22 19:35:57 mail postgres[53690]: [6-7]                                       FROM msgs
Aug 22 19:35:57 mail postgres[53690]: [6-8]                                      RIGHT JOIN maddr AS sender ON (msgs.sid = sender.id)
Aug 22 19:35:57 mail postgres[53690]: [6-9]                                      WHERE 1=1  AND sender.domain IN ('xxx.xx') AND msgs.time_iso > CURRENT_TIMESTAMP - INTERVAL '86400 seconds'
Aug 22 19:35:57 mail postgres[53690]: [6-10]                                     GROUP BY mail
Aug 22 19:35:57 mail postgres[53690]: [6-11]                                     ORDER BY total DESC
Aug 22 19:35:57 mail postgres[53690]: [6-12]                                     LIMIT 10
Aug 22 19:35:57 mail postgres[53690]: [6-13]
Aug 22 19:35:57 mail iredadmin ['Traceback (most recent call last):\n', '  File "/usr/local/www/iRedAdmin-Pro-SQL-4.4/libs/amavisd/log.py", line 622, in get_top_users\n    """ % (sql_append_wheber))\n', '  File "/usr/local/lib/python2.7/site-packages/web/db.py", line 831, in query\n    self._db_execute(db_cursor, sql_query)\n', '  File "/usr/local/lib/python2.7/site-packages/web/db.pe 757, in _db_execute\n    out = cur.execute(query, params)\n', 'GroupingError: column "msgs.policy" must appear in the GROUP BY clause or be used in an aggregate function\nLINE 5:                                 msgs.policy AS policy\n                                               ^\n\n'] (/usr/local/www/iRedAdmin-Pro-SQL-4.4/libs/logger.py, line 38)

To fix it I removed "AS policy" in the file "/usr/local/www/iRedAdmin-Pro-SQL-4.4/libs/amavisd/log.py", line 615.

Does this cause any problem ?

Thanks