Possible bug with last_login plugin and PostgreSQL

[Zhang Huangbin]

> On Apr 1, 2019, at 6:39 PM, Zhang Huangbin <zhb at iredmail.org> wrote:
> 
> I'm trying to get last_login plugin working with PostgreSQL, but seems Dovecot
> doesn't handle duplicate key while updating last login time (note: same setting
> works fine with MariaDB). I wonder whether it's a bug of Dovecot or i should handle
> this with PostgreSQL trigger.

According to Dovecot source code[1], only MySQL has the "ON DUPLICATE KEY"
support, this is out of date because PostgreSQL has similar feature ("ON CONFLICT")[2]
since version 9.5. Also:

- Debian 9 ships PostgreSQL-9.6.
- Ubuntu 18.04 ships PostgreSQL-10.6.
- openSUSE tumbleweed ships PostgreSQL 10 and 11.
- Although CentOS 7 ships PostgreSQL 9.2 (NOT CAPABLE), but RHEL 8 public beta
  already ships PostgreSQL 9.6 and 10.
- OpenBSD 6.4 ships PostgreSQL 10.5, upcoming OpenBSD 6.5 ships PGSQL 11.2.
- FreeBSD ports tree offers PGSQL 9.5, 9.6.

Seems latest mainstream Linux/BSD distribution releases are all ok to use this
"ON CONFLICT" support, the question is, can we have this feature in Dovecot for PostgreSQL?

Thank you very much for the help. :)
----
BTW, SQLite has similar feature since version 3.24.0. https://www.sqlite.org/lang_UPSERT.html

[1] Dovecot source code: https://github.com/dovecot/core/blob/master/src/lib-dict-backend/dict-sql.c#L108
[2] PostgreSQL: "ON CONFLICT" Clause: https://www.postgresql.org/docs/9.5/sql-insert.html#SQL-ON-CONFLICT