Thread: 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

> 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