possible patch for ACL SQL ERROR: duplicate key value violates unique constraint "user_shares_pkey"

[Steffen Kaiser]

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