PostgreSQL Trigger Usage Considerations

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.