From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
cc: Emil Iggland <emil@iggland.com>,
        David Christensen <david.christensen@crunchydata.com>,
        PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>,
        Surafel Temesgen <surafel3000@gmail.com>
Subject: Re: BUG #17141: SELECT LIMIT WITH TIES FOR UPDATE SKIP LOCKED returns
 wrong number of rows
In-reply-to: <202110022205.jwz6b5bdyja5@alvherre.pgsql>
References: <202110022205.jwz6b5bdyja5@alvherre.pgsql>
Comments: In-reply-to Alvaro Herrera <alvherre@alvh.no-ip.org>
	message dated "Sat, 02 Oct 2021 19:05:14 -0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3720263.1633213844.1@sss.pgh.pa.us>
Date: Sat, 02 Oct 2021 18:30:44 -0400
Message-ID: <3720264.1633213844@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3720264.1633213844%40sss.pgh.pa.us>
Precedence: bulk

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