similar to: Dict issue with PostgreSQL for last_login plugin (duplicate key)

On 19/05/2019 12:52, mabi via dovecot wrote: > Hello, > > I am trying to setup the last_login plugin on Dovecot 2.3 in order to log a user's last IMAP login into a separate PostgreSQL table called last_login. I am actually following that guide: https://docs.iredmail.org/track.user.last.login.html with the exception that I use PostgreSQL instead of MySQL. > > So far it works at

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

??????? 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 19/05/2019 20:31, mabi via dovecot wrote: > > ??????? Original Message ??????? > On Sunday, May 19, 2019 7:36 PM, John Fawcett via dovecot > <dovecot at dovecot.org> wrote: >> >> Attached is a tentative patch. I've verified no regression for mysql. >> There should be no regression for sqlite as the code path is identical. >> >> Are you able to

On 19/05/2019 17:08, mabi via dovecot wrote: > ??????? Original Message ??????? > On Sunday, May 19, 2019 4:44 PM, John Fawcett via dovecot > <dovecot at dovecot.org> wrote: > >> 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

On 19/05/2019 22:45, John Fawcett via dovecot wrote: > On 19/05/2019 22:37, John Fawcett via dovecot wrote: >> On 19/05/2019 20:31, mabi via dovecot wrote: >>> >>> ??????? Original Message ??????? >>> On Sunday, May 19, 2019 7:36 PM, John Fawcett via dovecot >>> <dovecot at dovecot.org> wrote: >>>> >>>> Attached is a

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,

> 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

On 19/05/2019 22:37, John Fawcett via dovecot wrote: > On 19/05/2019 20:31, mabi via dovecot wrote: >> >> ??????? Original Message ??????? >> On Sunday, May 19, 2019 7:36 PM, John Fawcett via dovecot >> <dovecot at dovecot.org> wrote: >>> >>> Attached is a tentative patch. I've verified no regression for >>> mysql. There should be no

??????? Original Message ??????? On Sunday, May 19, 2019 4:44 PM, John Fawcett via dovecot <dovecot at dovecot.org> wrote: > 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') ON CONFLICT(username) UPDATE SET

On 21/05/2019 15:45, mabi via dovecot wrote: > ??????? Original Message ??????? > On Monday, May 20, 2019 12:37 AM, John Fawcett via dovecot > <dovecot at dovecot.org> wrote: > >> So looking into this with a postgresql databse to work with: the >> above query does not work. You have to specify either the column name >> or the constraint name that you expect to

Hi, I have tried to implement last_login with PostgreSQL and I found an old thread from June 2019. I have found a simple solution that I want to share with you: I followed the instructions on how to set up a last_login on the official documentation. Therefor I creates a very simple table having userid and last_login fields. Added plugin and dict information. I received the duplicate key errors

??????? Original Message ??????? On Sunday, May 19, 2019 7:36 PM, John Fawcett via dovecot <dovecot at dovecot.org> wrote: > Attached is a tentative patch. I've verified no regression for mysql. There should be no regression for sqlite as the code path is identical. > > Are you able to test for pgsql? As mentioned by Akie it will break for PostgresSql < 9.5 but probably it

??????? Original Message ??????? On Monday, May 20, 2019 12:37 AM, John Fawcett via dovecot <dovecot at dovecot.org> wrote: > So looking into this with a postgresql databse to work with: the above query does not work. You have to specify either the column name or the constraint name that you expect to be violated in order for the update to take place. > > With a map like this one

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

On 28.5.2019 22.34, mabi via dovecot wrote: > ??????? Original Message ??????? > On Monday, May 20, 2019 12:37 AM, John Fawcett via dovecot > <dovecot at dovecot.org> wrote: > >> there's no field name that is obviously the primary key. I've >> reworked the patch to use the postgres default primary key constraint >> name (tablename_pkey). >>

Hello, i have problems configuring the last_login plugin with mysql. I have extended the postfixadmin database with a new field, configured the plugin as described in the wiki. Finally i got errors: table is not writable, INSERT failed. Why is the insert required? The plugin should only UPDATE the column for existing rows. Or am i missing some configuration options? Here is my dictionary

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Wed, 22 May 2019, John Fawcett via dovecot wrote: > an update when insert fails seems to be a MySQL specific extension to > standard Sql. So I think that it's clear that support for PostgreSql and > Sqlite? needs to be implemented. The same issue likely exist in other > plugins too, for example expire. > > My doubts are

??????? Original Message ??????? On Monday, May 20, 2019 12:37 AM, John Fawcett via dovecot <dovecot at dovecot.org> wrote: > there's no field name that is obviously the primary key. I've reworked the patch to use the postgres default primary key constraint name (tablename_pkey). > > The attached fix should work in that case, although I feel it's not general enough. I

Hi, I have a Dovecot test installation with MySQL userbase and authentication where I am trying to setup last_login plugin with SQL dictionary I read http://wiki2.dovecot.org/Plugins/LastLogin and I addes this to my configuration plugin { last_login_dict = sql:/etc/dovecot/dovecot-sql.conf.ext } mail_plugins = $mail_plugins stats protocol imap { mail_plugins = $mail_plugins imap_stats