Re: Select For Update and Left Outer Join - Mailing list pgsql-hackers

On Jul11, 2011, at 18:55 , Kevin Grittner wrote:
> Robert Haas <robertmhaas@gmail.com> wrote:
>> I find these responses to be a bit off point.
> 
> The OP is basically looking for what Florian tried to implement. 
> This is perhaps a *bit* off point, but arguably not more than
> pointing someone who is requesting planner hints in another
> direction.  And someone thought the issues were related here:
> 
> http://archives.postgresql.org/pgsql-hackers/2010-12/msg01792.php

Hm, I'm not so sure we're looking for the same thing here.

It seems to me that what the OP (or actually Hibernate) wants
are two related, but different, things.

(A) First, for a way to UPDATE a row that was returned by a previous
SELECT, without the need to know a set of fields which comprise
a candidate or primary key. So far, this has nothing to do with
locking, and everything with to do with convenience. The postgres
way of doing that is including the ctid of all to-be-updated
relations in the SELECT's target lest, and using UPDATE ...
WHERE ctid = <ctid from select>. Not sure how that behaves if
the row has been updated after the SELECT but before the UPDATE,
though...

(B) Secondly (but I don't know if this is even Hibernate's intention,
I'm no Hibernate expert), it might that Hibernate is trying to get
true serializability by doing S2PL, i.e. taking a lock on every row
it reads. That seems like a rather unwise thing to do on postgres,
because of the way we handle row locks. 

Part (B) has some relationship to what I tried to archive by
changing the way REPEATABLE READ transactions and row locks interact.
Though my intention wasn't full serializability, only enough protection
to make user-space FOREIGN KEYS work safely for REPEATABLE READ
transactions.

@OP, could you explain whether it is (A) or (B) or both that Hibernate
tries to archive with "FOR UPDATE".

>> The OP's point is that we - particularly Tom - have argued in the
>> past that we shouldn't allow this because it's too ill-defined 
>> and/or confusing.
> 
> And I have argued that what Florian wanted would be a valuable
> addition.  The approach foundered on technical details, although in
> re-reading the thread I'm wondering if it wouldn't make sense to
> dodge all that by having SELECT FOR UPDATE simple *do* a no-op
> UPDATE RETURNING.  This would cause behavior matching Oracle and MS
> SQL Server (when the latter is using MVCC without S2PL).  DB2 is
> more strict, acquiring a predicate lock over the selected range, but
> we can't be compatible with both behaviors at the same time.

That'd make FOR UPDATE much more expensive than it is today. As it
stands, FOR UPDATE causes the page containing to tuple to be dirtied,
but it doesn't require new index entries and generate no new tuple
version which must be cleaned up by VACUUM. (To be fair, however,
HOT would reduce the impact somewhat, but still...).

BTW, the technical issues that prevented my patch from working
correctly are tightly related to the issues that plague the combination
of sub-transactions and row locks. Namely that UPDATE overwrites the
information about previous lock holders, and the information stays gone
even if the UPDATE later rolls back. But so far, how to fix that evades
me, at least without major hacks or changes to the on-disk format.

best regards,
Florian Pflug