Re: SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5

hello tom ...

the reason for SELECT FOR UPDATE is very simple:
this is the typical lock obtained by basically every business
application if written properly (updating a product, whatever).
the problem with NOWAIT basically is that if a small transaction holds
a a lock for a subsecond, you will already lose your transaction
because it does not wait at all (which is exactly what you want in
some cases). however, in many cases you want to compromise on wait
forever vs. die instantly.
depending on the code path we could decide how long to wait for which
operation. this makes sense as we would only fire 1 statement instead
of 3 (set, run, set back).

i agree that a GUC is definitely an option.
however, i would say that adding an extension to SELECT FOR UPDATE,
UPDATE and DELETE would make more sense form a usability point of view
(just my 0.02 cents).

if hackers' decides to go for a GUC, we are fine as well and we will
add it to 8.5.

many thanks,

hans

On May 11, 2009, at 4:46 PM, Tom Lane wrote:

> Hans-Juergen Schoenig <postgres(at)cybertec(dot)at> writes:
>> i would like to propose an extension to our SELECT FOR UPDATE
>> mechanism.
>> especially in web applications it can be extremely useful to have the
>> chance to terminate a lock after a given timeframe.
>
> I guess my immediate reactions to this are:
>
> 1. Why SELECT FOR UPDATE in particular, and not other sorts of locks?
>
> 2. That "clear and easy to use" oracle syntax sucks. You do not want
> to be embedding lock timeout constants in your application queries.
> When you move to a new server and the appropriate timeout changes,
> do you want to be trying to update your clients for that?
>
> What I think has been proposed previously is a GUC variable named
> something like "lock_timeout", which would cause a wait for *any*
> heavyweight lock to abort after such-and-such an interval. This
> would address your point about not wanting to use an overall
> statement_timeout, and it would be more general than a feature
> that only works for SELECT FOR UPDATE row locks, and it would allow
> decoupling the exact length of the timeout from application query
> logic.
>
> regards, tom lane
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: www.postgresql-support.de