dovecot - Apr 2019 - Possible bug with last_login plugin and PostgreSQL

Dear all,

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.

OS: OpenBSD 6.4 (amd64)
Dovecot: 2.2.36
PostgreSQL: 10.5

Error log:
###################
Mar 31 11:15:21 ob dovecot: imap-login: Login: user=<postmaster at a.io>,
method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, mpid=5936, TLS,
session=<63/jV2CFhzt/AAAB>
Mar 31 11:15:21 ob dovecot: imap(postmaster at a.io): Logged out in=300 out=1604
Mar 31 11:15:21 ob dovecot: imap-login: Login: user=<postmaster at a.io>,
method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, mpid=29797, TLS,
session=<lLboV2CFuZ9/AAAB>
Mar 31 11:15:21 ob dovecot: dict(9584): Error: sql dict: commit failed: ERROR: 
duplicate key value violates unique constraint "last_login_pkey"
Mar 31 11:15:21 ob dovecot: dict: Error: DETAIL:  Key (username)=(postmaster at
a.io) already exists.
Mar 31 11:15:21 ob dovecot: imap(postmaster at a.io): Error: dict-client: server
returned failure: 1554023721 (reply took 0.012 secs)
Mar 31 11:15:21 ob dovecot: imap(postmaster at a.io): Error: last_login_dict:
Failed to write value for user postmaster at a.io
Mar 31 11:15:21 ob dovecot: imap(postmaster at a.io): Logged out in=70 out=732
##################

My Dovecot config:

###################
protocol imap {
    mail_plugins = ... last_login
    ...
}

protocol pop3 {
    mail_plugins = ... last_login
    ...
}

plugin {
    last_login_dict = proxy::lastlogin
    #last_login_key = last-login/%u
    ...
}

dict {
    lastlogin = pgsql:/etc/dovecot/dovecot-last-login.conf
    ...
}
###################

SQL commands used to create PostgreSQL table:
###########
CREATE TABLE last_login (
    username VARCHAR(255) NOT NULL,
    last_login INT DEFAULT NULL,
    PRIMARY KEY (username)
);
###########

File /etc/dovecot/dovecot-last-login.conf:
###########
connect = host=127.0.0.1 port=5432 dbname=vmail user=vmailadmin
password=<password>

map {
    pattern = shared/last-login/$user
    table = last_login
    value_field = last_login
    value_type = uint

    fields {
        username = $user
    }
}
##############

> 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