Do explicit locks in postgresql CTEs lock every row even with a cursor?

Let's say I have a table

CREATE TABLE mytable (
  user_id bigint,
  someval text
)

In my application, I want to lock and fetch every row for some given user_id e.g. 123 to do some additional processing.

But I don't want to fetch everything at once because of the volumetry.

In Jooq in my java application, I'd use something like

dslContext.select(MYTABLE.SOMEVAL)
          .from(MYTABLE)
          .where(MYTABLE.USER_ID.eq(123))
          .orderBy(MY_TABLE.SOMEVAL)
          .forUpdate()
          .fetchSize(...)
          .fetchLazy()
          ...

But the postgresql documentation states that

if a locking clause is used in a cursor's query, only rows actually fetched or stepped past by the cursor will be locked

So I instead might do something like

var cte = name("cte").as(
    select(MYTABLE.SOMEVAL)
    .from(MYTABLE)
    .where(MYTABLE.USER_ID.eq(123))
    .orderBy(MY_TABLE.SOMEVAL)
    .forUpdate()
);
dslContext.with(cte)
          .select(cte.field(MYTABLE.SOMEVAL))
          .orderBy(cte.field(MY_TABLE.SOMEVAL))
          .fetchSize(...)
          .fetchLazy()
          ...

But I don't know if this actually locks all the rows from the CTE before the main query, or if it might do some optimization and still only lock the rows actually fetched

I don't know if this actually locks all the rows from the CTE before the main query

It does not. Your assumption that it might get optimised away, is correct. Even if you somehow convinced jOOQ to explicitly materialize the CTE, which doesn't seem supported, it still would not affect the cursor locking behaviour.

What you can do, is fire a separate row-locking select beforehand:

select from mytable where user_id=123 for update;

Note that I'm not selecting any columns, which means this command will only respond with a command tag with affected row count. I'm not sure how to do that through jOOQ as it interprets empty .select() param list as an asterisk select *, which is the opposite of what I'm doing. You could try to use its emulated (otherwise unsupported by PostgreSQL) * except() clause to remove all columns.

If you want it all in one query, you could also try and build one like this:

declare cursor1 cursor for
with cte as(
  select someval 
  from mytable 
  where user_id=123 
  for update)
,cte2 as(select any_value(0) 
         from cte)
select someval
from cte
cross join cte2
order by someval;

It forces the evaluation of the whole locking scan before exposing the results. any_value() is a cheap way to flatten the uncorrelated subquery, but you could just as well use count(*) - once it does its job, it gets discarded.

Another idea would be to swap out cursor-based pagination for keyset pagination. You'd have to rethink your query but it's potentially cheaper, lighter, faster and doesn't have to keep your transaction open (or save a snapshot of the table, like with hold cursors do).

---

You can check pg_locks and pageinspect to see all the locks for yourself:
_{demo at db<>fiddle}

create extension pageinspect;

create table mytable (
  user_id bigint,
  someval text
);

insert into mytable
values (123,'a')
      ,(123,'b')
      ,(123,'c')
      ,(456,'d')
      ,(789,'e');

select relation::regclass,locktype,mode,virtualxid,transactionid from pg_locks;
select m.ctid,user_id,someval,t_xmax,t_infomask2,t_infomask,raw_flags 
from mytable as m
join heap_page_item_attrs(get_raw_page('mytable','main',0),'mytable')
  on t_ctid=m.ctid
cross join heap_tuple_infomask_flags(t_infomask, t_infomask2);

This is before locking:

Open the cursor, fetch something and see only the current and past rows are locked (table got a RowShareLock, only the first two rows' infomasks got modified):

begin;

declare cursor1 cursor for
select someval
from mytable
where user_id=123
order by someval
for update;

fetch next from cursor1;
fetch next from cursor1;

Wrapping the locking statement in a CTE, even a materialized one, changes nothing:

commit and chain; 

declare cursor1 cursor for
with cte as materialized(
  select someval
  from mytable
  where user_id=123
  order by someval
  for update)
select someval from cte;

fetch next from cursor1;
fetch next from cursor1;