Re: Implementation of LIMIT on DELETE and UPDATE statements

On Mon, 23 Sep 2002, Stephen R. van den Berg wrote:

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

I'm not going to get into the issue of whether it's good or not really,
but have you tested your patch with multiple updates? ISTM that it's
likely to have the same problem that select for update does when combined
with limit (which is that it may return less rows than the limit if
a row is modified such that it no longer meets an attached where clause)
I noticed this recently due to trying to using limit with fk statements.
I haven't actually put in the patch to try it however.

Example (on reasonably recent development 7.3):
create table test (a int);
insert into test values (1);
insert into test values (2);
insert into test values (3);

T1: begin
T2: begin
T1: update test set a=4 where a=1;
T2: select * from test where a<3 for update limit 1;
[this blocks]
T1: commit;
[T2 now returns 0 rows]