Query Fingerprints or Literal Text Diffs: A Debate for Agent SQL Regression

A Tuesday review queue held three agent rewrites of the same reporting query, each formatted differently and each carrying a new bind value. The text diff looked noisy, the join graph looked unchanged, and the reviewer had twelve minutes before a freeze window. None of the candidates touched writes, yet one rewrite moved a date filter from orders.created_at onto a denormalized snapshot column. The real question was not which assistant drafted the SQL, but which regression gate should fail the pull request.

This article treats that choice as a two-sided debate with evidence, a small runnable artifact, and a decision rule. The setting is PostgreSQL review for agent-written SELECT and constrained DML, not a claim about any particular production outage. Examples below are labeled proposals and unexecuted fixtures, not measured customer results.

Why agent SQL breaks naive regression gates

Agent-written SQL rarely arrives as a single canonical string, even when the logical plan is stable across attempts. Whitespace, alias names, literal formatting, and CTE labels change while the join graph and predicates stay equivalent. A gate that compares raw text therefore fails on harmless restyles and can bury the one predicate that actually moved. A gate that compares fingerprints can hide a literal that now scans an unbounded date range or an unparameterized IN list.

Review agents also outgrow the tests that only assert “the query still runs.” Runtime success does not prove that the accepted string is the same workload the team intended to keep. Fingerprints and literal diffs measure different failure modes, and mixing them without a rule produces both false red builds and silent plan drift.

Position A: normalize to a fingerprint, then compare

The fingerprint camp argues that reviewers should sign off on a workload identity, not on a pretty-printed string. Literals, comments, and ignorable whitespace are stripped or replaced, then a digest is compared to a committed golden. Equivalent restyles stay green; a join, filter, or projection change flips the digest and fails CI.

Evidence for this side is strongest on high-churn reporting SQL, where agents repeatedly rename aliases and reflow CTEs. PostgreSQL already thinks in normalized identities through pg_stat_statements.queryid, which collapses similar text so operators can track a workload rather than a file. Teams that store goldens as digests also keep review noise low when the only delta is formatting.

The cost is information loss. Two queries can share a fingerprint shape while one binds a day and the other binds a decade. Comments that document a lock-order constraint disappear. Dollar-quoted strings, INTERVAL literals, and array constructors are easy to mishandle in a homemade normalizer, which creates collisions the gate will not see.

Position B: keep literal text diffs, then require a human on every token

The literal-diff camp argues that agent SQL is an audit artifact, not only a plan. Every changed character is a chance to introduce a new table, a broader predicate, or a function with a different volatility. git diff on sqlfmt output is simple, explainable to compliance, and does not depend on a normalizer the team must maintain.

Evidence for this side is strongest on privileged DML, security-definer paths, and queries that embed business constants. A fingerprint that replaces 'pending' and 'closed' with ? cannot tell a status filter from an accidental cross-status scan. Reviewers who must reconstruct intent from a digest are slower than reviewers who read a three-line diff of the predicate list.

The cost is noise. Agents emit different pretty-printers, optional AS keywords, and unstable CTE names, so the diff becomes a formatting argument. Under time pressure, reviewers start rubber-stamping restyles and miss the one moved column. Literal gates also fight bind-parameter style, because '2026-09-17' and $1 are different text even when they are the same plan at runtime.

Artifact: a dual-gate fixture you can run on a replica

The proposal below is a regression fixture, not a production benchmark. It stores a golden fingerprint and a canonical text file, then fails with distinct exit codes so CI can apply different rules per query class. Label it unexecuted until a replica you control has loaded the schema.

# proposal: sql_regression_gates.py
from __future__ import annotations

import hashlib
import re
import sys
from pathlib import Path

COMMENT = re.compile(r"--.*?$|/\*.*?\*/", re.S | re.M)
LITERAL = re.compile(
    r"(?x)'(?:''|[^'])*'|\b\d+\.\d+\b|\b\d+\b"
)
AS_ALIAS = re.compile(r"\s+as\s+([a-z_][a-z0-9_]*)", re.I)
WS = re.compile(r"\s+")


def normalize(sql: str) -> str:
    text = COMMENT.sub(" ", sql)
    text = LITERAL.sub("?", text)
    text = AS_ALIAS.sub(r" as _a", text)
    return WS.sub(" ", text).strip().lower()


def fingerprint(sql: str) -> str:
    return hashlib.sha256(normalize(sql).encode()).hexdigest()[:16]


def load(path: Path) -> str:
    return path.read_text(encoding="utf-8")


def main() -> int:
    candidate = load(Path(sys.argv[1]))
    golden_sql = load(Path(sys.argv[2]))
    mode = sys.argv[3]  # fingerprint | literal | both
    fp_ok = fingerprint(candidate) == fingerprint(golden_sql)
    text_ok = normalize(candidate) == normalize(golden_sql) and candidate.count(";") == golden_sql.count(";")
    if mode == "fingerprint":
        return 0 if fp_ok else 10
    if mode == "literal":
        return 0 if candidate.strip() == golden_sql.strip() else 11
    if fp_ok and text_ok:
        return 0
    if not fp_ok:
        return 10
    return 12


if __name__ == "__main__":
    raise SystemExit(main())

Pair the script with a statement timeout and a read-only role on the replica. The commands below are a rehearsal sequence, not a claim that any host already ran them.

psql "$REPLICA_DSN" -v ON_ERROR_STOP=1 <<'SQL'
SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;
SET lock_timeout = '2s';
SET statement_timeout = '5s';
-- optional: confirm the role cannot write
SELECT current_user, inet_server_addr();
SQL

python sql_regression_gates.py candidate.sql golden.sql both
echo "exit=$?"  # 0 ok, 10 fingerprint miss, 11 literal miss, 12 shape-ok but text drifted

A second probe records whether the planner still sees the expected nodes after a fingerprint match. Keep this as a fixture EXPLAIN with COSTS OFF, because cost numbers move with cache and autovacuum and should not be the identity of the query.

EXPLAIN (COSTS OFF, VERBOSE FALSE)
SELECT o.id, o.total_cents
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.created_at >= DATE '2026-09-01'
  AND c.status = 'active';

Disclosure: This article was prepared as part of MonkeyCode's product outreach. A practical way to exercise both gates is to let a coding assistant propose candidate SQL, then run the script against a disposable replica. MonkeyCode's free model access and free server option can host that rehearsal loop without pointing the agent at the primary; they do not replace the decision rule below, and this article does not claim model names, token quotas, or hardware ratings.

Decision table for query class

Query class	Default gate	Fail closed when	Allow fingerprint-only when
Read-only report, no row-security predicates	both, warn on text drift	Join graph or filter digest changes	Alias and CTE restyles with identical EXPLAIN nodes
Parameterized OLTP lookup	fingerprint plus bind-arity check	Placeholder count changes	Formatter-only diffs
Privileged UPDATE/DELETE	literal text	Any token changes without human	Never
Queries with status literals or tenant ids	literal on the predicate list	Literal replaced by ? in the golden	None; those literals are the contract
Security-definer or search_path sensitive SQL	literal plus fully qualified names	Unqualified relation names appear	Never

Numbered rule a review bot can apply

1. Classify the file from path and the first statement verb before any model output is trusted. dml/ and security/ default to literal; reports/ may use fingerprints.
2. Run the replica with READ ONLY, lock_timeout, and statement_timeout so a bad candidate cannot wait on a lock or scan without a budget.
3. Compute the fingerprint and the literal result as separate exit codes, then map them through the table rather than a single boolean.
4. If the fingerprint matches and the literal drifts, require a human only when the class is privileged or literal-sensitive; otherwise record a restyle.
5. If the fingerprint misses, reject even when EXPLAIN still says Index Scan, because a moved predicate can keep the same node type while changing cardinality.
6. Store goldens as both golden.sql and golden.fp so a normalizer bug is visible as a dual mismatch instead of a silent collision.

Limitations the gates will not hide

Homemade normalizers are not PostgreSQL's parser. Dollar quotes, E'' escapes, WITH ORDINALITY, and jsonb literals can collapse two different strings into one digest. pg_stat_statements.queryid is safer than a regex, but it requires executing or parsing on a real engine and still ignores whether a bound range is one day or ten years.

Fixture EXPLAIN without ANALYZE will not catch a sequential scan that only appears after autovacuum lags. Literal diffs will not catch a logically identical query that switched from a partial index to a full index because statistics moved. Neither gate proves row-level security, because RLS depends on SET ROLE and session variables the file may not contain.

The dual-gate script also assumes one statement per file. Agents that emit batches, temp tables, or CREATE INDEX CONCURRENTLY need a splitter and a different rehearsal, which this article does not provide.

Who should not use this approach

Skip fingerprint goldens if the team cannot maintain a parser-quality normalizer or cannot run PostgreSQL in CI. Skip literal-only gates if the review queue is already flooded with formatter noise and reviewers have stopped reading diffs. Skip both if the agent is allowed to choose tables dynamically from a live catalog without a frozen contract, because there is then no stable golden to compare.

Teams without a replica that matches production extensions, collations, and search_path should not treat a green local gate as promotion evidence. The debate is about which identity to store, not about skipping rehearsal.

Closing the queue without a slogan

Agent SQL becomes engineering work when the merge rule is explicit: fingerprints guard workload shape, literal diffs guard privileged tokens, and the table decides which failure is blocking. The opening review queue needed that split more than it needed another restyled CTE. If a spare replica is useful while you wire the exit codes, MonkeyCode's free server option is one place to rehearse the suite before the freeze window starts.