warn-trigger-on-hot-table

What triggers it

CREATE TRIGGER where row === true — the parser's marker for FOR EACH ROW, as opposed to statement-level.

What does not

Statement-level triggers (FOR EACH STATEMENT, the default when row is omitted). Also the sync trigger of an expand/contract migration on the same table, recognized when all three hold: the migration adds a column to the trigger's own table earlier in the file, the trigger fires on both INSERT and UPDATE, and either the trigger's own function — if defined in this migration — references the added column, or a later statement backfills that column on the same table.

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

SHARE ROW EXCLUSIVE while the trigger is created — self-conflicting and blocks writes and other DDL, but not reads. On a busy table it can queue behind an existing long transaction. The lasting cost is separate from this lock: the trigger body then runs once per affected row inside every future writing transaction on the table.

Why it matters

Creating the trigger takes a SHARE ROW EXCLUSIVE lock, so reads continue but writes queue. The cost that lasts is the body: a row-level trigger runs once per affected row inside the transaction doing the writing, so from this migration onward the function sits on the critical path of every INSERT, UPDATE and DELETE on the table. A function that takes a millisecond is invisible on single-row writes and adds ten seconds to a 10,000-row UPDATE — ten seconds of extra lock-holding, not just extra runtime.

Unsafe, and safe

CREATE TRIGGER audit_users
  AFTER INSERT OR UPDATE OR DELETE ON users
  FOR EACH ROW EXECUTE FUNCTION write_audit_log();
-- Now part of every write to users, forever

-- A statement-level trigger with transition tables does the same work
-- once per statement instead of once per row.
CREATE TRIGGER audit_users
  AFTER UPDATE ON users
  REFERENCING NEW TABLE AS changed
  FOR EACH STATEMENT EXECUTE FUNCTION write_audit_log();

What it assumes

Assumes a row-level trigger is meaningfully expensive, but a trivial function on a low-write table costs almost nothing — the rule only sees that it runs per row, not what it does. The expand/contract exemption also depends on all three signals lining up in this exact migration file; a sync trigger split across separate migrations, or backed by a function defined outside the migration, won't be recognized and gets flagged like any other trigger.

What the CLI prints

⚠ [MP090] WARNING (line 1)
  CREATE TRIGGER "audit_users" on "users" is FOR EACH ROW on INSERT/DELETE/UPDATE. Creating it takes SHARE ROW EXCLUSIVE (writes block), and write_audit_log() then runs once per affected row inside every writing transaction on "users".

  Safe alternative:
  -- Bound the wait so the trigger creation cannot sit at the head of the
  -- write queue behind a long-running transaction:
  SET lock_timeout = '5s';
  CREATE TRIGGER audit_users ...;
  RESET lock_timeout;
  
  -- If the trigger only needs to see the change as a whole, a statement-level
  -- trigger with transition tables runs once instead of once per row:
  CREATE TRIGGER audit_users
    AFTER UPDATE ON users
    REFERENCING NEW TABLE AS changed
    FOR EACH STATEMENT EXECUTE FUNCTION write_audit_log();

  Why: Creating the trigger takes a SHARE ROW EXCLUSIVE lock, so writes queue behind it and, on a busy table, behind whatever long transaction is already holding a conflicting lock. That part is brief. The part that lasts is the trigger body: a row-level trigger executes once per affected row inside the writing transaction, so it is now on the critical path of every write to the table. A function that takes a millisecond turns a 10,000-row UPDATE into an extra ten seconds of held locks, and anything the trigger writes to is now part of that transaction too.
  Docs: https://migrationpilot.dev/rules/mp090

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 MP090
CREATE TRIGGER audit_users

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

rules:
  MP090: false

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

• MP064ban-disable-triggerdisabling triggers breaks replication and audit
• MP065ban-lock-tableexplicit locks in migrations