sequence-not-reset-after-data-migration

What triggers it

An InsertStmt whose raw SQL text has an integer literal as the very first value in the VALUES list — VALUES (123, ...) — matched with the AST node rather than a leading-keyword scan, so a comment above the statement doesn't throw it off. It also fires only when no statement anywhere else in the migration calls setval() or ALTER SEQUENCE ... RESTART mentioning the same table name.

What does not

INSERTs where the first VALUES element isn't a bare integer literal — a UUID, a text id, or an integer id that isn't the first column all pass through untouched — and any INSERT where another statement in the same migration calls setval() or ALTER SEQUENCE ... RESTART with that table's name. The reset search is a same-migration text scan, not a check across every file in the deploy.

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 is involved — this is a data-integrity check, not a locking one. The rule flags a sequence counter left stale after seeding rows with hand-picked ids, nothing held during the statement itself.

Why it matters

When you seed data with explicit IDs, the sequence counter stays at its initial value. The next auto-generated INSERT picks a low ID that already exists, causing "duplicate key violates unique constraint."

Unsafe, and safe

INSERT INTO users (id, name) VALUES (1, 'Alice');
INSERT INTO users (id, name) VALUES (2, 'Bob');
-- Next auto-generated INSERT gets id=1!

INSERT INTO users (id, name) VALUES (1, 'Alice');
INSERT INTO users (id, name) VALUES (2, 'Bob');
SELECT setval(pg_get_serial_sequence('users', 'id'),
  COALESCE(MAX(id), 1)) FROM users;

What it assumes

Detection is a regex match on the SQL text, so a multi-row INSERT whose first VALUES tuple doesn't start with the id column, or a sequence reset issued from a separate migration file, would both be missed. It also assumes the inserted ids are below the sequence's current value, which isn't always true.

What the CLI prints

⚠ [MP059] WARNING (line 1)
  INSERT INTO "users" with explicit integer IDs without resetting the sequence. The next auto-generated ID may conflict with existing rows.

  Safe alternative:
  -- Reset the sequence after inserting with explicit IDs:
  SELECT setval(pg_get_serial_sequence('users', 'id'), COALESCE(MAX(id), 1)) FROM users;

  Why: When you seed data or migrate rows with explicit integer IDs, the sequence counter is not automatically updated. The next auto-generated INSERT picks a low ID that already exists, causing "duplicate key violates unique constraint." This is one of the most common post-migration production errors, documented extensively in Django, Rails, Supabase, and Prisma issue trackers.
  Docs: https://migrationpilot.dev/rules/mp059

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 MP059
INSERT INTO users (id, name) VALUES (1, 'Alice');

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

rules:
  MP059: false

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

• MP015no-add-column-serialSERIAL creates implicit sequence
• MP039prefer-identity-over-serialGENERATED AS IDENTITY is safer