dovecot - May 2019 - Dict issue with PostgreSQL for last_login plugin (duplicate key)

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

> On 19 May 2019 16:42 mabi via dovecot <dovecot at dovecot.org> 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
It would be a feature request, also note that it's only available since
version 9.5, so it really won't help anyone before that.

It seems last_login is missing unset, unfortunately.

Aki

??????? Original Message ???????
On Sunday, May 19, 2019 3:57 PM, Aki Tuomi <aki.tuomi at open-xchange.com>
wrote:
> It would be a feature request, also note that it's only available since
version 9.5, so it really won't help anyone before that.
Would opening an issue as feature request on GitHub on the dovecot/core project
be appropriate place for that?

I was thinking in the mean time as workaround that maybe with a trigger in
PostgreSQL it should be possible to update the last_login column on the failing
INSERT. Just need to find out how exactly to do that and I am not sure if it
possible to execute a trigger when failing because of a duplicate key error.

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

On 19/05/2019 16:25, John Fawcett via dovecot wrote:
> INSERT INTO last_logins (last_login,username,domain) VALUES
(1558273000,'user at domain.tld','domain.tld')
I don't have PostgresSql, would you be able to verify if this syntax would
work:

INSERT INTO last_logins (last_login,username,domain) VALUES
(1558273000,'user at domain.tld','domain.tld')
ONCONFLICT(username) UPDATE SET last_login=1558273000,domain='user at
domain.tld'

It's important to check that this updates only the single row for that user
and it puts the right data in that row. If it doesn't work can you give the
correct syntax?

John 

-------------- next part --------------
An HTML attachment was scrubbed...
URL:
<https://dovecot.org/pipermail/dovecot/attachments/20190519/9f6aa34b/attachment.html>