Dict issue with PostgreSQL for last_login plugin (duplicate key)

mabi mabi at protonmail.ch
Sun May 19 16:42:02 EEST 2019


‐‐‐‐‐‐‐ 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


More information about the dovecot mailing list