pgfence 0.6: explain, RULES.md, and five footguns no other linter catches

pgfence 0.6 ships today. This release does three things: it makes the analyzer easier to share, it adds rules for footguns that no other Postgres migration linter catches, and it tightens the Trust Contract so reviewers can see exactly which statements the analyzer could not prove safe.

Install or upgrade:

npm install -D @flvmnt/pgfence@0.6

What is new

pgfence explain "<statement>"

A paste-and-run single-statement explainer. Give it any DDL, get back the lock mode, the operations it blocks, the risk level, and the safe rewrite recipe.

pgfence explain "ALTER TABLE users ADD COLUMN last_seen_at timestamptz NOT NULL"

Statement:
  ALTER TABLE users ADD COLUMN last_seen_at timestamptz NOT NULL;

[HIGH] add-column-not-null-no-default
  ADD COLUMN "last_seen_at" with NOT NULL but no DEFAULT: fails on non-empty tables and requires ACCESS EXCLUSIVE lock
  Lock: ACCESS EXCLUSIVE
  Blocks: reads, writes, other DDL

  Safe rewrite:
  Add nullable column, backfill, then add NOT NULL constraint
    ALTER TABLE users ADD COLUMN IF NOT EXISTS last_seen_at timestamptz;
    -- Backfill out-of-band in batches (FOR UPDATE SKIP LOCKED) ...

It reads from stdin too, so you can pipe in a tricky statement from a code review thread, get the analysis, paste the recipe back. --output json for machine consumption.

RULES.md for in-editor coding assistants

A new curated single-file rule catalog at the repo root, distilled from src/rules/* into ~250 readable lines covering lock modes, footguns, and safe rewrite patterns.

Drop it into your repo. In-editor coding assistants pick it up as project context, and the suggestions you get for new migrations come pre-loaded with pgfence’s conventions. The same content as pgfence analyze would output, just pre-staged so the assistant never has to guess.

This is the inverse of explain. explain is the on-demand lookup. RULES.md is the always-on context.

Five footguns no other linter catches today

Squawk has 37 rules. pgrubic has 100+. Eugene has a trace mode. strong_migrations has 21. None of them flag any of these:

• CLUSTER table USING idx: full table rewrite under ACCESS EXCLUSIVE. Same blast radius as VACUUM FULL. pgfence recommends pg_repack as the online alternative.
• ALTER TABLE t REPLICA IDENTITY FULL: every UPDATE and DELETE now writes the entire old row image to WAL. 10x to 100x amplification. Saturates Debezium and pglogical consumers.
• ALTER TABLE t ENABLE ROW LEVEL SECURITY without prior CREATE POLICY: denies all rows by default. The application appears to lose its data.
• ALTER TABLE t DISABLE ROW LEVEL SECURITY: silently exposes every row that was previously gated by policies.
• ALTER TABLE child INHERIT parent and NO INHERIT: catalog-bound metadata change under ACCESS EXCLUSIVE on both tables.

Plus two informational rules: CREATE POLICY (calls out that the policy is inert until RLS is enabled) and CREATE TYPE ... AS ENUM (Postgres has no ALTER TYPE ... DROP VALUE, so the value set is append-only forever; consider a lookup table instead).

Every assignment is verified against the PostgreSQL source (tablecmds.c, cluster.c, policy.c, pg_enum.c) and the documented lock modes in explicit-locking.html.

How we verified these rules

Lock-mode assignments are easy to get wrong. The Postgres documentation pages list lock modes per command category, but the actual lock acquisition lives in the C source and sometimes diverges from what the docs imply. For v0.6 every new rule was checked against the source tree, not just the docs. Here is the provenance, by rule.

replica-identity-full: the DDL itself takes a brief ACCESS EXCLUSIVE lock to update pg_class.relreplident. The runtime damage comes later, in WAL writes. The XLOG record types are declared in src/include/access/heapam_xlog.h, and the FULL vs DEFAULT branching for which old-tuple columns get logged lives in src/backend/replication/logical/decode.c. The user-facing semantics are documented at sql-altertable.html#SQL-ALTERTABLE-REPLICA-IDENTITY, which directly says FULL “generates a lot of WAL traffic.” The pgfence rule fires on FULL, lets DEFAULT and USING INDEX pass.

cluster: the lock acquisition is explicit in src/backend/commands/cluster.c. The function cluster_rel opens the target relation with table_open(tableOid, AccessExclusiveLock) and holds that lock for the entire rewrite. There is no CONCURRENTLY form. The user-facing reference is sql-cluster.html, which calls out the lock but does not call out the duration. The pgfence rule is HIGH with a pg_repack recipe.

create-enum-type: the irrevocability is structural, not a policy choice. The enum catalog code in src/backend/catalog/pg_enum.c implements AddEnumLabel and RenameEnumLabel. There is no RemoveEnumLabel function in the file. The catalog only supports append and rename, not remove. This is the same finding documented in the postgres-language-server thread on issue #699, where the Rust-side reviewers reached the same conclusion. The pgfence rule is LOW (most enums are fine) but the warning text explains the asymmetry so the choice is informed.

RLS toggles: row-level security has two moving parts in the source. The GUC row_security is registered in src/backend/utils/misc/guc_tables.c, and the policy machinery lives in src/backend/commands/policy.c. The deny-by-default behavior when ENABLE runs without a matching policy is a consequence of how RelationGetRowSecurityQual returns a deny qual when no policy exists for the role. The reference page is ddl-rowsecurity.html. Both ENABLE and DISABLE take ACCESS EXCLUSIVE on the table briefly; the danger is the consequence, not the lock.

inherit and no-inherit: the cross-table lock comes from src/backend/commands/tablecmds.c. The function MergeAttributes walks the parent and child schemas to validate compatibility; the function ATExecAddInherit opens both relations with ACCESS EXCLUSIVE and runs a full child scan to verify any inherited CHECK constraints hold. The scan is what turns a “metadata change” into a multi-minute block on a billion-row child. The pgfence rule is HIGH and the recipe calls for a maintenance window plus lock_timeout.

For lock-mode lookups across all DDL, the reference table lives in explicit-locking.html, but the source-of-truth is the C source. pgfence’s RULES.md cites the C file per rule so a reader can verify the claim without trusting the analyzer.

Why other linters miss these

Squawk, Eugene, pgrubic, and strong_migrations together cover the well-known footguns: ADD COLUMN ... NOT NULL without a default, CREATE INDEX outside CONCURRENTLY, foreign keys added inline, missing lock_timeout. Those rules are the ones that show up first in every “Postgres migration safety” blog post and have been canonical for a decade. They are correct and necessary.

The five v0.6 rules cover a different category. Replica-identity amplification is invisible at DDL time: the statement runs in milliseconds and the WAL volume doubles silently afterward. The cost lands on downstream consumers, not on the application that ran the migration. A static linter that only looks at lock duration misses it entirely.

The RLS toggles are similarly subtle. The DDL is fast and the lock is brief; the failure mode is logical, not physical. ENABLE without a matching policy denies all rows, DISABLE exposes rows that the policy was hiding. A linter that scores risk by lock duration sees a green light. A linter that understands what the statement means to the application sees a red one.

CLUSTER is a third pattern: a statement that has always been documented as “occasionally useful for maintenance,” and that runs cleanly in development on a tiny table, but rewrites the whole table under ACCESS EXCLUSIVE on production. Most linters do not parse CLUSTER at all because it is not part of the typical “review every ALTER TABLE” path. pgfence treats it the same as VACUUM FULL: a full table rewrite, recommend pg_repack.

The enum case is the most Postgres-specific. The catalog only supports add and rename; there is no DROP VALUE in any version of Postgres. Other linters do not warn on CREATE TYPE AS ENUM because the create itself is safe. pgfence warns because the absence of an exit ramp is what makes the choice load-bearing three years later.

What is next

v0.7 is in flight. The headline features are a live /play playground on pgfence.com that runs the analyzer in the browser against pasted SQL, and a richer /compare section that lays out each competing tool side by side with the same rule taxonomy pgfence uses internally. The playground will share the same WASM build the LSP uses, so the in-browser analysis is the analysis, not a rough approximation. The /compare pages will go beyond marketing tables: they will surface, per rule, which tools cover it and which do not. If you have a fixture migration that exposes a gap, the playground will be the place to share it.

Trust Contract: line numbers in the coverage line

The Trust Contract says every report MUST tell you what the analyzer could not prove safe. Before 0.6, the coverage line gave you a count of unanalyzable statements. Now it gives you the lines:

Analyzed: 7 statements  |  Unanalyzable: 2 (lines 14, 42)  |  Coverage: 78%

This works across all five reporters: CLI, JSON, GitHub PR markdown, GitLab Code Quality, and trace CLI. The JSON envelope also exposes coverage.dynamicStatementLines for machine consumers.

The LSP server now respects unknownHandling = "block" in editor diagnostics too: unanalyzable statements surface as Error severity in block mode, matching the CLI block-mode exit code. Previously the CLI blocked but the editor passed.

Other improvements

• libpg-query 16 to 17. PG17 grammar parity. All 534 tests still pass.
• ALTER COLUMN DROP NOT NULL bumped from LOW to MEDIUM. The operation is metadata-only and instant on PG9+, but the brief ACCESS EXCLUSIVE lock still risks lock-queue stalls under any concurrent long-running transaction.
• pgfence init --prisma-github-action scaffolds .github/workflows/pgfence-prisma.yml for projects on Prisma.
• The published npm tarball drops from 173 KB to 110 KB by excluding source maps.
• homepage, bugs, and repository fields added to package.json for npm SEO.

Reading list for v0.6

Three companion posts dig into the rule additions:

• ADD CONSTRAINT lock modes are not one-size-fits-all: a lock-mode correction story that justifies why pgfence cares about source-code verification.
• What lock does each DDL statement actually take?: a cheat sheet verified line by line against tablecmds.c, lockcmds.c, and indexcmds.c in PG17.
• Five Postgres migration footguns that no linter catches today: the longer-form case for the new rules.

Upgrade

npm install -D @flvmnt/pgfence@0.6
npx @flvmnt/pgfence analyze migrations/*.sql

The full changelog: CHANGELOG.md.

Found a footgun pgfence misses, or a check that fires when it should not? Open an issue: github.com/flvmnt/pgfence/issues. The repo has templates for false negatives, false positives, and unsupported ORM patterns.