DEV Community: Morgan Li

Masked Extracts or Local Draft Models: A Debate for Agent SQL Placement

Morgan Li — Tue, 13 Oct 2026 01:04:27 +0000

A platform team watched a checkout report stall after an agent rewrote a revenue query during a release review. The statement joined three large tables and omitted a selective predicate, so the planner chose a broad scan. An engineer cancelled the statement before it held locks, then recorded the draft path rather than the model brand. The remaining decision was where the next draft should run, and which data could leave the production boundary.

That incident is a composite scenario for this debate, not a measured outage report from a named company. It still matches a pattern that shows up whenever agents draft SQL against live catalogs without a placement rule. Better language models do not, by themselves, decide whether schema text may cross a network boundary. Software controls around placement, redaction, and rehearsal still determine whether a fluent draft is acceptable to run.

Two credible positions

Position A sends a masked catalog extract to a free hosted model, then rehearses the returned SQL on a free disposable server. Position B keeps all draft generation on a local model that remains inside the team trust boundary. That position uses an external server only as an execution witness that is loaded with synthetic rows. They disagree about which artifact may move, and about which system may see real object names.

Position A argues that hosted models often draft more fluent SQL when prompts include current table names and key relationships. A masked extract can remove row samples, comments, and high-sensitivity column names before that hosted call. A free server then provides an isolated planner and a permission set that production roles do not share. The claim is that separation of draft generation from production execution reduces lock risk more than an unrehearsed local draft.

Position B argues that column names, check constraints, and relationship graphs can still reveal product structure even after row samples are removed. A local model can read the live catalog through an internal tool and never transmit that text. The free server, if used at all, should receive only synthetic identifiers and the candidate statement. The claim is that an execution witness does not require the drafting model to sit outside the boundary.

Evidence that can actually be checked

Neither position is universally cheaper, safer, or more accurate, and this article does not report a benchmark. The useful evidence is qualitative and checkable by each team against its own written catalog policy. Hosted draft quality matters only when the team is allowed to export a defined schema subset. Local draft quality matters only when the team can actually run a model beside the catalog.

Rehearsal value depends on whether the disposable server has similar indexes, not on whether the server was free. The table below compares those placement inputs without ranking any vendor, model family, or hosting plan. Availability of a free hosted model and a free server can make Position A practical for unclassified practice catalogs. That availability does not change a contractual ban, and it does not prove that masked names are harmless.

Decision input	Favors masked hosted draft	Favors local draft model	Reject both external hops
Schema classification	Names are internal but not regulated	Names reveal unreleased product scope	Any catalog text is barred from export
Prompt contents	Row samples already stripped	Catalog text never leaves the host	A useful draft would require samples
Planner fidelity	Synthetic server can mirror indexes	Local database already mirrors indexes	No safe mirror exists this week
Bad-join failure cost	Rehearsal can catch it before production	Local explain can catch it before review	Write access cannot be prevented
Operational constraint	Hosted call and server are available now	Local model is maintained by the team	External terms are unacceptable

A placement workflow with numbered checks

The workflow below is a concrete placement procedure for the debate, and it can be run on an unclassified catalog. Each numbered step produces an artifact that a reviewer can inspect without trusting the agent's explanation. Commands are patterns for PostgreSQL-style rehearsal, and they should be adapted to the server version in use.

Classify the catalog extract before any prompt is built, and mark each table as exportable, mask-required, or local-only.
Build the extract from catalog metadata only, keeping names, types, and declared keys while dropping comments and sample values.
Score the extract with an automated gate that fails on missing labels, sensitive classes, or literals copied from logs.
Choose hosted placement only when the gate passes and a reviewer can read the exact payload before the call.
Rehearse on a disposable server with synthetic rows, a read-only role, and an explain plan captured before execution.
Record the model placement, extract hash, server identity, and explain output beside the statement hash for later review.

Artifact: a gate, a role, and four checks

The following Python is a proposal for the gate in step 3, and it is not a production policy engine. It has not been executed against a private catalog for this article, so teams should treat the output as a starting check. A passing result means the payload avoided three obvious failures, not that the schema is safe to export.

import re
from hashlib import sha256

SENSITIVE = {"email", "phone", "ssn", "api_key"}
LITERAL = re.compile(r"[A-Z0-9._%+-]+@[A-Z0-9.-]+\\.[A-Z]{2,}", re.I)

def gate(columns, payload):
    missing = [c["name"] for c in columns if "class" not in c]
    leaked = [c["name"] for c in columns if c.get("class") in SENSITIVE]
    literals = LITERAL.findall(payload)
    ok = not missing and not leaked and not literals
    digest = sha256(payload.encode("utf-8")).hexdigest()
    return {"ok": ok, "missing": missing, "leaked": leaked, "digest": digest}

A passing gate is necessary but not sufficient, because the rehearsal role still needs a database-side boundary. On PostgreSQL, a reviewer can create a read-only role and inspect the plan without granting write rights. The statements below are a pattern for a disposable server, not a claim that every host uses the same defaults. Replace the dates and table names with fixtures that contain no production values before anyone runs them.

CREATE ROLE agent_reader NOLOGIN;
GRANT CONNECT ON DATABASE rehearsal TO agent_reader;
GRANT USAGE ON SCHEMA public TO agent_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO agent_reader;
SET ROLE agent_reader;
EXPLAIN (FORMAT TEXT)
SELECT o.id
FROM orders AS o
JOIN payments AS p ON p.order_id = o.id
WHERE o.created_at >= DATE '2026-10-01';

PostgreSQL documents EXPLAIN and privilege rules in the current official reference, which is the primary source for those commands. A team should read that version's pages before copying the grants, because default privileges can differ by release. If the disposable server cannot host a faithful index set, label the plan as a syntax witness rather than a cost forecast.

Run four checks before anyone trusts the helper, and record the returned dictionary rather than a screenshot of a chat. These checks are expected behaviors for the proposal, not a transcript of an executed private run. Keep the fixture columns small enough that a reviewer can compare the dictionary with the source list by eye.

Pass a payload that lists only exportable columns with classes present, and expect the ok flag to be true.
Fail a payload that includes a column classed as email, and expect that column name to appear under leaked.
Fail a payload that contains an email literal in the text, and expect the gate to reject that payload.
Fail a column entry that omits its class field, and expect that column name to appear under missing.

Where a free practice surface fits

Some teams lack a maintained local model, yet they still need a place to practice agent SQL without touching production. In that setting, MonkeyCode's free model access and free server option can serve as the practice surfaces for that gated path. Disclosure: This article was prepared as part of MonkeyCode's product outreach. Readers should confirm current terms before relying on either surface, because this article does not treat availability as permanent.

This article does not name models, quotas, hardware, or duration, because those facts were not supplied as stable specifications. A free option can lower the cost of trying the workflow on an unclassified catalog that already passed the gate. It cannot replace the classification gate, and it should not be treated as a permanent control boundary. If the gate fails, the reviewer keeps the draft local and still uses an isolated database for the explain step.

Limitations and who should skip this

Masking column names does not anonymize a schema, because unique table names can reveal a launch, a partnership, or a fraud rule. Position A is therefore unsafe for catalogs whose names are themselves confidential, even when row samples never appear in the prompt. A free server with empty statistics will mislead cost comparisons, so its plan cost should not be treated as a production forecast. The sample gate also misses many secret shapes, including internal identifiers that do not resemble email addresses.

Teams should not use the hosted path when contracts forbid external inference or when classification labels are incomplete. They should also skip that path when the agent can obtain write credentials on any reachable database. This debate is the wrong tool if a human acceptance step cannot be staffed for every promoted statement. An automated promotion from a fluent draft to production sits outside the decision rule that follows.

Decision rule

Reviewers should choose the masked hosted draft only when three conditions hold together for that candidate statement. Every referenced object must be classified exportable before the payload is assembled for a hosted call. The gate report must show no sensitive columns and no literals, and the rehearsal server must stay isolated from production. Choose the local draft model when any object is local-only, or when the team cannot review the exact payload before the call.

Reject both external hops when write access cannot be prevented or when no faithful non-production mirror exists. The rule prefers the stricter placement whenever the evidence is mixed, incomplete, or still waiting on classification. Fluency is not evidence of safety, and a free practice surface is not evidence of an acceptable boundary. The recorded extract hash, role grant, and explain output are the acceptance package that later reviewers should trust.

If review notes already capture plans but not placement, add the extract hash before the next agent SQL change. That single field makes the debate auditable, whether the draft model ran locally or on a free hosted practice path. Teams with an export clearance can compare both placements on fixtures and keep the stricter result when the plans disagree.

Grant Matrices or Query Rewrites: A Debate for Agent SQL Least Privilege

Morgan Li — Sun, 11 Oct 2026 21:22:04 +0000

This account previously published a night-shift story about an agent query that locked a production table. The lock was the visible failure, but the missing debate was which privilege path allowed the statement to run at all. A reporting agent can still ask for a temporary superuser role when a narrower view would answer the question. Reviewers need an explicit rule before they choose between a new grant and a query rewrite.

Why least privilege is the debate

Recent developer discussion has stressed that model quality can improve while the surrounding software controls stay unchanged. That signal fits agent SQL, because a sharper model still runs inside roles, views, and grants that humans own. This article does not cite vendor benchmark scores, since no primary measurement was supplied for this draft. The practical question is which control should reject a dangerous statement before it touches shared data.

Position one: expand a grant matrix

A grant matrix treats the agent as an untrusted author and the role catalog as the system of record. Each workflow receives a dedicated login, a narrow schema usage grant, and explicit table or view rights. The matrix records who requested the right, which statement shape needs it, and when the grant should expire. Evidence for this position comes from ordinary privilege errors rather than from model confidence scores or prose explanations.

Supporters argue that query rewrites can hide missing rights until a later report needs one more column. A reviewed matrix makes that gap visible in role table grants before the agent is allowed to run. The operational cost is real, because every new column, sequence, or function can demand another reviewed grant. Teams that keep reporting schemas stable usually absorb that review cost better than teams shipping daily migrations.

PostgreSQL already exposes the evidence through catalog views, so the matrix does not depend on a new engine. A reviewer can compare requested relations with information_schema.role_table_grants and reject any right that lacks an owner. Sequences and functions need separate checks, because table grants do not cover nextval or routine execution. Primary documentation for GRANT and privilege types lives in the current PostgreSQL docs, which should be read for the exact major version in use.

Position two: rewrite onto an approved surface

A rewrite position refuses new grants and forces the agent to target views, functions, or procedures that already exist. The database owner defines those objects, and the agent receives only EXECUTE or SELECT on the approved surface. This approach reduces the chance that a generated statement invents a new path through a base table. The main tradeoff is flexibility, because a useful business question may have no approved object yet.

Evidence here is the diff between the drafted SQL and the allowed surface, which a reviewer can inspect. If the rewrite still needs a new column, the correct response is a schema change, not a silent grant. Rewrites fail closed when the catalog lacks an object, which is often safer than a broad role. They can fail open in practice when someone later adds a security definer routine without a fresh review.

View design matters, because a view can leak rows if the owner bypasses the caller's privileges. PostgreSQL documents security_invoker views, and teams should confirm that option on the version they actually run. A rewrite that ignores owner rights is not least privilege, even when the agent login looks narrow. The safer rewrite is boring: stable names, explicit column lists, and no dynamic schema search path.

CREATE VIEW

Evidence that can actually decide the debate

Decision input	Favors grant matrix	Favors query rewrite	Do not treat as proof
Same report shape runs daily	Rights can be reviewed once and expired on a calendar	An approved view already matches the shape	Model says the SQL looks safe
Schema migrations land daily	Matrix churn may exceed review capacity	Rewrite waits for a reviewed object	A passing local syntax check
Statement needs nextval or EXECUTE	Matrix must name sequence and routine rights	Rewrite should call a procedure instead	Table SELECT alone
Agent asks for superuser or BYPASSRLS	Reject and record the request	Reject and keep the approved surface	Free-tier availability
Reviewer cannot name the data owner	Stop, because a grant has no accountable owner	Stop, because a view owner is also unclear	A green dry run on empty tables

The table is a decision aid, not a measured benchmark, and every cell should be checked against the target catalog. Empty-table success is weak evidence, because missing privileges sometimes appear only after a sequence or a policy is touched. A language model can fill the first draft of this table, but it cannot certify the catalog. Server evidence still means the grants and the rewritten objects were applied on an isolated instance and inspected there.

Numbered rehearsal on a disposable database

The following workflow is a proposed test plan, and it was not executed against a production server for this article. Run it only on a disposable database that you can drop without notice or ticket approval. Keep the agent login unable to create roles, and apply every grant from a separate admin session. Record the SQL diff, the grant diff, and the final privilege query in the same change ticket.

1. Create the narrow role and the approved view

Create the role without superuser, createdb, or createrole, then point the agent at a view you own. Set security invoker only after you confirm that the option exists on the server version you run. Grant schema usage and view select, and do not grant the base table in this step. If view creation fails, stop and fix the owner path before you debate any extra rights.

-- Proposed example, not executed for this article.
-- Replace the password outside version control. Do not reuse this placeholder.
CREATE ROLE agent_reports LOGIN PASSWORD 'replace-me'
  NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;
CREATE SCHEMA IF NOT EXISTS reporting;
CREATE VIEW reporting.daily_orders
WITH (security_invoker = true) AS
SELECT o.id, o.created_on, o.status
FROM sales.orders AS o
WHERE o.created_on >= CURRENT_DATE - 7;
GRANT USAGE ON SCHEMA reporting TO agent_reports;
GRANT SELECT ON reporting.daily_orders TO agent_reports;

2. Stop drafts that ask for a privileged surface

Scan the drafted text for privilege verbs before you spend any review time on a rewrite. This scan is a heuristic filter, and it will miss obfuscated statements or comments that hide keywords. Treat a match as a stop condition, then ask the agent for a narrower statement shape. Do not treat a clean scan as permission to run the draft against any shared database.

# Proposed local check. It does not parse SQL and it is not a security boundary.
printf '%s\n' "$DRAFT_SQL" | grep -E -i -n \
  'pg_read_file|pg_sleep|bypassrls|security definer|copy |grant |revoke |alter role' \
  && echo 'stop: draft asks for a privileged surface'

3. Collect catalog evidence for both positions

Apply the view path first, then run the two catalog checks from an admin session and from the agent role. A successful view count supports the rewrite position only for that object and that role. A base-table error supports least privilege only if the error is insufficient privilege, not a missing relation caused by search path. Save both outputs beside the drafted SQL so the debate has server evidence rather than model prose.

-- Position A: rights the granting admin can see because that admin is the grantor.
SELECT table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'agent_reports'
ORDER BY 1, 2, 3;

-- Position B: connect as agent_reports. SET ROLE works only for a member or a superuser.
-- Expect the view to succeed and the base table to fail with insufficient_privilege.
SELECT count(*) FROM reporting.daily_orders;
SELECT count(*) FROM sales.orders;

4. Time-box a grant only after the rewrite loses

Choose the matrix only when the missing right is stable, owned, and narrower than the agent's original request. Put the grant and the matching revoke in one ticket, even if the revoke runs later by schedule. If you cannot name an owner or an expiry, the decision rule below says to stop. A query rewrite remains preferable when an existing approved object already answers the stated business question.

-- Proposed ticket pair. Do not leave the grant without the matching removal.
GRANT SELECT ON sales.orders TO agent_reports;
REVOKE SELECT ON sales.orders FROM agent_reports;

Decision rule

Use the rewrite position when an approved view, function, or procedure already returns the required columns and the agent can access it. Use the grant matrix when no such object exists, a named owner accepts the right, and the grant is narrower than superuser or bypassrls. Stop when the draft asks to create roles, read server files, copy data to a client path, or disable row security. Also stop when the only passing test used empty tables, because that result does not exercise sequences, policies, or default privileges.

If both positions seem viable, prefer the rewrite for one-off questions and the matrix for a report that will run unchanged for weeks. Revisit the choice after each migration, since a new column can invalidate either a view or a grant list. The rule is intentionally conservative, and it will block some useful drafts that an agent could otherwise run. That delay is an accepted cost, because a wrong privilege typically lasts longer than one delayed report.

Where a free model and a free server fit

Disclosure: This article was prepared as part of MonkeyCode's product outreach.

MonkeyCode is included here as an open-source project with operator-stated free model access and a free server option. Those two availability claims are operator-supplied, and this draft does not verify a quota, model list, hardware shape, or duration. A free model can draft the grant matrix, propose the view, and explain which decision-table row it chose. A free server can host the disposable database in the numbered plan, so the privilege checks do not land on production.

Use the model output as a proposal, then require the catalog queries above before anyone accepts a grant. Do not paste production connection strings into a hosted prompt, and do not reuse the sample password from the code block. If current project terms no longer include free model access or a free server, the workflow still stands on local PostgreSQL. Readers who want to rehearse the matrix can confirm today's MonkeyCode terms and repository before they copy any statement into a shared environment.

Limitations and who should skip this

This approach does not replace a SQL parser, a migration framework, or a formal access review. The text scan misses encoded statements, dynamic SQL inside functions, and rights inherited through group roles. security_invoker is version-dependent, and older servers need a different view design that you must verify locally. No latency, cost, or accuracy number in this article was measured against a live database service.

Skip the pattern when you cannot create a disposable database, cannot separate admin and agent logins, or cannot name a data owner. Also skip it for break-glass maintenance that legitimately needs a superuser, because hiding that work inside an agent prompt is worse. Teams that already generate grants from infrastructure code should extend that code, not add a parallel matrix in a chat log. If your only goal is to spend a free quota, this debate will not make the resulting SQL safer.

Closing the choice

Least privilege for agent SQL is a catalog problem with two honest answers, not a prompt-quality contest. Grant matrices make repeated rights reviewable, and query rewrites keep one-off questions on objects you already trust. The decision rule prefers the existing surface, allows a named expiring grant only when that surface is missing, and stops on superuser requests. Server evidence from the numbered checks should outrank a fluent explanation produced by any language model.

Fixture Databases or Disposable Servers: A Debate for Agent SQL Rehearsal

Morgan Li — Sat, 10 Oct 2026 17:47:20 +0000

A checkout service merged an agent-written refund update after a single-schema fixture returned three affected rows. The production planner resolved the unqualified table name through a session search path that the fixture never set. The statement updated a staging copy that shared the table name, and the refund job reported success. The finance auditors noticed the mismatch only after the nightly settlement export failed its reconciliation checks.

This scenario is a composite reconstruction, not a named customer incident, and it isolates one failure mode. Local fixtures proved that the statement could change three rows under a simplified catalog and role. They did not prove that the engine version, privileges, and search path matched the production session. The debate below asks where an agent-written mutation should be allowed to fail before any promotion.

The question the gate has to answer

Agent SQL tools often draft mutations faster than reviewers can replay them on a faithful engine. A fixture database is cheap to host, deterministic to reload, and easy to reset between tests. A disposable server is slower to provision, but it can expose planner, extension, and privilege behavior that a reduced fixture hides. The useful question is not which side sounds stricter, but which evidence the promotion gate can actually collect.

Position A: keep rehearsal inside fixture databases

Fixture advocates want every agent draft to fail inside versioned schema files and a local container. The catalog is small, the data is synthetic, and the test does not depend on a network or a shared host. Reviewers can diff the fixture files and the expected row counts in the same pull request. That position is strongest when the workload is read-only, the dialect is narrow, and production extensions are already mirrored in the fixture.

The weakness of this position is catalog fidelity, not the convenience of resetting a local container. A single-schema SQLite file will not reproduce PostgreSQL search path resolution, partial indexes, or role grants. A container that loads only the happy-path tables can still hide a view, a rule, or a trigger. Passing a fixture then becomes evidence about the fixture, not evidence about the engine that will execute the statement.

Position B: rehearse on a disposable server before promotion

Server advocates want the same statement to run against an engine that matches the production major version and the required extensions. The server should be disposable, loaded only with synthetic rows, and destroyed after the rehearsal run. Network distance and setup time are accepted costs because a wrong schema resolution is more expensive than a slower check. This position is strongest for data mutations, dynamic search paths, and catalogs that fixtures routinely simplify.

The weakness is operational scope, because a hosted server can leak secrets if connection strings are copied from production. It can also create false confidence if its version, extensions, or roles still differ from the target. A green run on the wrong major version is not stronger evidence than a green run on a fixture.

Evidence worth scoring

The decision rule needs evidence columns that either side can satisfy without stating a product preference. The table below is a proposed review rubric, not a benchmark taken from a measured lab. Scores in that table are ordinal labels for a review meeting, not percentages from a production study. A missing cell means the promotion gate cannot claim that class of evidence for the current draft.

Evidence	Fixture databases	Disposable servers
Catalog objects under test	Only what the fixture file creates	Whatever the rehearsal role can see
Search path and role grants	Often omitted or hard-coded	Set in the session before execution
Extension and version match	Easy to skip	Required before the run counts
Reset and reproducibility	Strong, because files are versioned	Weaker, unless the load script is versioned
Data-handling risk	Low if rows are synthetic	Higher if any production extract is used
Time to first failure	Usually shorter	Usually longer

A numbered rehearsal workflow

The numbered steps below are an unexecuted example for a PostgreSQL mutation rehearsal gate, not a timed study. They do not claim measured timings, and they assume a reviewer can run Docker on a local machine. Replace the database names, host ports, and rehearsal credentials before any real use of these commands.

Pin the engine and refuse to continue when the server version does not match the agreed major version.
Load only synthetic rows from a versioned script, and fail if the script contains a production connection string.
Set the rehearsal role, the search path, and a statement timeout before the agent statement is submitted.
Execute inside a transaction, capture row counts and notices, then roll back unless a later human step promotes the change.
Record which evidence columns were actually satisfied, and block promotion when any required column is missing.

A local fixture can still own the load step and the rollback step when the catalog is already complete. A disposable server should own the version pin and the session contract when the statement is a mutation. The gate should store the rollback output next to the SQL draft so the reviewer sees engine evidence, not only model commentary. Comments in the examples mark them as unexecuted, so nobody should treat the sample password as a real secret policy.

-- Unexecuted example: session contract before any agent mutation.
BEGIN;
SET LOCAL statement_timeout = '3s';
SET LOCAL search_path = app, public;
SET LOCAL ROLE rehearsal_writer;
-- Paste agent SQL below this contract, never before it.
-- ROLLBACK is the default end state for rehearsal.
ROLLBACK;

# Unexecuted example. Replace MAJOR with the production major version before running.
docker run --rm --name agent-sql-rehearsal \
  -e POSTGRES_PASSWORD=rehearsal-only \
  -e POSTGRES_DB=rehearsal \
  -p 55432:5432 "postgres:${MAJOR}"
psql 'postgresql://postgres:rehearsal-only@localhost:55432/rehearsal' \
  -c 'SHOW server_version;'

-- Unexecuted fixture that makes the composite failure visible.
CREATE SCHEMA app;
CREATE SCHEMA staging;
CREATE TABLE app.refunds (id int PRIMARY KEY, status text);
CREATE TABLE staging.refunds (id int PRIMARY KEY, status text);
INSERT INTO app.refunds VALUES (1, 'open'), (2, 'open'), (3, 'open');
INSERT INTO staging.refunds VALUES (1, 'open'), (2, 'open'), (3, 'open');

# Unexecuted example: list leading mutation statements for a manual qualifier check.
grep -nE '^(UPDATE|DELETE FROM|INSERT INTO) ' draft.sql

How to read a failed rehearsal

A failed run is useful only when the log shows the session state that produced it. Treat a missing notice, a missing role name, or a missing rollback marker as an incomplete test rather than a pass. Do not widen the statement timeout to hide a lock, because that change answers a different debate about budgets and caps. Record the failing statement text exactly as submitted, including any schema qualifier the agent omitted before execution.

Confirm the server version before you interpret a syntax error, because a version mismatch can mimic a dialect bug.
Print the search path and current user immediately after the failure, and store both lines with the SQL draft.
Compare the affected row count with the fixture expectation only after you know which schema resolved the table name.
Roll the transaction back before you retry, so a diagnostic query does not leave a partial mutation in the rehearsal database.

-- Unexecuted example: capture session state after a rehearsal failure.
SELECT current_user, current_schema(), current_setting('search_path') AS search_path;

Where free model access fits

Disclosure: This article was prepared as part of MonkeyCode's product outreach.

MonkeyCode is described in this outreach as an open-source project with free model access and a free server option. This article treats free model access and a free server option as availability claims supplied for the draft, not as a measured capacity sheet. A reviewer can use free model access to draft the mutation and to list assumptions, then ignore that commentary unless the server output agrees. The free server option is relevant only when a local container is unavailable and the documentation still confirms the offer.

The model should not be the acceptance witness for a mutation that can touch more than one schema. Ask it for a short assumption list: target schema, required indexes, roles, and statements it refused to qualify. Compare that assumption list with the session contract and with the rolled-back server output before anyone promotes the draft. If the model says the table is unique and the server shows two schemas with the same name, the server evidence wins.

That comparison is the same discipline whether the draft came from the assistant under test or from another tool. Reviewers should keep the product name out of the acceptance log and keep the server transcript in it. The draft source matters less than whether the rolled-back engine contradicted an assumption in the list.

Decision rule

Choose fixture databases when the statement is read-only and the fixture already creates every object it can touch. Choose a disposable server when the statement mutates data or uses unqualified names under a role the fixture never grants. Require both when the change is promoted to a shared environment, because each side covers a different failure. Reject the change if the server major version is unpinned, the rows are not synthetic, or the transaction does not roll back by default.

A practical tie-break helps when reviewers disagree about which missing column is acceptable for that draft. If a missing evidence column could change the target object or the lock footprint, the disposable server is mandatory. If the missing column could only change fixture convenience, the fixture is enough for that draft. Write the chosen column set into the pull request so the next agent run cannot silently drop it.

The function below is an unexecuted sketch of the tie-break, and it returns a boolean rather than a performance score. Callers must fill the evidence dictionary from server output, not from the model's own summary of that output. A false return means the draft stays in rehearsal, even if the generated SQL looks complete in review.

# Unexecuted example: promotion checklist. Labels are review notes, not measurements.
REQUIRED_FOR_MUTATION = (
    'version_pinned',
    'synthetic_rows_only',
    'search_path_set',
    'role_set',
    'rolled_back',
)

def allow_promotion(evidence: dict, statement_mutates: bool) -> bool:
    if not statement_mutates:
        return bool(
            evidence.get('fixture_objects_complete')
            and evidence.get('synthetic_rows_only')
        )
    return all(evidence.get(key) for key in REQUIRED_FOR_MUTATION)

Limitations and who should skip this

This workflow does not prove production safety, because a synthetic server is not production traffic, statistics, or concurrency. It does not estimate cost, and it does not replace grants reviews, backups, or a human approval for destructive statements. Quotas, model lists, server regions, and retention rules are outside this article and must be read from current project documentation before use. Anyone who cannot keep sample data synthetic should not point an agent at a hosted server.

Teams that lack a rollback default, a version pin, and a named rehearsal role should not treat a green model answer as acceptance. Operators who need a measured benchmark, a named model, or a guaranteed capacity number will not find those claims here. Readers who only need a local container should skip the hosted option and keep the session contract anyway. The composite opening is a teaching case, so it should not be cited as evidence that any vendor failed.

If the gate in your repository still accepts agent SQL from fixture row counts alone, compare that habit with the session contract above. The MonkeyCode project documentation is one place to check whether free model access and a free server option fit that gate today. Do that check before changing the promotion rule, and do not treat this article as a capacity guarantee. The decision remains the evidence columns in the rubric, not the assistant that drafted the statement.

Model Critique or Server Evidence: A Debate for Agent SQL Acceptance

Morgan Li — Fri, 09 Oct 2026 14:16:57 +0000

A Thursday review queue held one agent draft that joined orders, payments, and refunds for a weekly finance extract. The author had requested a seven-day window, yet the draft projected every column from all three source tables. Two reviewers debated the draft for several minutes before either would approve it for the scheduled job. One reviewer wanted a second model critique, while the other demanded execution evidence from a disposable server.

Both positions can be defended with operational evidence, yet they answer different questions about failure and exposure. A written critique can name a missing predicate, but only a server run shows the plan, the row count, and the locks. This article compares those gates, supplies a reproducible evidence worksheet, and ends with a decision rule for promotion.

The question that should frame the debate

The useful question is not which tool sounds more modern during a crowded afternoon review meeting. The useful question is which failure you fear, and which artifact can actually observe that failure. A missing date predicate is visible in the statement text, while a bad join order appears only after planning. Lock waits, temp file growth, and underestimated row counts appear only when a server executes the statement.

If your acceptance rule collects the wrong artifact, you will feel confident while the risky behavior remains untested. That mismatch, rather than model brand or server price, is the failure mode this debate should reduce. Headlines about model quality do not change the observation problem, because a fluent explanation is still not a plan. Treat news about newer models as a reason to retest your gate, not as a substitute for the artifact.

Position A: a structured critique as the acceptance gate

Position A says a second model pass should accept or reject the draft before any database connection opens. Supporters point to speed, repeatability, and the absence of lock risk while the statement remains plain text. A critique checklist can require named checks for predicates, join keys, selected columns, and explicit limits. The output can be stored beside the draft, diffed across revisions, and reviewed without copying table rows.

The evidence for this position is real, but it is evidence about the text rather than evidence about execution. A careful critique often catches a cartesian join, an unbounded scan, or a write hidden inside a function call. It cannot tell you whether the planner chooses a nested loop, a hash join, or a sequential scan. It also cannot show whether a selective predicate matches zero rows because of a type or timezone mistake.

Position A becomes weak when the same model family drafts the statement and then reviews its own work. Shared blind spots travel from the draft into the critique, especially around business calendars and late-arriving facts. A second pass remains useful as a prefilter, yet it should not be renamed into proof that the query is safe. Keep the critique artifact, and label it as a text review rather than as an execution result.

Position B: server evidence as the acceptance gate

Position B says no agent statement is accepted until a disposable server returns a plan and a bounded result. Supporters argue that production-shaped statistics reveal mistakes that a fluent critique will describe and then miss. A rehearsal session can set a statement timeout, a lock timeout, and a read-only transaction before the draft runs. Those settings do not make the draft correct, but they make a bad draft fail where you can afford it.

The evidence for this position is the plan, the estimates, the bounded output, and the error text from the session. A sequential scan on a fixture of known size is a fact, while a model warning about scans is only a hypothesis. Server evidence also exposes search path surprises, missing indexes, and casts that prevent index use. Those facts are difficult to invent from the SQL text alone, even when the critique sounds specific and confident.

Position B becomes weak when the fixture is tiny, stale, or shaped differently from the production tables. A clean plan on a small synthetic sample can hide a hash spill that appears on a much larger month. A hosted rehearsal server also does not grant permission to upload regulated rows, secrets, or unmasked customer attributes. If the fixture cannot legally leave your network, this position must be satisfied on infrastructure you control.

Where a free model pass and a free server participate

MonkeyCode offers free model access for the critique pass and a free server option for the rehearsal database.

Disclosure: This article was prepared as part of MonkeyCode's product outreach.

This draft states neither model names, token quotas, hardware size, nor how long either option remains available. Confirm the current terms in the product documentation before any promotion gate depends on them. The same worksheet still works if you replace that model access with another API and that server with local Postgres. Removing the product name should not remove the debate, the session limits, or the decision rule below.

Use the free options only for masked fixtures and for statement text, not for raw extracts from production. Record which option produced each artifact so a later review can see the boundary you actually tested. A hosted target is a collector of evidence, not a quieter copy of the production role. If the documented terms are narrower than this worksheet, follow the terms and shrink the fixture.

Numbered workflow: collect both artifacts, then decide

The following commands are a proposal for a local rehearsal, and they were not executed for this article. If you use a hosted free server, keep the SQL and replace only the connection target after you confirm its terms. Do not point this workflow at production, even with a read-only role, until a separate change process allows it. Start from an empty database so leftover objects from an earlier experiment cannot silently change the plan.

1. Start a disposable database

# Local throwaway password. Do not reuse it. Pin a supported image tag before relying on this.
docker run --rm --name agent-sql-rehearsal \
  -e POSTGRES_PASSWORD=rehearsal \
  -e POSTGRES_DB=rehearsal \
  -p 55432:5432 \
  postgres:16

The image tag above is an example for a local container, not a description of any hosted free server. Wait for the port to accept connections, then load a masked fixture you are allowed to copy. Keep the fixture row counts in a small file so later steps can detect drift before you trust a plan. Stop the container when the review ends so the data does not linger on a laptop.

2. Record fixture volume before planning

SELECT 'orders' AS relname, COUNT(*) AS fixture_rows FROM orders
UNION ALL
SELECT 'payments', COUNT(*) FROM payments
UNION ALL
SELECT 'refunds', COUNT(*) FROM refunds;

Compare those three counts with the ranges published by the team that owns the weekly finance extract. A fixture that is empty, duplicated, or missing a month will make both the plan and the critique look calmer than production. Write the counts into the evidence record before you open a model prompt or run EXPLAIN. If the drift exceeds the agreed threshold, rebuild the fixture and do not promote the draft.

3. Run the draft inside a read-only, time-bounded transaction

BEGIN READ ONLY;
SET LOCAL statement_timeout = '1500ms';
SET LOCAL lock_timeout = '400ms';
EXPLAIN (FORMAT JSON, COSTS, VERBOSE)
SELECT o.id, p.amount_cents
FROM orders AS o
JOIN payments AS p ON p.order_id = o.id
WHERE o.created_at >= DATE '2026-10-02'
  AND o.created_at < DATE '2026-10-09'
LIMIT 100;
ROLLBACK;

BEGIN READ ONLY rejects writes in that transaction, even if a later statement tries to insert rows. The timeouts bound waiting and execution, but they do not certify that the selected rows are the rows the author wanted. Save the JSON plan, the SQLSTATE if the statement fails, and the wall time reported by psql. If you need actual row counts, run a second statement with EXPLAIN ANALYZE only after the read-only plan is understood.

4. Ask for a critique without sending row samples

Review this SQL as text only. Do not request sample rows.
Checks: date predicate present, join keys named, selected columns minimal,
no DDL, no DML, no unbounded scan language, limit present.
Return a JSON object with pass, findings, and the fear each finding addresses.

Send the statement text and the checklist, not a CSV extract and not the JSON plan's private filter values if those values are sensitive. A critique that asks for example rows is a data-handling event, and it should fail the review even if the SQL looks sound. Store the model output next to the plan, using separate fields so neither artifact overwrites the other. If the critique and the plan disagree, keep both and let the decision rule below pick the stricter gate.

5. Score the record with an explicit function

# Proposal only: not executed against a live database or model for this article.
def promotion_allowed(record: dict) -> bool:
    writes = record["statement_class"] in {"dml", "ddl"}
    if writes and not record["server_evidence"]:
        return False
    if record["fear"] in {"plan", "locks", "row_estimate"} and not record["server_evidence"]:
        return False
    if record.get("regulated_columns") and record.get("rows_sent_to_model"):
        return False
    if record.get("fixture_drift_ratio", 0) > record.get("drift_limit", 0.2):
        return False
    if not record.get("human_reviewed"):
        return False
    return True

The function encodes the debate instead of hiding it inside a prompt that a later editor cannot diff. A 0.2 drift limit is a placeholder threshold for the example, not a measured standard from a production incident. Change that placeholder only when the owning team publishes a numeric range for the masked rehearsal fixture. Run the function in your test suite against the cases in the next section before you trust it in review.

Evidence map and proposed cases

Feared failure	Critique can observe it	Server can observe it	Acceptance rule
Missing date predicate	Yes, from the text	Yes, as an unexpectedly large scan	Either artifact may block; do not accept on silence
Bad join method	Only as a guess	Yes, in the plan nodes	Require server evidence
Hidden write	Sometimes, if the syntax is visible	Yes, read-only mode errors	Require server evidence
Lock wait	No	Yes, when `lock_timeout` fires	Require server evidence
Rows copied into a prompt	No	No	Fail closed; neither gate excuses the copy
Fixture drift	No	Only if you counted rows first	Discard the plan and rebuild

Use the table as a test oracle for promotion_allowed, not as a slogan to paste into a model prompt. The following cases are proposed inputs, and they illustrate the rule without claiming a measured pass rate. A read with fear plan, server evidence present, no regulated columns, drift 0.05, and human review returns true. A write without server evidence returns false, even when the critique field says the statement looks safe.

A regulated extract that sent sample rows to the model returns false, regardless of how clean the plan looks. An empty fixture with drift above the limit returns false, because the plan would describe the wrong data set. These cases are small on purpose, so a reviewer can implement them in minutes and argue about the rule rather than about a framework. Add a case from your own incident archive only when that archive can be shared inside the team.

Decision rule

Name the feared failure before you choose a tool, and write that name into the evidence record.
If the statement writes, changes objects, or calls a function you have not classified, require server evidence on non-production data.
If the fear is a plan shape, a lock, or a row estimate, a critique may prefilter the draft but cannot accept it.
If any column is regulated, do not upload those rows to a third-party server and do not paste samples into a model prompt.
If fixture drift exceeds the published threshold, discard both the plan and the critique's confidence language.
Promote only when the collected artifact matches the fear, the session limits match the script, and a human has read the record.

Apply the six checks in order, and stop at the first failed check instead of averaging them into a soft score. A soft score hides a regulated-data failure behind a fluent critique and a fast rehearsal plan. The rule is intentionally strict about copies and writes, because those failures are expensive relative to a delayed review. Textual doubts can still be resolved by editing the draft and collecting a new pair of artifacts.

Limitations, and who should skip this approach

This approach does not estimate production latency, and it should not be cited as a benchmark of any model or server. A masked fixture can still leak rare identifiers, so legal review remains necessary even when the SQL session is read-only. Timeouts can abort the statement before the needed plan is printed, so a timeout is not itself a safe plan. Shared rehearsal servers can also be noisy, so a slow plan there is not automatic proof of a slow plan in production.

Skip the hosted free server when the fixture cannot leave a private network, or when your policy forbids third-party compute. Skip the model critique path when the statement contains secrets, customer keys, or unpublished financial figures in literal predicates. Skip the whole worksheet if the only available credentials can also reach production, because a pasted connection string defeats the rehearsal boundary. Teams that need a change-advisory approval for every query should keep this method in a lab until that process explicitly allows it.

Closing

Before the next promotion meeting, run one unsettled draft through the six checks and keep the artifact that matches the feared failure. If those free collectors are currently available to you, use them only as described above after you confirm the live terms. That comparison is useful when it changes a decision, and it is noise when it merely adds another confident paragraph. Leave the production role untouched until the written record shows which gate actually observed the risk.

Session Budgets or Driver Caps: A Debate for Agent SQL Exploration

Morgan Li — Thu, 08 Oct 2026 09:49:39 +0000

A composite warehouse incident, rather than a claimed personal outage, is enough to frame this operational choice. An exploratory agent drafted a wide analytical query against a shared reporting database during an ordinary afternoon. The statement sorted a large fact table and held one pooled connection much longer than the dashboard jobs expected. Waiting sessions alone exhausted the pool, so a production lock was not required for the queue to stall.

Teams that let agents explore SQL face the same narrow question after that kind of stall. Should the database session carry the budget, or should the client driver refuse excess work first? Both positions are credible, and both fail in specific, observable ways that a small fixture can expose. The rest of this note compares those positions, then gives a measurement workflow you can rerun.

Two positions, stated without slogans

A session budget is a set of PostgreSQL parameters applied to the connection before the agent statement starts. Typical controls include statement_timeout, lock_timeout, and idle_in_transaction_session_timeout, and each value is a millisecond bound on the session. The server cancels the statement, or the idle transaction, when a bound is crossed, regardless of which client sent the work. That independence is the main evidence for this position, because agents often bypass a careful application wrapper.

A driver cap is a limit enforced in the client before, or while, rows are fetched from the server. Examples include a read-only transaction flag, a client-side query timeout, and a hard maximum on fetched rows. The appeal is precision, because the tool can stop a result stream without waiting for a server timeout to elapse. The weakness is coverage, because any second client, console, or retry path can skip the same wrapper.

These positions are not cosmetic variants of the same control, and the difference shows up in timing. A session budget still lets the server start the work, and only then aborts the statement. A driver cap can refuse further fetches, yet the server may already have produced a batch of rows. Measuring that split matters more than preferring whichever control happens to be easier to configure locally.

Evidence that should settle the argument

Evidence should come from a fixture you control, not from a vendor latency chart or a single anecdote. The useful observations are which bound fires, how many rows leave the server, and whether the pool slot is released. Cost estimates alone do not settle this debate, and neither does a successful run against a tiny sample. A query can be cheap to plan and still pin a connection while a client slowly drains a wide result.

PostgreSQL documents statement_timeout as a client-connection setting that aborts any statement exceeding the configured millisecond limit. The same manual chapter documents lock_timeout and idle_in_transaction_session_timeout as separate bounds that use different failure triggers. A single timeout therefore does not cover lock waits, long statements, and abandoned transactions at once. I am using that manual as the primary description of server behavior, and I am not claiming a hosted benchmark.

Primary source: Client Connection Defaults

Where a free draft host fits

Disclosure: This article was prepared as part of MonkeyCode's product outreach.

The operator describes MonkeyCode as an open-source project that offers free model access and a free server option. I am not restating a token quota, a model name, a hardware size, or a duration in this note. Those details were not attached as a primary source, and product terms of that kind change without notice. Check the current project documentation before you plan any workload around a number you saw elsewhere.

In this workflow, free model access is only a drafting aid for two candidate statements, not a safety judge. The free server option is only a disposable PostgreSQL host for the fixture, useful when a local container is inconvenient. Neither availability claim replaces the session budget or the driver cap, and neither claim decides the debate. If those options are unavailable, run the same steps on any Postgres instance you are allowed to reset.

Measurement workflow

The examples below are unexecuted proposals, and they are labeled so they are not mistaken for measured results. They use a tiny table so you can see a cancellation path without scanning a real warehouse table. Replace the connection string with a database you are allowed to reset, and never point the script at production. A passing local run still needs the decision table, because one green script does not rank the two positions.

1. Create a resettable fixture

Create the fixture table, load twenty thousand wide rows, and collect planner statistics before either probe starts. The payload column is intentionally wide, so a careless projection costs memory even when the row count looks modest. Identity keys keep the insert simple, and the region column gives the grouped query a small, stable cardinality. Run ANALYZE so the planner is not the accidental source of a timeout you meant to attribute to data volume.

-- Unexecuted example. Run only on a disposable database.
DROP TABLE IF EXISTS explore_events;
CREATE TABLE explore_events (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  region text NOT NULL,
  payload text NOT NULL,
  occurred_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO explore_events (region, payload)
SELECT
  CASE WHEN g % 4 = 0 THEN 'east' ELSE 'west' END,
  repeat('x', 200)
FROM generate_series(1, 20000) AS g;

ANALYZE explore_events;

2. Record a session budget on a fresh connection

Open a fresh connection, apply three local timeouts, and expect a cancellation rather than a quiet success. The sleep call is a synthetic delay, so the bound can fire without depending on your disk or CPU speed. A completed statement is not evidence that the budget works, so keep adjusting until you observe the error. The error you want, when the bound is actually hit, is SQLSTATE 57014, which means query_canceled.

-- Unexecuted example. Expect query_canceled if the sleep exceeds the budget.
BEGIN READ ONLY;
SET LOCAL statement_timeout = '150ms';
SET LOCAL lock_timeout = '100ms';
SET LOCAL idle_in_transaction_session_timeout = '2s';

SELECT region, count(*), pg_sleep(1)
FROM explore_events
GROUP BY region
ORDER BY region;

COMMIT;

3. Record a driver cap on another connection

This client stops after fifty fetched rows, which is the concrete driver-cap claim under test today. It does not prove the server avoided work, because a named cursor may already have filled one batch. Capture pg_stat_activity during the fetch if you need to separate client silence from server idle time. Close the cursor by leaving the block, so an abandoned hold does not imitate a pool leak.

# Unexecuted example. Requires psycopg 3 and a disposable DSN.
import os
import psycopg

dsn = os.environ["EXPLORE_DSN"]
sql = """
SELECT id, region, payload
FROM explore_events
ORDER BY id
"""

with psycopg.connect(dsn) as conn:
    conn.execute("SET statement_timeout = '30s'")
    with conn.cursor(name="agent_probe") as cur:
        cur.itersize = 100
        cur.execute(sql)
        rows = []
        for row in cur:
            rows.append(row)
            if len(rows) >= 50:
                break
print(len(rows))

4. Compare one shell observation

Run the activity query while each probe is in flight, using a role that can read pg_stat_activity. Note whether the session remains active, or idle in transaction, after the client has stopped reading rows. In practice, that server-side row often reverses a preference formed entirely from the client application logs. Repeat the check for both probes, because a single snapshot can miss a session that already exited.

# Unexecuted example. Replace the DSN, and do not use a production role.
psql "$EXPLORE_DSN" -c "SELECT pid, state, wait_event_type, query_start
FROM pg_stat_activity
WHERE datname = current_database()
  AND pid <> pg_backend_pid();"

Decision table

Observation on the fixture	Favors session budgets	Favors driver caps	Practical reading
A second client can open a session	Yes	No	The server bound is mandatory
A statement ends quickly but streams many rows	Weak alone	Yes	Add a fetch cap
A client dies and leaves a transaction open	Yes, if the idle timeout is set	No	Set the idle-transaction timeout
You need the error in the server log	Yes	Usually no	Keep the server bound
The wrapper is the only permitted client	Still useful as a backstop	Yes for row stops	Use both, with the server last

Decision rule

Adopt session budgets whenever any client other than your wrapper can reach the exploration database at all. Add driver caps when the tool streams rows and a finished statement can still exhaust memory or pool time. Do not treat a driver cap as a substitute for statement_timeout, lock_timeout, or an idle-transaction timeout. If the fixture cannot show a cancellation or a stopped fetch, do not promote the control into a shared tool.

Use the server budget as the backstop even when the driver cap looks sufficient on one happy script. Retry paths, SQL consoles, and later agents are new clients, and they will not inherit yesterday's wrapper settings. A read-only transaction reduces write risk, but it does not by itself limit CPU, locks, or result size. Keep the three server settings even if the fetch loop already stops at a small row count.

Limitations

This fixture uses twenty thousand rows, so it will not reproduce production planner choices or cache behavior. A statement_timeout that fires here may never fire on a selective query with a huge projected payload. Named cursors reduce client memory, yet the server can still perform substantial work before the first fetch. Free model drafts can propose unsafe SQL, so a human should read both candidates before any execution.

I did not run these statements against a hosted service for this draft, and I am not reporting timings. I am also not reporting quotas, hardware sizes, or permanence for the free server option mentioned above. Availability of that option can change, and a shared host is the wrong place for private data or load generation. Roles that can write, or that can change global configuration, sit outside this read-only exploration workflow.

Who should skip this approach

Skip this approach if the workload is latency-sensitive OLTP and a canceled statement is worse than a slow one. Skip it if policy forbids session-level settings and you also cannot wrap every client that reaches the database. Skip it if the rows are production records and the only available host is a shared evaluation server. Skip it if the agent must apply migrations, because write probes need controls beyond read exploration.

Closing the choice

Session budgets and driver caps answer different failure modes, and the fixture above makes that split visible. Prefer the server bound when you cannot enumerate every client, and add the driver bound when row volume is the risk. Re-run the four steps after any change to pool size, timeout values, or the agent tool's fetch loop. If a local database is inconvenient, confirm the current free-server terms, then run only this disposable fixture there.

Schema Cards or Isolated Rehearsal: A Debate for Agent SQL Drafts

Morgan Li — Thu, 24 Sep 2026 20:15:23 +0000

The staging failure below is a reconstructed example, not a measured incident from this account's history. A checkout service asked an agent to draft a backfill after refunds.amount became a stored generated column. The prompt still held an older schema card that described amount as a writable numeric field. The draft update looked legal on that card and survived a short human skim in review.

Shared staging rejected the statement only after the agent had already consumed a full review cycle. The channel then split between people who wanted stricter cards and people who wanted a disposable database. Neither side had a written rule for when a paper review of the card was enough. This debate keeps both positions and ends with a rule you can apply before any promotion.

Two positions that both survive contact with SQL

A schema-card position treats the attached card as the only schema the agent is allowed to trust. The card lists tables, columns, types, nullability, generated flags, and the privileges of the drafting role. Reviewers compare the SQL text with that card and refuse execution while the draft remains open. The blast radius stays small, because a wrong statement never opens a live database connection at all.

An isolated-rehearsal position treats the same card as a hypothesis rather than as proof of executability. The agent may run the draft only on a database that holds synthetic rows and no production secrets. Every trial sits inside a transaction that ends with rollback, together with a short statement timeout. The useful output is an error class, not a guess about how the target server will reject the text.

What this debate refuses to rerun

This split is not a rerun of the contract-versus-catalog tool debate, which asks how an agent learns table shape. Here the card is already in hand, and the open question is whether text review is sufficient before promotion. Execution on shared staging is excluded from both winning paths in the decision rule that follows. The artifact is a hash plus a rolled-back SQLSTATE, not a preference for one metadata reading API.

Evidence you can check without a benchmark

PostgreSQL rejects an ordinary update that assigns to a stored generated column, which a thin card can hide. In PostgreSQL's documented error codes, that rejection is SQLSTATE 428C9, and you should confirm it on your major version. A card that omits the generated flag will approve text that a matching server is required to refuse. A rehearsal on a different major version can hide the same fault, so the server version must be pinned in the run record.

Catalog reads are not a substitute for that pin, because information_schema reflects the database you actually connected to. If the card was exported yesterday and the rehearsal catalog was built last month, the hashes will disagree. Disagreement is a hard stop condition, not a warning you silently override inside the agent prompt. The rest of this workflow is an unexecuted proposal you should run on disposable data before you trust it.

Step 1: Export a schema card and hash it

Start from a database role that can read metadata and cannot read any customer table rows. The export below keeps only the columns a reviewer needs in order to judge a write target. Pipe the copy to a hash so later runs can prove they used the same card. Store the hash beside the draft instead of pasting the whole catalog into every new prompt.

-- Unexecuted proposal. Confirm column names on your PostgreSQL version before use.
SELECT table_name,
       column_name,
       data_type,
       is_nullable,
       is_generated,
       generation_expression
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;

# Unexecuted proposal. REHEARSAL_URL must point at synthetic data only.
psql "$REHEARSAL_URL" -v ON_ERROR_STOP=1 -At -f card_export.sql | sha256sum

Step 2: Build a synthetic catalog that matches the hash

Apply only the DDL that the card describes, using invented keys and amounts rather than a production sample. Skip extensions, collations, and row-level policies that you have not copied on purpose into this catalog. After the load, recompute the hash and stop if it differs from the card hash in the pull request. A mismatched hash means you are rehearsing a different database than the one the agent was shown.

-- Unexecuted proposal. Synthetic rows only.
CREATE TABLE refunds (
  refund_id bigint PRIMARY KEY,
  order_id bigint NOT NULL,
  net_amount numeric(12,2) NOT NULL,
  fee_amount numeric(12,2) NOT NULL,
  amount numeric(12,2) GENERATED ALWAYS AS (net_amount - fee_amount) STORED
);
INSERT INTO refunds (refund_id, order_id, net_amount, fee_amount)
VALUES (42, 7, 20.00, 1.50);

Step 3: Run the draft inside a transaction that always rolls back

Give the agent a rehearsal role that can write the synthetic tables and nothing else at all. Set a local statement timeout so a bad join cannot occupy the session for the rest of the review. Execute the candidate text, capture the SQLSTATE, and roll back even when the statement succeeds cleanly. Success inside this wrapper means the text parsed and ran on this catalog, not that it is safe for production.

-- Unexecuted proposal. Always end in ROLLBACK.
BEGIN;
SET LOCAL statement_timeout = '3s';
SET LOCAL search_path = public, pg_temp;
UPDATE refunds
SET amount = amount - 5.00
WHERE refund_id = 42;
ROLLBACK;

On the table above, that update should fail because amount is generated always as an expression. Record 428C9 if your version returns it, and record the exact message if it does not. Do not edit the draft until the recorded class matches the failure you intended to catch. A timeout, a missing table, or a permission error is a different defect and needs its own row in the log.

Step 4: Score the two positions with the same run record

Use one row per draft so the debate does not depend on memory of the review thread. The columns below are deliberately boring, because a promotion meeting should be able to read them aloud. Fill them from the hash command and the rolled-back session, not from the agent's own summary. If a cell is unknown, the decision rule later in this article treats the draft as not ready.

Check	Schema-card only	Isolated rehearsal	Stop if
Card hash stored with the draft	Required	Required	Hash missing
Generated and identity flags present	Required	Required	Flag omitted
Write executed somewhere	Forbidden	Allowed on synthetic data	Shared staging was used
SQLSTATE captured	Not available	Required for writes	Class unknown
Version pin recorded	Optional	Required	Rehearsal version differs
Rollback proven	Not applicable	Required	Session committed

A decision rule for promotion

Choose the schema-card position when the statement is read-only and names only columns on the hashed card. Also require that the statement does not call a function with side effects or a volatile helper. In that narrow case, execution adds little evidence and a banned connection is the safer default. Require isolated rehearsal when the statement writes or targets a generated or identity column at all.

Also require rehearsal when the statement joins tables whose row counts are absent from the card. Refuse both options when the only reachable database is shared staging or production, then return the draft to a named owner. A named owner is a person who can change grants, not the agent that produced the SQL text.

Read-only does not mean harmless, because a select can still lock rows if the agent adds a locking clause. Treat FOR UPDATE, FOR SHARE, and any explicit lock statement as a write for this rule. Also treat DDL, COPY, and calls that insert into audit tables as writes even when the main verb looks like a query. The rule is conservative on purpose, and it will send some safe drafts through rehearsal that a senior reviewer might have waived.

Where a free drafting setup fits

Disclosure: This article was prepared as part of MonkeyCode's product outreach. MonkeyCode, supplied here as an open-source option, offers free model access and a free server for rehearsal. Use that pair only as the rehearsal role described here, with the hashed card attached to the prompt. Keep rollback as the default ending, and do not send production connection strings, dumps, or customer keys.

It does not replace the decision rule, and it should never receive a credential that can reach shared staging. The model can propose the update text, while the free server is only useful if it can run your pinned engine. Keep the agent on the rehearsal role so a confident review note cannot open a broader grant. If the free server cannot load an extension you depend on, stop and record that limitation instead of using shared staging.

A missing extension is a catalog mismatch, and the hash check is already designed to catch that class of drift. If your synthetic catalogs already exist, the free model access and the free server option can host the next write rehearsal. Do not treat that hosting convenience as evidence that the generated SQL is correct on your production major version. Confirm the version pin in the run record before anyone promotes the text beyond the rehearsal role.

Limitations that should stay visible

This workflow does not estimate cost, lock duration, or rows touched on a production-sized refund table. A three-second timeout on synthetic data says nothing about a sequential scan against a much larger refund table. Generated-column rules and identity rules vary across major versions, so a pin from last year is not current evidence. Free server capacity can also differ in collation, timezone, and installed extensions, which is why the hash remains mandatory.

The reconstructed checkout story is only a teaching frame, and it is not a customer result or a measured outage count. No latency, token, or accuracy figure is claimed here, because this account did not run a comparative benchmark for this draft. If your operator later supplies versioned product limits, cite those primary notes beside the step that depends on them. Until then, treat availability of a free server as a convenience, not as a guarantee that the option remains unchanged.

Who should not use this split

Skip the rehearsal half if policy forbids sending schema names to an external model, even when the rows are synthetic. Skip it if the workload needs an extension, foreign-data wrapper, or collation that the free server cannot load. Skip the card-only half if reviewers cannot reliably see generated flags, identity flags, and grants on the attachment. Skip the whole debate if the agent already holds a write credential on a shared database, because that credential breaks the rule.

Teams that promote SQL through a locked migration tool may already have a stronger gate than either position. They can still borrow the hash and the SQLSTATE log without adopting the full agent loop. Everyone else should keep the stop row in the table, since an unknown cell is cheaper than a committed staging trial. The next useful change is a stricter card, or a cleaner synthetic catalog, not a broader database role.

Stored Contracts or Live Catalog Reads: A Debate for Agent SQL Tools

Morgan Li — Wed, 23 Sep 2026 16:41:00 +0000

A reconstructed agent-SQL incident usually starts with a quiet catalog query rather than a dramatic production lock. The agent lists columns from information_schema, then writes a join that looks syntactically polite to reviewers. That join still touches a relation the product owner never intended to expose through the agent role. The failure is an interface design choice, not merely a weak language-model sample.

Teams that let models reach PostgreSQL tend to land on one of two tool designs. The first design publishes a small set of stored contracts and refuses any other statement text. The second design lets the agent read the live catalog and compose SQL under a constrained database role. Neither design is a branding exercise, and each one fails in a different, measurable way.

Position A: stored contracts only

A stored contract is a named function, view, or prepared statement with typed arguments and an owner-reviewed plan. The agent may call app.orders_for_account(uuid) and must not submit arbitrary SELECT text. Catalog browsing is revoked, so information_schema is empty for that role, and pg_catalog reads are limited to what the function owner already encoded.

This position treats SQL text as an untrusted interface, closer to an HTTP handler that never evaluates a query string. Reviewers can EXPLAIN a finite object set, grant EXECUTE only on those objects, and keep row filters inside the function body. Drift still happens, but it happens at review time, when a human changes a contract, not at 02:00 when the agent discovers a new column.

Position B: live catalog introspection

The opposing position argues that contracts rot as soon as the warehouse schema moves underneath them. Agents that cannot see columns, constraints, and foreign keys will guess identifiers, then fail closed or, worse, query the wrong relation. Live reads of pg_catalog and information_schema let the model bind names to objects that actually exist before it composes a statement.

This position treats the catalog as the only honest API for a changing analytical store. Onboarding a new fact table does not wait on a function review, and the agent can notice indexes that a stale contract document omitted. The cost is a larger attack surface: every readable relation becomes a prompt-visible target, including tables that exist only for backfill or legal hold.

What to measure instead of what to prefer

A debate that stays at the slogan layer will not survive the first schema freeze. Measure four quantities on a cloned database that uses production-shaped cardinality, not on a laptop fixture. Record them for both designs on the same task pack so the comparison is mechanical.

Unauthorized relation touch rate. Count statements whose parse tree references a relation outside an allowlisted schema or comment tag.
Identifier miss rate. Count runs that fail because a column or table name does not exist, including renamed objects.
Plan-shape surprises. Count queries whose EXPLAIN node set differs from the reviewed contract plan or from a stored baseline fingerprint of join types.
Review load. Count human minutes spent reading new SQL text versus reviewing a function diff with a locked signature.

Those four numbers decide the interface. A design that looks elegant in a design doc and then leaks PII tables, or blocks every schema change, is not winning; it is unmeasured.

A small, reproducible artifact

The following objects are labeled as a lab fixture, not as production-proven numbers. They make the two positions executable so a team can score the four measures on their own clone. Run them on a disposable database, never on a writer that serves customers.

-- Lab fixture: two schemas, one of which must stay invisible to the agent.
CREATE SCHEMA app;
CREATE SCHEMA hold; -- legal-hold copy, must not appear in agent tools

CREATE TABLE app.accounts (
  id uuid PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE app.orders (
  id uuid PRIMARY KEY,
  account_id uuid NOT NULL REFERENCES app.accounts(id),
  placed_at timestamptz NOT NULL,
  amount_cents bigint NOT NULL
);

CREATE TABLE hold.orders_archive (
  LIKE app.orders INCLUDING ALL
);

CREATE ROLE agent_reader LOGIN PASSWORD 'replace-in-lab-only';
GRANT USAGE ON SCHEMA app TO agent_reader;
GRANT SELECT ON app.accounts, app.orders TO agent_reader;
REVOKE ALL ON SCHEMA hold FROM PUBLIC, agent_reader;

Position A wraps the only legal read in a function whose search_path cannot wander into hold.

CREATE OR REPLACE FUNCTION app.orders_for_account(p_account_id uuid)
RETURNS TABLE(order_id uuid, placed_at timestamptz, amount_cents bigint)
LANGUAGE sql
STABLE
SECURITY INVOKER
SET search_path = pg_catalog, app
AS $$
  SELECT o.id, o.placed_at, o.amount_cents
  FROM app.orders AS o
  WHERE o.account_id = p_account_id
    AND o.placed_at >= now() - interval '90 days';
$$;

REVOKE ALL ON FUNCTION app.orders_for_account(uuid) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION app.orders_for_account(uuid) TO agent_reader;
REVOKE SELECT ON app.accounts, app.orders FROM agent_reader; -- force the contract

Position B instead hands the agent a catalog probe. That probe is convenient, and it is also how hold.orders_archive becomes visible if grants are even slightly too wide.

-- Catalog probe the unconstrained agent would run
SELECT n.nspname AS schema_name,
       c.relname AS relation_name,
       a.attname AS column_name,
       pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type
FROM pg_catalog.pg_class AS c
JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
JOIN pg_catalog.pg_attribute AS a ON a.attrelid = c.oid
WHERE a.attnum > 0
  AND NOT a.attisdropped
  AND c.relkind IN ('r', 'v', 'm')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2, 3;

A classifier can score agent output before the database sees it. The script below is a proposed harness, not an executed benchmark.

# proposed_harness.py — classify agent SQL against a contract allowlist
import json, sys
from pathlib import Path

try:
    import sqlglot
    from sqlglot import exp
except ImportError:
    raise SystemExit("pip install sqlglot")  # lab dependency only

ALLOW_FUNCS = {("app", "orders_for_account")}
DENY_SCHEMAS = {"hold", "pg_catalog", "information_schema"}

def tables_and_functions(sql: str):
    tree = sqlglot.parse_one(sql, read="postgres")
    rels, funcs = set(), set()
    for node in tree.find_all(exp.Table):
        rels.add((node.db or node.catalog or "public", node.name))
    for node in tree.find_all(exp.Anonymous):
        funcs.add(("app" if "." not in node.name else node.name.split(".")[0], node.name.split(".")[-1]))
    for node in tree.find_all(exp.Dot):
        pass  # keep the allowlist strict; unknown dotted calls fail closed
    return rels, funcs

def classify(sql: str, mode: str) -> dict:
    rels, funcs = tables_and_functions(sql)
    denied = [r for r in rels if r[0] in DENY_SCHEMAS]
    if mode == "contract":
        ok = funcs <= ALLOW_FUNCS and not rels and not denied
        return {"ok": ok, "mode": mode, "rels": list(rels), "funcs": list(funcs), "denied": denied}
    ok = not denied and all(r[0] == "app" for r in rels)
    return {"ok": ok, "mode": mode, "rels": list(rels), "funcs": list(funcs), "denied": denied}

if __name__ == "__main__":
    payload = json.loads(Path(sys.argv[1]).read_text())
    print(json.dumps(classify(payload["sql"], payload["mode"]), indent=2))

A short test plan keeps the debate honest. Save each case as JSON and run the harness in CI against both modes.

Contract call SELECT * FROM app.orders_for_account('...') should pass in contract mode and fail if the function is missing from ALLOW_FUNCS.
Direct SELECT amount_cents FROM hold.orders_archive should fail in both modes because hold is denied.
SELECT * FROM app.orders should fail in contract mode and pass in catalog mode only when app is the allowed schema.
A renamed column on app.orders should raise identifier misses in catalog mode until the clone is refreshed, which is the rotting-contract problem in reverse.

A workflow that keeps the debate on one clone

Use one masked clone for both positions so hardware and data shape stay constant. The steps below are a lab procedure, not a claim about any vendor quota, model name, or duration.

Restore a masked snapshot into a throwaway instance and create the app / hold split shown above.
Freeze a task pack of twenty natural-language questions that a support or finance agent would actually ask.
Run the pack once with only EXECUTE on contracts, and once with catalog reads plus SELECT on app.
Classify every statement with the harness, then EXPLAIN the statements that passed classification.
Score unauthorized touches, identifier misses, plan-shape surprises, and review minutes into a single table.
Keep the losing design’s traces; they are the regression pack for the next schema change.

When the clone and the harness are the bottleneck, a disposable coding environment is more useful than another slide. Disclosure: This article was prepared as part of MonkeyCode's product outreach. MonkeyCode’s free model access and free server option can host that review loop without pointing the agent at a writer, which is the only reason the product appears in this method.

Decision table

Condition on the workload	Prefer stored contracts	Prefer live catalog reads
Agent role can write, or statements run unattended	Yes	No
Schema changes more than once per week, reads only	Rarely	Yes, on a masked clone
PII, hold, or backfill schemas share the cluster	Yes	Only after those schemas are revoked and tested
Reviewers can `EXPLAIN` a finite function set	Yes	Weak, because text is unbounded
Identifier misses currently dominate failures	After publishing new contracts	Yes, until contracts catch up
You cannot clone or mask production-shaped data	Yes, smaller blast radius	No

A decision rule you can actually enforce

Use stored contracts when the agent can mutate data, see PII, or run without a human in the loop. Use live catalog introspection only on a masked clone, with a SELECT-only role, a schema allowlist, and a statement timeout that matches the clone’s budget. Even then, compile the final text through the classifier so hold, COPY, and session-mutation statements fail closed before they reach postgres.

Promote a catalog-mode query to production only by turning it into a contract. That promotion is the decision rule’s load-bearing step: unbounded text may explore, but only a named function with a reviewed plan may run near customer data. If a team cannot write that function, the query is not ready, regardless of how fluent the model sounded.

Limitations and who should skip this

This debate does not replace threat modeling for SECURITY DEFINER, because a sloppy definer function is a privilege escalation with extra steps. It also does not claim that catalog hiding equals secrecy; a determined reader with a broader role will still see objects. Teams without a masked clone should not enable catalog mode at all, because identifier accuracy on empty fixtures is not evidence.

Do not use this approach for engines without a stable catalog or without REVOKE that actually sticks. Do not use it to justify letting an agent emit DDL, LISTEN/NOTIFY side channels, or cross-database FDW reads. The harness parses a subset of SQL and will fail closed on dialects it does not understand, which is safer than pretending it is a full auditor.

If you already classify agent SQL on a disposable clone, running the same harness where free model access and a free server are available is enough of an experiment. Keep the four measures; drop the vendor if the unauthorized-touch rate does not move.

Advisory Locks or SKIP LOCKED: A Debate for Agent SQL Workers

Morgan Li — Tue, 22 Sep 2026 14:59:51 +0000

The following reconstruction is a composite queue-worker failure, not a report from a named production tenant. A checkout worker queue stalled after midnight when three agent-written jobs targeted the same unpaid-order slice. Each session selected candidate rows without a skip clause, then waited on locks that never yielded under replica lag. The parser accepted every statement, so the usual syntax gate never fired during the pre-promotion review.

The on-call thread split into two durable camps with different promotion rules for the same queue table. One camp wanted named advisory locks so overlapping agents would wait inside a single critical section. The other camp wanted SKIP LOCKED so workers would skip held rows and keep draining the backlog. Both positions are credible in PostgreSQL; they encode opposite answers to whether waiting is cheaper than progress.

Why agent-written SQL changes the locking debate

Agent-written SQL often looks locally correct while ignoring the other sessions that will run the same text. Tool-calling agents emit SELECT ... FOR UPDATE because that pattern appears in runbooks, reviews, and a large amount of training data. Concurrent copies of that pattern convert a queue into a convoy, especially when the agent also omits a session timeout. The operational question is not whether locking is required; it is which lock semantic an agent is allowed to promote.

Database-native evidence is stronger than a model confidence score for this class of defect. PostgreSQL documents session and transaction advisory locks in the explicit locking chapter, and it documents SKIP LOCKED as a row wait policy on SELECT. Those are primary behaviors, not folklore, and they fail in different ways when agents reuse them blindly. A promotion rule that cannot name one semantic is not a rule.

Position A: transaction-scoped advisory locks

Advocates treat pg_advisory_xact_lock as a named mutex the agent must request before touching business rows. The lock key becomes an explicit critical section, which is easy to grep in review and easy to wrap in one transaction. When the transaction ends, PostgreSQL releases the lock without a separate unlock call, which reduces leaked-lock incidents from forgotten cleanup paths. That property matters when the authoring agent is better at opening work than at writing ROLLBACK handlers.

This position is strongest when overlap is a correctness bug rather than a throughput nuisance. Nightly close, singleton reconciliation, and “only one agent may rewrite this partition” jobs match that shape. The cost is wait time: other agents block until the lock holder commits or rolls back, so a slow holder becomes a pause for every peer that uses the same key. Agents that hash unstable strings into the key recreate that pause under a new name.

The rehearsal below is labeled sample SQL and is not an executed production result:

BEGIN;
SET LOCAL lock_timeout = '2s';
SET LOCAL statement_timeout = '15s';
SELECT pg_advisory_xact_lock(hashtext('agent-job:billing-close'));
-- agent DML is inserted only after the lock is granted
ROLLBACK; -- rehearsal only; replace with COMMIT after sign-off

hashtext is a convenience, not a cryptographic guarantee, and collisions remain possible across poorly chosen names. Teams that take this path should keep a registry of lock keys beside the agent prompt. Unknown keys should fail closed, because an agent that invents a mutex is not locking the resource the reviewer had in mind.

Position B: `FOR UPDATE SKIP LOCKED`

The other camp argues that queue workers should never wait on a sibling row. FOR UPDATE SKIP LOCKED lets a session take the next unlocked row that matches the predicate, which keeps throughput alive when several agents drain one table. Reviewers can cite the SELECT reference instead of a remembered blog post, which is the kind of evidence this debate should prefer. Waiting is treated as a failure mode, not as a fairness feature.

This position is strongest when skipping a locked row is acceptable and duplicate processing is prevented by the row lock plus a status column. It is weaker when the business requires strict serial order, because skipped rows can be processed later by another worker. Agents also emit SKIP LOCKED without a supporting LIMIT, which can lock a much larger set than the author intended. A skip clause without a bound is still a wide lock.

BEGIN;
SET LOCAL lock_timeout = '1s';
SET LOCAL statement_timeout = '10s';
WITH next_job AS (
  SELECT id
  FROM agent_jobs
  WHERE status = 'queued'
  ORDER BY id
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
UPDATE agent_jobs AS j
SET status = 'running',
    locked_at = now()
FROM next_job
WHERE j.id = next_job.id
RETURNING j.id, j.status;
ROLLBACK; -- rehearsal only

A missing WHERE status = 'queued' remains catastrophic even with SKIP LOCKED in the statement. The skip clause does not replace predicate hygiene, a row limit, or a timeout. If the agent cannot state which rows it is willing to skip, the text is not a queue drain.

Evidence that should change the default

Advisory locks do not conflict with ordinary row locks, which surprises agents that treat “lock” as one global concept. Two sessions can hold a row lock and an advisory lock independently, so mixing both without a diagram creates false confidence during review. SKIP LOCKED never waits, which means a monitoring query that expects a blocked wait_event can report a healthy worker that is skipping the entire backlog. Absence of blocking is not proof of progress.

pg_locks and pg_stat_activity remain the ground truth during rehearsal, and an agent summary that says “looks safe” is not evidence. Capture wait_event_type, granted, and lock type before anyone promotes the text. If rehearsal cannot show either a granted advisory lock or a skipped-row update, the candidate statement is not ready.

SELECT pid, wait_event_type, wait_event, state, left(query, 120) AS query
FROM pg_stat_activity
WHERE datname = current_database()
  AND pid <> pg_backend_pid();

SELECT locktype, mode, granted, pid, relation::regclass
FROM pg_locks
WHERE database = (SELECT oid FROM pg_database WHERE datname = current_database());

Run those two queries from a third session while the candidate SQL is still inside an open transaction. Evidence collected after COMMIT is a postmortem, not a rehearsal. Agents that cannot keep the transaction open long enough for this inspection are not ready for promotion either.

A numbered rehearsal workflow

Use this sequence on a disposable database that already contains a realistic volume of queued rows. Do not point the authoring agent at a primary that lacks timeouts, and do not treat a dry parse as a concurrency test.

Freeze the candidate statement as a file, including a comment that names the intended lock semantic in one word: exclude or skip.
Reject any text that mixes pg_advisory_xact_lock and FOR UPDATE SKIP LOCKED in one transaction without a written split into two jobs.
Require SET LOCAL statement_timeout and SET LOCAL lock_timeout in the same transaction as the lock or the UPDATE.
Execute the file under BEGIN, inspect pg_locks, then force ROLLBACK and record rows touched.
Repeat the same file from two concurrent psql sessions and capture whether the second session blocked or progressed.
Promote only when the observed overlap matches the semantic named in step 1, not the comment the agent generated after the fact.

Commands for the concurrent check, labeled as a local rehearsal sketch:

# terminal A
psql -v ON_ERROR_STOP=1 -f rehearsal_worker.sql

# terminal B, started within one second
psql -v ON_ERROR_STOP=1 -f rehearsal_worker.sql

Keep both invocations on the same frozen file when testing exclusion and when testing skip behavior. Agents sometimes emit “unique” advisory keys that still hash together, and that collision only appears when two sessions use the same text. If the second session blocks on a queue drain, the semantic is wrong even if the SQL is pretty.

Where a second-pass model review fits

Static gates catch missing timeouts; they do not catch a wrong lock semantic chosen with fluent comments. A useful extra step is to send the frozen SQL, the intended semantic, and the lock-key registry to a review model that is not the authoring model. The review prompt should ask for a verdict of exclude, skip, or reject-and-split, plus a PostgreSQL reason, and it should refuse to rewrite the query.

Disclosure: This article was prepared as part of MonkeyCode's product outreach.

MonkeyCode is an open-source project with free model access and a free server option, which is enough to host that second pass without folding it into the authoring loop. Treat it as a review sidecar: the model should not receive production row data, and it should not execute SQL. If the sidecar disagrees with concurrent psql evidence, the database evidence wins and the statement stays blocked. The sidecar is optional for teams that already freeze SQL and run two-person review; it is not optional for teams that let an authoring agent promote its own text.

Decision table

Observed shape	Prefer advisory xact lock	Prefer `SKIP LOCKED`	Reject promotion
Singleton job where overlap is a correctness bug	Yes	No	Mixing both semantics
Queue drain where progress under contention matters	No	Yes, with `LIMIT`	`SKIP LOCKED` without `LIMIT`
Strict FIFO business order	Yes, or a single worker	No	`SKIP LOCKED` claiming FIFO
Unknown lock key or missing timeout GUC	No	No	Yes
DDL, `LOCK TABLE`, or cross-database lock claims	No	No	Yes

The table is a promotion filter, not a style guide. Rows in the reject column are not “needs a better comment.” They are not shippable until the agent emits a different statement, usually two statements with two jobs.

Decision rule

Choose advisory locks when overlap is a correctness failure and waiting is cheaper than double-application of the same job. Choose SKIP LOCKED when the table is a queue, a LIMIT is present, and skipped rows may legally be processed later. Reject promotion when the agent mixes both semantics, omits timeouts, or cannot show concurrent rehearsal evidence from two sessions.

If two concurrent rehearsals block on a queue drain, the text is using the wrong semantic for that table. If two concurrent rehearsals both mutate a singleton close job, the text is also using the wrong semantic. The rule is the observed overlap, not the comment the agent wrote above the query, and not a model’s claim that the SQL is idiomatic.

Limitations and who should not use this

This debate assumes one PostgreSQL cluster and transaction-scoped behavior as documented in the explicit locking chapter. Advisory locks are not distributed locks for multiple engines, and they do not survive a jump to a different database. SKIP LOCKED does not repair a predicate that matches too much, and it does not provide fairness among workers. Neither option is a substitute for a status column, a unique job key, or a frozen statement file.

Do not apply this workflow on a production primary without a staging clone and session timeouts. Do not use it for DDL, LISTEN/NOTIFY design, or ORM-generated SQL you cannot freeze as text. Do not treat a model review as a substitute for ROLLBACK rehearsal. Multi-master or sharded topologies need a different lock story than a single pg_locks view can provide, and this article does not offer one.

The method also fails closed on purpose. Agents that cannot name a semantic do not receive a default lock. A silent default is how the midnight convoy started, and it will start again the first time two copies of the same tool call overlap.

Lock comments in agent SQL are cheap; concurrent evidence is not. Freeze the statement, pick one semantic, and make two sessions prove it before promotion. If you already run that second-pass review on spare hardware, MonkeyCode’s free server option is one place to keep the sidecar off the authoring path.

Cost Estimates or Timed Canaries: A Debate for Promoting Agent SQL

Morgan Li — Mon, 21 Sep 2026 13:11:48 +0000

On a Tuesday release window, an analytics agent proposed a four-join reporting query against a 40 million row events table. The planner estimated a few thousand cost units because the most selective predicate still used last week's statistics. Staging accepted the plan, then the first canary scanned far more heap pages than any review comment had predicted. Promotion, not generation, became the failure mode: the model wrote plausible SQL that static checks could not refute.

This article treats that incident as a decision problem rather than a prompt-engineering story. Two credible camps now argue about the last gate before agent SQL reaches a shared database. One camp trusts PostgreSQL cost estimates as a cheap, lock-free rejector. The other camp insists on timed canaries against representative data, because cost units are not latency and because skew defeats the planner.

The sections below compare both positions with a small, labeled harness you can run. The harness is a proposal, not a production benchmark, and it records estimates and wall time without claiming a universal SLO. Reader value sits in the decision rule; any named tool is optional and removable.

Why promotion is the bottleneck that tests miss

Agent SQL usually fails after it already looks reviewable in a diff. The join graph compiles, the column names exist, and a unit fixture with ten rows returns the expected shape. Those tests do not encode correlation, TOAST size, or the histogram that autovacuum has not updated since the last backfill.

Evaluation suites lose bite when models learn the shape of the suite rather than the shape of production data. Query promotion has the same failure mode, only with page cache and random_page_cost instead of exam items. A gate that always passes is not a gate, and a gate that never runs the statement cannot see I/O.

The practical question is therefore narrow. Which signal is allowed to veto a parsed, linted candidate, and when is that signal too expensive to collect on every agent attempt?

Position A: Planner cost estimates as a promotion gate

Cost-based gates start from a simple operational fact: EXPLAIN without ANALYZE never executes the query. That property matters when an agent might emit a nested loop that only explodes after the first million rows. A reviewer can reject a candidate when total cost, estimated rows, or a sequential scan on a large relation crosses a numeric budget.

Advocates also note that cost estimates stay comparable when statistics are frozen for the test. Teams can restore a catalog snapshot, run EXPLAIN (FORMAT JSON), and compare total cost against a stored ceiling. The comparison is deterministic, fast, and free of write locks, which makes it attractive in CI for high-frequency agents.

PostgreSQL documents that the planner uses relation statistics to compute startup and total cost in abstract units, not milliseconds. The current EXPLAIN reference is the primary source for that behavior, including ANALYZE as the switch that actually runs the statement (PostgreSQL EXPLAIN). When those statistics lag, the same mechanism will underprice a scan, which is the opening Position B uses.

Even so, Position A remains rational for narrow OLTP lookups that must be screened hundreds of times per hour. A cost cap is a filter, not a proof of safety, and cheap filters belong at the earliest layer. Throwing away a bad plan before allocating a rehearsal host is an engineering choice, not a philosophical one.

Position B: Timed canaries on a rehearsal server

Canary advocates treat cost units as a different quantity from the SLO the pager actually pages on. Wall time, shared buffer hits, and rows actually returned can diverge from the estimate when predicates correlate or when a TOAST table dominates I/O. A rehearsal run with a tight statement_timeout converts that divergence into a binary promote-or-reject signal that CI can store.

The second argument is statistical freshness rather than philosophy. Agent SQL often encodes filters the warehouse added this week, so last week's histogram cannot price the plan honestly. Measuring a read-only canary against a subset that preserves skew is then the only test that can fail for the right reason. Realistic API performance work makes the same claim at another layer: the test has to look like production traffic, not like a fixture.

Canaries are not free in time or in data hygiene. They need a dataset that is not production, a timeout that is not infinite, and isolation from writers who serve customers. They also need a host you are willing to burn if the agent invents a pathological join, which is the only reason a scratch server belongs in this workflow.

Disclosure: This article was prepared as part of MonkeyCode's product outreach. When a team already has a staging replica, that replica is the correct canary target and no extra host is required. When it does not, MonkeyCode's free model access can draft candidate SQL, and the free server option can hold a throwaway rehearsal database for the harness below. Neither option replaces statistics management, anonymized subsets, or the decision rule, and this article does not claim quotas, hardware profiles, or durability.

Evidence both camps already accept

Both sides agree that agent SQL should not meet production on the first execution of a new text. Both sides also agree that parser-level checks and timeout budgets answer different questions than promotion, so this debate does not reopen those gates. The remaining dispute is which signal may veto a candidate that already parsed and already sat under a statement timeout.

Three shared facts constrain any honest comparison of the two camps. First, EXPLAIN without ANALYZE is cheap relative to execution, while EXPLAIN ANALYZE runs the statement and therefore needs a rehearsal role. Second, statement_timeout aborts a canary but does not repair a bad join order for the next agent attempt. Third, frozen statistics make cost comparisons reproducible, and thawed statistics make them honest about today's data.

A useful artifact has to record both signals instead of declaring a winner in prose alone.

A two-stage promotion harness

The following workflow is a proposal. It does not execute against a warehouse until you point the connection string at a scratch database you own.

Step 1: Freeze the question, not the model output

Store the candidate SQL, the intended read-only role, and the SLO in a small YAML file. Do not let the agent rewrite the SLO after it sees a failing canary, because that loop trains the model to game the gate. Keep the YAML in review so humans change budgets on purpose.

# proposal: promo_case.yml — not a live production contract
name: events_daily_rollups
slo_ms: 1500
max_explain_cost: 250000
statement_timeout_ms: 4000
require_canary_if:
  estimated_rows_gt: 100000
  seq_scan_relations:
    - events
    - event_payloads

Step 2: Capture a lock-free plan

Run EXPLAIN in JSON mode under a role that cannot write. Persist the total cost, planned rows, and node types beside the candidate. This is Position A as a command rather than a manifesto, and it should fail closed if the role is missing.

-- proposal: capture_plan.sql
SET default_transaction_read_only = on;
EXPLAIN (FORMAT JSON, VERBOSE, COSTS)
SELECT date_trunc('day', e.created_at) AS day,
       e.event_type,
       count(*) AS n
FROM events e
JOIN accounts a ON a.id = e.account_id
WHERE e.created_at >= now() - interval '7 days'
  AND a.plan = 'enterprise'
GROUP BY 1, 2;

Step 3: Decide whether a canary is mandatory

Apply the YAML thresholds before you spend rehearsal time. If estimated rows stay tiny and no large sequential scan appears, Position A may be sufficient for that candidate. If the plan touches a fact table or the cost sits near the cap, Position B becomes mandatory rather than optional.

Step 4: Time a bounded canary

On a rehearsal host only, set statement_timeout below human patience and above the published SLO. Record wall time, EXPLAIN ANALYZE buffer totals, and whether the timeout fired. Never point this step at a primary that serves customers, even if the SQL looks like a SELECT.

# proposal: promo_harness.py — unexecuted example, scratch DB only
import json, os, time
import psycopg

SQL_PATH = os.environ["CANDIDATE_SQL"]
DSN = os.environ["SCRATCH_DSN"]
SLO_MS = int(os.environ.get("SLO_MS", "1500"))
TIMEOUT_MS = int(os.environ.get("STATEMENT_TIMEOUT_MS", "4000"))
MAX_COST = float(os.environ.get("MAX_EXPLAIN_COST", "250000"))
ROW_TRIGGER = float(os.environ.get("EST_ROWS_TRIGGER", "100000"))

def load_sql():
    return open(SQL_PATH, encoding="utf-8").read()

def dsn_looks_unsafe(dsn: str) -> bool:
    lowered = dsn.lower()
    return any(token in lowered for token in ("prod", "primary", "master"))

def explain_only(cur, sql):
    cur.execute("SET default_transaction_read_only = on")
    cur.execute("EXPLAIN (FORMAT JSON, COSTS) " + sql)
    plan = cur.fetchone()[0]
    if isinstance(plan, str):
        plan = json.loads(plan)
    node = plan[0]["Plan"]
    return float(node["Total Cost"]), float(node.get("Plan Rows", 0)), plan

def run_canary(cur, sql):
    cur.execute("SET default_transaction_read_only = on")
    cur.execute(f"SET statement_timeout = {TIMEOUT_MS}")
    started = time.perf_counter()
    cur.execute("EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) " + sql)
    elapsed_ms = (time.perf_counter() - started) * 1000.0
    payload = cur.fetchone()[0]
    if isinstance(payload, str):
        payload = json.loads(payload)
    return elapsed_ms, payload[0]["Plan"]

def main():
    if dsn_looks_unsafe(DSN):
        raise SystemExit("refusing a DSN that looks like production")
    sql = load_sql()
    with psycopg.connect(DSN) as conn:
        conn.autocommit = True
        with conn.cursor() as cur:
            cost, est_rows, _ = explain_only(cur, sql)
            stage_a = "reject" if cost > MAX_COST else "pass"
            need_canary = est_rows >= ROW_TRIGGER or cost > MAX_COST * 0.4
            result = {
                "stage_a_cost": cost,
                "stage_a_est_rows": est_rows,
                "stage_a": stage_a,
                "need_canary": bool(need_canary and stage_a == "pass"),
            }
            if result["need_canary"]:
                try:
                    elapsed_ms, plan = run_canary(cur, sql)
                    result["stage_b_ms"] = round(elapsed_ms, 1)
                    result["stage_b"] = "pass" if elapsed_ms <= SLO_MS else "reject"
                    result["shared_hit"] = plan.get("Shared Hit Blocks")
                    result["shared_read"] = plan.get("Shared Read Blocks")
                except Exception as exc:
                    result["stage_b"] = "reject"
                    result["stage_b_reason"] = type(exc).__name__
            print(json.dumps(result, indent=2))

if __name__ == "__main__":
    main()

Step 5: Persist both verdicts next to the SQL

Write the JSON object beside the candidate in source control so later reviews see cost and milliseconds together. If Stage A rejects, skip the canary to keep the scratch host cheap and the logs readable. If Stage A passes and Stage B rejects, keep the cost number anyway so planner optimism becomes visible rather than anecdotal.

Sample output from an unexecuted run would look like the object below, which is a fixture for the debate, not a measured cluster result.

{
  "stage_a_cost": 188432.4,
  "stage_a_est_rows": 240000,
  "stage_a": "pass",
  "need_canary": true,
  "stage_b_ms": 2210.6,
  "stage_b": "reject",
  "shared_hit": 1204,
  "shared_read": 88110
}

Decision table

Signal	Cheap to collect	Distorted by stale stats	Executes the query	Suggested veto
Parser and lint only	Yes	No	No	Syntax or missing objects
EXPLAIN total cost	Yes	Yes, can under-reject	No	Cost above the cap
Estimated rows on a fact table	Yes	Yes	No	Rows above threshold force a canary
Timed canary with timeout	No	Less than EXPLAIN alone	Yes, read-only	Time above SLO or timeout
Buffer reads from ANALYZE	No	Less than EXPLAIN alone	Yes, read-only	Reads explode versus a stored baseline

The table is a decision aid, not a benchmark. Your numbers will differ with cache warmth, disk, and the quality of the data subset. Treat every ceiling as local until a week of promotions says otherwise.

A decision rule you can operationalize

Use both stages, in order, with an explicit exception list rather than a vibe. Stage A is a cheap rejector: if total cost exceeds the cap, do not promote and do not spend a canary. Stage B is mandatory when estimated rows on a fact table exceed the threshold, when the plan sequential-scans a large relation, when the SQL uses correlated subqueries, OFFSET paging, or volatile functions, or when yesterday's canary and today's estimate already disagreed by more than a factor of three.

If none of those hold, a passing cost gate may promote a narrow OLTP lookup without a timed run. That exception exists to keep CI fast, not to spare the agent from measurement as tables grow. Revisit the exception whenever autovacuum lag, a new index, or a warehouse backfill changes the shape of the fact table.

The rule is deliberately silent on writes. This harness assumes default_transaction_read_only, and agent-written DML or DDL needs rollback-first rehearsals that this debate does not cover.

Limitations

Cost units are not portable across PostgreSQL versions, random_page_cost, or work_mem, so a cap copied from another cluster is not evidence. Canary wall time depends on cache warmth, and a cold rehearsal host will reject queries that a warm replica would accept. Subset databases that drop the long tail of a skewed distribution will lie in the other direction and promote queries that production will punish.

EXPLAIN ANALYZE still runs the query, including functions with side effects if the role was not constrained. statement_timeout does not undo work already performed by a trigger that volunteered to write. The DSN heuristic that looks for names such as prod is a naming convention, not a security control, and it will miss a poorly named primary.

Who should not use this approach

Do not run timed canaries if you cannot obtain an anonymized subset and you would be tempted to use the primary instead. Do not use cost caps as the only gate if large tables sit unanalyzed for days. Regulated workloads that cannot copy rows onto a scratch host need EXPLAIN-only gates plus human sign-off, and they should not import customer payloads into a shared rehearsal server.

Teams that already keep a production-shaped replica and a mature query bot may find the Python file redundant. In that case the decision rule still applies, but the host should be the replica you already trust. A second scratch machine adds noise without adding a new signal.

What to count after you pick a side

After a week of promotions, count four numbers and ignore the rest of the telemetry. Count Stage A rejects, Stage B rejects, promotions that later needed a human rollback, and canaries skipped under the OLTP exception. If Stage B never rejects, the SLO is too loose or the subset is too kind, and if Stage A never rejects, the cost cap is decorative.

Those counts are the evidence this debate actually needs, and they cost nothing but a JSON file beside each candidate query. If you already operate a scratch database, run the harness there first; a free rehearsal host is only a convenience when that database does not exist.

Golden Result Files or Invariant Assertions: A Debate for Agent SQL Tests

Morgan Li — Sun, 20 Sep 2026 11:19:03 +0000

A payments team kept a directory of golden CSV files for every reporting query an agent was allowed to rewrite. Each pull request ran the candidate SQL against a restored staging snapshot and compared output bytes to the committed fixture. After three months of continued agent rewrites, the suite stayed green across eleven consecutive staging releases. Two queries had changed join order, and one had quietly dropped a filter on reversed transactions.

The suite had not become more rigorous. It had become easier to satisfy, because the agent learned the fixture rather than the business rule. That pattern now shows up wherever coding agents emit SQL faster than review capacity can grow. The useful debate is not whether to test agent SQL. It is which oracle still fails when the model has already seen last week's expected rows.

The problem the suite stopped measuring

Golden files are a literal oracle. They encode one accepted result for one frozen database image, then treat any byte difference as a regression. That design is excellent when the extract must match a regulator's file, a partner feed, or a previously signed financial close. It is a weak design when the agent is allowed to rewrite joins, push filters, or change aggregation order for cost.

Invariant assertions are a property oracle. They encode rules that must remain true even when the result set is allowed to change shape slightly, or when the snapshot is a day newer than the fixture. Typical invariants include grain, uniqueness, referential closure, sign constraints, and reconciliation totals against a slower source of truth. They fail on meaning, not on formatting.

This article treats both oracles as engineering instruments, not as ideology. The artifact is a small PostgreSQL scenario, two test styles, and a decision rule you can apply before the next agent rewrite lands in review.

Position A: keep golden result files

Golden files win when correctness is defined as reproduction. If yesterday's close file is the contract, a new plan that returns the same rows in a different order is already a defect. Teams that ship CSV to banks, tax authorities, or data vendors often need that strictness, because downstream parsers treat column order and numeric formatting as part of the interface.

They also win on diagnosis speed. A failing diff points at the first mismatched row, which is easier to discuss in a pull request than a failed predicate about “settled amount within 0.5 percent.” When the staging snapshot is versioned beside the fixture, the test is deterministic and cheap to shard in CI.

The failure mode is silent under-specification. Once an agent can emit SQL that recreates the fixture, it can drop a predicate that never fired on that snapshot. The test still passes. Production data that was absent from the restore then violates a rule nobody encoded.

Position B: replace files with invariant assertions

Invariant tests win when the query is a living report, not an archival extract. Agent rewrites are usually trying to reduce work_mem, avoid sequential scans, or replace a correlated subquery with a join. Those edits should be allowed to change row order, column aliases used only internally, and even the physical plan. What must not change is the grain of the result and the money math.

They also degrade more slowly as agents improve. A model that has memorized expected/daily_settle.csv can still violate SUM(amount) = SUM(leg_amount) or emit two rows for one payment_id. Property checks keep failing after golden files have been saturated. That is the practical answer to evaluation suites that no longer discriminate.

The failure mode is incomplete properties. If you only assert COUNT(*) > 0, an agent can return the wrong customers and still look healthy. Invariants require the same design effort as a schema, and they are easy to under-build on the first pass.

A concrete schema and two oracles

The following objects are a teaching fixture, not a production dump. Label them as such if you adapt them. They are small enough to restore on a laptop Postgres and large enough to show both oracles disagreeing.

-- teaching fixture: settlement grain is one row per payment_id
CREATE TABLE payments (
  payment_id   bigint PRIMARY KEY,
  account_id   bigint NOT NULL,
  amount_cents integer NOT NULL CHECK (amount_cents <> 0),
  reversed     boolean NOT NULL DEFAULT false,
  settled_at   timestamptz NOT NULL
);

CREATE TABLE ledger_legs (
  leg_id       bigint PRIMARY KEY,
  payment_id   bigint NOT NULL REFERENCES payments(payment_id),
  amount_cents integer NOT NULL
);

-- seed omitted: include at least one reversed payment whose legs still sum

Candidate query A is the sort of rewrite an agent proposes after reading an old report:

-- candidate_a.sql: looks cheaper, drops reversed filter
SELECT p.payment_id, p.account_id, p.amount_cents
FROM payments p
JOIN ledger_legs l ON l.payment_id = p.payment_id
WHERE p.settled_at >= DATE '2026-09-01'
GROUP BY p.payment_id, p.account_id, p.amount_cents;

Candidate query B preserves the business filter the fixture happened not to exercise:

-- candidate_b.sql
SELECT p.payment_id, p.account_id, p.amount_cents
FROM payments p
WHERE p.reversed = false
  AND p.settled_at >= DATE '2026-09-01'
  AND p.amount_cents = (
    SELECT SUM(l.amount_cents) FROM ledger_legs l
    WHERE l.payment_id = p.payment_id
  );

Oracle 1: golden file

psql -d staging -At -F ',' -c "$(cat candidate_a.sql)" | sort > /tmp/got.csv
diff -u tests/golden/daily_settle.csv /tmp/got.csv

If the restored snapshot contained no reversed rows in September, candidate_a.sql matches the golden file. The diff is silent. That is the saturation case.

Oracle 2: invariants

-- tests/invariants/daily_settle.sql
BEGIN;
CREATE TEMP TABLE got AS
  /* paste candidate here */ ;

-- grain: one row per payment
SELECT 1 FROM got GROUP BY payment_id HAVING COUNT(*) > 1;

-- no reversed payments in the report grain
SELECT g.* FROM got g
JOIN payments p USING (payment_id)
WHERE p.reversed;

-- money identity against legs
SELECT g.payment_id
FROM got g
JOIN (
  SELECT payment_id, SUM(amount_cents) AS leg_sum
  FROM ledger_legs GROUP BY payment_id
) s USING (payment_id)
WHERE g.amount_cents IS DISTINCT FROM s.leg_sum;
ROLLBACK;

Any of those SELECT statements returning a row is a failed invariant. On the same snapshot, candidate_a.sql can pass the golden file and fail the reversed-payment check the moment a single reversed row exists. That is the discrimination the suite had lost.

A numbered rehearsal before you pick a side

Freeze a staging restore hash, not a production connection string, and record the restore command in the review notes.
Classify the query as extract, aggregate, or mutating statement; mutating SQL does not belong in either oracle until it has a savepoint rehearsal.
Run the current human-written SQL and the agent candidate against the same restore, capturing both row hashes and invariant violations.
If the golden file matches and an invariant fails, keep the invariant and treat the file as stale documentation.
If both pass, add one negative row to the restore that should make a bad rewrite fail, then rerun before merge.
Only then accept a plan change, index hint, or CTE rewrite from the agent.

A coding agent that can reach a free remote model and a free server is useful in step 3, because you can generate several candidate rewrites without burning a production quota. Disclosure: This article was prepared as part of MonkeyCode's product outreach. I used MonkeyCode in that narrow slot: free model access and a free server option to emit candidate SQL files, then ran the oracles above outside the product. The oracles remain the method even if you generate the SQL by hand.

# labeled workflow, not a benchmark
# 1. ask the agent for three semantically equivalent rewrites
# 2. store them as candidate_a.sql, candidate_b.sql, candidate_c.sql
# 3. execute golden diff + invariant SQL on the same restore
# 4. keep the candidate that preserves invariants at the lowest EXPLAIN cost

Do not treat generation latency, token ceilings, or hardware size as known facts here. Those numbers change, and this debate does not depend on them. What matters is that candidate generation is cheap enough to produce disagreements your tests can still see.

Evidence you can collect in one afternoon

You do not need a published benchmark to choose an oracle. You need a disagreement matrix from your own restore. The table below is the artifact to fill, not a claim about any vendor.

Query class	Golden file result	Invariant result	Merge rule
Regulatory extract, fixed column order	Pass	Pass	Prefer golden; invariants are extra
Regulatory extract	Pass	Fail	Block merge; fixture is under-specified
Agent join rewrite	Fail (row order)	Pass	Prefer invariants; sort in the exporter
Agent filter rewrite	Pass	Fail	Block merge; snapshot lacked the filtered rows
Time-window aggregate	Fail (new day)	Pass	Prefer invariants; rebase golden files daily
Mutating upsert	Either	Either	Do not use these oracles; use savepoints

Fill the table with three real queries from your warehouse, not with synthetic slogans. If every cell is “both pass,” your negative fixtures are too weak, and the agent is no longer being tested.

Decision rule

Use golden result files as the primary oracle when all three conditions hold: the output is a contractual extract, the staging snapshot is versioned with the fixture, and row order plus formatting are part of the interface. Use invariant assertions as the primary oracle when any of these is true: the agent may change plans for cost, the snapshot ages faster than the fixture, or the business cares about grain and totals rather than byte identity.

If both oracles are affordable, run invariants first and keep golden files only for extract queries. That ordering catches silent filter drops before a diff can be argued away as “just sorting.” If you can afford only one oracle this quarter, choose invariants for agent-written SQL and leave golden files on the human-maintained extracts.

A short rule that fits on a runbook card: byte identity for files you ship; properties for queries you rewrite.

Limitations and who should skip this

This approach assumes a restorable staging database and queries that are read-only during review. It does not replace parser gates, statement timeouts, or privilege scoping, and it does not prove that a query will hold locks safely. Teams without a snapshot pipeline should not pretend a laptop subset is an oracle for production cardinality.

Skip golden files if your reports are timezone-sensitive and the fixture is rebuilt from a moving clock. Skip invariants if nobody on the team can name the grain of the result in one sentence. Skip agent generation entirely for DDL, role changes, and anything that writes without a rehearsed down path.

The tests you already have will keep passing as agents get better at imitating them. The decision is whether your next oracle still knows how to fail. If you want a place to generate extra SQL candidates before those oracles run, MonkeyCode’s free model access and free server option are one way to do that rehearsal without pointing the agent at production.

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

Morgan Li — Fri, 18 Sep 2026 07:45:27 +0000

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

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.
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.
Compute the fingerprint and the literal result as separate exit codes, then map them through the table rather than a single boolean.
If the fingerprint matches and the literal drifts, require a human only when the class is privileged or literal-sensitive; otherwise record a restyle.
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.
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.

Timeout Budgets or Work-Mem Caps: A Debate for Agent-Written SQL

Morgan Li — Thu, 17 Sep 2026 05:42:02 +0000

A staging replica accepted an agent-written reporting query that never finished its hash join. The statement held a share lock on a large fact table while nested loops spilled to disk. Review comments had approved the SQL because the join keys looked correct and the filters used indexed columns. The missing control was not syntax; it was a resource envelope the session never received.

This article treats that rehearsal case as a design debate, not a war story with invented outage metrics. Two credible camps now argue about how SQL review agents should bound work. One camp pins a clock on every statement. The other camp pins memory, temp files, and planner ceilings before the first row is touched.

Why resource envelopes belong in SQL review

Agent-written SQL often looks locally reasonable and still saturates a replica. Join order, predicate shape, and index names can pass a linter while the planner chooses a hash that spills. Human reviewers read intent. Database engines spend CPU, work_mem, and lock time. Those two views diverge under agent volume.

The last week of developer discussion around AI coding quality keeps returning to the same gap. Generating a query is cheap. Owning its runtime envelope is still engineering. For SQL review agents, the practical question is which envelope to encode as policy, and which envelope to leave as an incident runbook.

The rest of this piece compares timeout budgets with memory caps, then offers a labeled rehearsal harness. No production timings are claimed. The harness is a method you can run against a disposable Postgres instance you already operate.

Position A: timeout budgets as the primary gate

Timeout advocates treat wall-clock time as the only signal operators will actually enforce. A statement that exceeds a budget is cancelled, the transaction ends, and the agent receives a typed failure. Review then becomes a question of whether the proposed SQL can finish inside a published ceiling.

The evidence for this camp is operational, not aesthetic. On-call teams already know statement_timeout and lock_timeout. Orchestrators already retry on cancellation. Product owners already understand “this report may run for thirty seconds.” A clock is easy to explain in a pull request and easy to test with pg_sleep.

A typical session envelope looks like the following labeled example. Treat it as a contract template, not a benchmark.

-- labeled example: statement envelope, not a measured SLA
SET application_name = 'sql_review_agent';
SET statement_timeout = '15s';
SET lock_timeout = '3s';
SET idle_in_transaction_session_timeout = '10s';

-- agent SQL lands only after the GUCs above are applied
SELECT o.id, sum(i.amount)
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.created_at >= DATE '2026-09-01'
GROUP BY o.id;

Timeout budgets fail in a predictable way. A query can be cheap for ten seconds and then explode on the eleventh because a filter was not selective. Cancellation also leaves partial work if the agent used autocommit DML. The clock answers “how long,” not “how heavy.”

Position B: work-mem caps as the primary gate

Memory-cap advocates treat planner resource use as the real hazard. Hash joins, sorts, and materialize nodes consume work_mem per node, not per session, and temp files can fill a disk long before a timeout fires. A fifteen-second query that spills a few gigabytes is still an incident on a shared replica.

This camp wants the review agent to emit session GUCs and planner constraints together with the SQL. The SQL is not “approved” until the envelope would keep sorts in a bounded arena. Operators then fail the review when EXPLAIN shows sorts or hashes that would exceed the published cap at the stated row estimates.

-- labeled example: memory and temp-file envelope
SET work_mem = '16MB';
SET temp_file_limit = '256MB';
SET max_parallel_workers_per_gather = 0;
SET enable_nestloop = on;

EXPLAIN (FORMAT JSON)
SELECT o.id, sum(i.amount)
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.created_at >= DATE '2026-09-01'
GROUP BY o.id;

The evidence here is structural. Postgres documentation is explicit that work_mem is per operation, so a query with several hash nodes multiplies the ceiling. Disk spill is visible in EXPLAIN (ANALYZE, BUFFERS) after a rehearsal run. Memory caps catch “small time, huge footprint” queries that timeout policy would bless.

The weakness is estimation. If statistics are stale, the plan JSON understates memory, and the cap becomes theater. Memory GUCs also change plans, so a review that rewrites work_mem may approve a shape that production, with a higher default, will never use. Caps without a frozen statistics snapshot are incomplete.

Artifact: a decision table and rehearsal loop

The original artifact is a decision table plus a small rehearsal script. The table is meant for review bots that must choose an envelope before they emit “approve.” Rows are query classes, not product claims.

Query class	Timeout budget first?	Work-mem cap first?	Why
Point lookup by primary key	Yes	No	Clock detects lock waits; memory is already tiny
Aggregates on filtered facts	Split	Yes	Hash aggregates spill before wall-clock alerts
Agent DML with joins	Yes	Yes	Need lock_timeout and a tight work_mem
Window functions over dates	No	Yes	Sorts dominate; a long timeout hides spill
Cross-database reporting	Yes	Split	Remote waits look like CPU; cap alone misleads
Unknown cardinality	Yes	Yes	Dual envelope, then refuse if either trips

Use the table as a routing rule inside the reviewer, not as a production SLA. The following labeled Python shows one rehearsal loop. It applies both envelopes, runs EXPLAIN only, and prints which gate would have fired. It does not execute DML.

# labeled rehearsal: unexecuted against your instance until you fill DSN
import json
import os
import psycopg

TIMEOUT = os.environ.get("REVIEW_STATEMENT_TIMEOUT", "15s")
WORK_MEM = os.environ.get("REVIEW_WORK_MEM", "16MB")
SQL = os.environ["CANDIDATE_SQL"]  # SELECT-only for this harness

ENVELOPE = f"""
SET statement_timeout = '{TIMEOUT}';
SET lock_timeout = '3s';
SET work_mem = '{WORK_MEM}';
SET temp_file_limit = '256MB';
SET max_parallel_workers_per_gather = 0;
"""

EXPLAIN = "EXPLAIN (FORMAT JSON, VERBOSE) " + SQL

def node_needs_memory(node: dict) -> bool:
    plan_type = node.get("Node Type", "")
    return plan_type in {"Hash", "Hash Join", "Sort", "Aggregate", "WindowAgg"}

def walk(node: dict, hits: list) -> None:
    if node_needs_memory(node):
        hits.append({
            "type": node.get("Node Type"),
            "plan_rows": node.get("Plan Rows"),
            "sort_space": node.get("Sort Space Used"),
        })
    for child in node.get("Plans", []):
        walk(child, hits)

with psycopg.connect(os.environ["REVIEW_DSN"], autocommit=True) as conn:
    with conn.cursor() as cur:
        cur.execute(ENVELOPE)
        cur.execute(EXPLAIN)
        plan = cur.fetchone()[0][0]["Plan"]
        memory_nodes = []
        walk(plan, memory_nodes)
        print(json.dumps({
            "timeout_budget": TIMEOUT,
            "work_mem": WORK_MEM,
            "memory_nodes": memory_nodes,
            "would_cap_memory": any(memory_nodes),
        }, indent=2))

Numbered rehearsal steps keep the debate testable.

Restore a sanitized schema snapshot into a disposable database you control, never a shared warehouse endpoint.
Freeze statistics with ANALYZE on the tables the candidate SQL touches, then store pg_stats row counts beside the review artifact.
Apply the timeout budget alone, run EXPLAIN (not ANALYZE) on SELECT-only SQL, and record estimated cost.
Reset the session, apply the work-mem cap alone, and run the same EXPLAIN so plan shape changes are visible.
Apply both envelopes together and refuse the candidate if the plan still contains unbounded sorts or missing join filters.
For DML, stop after the dual envelope; do not auto-apply, and require a human runbook for rollback.

A decision rule that does not pick a mascot

Pick the timeout budget as the outer gate when the dominant failure mode is waiting: lock queues, remote scans, or agent loops that forget a predicate. Pick the work-mem cap as the outer gate when the dominant failure mode is shape: hashes, sorts, and window functions on wide fact tables. If the reviewer cannot classify the query, apply both envelopes and fail closed.

Concretely, encode three booleans in the review result. clock_ok means the estimated cost sits under the published timeout heuristic you already use for humans. memory_ok means no memory node in the plan JSON would obviously exceed work_mem at the frozen row counts. write_ok means the SQL is SELECT-only, or the DML is wrapped in an explicit transaction with lock_timeout. Approve only when all three are true.

That rule is stricter than either camp alone. It will reject some queries that would have finished. That is the point of a rehearsal-first reviewer. False rejects are cheaper than a replica that spills, provided the agent can rewrite and resubmit inside the same envelope.

If a team already reviews SQL with a constrained editor, MonkeyCode's free model access can draft envelope wrappers, and its free server option can run the EXPLAIN-only loop off the warehouse. Disclosure: This article was prepared as part of MonkeyCode's product outreach. Remove that runtime and the decision table still stands; the envelopes are session GUCs and a plan walk, not a vendor feature.

Limitations and who should skip this approach

This method assumes Postgres-style session GUCs and JSON EXPLAIN. Engines without per-session memory caps need a different artifact. It also assumes you can freeze statistics; autovacuum drift will make memory_ok lie. The harness must not run EXPLAIN ANALYZE on writes, and it must not point at production connection strings.

Do not use dual envelopes as a substitute for bind parameters, row-level security, or a migration rehearsal. Do not use them on nested-loop accidental cross joins and then raise timeouts until the query “passes.” Do not publish timeout numbers as SLAs without measuring them on your own hardware. This article does not claim model quality, token quotas, or server capacity beyond the two availability notes above.

Teams with a dedicated query-gateway team and admission control may already have a better outer gate. Teams that only generate one-off analyst SQL, with a human watching pg_stat_activity, will find the table heavy. The debate is for review agents that emit SQL faster than humans can watch dashboards.

The useful close is a checklist, not a slogan. Publish one timeout, one work_mem, and one refuse-closed rule beside every agent-written query. If you want a disposable place to rehearse that checklist, the free server path is optional; the envelopes remain the engineering work.