NO WAIT ...

=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at> writes:

i have attached a patch implementing NO WAIT with the help of a GUC
variable.

I consider this patch incredibly dangerous, as it affects *every* lock
taken, including system internal lock acquisitions.

I think it might be reasonable to implement a no-wait option on explicit
LOCK TABLE commands, and perhaps we could do it for SELECT FOR UPDATE
as well. But it should not be done in a way that breaks internal lock
attempts.

Also, I don't care for the idea of a GUC variable affecting this.
See recent discussions about how changing fundamental semantics via
easily-changed GUC values is risky. If we're going to do it we should
add syntax to the LOCK command so that apps explicitly request it.

regards, tom lane

Tom Lane wrote:

=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at> writes:

i have attached a patch implementing NO WAIT with the help of a GUC
variable.

I consider this patch incredibly dangerous, as it affects *every* lock
taken, including system internal lock acquisitions.

I think it might be reasonable to implement a no-wait option on explicit
LOCK TABLE commands, and perhaps we could do it for SELECT FOR UPDATE
as well. But it should not be done in a way that breaks internal lock
attempts.

Also, I don't care for the idea of a GUC variable affecting this.
See recent discussions about how changing fundamental semantics via
easily-changed GUC values is risky. If we're going to do it we should
add syntax to the LOCK command so that apps explicitly request it.

There was discussion and I thought agreement that a GUC was best because
we wouldn't have to add syntax to every command. I think the idea
originally was that we were going to do nowait only on exclusive locks
and not shared ones, which would hopefully reduce system lock cases
where are usually shared ones.

I imagine folks would want it on UPDATE, DELETE, and VACUUM FULL too,
and that's why a GUC makes more sense, rather than littering the syntax
with nowait controls.

Also, I don't see this changing sematics like the regex flavor did.
With that one, we actually had stored data in a table that wouldn't
match a CHECK constraint. This isn't going to affect data validity,
only query success.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tom,

Yes, it can be dangerous. I am aware of that.
The problem with adding NO WAIT to specific commands is that is
inheritly unflexible. I think this is why the community has agreed on
implementing it based on GUC.
I have done some testing with a real world application. As far as I can
see it did not have an impact on other internals (at least not when used
cleverly).
SELECT FOR UPDATE NO WAIT should be added as well because it might be
useful to Oracle <-> compatibility.
Do you think it would help to reduce the GUCs flexibility by reducing
the lock levels a user is allowed to define?

Best regards,

Hans

Tom Lane wrote:

=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at> writes:

i have attached a patch implementing NO WAIT with the help of a GUC
variable.

I consider this patch incredibly dangerous, as it affects *every* lock
taken, including system internal lock acquisitions.

I think it might be reasonable to implement a no-wait option on explicit
LOCK TABLE commands, and perhaps we could do it for SELECT FOR UPDATE
as well. But it should not be done in a way that breaks internal lock
attempts.

Also, I don't care for the idea of a GUC variable affecting this.
See recent discussions about how changing fundamental semantics via
easily-changed GUC values is risky. If we're going to do it we should
add syntax to the LOCK command so that apps explicitly request it.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/2952/30706 or +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I imagine folks would want it on UPDATE, DELETE, and VACUUM FULL too,

Why? You can do a SELECT FOR UPDATE first and then you know that you
have the row lock. There's no need for any special handling of UPDATE
or DELETE. I don't see the applicability to VACUUM, either.

BTW, one idea I was thinking about was that a SELECT FOR UPDATE NOWAIT
behavior might simply not return the rows it couldn't acquire lock on,
instead of erroring out. Not sure if this would be more or less useful
than the error behavior, but I can definitely think of possible
applications for it.

Also, I don't see this changing sematics like the regex flavor did.

You're kidding. This is a much more fundamental change of behavior than
whether some seldom-used regex features work. In particular, we know
that the regex behavior does not affect any other part of the system.
I do not think any equivalent safety claims can be made for random
hacking of whether LockAcquire succeeds or not.

regards, tom lane

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I imagine folks would want it on UPDATE, DELETE, and VACUUM FULL too,

Why? You can do a SELECT FOR UPDATE first and then you know that you
have the row lock. There's no need for any special handling of UPDATE
or DELETE. I don't see the applicability to VACUUM, either.

Why bother when you can do it without the SELECT FOR UPDATE?

BTW, one idea I was thinking about was that a SELECT FOR UPDATE NOWAIT
behavior might simply not return the rows it couldn't acquire lock on,
instead of erroring out. Not sure if this would be more or less useful
than the error behavior, but I can definitely think of possible
applications for it.

Also, I don't see this changing sematics like the regex flavor did.

You're kidding. This is a much more fundamental change of behavior than

No, I am not.

whether some seldom-used regex features work. In particular, we know
that the regex behavior does not affect any other part of the system.
I do not think any equivalent safety claims can be made for random
hacking of whether LockAcquire succeeds or not.

It throws an error. I don't see how that could cause actual data
corruption or invalid data.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at> writes:

The problem with adding NO WAIT to specific commands is that is
inheritly unflexible. I think this is why the community has agreed on
implementing it based on GUC.

I recall no such agreement ... when was this exactly? In any case
Bruce's recent complaints about regex_flavor have altered my opinions
about GUC variables a bit. They are bigger safety risks than they look,
especially ones that change semantics and are intended to be modified on
the fly.

Do you think it would help to reduce the GUCs flexibility by reducing
the lock levels a user is allowed to define?

I will vote against the patch no matter what, but I agree that it would
be less dangerous if it were confined to only apply to a limited set of
lock types.

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

Why? You can do a SELECT FOR UPDATE first and then you know that you
have the row lock. There's no need for any special handling of UPDATE
or DELETE. I don't see the applicability to VACUUM, either.

Why bother when you can do it without the SELECT FOR UPDATE?

Because you want the extra feature?

It throws an error. I don't see how that could cause actual data
corruption or invalid data.

I am concerned about what behavior will stop working nicely when locks
that normally always succeed suddenly error out instead. Perhaps it
won't corrupt your database, but that doesn't mean that the behavior
will be pleasant.

As an example: the proposed patch is able to cause an error instead of
waiting for access-share locks. Suppose you actually turn that on, and
then try to call some function, and the resulting attempt to read
pg_proc errors out because someone was transiently holding a conflicting
lock. This means your application fails, randomly, and in
hard-to-reproduce ways. Not only would the failure or not-failure
depend on what other people were doing, it'd depend on whether you'd
already cached the function definition (if so, no lock would actually
get taken on pg_proc during the call).

I think a feature narrowly focused on suppressing waits for specific
locks (like the lock on a specific row that you're trying to update)
would be useful. Implementing something that affects *every* lock in
the system is nothing more nor less than a foot-gun, because you could
never be very certain what lock attempts would fail.

regards, tom lane

Tom Lane wrote:

=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at> writes:

The problem with adding NO WAIT to specific commands is that is
inheritly unflexible. I think this is why the community has agreed on
implementing it based on GUC.

I recall no such agreement ... when was this exactly? In any case
Bruce's recent complaints about regex_flavor have altered my opinions
about GUC variables a bit. They are bigger safety risks than they look,
especially ones that change semantics and are intended to be modified on
the fly.

Do you think it would help to reduce the GUCs flexibility by reducing
the lock levels a user is allowed to define?

I will vote against the patch no matter what, but I agree that it would
be less dangerous if it were confined to only apply to a limited set of
lock types.

The question is whether we should have a GUC variable to control no
waiting on locks or add NO WAIT to specific SQL commands.

Does anyone want to vote _against_ the GUC idea for nowait locking. (We
already have two voting for such a variable.)

If there is no one except Tom, we can continue.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

Why? You can do a SELECT FOR UPDATE first and then you know that you
have the row lock. There's no need for any special handling of UPDATE
or DELETE. I don't see the applicability to VACUUM, either.

Why bother when you can do it without the SELECT FOR UPDATE?

Because you want the extra feature?

It throws an error. I don't see how that could cause actual data
corruption or invalid data.

I am concerned about what behavior will stop working nicely when locks
that normally always succeed suddenly error out instead. Perhaps it
won't corrupt your database, but that doesn't mean that the behavior
will be pleasant.

As an example: the proposed patch is able to cause an error instead of
waiting for access-share locks. Suppose you actually turn that on, and
then try to call some function, and the resulting attempt to read
pg_proc errors out because someone was transiently holding a conflicting
lock. This means your application fails, randomly, and in
hard-to-reproduce ways. Not only would the failure or not-failure
depend on what other people were doing, it'd depend on whether you'd
already cached the function definition (if so, no lock would actually
get taken on pg_proc during the call).

I think a feature narrowly focused on suppressing waits for specific
locks (like the lock on a specific row that you're trying to update)
would be useful. Implementing something that affects *every* lock in
the system is nothing more nor less than a foot-gun, because you could
never be very certain what lock attempts would fail.

The original idea I had was for the GUC variable to affect only
exclusive locks. If we want more, we can add it later. I agree the
extra GUC which controls the types of locks we will nowait for seems
pretty useless.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

The question is whether we should have a GUC variable to control no
waiting on locks or add NO WAIT to specific SQL commands.

That's only a minor part of the issue. The major problem I have with
the patch is that it affects *all* locks, including system-internal
lock attempts that the user is probably not even aware of much less
able to control. It's like giving someone a poorly-aligned shotgun
when what they need is a rifle --- they'll end up putting holes in
a lot of other things besides what they intended.

I think that what we actually want is something that is narrowly
tailored to affect only row-level locks taken by SELECT FOR UPDATE,
and maybe one or two other places that (a) people can make specific
use-cases for, and (b) we can be certain are only invoked by user
commands and never indirectly from behind-the-scenes system operations.

The reason for proposing syntax rather than a GUC variable is the same
one of control. If you set a GUC variable then it will be hard to
prevent it from breaking operations other than the one you thought you
intended. (Example: you think you are only causing your SELECT FOR
UPDATE to error out, but what about ones done behind the scenes for
foreign key checks?) GUC variables are good for stuff that tends to
apply application-wide, which is why I thought regex_flavor wasn't too
dangerous, but they're terrible for functions that you want to apply to
only certain specific operations. And I can't imagine an app where that
wouldn't be true for NO WAIT.

regards, tom lane

Tom Lane wrote:

=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at> writes:

The problem with adding NO WAIT to specific commands is that is
inheritly unflexible. I think this is why the community has agreed on
implementing it based on GUC.

I recall no such agreement ... when was this exactly? In any case
Bruce's recent complaints about regex_flavor have altered my opinions
about GUC variables a bit. They are bigger safety risks than they look,
especially ones that change semantics and are intended to be modified on
the fly.

I thought there was an agreement because the GUC version is on the TODO
list. Anyway ...

Do you think it would help to reduce the GUCs flexibility by reducing
the lock levels a user is allowed to define?

I will vote against the patch no matter what, but I agree that it would
be less dangerous if it were confined to only apply to a limited set of
lock types.

regards, tom lane

Tom,

I think we should compromise.
We can restrict it to locks which are high enough or higher to make
SELECT FOR UPDATE work.
Of course it would have been nice to have full flexibility but I think
we can have almost the same benefit for lower risk.
How about it? Tom, Bruce - which types of locks do we allow?
I will change the patch then.
Maybe this is the best solution.

Regards,

Hans

--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/2952/30706 or +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at

--On Wednesday, February 18, 2004 13:56:14 -0500 Bruce Momjian
<pgman@candle.pha.pa.us> wrote:

Tom Lane wrote:

The question is whether we should have a GUC variable to control no
waiting on locks or add NO WAIT to specific SQL commands.

Does anyone want to vote _against_ the GUC idea for nowait locking. (We
already have two voting for such a variable.)

If there is no one except Tom, we can continue.

I'm with Tom. Playing with *ALL* locks is just asking for TROUBLE.

(I don't know if I count).

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

The question is whether we should have a GUC variable to control no
waiting on locks or add NO WAIT to specific SQL commands.

Does anyone want to vote _against_ the GUC idea for nowait locking. (We
already have two voting for such a variable.)

I vote against. We got bit by both the regex and the autocommit GUC vars
and this is setting up to cause a similar headache with old code on new
platforms.

I agree with Tom here. I have used the Oracle NOWAIT feature in the
past and think it is a great feature IMHO. But when you need to use it,
you want it to apply very specifically to a single statement. Using a
sledge hammer when you need a tweezers isn't the right way to go.

thanks,
--Barry

Tom Lane wrote:

Bruce Momjian wrote:

The question is whether we should have a GUC variable to control no
waiting on locks or add NO WAIT to specific SQL commands.

Does anyone want to vote _against_ the GUC idea for nowait locking. (We
already have two voting for such a variable.)

If there is no one except Tom, we can continue.

This strikes me as a very broad-brush approach. I suspect that even if
we did not cause major breakage we would end up wanting to go back and
implement the finer grained per command mechanism.

So I'm not in favor - always open to persuasion, of course ;-)

cheers

andrew

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

The question is whether we should have a GUC variable to control no
waiting on locks or add NO WAIT to specific SQL commands.

That's only a minor part of the issue. The major problem I have with
the patch is that it affects *all* locks, including system-internal
lock attempts that the user is probably not even aware of much less
able to control. It's like giving someone a poorly-aligned shotgun
when what they need is a rifle --- they'll end up putting holes in
a lot of other things besides what they intended.

I think that what we actually want is something that is narrowly
tailored to affect only row-level locks taken by SELECT FOR UPDATE,
and maybe one or two other places that (a) people can make specific
use-cases for, and (b) we can be certain are only invoked by user
commands and never indirectly from behind-the-scenes system operations.

The reason for proposing syntax rather than a GUC variable is the same
one of control. If you set a GUC variable then it will be hard to
prevent it from breaking operations other than the one you thought you
intended. (Example: you think you are only causing your SELECT FOR
UPDATE to error out, but what about ones done behind the scenes for
foreign key checks?) GUC variables are good for stuff that tends to
apply application-wide, which is why I thought regex_flavor wasn't too
dangerous, but they're terrible for functions that you want to apply to
only certain specific operations. And I can't imagine an app where that
wouldn't be true for NO WAIT.

Well, they have statement_timeout to prevent a command from taking too
long, so that obviously isn't the usage case for NO WAIT. The problem I
see for statement_timeout emulating NO WAIT is that on a lightly loaded
machine, the usual query time is different from a loaded machine, so
guessing a number seems difficult. Saying "Oh, the query is taking
longer than X seconds, I must be waiting on a lock" is prone to failure.
And if you get a faster machine or have an app that runs on machines of
different speeds, it doesn't work either.

One idea would be to allow the NOWAIT take a duration like
statement_timeout so you could say I only want to wait a maximum of X ms
before failing.

However, if the usage case for NOWAIT is for an applicaiton to return a
string saying "Record Locked", a GUC variable will not work and we have
to be fine-grained about it as you suggest.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Hans-J���rgen Sch���nig wrote:

Tom Lane wrote:

=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres@cybertec.at> writes:

The problem with adding NO WAIT to specific commands is that is
inheritly unflexible. I think this is why the community has agreed on
implementing it based on GUC.

I recall no such agreement ... when was this exactly? In any case
Bruce's recent complaints about regex_flavor have altered my opinions
about GUC variables a bit. They are bigger safety risks than they look,
especially ones that change semantics and are intended to be modified on
the fly.

I thought there was an agreement because the GUC version is on the TODO
list. Anyway ...

There was, but we have seen some concerns about GUC controlling too much
since we added it, I think.

Do you think it would help to reduce the GUCs flexibility by reducing
the lock levels a user is allowed to define?

I will vote against the patch no matter what, but I agree that it would
be less dangerous if it were confined to only apply to a limited set of
lock types.

regards, tom lane

Tom,

I think we should compromise.
We can restrict it to locks which are high enough or higher to make
SELECT FOR UPDATE work.
Of course it would have been nice to have full flexibility but I think
we can have almost the same benefit for lower risk.
How about it? Tom, Bruce - which types of locks do we allow?
I will change the patch then.
Maybe this is the best solution.

Well, you already allow the user to control the level of locks he wants
to timeout on, so it is merely an issue of setting the default for your
second GUC.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Larry Rosenman wrote:
-- Start of PGP signed section.

--On Wednesday, February 18, 2004 13:56:14 -0500 Bruce Momjian
<pgman@candle.pha.pa.us> wrote:

Tom Lane wrote:

The question is whether we should have a GUC variable to control no
waiting on locks or add NO WAIT to specific SQL commands.

Does anyone want to vote _against_ the GUC idea for nowait locking. (We
already have two voting for such a variable.)

If there is no one except Tom, we can continue.

I'm with Tom. Playing with *ALL* locks is just asking for TROUBLE.

OK, seems folks want the NO WAIT to apply only to the requested lock,
and want syntax to show that.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

TODO updated:

< * Add GUC variable to prevent waiting on locks

* Add NO WAIT option to various SQL commands

---------------------------------------------------------------------------

Barry Lind wrote:

I agree with Tom here. I have used the Oracle NOWAIT feature in the
past and think it is a great feature IMHO. But when you need to use it,
you want it to apply very specifically to a single statement. Using a
sledge hammer when you need a tweezers isn't the right way to go.

thanks,
--Barry

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

The question is whether we should have a GUC variable to control no
waiting on locks or add NO WAIT to specific SQL commands.

That's only a minor part of the issue. The major problem I have with
the patch is that it affects *all* locks, including system-internal
lock attempts that the user is probably not even aware of much less
able to control. It's like giving someone a poorly-aligned shotgun
when what they need is a rifle --- they'll end up putting holes in
a lot of other things besides what they intended.

I think that what we actually want is something that is narrowly
tailored to affect only row-level locks taken by SELECT FOR UPDATE,
and maybe one or two other places that (a) people can make specific
use-cases for, and (b) we can be certain are only invoked by user
commands and never indirectly from behind-the-scenes system operations.

The reason for proposing syntax rather than a GUC variable is the same
one of control. If you set a GUC variable then it will be hard to
prevent it from breaking operations other than the one you thought you
intended. (Example: you think you are only causing your SELECT FOR
UPDATE to error out, but what about ones done behind the scenes for
foreign key checks?) GUC variables are good for stuff that tends to
apply application-wide, which is why I thought regex_flavor wasn't too
dangerous, but they're terrible for functions that you want to apply to
only certain specific operations. And I can't imagine an app where that
wouldn't be true for NO WAIT.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073