Recursive CTE with cycle detection using path array

Question

I have a recursive CTE definition (graph) in Postgres that involves cycle detection: fiddle

CREATE TABLE relationships (
    subject_type,
    subject_id,
    subject_relation,
    resource_type,
    resource_id,
    relationship
)AS VALUES(
    'subject_type1',
    'subject_id1',
    'subject_relation1',
    'resource_type1',
    'resource_id1',
    'relationship1');
CREATE TABLE type_restrictions (
    subject_type,
    subject_relation,
    resource_type,
    relationship 
)AS VALUES(
    'subject_type1',
    'subject_relation1',
    'resource_type1',
    'relationship1');

CREATE VIEW graph AS WITH RECURSIVE
 relationship_graph(subject_type, subject_id, subject_relation, resource_type, resource_id, relationship, depth, is_cycle, path)
 AS(SELECT 
        r.subject_type, r.subject_id, r.subject_relation, 
        r.resource_type, r.resource_id, 
        r.relationship, 
        0, 
        false,
        ARRAY[ROW(r.resource_type, r.resource_id, r.relationship, r.subject_type, r.subject_id, r.subject_relation)]
    FROM relationships r
    INNER JOIN type_restrictions tr
    ON 
        r.subject_type = tr.subject_type AND 
        r.resource_type = tr.resource_type AND 
        r.relationship = tr.relationship
    UNION
    SELECT
        g.subject_type,
        g.subject_id,
        g.subject_relation,
        r.resource_type,
        r.resource_id,
        r.relationship,
        g.depth + 1,
        ROW(r.resource_type, r.resource_id, r.relationship, r.subject_type, r.subject_id, r.subject_relation) = ANY(path),
        path || ROW(r.resource_type, r.resource_id, r.relationship, r.subject_type, r.subject_id, r.subject_relation)
    FROM relationship_graph g, relationships r
    WHERE
        g.resource_type = r.subject_type AND 
        g.resource_id = r.subject_id AND
        g.relationship = r.subject_relation AND
        NOT is_cycle )
SELECT * FROM relationship_graph WHERE depth < 3;

When I try to create this view I get the following error from Postgres:

ERROR:  column "path" has pseudo-type record[]

I am following the exact guide as specified in the official Postgres documentation. See https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-CYCLE (example 3).

I'm not seeing the obvious reason for this error, could someone assist?

I expect the ARRAY[ROW] types to not be ARRAY[RECORD], and I don't expect an error. Unless the postgres documentation is wrong, it seems that this should work.

Answer 1

I expect the ARRAY[ROW] types to not be ARRAY[RECORD]

A composite value ROW() constructor by default yields a record type. Quoting the doc, chapter 4.2.13. SQL Syntax, Value Expressions, Row Constructors:

By default, the value created by a ROW expression is of an anonymous record type. If necessary, it can be cast to a named composite type — either the row type of a table, or a composite type created with CREATE TYPE AS. An explicit cast might be needed to avoid ambiguity.

You can use pg_typeof() to verify that:

select pg_typeof(row(1,'a'));

pg_typeof
record

---

I don't expect an error. Unless the postgres documentation is wrong, it seems that this should work.

Both your code and the one in the doc are perfectly correct, as plain select statements. The difference is that you're trying to use yours as a view definition, which won't work because those cannot involve pseudo-types. It's mentioned in chapter 8.21 Data Types, Pseudo-Types:

A pseudo-type cannot be used as a column data type, but it can be used to declare a function's argument or result type.

Type record is on that list:

Name	Description
record	Identifies a function taking or returning an unspecified row type.

Since you're collecting all fields of the relationships table row into the array element, you could just as well do a bulk array[r]::relationships[] - that's a single-element array with a record of relationships in it, explicitly typed as an array of relationships[].
Not only does that fix your type resolution problem, it also saves you a ton of re-typing those field names in the constructor:
_{demo at db<>fiddle}

CREATE VIEW graph AS WITH RECURSIVE
 relationship_graph(subject_type, subject_id, subject_relation, resource_type, resource_id, relationship, depth, is_cycle, path)
 AS(SELECT r.*
         , 0     AS depth
         , false AS is_cycle
         , ARRAY[r]::relationships[] AS path
    FROM relationships AS r
    JOIN type_restrictions AS tr
    USING(subject_type,resource_type,relationship)
    UNION
    SELECT g.subject_type
         , g.subject_id
         , g.subject_relation
         , r.resource_type
         , r.resource_id
         , r.relationship
         , g.depth + 1   AS depth
         , r = ANY(path) AS is_cycle
         , path || r     AS path
    FROM relationship_graph AS g
    JOIN relationships AS r
    ON  g.resource_type = r.subject_type
    AND g.resource_id = r.subject_id
    AND g.relationship = r.subject_relation
    AND NOT is_cycle  )
SELECT * FROM relationship_graph WHERE depth < 3;

SELECT*FROM graph;

subject_type	subject_id	subject_relation	resource_type	resource_id	relationship	depth	is_cycle	path
subject_type1	subject_id1	subject_relation1	resource_type1	resource_id1	relationship1	0	f	{"(subject_type1,subject_id1,subject_relation1,resource_type1,resource_id1,relationship1)"}