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

Alvaro Herrera wrote:
>srb(at)cuci(dot)nl (Stephen R. van den Berg) escribi?:
>> Tom Lane wrote:
>> >srb(at)cuci(dot)nl (Stephen R. van den Berg) writes:
>> 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

>In this case, use an explicit transaction "just to be sure", and if you
>find that more tuples were deleted that should have been, rollback.

Ok, granted. This will work in the interactive case. This will not
work in the case of a bug in an applicationprogram, unless you
enclose every delete and update in a transaction which is checked
for tuples modified (checking the number of tuples modified is not
possible in many application languages).

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

>It's not consistent, because it can delete/update different rows, given
>the same dataset and the same query. It's not logical if you look from
>the user's point of view. It may be internally, but that's another

IMHO it is logical, because if it's documented to be a random tuple,
then it should be a random tuple. There's no user that can complain
about that (unless he/she does not consult the manual).

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

>I suspect you'd have to tweak the planner...

I may be mistaken, but I believe to have noticed a small changelog
entry somewhere before 7.2.1 which read that the planner now
considers LIMIT when optimising the query.

>> MySQL documents that the actual record being deleted is "random".

>I think this feature would be much more useful if you could use ORDER BY
>and an expression on the LIMIT clause instead of just a number. And the
>corresponding OFFSET clause should be added as well. So one can say
>"drop the three worst customers" or "change to 'gold' the status of the
>customers with total > $10000 last week". I don't know if this can be
>done on one query with the current featureset.

As far as I could determine this appears to be rather easy to
add if so desired (the engine supports it already).
--
Sincerely, srb(at)cuci(dot)nl
Stephen R. van den Berg (AKA BuGless).

Do more than anyone expects, and pretty soon everyone will expect more.