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

On Wed, May 14, 2014 at 8:26 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Sun, May 11, 2014 at 12:47 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> >> On 11 May 2014 11:18, Andres Freund <andres(at)2ndquadrant(dot)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