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

Stephen R. van den Berg wrote:
>Tom Lane wrote:
>>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.

Funny, actually.
I just changed my application from doing a:

    DELETE FROM a WHERE b=3 LIMIT 1;
to:
    DELETE FROM a WHERE ctid=(SELECT ctid FROM a WHERE b=3 LIMIT 1);

It works.
Actually, it works *identically*, i.e. the actual row deleted is
just as randomly chosen as in the DELETE with LIMIT case.
The first version looks cleaner to me (and better understandable) though.

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?
Somehow the argumentation is not conclusive.
--
Sincerely,                                                          srb@cuci.nl
           Stephen R. van den Berg (AKA BuGless).

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