Kevin Grittner <kgri...@ymail.com> wrote: > Florian Pflug <f...@phlo.org> wrote:
>> Also, note that after the DELETE FROM parent, further SELECTS in >> the same transaction will use the original snapshot again, und >> thus will see the conflicting child rows again that were ignored >> by the RI trigger. But they won't, of course, see the parent >> row. >> >> IOW, transaction A will, after the delete, see a state of the >> database in which the PK constraint is broken. I don't think >> that's acceptable in any isolation level. > > Good point. Based on that observation, I agree that our RI is > broken at both the REPEATABLE READ and SERIALIZABLE isolation > levels. I think that READ COMMITTED is OK, because it will see > the child row as deleted in time to prevent problems. Every way I look at it, inside a REPEATABLE READ or SERIALIZABLE transaction a check for child rows when validating a parent DELETE should consider both rows which exist according to the transaction snapshot and according to a "current" snapshot. Interestingly, the run of the query passes both snapshots through to the executor, but for this query the estate->es_crosscheck_snapshot field (which contains the transaction snapshot) doesn't seem to be consulted. It makes me wonder whether we were at some point doing this right and it later got broken. Before I write a patch to fix this, does anyone feel that we should not use that -- in other words, does anyone consider that it is OK for a REPEATABLE READ or SERIALIZABLE transaction to delete a referenced row if that transaction can see referencing rows but a concurrent transaction has deleted them? (This currently allows subsequent queries in the transaction to see orphaned "child" rows when they can no longer see the parent.) -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
