Re: DELETE with LIMIT (or my first hack)

On Tue, Nov 30, 2010 at 11:04 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Tue, Nov 30, 2010 at 10:04 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Unfortunately, the UPDATE
>>> case would be an order of magnitude harder (think inheritance trees
>>> where the children aren't all alike).
>
>> I don't understand why there's anything more to this than sticking a
>> Limit node either immediately above or immediately below the
>> ModifyTable node.
>
> 1. You need to support ORDER BY too, otherwise I *will* be on the
> warpath against this as a foot-gun with no redeeming social value.

Will you be wielding a Tom-ahawk?

> 2. So what you need is Sort underneath Limit underneath ModifyTable.
> Putting them above it would be quite the wrong semantics.

OK.

> 3. This doesn't work tremendously well for inheritance trees, where
> ModifyTable acts as sort of an implicit Append node.  You can't just
> funnel all the tuples through one Sort or Limit node because they aren't
> all the same rowtype.  (Limit might perhaps not care, but Sort will.)
> But you can't have a separate Sort/Limit for each table either, because
> that would give the wrong behavior.  Another problem with funneling all
> the rows through one Sort/Limit is that ModifyTable did need to know
> which table each row came from, so it can apply the modify to the right
> table.

Could you possibly have ModifyTable -> Limit -> MergeAppend?

> Or I guess you could try to persuade us that DELETE/UPDATE with ORDER BY
> or LIMIT doesn't need to support inherited target tables.  I wouldn't
> bet on that proposal flying either.

I've spent enough time worrying about the fact that tables with
inheritance children don't behave as nicely as those that don't to
have any interest in going in the other direction.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company