On 19 May 2019 16:42 mabi via dovecot <dovecot@dovecot.org> wrote:
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Sunday, May 19, 2019 2:58 PM, John Fawcett via dovecot <dovecot@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@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
It would be a feature request, also note that it's only available since version 9.5, so it really won't help anyone before that.
It seems last_login is missing unset, unfortunately.
Aki