Re: 9.5: UPDATE/DELETE .. ORDER BY .. LIMIT ..

On 11 May 2014 11:18, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2014-05-11 10:33:10 +0200, Simon Riggs wrote:
>> On 11 May 2014 07:37, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>>
>> > Tom Lane has explained these problems in a very clear manner
>> > in his below mail and shared his opinion about this feature as
>> > well.
>> > http://www.postgresql.org/message-id/26819.1291133045@sss.pgh.pa.us
>>
>> I don't have Tom's wonderfully articulate way of saying things, so
>> I'll say it my way:
>>
>> If you want to do this, you already can already write a query that has
>> the same effect. But supporting the syntax directly to execute a
>> statement with an undefinable outcome is a pretty bad idea, and worse
>> than that, there's a ton of useful things that we *do* want that would
>> be a much higher priority for work than this. If you support Postgres,
>> prioritise, please.
>
> I don't know. I'd find UPDATE/DELETE ORDER BY something rather
> useful.

Perhaps if an index exists to provide an ordering that makes it clear
what this means, then yes.

Forcing Rukh to implement ORDER BY when an index doesn't exist sounds
like useless work though, so if we were to accept that this statement
gives an ERROR in the absence of such an index, it would make sense
all round.

> It's required to avoid deadlocks in many scenarios and it's not
> that obvious how to write the queries in a correct manner.
> LIMIT would be a nice bonus for queues, especially if we can get SKIP
> LOCKED.

With SKIP LOCKED it begins to have some use. Thanks for the nudge.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services