From: Emil Iggland <emil@iggland.com>
To: David Christensen <david.christensen@crunchydata.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Cc: Surafel Temesgen <surafel3000@gmail.com>
Subject: Re: BUG #17141: SELECT LIMIT WITH TIES FOR UPDATE SKIP LOCKED returns wrong number of rows
Date: Wed, 25 Aug 2021 11:25:56 +0200
Message-ID: <c9241eb5-905d-779a-5769-cb9e1ef27119@iggland.com> (raw)
In-Reply-To: <CAOxo6XKrWf5Qi+a4z5jP3opXO6gvYMDdAZfEAbxE9C3-uH7ZsA@mail.gmail.com>
References: <17141-913d78b9675aac8e@postgresql.org>
<202108112007.evylbmzueapg@alvherre.pgsql>
<CAOxo6XKrWf5Qi+a4z5jP3opXO6gvYMDdAZfEAbxE9C3-uH7ZsA@mail.gmail.com>
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%40ma...
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: emil@iggland.com, david.christensen@crunchydata.com, alvherre@alvh.no-ip.org, pgsql-bugs@lists.postgresql.org, surafel3000@gmail.com
Subject: Re: BUG #17141: SELECT LIMIT WITH TIES FOR UPDATE SKIP LOCKED returns wrong number of rows
In-Reply-To: <c9241eb5-905d-779a-5769-cb9e1ef27119@iggland.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox