Re: outer joins and for update - Mailing list pgsql-hackers

From Jim C. Nasby
Subject Re: outer joins and for update
Date
Msg-id 20051115165534.GJ18570@pervasive.com
Whole thread Raw
In response to Re: outer joins and for update  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
On Mon, Nov 14, 2005 at 07:38:00PM -0500, Tom Lane wrote:
> "Jim C. Nasby" <jnasby@pervasive.com> writes:
> > Do we really need to prevent inserts from happening under a SELECT FOR
> > UPDATE? ISTM that's trying to apply serializable concurrency to SELECT
> > FOR UPDATE even if it's running in a read committed transaction. In the
> > single table case we don't prevent someone from inserting a value...
> 
> You're missing the point entirely, Jim.  In the first place, SELECT FOR
> UPDATE has little or nothing to do with serializable mode: it's
> guaranteed to lock and return the latest committed version of the row.
> In the second case, inserting additional tuples does not invalidate your
> lock on the tuples you selected to begin with.  SELECT FOR UPDATE
> doesn't try to guarantee that if you were to select again with the same
> WHERE condition, there might not be more rows matching the same
> condition.  It does try to guarantee that the rows you selected before
> are still there and unchanged.
> 
> In the case being discussed here, you're trying to lock rows of an
> outer-join.  IMHO, if that means anything at all, it means that if
> you read those rows again they will still look the same.  Having the
> righthand side go from NULL to not-NULL does not qualify as "looking the
> same" in my book.

Another way to look at it is that it's not locking rows in a SELECT
clause, it's locking rows in tables. In fact, that's at least supported
by DB2; it allows you to specify field or table names when you do SELECT
FOR UPDATE on a join. That way you can tell it exactly what you expect
to be updating, and what exactly it should be locking.

> Perhaps this could be clarified if someone has an actual use case of
> wanting to SELECT FOR UPDATE from an outer join, and can explain what
> semantics they think they need for that.

Agreed. Hopefully the original author can provide one.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461


pgsql-hackers by date:

Previous
From: "Dave Page"
Date:
Subject: Re: Running PostGre on DVD
Next
From: Bruce Momjian
Date:
Subject: Re: Fixes for 8.1 run of pgindent