[BUG] postgres DBM explain_parameterized_queries fails on customer queries with untyped parameters

[paymog]

Agent: 7.78.3 and 7.78.4
Helm chart: 3.213.2
Postgres integration: 23.7.0 (bundled with 7.78.3)
Driver: psycopg3 (error class is psycopg.errors.*, no "2")
Deployment: cluster checks runner on EKS

What happens

DBM's explain_parameterized_queries feature wraps customer queries in PREPARE ... AS <stmt> to inspect their plans. When the customer query has untyped parameters, PREPARE fails because PostgreSQL can't resolve the parameter types. The check retries the same statement every collection cycle indefinitely. For one of our workloads this produced tens of thousands of identical error log lines per pod per day.

A representative error:

psycopg.errors.AmbiguousFunction: function unnest(unknown) is not unique
LINE 3:  where exists (select 1 from unnest($2) as p(id) where id ...
                                     ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.

Reproduced on both PostgreSQL 13.20 and 17.4. I initially assumed PG13-only and disabled the feature on the PG13 hosts. The same error kept firing on the PG17 hosts. So it isn't really a PG-version thing. The PREPARE wrapper doesn't preserve or annotate parameter types from pg_stat_statements, and any PG version will reject an untyped unnest($N).

Traceback origin:

File ".../datadog_checks/postgres/explain_parameterized_queries.py", line 122, in _create_prepared_statement
    self._execute_query(conn, PREPARE_STATEMENT_QUERY.format(...))
File ".../datadog_checks/postgres/explain_parameterized_queries.py", line 190, in _execute_query
    cursor.execute(query, ignore_query_metric=True)

Reproduction

Any workload that emits queries with untyped array parameters (unnest($N), ANY($N) without a cast, similar shapes) against a DBM-enabled postgres instance will trigger it. #19860 reports the same crash on Hasura-generated queries; my workload is different but generates similar shapes. Probably any ORM or codegen path that emits unnest($N) directly will hit this.

What I'd want

Two options that would each fix it:

1. Type-annotate parameters in the PREPARE wrapper. The types are available from pg_stat_statements.
2. Catch AmbiguousFunction and IndeterminateDatatype once per query signature and skip the statement for the rest of the agent process lifetime, instead of retrying every cycle.

Option 2 is the smaller fix. Even if PREPARE can never succeed for a given parameterized query, the agent shouldn't keep retrying it forever — that produces log spam and presumably some amount of memory churn from the exception path.

Workaround

Set query_samples.explain_parameterized_queries: false per instance. That stops the noise.

Related

#19860 (open since 2025-03) is the psycopg2 / IndeterminateDatatype / Hasura form of this. Same _create_prepared_statement codepath. The bug survived the psycopg2 → psycopg3 migration intact. This report is the unnest / AmbiguousFunction variant.

---

Edit

An earlier version of this report claimed the bug was causing pod crashloops. After more investigation that turned out to be wrong — the crashloops in our environment are OOM kills (8Gi limit, agent reaches 8.3GB anon-rss), unrelated to this bug. The unnest error is still a real bug worth fixing, but the symptom is log spam and wasted check cycles, not a crashloop.