feature request - update nowait

On Thu, Sep 8, 2011 at 10:01 AM, Eduardo Piombino <drakorg@gmail.com> wrote:

Hi, would it be possible to implement a nowait modifier to the update
statement in order to tell it not to wait and raise an error -just like a
select for update nowait would-, instead of defaulting to waiting forever
until the lock becomes available?

The lack of such a modifier nowadays forces me to do a select for update
before every update on which I need the fastest response possible, and it
would be great if it could be integrated into the command itself.

Just an idea.

Sounds like a good idea.

NOWAIT is outside the SQL Standard anyway. Oracle doesn't support it
either, but that's their loss as well, I think.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On Thu, Sep 8, 2011 at 4:01 AM, Eduardo Piombino <drakorg@gmail.com> wrote:

Hi, would it be possible to implement a nowait modifier to the update
statement in order to tell it not to wait and raise an error -just like a
select for update nowait would-, instead of defaulting to waiting forever
until the lock becomes available?

The lack of such a modifier nowadays forces me to do a select for update
before every update on which I need the fastest response possible, and it
would be great if it could be integrated into the command itself.

Just an idea.

+1

note you may be able to emulate this by sneaking a nolock into the
update statement in a highly circuitous fashion with something like:
update foo set v = 2 from (select 1 from foo where id = 1 for update
nowait) q where id = 1;

merlin

Try a command LOCK NOWAIT

2011/9/8, Eduardo Piombino <drakorg@gmail.com>:

Hi, would it be possible to implement a *nowait *modifier to the
*update*statement in order to tell it not to wait and raise an error
-just like a
select for update nowait would-, instead of defaulting to waiting forever
until the lock becomes available?

The lack of such a modifier nowadays forces me to do a select for update
before every update on which I need the fastest response possible, and it
would be great if it could be integrated into the command itself.

Just an idea.

Best regards,
Eduardo.

--
------------
pasman

Nice.
Much more maintainable IMO and quite close to what I was looking for.
Thanks a lot for the suggestion, I will definitely try it/implement it right
away.
Still has some redundancy compared to an hypothetical nowait modifier but I
think it's the very best alternative so far.

Eduardo

On Thu, Sep 8, 2011 at 1:22 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

I'm sorry, isn't it meant for table locks?
I was talking about row level locking.

Eduardo

2011/9/8 pasman pasmański <pasman.p@gmail.com>

On Thu, Sep 8, 2011 at 1:22 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Thu, Sep 8, 2011 at 4:01 AM, Eduardo Piombino <drakorg@gmail.com>
wrote:

Hi, would it be possible to implement a nowait modifier to the update
statement in order to tell it not to wait and raise an error -just like
a
select for update nowait would-, instead of defaulting to waiting
forever
until the lock becomes available?

The lack of such a modifier nowadays forces me to do a select for update
before every update on which I need the fastest response possible, and
it
would be great if it could be integrated into the command itself.

Just an idea.

+1

note you may be able to emulate this by sneaking a nolock into the
update statement in a highly circuitous fashion with something like:
update foo set v = 2 from (select 1 from foo where id = 1 for update
nowait) q where id = 1;

On Thu, Sep 8, 2011 at 3:33 PM, Eduardo Piombino <drakorg@gmail.com> wrote:

Nice.
Much more maintainable IMO and quite close to what I was looking for.
Thanks a lot for the suggestion, I will definitely try it/implement it right
away.
Still has some redundancy compared to an hypothetical nowait modifier but I
think it's the very best alternative so far.

Eduardo

Thanks -- in hindsight though I think it's better to write it this way:

explain update foo set v = 2 from
(
select id from foo where id = 1 for update nowait
) q where q.id = foo.id;

another interesting way to write it that is 9.1 only is like this:
with x as
(
select id from foo where id = 1 for update nowait
) update foo set v = 2 where exists (select 1 from x where x.id = foo.id);

which gives approximately the same plan.

merlin

On Thu, Sep 8, 2011 at 4:32 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Thu, Sep 8, 2011 at 1:22 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

On Thu, Sep 8, 2011 at 4:01 AM, Eduardo Piombino <drakorg@gmail.com>
wrote:

Hi, would it be possible to implement a nowait modifier to the update
statement in order to tell it not to wait and raise an error -just like
a
select for update nowait would-, instead of defaulting to waiting
forever
until the lock becomes available?

The lack of such a modifier nowadays forces me to do a select for update
before every update on which I need the fastest response possible, and
it
would be great if it could be integrated into the command itself.

Just an idea.

+1

note you may be able to emulate this by sneaking a nolock into the
update statement in a highly circuitous fashion with something like:
update foo set v = 2 from (select 1 from foo where id = 1 for update
nowait) q where id = 1;

On Thu, Sep 8, 2011 at 3:33 PM, Eduardo Piombino <drakorg@gmail.com> wrote:

Nice.
Much more maintainable IMO and quite close to what I was looking for.
Thanks a lot for the suggestion, I will definitely try it/implement it right
away.
Still has some redundancy compared to an hypothetical nowait modifier but I
think it's the very best alternative so far.

Eduardo

Thanks -- in hindsight though I think it's better to write it this way:

explain update foo set v = 2 from
(
 select id from foo where id = 1 for update nowait
) q where q.id = foo.id;

another interesting way to write it that is 9.1 only is like this:
with x as
(
 select id from foo where id = 1 for update nowait
) update foo set v = 2 where exists (select 1 from x where x.id = foo.id);

which gives approximately the same plan.

...I spoke to soon! either use the CTE method, or write it like this:
update foo set v = 2 where id in (select id from foo where id = 1 for update);

sorry for the noise :-). (update...using can be tricky to get right)

merlin

Don't worry ! I will surely try some different alternatives anyways, but the
idea is the same, include somehow a select for update in the same sentence
as the update. I'm most inclined to the last one you suggested, maybe with
an equals instead of an in (I'd rather always instinctively use an equals
over an in for a single record match, whatever the context is).

Considering of course it is a pk. If multiple rows should be affected by the
update, well an in would then be way, but I don't think it will be the case
for me.

Thanks again!
Eduardo

PS: Please feel free to mail me directly if you happen to come up with a
better alternative too, so as not to bore the list to death, if that was the
case.

On Thu, Sep 8, 2011 at 6:39 PM, Merlin Moncure <mmoncure@gmail.com> wrote: