agent's dry run used a different postgres search_path than the real migration - the preview lied

TL;DR

Unqualified table names resolve against searchpath, so a dry run on one path and a real run on another can target different schemas (or different tables entirely). Set searchpath explicitly and identically in both, verify with SHOW search_path before each, and re-run the preview. Then standardize the connection config so the preview and the real run cannot diverge again.

The query

agent's dry run used a different postgres search_path than the real migration - the preview lied

Use this when

• Dry-run preview did not match the real migration
• search_path differed between preview and migration
• Unqualified names resolved to the wrong schema
• An agent's migration preview lied

Not for

• Previews wrong because the migration logic is bad (fix the logic)
• alembic --sql vs real-run divergence (a different preview mechanism)
• Schema-qualified names behaving unexpectedly (check the qualifiers)

Steps

1. Prove the divergence

SHOW search_path;

Run it in the dry-run session and in the real migration session. Expected output: two different values, e.g. the preview on "$user", public and the migration on app_schema, public. That difference is the lie's mechanism.

2. Re-run the preview with the real search_path

SET search_path TO [real path];

Then re-run the exact preview. Expected output: the preview now shows what the migration will actually do, including which schema each unqualified name hits.

3. Set search_path explicitly in the migration config

Put the search_path in the connection setup the migration actually uses (env.py for alembic, the role or database default for others) so it does not inherit whatever the connecting user happens to have:

# in env.py, after connecting
connection.execute(text("SET search_path TO [schema], public"))

Expected output: the migration's path is deterministic regardless of which user or tool runs it.

4. Make the preview use the identical connection config

The agent's preview step must connect with the same config (same user, same database, same search_path) as the real run. A preview on a different config is not a preview.

Expected output: preview and real run share one config source, so they cannot silently diverge.

5. Prefer schema-qualified names in migrations

Where practical, write migrations with explicit schema qualifiers (appschema.users, not users). Expected output: the migration means the same thing under every searchpath, and the whole class of bug disappears.

Tool compatibility

postgres 12 through 17. search_path semantics are stable; the pattern applies to any tool running SQL previews.

Variant phrasings

dry run different search_path than migration

Steps 1 and 2: prove it with SHOW, re-run the preview on the real path.

postgres preview lied wrong schema

Step 5: schema-qualify names so previews cannot lie this way again.

alembic preview vs real migration different schema

Steps 3 and 4: set the path explicitly in env.py and make the preview use the same config.

Why it happens

Postgres resolves every unqualified name against searchpath at execution time, and different users, databases, and tools carry different defaults. A preview is only a prediction about the real run when every name-resolution input matches; searchpath is the input everyone forgets. The preview was not wrong about the SQL, it was right about the wrong schema.

Edge cases

• Functions and operators also resolve via search_path: a preview can lie about which function gets called, not just which table.
• ALTER ROLE ... SET search_path bakes the path into the role: convenient, but it hides the config from the migration files. Prefer explicit SET in the migration setup.
• Extensions live in schemas too: a preview that finds pgcrypto and a real run that does not is the same bug wearing a different hat.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_MeETar6SKzQoT-hHbCQs9Q