Florian Pflug <fgp(at)phlo(dot)org> wrote:
> The serialization error, however, disappears if the two
> transactions are swapped. The following sequence of commands
> succeeds, even though the FK constraint is not satisfied.
>
> C1: BEGIN
> C1: INSERT INTO child (parent_id) VALUES (0)
> C2: BEGIN
> C2: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> C2: SELECT TRUE -- Take snapshot *before* C1 commits
> C1: COMMIT
> C2: DELETE FROM parent WHERE parent_id = 0 -- Works!
> C2: COMMIT
Thanks for another good example. Added to serializable test suite.
C2> DELETE FROM parent WHERE parent_id = 0;
ERROR: could not serialize access due to read/write dependencies
among transactions
HINT: The transaction might succeed if retried.
CONTEXT: SQL statement "SELECT TRUE FROM child WHERE parent_id =
OLD.parent_id"
PL/pgSQL function "ri_parent" line 2 at PERFORM
By the way, when adding these, I'm taking off the "FOR SHARE" or
"FOR UPDATE" clauses; they're not needed with true serializable
transactions. Otherwise, examples used as presented.
-Kevin