What happens for "FOR SHARE" with nested select?

Question

Say, I have a nested select:

WITH candidate AS (
   select * table_a where flag = 'a'
)
select * from candidate where other_flag = 'b' for share

This SQL indirectly select from table_a with for share. In this case, will row locks be acquired on returned rows from table_a?

Answer 1

That query should not acquire any row locks, because candidate is not a base table. But you can see yourself: run

EXPLAIN WITH (...) SELECT ...

and see if there is a LockRows node in the execution plan.

If you want to lock the rows, you will have to use FOR SHARE in the subquery.