drop-pk-replica-identity-break

What triggers it

An AlterTableStmt command of subtype AT_DropConstraint whose name either ends in _pkey or, lowercased, contains the substring pk anywhere — and for which no statement in the file satisfies a suppression text scan. That scan lowercases each statement in ctx.allStatements and requires it to contain both replica identity and the target table name as substrings.

What does not

A DROP CONSTRAINT whose name matches neither pattern. A file where any statement, in any position, mentions replica identity together with the table name. Any ALTER TABLE with no AT_DropConstraint command, and any non-AlterTableStmt node — DROP INDEX on the index backing a PK is not seen.

Where it applies

Applies to every PostgreSQL version MigrationPilot targets. It works on the SQL text alone — no database connection needed.

The lock, and what it blocks

The rule does not lead with locking, though ALTER TABLE ... DROP CONSTRAINT does take ACCESS EXCLUSIVE briefly and also drops the unique index underneath the key. The real subject is downstream: the default REPLICA IDENTITY is the primary key, so removing it leaves the table with nothing to identify rows by. The publisher keeps accepting UPDATE and DELETE; the subscriber is what fails, which is why the breakage shows up somewhere other than where the migration ran.

Why it matters

The default replica identity IS the primary key. When you drop a PK without setting REPLICA IDENTITY FULL or USING INDEX, all subsequent UPDATE and DELETE operations fail on logical replication subscribers (Supabase, Neon, AWS RDS read replicas, Debezium CDC).

Unsafe, and safe

ALTER TABLE users DROP CONSTRAINT users_pkey;
-- Logical replication breaks silently

ALTER TABLE users REPLICA IDENTITY FULL;
ALTER TABLE users DROP CONSTRAINT users_pkey;

What it assumes

Whether a constraint is the primary key is decided by name pattern, not by the catalog. A constraint named orders_pkg_check contains pk and is flagged; a primary key named users_primary_key contains neither _pkey nor pk and is missed entirely. The suppression side is equally loose: it is satisfied by a REPLICA IDENTITY NOTHING statement, which does not help at all, and by a statement that mentions the table name only in passing. The rule also cannot tell whether the table is actually part of a publication — nothing reads ctx.cluster, so a database with no logical replication configured gets the same critical violation.

What backs this rule

Every rule is a claim about PostgreSQL, so it carries what the claim rests on: a handbook chapter that cites the manual, the incidents that put it there, and the version it was last checked against.

What the CLI prints

✗ [MP055] CRITICAL (line 1)
  Dropping primary key constraint "users_pkey" on "users" without setting REPLICA IDENTITY. This breaks logical replication (Supabase, Neon, RDS read replicas, CDC pipelines).

  Safe alternative:
  -- Before dropping the PK, set replica identity:
  ALTER TABLE users REPLICA IDENTITY FULL;
  -- Or create a unique index and use it:
  -- CREATE UNIQUE INDEX CONCURRENTLY idx_users_new_pk ON users (id);
  -- ALTER TABLE users REPLICA IDENTITY USING INDEX idx_users_new_pk;

  Why: The default replica identity IS the primary key. When you drop a PK without setting REPLICA IDENTITY FULL or USING INDEX, all subsequent UPDATE and DELETE operations fail on logical replication subscribers (Supabase, Neon, AWS RDS read replicas, Debezium CDC). The publisher succeeds but the subscriber errors with "cannot delete from table because it does not have a replica identity."
  Docs: https://migrationpilot.dev/rules/mp055

Generated by running the CLI's own formatter over the flagged example above, so it is the text the tool actually produces. A real run also reports the other rules that fire on the same statement; those blocks are left out here.

Turning it off

For one statement, put a comment on the line before it:

-- migrationpilot-disable MP055
ALTER TABLE users DROP CONSTRAINT users_pkey;

For the whole project, in .migrationpilotrc.yml — by name or by id:

rules:
  MP055: false

# or keep it, and downgrade it
rules:
  MP055:
    severity: warning

Try it

Open this rule's flagged example in the playground. It runs in your browser — edit it and watch the finding appear and disappear.

Related rules

• MP045require-primary-keytables should always have a PK
• MP017no-drop-columndropping columns requires ACCESS EXCLUSIVE lock