> > client 2 can carry on processing now,
> > and its SELECT * FROM work WHERE alloc IS NULL LIMIT 1 FOR UPDATE;
> > **returns no rows** despite the fact the second row matches.
>
> This is annoying but I do not think it is readily fixable, because the
> LIMIT filter acts before the FOR UPDATE processing. When FOR UPDATE
> rejects the first row because it's been changed by the previous updater,
> that's all she wrote --- the LIMIT's not gonna produce more rows.
At least there is a workaround for this:
Use a cursor without (or with a high eg 10000) LIMIT and stop fetching after
the 1000 wanted rows :-)
Andreas