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

Tom Lane wrote:
>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?

You don't. The idea is that the database deletes at most x items.
It's documented as such, it's supposed to work this way.

It *can* be used as a safeguard against catastrophic failure of
the (programmer or) application driving the database.
I.e. use "DELETE FROM tabley WHERE x=3 LIMIT 1;" will never delete more
than one item, even if the silly programmer used the wrong column (like
the condition should have been "z=3", because x=3 happens to match all
table entries).

>This just seems like a really bad idea ...

It's logical and consistent (it works as advertised) and doesn't cost
much implementation wise.

>> - 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.

Hmmm, I didn't know about ctid. It does seem to allow me to distinguish
values. It will require a SELECT followed by a DELETE or UPDATE though
AFAICS. But I agree that it seems to solve my problem.

>> - 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?

No. I just noted this as a sideeffect which is a result of me solving
the delete/update problem above.
The actual speedup depends on the query planner. I presume that
the planner is likely to spend less time optimising the query if it
knows in advance that it's going to need just one result row.

>> - 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.

MySQL documents that the actual record being deleted is "random".
Any application which expects and uses this feature in its documented
way would work equally well on PostgreSQL (and yes, the records deleted
might differ, but for all intents and purposes they are the same anyway,
if not, the WHERE clause is not specific enough).

>> 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.

I already assumed that, which is why I made note of the fact that they
are being generated due to my proposed patch. If my patch should be
accepted, I'm willing to eliminate the warnings. It's just that it
could be that to an experienced PostgreSQL hacker these warnings might
have been trivial to fix, whereas my familiarity with the PostgreSQL source
code currently is based on a 20 minute cursory reading of it while patching.

Thing is, I spent 4 weeks (off and on) trying to find an efficient workaround
for the delete-just-one-of-a-set-of-identical-records problem in PostgreSQL,
and as I finally got fed up with it, I unpacked the source and looked if
adding the LIMIT clause was difficult or not. It turned out it was not.

IMO the patch still has its merits; but if it's not accepted, I'm content
with a slightly elaborate and even more non-standard ctid hack.
--
Sincerely, srb(at)cuci(dot)nl
Stephen R. van den Berg (AKA BuGless).

"Sleep: A completely inadequate substitute for caffeine."