Skip site navigation (1) Skip section navigation (2)

Re: DELETE with LIMIT (or my first hack)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Andrew Dunstan <andrew(at)dunslane(dot)net>, Jaime Casanova <jaime(at)2ndquadrant(dot)com>, Daniel Loureiro <loureirorg(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: DELETE with LIMIT (or my first hack)
Date: 2010-11-30 16:04:05
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
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.

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

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

I don't offhand see a solution other than integrating the responsibility
for limit-counting and sorting into ModifyTable itself, making it into
an unholy union of ModifyTable+Limit+MergeAppend (with the individual
inputs required to deliver sorted outputs separately).  That's
sufficiently ugly, and probably bad for performance in the normal case,
that I don't think it's going to be acceptable for such a marginal

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.

			regards, tom lane

In response to


pgsql-hackers by date

Next:From: Tom LaneDate: 2010-11-30 16:10:13
Subject: Re: crash-safe visibility map, take three
Previous:From: Heikki LinnakangasDate: 2010-11-30 16:02:05
Subject: Re: crash-safe visibility map, take three

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group