Re: DELETE with LIMIT (or my first hack)

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Andrew Dunstan <andrew(at)dunslane(dot)net>
Cc: 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 05:05:12
Message-ID: AANLkTin3vaOdVDrHR0x=TQD3aH_dFWUL89ocgKxQC4kh@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Marti Raudsepp 2010-11-30 05:50:55 Re: DELETE with LIMIT (or my first hack)
Previous Message Robert Haas 2010-11-30 04:57:25 crash-safe visibility map, take three