Re: BUG #17141: SELECT LIMIT WITH TIES FOR UPDATE SKIP LOCKED returns wrong number of rows

On Wed, Aug 11, 2021 at 3:07 PM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
>
> On 2021-Aug-11, PG Bug reporting form wrote:
>
> > BEGIN;
> > SELECT * FROM queue
> > ORDER BY task DESC
> > FETCH FIRST 1 ROWS WITH TIES
> > FOR UPDATE SKIP LOCKED;
> > /* Some work to be done here */
> > COMMIT;
> >
> > select version();
> > PostgreSQL 13.3, compiled by Visual C++ build 1914, 64-bit
> >
> > Expected result Worker 1: (580), (580), Actual result Worker 1: (580), (580)
> > Expected result Worker 2: (480), (480), Actual result Worker 2: (480)
>
> Ouch, we already saw this actually:
> https://postgr.es/m/16676-fd62c3c835880da6@postgresql.org
> The problem is that the first worker locks the first (480) row (even
> though it does not return it), so the second worker skips it due to SKIP
> LOCKED.
>
> I have this on my list of things to look at, but it's not at the top
> yet sadly ...

I have written a patch[1] to detect this situation and error out
instead of silently not returning some of the rows it ostensibly
should have. I'm not convinced it's the *right* solution, as we may
want to allow the existing types of SELECT that currently trigger this
to run instead, but it is at least a solution and the start of a
discussion.

Best,

David

[1] https://www.postgresql.org/message-id/CAOxo6XLPccCKru3xPMaYDpa%2BAXyPeWFs%2BSskrrL%2BHKwDjJnLhg%40mail.gmail.com