Getting error recursive reference to query must not appear within its non-recursive term in postgres

Question

I am running this query:

WITH RECURSIVE base_record AS (
  -- Base case: start from the initial defined term
  SELECT 
    dt.id AS defined_term_id,
    dt.name AS term_name,
    1 AS depth,
    ARRAY[dt.id] AS path  -- Track visited terms in an array
  FROM 
    "DefinedTerm" dt
  WHERE 
    dt.id = 'cm1avefkf003pe5h46ntdclns'  -- Start with the initial defined term
), graph as (
  SELECT * FROM base_record
  -- Traverse to related defined terms through ConditionSegments
  UNION ALL
      
      SELECT 
        csdt.id AS defined_term_id,
        csdt.name AS term_name,
        dt.depth + 1 AS depth,
        dt.path || csdt.id 
      FROM 
        graph dt
      JOIN 
        "Condition" c ON c."definedTermId" = dt.defined_term_id
      JOIN 
        "ConditionSegment" cs ON cs."conditionId" = c.id
      JOIN 
        "DefinedTerm" csdt ON cs."referencedDefinedTermId" = csdt.id
      WHERE 
        NOT csdt.id = ANY(dt.path)  


  -- Traverse to related defined terms through FormulaSegments
  UNION all 
      
      SELECT 
        fsdt.id AS defined_term_id,
        fsdt.name AS term_name,
        dt.depth + 1 AS depth,
        dt.path || fsdt.id
      FROM 
        graph dt
      JOIN 
        "FormulaSegment" fs ON fs."definedTermId" = dt.defined_term_id
      JOIN 
        "DefinedTerm" fsdt ON fs."referencedDefinedTermId" = fsdt.id
      WHERE 
        NOT fsdt.id = ANY(dt.path)
)

-- Select the recursive traversal results
SELECT *
FROM 
  graph
ORDER BY 
  depth, defined_term_id;

This is always throwing the error:

ERROR: recursive reference to query "graph" must not appear within its non-recursive term

If i remove one of the UNION ALL, so running this query:

WITH RECURSIVE base_record AS (
  -- Base case: start from the initial defined term
  SELECT 
    dt.id AS defined_term_id,
    dt.name AS term_name,
    1 AS depth,
    ARRAY[dt.id] AS path  -- Track visited terms in an array
  FROM 
    "DefinedTerm" dt
  WHERE 
    dt.id = 'cm1avefkf003pe5h46ntdclns'  -- Start with the initial defined term
), graph as (
  SELECT * FROM base_record
  -- Traverse to related defined terms through ConditionSegments
  UNION ALL
      
      SELECT 
        csdt.id AS defined_term_id,
        csdt.name AS term_name,
        dt.depth + 1 AS depth,
        dt.path || csdt.id 
      FROM 
        graph dt
      JOIN 
        "Condition" c ON c."definedTermId" = dt.defined_term_id
      JOIN 
        "ConditionSegment" cs ON cs."conditionId" = c.id
      JOIN 
        "DefinedTerm" csdt ON cs."referencedDefinedTermId" = csdt.id
      WHERE 
        NOT csdt.id = ANY(dt.path)  

)

-- Select the recursive traversal results
SELECT *
FROM 
  graph
ORDER BY 
  depth, defined_term_id;

Then it functions as expected.

Am I missing some specific syntax to specify that it is still in the recursion? I've tried to restructure the query in multiple different ways but can't seem to find the correct way to write it.

Zegarek · Accepted Answer · 2024-10-02 17:16:32Z

3

You can use parentheses to group these, e.g.:
_{demo at db<>fiddle}

WITH RECURSIVE base_record AS (
  -- Base case: start from the initial defined term
), graph as ( 
   select*from base_record 
   union all 
   (--open the parentheses here
   select...
   union all 
   select...
   )--this clarifies that everything inside is the recursive term
)--you can no longer add `CYCLE` or `SEARCH` here
SELECT*FROM graph
ORDER BY depth
       , defined_term_id;

That costs you search and cycle clauses, but if you're not using them anyways (you better be sure there are no loops in the hierarchy), it'll let you use the double union in the recursive term. That side-steps this one limitation, but it does not let you use multiple recursive statements in one. If you try, you'll get

ERROR:  recursive reference to query "cte" must not appear more than once

To do things like traversing the hierarchy in two directions, you could split that between two recursive CTEs:
_{demo at db<>fiddle}

with recursive base_record as (
  select id,parent,child,1 as depth
  from the_hierarchy
  where id=21
  limit 1)
,go_up as(
  select id,parent,child,depth as height 
  from base_record
  union all
  select h.id,h.parent,h.child,cte.height+1
  from the_hierarchy h join go_up as cte
    on cte.parent=h.id
)CYCLE id SET is_cycle USING path
,go_down as(
  table base_record
  union all
  select h.id,h.parent,h.child,cte.depth+1
  from the_hierarchy h join go_down as cte
    on cte.child=h.id
)CYCLE id SET is_cycle USING path
(select * from go_up where height<20
union all
select * from go_down where depth<20) order by id;

There might also be a way to re-arrange conditions in your current recursive term so that it does in one operation what you're currently doing in two, but you'll have to test on your setup if that's a better idea than the split. In my example that would mean looking up on cte.child=h.id OR cte.parent=h.id, plus rearranging the select list. In yours, you'd probably need to merge the join lists, which complicates things.

If you plan to do a lot of graph-related work in PostgreSQL, you might want to take a look at pgrouting and Apache AGE extensions.

edited Oct 2, 2024 at 17:16

answered Oct 2, 2024 at 11:27

Zegarek

29.9k5 gold badges27 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Charlieface Over a year ago

Interesting, SQL Server allows double recursion without () as long as all the recursive parts come last.

Zegarek Over a year ago

@Charlieface Thanks for mentioning this - I didn't really address OP's need to actually run more than one recursive term, only how to allow a union in there, which solves only their intermediate problem without really letting them finish their logic the way they intended. In PostgreSQL, you can only use a single self-reference, so the CTE has to be split up to accommodate more than one recursive term - I just added an update with that. It seems that M$SQL allows a bunch of those, as long as they aren't mixed with anchors and only combined with union all.

Charlieface Over a year ago

You can simulate the same thing in most cases by using a from cte cross join lateral (OtherStuffHere union all OtherStuffHere2) see dbfiddle.uk/x_tD-rwG Note the use of a direction qualifier otherwise you get an infinite loop.

Zegarek Over a year ago

Not sure what you meant by same thing - as what? The cross join in the demo you showed will not let you run another recursive reference, if that's what you mean - that's a flat join to an external table, using the single current reference you already had outside. I'm also not sure what's the purpose of the cross join in the anchor, to two rows that you then still limit 1 afterwards. If that's a sketch of an idea of making it run up and down simultaneously in a single term, I think I'd stick with the simple OR mentioned in the answer but I guess you could do it that way just as well.

Sinan Guclu Over a year ago

Thanks for all your answers and comments. I'm marking this answer as correct as the suggestion to re-arrange conditions has helped get to a working recursion. Along with all the other ideas suggested.

|

Collectives™ on Stack Overflow

Getting error recursive reference to query must not appear within its non-recursive term in postgres

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related