warn-trigger-on-hot-table

What It Detects

CREATE TRIGGER ... FOR EACH ROW locks out writes to add code that then runs on every row written.

Why It's Dangerous

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.

Bad Example

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

Good Example

-- 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();

Configuration

Disable this rule:

# .migrationpilotrc.yml
rules:
  MP090: false

Or change its severity:

# .migrationpilotrc.yml
rules:
  MP090:
    severity: warning