mabi
2019-May-19 13:42 UTC
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
Aki Tuomi
2019-May-19 13:57 UTC
Dict issue with PostgreSQL for last_login plugin (duplicate key)
> 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, > MabiIt 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
mabi
2019-May-19 14:24 UTC
Dict issue with PostgreSQL for last_login plugin (duplicate key)
??????? 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.
John Fawcett
2019-May-19 14:25 UTC
Dict issue with PostgreSQL for last_login plugin (duplicate key)
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, > MabiYou 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
John Fawcett
2019-May-19 14:44 UTC
Dict issue with PostgreSQL for last_login plugin (duplicate key)
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>
Seemingly Similar Threads
- Dict issue with PostgreSQL for last_login plugin (duplicate key)
- Dict issue with PostgreSQL for last_login plugin (duplicate key)
- Dict issue with PostgreSQL for last_login plugin (duplicate key)
- Dict issue with PostgreSQL for last_login plugin (duplicate key)
- Dict issue with PostgreSQL for last_login plugin (duplicate key)