42P19 — Invalid recursion

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

English manual coverage begins with PostgreSQL 8.1. Source history and runtime observations have their own recorded version boundaries.

Reading PG 18·Select a recorded version

8.4 9.0 9.1 9.2 9.3 9.4 9.5 9.6 10 11 12 13 14 15 16 17 18 19 20

aliases: Unknown
class name: Syntax Error or Access Rule Violation
condition name: invalid_recursion
lang: en
sqlstate: 42P19

Definition PG 18

42P19 — Invalid recursion

At a glance {#at-a-glance}

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

Meaning {#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 {#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 {#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 {#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 {#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 {#related}

Sources {#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)

Compare versions

SQLSTATE evidence and recorded runtime versions

claims · identity.class-and-condition

{
  "claim_id": "identity.class-and-condition",
  "limits": "Identity does not enumerate all source branches or client failures.",
  "method": "Read fixed REL_18_6 errcodes.txt and locked facts.",
  "position": 0,
  "runtime": [],
  "sources": [
    "src.errcodes.42P19.18.6"
  ],
  "statement": "42P19 is the invalid_recursion condition in Class 42."
}

claims · mechanism.selected-source-path

{
  "claim_id": "mechanism.selected-source-path",
  "limits": "Representative confirmed core paths only; not exhaustive historical or extension coverage.",
  "method": "Read selected resolved REL_18_6 report groups and message roles.",
  "position": 1,
  "runtime": [],
  "sources": [
    "src.call.42P19.e59c077eba1a4a52a69a6d97",
    "src.call.42P19.e6e0586856ee8989b006f7ae",
    "src.call.42P19.3b25492c51b06d773f237993",
    "src.call.42P19.recursion-context"
  ],
  "statement": "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 p_hasAggs; it does not establish that every window function is forbidden. Outer-join context applies to the nullable side (LEFT right, RIGHT left, FULL both), while the preserved side of a one-sided join is not covered by that guard."
}

claims · versions.catalogue-boundary

{
  "claim_id": "versions.catalogue-boundary",
  "limits": "First observed release is a lower bound, not precise behavioral introduction.",
  "method": "Read locked catalogue facts and definition snapshot.",
  "position": 2,
  "runtime": [],
  "sources": [
    "src.errcodes.42P19.18.6"
  ],
  "statement": "Locked catalogue snapshots show this condition by PostgreSQL 8.4.0; source behavior here is fixed at PostgreSQL 18.6."
}

messages · message.variant-1

{
  "limits": "Placeholders remain dynamic source fields; no concrete runtime value is claimed.",
  "message_id": "message.variant-1",
  "path": "",
  "position": 0,
  "raw": {
    "detail_templates": [],
    "hint_templates": [],
    "id": "message.variant-1",
    "limits": "Placeholders remain dynamic source fields; no concrete runtime value is claimed.",
    "primary_template": "aggregate functions are not allowed in a recursive query's recursive term",
    "roles": [
      {
        "role": "errmsg",
        "template": "aggregate functions are not allowed in a recursive query's recursive term"
      }
    ],
    "severity_source": "ERROR",
    "sources": [
      "src.call.42P19.e59c077eba1a4a52a69a6d97"
    ],
    "sqlstate": "42P19"
  },
  "severity": "ERROR",
  "sources": [
    "src.call.42P19.e59c077eba1a4a52a69a6d97"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "aggregate functions are not allowed in a recursive query's recursive term",
      "position": 0,
      "role": "",
      "template": "aggregate functions are not allowed in a recursive query's recursive term"
    }
  ]
}

messages · message.variant-2

{
  "limits": "Placeholders remain dynamic source fields; no concrete runtime value is claimed.",
  "message_id": "message.variant-2",
  "path": "",
  "position": 1,
  "raw": {
    "detail_templates": [],
    "hint_templates": [],
    "id": "message.variant-2",
    "limits": "Placeholders remain dynamic source fields; no concrete runtime value is claimed.",
    "primary_template": "recursive query \"%s\" does not have the form non-recursive-term UNION [ALL] recursive-term",
    "roles": [
      {
        "role": "errmsg",
        "template": "recursive query \"%s\" does not have the form non-recursive-term UNION [ALL] recursive-term"
      }
    ],
    "severity_source": "ERROR",
    "sources": [
      "src.call.42P19.e6e0586856ee8989b006f7ae"
    ],
    "sqlstate": "42P19"
  },
  "severity": "ERROR",
  "sources": [
    "src.call.42P19.e6e0586856ee8989b006f7ae"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "recursive query \" \" does not have the form non-recursive-term UNION [ALL] recursive-term",
      "position": 0,
      "role": "",
      "template": "recursive query \"%s\" does not have the form non-recursive-term UNION [ALL] recursive-term"
    }
  ]
}

messages · message.variant-3

{
  "limits": "Placeholders remain dynamic source fields; no concrete runtime value is claimed.",
  "message_id": "message.variant-3",
  "path": "",
  "position": 2,
  "raw": {
    "detail_templates": [],
    "hint_templates": [],
    "id": "message.variant-3",
    "limits": "Placeholders remain dynamic source fields; no concrete runtime value is claimed.",
    "primary_template": "recursive reference to query \"%s\" must not appear more than once",
    "roles": [
      {
        "role": "errmsg",
        "template": "recursive reference to query \"%s\" must not appear more than once"
      }
    ],
    "severity_source": "ERROR",
    "sources": [
      "src.call.42P19.3b25492c51b06d773f237993"
    ],
    "sqlstate": "42P19"
  },
  "severity": "ERROR",
  "sources": [
    "src.call.42P19.3b25492c51b06d773f237993"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "recursive reference to query \" \" must not appear more than once",
      "position": 0,
      "role": "",
      "template": "recursive reference to query \"%s\" must not appear more than once"
    }
  ]
}

sources · src.errcodes.42P19.18.6

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "line 345",
  "path": "src/backend/utils/errcodes.txt",
  "position": 0,
  "sha256": "6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba",
  "source_id": "src.errcodes.42P19.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/errcodes.txt#L345"
}

sources · src.call.42P19.e59c077eba1a4a52a69a6d97

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "lines 1329-1333",
  "path": "src/backend/parser/parse_agg.c",
  "position": 1,
  "sha256": "6e80a805fddbfc9c7e0f3047b348bf992a20c99f16ce7fee89c7067753027f72",
  "source_id": "src.call.42P19.e59c077eba1a4a52a69a6d97",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/parser/parse_agg.c#L1329-L1333"
}

sources · src.call.42P19.e6e0586856ee8989b006f7ae

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "lines 940-944",
  "path": "src/backend/parser/parse_cte.c",
  "position": 2,
  "sha256": "908eef14ac26654fe0b9c176ef50e56ba710b7fcd8e1239b944d7c2152fc2d6b",
  "source_id": "src.call.42P19.e6e0586856ee8989b006f7ae",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/parser/parse_cte.c#L940-L944"
}

sources · src.call.42P19.3b25492c51b06d773f237993

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "lines 1072-1077",
  "path": "src/backend/parser/parse_cte.c",
  "position": 3,
  "sha256": "908eef14ac26654fe0b9c176ef50e56ba710b7fcd8e1239b944d7c2152fc2d6b",
  "source_id": "src.call.42P19.3b25492c51b06d773f237993",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/parser/parse_cte.c#L1072-L1077"
}

sources · src.call.42P19.recursion-context

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "lines 1056-1069",
  "path": "src/backend/parser/parse_cte.c",
  "position": 4,
  "sha256": "908eef14ac26654fe0b9c176ef50e56ba710b7fcd8e1239b944d7c2152fc2d6b",
  "source_id": "src.call.42P19.recursion-context",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/parser/parse_cte.c#L1056-L1069"
}

sources · src.calls.REL_18_6.42P19

{
  "commit": "",
  "docs_url": "",
  "kind": "local_artifact",
  "location": "",
  "path": "raw/calls/REL_18_6.jsonl",
  "position": 5,
  "sha256": "9ee8a0e81d8f0825c5c1ae45583439859a26e602bdd4ce2f2a62aa278867ccbf",
  "source_id": "src.calls.REL_18_6.42P19",
  "tag": "",
  "url": ""
}

Source references

Original manual reference

Full definition and evidence JSON

Definition sources

center · PostgreSQL 18 · english-manuals:6c2302d81cb07ddd1765aff6e9c2eba14895eb7bcac9cd7dba76190c5984f726

Text language: en · english-manuals:6c2302d81cb07ddd1765aff6e9c2eba14895eb7bcac9cd7dba76190c5984f726