Re: Implementation of LIMIT on DELETE and UPDATE statements (rel to 7.2.1)

srb(at)cuci(dot)nl (Stephen R. van den Berg) writes:
> The patch allows for a LIMIT clause on a DELETE or UPDATE statement.

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

This just seems like a really bad idea ...

> Why is this needed?

I find none of these arguments compelling.

> - I have a table which has several identical entries, and I want to
> delete or update just one of them (I don't care which one, obviously).
> And, no, I cannot use OIDS because they'd represent unwanted overhead
> (the table contains a lot of entries).

Then use ctid.

> - It allows you to speed up DELETE or UPDATE statements which are known
> in advance to match only one record by adding a LIMIT 1.

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

> - It makes migrations from MySQL to PostgreSQL easier (MySQL already
> supports LIMIT on DELETEs and UPDATEs).

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.

> When checking the patches, please pay attention to the three extra warnings
> the yacc file now generates.

We have a zero-tolerance policy on yacc warnings.

regards, tom lane