Dave E Martin (xxiii@cyberdude.com) reports a bug with a severity of 4
The lower the number the more severe it is.
Short Description
bug #126, referential integrity makes big LOCK
Long Description
In referral to bug #126, where someone is wondering why triggers use FOR UPDATE on data they aren't actually going to
update:
Alas, bug responses are not public, so either this is really a problem, or its not, I don't know. I post here to make
surethat this is a scenario that has been considered. I assume the reason is:
given mvcc, the following could happen if not careful:
transaction 1:
begin
huge update
commit
referential triggers start
transaction 2
begin
delete something that referential triggers from tran 1 just checked and are relying on.
commit
tran 2 triggers see pre-tran 1 data and think its ok to delete.
tran 2 finishes before tran 1.
tran 1 already checked that tran 2 row and thinks its fine, commit
finishes.
Because of mvcc tran 2 does not realize there is a problem (its triggers looked at the state of things before tran 1
began)
meanwhile tran 2 then completes, but now the state is not the same as it was when tran 2 began, and we now have a
databasewith an integrity violation. So, either tran 2 (or at least its triggers) needs access to tran 1's changes,
eventhough tran 1 isn't finished yet, or tran 1 needs a way to block tran 2 from altering data its relying on, until
tran2's triggers can see tran 1's alterations.
Presumably, by the triggers using FOR UPDATE, they circumvent mvcc, and force the right thing to happen, even though
theyare not actually going to alter the thing being selected with FOR UPDATE. By locking it, "we" force other entities
tonot alter it until the assumptions "we" made based on that value are committed.
This is some speculation on my part based on what i've read, and I at least hope that postgres would not allow the
abovescenario to happen.
Perhaps something about this could be put in the documentation on referential integrity.
Sample Code
No file was uploaded with this report
