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

--
Greg

On 11 May 2009, at 11:18, Hans-Juergen Schoenig <postgres@cybertec.at>
wrote:

hello greg,

the thing with statement_timeout is a little bit of an issue.
you could do:
SET statement_timeout TO ...;
SELECT FOR UPDATE ...
SET statement_timeout TO default;

this practically means 3 commands.

I tend to think there should be protocol level support for options
like this but that would require buy-in from the interface writers.

the killer argument, however, is that the lock might very well
happen ways after the statement has started.

Sure. But Isn't the statement_timeout behaviour what an application
writer would actually want? Why would he care how long some sub-part
of the statement took? Isn't an application -you used the example of a
web app - really concerned with its response time?

I tend to think there should be protocol level support for options
like this but that would require buy-in from the interface writers.

how would you do it?
if you support it on the protocol level, you still need a way to allow
the user to tell you how ...
i would see WAIT for DELETE, UPDATE and SELECT FOR UPDATE.
did you have more in mind?

the killer argument, however, is that the lock might very well happen
ways after the statement has started.

Sure. But Isn't the statement_timeout behaviour what an application
writer would actually want? Why would he care how long some sub-part
of the statement took? Isn't an application -you used the example of a
web app - really concerned with its response time?

no, for a simple reason: in this case you would depend ways too much in
other tasks. some other reads which just pump up the load or some
nightly cronjobs would give you timeouts which are not necessarily
related to locking. we really want to protect us against some "LOCK
TABLE IN ACCESS EXCLUSIVE MODE" - i am not looking for a solution which
kills queries after some time (we have that already). i want protect
myself against locking issues.
this feature is basically supported by most big vendor (informix,
oracle, just to name a few). i am proposing this because i have needed
it for a long time already and in this case it is also needed for a
migration project.

hans

--
Cybertec Schönig & Schönig GmbH
Professional PostgreSQL Consulting, Support, Training
Gröhrmühlgasse 26, A-2700 Wiener Neustadt
Web: www.postgresql-support.de

Hans-Juergen Schoenig <postgres@cybertec.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

Hi,

Tom Lane �rta:

Hans-Juergen Schoenig <postgres@cybertec.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.

Would the "lock_timeout" work for all to be acquired locks individually,
or all of them combined for the statement? The individual application
of the timeout for every locks individually wouldn't be too nice.
E.g. SELECT ... FOR ... WAIT N (N in seconds) behaviour in this
scenario below is not what the application writed would expect:

xact 1: SELECT ... FOR UPDATE (record 1)
xact 2: SELECT ... FOR UPDATE (record 2)
xact 3: SELECT ... FOR UPDATE WAIT 10 (record 1 and 2, waits for both
records sequentially)
xact 1: COMMIT/ROLLBACK almost 10 seconds later
xact 3 acquires lock for record 1, wait for lock on record2
xact 2: COMMIT/ROLLBACK almost 10 seconds later
xact 3 acquires lock for record 2

3rd transaction has to wait for almost 2 times the specified time.
E.g. in Informix the SET LOCK MODE TO WAIT N works
for all to-be acquired locks combined. If lock_timeout and/or
... "FOR <lockmode> WAIT N" ever gets implemented, it should
behave that way.

Best regards,
Zolt�n B�sz�rm�nyi

regards, tom lane

--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

----------------------------------
Zolt�n B�sz�rm�nyi
Cybertec Sch�nig & Sch�nig GmbH
http://www.postgresql.at/

Boszormenyi Zoltan <zb@cybertec.at> writes:

Would the "lock_timeout" work for all to be acquired locks individually,
or all of them combined for the statement? The individual application
of the timeout for every locks individually wouldn't be too nice.

I think the way you're describing would be both harder to implement
and full of its own strange traps.

regards, tom lane

Tom Lane �rta:

Boszormenyi Zoltan <zb@cybertec.at> writes:

Would the "lock_timeout" work for all to be acquired locks individually,
or all of them combined for the statement? The individual application
of the timeout for every locks individually wouldn't be too nice.

I think the way you're describing would be both harder to implement
and full of its own strange traps.

Why?

PGSemaphoreTimedLock(..., struct timespec *timeout)
{
...
gettimeofday(&tv1, NULL);
semtimedop(... , timeout);
gettimeofday(&tv2, NULL);

<decrease *timeout with the difference of tv1 and tv2>
}

Next call will use the decreased value.
Either all locks are acquired in the given time, or the next try will
timeout (error) or there are still locks and the timeout went down to
or below zero (error). Why is it hard?

regards, tom lane

--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

----------------------------------
Zolt�n B�sz�rm�nyi
Cybertec Sch�nig & Sch�nig GmbH
http://www.postgresql.at/

Boszormenyi Zoltan <zb@cybertec.at> writes:

Tom Lane �rta:

I think the way you're describing would be both harder to implement
and full of its own strange traps.

Why?

Well, for one thing: if I roll back a subtransaction, should the lock
wait time it used now no longer count against the total? If not,
once a timeout failure has occurred it'll no longer be possible for
the total transaction to do anything, even if it rolls back a failed
subtransaction.

But more generally, what you are proposing seems largely duplicative
with statement_timeout. The only reason I can see for a
lock-wait-specific timeout is that you have a need to control the
length of a specific wait and *not* the overall time spent. Hans
already argued upthread why he wants a feature that doesn't act like
statement_timeout.

regards, tom lane

Tom Lane �rta:

Boszormenyi Zoltan <zb@cybertec.at> writes:

Tom Lane �rta:

I think the way you're describing would be both harder to implement
and full of its own strange traps.

Why?

Well, for one thing: if I roll back a subtransaction, should the lock
wait time it used now no longer count against the total?

Does statement_timeout counts against subtransactions as well? No.
If a statement finishes before statement_timeout, does it also decrease
the possible runtime for the next statement? No. I was talking about
locks acquired during one statement.

If not,
once a timeout failure has occurred it'll no longer be possible for
the total transaction to do anything, even if it rolls back a failed
subtransaction.

But more generally, what you are proposing seems largely duplicative
with statement_timeout. The only reason I can see for a
lock-wait-specific timeout is that you have a need to control the
length of a specific wait and *not* the overall time spent. Hans
already argued upthread why he wants a feature that doesn't act like
statement_timeout.

He argued about he wants a timeout *independent* from statement_timeout
for locks only inside the same statement IIRC.

regards, tom lane

--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

----------------------------------
Zolt�n B�sz�rm�nyi
Cybertec Sch�nig & Sch�nig GmbH
http://www.postgresql.at/

2009/5/11 Boszormenyi Zoltan <zb@cybertec.at>:

Does statement_timeout counts against subtransactions as well? No.
If a statement finishes before statement_timeout, does it also decrease
the possible runtime for the next statement? No. I was talking about
locks acquired during one statement.

With respect I can't figure out what you're trying to say here.

He argued about he wants a timeout *independent* from statement_timeout
for locks only inside the same statement IIRC.

I think what you're saying is you think he only wanted to distinguish
total time spent waiting for locks from total time spent executing
including such things as i/o wait time. That's possible, Hans-Juergen
wasn't very clear on what "locking issues" he was concerned about. I
can think of a few categories of "locking issues" that might be
problems though:

1) A web application wants to ensure that a slow batch job which locks
records doesn't impact responsiveness. I think statement_timeout
handles this better though.

2) A batch job might want to ensure it's still "making progress" even
if slowly, but some other jobs might block indefinitely while holding
locks (for example an email generating script might be stuck waiting
for remote sites to respond). statement_timeout is better for ensuring
overall execution speed but it won't fire until the entire time
allotment is used up whereas something which detects being stuck on an
individual lock would detect the problem much earlier (and perhaps the
rest of the job could still be completed).

3) Applications which have hidden deadlocks because they block each
other outside the database while holding locks in the database. This
can be dealt with by using userlocks to represent the external
resources but that depends on all of those external resources being
identified correctly. A lock timeout would be an imprecise way to
detect possible deadlocks even though it's always possible it just
didn't wait long enough.

Hans-Juergen, are any of these use cases good descriptions of your
intended use? Or do you have a different case?
--
greg

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@cybertec.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@postgresql.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

Greg Stark �rta:

2009/5/11 Boszormenyi Zoltan <zb@cybertec.at>:

Does statement_timeout counts against subtransactions as well? No.
If a statement finishes before statement_timeout, does it also decrease
the possible runtime for the next statement? No. I was talking about
locks acquired during one statement.

With respect I can't figure out what you're trying to say here.

Sorry, bad rhetorics. Point correctly made is below.

2009/5/11 Hans-Jürgen Schönig <postgres@cybertec.at>:

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).

I kinda agree with this. I believe Tom was arguing upthread that any
change of this short should touch all of the places where NOWAIT is
accepted now, and I agree with that. But having to issue SET as a
separate statement and then maybe do another SET afterward to get the
old value back doesn't seem like it provides any real advantage. GUCs
are good for properties that you want to set and leave set, not so
good for things that are associated with particular statements.

It also seems to me that there's no reason for NOWAIT to be part of
the syntax, but WAIT n to be a GUC.

...Robert

But more generally, what you are proposing seems largely duplicative
with statement_timeout. The only reason I can see for a
lock-wait-specific timeout is that you have a need to control the
length of a specific wait and *not* the overall time spent. Hans
already argued upthread why he wants a feature that doesn't act like
statement_timeout.

I agree with Tom here; I want to wait for a specific amount of time for
a specific lock request.

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

Josh Berkus írta:

But more generally, what you are proposing seems largely duplicative
with statement_timeout. The only reason I can see for a
lock-wait-specific timeout is that you have a need to control the
length of a specific wait and *not* the overall time spent. Hans
already argued upthread why he wants a feature that doesn't act like
statement_timeout.

I agree with Tom here; I want to wait for a specific amount of time
for a specific lock request.

Well, thinking about it a bit more, I think we can live with that.
The use case would be mostly 1 record per SELECT FOR UPDATE
WAIT N query, so for this the two semantics are equal.
We would differ from Informix when one SELECT fetches
more than one record obviously.
We can have both GUC and the SQL extension for temporary setting.

SET lock_timeout = N; -- 0 means infinite? or:
SET lock_timeout = infinite;

NOWAIT
| WAIT (or no keyword as of now) for infinite waiting
| WAIT DEFAULT
| WAIT N (N seconds timeout)

Comments?

--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/

Robert Haas <robertmhaas@gmail.com> writes:

I kinda agree with this. I believe Tom was arguing upthread that any
change of this short should touch all of the places where NOWAIT is
accepted now, and I agree with that. But having to issue SET as a
separate statement and then maybe do another SET afterward to get the
old value back doesn't seem like it provides any real advantage. GUCs
are good for properties that you want to set and leave set, not so
good for things that are associated with particular statements.

My point is that I don't believe the scenario where you say that you
know exactly how long each different statement in your application
should wait and they should all be different. What I do find credible
is that you want to set a "policy" for all the lock timeouts. Now
think about what happens when it's time to change the policy. A GUC
is gonna be a lot easier to manage than timeouts that are embedded in
all your individual queries.

It also seems to me that there's no reason for NOWAIT to be part of
the syntax, but WAIT n to be a GUC.

I wasn't happy about NOWAIT in the syntax, either ;-) ... but at least
that's a boolean and not a parameter whose specific value was plucked
out of thin air, which is what it's pretty much always going to be.

regards, tom lane

Tom,

My point is that I don't believe the scenario where you say that you
know exactly how long each different statement in your application
should wait and they should all be different. What I do find credible
is that you want to set a "policy" for all the lock timeouts. Now
think about what happens when it's time to change the policy. A GUC
is gonna be a lot easier to manage than timeouts that are embedded in
all your individual queries.

For production applications, it's credible that you're going to desire
three different behaviors for different locks: you'll want to not wait
at all for some locks, wait a limited time for others, and for a few
wait forever. I agree that the time for the 2nd case wouldn't vary per
lock in any reasonable case.

I can see Zoltan's argument: for web applications, it's important to
keep the *total* wait time under 50 seconds for most users (default
browser timeout for most is 60 seconds). So it would certainly be nice
if we could somehow set total wait time instead of individual operation
wait time. It's also completely and totally unworkable on the database
layer for multiple reasons, so I'm not going to bother pushing any idea
which implements this.

So, I can see having a session-based lock_timeout GUC, and also a NOWAIT
statement. It would mean that users would need to set lock_timeout=-1
if they didn't want the lock to timeout, but that's consistent with how
other timeouts behave.

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

Josh Berkus <josh@agliodbs.com> writes:

I can see Zoltan's argument: for web applications, it's important to
keep the *total* wait time under 50 seconds for most users (default
browser timeout for most is 60 seconds).

And why is that only about lock wait time and not about total execution
time? I still think statement_timeout covers the need, or at least is
close enough that it isn't justified to make lock_timeout act like that
(thus making it not serve the other class of requirement).

regards, tom lane

On 5/11/09 4:25 PM, Tom Lane wrote:

Josh Berkus<josh@agliodbs.com> writes:

I can see Zoltan's argument: for web applications, it's important to
keep the *total* wait time under 50 seconds for most users (default
browser timeout for most is 60 seconds).

And why is that only about lock wait time and not about total execution
time? I still think statement_timeout covers the need, or at least is
close enough that it isn't justified to make lock_timeout act like that
(thus making it not serve the other class of requirement).

That was one of the reasons it's "completely and totally unworkable", as
I mentioned, if you read the next sentence.

The only real answer to the response time issue is to measure total
response time in the middleware.

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

hello everybody,

from my side the goal of this discussion is to extract a consensus so
that we can go ahead and implement this issue for 8.5.
our customer here needs a solution to this problem and we have to come
up with something which can then make it into PostgreSQL core.
how shall we proceed with the decision finding process here?
i am fine with a GUC and with an grammar extension - i just need a
decision which stays unchanged.

comments and votes are welcome.

many thanks,

hans

--
Cybertec Schönig & Schönig GmbH
Professional PostgreSQL Consulting, Support, Training
Gröhrmühlgasse 26, A-2700 Wiener Neustadt
Web: www.postgresql-support.de