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) escribi:
>>> Incidentally, using a SELECT without an ORDER BY but with a LIMIT is
>>> documented to give unpredictable results, yet users are expected cope with
>>> this fact, but are expected to have problems with a similar fact in
>>> an UPDATE or DELETE statement?

>Well, IMHO there's a big difference in documented unpredictable output
>from a documented-unpredictable query, as opposed to
>documented-unpredictable changes in the database state. There is not
>a lot of use for the latter AFAICS.

There is, in the case of duplicate entries where you want to delete just
one of them.

>I'd have more confidence in the usefulness of the idea if it included
>ORDER BY to make the LIMIT predictable. But before you run off and
>implement that: does MySQL support such a thing? If not, the argument
>of improving compatibility still doesn't hold any water...

MySQL supports ORDER BY in conjunction with LIMIT on a DELETE, on an
UPDATE it just seems to support LIMIT, no ORDER BY.

However, I do concede that a subselect in most cases is able to deal
with this problem in standard SQL.
The only actual improvement in readability/portability would be the
case of the multiple identical tuples of which you only want to delete
or update a few.

As far as bloat is concerned, the engine supports it already, it basically
boils down to a brief yacc-syntax extension. It does allow for a more
orthogonal syntax on SELECT/DELETE/UPDATE, which is a plus, I'd say.

Anyway, since ctid's solve my problem, I'm not particularly keen on
getting the LIMIT support on UPDATE/DELETE anymore.
I still think that the ctid solution is ugly and non-portable.
But, that's a value-judgement I'm not qualified to make about PostgeSQL.
Your call:
- Require a ctid non-standard solution.
- Or allow for a non-standard-yet-fully-orthogonal LIMIT implementation
(with ORDER BY support if you like).
Whatever you pick, I'm happy with; I'll provide patches for
version two if so desired.
--
Sincerely, srb(at)cuci(dot)nl
Stephen R. van den Berg (AKA BuGless).

"To err is human, to debug ... divine."