Date: Sat, 2 Oct 2021 19:05:14 -0300
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Emil Iggland <emil@iggland.com>
Cc: 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
Message-ID: <202110022205.jwz6b5bdyja5@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <c9241eb5-905d-779a-5769-cb9e1ef27119@iggland.com>
Archived-At: 
 <https://www.postgresql.org/message-id/202110022205.jwz6b5bdyja5%40alvherre.pgsql>
Precedence: bulk

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.

-- 
Álvaro Herrera           39°49'30"S 73°17'W  —  https://www.EnterpriseDB.com/