avoidCreateTrigger

Diagnostic Category: lint/safety/avoidCreateTrigger

Since: vnext

Sources: - Inspired from: pgfence/create-trigger

Description

Creating a trigger acquires a SHARE ROW EXCLUSIVE lock on the table.

CREATE TRIGGER can block concurrent writes while the lock is held. Triggers also add hidden complexity to write operations on the table, which can cause unexpected performance issues and make debugging harder.

Examples

Invalid

create trigger my_trigger after insert on my_table for each row execute function my_func();

code-block.sql:1:1 lint/safety/avoidCreateTrigger ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

  ! Creating a trigger acquires a SHARE ROW EXCLUSIVE lock on the table.

  > 1 │ create trigger my_trigger after insert on my_table for each row execute function my_func();
      │ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    2 │ 

  i Triggers add hidden complexity and can block concurrent writes. Consider using application-level logic instead.

Valid

select 1;

How to configure

{
  "linter": {
    "rules": {
      "safety": {
        "avoidCreateTrigger": "error"
      }
    }
  }
}