Re: DELETE with LIMIT (or my first hack)

On Mon, Nov 29, 2010 at 10:09 PM, Jaime Casanova <jaime(at)2ndquadrant(dot)com> wrote:
> On Mon, Nov 29, 2010 at 9:55 PM, Daniel Loureiro <loureirorg(at)gmail(dot)com> wrote:
>> good point. But when you use a LIMIT in a SELECT statement you WANT n RANDOM
>> tuples
>
> no. at least IMHO the only sensible way that LIMIT is usefull is with
> an ORDER BY clause with make the results very well defined...

That's not 100% true - it can sometimes be very useful when digging
through a database to grab 50 rows from a table just to get a feel for
what kind of stuff in there. Maybe it's stupid, but I find it handy.
But even granting the premise, that's an argument for making DELETE
support both ORDER BY and LIMIT, not for supporting neither of them.
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 think the executor already pretty much knows how to do this. The
planner might need some fiddling to hand over the correct
instructions, not sure. But this might not even be super hard, though
Daniel might want to pick something a little less ambitious for his
very first project, because debugging planner and executor problems is
not so easy.

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