Re: DELETE with LIMIT (or my first hack)

Hi all,

The workaround recommended some time ago by Tom is:

DELETE FROM residents_of_athens WHERE ctid = any(array(SELECT ctid FROM
residents_of_athens ORDER BY ostracism_votes DESC LIMIT 1));

It is about as efficient as the requested feature would be, just uglier
to write down. I use it all the time when batch-deleting something large
(to avoid long running transactions and to not crash slony). It also
helps to vacuum frequently if you do that on large amount of data...

Cheers,
Csaba.

On Tue, 2010-11-30 at 00:05 -0500, Robert Haas wrote:
> On Mon, Nov 29, 2010 at 11:25 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> >
> >
> > On 11/29/2010 10:19 PM, Robert Haas wrote:
> >
> > For example, suppose we're trying to govern an ancient Greek
> > democracy:
> >
> > http://en.wikipedia.org/wiki/Ostracism
> >
> > DELETE FROM residents_of_athens ORDER BY ostracism_votes DESC LIMIT 1;
> >
> > I'm not sure this is a very good example. Assuming there isn't a tie, I'd do
> > it like this:
> >
> > DELETE FROM residents_of_athens
> > WHERE ostracism_votes >= 6000
> > and ostracism_votes =
> > (SELECT max(ostracism_votes)
> > FROM residents_of_athens);
>
> That might be a lot less efficient, though, and sometimes it's not OK
> to delete more than one record. Imagine, for example, wanting to
> dequeue the work item with the highest priority. Sure, you can use
> SELECT ... LIMIT to identify one and then DELETE it by some other key,
> but DELETE .. ORDER BY .. RETURNING .. LIMIT would be cool, and would
> let you do it with just one scan.
>
> > I can't say I'd be excited by this feature. In quite a few years of writing
> > SQL I don't recall ever wanting such a gadget.
>
> It's something I've wanted periodically, though not badly enough to do
> the work to make it happen.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>