Subject: Re: BUG #17141: SELECT LIMIT WITH TIES FOR UPDATE SKIP LOCKED returns
 wrong number of rows
To: David Christensen <david.christensen@crunchydata.com>,
 Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: pgsql-bugs@lists.postgresql.org, Surafel Temesgen <surafel3000@gmail.com>
References: <17141-913d78b9675aac8e@postgresql.org>
 <202108112007.evylbmzueapg@alvherre.pgsql> <lzk0krveyz.fsf@crunchydata.com>
From: Emil Iggland <emil@iggland.com>
Message-ID: <58fd3519-8866-daed-0ca5-768f5bab1c00@iggland.com>
Date: Thu, 12 Aug 2021 08:47:56 +0200
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:78.0) Gecko/20100101
 Thunderbird/78.12.0
MIME-Version: 1.0
In-Reply-To: <lzk0krveyz.fsf@crunchydata.com>
Content-Type: text/plain; charset=utf-8
Content-Language: en-GB
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/58fd3519-8866-daed-0ca5-768f5bab1c00%40iggland.com>
Precedence: bulk

I continued trying the variations in the linked thread.

* Sub-query
BEGIN;
SELECT * FROM (SELECT * FROM queue
ORDER BY task DESC
FETCH NEXT 1 ROWS WITH TIES) t
FOR UPDATE SKIP LOCKED;
COMMIT;

This behaves the same way, this does not work around the bug.
The same goes for my previous "find" with NEXT. I can not replicate the
working state, I must have done something wrong last night.

I added some more tasks with the same number in order to see if there
was a problem with the first row, or with the count.

I now have the following counts:
task	count(*)
180	2
280	2
380	4
480	3
580	2

I attempted to select multiple tasks at the same time, representing a
case where a worker might select multiple tasks.

SELECT * FROM queue
ORDER BY task DESC
FETCH FIRST 3 ROWS WITH TIES
FOR UPDATE SKIP LOCKED;

Here I get three rows back (580), (580), (480)

If I run
SELECT * FROM queue ORDER BY task DESC
FETCH FIRST 3 ROWS WITH TIES;
I get back 5 rows (580), (580), (480), (480), (480) as expected.

/Emil


On 2021-08-11 23:39, David Christensen wrote:
> 
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> 
>> 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 ...
> 
> Yeah, I'd looked at this when I found it, and short of detecting the situation "WITH TIES FOR UPDATE
> SKIP LOCKED" and erroring out, it seems like it would require adding in infrastructure that we don't
> support (AFAIK) with unlocking an already locked row inside a transaction or reworking the order of
> LockRows and Limit such that Limit comes first (and itself handles the WITH TIES) before handing to
> LockRows.  Either way (other than the error), it seems to be a fairly invasive change.
> 
> If someone has another idea on how to handle this, I could take a stab at things.  Detecting the
> situation and erroring seems like the easiest way to handle so you're at least not getting back bad
> results, though I agree that the functionality would be useful if we *could* support it somehow.
> 
> David
>