Query obfuscation mismatch between pg_stat_activity and pg_stat_statements

[dlants]

Summary

A single PostgreSQL query containing EXTRACT(epoch FROM …) (or any other field-name argument that Postgres internally treats as a string constant) produces two distinct query signatures in Database Monitoring:

• one signature derived from pg_stat_activity samples (containing the literal epoch)
• a different signature derived from pg_stat_statements query metrics (where epoch has been replaced by Postgres' own normalization).

Because the signatures don't match, DBM treats them as two separate queries. The practical impact is that the explain plan (collected via the activity/samples path) ends up under one signature, while the query metrics (latency, calls, rows, etc., collected via the statements path) end up under the other. They never join, so for these queries you cannot click from a metrics row to its execution plan, and aggregate volume/performance numbers are split.

This issue affects any query using EXTRACT(<field> FROM …) — and likely other Postgres functions whose first argument is internally a string constant — and is reproducible against a stock Postgres + Datadog Agent setup. Details and a self-contained reproduction below.

---

Step 1: Postgres itself normalizes epoch away in pg_stat_statements.

I spun up a stock Postgres 16 with pg_stat_statements, ran:

SELECT EXTRACT(epoch FROM assay_result.created_at) AS age, count(*) AS n
  FROM assay_result
 WHERE assay_result.tenant_id = 42
   AND assay_result.status = 'active'
   AND EXTRACT(epoch FROM assay_result.created_at) > 1700000000
 GROUP BY 1;

…and pg_stat_statements stored it as:

SELECT EXTRACT($1 FROM assay_result.created_at) AS age, count(*) AS n
  FROM assay_result
 WHERE assay_result.tenant_id = $2
   AND assay_result.status = $3
   AND EXTRACT($4 FROM assay_result.created_at) > $5
 GROUP BY 1

epoch is replaced with $1/$4. This is because internally Postgres rewrites EXTRACT(epoch FROM x) to pg_catalog.date_part('epoch', x); 'epoch' is a string constant in the parse tree, and pg_stat_statements normalizes string constants.

Step 2: I fed both Postgres outputs through both agent obfuscators.

Using the actual code from datadog-agent/pkg/obfuscate and DataDog/go-sqllexer. The two obfuscator pathways are exercised like this:

import ddobf "github.com/DataDog/datadog-agent/pkg/obfuscate"

// Both pathways use the SAME entry-point constructor `ddobf.NewObfuscator(...)`
// from `datadog-agent/pkg/obfuscate`. Which underlying implementation actually
// runs is decided inside ObfuscateSQLStringWithOptions based on whether the
// SQLConfig.ObfuscationMode field is set:
//
//   if opts.ObfuscationMode != "" {
//       oq, err = o.ObfuscateWithSQLLexer(in, opts)   // -> go-sqllexer
//   } else {
//       oq, err = o.obfuscateSQLString(in, opts)      // -> legacy in-tree
//   }

// LEGACY pathway: ObfuscationMode is left as the zero value, so dispatch
// falls through to the in-tree obfuscateSQLString implementation in
// pkg/obfuscate/sql.go (the homegrown tokenizer + replaceFilter/groupingFilter).
legacy := ddobf.NewObfuscator(ddobf.Config{})
oqLegacy, _ := legacy.ObfuscateSQLString(input)

// NEW (obfuscate_and_normalize) pathway: same constructor, but with
// ObfuscationMode set, dispatch goes to ObfuscateWithSQLLexer, which in turn
// invokes the go-sqllexer library configured with the agent's defaults
// (KeepPositionalParameter=false in production, which becomes
// WithReplacePositionalParameter(true) inside the lexer).
newer := ddobf.NewObfuscator(ddobf.Config{
    SQL: ddobf.SQLConfig{ObfuscationMode: ddobf.ObfuscateAndNormalize},
})
oqNew, _ := newer.ObfuscateSQLString(input)

Each pathway was run on two inputs: the raw query as it appears in pg_stat_activity (containing epoch), and the same query as Postgres stores it in pg_stat_statements (with epoch already rewritten to $1/$4, captured directly from the running container in Step 1).

Obfuscator pathway	Input from pg_stat_activity (raw)	Input from pg_stat_statements (Postgres-normalized)
Legacy pkg/obfuscate	... EXTRACT ( epoch FROM ... ) ...	... EXTRACT ( ? FROM ... ) ...
obfuscate_and_normalize (production defaults — keep_positional_parameter: false)	... EXTRACT ( epoch FROM ... ) ...	... EXTRACT ( ? FROM ... ) ...
obfuscate_and_normalize (with keep_positional_parameter: true)	... EXTRACT ( epoch FROM ... ) ...	... EXTRACT ( $1 FROM ... ) ...

In our datadog instance, the two queries we see in DBM (EXTRACT ( epoch FROM ... ) and EXTRACT ( ? FROM ... )) are the output of both the legacy pkg/obfuscate and the new obfuscate_and_normalize mode at its production default (keep_positional_parameter: false). The only configuration that produces different output is obfuscate_and_normalize with keep_positional_parameter: true, and that variant also yields a divergent pair (EXTRACT ( epoch FROM ... ) vs EXTRACT ( $1 FROM ... )). The split therefore exists in every supported configuration; no obfuscation_mode setting is a workaround.

Proposed fix:

The agent should obfuscate EXTRACT(field FROM …) (and other functions whose first argument Postgres treats as a string constant) on the activity path the same way Postgres already does for pg_stat_statements. Concretely: when obfuscating a raw query coming from pg_stat_activity, replace the epoch/dow/doy/quarter/etc. field argument with the same placeholder Postgres' parser would produce ($N for obfuscate_and_normalize, ? for the legacy obfuscator). That way the activity-path and statements-path inputs collapse to identical text before the query signature is computed, and the two signatures we see today merge into one.

[eric-weaver]

Thank you for reporting this issue. We've identified and implemented a fix in go-sqllexer here. It's landed on datadog-agent here. This should release in datadog-agent v7.80 🙇