warn-do-block-ddl

What triggers it

A DoStmt whose as argument (the PL/pgSQL body string) matches one of eleven hardcoded regex patterns for DDL keywords — ALTER TABLE, CREATE INDEX, CREATE TABLE, DROP TABLE, DROP INDEX, DROP COLUMN, ADD COLUMN, ADD CONSTRAINT, DROP CONSTRAINT, RENAME, or TRUNCATE — found anywhere in the block's text.

What does not

A DoStmt with no as argument in its args list, where body extraction returns null, or a DO block body whose text matches none of the eleven DDL keyword patterns — for example a block that only runs DML or calls functions.

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 named — that's the point of the rule. Whatever DDL runs inside the block takes the same lock it would if written directly, but because the body executes as opaque PL/pgSQL, static analysis can't identify which lock that is.

Why it matters

PL/pgSQL DO blocks execute arbitrary code that cannot be analyzed by SQL linters. DDL inside DO blocks (ALTER TABLE, CREATE INDEX, DROP) acquires the same locks as direct SQL, but the operations are invisible to static analysis. Extract DDL from DO blocks into direct SQL statements for full safety analysis.

Unsafe, and safe

DO $$
BEGIN
  ALTER TABLE users ADD COLUMN age INTEGER;
  CREATE INDEX idx_users_age ON users (age);
END;
$$;

-- Extract DDL into direct SQL statements
ALTER TABLE users ADD COLUMN age INTEGER;
CREATE INDEX CONCURRENTLY idx_users_age ON users (age);

What it assumes

Detection is a keyword regex against the block's text, so it misses DDL assembled dynamically (e.g. via EXECUTE format(...) built from variables) and can false-positive on a keyword that only appears in a comment or string literal inside the block, since the matcher never parses the PL/pgSQL body.

What the CLI prints

⚠ [MP063] WARNING (line 1)
  DO block contains DDL (ALTER TABLE, CREATE INDEX, ADD COLUMN) that cannot be statically analyzed for lock safety. Extract DDL into direct SQL statements.

  Why: PL/pgSQL DO blocks execute arbitrary code that cannot be analyzed by SQL linters. DDL inside DO blocks (ALTER TABLE, CREATE INDEX, DROP) acquires the same locks as direct SQL, but the operations are invisible to static analysis. Extract DDL from DO blocks into direct SQL statements for full safety analysis.
  Docs: https://migrationpilot.dev/rules/mp063

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 MP063
DO $$

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

rules:
  MP063: false

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