From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | csteifel(at)gmail(dot)com |
Subject: | BUG #18472: SELECT FOR UPDATE locking more rows than expected |
Date: | 2024-05-17 15:35:58 |
Message-ID: | 18472-b6a2287713c0a40c@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 18472
Logged by: Colin Steifel
Email address: csteifel(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2024-05-17 18:01:51 | Re: BUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16 |
Previous Message | Tom Lane | 2024-05-17 14:37:35 | Re: BUG #18470: Time literal accepted in Postgres 15 and below, not accepted in Postgres 16 |