warn-matview-with-data

What triggers it

A CreateTableAsStmt node whose objtype is OBJECT_MATVIEW and whose into.skipData is not true. skipData is set only by an explicit WITH NO DATA, so its absence is read as the WITH DATA default — which means the rule fires on the ordinary case where the migration wrote no data clause at all.

What does not

CREATE TABLE ... AS, which parses to the same CreateTableAsStmt node but carries an objtype other than OBJECT_MATVIEW. WITH NO DATA sets into.skipData === true and returns null. Nothing else stands it down: no catalog is consulted, no size threshold is applied, and there is no dependency on --database-url.

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 new matview is held at ACCESS EXCLUSIVE for the whole build, but nothing can contend for an object that did not exist a moment ago. The lock that matters is on the other side: the view query holds ACCESS SHARE on every source table until it completes, and the migration transaction stays open for that entire time, extending every lock it already holds and pinning xmin so vacuum cannot clean up dead rows anywhere in the database.

Why it matters

WITH DATA is the default, so this usually happens without anyone choosing it. The statement looks like a definition and behaves like a batch job: the migration runs the view's query to completion before returning, holding locks on every table the query reads. A materialized view is generally materialized because the query is expensive, so the build is expensive by construction — and the transaction stays open throughout, which keeps xmin pinned so vacuum cannot clean up rows anywhere in the database.

Unsafe, and safe

CREATE MATERIALIZED VIEW daily_revenue AS
  SELECT date_trunc('day', created_at) AS day, sum(total)
  FROM orders GROUP BY 1;
-- Migration blocks until the aggregate finishes

-- Returns immediately; the expensive part becomes a REFRESH you can
-- schedule and retry. Note the view is not queryable until that runs,
-- and the first REFRESH cannot use CONCURRENTLY.
CREATE MATERIALIZED VIEW daily_revenue AS
  SELECT date_trunc('day', created_at) AS day, sum(total)
  FROM orders GROUP BY 1
  WITH NO DATA;

Deploy and transaction boundaries

The suggested WITH NO DATA form leaves the view unqueryable until something refreshes it (materialized view ... has not been populated), and that first REFRESH cannot use CONCURRENTLY. The refresh therefore has to be sequenced before anything reads the view, which is a change to whatever runs next, not just to this statement.

What it assumes

The rule has no idea how expensive the query is. It reads no table stats and applies no threshold, so a matview over a ten-row lookup table produces the same finding as an aggregate over a billion-row fact table. It also cannot distinguish a deliberate WITH DATA from an omitted data clause — both parse identically — so it cannot tell a considered choice from an accident.

What the CLI prints

⚠ [MP096] WARNING (line 1)
  CREATE MATERIALIZED VIEW "daily_revenue" populates immediately (WITH DATA is the default). The migration blocks until the full query completes, holding locks on every source table.

  Safe alternative:
  -- Create the view empty so the migration returns straight away:
  CREATE MATERIALIZED VIEW daily_revenue AS SELECT ... WITH NO DATA;
  
  -- Populate it outside the migration. The first refresh cannot use
  -- CONCURRENTLY, and the view is not queryable until it completes:
  REFRESH MATERIALIZED VIEW daily_revenue;
  
  -- A unique index lets every later refresh run without blocking readers:
  CREATE UNIQUE INDEX CONCURRENTLY daily_revenue_pk ON daily_revenue (<key>);
  REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

  Why: WITH DATA is the default, so this usually happens without anyone choosing it. The migration then runs the view query to completion. An aggregate over a large fact table can take many minutes, while holding locks on every table the query reads and keeping the migration transaction open the entire time. Deploy tooling with a timeout gives up partway and leaves the schema half-applied. Creating the view WITH NO DATA returns immediately and moves the expensive part into a REFRESH you can schedule, retry, and run outside the deploy.
  Docs: https://migrationpilot.dev/rules/mp096

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 MP096
CREATE MATERIALIZED VIEW daily_revenue AS

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

rules:
  MP096: false

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

• MP033require-concurrent-refresh-matviewrefreshing without blocking readers
• MP080ban-data-in-migrationlong-running data work inside DDL migrations