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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
