Re: referential Integrity and SHARE locks

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(at)bloodnok(dot)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(at)Yahoo(dot)com #
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +