Re: allow LIMIT in UPDATE and DELETE

On Fri, 2006-05-19 at 15:51, Tom Lane wrote:
> Csaba Nagy <nagy@ecircle-ag.com> writes:
> > Currently the LIMIT clause is not allowed in UPDATE or DELETE
> > statements. I wonder how easy it would be to allow it, and what people
> > think about it ?
>
> This has been proposed before, and rejected before, and the arguments
> against are just as strong as they were before.  See the archives.

Tom, I guess you refer to the following:

http://archives.postgresql.org/pgsql-patches/2002-09/msg00256.php

Well, let me fight your arguments.

The first one here is not the first in your mail, but the most important
I guess:

"Just because MySQL is willing to implement nonstandard bad ideas
doesn't
mean we are.  In any case the idea that this might provide some amount
of compatibility is illusory: the odds are good that we'd delete or
update a different tuple than they do, because of implementation
differences.  An application that actually depends on MySQL's behavior
would surely be broken."

Well, first of all, you're not competing here with MySQL in this case, but with Oracle. Our application does this using
Oracle'sROWNUM trick and it works perfectly fine. Now I guess you think Oracle's ROWNUM is also stupid in this case,
butit certainly helps us writing cleaner SQL, and a missing postgres alternative which is easy to use won't help you in
attractingOracle users. 

Regarding the compatibility, I do not expect that the rows deleted/updated will be the same as on Oracle or other DB, I
simplyexpect that only a limited number of rows will be processed at a time... the rest will be processed in the next
runs,on all DBs... Is this so outrageous ? 

Regarding non-standard, LIMIT is non-standard in SELECTS as well, and I don't see how much more non-standard would it
bein DELETE/UPDATE. 

Regarding "bad ideas", "depending on MySQLs behavior", I think you're simply overreacting here... for me LIMIT in
DELETE/UPDATEwould have saved a few days of compatibility work on our application WITH ORACLE, not MySQL, and a few
contrivedqueries. 

"And how exactly do you control *which* tuple(s) get deleted or updated,
if the WHERE clause selects more than the limit?"

I DO NOT CARE about which rows are deleted. The fact that it is nondeterministic can be very clearly specified in the
documentationif you think it is such a bad thing, but nondeterministic is perfectly fine sometimes. There are lots of
nondeterminismsin the data base world, starting with the ordering of selects if you don't use order by, then why don't
weforce everybody using order by ? Why don't you force to use order by on a select with limit ? Why there it is enough
tosay it in the docs that it WILL BE NON_DETERMINISTIC ? 

"Then use ctid."

For the problem at hand in your post it is a good solution, except that it will cause a full table scan cause I guess
fewpeople have indexes on ctid. Or you have to write your queries really contrived, by duplicating most of your query
conditionsso that it can use some indexes. I'm not sure if you'll get away without at least 2 full table scans if using
ctidand no indexes, one for the subquery and one for the delete itself... not to mention the need for something like a
HashAggregateon the subquery results... all this is speculation, but for sure you'll spend 10x the time for optimizing
thesubquery then you would writing a simple DELETE with LIMIT. 

"Have you got any evidence that there's a meaningful speedup?"

No, but from speculating the query plans I see it would mean up to 50% speedup for my use case.

"We have a zero-tolerance policy on yacc warnings."
"This just seems like a really bad idea ..."

You simply have prejudices against this feature. If you wouldn't be so against it I'm sure the problems could be
solved.You're one of the most influent person on where postgres is going, and it's a pity when you're so against
somethingyou don't like based on gut feelings... 

Cheers,
Csaba.