Trigger: How it Works in PostgreSQL Internals
PGConf.EU 2022, Berlin, Germany
Oct 26, 2022
Yugo Nagata (SRA OSS LLC)

About me●

Yugo Nagata–

Software Engineer & Researcher at SRA OSS LLC–

Research and Development on PostgreSQL–●

Incremental View Maintenance (IVM)●

pg_ivm (https://github.com/sraoss/pg_ivm)

Lecture on PostgreSQL Internal© 2022 SRA OSS LLC

2

Outline●

Overview of Trigger●

How Triggers are created●

How Triggers work

© 2022 SRA OSS LLC

3

Overview of Trigger© 2022 SRA OSS LLC

4

Trigger●●

A special function is automatically executed whenever acertain type of operation is performed
Attached to tables, views, and foreign tables

INSERT, UPDATE, DELETE, ...
Trigger

Table

Function© 2022 SRA OSS LLC

5

Use Cases of Triggers●

Audit trail / logging●

Input data validation●●

INSERT, UPDATE, DELETE, ...
Trigger

validate

Table

Enforcing / checking constraints
Complex business rules

Functionconstraints checklogging
generate

BusinessData© 2022 SRA OSS LLC

Audit
Trail

6

When a Trigger is Fired
After or before ...

COPY FROM●INSERT, UPDATE, DELETE●

TRUNCATE●

COPY FROM●MERGE (PG15+)

MERGE

INSERT, UPDATE, DELETE, TRUNCATE

Trigger

Table
Function© 2022 SRA OSS LLC

7

Types of Triggers●

Per-row (row-level) trigger–●

Per-statement (statement-level) trigger–●

Invoked once for each row that is affected by the statement

Invoked only once when an appropriate statement is executed

Triggers on TRUNCATE may only be defined at statementlevel.

© 2022 SRA OSS LLC

8

What a Trigger Function Can Do●

Scan and/or modify other tables●

Refer to the old and/or new row record (in row-level triggers)●

Modify the row being inserted or updated, or skip the operation(in row-level BEFORE triggers)
UPDATE(OLD record → NEW record)
Trigger

Refer to, Modify

Skip

Table

Raise an error
Function

Scan, Modify

Scan, Modify© 2022 SRA OSS LLC

OtherTables9Transition Tables●●

Set of affected rows–

OLD TABLE: before-images of all rows updated or deleted–

NEW TABLE: after-images of all rows updated or inserted

In AFTER triggers (both statement-level and row-level)
UPDATE
OLD
Table

NEW
Table

Trigger

Table

Refer toFunction© 2022 SRA OSS LLC

10

INSTEAD OF Triggers on Views
Making a complex view updatable
INSERT, UPDATE, DELETE
INSTEAD OF
Trigger

View

Per-row

FunctiondefineModifyBase
Base Tables
Tables© 2022 SRA OSS LLC

11

How Triggers are created© 2022 SRA OSS LLC

12

CREATE TRIGGER
CREATE TRIGGER check_update
BEFORE UPDATE ON accounts
FOR EACH ROWEXECUTE FUNCTION check_account_update();BEFORE
Triggercheck_update

Table:
accounts

UPDATE

Per-row

The trigger function must be
defined before creating a trigger.

Function:check_account_update()© 2022 SRA OSS LLC

13

Creating a trigger function
CREATE FUNCTION check_account_update()
Returning type: “trigger”
RETURNS trigger AS $$No arguments
BEGIN
IF NEW.data > 100 THEN
RAISE EXCEPTION 'data out of data';
END IF;
RETURN NEW;
Return the row to be inserted,
END;
or the new row after update.
$$ LANGUAGE plpgsql;- Can be modified- NULL means skip of the operation.

© 2022 SRA OSS LLC

14

CREATE TRIGGER with arguments
CREATE TRIGGER check_update
BEFORE UPDATE ON accounts
FOR EACH ROWEXECUTE FUNCTION check_account_update(true);
CREATE FUNCTION check_account_update()
RETURNS trigger AS $$BEGIN
IF NEW.data > 100 AND TG_ARGV[0] = 'true' THEN
RAISE EXCEPTION 'data out of data';
END IF;
RETURN NEW;
END;$$ LANGUAGE plpgsql;© 2022 SRA OSS LLC

15

WHEN condition
CREATE TRIGGER check_update
BEFORE UPDATE ON accounts
FOR EACH ROWWHEN (OLD.balance IS DISTINCT FROM NEW.balance)
EXECUTE FUNCTION check_account_update();BEFORE
Triggercheck_update

Table:
accounts

UPDATE
only if column balance has in fact changed

Per-row
Function:check_account_update()© 2022 SRA OSS LLC

16

System Catalogs
pg_trigger
pg_class
Table

Trigger
Trigger name
Trigger type
Arguments
WHEN condition
Transition table names

pg_proc
Function...

© 2022 SRA OSS LLC

17

Constraint Trigger●

A trigger for implementing a constraint●

Row-level AFTER trigger only●

The timing of the trigger firing is deferrable–

At the end of the statement = immediate–

At the end of the containing transaction = deferred

Table: t
Constraint
Triggerdeferred
immediateTransaction
Start
BEGIN;

Statement
Start

Statement
End

INSERT INTO t VALUES (10)© 2022 SRA OSS LLC

TransactionEnd
COMMIT;

time
18

Creating a constraint trigger
CREATE CONSTRAINT TRIGGER check_constraint
AFTER INSERT ON accounts
DEFERRABLE INITIALLY DEFERREDFOR EACH ROWEXECUTE FUNCTION check_my_constraint();
AFTER
Triggercheck_constraint

Table:
accounts

INSERTdeferrable and initially deferred
Per-row
Function:check_my_constraint()© 2022 SRA OSS LLC

19

SET CONSTRAINTS
BEGIN;SET CONSTRAINTS check_constraint DEFERRED;orSET CONSTRAINTS check_constraint IMMEDIATE;
...
END;

© 2022 SRA OSS LLC

20

System Catalogs
pg_trigger
pg_class
Table

Trigger
Trigger name
Trigger type
Arguments
WHEN condition
Transition table names

pg_proc
Function...
pg_constraint
Constraint© 2022 SRA OSS LLC

21

Constraint Triggers Created Internally●

Foreign key constraint (Referential Integrity)
CREATE TABLE orders (order_id integer PRIMARY KEY,
product_no integer REFERENCES products (product_no),quantity integer);●

Primary key / unique / exclusion constraint (deferrable only)
CREATE TABLE distributors (
didinteger,namevarchar(40) UNIQUE DEFERRABLE);© 2022 SRA OSS LLC

22

Foreign Key Constraint
CREATE TABLE orders (order_id integer PRIMARY KEY,...
);
CREATE TABLE order_items (order_id integer REFERENCES orders ON DELETE CASCADE,...
);

order_items

ordersDELETEorder_id = 100
order_id = 110

must exists in orders
delete in cascade© 2022 SRA OSS LLC

INSERTorder_id = 100
order_id = 110
23

Foreign Key Constraint Triggers
CREATE TABLE orders (order_id integer PRIMARY KEY,...
);
CREATE TABLE order_items (order_id integer REFERENCES orders ON DELETE CASCADE,...
);

DELETEUPDATE

Table:
ordersTriggers

RI_ConstraintTrigger_a_xxxx

check

Functions:
RI_FKey_check_ins()
RI_FKey_check_upd()
Functions:
RI_FKey_cascade_del()RI_FKey_noaction_upd()© 2022 SRA OSS LLC

INSERT
UPDATE
Triggers

RI_ConstraintTrigger_c_xxxx

deleteerror

Table:
order_items
24Primary Key / Unique / Exclusion Constraint●

Not deferrable–

Unique constrain is checked when a new index entry is inserted.

–

Exclusion constrain is checked in the executor.

–●

Function:unique_key_recheck()check
Table: t

Trigger

No constraint triggers

Unique_ConstraintTrigger_xxx

Deferrable–

Constraints are checked using constraint triggers.
Transaction
Start
BEGIN;

Statement
Start

INSERT INTO t VALUES (10)© 2022 SRA OSS LLC

deferred

immediate

StatementEnd

Index:
idx_t_i

TransactionEnd
COMMIT;

time
25

System Catalogs
pg_trigger
pg_index
Index
Info.

pg_class
Table

Index

Trigger
Trigger name
Trigger type
Arguments
WHEN condition
Transition table names

pg_proc
Function...
pg_constraint
Constraint© 2022 SRA OSS LLC

26

How Triggers Work© 2022 SRA OSS LLC

27

How a trigger is fired
Executor(ModifyTable plan node)
modify- INSERT- DELETE- UPDATE- MERGE

Table

Trigger

Function© 2022 SRA OSS LLC

28

How a trigger is fired
Executor(ModifyTable plan node)
modify- INSERT- DELETE- UPDATE- MERGE

Table

check
- if the trigger is enabled- WNEN condition etc.

Trigger

Function© 2022 SRA OSS LLC

29

How a trigger is fired
Executor(ModifyTable plan node)
modify- INSERT- DELETE- UPDATE- MERGE

Table

check
- if the trigger is enabled- WNEN condition etc.

execute- with TriggerData- trigger type- a NEW/OLD row- transition tables- arguments- …and more

Trigger

Function© 2022 SRA OSS LLC

30

How a trigger is fired
Executor(ModifyTable plan node)
modify- INSERT- DELETE- UPDATE- MERGE

Table

check
- if the trigger is enabled- WHEN condition etc.

execute- with TriggerData- trigger type- a NEW/OLD row- transition tables- arguments- …and more

Trigger

return- (modified)
NEW row- or NULL forskipping theoperation

Function© 2022 SRA OSS LLC

31

When Triggers are Fired:(1) Statement-level BEFORE triggers
UPDATE tbl SET val = val + 100 WHERE flag = 1;

tbl
id = 1, val = 10, flag = 0

Executed Triggers:
Statement-level BEFORE triggers

id = 2, val = 20, flag = 0
id = 3, val = 30, flag = 1
id = 4, val = 40, flag = 1
id = 5, val = 50, flag = 0
id = 6, val = 60, flag = 1
id = 7, val = 70, flag = 0
id = 8, val = 80, flag = 0© 2022 SRA OSS LLC

32

When Triggers are Fired:(2) The statement starts processing rows
UPDATE tbl SET val = val + 100 WHERE flag = 1;
Start tbl
id = 1, val = 10, flag = 0

Executed Triggers:
Statement-level BEFORE triggers

id = 2, val = 20, flag = 0
id = 3, val = 30, flag = 1
id = 4, val = 40, flag = 1
id = 5, val = 50, flag = 0
id = 6, val = 60, flag = 1
id = 7, val = 70, flag = 0
id = 8, val = 80, flag = 0© 2022 SRA OSS LLC

33

When Triggers are Fired:(3) Row-level BEFORE triggers
UPDATE tbl SET val = val + 100 WHERE flag = 1;
Start tbl
id = 1, val = 10, flag = 0
id = 2, val = 20, flag = 0

Executed Triggers:
Statement-level BEFORE triggers
Row-level BEFORE triggers:
for id = 3

id = 3, val = 30, flag = 1
id = 4, val = 40, flag = 1
id = 5, val = 50, flag = 0
id = 6, val = 60, flag = 1
id = 7, val = 70, flag = 0
id = 8, val = 80, flag = 0© 2022 SRA OSS LLC

34

When Triggers are Fired:(4) The row is updated
UPDATE tbl SET val = val + 100 WHERE flag = 1;
Start tbl
id = 1, val = 10, flag = 0
id = 2, val = 20, flag = 0

Executed Triggers:
Statement-level BEFORE triggers
Row-level BEFORE triggers:
for id = 3

id = 3, val = 130, flag = 1
id = 4, val = 40, flag = 1
id = 5, val = 50, flag = 0
id = 6, val = 60, flag = 1
id = 7, val = 70, flag = 0
id = 8, val = 80, flag = 0© 2022 SRA OSS LLC

35

When Triggers are Fired:(5) Row-level AFTER triggers
UPDATE tbl SET val = val + 100 WHERE flag = 1;
Start tbl
id = 1, val = 10, flag = 0
id = 2, val = 20, flag = 0

Executed Triggers:
Statement-level BEFORE triggers
Row-level BEFORE triggers:
for id = 3

Transition Tables:
OLD table:(id=3, val=30, flag=1)

id = 3, val = 130, flag = 1
id = 4, val = 40, flag = 1
id = 5, val = 50, flag = 0
id = 6, val = 60, flag = 1
id = 7, val = 70, flag = 0
id = 8, val = 80, flag = 0

queue:
Row-level AFTER triggers:
for id = 3© 2022 SRA OSS LLC

NEW table:(id=3, val=130, flag=1)

36

When Triggers are Fired:(6) Processing the next rows ...
UPDATE tbl SET val = val + 100 WHERE flag = 1;
Start tbl
id = 1, val = 10, flag = 0
id = 2, val = 20, flag = 0

Executed Triggers:
Statement-level BEFORE triggers
Row-level BEFORE triggers:
for id = 3, 4, 6

id = 3, val = 130, flag = 1

Transition Tables:
OLD table:(id=3, val=30, flag=1)(id=4, val=40, flag=1)(id=6, val=60, flag=1)

id = 4, val = 140, flag = 1
id = 5, val = 50, flag = 0
id = 6, val = 160, flag = 1
id = 7, val = 70, flag = 0
id = 8, val = 80, flag = 0

queue:
Row-level AFTER triggers:
for id = 3, 4, 6
© 2022 SRA OSS LLC

NEW table:(id=3, val=130, flag=1)(id=3, val=140, flag=1)(id=3, val=160, flag=1)

37

When Triggers are Fired:(7) Statement-level AFTER triggers
UPDATE tbl SET val = val + 100 WHERE flag = 1;
Start tbl
id = 1, val = 10, flag = 0
id = 2, val = 20, flag = 0

Executed Triggers:
Statement-level BEFORE triggers
Row-level BEFORE triggers:
for id = 3, 4, 6

id = 3, val = 130, flag = 1

Transition Tables:
OLD table:(id=3, val=30, flag=1)(id=4, val=40, flag=1)(id=6, val=60, flag=1)

id = 4, val = 140, flag = 1
id = 5, val = 50, flag = 0
id = 6, val = 160, flag = 1
id = 7, val = 70, flag = 0
id = 8, val = 80, flag = 0

Endqueue:
Row-level AFTER triggers:
for id = 3, 4, 6
Statement-level AFTER triggers© 2022 SRA OSS LLC

NEW table:(id=3, val=130, flag=1)(id=3, val=140, flag=1)(id=3, val=160, flag=1)

38

When Triggers are Fired:(8) Queued triggers are executed
UPDATE tbl SET val = val + 100 WHERE flag = 1;
Start tbl
id = 1, val = 10, flag = 0
id = 2, val = 20, flag = 0
id = 3, val = 130, flag = 1
id = 4, val = 140, flag = 1

Transition Tables:
Executed Triggers:
Statement-level BEFORE triggers
OLD table:
Row-level BEFORE triggers:
Refer to (id=3, val=30, flag=1)
for id = 3, 4, 6
(id=4, val=40, flag=1)
Row-level AFTER triggers:(id=6, val=60, flag=1)
for id = 3, 4, 6
Statement-level AFTER triggers

id = 5, val = 50, flag = 0
id = 6, val = 160, flag = 1

NEW table:(id=3, val=130, flag=1)(id=3, val=140, flag=1)(id=3, val=160, flag=1)

queue:

id = 7, val = 70, flag = 0
id = 8, val = 80, flag = 0

End© 2022 SRA OSS LLC

39

Multiple Triggers on a Table
Multiple triggers for the same event on the same relation
are fired alphabetical order by name.

●Row-level
BEFORE triggers:

Statement-level
BEFORE triggers:

Row-level
AFTER triggers:

Statement-level
AFTER triggers:

For row1:

For row2:

For row1:

For row2:

Before_Statement_A

Before_Row_A

Before_Row_A

After_Row_A

After_Row_A

Before_Statement_B

Before_Row_B

Before_Row_B

After_Row_B

After_Row_B

Before_Statement_C

Before_Row_C

Before_Row_C

After_Row_C

After_Row_C

After_Statement_C

:
:

:
:

:
:

:
:

:
:

:
:

...

© 2022 SRA OSS LLC

After_Statement_A...

After_Statement_B

40

Deferred Constraint Triggers●

Not executed at the end of the statement●

Moved to the trigger queue for the transaction●

Executed at the end of the transaction
Statement End

queue:
Row-level AFTER trigger (immediate)
Row-level AFTER trigger (deferred)
Row-level AFTER trigger (immediate)
Row-level AFTER trigger (deferred)
Statement-level AFTER trigger

Transaction End
Executed
queue for the transaction:
Row-level AFTER trigger (deferred)
Row-level AFTER trigger (deferred):© 2022 SRA OSS LLC

Executed

41

SET CONSTRAINTS●

Deferred constraint trigger in the queue are executed
when changed to immediate.
BEGIN;INSERT INTO tbl VALUES(999);
SET CONSTRAINTS tbl_check_constraint IMMEDIATE;

queue:SET CONSTRAINTSStatement End
queue for the transaction:

Row-level AFTER trigger (deferred)
Row-level AFTER trigger (deferred)

Row-level AFTER trigger (deferred)
Row-level AFTER trigger (deferred):© 2022 SRA OSS LLC(immediate)

Executed42

Triggers and Transaction●

Triggers are executed as part of the same transaction as
the statement that triggered it.
– Regardless of whether it is deferred or not●

If either the statement or the trigger causes an error, theeffects of both will be rolled back.

© 2022 SRA OSS LLC

43

Some Complex Situations© 2022 SRA OSS LLC

44

INSERT ... ON CONFLICT DO UPDATEINSERT INTO tbl VALUES (1,100), (2,200)
ON CONFLICT(id) DO UPDATE val = EXCLUDED.val;

tbl
id = 2, val = 20

INSERTid = 1, val = 100
id = 2, val = 200© 2022 SRA OSS LLC

45

INSERT ... ON CONFLICT DO UPDATEINSERT INTO tbl VALUES (1,100), (2,200)
ON CONFLICT(id) DO UPDATE val = EXCLUDED.val;
Statement-level triggers for INSERTBEFORE and AFTER

tbl
id = 2, val = 20

Statement-level triggers for UPDATEBEFORE and AFTER

INSERTid = 1, val = 100
id = 2, val = 200© 2022 SRA OSS LLC

46

INSERT ... ON CONFLICT DO UPDATEINSERT INTO tbl VALUES (1,100), (2,200)
ON CONFLICT(id) DO UPDATE val = EXCLUDED.val;
Statement-level triggers for INSERTBEFORE and AFTER

tbl
id = 2, val = 20

INSERTid = 1, val = 100
id = 2, val = 200

Statement-level triggers for UPDATEBEFORE and AFTER

successfully
insertedRow-level triggers for INSERT (1, 100)
BEFORE and AFTER

tried to insert

Row-level triggers for INSERT (1, 200)
BEFORE only

© 2022 SRA OSS LLC

47

INSERT ... ON CONFLICT DO UPDATEINSERT INTO tbl VALUES (1,100), (2,200)
ON CONFLICT(id) DO UPDATE val = EXCLUDED.val;
Statement-level triggers for UPDATEBEFORE and AFTER

Statement-level triggers for INSERTBEFORE and AFTER

tbl
id = 2, val = 20

INSERT

successfully
insertedRow-level triggers for INSERT (1, 100)
BEFORE and AFTER

tried to insert

Row-level triggers for INSERT (1, 200)
BEFORE only

id = 1, val = 100
id = 2, val = 200

on conflict

Row-level triggers for UPDATE (2,20) → (2,200)
BEFORE and AFTER© 2022 SRA OSS LLC

48

MERGE
MERGE INTO wines w
USING wine_stock_changes s
ON s.winename = w.winename
WHEN NOT MATCHED AND s.stock_delta > 0 THEN
INSERT VALUES(s.winename, s.stock_delta)
WHEN MATCHED AND w.stock + s.stock_delta > 0 THENUPDATE SET stock = w.stock + s.stock_delta
WHEN MATCHED THEN
DELETE;
wine_stock_changes(winename, stock_delta)

wines (winename, stock)‘Chateau Margaux 2000’, 1'Chateau Lafite 2003', 2'Chateau Lafite 2003', 1‘Chateau Latour 1997’, -1

MERGE‘Chateau Latour 1997’, 1© 2022 SRA OSS LLC

49

MERGE
MERGE INTO wines w
USING wine_stock_changes s
ON s.winename = w.winename
Statement-level triggers for INSERTWHEN NOT MATCHED AND s.stock_delta > 0 THEN
INSERT VALUES(s.winename, s.stock_delta)
WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN Statement-level triggers for UPDATEUPDATE SET stock = w.stock + s.stock_delta
WHEN MATCHED THEN
Statement-level triggers for DELETE
DELETE;
wine_stock_changes(winename, stock_delta)

wines (winename, stock)‘Chateau Margaux 2000’, 1'Chateau Lafite 2003', 2'Chateau Lafite 2003', 1‘Chateau Latour 1997’, -1

MERGE‘Chateau Latour 1997’, 1© 2022 SRA OSS LLC

50

MERGE
MERGE INTO wines w
USING wine_stock_changes s
ON s.winename = w.winename
Statement-level triggers for INSERTWHEN NOT MATCHED AND s.stock_delta > 0 THEN
INSERT VALUES(s.winename, s.stock_delta)
WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN Statement-level triggers for UPDATEUPDATE SET stock = w.stock + s.stock_delta
WHEN MATCHED THEN
Statement-level triggers for DELETE
DELETE;
wine_stock_changes(winename, stock_delta)

wines (winename, stock)inserted'Chateau Lafite 2003', 2

updated Row-level triggers for UPDATE‘Chateau Margaux 2000’, 1'Chateau Lafite 2003', 1‘Chateau Latour 1997’, -1

MERGE‘Chateau Latour 1997’, 1deleted© 2022 SRA OSS LLC

Row-level triggers for INSERT

Row-level triggers for DELETE
51

Triggers on Partitioned Table
UPDATE tbl SET key = 7 WHERE key = 3;
Partitioned Table:
tbl

Partition: tbl_1( 1 ≦ key ≦10)

Partition: tbl_2( 11 ≦ key ≦20)

key = 3 → 7

key = 12

key = 5key = 16

© 2022 SRA OSS LLC

52

Triggers on Partitioned Table
UPDATE tbl SET key = 7 WHERE key = 3;
Partitioned Table:
tbl

Statement-level triggers for UPDATE on tbl
BEFORE and AFTER

Row-level triggers for UPDATE on tbl_1
BEFORE and AFTER

Partition: tbl_1( 1 ≦ key ≦10)

Partition: tbl_2( 11 ≦ key ≦20)

key = 3 → 7

key = 12

key = 5key = 16

© 2022 SRA OSS LLC

53

Triggers on Partitioned Table:
moving between partitions
UPDATE tbl SET key = 14 WHERE key = 7;
Partitioned Table:
tbl

Partition: tbl_1( 1 ≦ key ≦10)
deletedkey = 7 → 14

Partition: tbl_2( 11 ≦ key ≦20)
moved

key = 5key = 12
key = 16
key = 14© 2022 SRA OSS LLC

inserted54

Triggers on Partitioned Table:
moving between partitions
UPDATE tbl SET key = 14 WHERE key = 7;
Partitioned Table:
tbl

Statement-level triggers for UPDATE on tbl
BEFORE and AFTER

Row-level triggers for UPDATE on tbl_1
BEFORE only

Partition: tbl_1( 1 ≦ key ≦10)
deletedkey = 7 → 14

Partition: tbl_2( 11 ≦ key ≦20)
moved

key = 5key = 12
key = 16
key = 14© 2022 SRA OSS LLC

inserted55

Triggers on Partitioned Table:
moving between partitions
UPDATE tbl SET key = 14 WHERE key = 7;
Partitioned Table:
tbl

Statement-level triggers for UPDATE on tbl
BEFORE and AFTER

Row-level triggers for UPDATE on tbl_1
BEFORE only

Partition: tbl_1( 1 ≦ key ≦10)
deletedkey = 7 → 14

Partition: tbl_2( 11 ≦ key ≦20)
moved

key = 5
Row-level triggers for DELETE on tbl_1
BEFORE and AFTER

key = 12
key = 16
key = 14© 2022 SRA OSS LLC

Row-level triggers for INSERT on tbl_2
BEFORE and AFTER

inserted
56

Summary●

Triggers– Automatically executed whenever a certain type of operationis performed– Internally created and used for constraints implementation●

How triggers work
– How and when it is fired– Some complex situations© 2022 SRA OSS LLC

57

Thank you!

© 2022 SRA OSS LLC

58