Re: feature request - update nowait

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

merlin