How to dry-run an UPDATE before an AI agent commits it

The short answer

To find out how many rows an UPDATE will change before it commits, run it inside a transaction and roll it back: BEGIN; UPDATE …; ROLLBACK;. The command tag between them is the real count. Don’t rely on plain EXPLAIN: it only plans the statement and shows estimated rows.¹ In our test, EXPLAIN estimated 25 rows for an UPDATE that changed 800.¹

A rolled-back dry run isn’t free of side effects. Triggers run during it. Sequence values they consume aren’t given back, while a NOTIFY is only delivered if the transaction commits.² Anything a trigger does outside Postgres, such as calling a web service, happens for real. Know your triggers before you use this on a production table.

EXPLAIN is an estimate

EXPLAIN without ANALYZE plans the statement and shows the planner’s estimates: costs and an estimated number of rows. Nothing runs.¹ That’s useful for “will this use an index?”, and unreliable for “how many rows will this change?”.

Our test table had 5,000 orders across 50 customers, statistics freshly gathered with ANALYZE. The agent’s statement:

Code A database command in SQL. The text around it says what it does.

EXPLAIN UPDATE orders SET status = 'cancelled' WHERE customer_id % 7 = 0;

Code What the screen or file shows, copied as is.

 Update on orders  (cost=0.00..107.00 rows=0 width=0)
   ->  Seq Scan on orders  (cost=0.00..107.00 rows=25 width=38)
         Filter: ((customer_id % 7) = 0)

The scan’s estimate is 25 rows. The real answer was 800.¹ The planner can’t know how an arithmetic expression on a column is distributed, so it falls back on a generic guess. Any WHERE clause built from expressions, functions or correlated columns can be off by this much or more.

An agent that reads “rows=25” and reports “about 25 orders will be cancelled” would be off by a factor of 32.

Count it in a transaction you roll back

Running the statement for real, inside a transaction you then abort, gives the exact count:

Code A database command in SQL. The text around it says what it does.

BEGIN;
UPDATE orders SET status = 'cancelled' WHERE customer_id % 7 = 0;
-- UPDATE 800
ROLLBACK;

The command tag is the number of rows the UPDATE matched, including any whose values didn’t actually change.⁴ If you want per-row detail, add RETURNING with the key and, on PostgreSQL 18, the old and new values; RETURNING OLD and NEW shows how.

EXPLAIN ANALYZE combines both: it executes the statement and reports actual rows next to estimates. The docs warn that the statement is actually executed, and suggest exactly this wrapper for INSERT, UPDATE, DELETE and MERGE.¹

Code A database command in SQL. The text around it says what it does.

BEGIN;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
  UPDATE orders SET status = 'cancelled' WHERE customer_id % 7 = 0;
ROLLBACK;

Code What the screen or file shows, copied as is.

 Update on orders (actual rows=0.00 loops=1)
   ->  Seq Scan on orders (actual rows=800.00 loops=1)
         Filter: ((customer_id % 7) = 0)
         Rows Removed by Filter: 4200
 Trigger orders_audit: calls=800

Note the last line. The trigger ran 800 times.¹

What a rollback doesn’t undo

Our table had an AFTER UPDATE trigger that inserted a row into order_audit and called pg_notify. Here is what the dry run left behind:

Three of those need care.

Sequences. A value taken by nextval isn’t reclaimed when the transaction aborts, and setval changes aren’t undone.³ The only visible effect is a gap in ids, harmless for most tables, but surprising if anything assumes ids are dense.

Anything outside Postgres. Postgres can only roll back what Postgres did. A trigger or function that calls an external service, writes a file, or sends a message through an extension acts immediately. If a table has a trigger like that, a dry run there is a real run of that part.

Locks and time. The dry run takes the same locks as the real statement and holds them until ROLLBACK. A dry run of a large update on a busy table blocks other writers for as long as it runs. Set a statement_timeout for the dry run so it can’t hold locks for long.⁵

Before you dry-run on a table, list its triggers:

Code A database command in SQL. The text around it says what it does.

SELECT tgname, pg_get_triggerdef(oid)
FROM pg_trigger
WHERE tgrelid = 'orders'::regclass AND NOT tgisinternal;

A dry-run recipe for agent tools

Build the dry run into the agent’s database tool, so the agent can’t skip it:

1. Run the statement in a transaction with a short statement_timeout.
2. Read the command tag and, if you can, RETURNING keys.
3. Roll back.
4. Compare the count with a limit. More than, say, 50 rows, or more than the agent said it expected, means a person decides.
5. If approved, run it again for real in a new transaction, and check the count matches the dry run. If it doesn’t, something changed in between: stop and ask again.

Step 5 matters. The data can change between the dry run and the real run, so the dry run is a preview, not a promise. A count that differs is a reason to stop, not a rounding error.

When not to dry-run

• Tables with triggers that act outside the database. The dry run isn’t dry.
• Very large updates on busy tables. The dry run costs as much as the real one, locks included. Estimate with a SELECT count(*) using the same WHERE instead; it doesn’t take row locks.
• Statements whose effect depends on timing, such as WHERE updated_at < now() - interval '1 hour'. The count can change between preview and run.

The SELECT count(*) alternative deserves a note: it’s exact for that moment and has no side effects, but it can’t see what triggers or cascades would change. For that, our 1 row vs 301 rows case shows why you also want a check after the real write.

A checklist

1. Never trust EXPLAIN row estimates for write counts.¹
2. Dry-run in BEGIN … ROLLBACK with a statement timeout.
3. List triggers first, and know which act outside Postgres.
4. Expect sequence gaps after dry runs.³
5. Re-check the count on the real run.
6. Put a row limit on agent writes, and route anything over it to a person.

A limit on rows is a limit on blast radius, and an UPDATE with no WHERE is the extreme case it catches. Notifications are a blind spot for row-based checks too, as where our gate loses shows. To see writes refused and checked on a real Postgres in your browser, open the playground; to grade what an agent can reach on your machine, run npx deedglass scan (the grade, explained).

Written with AI assistance and checked against our claims register.