Subject: Re: BUG #17141: SELECT LIMIT WITH TIES FOR UPDATE SKIP LOCKED returns
 wrong number of rows
To: Tom Lane <tgl@sss.pgh.pa.us>, Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: David Christensen <david.christensen@crunchydata.com>,
 PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>,
 Surafel Temesgen <surafel3000@gmail.com>
References: <202110022205.jwz6b5bdyja5@alvherre.pgsql>
 <3720264.1633213844@sss.pgh.pa.us>
From: Emil Iggland <emil@iggland.com>
Message-ID: <c159816b-2148-aaff-d78e-1b52a0cd68c5@iggland.com>
Date: Mon, 11 Oct 2021 16:07:40 +0200
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:78.0) Gecko/20100101
 Thunderbird/78.14.0
MIME-Version: 1.0
In-Reply-To: <3720264.1633213844@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8
Content-Language: en-GB
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/c159816b-2148-aaff-d78e-1b52a0cd68c5%40iggland.com>
Precedence: bulk

To be it would seem that a "peek()" type function would do a good job
also, you can always peek at the next row to determine if it is not a
candidate, if it is the "look and lock"-it.
Then you can keep both features.


On 2021-10-03 00:30, Tom Lane wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
>> On 2021-Aug-25, Emil Iggland wrote:
>>> 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.
> 
>> The problem is that when the WITH TIES clause is added, we need to read
>> one extra row after the one that reaches the LIMIT count, in order to
>> verify whether it (the next one) should be included due to a tie.  With
>> the executor structure that we currently have, there is no way to read
>> that row and not lock it.  So a good fix would be to separate the act of
>> locking the row from the act of reading it.
> 
> I'm not convinced that's a "good fix".  SELECT FOR UPDATE generally
> considers that it should return rows only if they still match the
> WHERE condition after they're locked.  The natural extension of that
> to WITH TIES is that you ought to check for equality after locking.
> So these features seem rather fundamentally in conflict.
> 
> 			regards, tom lane
>