warn-xid-consuming-retry

What triggers it

A TransactionStmt whose kind is TRANS_STMT_SAVEPOINT (returned as that string, or numerically 3) — any SAVEPOINT <name> statement.

What does not

Any other TransactionStmt kind — BEGIN, COMMIT, ROLLBACK, RELEASE SAVEPOINT, ROLLBACK TO SAVEPOINT — and any statement that isn't a TransactionStmt at all. The check is a single equality test against one kind value.

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

No lock applies — SAVEPOINT doesn't take a table lock. The cost the rule warns about is a consumed transaction id and a subtransaction holding its snapshot open, not anything blocking other sessions.

Why it matters

Each SAVEPOINT allocates a new transaction ID (XID). In retry loops, every SAVEPOINT/ROLLBACK TO consumes another XID. On high-throughput systems, subtransaction XID consumption can push the database toward XID wraparound.

Unsafe, and safe

SAVEPOINT my_savepoint;

-- Use separate transactions instead of subtransactions
-- Or retry the entire transaction, not a subtransaction

What it assumes

The rule flags every SAVEPOINT regardless of how many appear in the migration or how high-throughput the database actually is — a single SAVEPOINT in an otherwise ordinary migration is unlikely to meaningfully affect wraparound timing, and the rule can't distinguish a one-off from a retry loop issuing thousands.

What the CLI prints

⚠ [MP076] WARNING (line 1)
  SAVEPOINT "my_savepoint" creates a subtransaction that consumes a separate XID. In retry loops, this accelerates XID wraparound risk.

  Safe alternative:
  -- Consider restructuring to avoid subtransactions:
  -- 1. Use separate transactions instead of SAVEPOINT/ROLLBACK TO
  -- 2. Use advisory locks for coordination instead of subtransactions
  -- 3. If retries are needed, retry the entire transaction, not a subtransaction

  Why: Each SAVEPOINT allocates a new transaction ID (XID). In retry loops, every SAVEPOINT/ROLLBACK TO consumes another XID without the previous one being freed. On systems processing millions of transactions, subtransaction XID consumption can push the database toward XID wraparound, which forces a complete database freeze for maintenance. PostgreSQL 14+ improved subtransaction handling, but the XID cost remains.
  Docs: https://migrationpilot.dev/rules/mp076

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 MP076
SAVEPOINT my_savepoint;

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

rules:
  MP076: false

# or keep it, and downgrade it
rules:
  MP076:
    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.