Re: [PATCHES] NO WAIT ...

hi everyone ...

i have attached a patch implementing NO WAIT with the help of a GUC
variable.
documentation should be included as well.
it works nicely for me.

test=# begin;
BEGIN
test=# show wait_for_locks;
wait_for_locks
----------------
row share
(1 row)

test=# lock table x in exclusive mode;
LOCK TABLE
test=# commit;
COMMIT
test=# begin;
BEGIN
test=# -- somebody else has locked the table ...
test=# lock table x in exclusive mode;
ERROR: LockAcquire failed

[hs(at)fedora pgsql]$ difforig > nowait.patch
./doc/src/sgml/runtime.sgml
./src/backend/storage/lmgr/lock.c
./src/backend/utils/misc/guc.c
./src/backend/utils/misc/postgresql.conf.sample
./src/bin/psql/tab-complete.c
./src/include/storage/lock.h
./src/include/utils/guc.h

i hope this patch is ok.
if there are any modifications needed just drop me a line.

maybe i will have some spare time to implement "SELECT FOR UPDATE
NOWAIT" (SQL version). maybe this extension would make sense as well
because many people porting from oracle to pg would like that.

cheers,

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

=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres(at)cybertec(dot)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(at)cybertec(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)cybertec(dot)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(at)postgresql(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)cybertec(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)cybertec(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)cybertec(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)lerctr(dot)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 <pgman(at)candle(dot)pha(dot)pa(dot)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

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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Hans-Jrgen Schnig wrote:
> Tom Lane wrote:
> > =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres(at)cybertec(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)postgresql(dot)org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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 absolutely agree to that.

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

I would gereralize this to user table row level and explicit lock table.
There is no need to force a separate SELECT FOR UPDATE in front of every
UPDATE or DELETE attempt in order to achieve the desired nolock behaviour.

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

This all needs a lot more detail than it has so far. If one tries to
UPDATE a row with NOLOCK, and a trigger fired during this would block on
the attempt to create a temp table, I think the resulting transaction
abort would rather be reported as a bug to us, then accepted as a nice
feature.

If there is a call for vote, I vote against.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

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

Once I have written patches for 7.3 to implement this feature for LOCK
statement. For example:

test=# LOCK TABLE sales NO WAIT;
ERROR: Cannot aquire relation lock

If there's enough interest, I will modify and submit it for 7.5.
--
Tatsuo Ishii

Tatsuo Ishii 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.
>
>
> Once I have written patches for 7.3 to implement this feature for LOCK
> statement. For example:
>
> test=# LOCK TABLE sales NO WAIT;
> ERROR: Cannot aquire relation lock
>
> If there's enough interest, I will modify and submit it for 7.5.
> --
> Tatsuo Ishii
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

That would be great.
Many people are asking for that.
Maybe I have time to implement that for SELECT FOR UPDATE.

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

> Tatsuo Ishii 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.
> >
> >
> > Once I have written patches for 7.3 to implement this feature for LOCK
> > statement. For example:
> >
> > test=# LOCK TABLE sales NO WAIT;
> > ERROR: Cannot aquire relation lock
> >
> > If there's enough interest, I will modify and submit it for 7.5.
> > --
> > Tatsuo Ishii
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 8: explain analyze is your friend
>
>
> That would be great.
> Many people are asking for that.
> Maybe I have time to implement that for SELECT FOR UPDATE.

Here it is(against 7.3.3).
--
Tatsuo Ishii

LOCK TABLE table NO WAIT is OK for 7.5? If ok, I will make patches
against current with some docs changes.
--
Tatsuo Ishii

> > Tatsuo Ishii 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.
> > >
> > >
> > > Once I have written patches for 7.3 to implement this feature for LOCK
> > > statement. For example:
> > >
> > > test=# LOCK TABLE sales NO WAIT;
> > > ERROR: Cannot aquire relation lock
> > >
> > > If there's enough interest, I will modify and submit it for 7.5.
> > > --
> > > Tatsuo Ishii
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 8: explain analyze is your friend
> >
> >
> > That would be great.
> > Many people are asking for that.
> > Maybe I have time to implement that for SELECT FOR UPDATE.
>
> Here it is(against 7.3.3).
> --
> Tatsuo Ishii

Yes, I think it looks good.

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

Tatsuo Ishii wrote:
> LOCK TABLE table NO WAIT is OK for 7.5? If ok, I will make patches
> against current with some docs changes.
> --
> Tatsuo Ishii
>
> > > Tatsuo Ishii 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.
> > > >
> > > >
> > > > Once I have written patches for 7.3 to implement this feature for LOCK
> > > > statement. For example:
> > > >
> > > > test=# LOCK TABLE sales NO WAIT;
> > > > ERROR: Cannot aquire relation lock
> > > >
> > > > If there's enough interest, I will modify and submit it for 7.5.
> > > > --
> > > > Tatsuo Ishii
> > > >
> > > > ---------------------------(end of broadcast)---------------------------
> > > > TIP 8: explain analyze is your friend
> > >
> > >
> > > That would be great.
> > > Many people are asking for that.
> > > Maybe I have time to implement that for SELECT FOR UPDATE.
> >
> > Here it is(against 7.3.3).
> > --
> > Tatsuo Ishii
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>

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

Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp> writes:
> LOCK TABLE table NO WAIT is OK for 7.5? If ok, I will make patches
> against current with some docs changes.

Dept of minor gripes: can we do this without turning "NO" into a
keyword? Even as a nonreserved word, I think that would be annoying.
"no" is a common abbreviation for "number" so I think it's likely to
get used as a column name.

If Oracle spells it "NOWAIT" then I'd be much happier with that...

regards, tom lane

If "NOWAIT" is the choice, I could live with it. If there's no
objection, I will go with "NOWAIT", not "NO WAIT".
--
Tatsuo Ishii

> Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp> writes:
> > LOCK TABLE table NO WAIT is OK for 7.5? If ok, I will make patches
> > against current with some docs changes.
>
> Dept of minor gripes: can we do this without turning "NO" into a
> keyword? Even as a nonreserved word, I think that would be annoying.
> "no" is a common abbreviation for "number" so I think it's likely to
> get used as a column name.
>
> If Oracle spells it "NOWAIT" then I'd be much happier with that...
>
> regards, tom lane
>

> If "NOWAIT" is the choice, I could live with it. If there's no
> objection, I will go with "NOWAIT", not "NO WAIT".

How about "WITHOUT WAIT", which is like many of our other commands?

Chris

Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> writes:
>> If "NOWAIT" is the choice, I could live with it. If there's no
>> objection, I will go with "NOWAIT", not "NO WAIT".

> How about "WITHOUT WAIT", which is like many of our other commands?

The first question in my mind is "exactly how does Oracle spell this?"
Let's not go inventing compatible-with-no-one syntax if we don't have to.

regards, tom lane

Oracle uses "NOWAIT" so we should go for that one.

Regards,

Hans

Tatsuo Ishii wrote:
> If "NOWAIT" is the choice, I could live with it. If there's no
> objection, I will go with "NOWAIT", not "NO WAIT".
> --
> Tatsuo Ishii
>
>
>>Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp> writes:
>>
>>>LOCK TABLE table NO WAIT is OK for 7.5? If ok, I will make patches
>>>against current with some docs changes.
>>
>>Dept of minor gripes: can we do this without turning "NO" into a
>>keyword? Even as a nonreserved word, I think that would be annoying.
>>"no" is a common abbreviation for "number" so I think it's likely to
>>get used as a column name.
>>
>>If Oracle spells it "NOWAIT" then I'd be much happier with that...
>>
>> regards, tom lane
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.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

It seems "NOWAIT" is the winner...
--
Tatsuo Ishii

> Oracle uses "NOWAIT" so we should go for that one.
>
> Regards,
>
> Hans
>
>
>
> Tatsuo Ishii wrote:
> > If "NOWAIT" is the choice, I could live with it. If there's no
> > objection, I will go with "NOWAIT", not "NO WAIT".
> > --
> > Tatsuo Ishii
> >
> >
> >>Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp> writes:
> >>
> >>>LOCK TABLE table NO WAIT is OK for 7.5? If ok, I will make patches
> >>>against current with some docs changes.
> >>
> >>Dept of minor gripes: can we do this without turning "NO" into a
> >>keyword? Even as a nonreserved word, I think that would be annoying.
> >>"no" is a common abbreviation for "number" so I think it's likely to
> >>get used as a column name.
> >>
> >>If Oracle spells it "NOWAIT" then I'd be much happier with that...
> >>
> >> regards, tom lane
> >>
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>

Here is the patch I promised (against current). Regression tests all
passed. One thing I have not checked is the doc(lock.sgml). For some
reason I failed to install docbook V4.2 (I have working docbook V3.1
though), and I couldn't test the correctness of the file. Also, it
would be nice if some one checks my English grammer:-)
--
Tatsuo Ishii

> It seems "NOWAIT" is the winner...
> --
> Tatsuo Ishii
>
> > Oracle uses "NOWAIT" so we should go for that one.
> >
> > Regards,
> >
> > Hans
> >
> >
> >
> > Tatsuo Ishii wrote:
> > > If "NOWAIT" is the choice, I could live with it. If there's no
> > > objection, I will go with "NOWAIT", not "NO WAIT".
> > > --
> > > Tatsuo Ishii
> > >
> > >
> > >>Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp> writes:
> > >>
> > >>>LOCK TABLE table NO WAIT is OK for 7.5? If ok, I will make patches
> > >>>against current with some docs changes.
> > >>
> > >>Dept of minor gripes: can we do this without turning "NO" into a
> > >>keyword? Even as a nonreserved word, I think that would be annoying.
> > >>"no" is a common abbreviation for "number" so I think it's likely to
> > >>get used as a column name.
> > >>
> > >>If Oracle spells it "NOWAIT" then I'd be much happier with that...
> > >>
> > >> regards, tom lane
> > >>
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 6: Have you searched our list archives?
> > >
> > > http://archives.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
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faqs/FAQ.html
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp> writes:
> Here is the patch I promised (against current).

This is missing the necessary adjustments in backend/nodes/ (copy and
equal funcs). Also the NOWAIT keyword must be added to the list of
nonreserved keywords near the bottom of gram.y.

regards, tom lane

> Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp> writes:
> > Here is the patch I promised (against current).
>
> This is missing the necessary adjustments in backend/nodes/ (copy and
> equal funcs). Also the NOWAIT keyword must be added to the list of
> nonreserved keywords near the bottom of gram.y.

Thanks for the review. I'll work on this.
--
Tatsuo Ishii

> > This is missing the necessary adjustments in backend/nodes/ (copy and
> > equal funcs). Also the NOWAIT keyword must be added to the list of
> > nonreserved keywords near the bottom of gram.y.
>
> Thanks for the review. I'll work on this.

Here is the revised patch.
--
Tatsuo Ishii

> > > This is missing the necessary adjustments in backend/nodes/ (copy and
> > > equal funcs). Also the NOWAIT keyword must be added to the list of
> > > nonreserved keywords near the bottom of gram.y.
> >
> > Thanks for the review. I'll work on this.

Patches committed.
--
Tatsuo Ishii