Re: SHARE locks vs. DELETE in SERIALIZABLE mode (Was: Partitioning/inherited tables vs FKs)

Florian Pflug <fgp@phlo.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