From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Emil Iggland <emil@iggland.com>
Cc: David Christensen <david.christensen@crunchydata.com>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Cc: Surafel Temesgen <surafel3000@gmail.com>
Subject: Re: BUG #17141: SELECT LIMIT WITH TIES FOR UPDATE SKIP LOCKED returns wrong number of rows
Date: Sat, 02 Oct 2021 18:30:44 -0400
Message-ID: <3720264.1633213844@sss.pgh.pa.us> (raw)
In-Reply-To: <202110022205.jwz6b5bdyja5@alvherre.pgsql>
References: <202110022205.jwz6b5bdyja5@alvherre.pgsql>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, alvherre@alvh.no-ip.org, emil@iggland.com, david.christensen@crunchydata.com, pgsql-bugs@lists.postgresql.org, surafel3000@gmail.com
Subject: Re: BUG #17141: SELECT LIMIT WITH TIES FOR UPDATE SKIP LOCKED returns wrong number of rows
In-Reply-To: <3720264.1633213844@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox