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

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