Re: allow LIMIT in UPDATE and DELETE

On Fri, 2006-05-19 at 15:51, Tom Lane wrote:
> Csaba Nagy <nagy(at)ecircle-ag(dot)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's ROWNUM trick and it works perfectly fine. Now I guess you think Oracle's ROWNUM is also stupid in this case, but it certainly helps us writing cleaner SQL, and a missing postgres alternative which is easy to use won't help you in attracting Oracle users.

Regarding the compatibility, I do not expect that the rows deleted/updated will be the same as on Oracle or other DB, I simply expect 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 be in DELETE/UPDATE.

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

"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 documentation if you think it is such a bad thing, but nondeterministic is perfectly fine sometimes. There are lots of nondeterminisms in the data base world, starting with the ordering of selects if you don't use order by, then why don't we force everybody using order by ? Why don't you force to use order by on a select with limit ? Why there it is enough to say 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 few people have indexes on ctid. Or you have to write your queries really contrived, by duplicating most of your query conditions so that it can use some indexes. I'm not sure if you'll get away without at least 2 full table scans if using ctid and no indexes, one for the subquery and one for the delete itself... not to mention the need for something like a HashAggregate on the subquery results... all this is speculation, but for sure you'll spend 10x the time for optimizing the subquery 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 something you don't like based on gut feelings...

Cheers,
Csaba.