Thread

1. bug #126, referential integrity makes big LOCK

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2001-02-13T22:49:11Z

   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 sure that 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 database with an integrity violation. So, either tran 2 (or at least its triggers) needs access to tran 1's changes, even though tran 1 isn't finished yet, or tran 1 needs a way to block tran 2 from altering data its relying on, until tran 2'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 they are not actually going to alter the thing being selected with FOR UPDATE. By locking it, "we" force other entities to not 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 above scenario to happen.
   
   Perhaps something about this could be put in the documentation on referential integrity.
   
   Sample Code
   
   
   No file was uploaded with this report
   
   
   

2. Re: bug #126, referential integrity makes big LOCK

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-02-13T23:49:56Z

   pgsql-bugs@postgresql.org writes:
   > 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.
   
   This is exactly what the SELECT FOR UPDATEs are in there to prevent.
   
   			regards, tom lane