Re-to-archived-thread: Dict issue with PostgreSQL for last_login plugin (duplicate key)

[lists at mlserv.org]

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 and I did this trick for now, which seems to solve this issue:

CREATE OR REPLACE RULE insert_to_update AS
        ON INSERT
        TO users WHERE EXISTS (SELECT userid FROM users WHERE userid = NEW.userid)
        DO INSTEAD
        UPDATE users SET last_login = NEW.last_login WHERE userid = NEW.userid

With this rule in place, INSERTs are changed to UPDATEs, if a userid entry already exists. No ned to patch code.

Feel free to comment :-)

Christian

Thread online: https://www.dovecot.org/list/dovecot/2019-May/115921.html
-- 
Rößner-Network-Solutions
Karl-Bröger-Str. 10, 36304 Alsfeld
Fax: +49 6631 78823409, Mobil: +49 171 9905345
USt-IdNr.: DE225643613, https://roessner.website
PGP fingerprint: 658D 1342 B762 F484 2DDF 1E88 38A5 4346 D727 94E5