Re: Question about RI checks

Florian Pflug <fgp(at)phlo(dot)org> wrote:
> Florian Pflug <fgp(at)phlo(dot)org> wrote:

> But that's wrong. The transaction's snapshot *would* see that row, so we
> ought to raise an error. Note that this applies also to mode SERIALIZABLE, and
> breaks true serializability in some cases, since we don't do conflict detection
> for RI enforcement queries.
>
> Here's a test case, involving two transaction A and B. I tried this on
> REL9_4_STABLE.
>
> Setup
> -----
> CREATE TABLE parent (id int NOT NULL PRIMARY KEY);
> CREATE TABLE child (id int NOT NULL PRIMARY KEY,
> parent_id int NOT NULL REFERENCES parent (id));
> INSERT INTO parent (id) VALUES (1);
> INSERT INTO child (id, parent_id) VALUES (1, 1);
>
> Failure Case
> ------------
> A:: set default_transaction_isolation='serializable';
> A:: begin;
> A:: select * from child;
> -> id | parent_id
> ----+-----------
> 1 | 1
> B:: set default_transaction_isolation='serializable';
> B:: begin;
> B:: delete from child;
> -> DELETE 1
> B:: commit;
> A:: delete from parent;
> -> DELETE 1
> A:: commit;
>
> A can neither come before B in any serializable history (since the DELETE
> should fail then), nor after it (since it shouldn't see the row in the child
> table then). Yet we don't complain, even though both transaction run in
> SERIALIZABLE mode.

Simplifying the display of this a bit:

Tables parent and child each have one row.

Transaction A
=============
select * from child;
[it sees the child row]
Transaction B
=============
delete from child;
[child row is deleted]
delete from parent;
[parent row deleted]

TA sees the row that TB deletes, creating a rw-dependency that
implies that TA ran first. On the other hand, the FK trigger
fired by the delete from parent *doesn't* see the row deleted by
TB, which implies that TB ran first. I agree, this is a problem
for serializable transactions.

This should not be considered a problem for repeatable read
transactions because the change in visible rows meet the definition
of phantom reads, which are allowed in repeatable read: "A
transaction re-executes a query returning a set of rows that
satisfy a search condition and finds that the set of rows
satisfying the condition has changed due to another
recently-committed transaction." Phantom reads are not *required*
to occur in repeatable read transactions, and in PostgreSQL they
generally don't, so we *might* want to change this behavior; I'm
just saying that we are conforming to requirements of the standard
even if we don't. Leaving this alone for repeatable read
transactions would require a change to our documentation, though,
since we currently assert that we don't allow phantom reads in our
repeatable read implementation.

Either SSI needs to include the RI checking queries in its tracking
*or* TA needs to throw an error if there are child rows visible
according to its transaction snapshot -- at least in serializable
transactions.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company