Re: referential Integrity and SHARE locks - Mailing list pgsql-hackers

Added to TODO:
* Allow UPDATEs on only non-referential integrity columns not to conflict  with referential integrity locks
http://archives.postgresql.org/pgsql-hackers/2007-02/msg00073.php


---------------------------------------------------------------------------

Jan Wieck wrote:
> On 2/8/2007 2:46 PM, Marc Munro wrote:
> > On Thu, 2007-08-02 at 14:33 -0500, Tom Lane wrote:
> >> Marc Munro <marc@bloodnok.com> writes:
> >> > Yes in this case, T1 must abort because the record it was going to
> >> > update has disappeared from underneath it.  I don't see how this is
> >> > significantly different from the same race for the record if the table
> >> > had no RI constraints.  The only difference that I can see, is that T1
> >> > now has some locks that it must relinquish as the transaction aborts.
> >> 
> >> No, the difference is there would have been no error at all before;
> >> if the record were deleted before T1 got to it then it wouldn't have
> >> attempted to update it.  I really don't think you can make it work
> >> to perform updates or deletes on a record you have not yet locked.
> > 
> > The record would be locked before the update or delete is attempted,
> > however it would not be locked until the referential integrity
> > constraints have succeeded in acquiring their locks.
> > 
> > It is becoming clear to me that I am missing something but I still don't
> > know what it is.  If anyone can see it and explain it I'd really
> > appreciate it.
> 
> I think you are missing the fact that the exclusive row lock on UPDATE 
> is taken before any triggers are fired at all, even BEFORE ROW triggers. 
> This is necessary in order to prevent the row being updated or removed 
> concurrently while the triggers are executing. Since BEFORE ROW triggers 
> can modify the content of the row (including the foreign key), the RI 
> check and lock of the referenced row cannot happen before other BR 
> triggers are completed.
> 
> In order to make your idea fly, the RI check trigger on INSERT or UPDATE 
> would have to be fired before taking the row lock considering the NEW 
> values for referencing columns as they are thus far. Since the row isn't 
> locked at this time, it can change or disappear while the RI trigger is 
> executing, so the check and lock has to be redone later with the actual 
> row that got locked and after all BR triggers are done with it.
> 
> 
> Jan
> 
> -- 
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me.                                  #
> #================================================== JanWieck@Yahoo.com #
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly

--  Bruce Momjian  <bruce@momjian.us>          http://momjian.us EnterpriseDB
http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +