dovecot - May 2019 - Dict issue with PostgreSQL for last_login plugin (duplicate key)

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

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

??????? Original Message ???????
On Sunday, May 19, 2019 2:58 PM, John Fawcett via dovecot <dovecot at
dovecot.org> wrote:
> 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.
That's it, PostgreSQL does not have "ON DUPLICATE KEY" but instead
uses "ON CONFLICT" as documented here:

https://www.postgresql.org/docs/10/sql-insert.html#SQL-ON-CONFLICT
> You could verify the query being used by turning on query logging in
> PostgresSQL.
I enabled query logging as suggested and found out that Dovecot dict is not
using the "ON CONFLICT" feature of INSERT with PostgreSQL, as you can
see from the query below:

INSERT INTO last_logins (last_login,username,domain) VALUES
(1558273000,'user at domain.tld','domain.tld')

For me this makes Dovecot's dictionary feature useless with PostgreSQL.
Should I open a bug for that? or is it more of a "feature request"?

Regards,
Mabi