Re: 9.5: UPDATE/DELETE .. ORDER BY .. LIMIT .. - Mailing list pgsql-hackers

From Jeff Janes
Subject Re: 9.5: UPDATE/DELETE .. ORDER BY .. LIMIT ..
Date
Msg-id CAMkU=1zLG7e6-OA1LOcx4B0ahAz834P27Ov6PvR3nAhhbDHjNw@mail.gmail.com
Whole thread Raw
In response to Re: 9.5: UPDATE/DELETE .. ORDER BY .. LIMIT ..  (Robert Haas <robertmhaas@gmail.com>)
List pgsql-hackers
On Wed, May 14, 2014 at 8:26 AM, Robert Haas <robertmhaas@gmail.com> wrote:
On Sun, May 11, 2014 at 12:47 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Simon Riggs <simon@2ndQuadrant.com> writes:
>> On 11 May 2014 11:18, Andres Freund <andres@2ndquadrant.com> wrote:
>>> 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.
>
> The $64 question is whether we'd accept an implementation that fails
> if the target table has children (ie, is partitioned).

I'd say "no".  Partitioning is important, and we need to make it more
seamless and better-integrated, not add new warts.


I think the importance of partitioning argues the other way on this issue.  Where I most wanted a LIMIT clause on DELETE is where I was moving tuples from one partition to a different one in a transactional way using bite-size chunks that wouldn't choke the live system with locks or with IO.  

So the DELETE was always running against either a child by name, or against ONLY parent, not against the whole inheritance tree.  Not being able to do this on single partitions makes partitioning harder, not easier.

Sure, I can select the nth smallest ctid and then "WITH T AS (DELETE FROM ONLY foo WHERE ctid < :that RETURNING *) INSERT INTO bar SELECT * from T", but how annoying.

 
> That seems
> to me to not be up to the project's usual quality expectations, but
> maybe if there's enough demand for a partial solution we should do so.

I like this feature, but if I were searching for places where it makes
sense to loosen our project's usual quality expectations, this isn't
where I'd start.

In this case I'd much rather have half a loaf rather than none at all.  We wouldn't be adding warts to partitioning, but removing warts from the simpler case.

Cheers,

Jeff

pgsql-hackers by date:

Previous
From: Andres Freund
Date:
Subject: Re: Proposal for CSN based snapshots
Next
From: David G Johnston
Date:
Subject: Re: CTE that result in repeated sorting of the data