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,'u...@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
