I tried to read the other thread to understand the underlying problem of
an extra row being locked, but couldn't find any good explanation.
I see a lot of thought being put into how this issue can be worked
around, but very little discussion on if this behaviour is correct or
not. Without having thought about it much deeper, this seems to only be
a problem with the "WITH TIES" clause which provokes this extra row
being locked. Perhaps that is where the problem should be attacked.
On 2021-08-13 23:46, David Christensen wrote:
> On Wed, Aug 11, 2021 at 3:07 PM Alvaro Herrera <alvherre@alvh.no-ip.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
>
