On 19/05/2019 15:42, mabi via dovecot 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
You may be able to find a workaround, by redefining the table without a primary key and then select the max(last_login) from the table, with some periodic job that clears out the old entries.
Or you could make a workaround with the rules syntax from PostgresSql.
https://www.postgresql.org/docs/9.2/sql-createrule.html
Though probably the best thing is a patch to dovecot in order to support last_login for PostgresSql.
John