ban-set-logged-unlogged

What triggers it

An AlterTableStmt with a command whose subtype is AT_SetLogged or AT_SetUnLogged. That is the whole test — no other field is read, and the direction is used only to pick the wording and to append the crash-loss sentence for the UNLOGGED case.

What does not

CREATE UNLOGGED TABLE, which is a CreateStmt with relpersistence: "u" and never reaches this rule — creating a table unlogged is free, it is the conversion that is expensive. Any ALTER TABLE whose commands are all some other subtype. Temporary tables, which cannot be set logged in the first place.

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

Both directions rewrite the entire heap under ACCESS EXCLUSIVE, held for the whole rewrite. LOGGED to UNLOGGED has to write out a fresh unlogged copy; UNLOGGED to LOGGED has to write the whole table through WAL, which on a large table is both the slowest direction and the one that can fill an archive. Reads and writes are blocked throughout, and peak disk is roughly double the table size while both copies exist.

Why it matters

Changing a table between LOGGED and UNLOGGED requires physically rewriting every page, which holds ACCESS EXCLUSIVE for the entire duration.

Unsafe, and safe

ALTER TABLE users SET UNLOGGED;

-- Consider creating a new unlogged table and migrating data
CREATE UNLOGGED TABLE users_new (LIKE users INCLUDING ALL);

What it assumes

The rule has no idea how big the table is — it never touches ctx.tableStats — so a ten-row lookup table and a two-terabyte fact table produce the identical critical violation. It also cannot know the intent: converting a freshly loaded staging table to LOGGED before it is exposed is a normal and safe pattern, and the rule flags it the same as flipping a live table. The data-loss impact applies to the UNLOGGED direction only: unlogged contents do not survive a crash or an unclean restart.

What the CLI prints

✗ [MP047] CRITICAL (line 1)
  SET UNLOGGED on "users" rewrites the entire table under ACCESS EXCLUSIVE lock. This blocks all reads and writes for the full duration. UNLOGGED tables lose all data on crash.

  Safe alternative:
  -- Changing LOGGED/UNLOGGED requires a full table rewrite.
  -- Consider performing this during a maintenance window.
  -- For LOGGED→UNLOGGED: ensure you have backups, data will be lost on crash.
  -- For UNLOGGED→LOGGED: consider creating a new LOGGED table and migrating data.

  Why: Changing a table between LOGGED and UNLOGGED requires a full table rewrite while holding ACCESS EXCLUSIVE lock. On large tables this can take hours, blocking all queries. UNLOGGED tables also do not survive crash recovery: all data is lost on restart.
  Docs: https://migrationpilot.dev/rules/mp047

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 MP047
ALTER TABLE users SET UNLOGGED;

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

rules:
  MP047: false

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

• MP006no-vacuum-fullVACUUM FULL also rewrites the entire table
• MP032ban-clusterCLUSTER also rewrites the entire table
• MP020require-statement-timeoutlong-running operations need timeouts