Re: Working around spurious unique constraint errors due to SERIALIZABLE bug

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