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 includingUPDATE 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.)
regards, tom lane