Triggers: How It Works in PostgreSQL Internals - Yugo Nagata - PGCon 2022

A tour of PostgreSQL triggers, exploring how they work from an internal perspective, covering row-level and statement-level triggers, constraints, trigger functions, and more.

Key takeaways

Triggers are a mechanism in PostgreSQL to execute a function automatically whenever a certain operation is performed on a table.
There are two main types of triggers: row-level triggers and statement-level triggers.
Row-level triggers are fired for each row that is updated or inserted, and can be used to implement business rules.
Statement-level triggers are fired once per statement, and can be used to implement constraints.
Constraints are rules that must be satisfied before a data modification can be executed.
Deferred constraint triggers are not executed immediately, but are moved to a queue and executed at the end of the transaction.
Immediate constraint triggers are executed immediately when the constraint is violated.
Triggers can be used to implement common use cases, such as auditing and logging.
Trigger functions can be used to modify the behavior of a trigger, and can raise errors if a constraint is violated.
Triggers can be created using the CREATE TRIGGER statement, and can be managed using the pg_trigger system catalog.
Transition tables are used to store the affected rows, and can be used in after triggers for statement-level triggers.
The CREATE CONSTRAINT TRIGGER statement can be used to create a constraint trigger.
Constraint triggers can be used to implement constraints, and can raise errors if a constraint is violated.
The pg_constraint system catalog is used to store information about constraints.
The CREATE TRIGGER statement can be used to create a trigger, and can be used to implement business rules.
Triggers can be used to implement common use cases, such as auditing and logging.
The pg_trigger system catalog is used to store information about triggers.
The SET CONSTRAINTS statement can be used to set the timing of constraint triggers.
The pg_trigger system catalog is used to store information about triggers.
Triggers can be used to implement common use cases, such as auditing and logging.
The pg_trigger system catalog is used to store information about triggers.
The pg_constraint system catalog is used to store information about constraints.
The CREATE TRIGGER statement can be used to create a trigger, and can be used to implement business rules.
The pg_trigger system catalog is used to store information about triggers.
The pg_constraint system catalog is used to store information about constraints.
Triggers can be used to implement common use cases, such as auditing and logging.
The pg_trigger system catalog is used to store information about triggers.
The pg_constraint system catalog is used to store information about constraints.
The CREATE TRIGGER statement can be used to create a trigger, and can be used to implement business rules.
Triggers can be used to implement common use cases, such as auditing and logging.
The pg_trigger system catalog is used to store information about triggers.
The pg_constraint system catalog is used to store information about constraints.
The CREATE TRIGGER statement can be used to create a trigger, and can be used to implement business rules.
Triggers can be used to implement common use cases, such as auditing and logging.
The pg_trigger system catalog is used to store information about triggers.
The pg_constraint system catalog is used to store information about constraints.
The CREATE TRIGGER statement can be used to create a trigger, and can be used to implement business rules.
Triggers can be used to implement common use cases, such as auditing and logging.
The pg_trigger system catalog is used to store information about triggers.
The pg_constraint system catalog is used to store information about constraints.

Triggers: How It Works in PostgreSQL Internals - Yugo Nagata - PGCon 2022

More talks