PostgreSQL Trigger Usage Considerations

Ruohang FengPigsty founder, PostgreSQL mage, database guru, cloud-exit evangelist

Overview

• Trigger behavior overview
• Trigger classification
• Trigger functionality
• Trigger types
• Trigger firing
• Trigger creation
• Trigger modification
• Trigger queries
• Trigger performance

Trigger Overview

Trigger behavior overview: English, Chinese

Trigger Classification

Trigger timing: BEFORE, AFTER, INSTEAD

Trigger events: INSERT, UPDATE, DELETE, TRUNCATE

Trigger scope: Statement-level, row-level

Internal creation: Constraint triggers, user-defined triggers

Trigger modes: origin|local(O), replica(R), disable(D)

Trigger Operations

Trigger operations are performed through SQL DDL statements, including CREATE|ALTER|DROP TRIGGER, and ALTER TABLE ENABLE|DISABLE TRIGGER. Note that PostgreSQL’s internal constraints are implemented through triggers.

Creation

CREATE TRIGGER can be used to create triggers.

CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

event includes:
    INSERT
    UPDATE [ OF column_name [, ... ] ]
    DELETE
    TRUNCATE

Deletion

DROP TRIGGER is used to remove triggers.

DROP TRIGGER [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]

Modification

ALTER TRIGGER is used to modify trigger definitions. Note that this can only modify trigger names and their dependent extensions.

ALTER TRIGGER name ON table_name RENAME TO new_name
ALTER TRIGGER name ON table_name DEPENDS ON EXTENSION extension_name

Enabling/disabling triggers and modifying trigger modes is implemented through ALTER TABLE clauses.

ALTER TABLE contains a series of trigger modification clauses:

ALTER TABLE tbl ENABLE TRIGGER tgname; -- Set trigger mode to O (local connection writes trigger, default)
ALTER TABLE tbl ENABLE REPLICA TRIGGER tgname; -- Set trigger mode to R (replica connection writes trigger)
ALTER TABLE tbl ENABLE ALWAYS TRIGGER tgname; -- Set trigger mode to A (always trigger)
ALTER TABLE tbl DISABLE TRIGGER tgname; -- Set trigger mode to D (disabled)

Note that when ENABLE and DISABLE triggers, you can specify USER to replace specific trigger names, which allows disabling only user-explicitly-created triggers without disabling system triggers used to maintain constraints.

ALTER TABLE tbl_name DISABLE TRIGGER USER; -- Disable all user-defined triggers, system triggers unchanged  
ALTER TABLE tbl_name DISABLE TRIGGER ALL;  -- Disable all triggers
ALTER TABLE tbl_name ENABLE TRIGGER USER;  -- Enable all user-defined triggers
ALTER TABLE tbl_name ENABLE TRIGGER ALL;   -- Enable all triggers

Queries

Getting table triggers

The simplest way is psql’s \d+ tablename. But this method only lists user-created triggers, not triggers associated with table constraints. Query system catalog pg_trigger directly and filter by table name through tgrelid:

SELECT * FROM pg_trigger WHERE tgrelid = 'tbl_name'::RegClass;

Getting trigger definitions

The pg_get_triggerdef(trigger_oid oid) function can provide trigger definitions.

This function takes trigger OID as input parameter and returns the SQL DDL statement that creates the trigger.

SELECT pg_get_triggerdef(oid) FROM pg_trigger; -- WHERE xxx

Trigger Views

pg_trigger (Chinese) provides the catalog of triggers in the system.

Trigger Types

Trigger type tgtype contains trigger condition information: BEFORE|AFTER|INSTEAD OF, INSERT|UPDATE|DELETE|TRUNCATE

TRIGGER_TYPE_ROW         (1 << 0)  // [0] 0:statement-level 	1:row-level
TRIGGER_TYPE_BEFORE      (1 << 1)  // [1] 0:AFTER 	1:BEFORE
TRIGGER_TYPE_INSERT      (1 << 2)  // [2] 1: INSERT
TRIGGER_TYPE_DELETE      (1 << 3)  // [3] 1: DELETE
TRIGGER_TYPE_UPDATE      (1 << 4)  // [4] 1: UPDATE
TRIGGER_TYPE_TRUNCATE    (1 << 5)  // [5] 1: TRUNCATE
TRIGGER_TYPE_INSTEAD     (1 << 6)  // [6] 1: INSTEAD OF 

Trigger Modes

The trigger tgenabled field controls the trigger’s working mode. Parameter session_replication_role can be used to configure trigger firing modes. This parameter can be changed at session level, possible values include: origin(default), replica, local.

(D)isable triggers are never fired, (A)lways triggers fire in any situation, (O)rigin triggers fire in origin|local mode (default), while (R)eplica triggers fire in replica mode. R triggers are mainly used for logical replication, for example pglogical replication connections set session parameter session_replication_role to replica, and R triggers only fire on changes made by that connection.

ALTER TABLE tbl ENABLE TRIGGER tgname; -- Set trigger mode to O (local connection writes trigger, default)
ALTER TABLE tbl ENABLE REPLICA TRIGGER tgname; -- Set trigger mode to R (replica connection writes trigger)
ALTER TABLE tbl ENABLE ALWAYS TRIGGER tgname; -- Set trigger mode to A (always trigger)
ALTER TABLE tbl DISABLE TRIGGER tgname; -- Set trigger mode to D (disabled)

In information_schema there are two more trigger-related views: information_schema.triggers, information_schema.triggered_update_columns, but they’re not discussed here.

Trigger FAQ

What types of tables can triggers be created on?

Regular tables (partitioned table parent tables, partitioned table partitions, inheritance table parent tables, inheritance table child tables), views, foreign tables.

Trigger type restrictions

• Views don’t allow BEFORE and AFTER triggers (whether row-level or statement-level)
• Views can only have INSTEAD OF triggers built, INSTEAD OF triggers can only be built on views, and only row-level, no statement-level INSTEAD OF triggers exist.
• INSTEAD OF triggers can only be defined on views and must use row-level triggers, not statement-level triggers.

Triggers and locks

Creating triggers on tables first attempts to acquire table-level Share Row Exclusive Lock. This lock blocks data changes to the underlying table and is self-exclusive. Therefore creating triggers blocks writes to the table.

Triggers and COPY relationship

COPY only eliminates the overhead of data parsing and packaging. When actually writing to the table, it still fires triggers, just like INSERT.