Row locking in recursive query

Question

The documentation says:

The locking clauses cannot be used in contexts where returned rows cannot be clearly identified with individual table rows; for example they cannot be used with aggregation.

How do I work around this in the setting of a recursive query, that is implemented with UNION (ALL)?

Is there a better solution than joining the result of the recursive query with the table again, this time with FOR UPDATE? I would have to lock the whole table for this query to make sure that nothing changes concurrently to the join, right?

Answer 1

What makes you think that aggregation has anything to do with a recursive query? Seems to work for me,

CREATE TABLE foo(pk,fk,description)
AS VALUES
    ( 1 , null , 'domains' ),
    ( 2 , 1 , 'people' ),
    ( 3 , 1 , 'cars' ),
    ( 4 , 2 , 'tom' ),
    ( 5 , 2 , 'smith' ),
    ( 6 , 3 , 'vmw' ),
    ( 7 , 2 , 'betty' ),
    ( 8 , 3 , 'ford' );

WITH RECURSIVE t(pk, fk, description, level) AS (
    SELECT pk, fk, ARRAY[description], 0
    FROM foo
    WHERE fk IS NULL
    UNION ALL
        SELECT foo.pk, foo.fk, t.description || foo.description, t.level+1
        FROM t
        JOIN foo ON (foo.fk = t.pk)
)
SELECT *
FROM t
FOR UPDATE;

Example taken from example on this question on dba.se