Skip to main content
Background Image
  1. PostgreSQL Posts/

PostgreSQL Trigger Usage Considerations

·1069 words·6 mins· ·
Ruohang Feng
Author
Ruohang Feng
Pigsty Founder, @Vonng
Table of Contents

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.

NameTypeReferenceDescription
oidoidTrigger object identifier, system hidden column
tgrelidoidpg_class.oidOID of the table the trigger is on
tgnamenameTrigger name, unique within table-level namespace
tgfoidoidpg_proc.oidFunction called by the trigger
tgtypeint2Trigger type, trigger conditions, see comments
tgenabledcharTrigger mode, see below. `O
tgisinternalboolTrue if internal trigger for constraints
tgconstrrelidoidpg_class.oidReferenced table in referential integrity constraint, 0 if none
tgconstrindidoidpg_class.oidRelated index supporting constraint, 0 if none
tgconstraintoidpg_constraint.oidConstraint object related to trigger
tgdeferrableboolTrue if DEFERRED
tginitdeferredboolTrue if INITIALLY DEFERRED
tgnargsint2Number of string arguments passed to trigger function
tgattrint2vectorpg_attribute.attnumColumn numbers for column-level update triggers, empty array otherwise
tgargsbyteaArgument strings passed to trigger, C-style null-terminated strings
tgqualpg_node_treeInternal representation of trigger WHEN condition
tgoldtablenameREFERENCING column name for OLD TABLE, empty if none
tgnewtablenameREFERENCING column name for NEW TABLE, empty if none

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.

Related

Implementing Cache Synchronization with Go and PostgreSQL
·1234 words·6 mins
Cleverly utilizing PostgreSQL’s Notify feature, you can conveniently notify applications of metadata changes and implement trigger-based logical replication.
Auditing Data Changes with Triggers
·477 words·3 mins
Sometimes we want to record important metadata changes for audit purposes. PostgreSQL triggers can conveniently solve this need automatically.
GeoIP Geographic Reverse Lookup Optimization
·1551 words·8 mins
A common requirement in application development is GeoIP conversion - converting source IP addresses to geographic coordinates or administrative divisions (country-state-city-county-town-village)
PostgreSQL Development Convention (2018 Edition)
·3438 words·17 mins
Without rules, there can be no order. This article compiles a development specification for PostgreSQL database principles and features, which can reduce confusion encountered when using PostgreSQL.
Efficient Administrative Region Lookup with PostGIS
·2180 words·11 mins
How to efficiently solve the typical reverse geocoding problem: determining administrative regions based on user coordinates.
KNN Ultimate Optimization: From RDS to PostGIS
·4114 words·20 mins
Ultimate optimization of KNN problems, from traditional relational design to PostGIS