1 Topic by milprog

  • milprog
  • Member
  • Offline
  • Registered: 2016-09-08
  • Posts: 1

Topic: duplicate key errors in postgresql log

==== Required information ====
- iRedMail version (check /etc/iredmail-release): 0.9.5-1
- Linux/BSD distribution name and version: CentOS 6.8
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): PGSQL
- Web server (Apache or Nginx): Nginx
- Manage mail accounts with iRedAdmin-Pro? no
- Related log if you're reporting an issue: $PGDATA/pg_log/postgresql-xxx.log
====

Hi all

I get lots of messages

2016-09-08 23:30:07.426 CEST >ERROR:  duplicate key value violates unique constraint "idx_greylisting_whitelists_account_sender"
< 2016-09-08 23:30:07.426 CEST >DETAIL:  Key (account, sender)=(@., 182.50.78.64/28) already exists.
< 2016-09-08 23:30:07.426 CEST >STATEMENT:  INSERT INTO greylisting_whitelists (comment, account, sender) VALUES ('AUTO-UPDATE: constantcontact.com', '@.', '182.50.78.64/28'); SELECT currval('greylisting_whitelists_id_seq')

...I suspect that the "UPSERT" logic may be somewhat inappropriate for postgreSQL?

Can I safely ignore these messages for now, or is there a fix?

Thanks and best regards
Marcel

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,241

Re: duplicate key errors in postgresql log

*) It's safe to ignore.
*) Which iRedAPD release are you running? Please show us output of command "ls -l /opt"

3 Reply by hulth

  • hulth
  • Member
  • Offline
  • Registered: 2016-12-30
  • Posts: 2

Re: duplicate key errors in postgresql log

Picking up this thread since I've been seeing the same errors ever since i first installed and it's kinda annoying tongue

==== Required information ====
- iRedMail version (check /etc/iredmail-release): 0.9.5-1
- Linux/BSD distribution name and version: Debian Jessie
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): PGSQL
- Web server (Apache or Nginx): Apache
- Manage mail accounts with iRedAdmin-Pro? no
- Related log if you're reporting an issue: $PGDATA/pg_log/postgresql-xxx.log
====

Running iRedAPD-1.9.1

snippet from postgresql-9.4-main.log

2016-12-30 12:30:08 CET [8117-328] iredapd@iredapd ERROR:  duplicate key value violates unique constraint "idx_greylisting_whitelists_account_sender"
2016-12-30 12:30:08 CET [8117-329] iredapd@iredapd DETAIL:  Key (account, sender)=(@., 182.50.78.64/28) already exists.
2016-12-30 12:30:08 CET [8117-330] iredapd@iredapd STATEMENT:  INSERT INTO greylisting_whitelists (comment, account, sender) VALUES ('AUTO-UPDATE: constantcontact.com', '@.', '182.50.78.64/28'); SELECT currval('greylisting_whitelists_id_seq')

Theese 3 lines repeat for every greylisting_whitelists INSERT-statement, with different IPs. I get about 5k errors a day in the log related to "duplicate key value"

4 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,241

Re: duplicate key errors in postgresql log

hulth wrote:

Theese 3 lines repeat for every greylisting_whitelists INSERT-statement, with different IPs. I get about 5k errors a day in the log related to "duplicate key value"

It must be caused by cron job with task runs '/opt/iredapd/tools/spf_to_greylist_whitelists.py'.

It's safe to ignore, but if you feel it's annoying, try to update the cron task to run each 30 minutes, or even longer like 1 hour.

5 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,241

Re: duplicate key errors in postgresql log

== UPDATE ==

This annoying log has been fixed moment ago:
https://bitbucket.org/zhb/iredapd/commi … telists.py

6 Reply by hulth

  • hulth
  • Member
  • Offline
  • Registered: 2016-12-30
  • Posts: 2

Re: duplicate key errors in postgresql log

ZhangHuangbin wrote:

== UPDATE ==

This annoying log has been fixed moment ago:
https://bitbucket.org/zhb/iredapd/commi … telists.py

Thank you so much big_smile Enjoy the five cups of coffee heading your way smile

7 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,241

Re: duplicate key errors in postgresql log

hulth wrote:

Enjoy the five cups of coffee heading your way smile

Thank you so much. You're the first one bought me so much coffee. big_smile