Re: BUG #15026: Deadlock using GIST index

Peter Geoghegan-4 wrote
> While I think that your complaint is a legitimate one, I'd say that
> it's very unlikely to be fixed. Ordinary (non-deferrable) unique
> constraints don't have this problem because they pessimistically lock
> the first leaf page the value could be on ahead of inserting. In
> contrast, exclusion constraints generally optimistically detect any
> conflict in a separate pass. That's how you can get concurrent
> insertions to wait on each other, rather than having a clear "winner"
> at the precise point that insertion occurs.

I was already afraid of this reading your post in pg-hackers (Jan 24th 2015,
"Moving ExecInsertIndexTuples and friends to new file") where comment was
added in sourcecode:

> FWIW, both Jeff Davis and Tom Lane were well aware of this issue back
> when exclusion constraints went in - it was judged to be acceptable at
> the time, which I agree with. I happened to discuss this with Jeff in
> New York recently. I agree that it should definitely be documented
> like this (and the fact that ordinary unique indexes are unaffected,
> too).

But how to work around this deadlock without requiring a more general lock,
like table lock or some other related object? I need guaranteed
transactional processing. In case of (this) deadlock, I attempted to catch
the deadlock and retry. A new issue popped up: if more then two sessions
compete for the lock, I get an infinite loop of deadlocks (cf. "Looking for
workaround to avoid deadlock when using exclusion constraint" post of Feb 4
2018).

Following code snippet appears to work if pg_sleep is larger than
deadlock_timeout value, but I have two problems with that:
- I need a deadlock timeout as small as possible (10ms might work, not
sure). This will result in many long wait message in log (which I want to
monitor...) plus higher CPU load.
- I'm not 100% sure this works in all cases.

DO $$
DECLARE
   key_       locked.key%TYPE;
   ctid_      locked.ctid%TYPE;
BEGIN
   WHILE key_ IS NULL
   LOOP
      BEGIN
         SELECT key, ctid FROM locked WHERE key = 'a' INTO STRICT key_,
ctid_;
      EXCEPTION
         WHEN no_data_found THEN
            BEGIN
               INSERT INTO locked(key) values('a') RETURNING key, ctid INTO
key_, ctid_;
            EXCEPTION
               WHEN exclusion_violation THEN NULL;
               WHEN deadlock_detected THEN
                  RAISE WARNING 'Deadlock!! (t=%)', clock_timestamp();
                  PERFORM pg_sleep(1.1); -- deadlock_timeout = 1s
            END;
      END;
   END LOOP;
   RAISE WARNING 'key=%,ctid=%', key_, ctid_;
END
$$;

Execute DO block above from at least 3 sessions after inserting a row from
4th session, and rolling back after all sessions are started:

DROP TABLE IF EXISTS locked; 
CREATE TABLE locked 
( 
    key text NOT NULL, 
    EXCLUDE USING gist (key WITH =) 
); 
BEGIN; 
INSERT INTO locked(key) values('a');
-- Start 3+ DO blocks 
ROLLBACK;



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html