From: Emil Iggland <emil@iggland.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
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: Mon, 11 Oct 2021 16:07:40 +0200
Message-ID: <c159816b-2148-aaff-d78e-1b52a0cd68c5@iggland.com> (raw)
In-Reply-To: <3720264.1633213844@sss.pgh.pa.us>
References: <202110022205.jwz6b5bdyja5@alvherre.pgsql>
<3720264.1633213844@sss.pgh.pa.us>
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
>
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: emil@iggland.com, tgl@sss.pgh.pa.us, alvherre@alvh.no-ip.org, 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: <c159816b-2148-aaff-d78e-1b52a0cd68c5@iggland.com>
* 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