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

En Sat, 21 Sep 2002 19:14:20 +0200
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:
> >> 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?
>
> 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).

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.
As is, this is a dangerous feature because it's not predictable.

> >This just seems like a really bad idea ...
>
> 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
story.

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

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

--
Alvaro Herrera (<alvherre[a]atentus.com>)
"Lo esencial es invisible para los ojos" (A. de Saint Exúpery)