42P19 — Invalid recursion

At a glance

42P19 (invalid_recursion) Recursive query structure is checked before the recursive term can run.

Meaning

Recursive-query validation rejects a recursive term whose structure cannot be evaluated safely. Core 18.6 checks aggregate use in the recursive term, the required non-recursive UNION [ALL] recursive form, and recursive-reference context and multiplicity. The selected aggregate guard is about aggregates (p_hasAggs); it does not justify saying that every window function is forbidden.

Diagnosis

Read the query shape rather than only the CTE name. Separate the anchor term from the recursive term, count references to the recursive query, and inspect whether a reference appears in a forbidden recursive context such as a subquery or the nullable side of an outer join: for LEFT JOIN that is the right side, for RIGHT JOIN the left side, and for FULL JOIN both sides; the preserved side of a one-sided outer join is not covered by that guard. Also check INTERSECT/EXCEPT contexts. Identify actual aggregates in the recursive term; do not classify every window expression as this error. This distinguishes structural recursion errors from 42P18 type inference failures.

Response

Rewrite the CTE into one non-recursive anchor followed by one UNION or UNION ALL recursive term, keep one recursive reference in an allowed context, and move aggregation outside the recursive step when needed. Validate termination and result cardinality before retrying. If this ERROR occurs inside an explicit transaction, ROLLBACK or ROLLBACK TO a pre-error savepoint before sending the rewritten query.

Messages

Representative source messages include: message: aggregate functions are not allowed in a recursive query's recursive term; message: recursive query "%s" does not have the form non-recursive-term UNION [ALL] recursive-term; message: recursive reference to query "%s" must not appear more than once. Placeholders are filled by the actual object, column, or parameter.

Versions

The locked catalogue shows this condition by PostgreSQL 8.4.0; behavior here is fixed to PostgreSQL 18.6 source. Catalogue presence is a range boundary, not proof that every message or feature began in that release.

Related

• 42P18
• 42P07

Sources

Source messages, line anchors, and evidence limits are recorded in authored evidence.

• src/backend/parser/parse_agg.c:1329-1333 (fixed source)
• src/backend/parser/parse_cte.c:940-944 (fixed source)
• src/backend/parser/parse_cte.c:1072-1077 (fixed source)
• src/backend/parser/parse_cte.c:1056-1069 (fixed source)