Re: feature request - update nowait

On Thu, Sep 8, 2011 at 4:32 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>> On Thu, Sep 8, 2011 at 1:22 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>>>
>>> On Thu, Sep 8, 2011 at 4:01 AM, Eduardo Piombino <drakorg(at)gmail(dot)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(at)gmail(dot)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