pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: 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, 2 Oct 2021 19:05:14 -0300
Message-ID: <202110022205.jwz6b5bdyja5@alvherre.pgsql> (raw)
In-Reply-To: <c9241eb5-905d-779a-5769-cb9e1ef27119@iggland.com>

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/





view thread (11+ messages)  latest in thread

Message-ID: <202110022205.jwz6b5bdyja5@alvherre.pgsql>
Permalink:  ../202110022205.jwz6b5bdyja5@alvherre.pgsql/
Also on:    postgresql.org/message-id/202110022205.jwz6b5bdyja5@alvherre.pgsql

 · 

reply
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: 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: <202110022205.jwz6b5bdyja5@alvherre.pgsql>

* 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