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

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(at)alvh(dot)no-ip(dot)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
>