Dict issue with PostgreSQL for last_login plugin (duplicate key)

[John Fawcett]

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

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