From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: csteifel@gmail.com
Subject: BUG #18472: SELECT FOR UPDATE locking more rows than expected
Date: Fri, 17 May 2024 15:35:58 +0000
Message-ID: <18472-b6a2287713c0a40c@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 18472
Logged by: Colin Steifel
Email address: csteifel@gmail.com
PostgreSQL version: 16.3
Operating system: Official docker container 16.3 tag
Description:
I'm using the following to set up what I believe to be the issue at hand:
CREATE TABLE IF NOT EXISTS public.tab1
(
a integer NOT NULL,
b integer NOT NULL
);
CREATE TABLE IF NOT EXISTS public.tab2
(
a_ref integer NOT NULL,
c integer NOT NULL
);
INSERT INTO public.tab1 VALUES (1,1),(2,2),(3,3);
INSERT INTO public.tab2 VALUES (1,5),(1,6),(1,7),(2,8),(2,9),(3,10);
Then in pgadmin I'm opening two query tabs for the same database and running
the following in tab 1
BEGIN;
SELECT *
FROM public.tab1 t1,
LATERAL (SELECT *
FROM public.tab2
WHERE "a"="a_ref"
LIMIT 1
) t2
ORDER BY b ASC
LIMIT 1
FOR UPDATE;
Keeping the transaction open and running the following in tab 2:
SELECT * FROM public.tab2 FOR UPDATE SKIP LOCKED;
What is happening:
I'm only getting 3 rows back in query tab 2
What I'm expecting:
Expecting to get 5 rows in query tab 2
If I change the original query to either remove the LIMIT clause in the
subquery eg:
SELECT *
FROM public.tab1 t1,
LATERAL (SELECT *
FROM public.tab2
WHERE "a"="a_ref"
) t2
ORDER BY b ASC
LIMIT 1
FOR UPDATE;
Or if I remove the ORDER BY b ASC in the top level query eg:
SELECT *
FROM public.tab1 t1,
LATERAL (SELECT *
FROM public.tab2
WHERE "a"="a_ref"
LIMIT 1
) t2
LIMIT 1
FOR UPDATE;
And then retry I can see that I get the expected 5 rows back in the query
from tab 2 leading me to believe that when both the LIMIT and ORDER BY are
present postgres is locking more rows than I believe should be necessary
based on the documentation that I've read and experiments that I've done.
Thanks for your time,
Colin
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: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, csteifel@gmail.com
Subject: Re: BUG #18472: SELECT FOR UPDATE locking more rows than expected
In-Reply-To: <18472-b6a2287713c0a40c@postgresql.org>
* 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