Re: Support for Limit in Update, Insert...

Well, I do have a use case for it.

Context:

We have data coming in from web requests, which must be fast, so we just
insert them in temporary tables without any verification. Then they are
periodically processed by a background task, but even that one will
process just a chunk at a time to avoid long running queries and the
possible socket timeouts bundled with them. Now for identifying a chunk
we use a "chunkid" field in those temporary tables, which is initially
null. When a chunk is selected for processing, we update the chunkid
field with the next value of a sequence, and then all further processing
has a where clause which selects only records with that chunkid.

Use case:

To set the chunkid only for 1000 rows, we actually don't care which
ones. The idea is to uniquely mark a chunk of data, we really don't care
which rows are selected in each chunk, they will be processed all
eventually.

Of course right now we do it by something similar with what you
proposed, using a subselect with a limit clause, I wonder if a simple
update with limit could be faster ?

Cheers,
Csaba.

On Fri, 2005-09-09 at 04:49, Tom Lane wrote:
> Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
> > This has been discussed before, and rejected. Please see the archives.
>
> For SELECT, both LIMIT and OFFSET are only well-defined in the presence
> of an ORDER BY clause. (One could argue that we should reject them when
> no ORDER BY, but given that the database isn't getting changed as a side
> effect, that's probably too anal-retentive. When the database *is*
> going to be changed, however, I for one like well-defined results.)
>
> If this proposal included adding an ORDER BY to UPDATE/DELETE, then it
> would at least be logically consistent. I have not seen the use-case
> for it though. In any case you can usually get the equivalent result
> with something like
>
> UPDATE foo SET ...
> WHERE pkey IN (SELECT pkey FROM foo ORDER BY ... LIMIT ...);
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster