Kevin Grittner <kgrittn@ymail.com> wrote:
> Florian Pflug <fgp@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