Alvaro Herrera wrote:
>srb@cuci.nl (Stephen R. van den Berg) escribi?:
>> Tom Lane wrote:
>> >srb@cuci.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@cuci.nl
Stephen R. van den Berg (AKA BuGless).
Do more than anyone expects, and pretty soon everyone will expect more.
