Re: feature request - update nowait

[Eduardo Piombino <drakorg@xxxxxxxxx>]

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@xxxxxxxxx> wrote:

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