Dict issue with PostgreSQL for last_login plugin (duplicate key)

[John Fawcett]

On 19/05/2019 12:52, mabi via dovecot wrote:
> Hello,
>
> I am trying to setup the last_login plugin on Dovecot 2.3 in order to log a user's last IMAP login into a separate PostgreSQL table called last_login. I am actually following that guide: https://docs.iredmail.org/track.user.last.login.html with the exception that I use PostgreSQL instead of MySQL.
>
> So far it works at least for the very first login of a user but it looks like Dict always want to add a new record into my last_login table instead of updating the existing record for a specific user. Here is the error message from Dovecot:
>
> May 19 12:45:14-mbox1 dovecot: imap(user at domain.tld)<51915><if6KTzuJMZ25D+Vc>: Error: last_login_dict: Failed to write value: dict-server returned failure: sql dict: commit failed: ERROR:  duplicate key value violates unique constraint "last_logins_pkey"
>
> It looks like Dict with PostgreSQL does not support UPDATEing an already existing record in the table. Or am I missing something? or it does not work with PostgreSQL? Which does not make much sense to me...
>
> Regards,
> Mabi
>
I guess it is not supported for PostgreSQL or Sqlite, since they don't
have "ON DUPLICATE KEY UPDATE" statement which is what is being used in
MySQL.

You could verify the query being used by turning on query logging in
PostgresSQL.

John