Re: Working around spurious unique constraint errors due to SERIALIZABLE bug - Mailing list pgsql-general

Florian Weimer wrote:
> SERIALIZABLE isolation level doesn't really conform to the spec
> because it doesn't deal with phantoms.  The only case I've come across
> where this actually matters is when you're implementing some sort of
> "insert into table if not yet present" operation.  This will typically
> result in a unique constraint violation.[*]
>
> Usually, constraint violations are programming errors, but not this
> one.  It's more like a detected deadlock.  Is there a way to tell this
> type of constraint violation from other types, so that the transaction
> can be restarted automatically (as if there was a deadlock)?
> Theoretically, PostgreSQL should detect that the conflicting row
> wasn't there when the snapshot for the transaction was taken, and
> somehow export this piece of information, but I'm not sure if it's
> available to the client.
>
> [*] One way to work around this is to batch inserts and eventually
> perform them in a background task which doesn't run in parallel, but
> this approach isn't always possible.

Let me construct an example:

CREATE TABLE a (id integer PRIMARY KEY);

CREATE FUNCTION ins(i integer) RETURNS boolean
   LANGUAGE plpgsql STRICT AS
$$DECLARE
   i2 integer;
BEGIN
   SELECT COUNT(id) INTO i2 FROM a WHERE id = i;
   IF i2 = 0 THEN
      /* This INSERT will never throw an exception if the
         transactions are truly serialized */
      INSERT INTO a (id) VALUES (i);
      RETURN TRUE;
   ELSE
      RETURN FALSE;
   END IF;
END;$$;

Now sessions A and B do the following:

A: START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
A: SELECT * FROM a;
 id
----
(0 rows)

B: START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
B: SELECT * FROM a;
 id
----
(0 rows)

B: SELECT ins(1);
 ins
-----
 t
(1 row)

A: SELECT ins(1);
Session A is blocked by B's exclusive lock.

B: COMMIT;

Now A gets:
ERROR:  duplicate key value violates unique constraint "a_pkey"
CONTEXT:  SQL statement "INSERT INTO a (id) VALUES ( $1 )"
PL/pgSQL function "ins" line 1 at SQL statement


This is what you are talking about, right?

I am not sure what exactly you mean by retrying the transaction in
Session A. Even on a second try A would not be able to insert the
duplicate key. But at least there would not be an error:

A: ROLLBACK;
A: SELECT ins(1);
 ins
-----
 f
(1 row)

The best way to work around a problem like this is to write
code that does not assume true serializability, for example:

BEGIN
   INSERT INTO a (id) VALUES (i);
   RETURN TRUE;
EXCEPTION
   WHEN unique_violation THEN
      RETURN FALSE;
END;

Maybe my example is too simple, but it should work similar to this whenever
error conditions are involved.

Other problems will be more tricky (I am thinking of the example I
constructed for http://archives.postgresql.org/pgsql-hackers/2009-05/msg00316.php).
I don't think that there is a "king's way" to cope with all possible problems.

Yours,
Laurenz Albe