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.