-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
I'm using Postgres as dict backend now and then I get this error, when someone changed ACLs, actually for some reason some users, who get permission from other users, get this error quite often:
dict: Error: sql dict: commit failed: ERROR: duplicate key value violates unique constraint "user_shares_pkey" (query: INSERT INTO user_shares (dummy,to_user,from_user) VALUES ('1','user1','user2'))
It looks like that this error is triggered, when a MUA is connecting to Dovecot after ACLs had been changed and opens several mailboxes at once. So two or parallel Dovecot instances try to insert the record simultaneously.
With a similiar technique to the quota problem, I added a trigger. The idea is to use the condition dummy ISNULL to detect, that the function was triggered from the INSERT of the function itself, hence, breaking the recursive triggers. This assumes that dummy is never NULL from Dovecot.
The same applies to anyone_shares, just remove the to_user field from the function.
Does somebody see something wrong with my hack?
=============
CREATE FUNCTION insert_sharing() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF NEW.dummy IS NULL THEN -- ugly kludge: we came here from this function, really do try to insert return NEW; END IF;
LOOP UPDATE user_shares SET dummy = NEW.dummy WHERE from_user = NEW.from_user AND to_user = NEW.to_user; IF found THEN -- updated and inserted RETURN NULL; END IF;
BEGIN
INSERT INTO user_shares (from_user,to_user,dummy) VALUES (NEW.from_user,NEW.to_user,NULL); --- return NULL; EXCEPTION WHEN unique_violation THEN -- someone just inserted the record, update it END; -- Update dummy after INSERT END LOOP; END; $$;
CREATE TRIGGER insertsharing BEFORE INSERT ON user_shares FOR EACH ROW EXECUTE PROCEDURE insert_sharing();
Steffen Kaiser -----BEGIN PGP SIGNATURE----- Version: GnuPG v1
iQEVAwUBVbHxJXz1H7kL/d9rAQIn5gf/dNMQKmmigGrbccGQQrsGU66B6OHvTY7L 1vyh8hgr985KNk2/Ws/usLnAqpl5jhpo8kVPLI8TqcGYLAS/67TOIFrwLKAEBU96 e+QpbqqiSm66mZwWOQZgCI63+Fh7fdBUMREL6UqGleFF1GrzNnjmX4MGecbpu/L9 gQMAGUG++/S+ka9S6B275RXIj9DvWzq/oYqJ1M1grGckUxXfDk5szLyvuIJ981At 20qaCHEIlIOlkQ0ttE4W+RWkkYS2rMJvCzQelF4pcm6vaxhXxvqUryzmy9pv+UUT osqS6igWt7BPXF7ku8+rKYVp/qPZrh9IWXMsajr/2nI+V5gRb6u2ow== =gb1P -----END PGP SIGNATURE-----