BUG #18472: SELECT FOR UPDATE locking more rows than expected

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

Responses

Browse pgsql-bugs by date

  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