Re: Implementation of LIMIT on DELETE and UPDATE statements

On Mon, 23 Sep 2002, Yury Bokhoncovich wrote:

> Hello!
>
> On Sun, 22 Sep 2002, Bruce Momjian wrote:
>
> > I see no reason to add stuff to UPDATE/DELETE when a subquery does the
> > job just as well.  It just seems like bloat.
>
> That's looks funny but can be useful.
> Imagine typical usage of LIMIT/OFFSET: pagination of a web-output.
> Say, the output is fetched thru "select id,body from articles limit 10
> offset 20".
> Now, content-admin, surfing the content and looking to the page say 2,
> wanna drop all info on THAT page 2.
> Guess how it could ease the life for programmer?8)

I *really* hope noone (mis)uses limit/offset like that. Really. Without an
order by there's no guarantee that the rows will be in the same order two
statements in a row.  It's ugly but marginally ok for selects, doing the
same with statements that modify data is frightening. Even with an order
by, concurrent modifications will make that iffy in non-serializable
transactions. If the select and delete are in separate transactions,
oh boy.

The delete a random matching row seems okay if that's really what the user
wants (if only because it makes deleting duplicates easier).