Teaching in-editor coding assistants Postgres lock semantics with a single rules file

Your in-editor coding assistant is good at most things. It knows your codebase conventions, it picks up your test patterns, it writes plausible TypeScript on the first try. Then someone asks it for a migration and it cheerfully produces:

ALTER TABLE users ADD COLUMN active boolean NOT NULL DEFAULT true;

On an empty dev database this works. On a 50 million row production table it takes an ACCESS EXCLUSIVE lock, rewrites every row, and blocks reads and writes for as long as the rewrite runs. The assistant did not warn you. It did not even know.

This post is about the cheapest possible fix: a single curated file at the repo root that teaches the assistant the lock table and the safe rewrite recipes, so the next suggestion is correct by default.

The problem: assistants are fluent in SQL, blind to locks

In-editor coding assistants are pattern matchers trained on a huge corpus of public code and docs. That corpus is heavy on tutorials, schema examples, and ORM quickstarts. It is light on production postmortems. The result is a model that writes syntactically perfect DDL while having no internalized sense of what each statement does to a busy database.

Symptoms you have probably seen:

• ALTER TABLE ... ADD COLUMN ... NOT NULL suggested as a one-liner, no backfill in sight.
• CREATE INDEX without CONCURRENTLY, then a follow-up suggestion to wrap it in a transaction (which would make CONCURRENTLY impossible anyway).
• ALTER TABLE ... ALTER COLUMN TYPE with a casual cast across millions of rows.
• ADD CONSTRAINT ... FOREIGN KEY with no NOT VALID, locking both tables for the validation scan.

None of these are syntax errors. They will all pass typecheck and code review unless the reviewer happens to remember the lock table.

The fix: project context is the strongest signal you have

Most in-editor assistants automatically pull files from the workspace into the model’s context window. A README.md at the repo root, a CONVENTIONS.md in docs/, a config file with naming rules: the assistant sees them and adjusts. Project files outrank training-time priors.

So we use that. Drop a single curated rules file at the repo root. The assistant now has the lock mode table, the footgun categories, and the safe rewrite patterns, all in the same context window as your prompt.

pgfence 0.6 ships exactly this file at github.com/flvmnt/pgfence/blob/main/RULES.md. It is the same content the analyzer uses, distilled into a readable single page.

What goes in the file

RULES.md is roughly 250 lines, organized as four sections:

1. Pre-migration block: the always-do-this preamble. SET lock_timeout = '2s', SET statement_timeout, SET idle_in_transaction_session_timeout = '30s', SET application_name. Five lines that prevent the lock-queue death spiral and make orphaned transactions visible in pg_stat_activity.
2. Lock mode reference table: every DDL pattern pgfence knows, mapped to the actual Postgres lock mode and what it blocks. ADD COLUMN ... NOT NULL is ACCESS EXCLUSIVE, ADD CONSTRAINT ... FOREIGN KEY is SHARE ROW EXCLUSIVE on both tables, CREATE INDEX CONCURRENTLY is SHARE UPDATE EXCLUSIVE. Every entry verified against tablecmds.c in the PG source.
3. Footgun categories: the patterns that look harmless and are not. REPLICA IDENTITY FULL (10x to 100x WAL amplification), ENABLE ROW LEVEL SECURITY without policies (denies all rows), CLUSTER (full table rewrite under ACCESS EXCLUSIVE), CREATE TYPE ... AS ENUM (no DROP VALUE ever).
4. Safe rewrite patterns: the expand/contract sequences. ADD COLUMN NOT NULL becomes a four-step recipe. ADD FOREIGN KEY becomes NOT VALID plus VALIDATE CONSTRAINT. ADD UNIQUE becomes CREATE UNIQUE INDEX CONCURRENTLY plus USING INDEX.

That is the whole file. No prose, no marketing, just the table and the recipes. The assistant reads it the same way a careful engineer would.

Concrete before/after

Same prompt, same model, same repo, two different files in context.

Prompt: “Add a last_login_at timestamp column to the users table. It should be required, default to now.”

Before, no RULES.md in repo:

ALTER TABLE users
  ADD COLUMN last_login_at timestamptz NOT NULL DEFAULT clock_timestamp();

The suggestion pattern-matches on the schema tutorial it saw 10,000 times. The statement takes ACCESS EXCLUSIVE, holds it for the duration of the rewrite, blocks reads and writes. clock_timestamp() is volatile, so even on PG11+ the metadata-only fast path does not apply.

After, with RULES.md at the repo root:

-- Migration 1: add nullable column (metadata-only on PG11+)
SET lock_timeout = '2s';
ALTER TABLE users
  ADD COLUMN IF NOT EXISTS last_login_at timestamptz;

-- Migration 2: backfill out-of-band in batches, run as a job
-- UPDATE users SET last_login_at = now()
--   WHERE id IN (SELECT id FROM users WHERE last_login_at IS NULL
--                LIMIT 1000 FOR UPDATE SKIP LOCKED);

-- Migration 3: add CHECK NOT VALID, validate, then SET NOT NULL
SET lock_timeout = '2s';
ALTER TABLE users
  ADD CONSTRAINT users_last_login_at_not_null
  CHECK (last_login_at IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_last_login_at_not_null;
ALTER TABLE users ALTER COLUMN last_login_at SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT users_last_login_at_not_null;

Same prompt. The assistant now matches on the recipes it can see in context. It splits the migration, sets lock_timeout, references FOR UPDATE SKIP LOCKED, and ends with SET NOT NULL after the constraint is validated.

It also stops doing things it did before. No volatile DEFAULT clock_timestamp() in a one-shot ADD COLUMN. No combined NOT NULL on ADD COLUMN. No transaction wrapping around CREATE INDEX CONCURRENTLY.

Why this works

Assistants are pattern matchers over context. The strongest signal in the context window is the project file that names the failure modes and the rewrites. When the assistant sees ADD COLUMN ... NOT NULL in a prompt and has a file in context that maps that pattern to ACCESS EXCLUSIVE and offers a four-step rewrite, the path of least resistance is to write the rewrite.

This is not magic. It is just giving the assistant the vocabulary it needs. The lock modes have always existed. The recipes have always existed. The assistant just did not have them at hand.

Limits, honestly

A file in context is not a verifier. The assistant can still misread the table, generalize the wrong pattern, or invent a recipe that looks like the real one and is not. Three honest caveats:

1. This is not a substitute for pgfence analyze in CI. The assistant gets the easy cases right. The analyzer catches the cases the assistant misses, and the CI gate is what stops a bad migration from merging. Trust but verify.
2. The file gets stale. Pin the version. The RULES.md shipped with each pgfence release is the source of truth for that release.
3. Context-window pressure matters. If your repo has 50 markdown files at the root, RULES.md competes for attention. Putting it at the root and naming it RULES.md is the loudest signal you can send without custom configuration.

How to set it up

Three steps:

1. Copy RULES.md from the pgfence repo to the root of your project. Or symlink it from docs/, whichever your assistant prefers.
2. Commit it. The whole team gets the same context.
3. Install pgfence and run pgfence analyze migrations/*.sql in CI. The assistant is your first line of defense; the analyzer is the proof.

For one-off lookups while you are writing a migration, pgfence explain "<stmt>" gives you the same content for a single statement from the command line:

pgfence explain "ALTER TABLE users ALTER COLUMN email TYPE citext"

explain is the on-demand reference. RULES.md is the always-on context. The two together give the assistant a pre-loaded map and a way to check any specific corner.

npm install -D @flvmnt/pgfence@0.6
curl -O https://raw.githubusercontent.com/flvmnt/pgfence/main/RULES.md

That is the whole intervention. One file, committed once, and the next migration the assistant writes for you is the one you would have written yourself.

GitHub · Docs · npm