On Wed, 24 Sep 2003, Tom Lane wrote:
> Wade Klaver <archeron@wavefire.com> writes:
> > OK, I set you up a login on arch.wavefire.com
>
> Okay, what I find is this sequence of events:
>
> 1. delete from te_users where id = 954;
>
> 2. The ON DELETE CASCADE RI constraint propagates this to a delete of
> some row(s) in c_categories.
>
> 3. That fires the c_delete_categories BEFORE DELETE trigger.
>
> 4. That does several things including
> UPDATE c_categories SET lft = lft - 2 WHERE lft > old.rgt;
>
> 5. This update command suffers a Halloween problem, namely trying to
> update rows it's already updated.
>
> Why does it do that, you ask? Because ReferentialIntegritySnapshotOverride
> is true, since we are inside the ON DELETE CASCADE RI trigger and
> haven't yet returned from any trigger. So instead of using the correct
> snapshot for the UPDATE command, tqual.c mistakenly uses SnapshotNow
> rules. We have successfully executed a select or two inside the trigger
> function already, so CurrentCommandId is greater than the command ID
> associated with the UPDATE command, making the updated rows visible.
> Oops.
>
> I think this is proof of something I've felt since day one, namely that
> a global ReferentialIntegritySnapshotOverride flag is an unusable hack.
> How can we get rid of it? Why did we need it in the first place?
>
> (I suspect the proper answer for "how can we get rid of it" will be to
> extend the Executor API so that the RI functions can tell the executor
> to use SnapshotNow as es_snapshot, instead of a standard query snapshot.
> But I'm wondering why we have to do this at all.)
I think if you have something like:
create table test1 (id int primary key, otherid int references test1);
insert into test1 values (4,4);
T1: begin;
T1: set transaction isolation level serializable;
T1: select * from test1;
T2: begin;
T2: insert into test1 values (5,4);
T2: end;
T1: delete from test1 where id=4;-- I think the standard snapshot rules would mean that the row 5,4 would be hidden
fromthe select in the trigger, which means that the delete would be allowed, where it should fail since that'd leave
anorphaned child row.
Or at least I've commented out the updates to
ReferentialIntegritySnapshotOverride in my local ri_triggers.c and see
behavior consistent with that (that the delete succeeds and the child
row is orphaned).