Statement Triggers in Postgres

Sep 7, 2024

Postgres can define a trigger for each row, or for each statement.

For a while, statement triggers were not used much (I’ve never seen one in the wild actually), because they couldn’t access the rows modified by the statement.

But in Postgres 10, they introduced something called “transition tables” which allow you to access the rows that were modified by the statement.

I’ll give a quick example, adapted from this article.

1
create table books (
2
  id serial primary key not null,
3
  name text not null
4
);
5

6
create or replace function show_modified_tups() returns trigger as $$
7
declare
8
r1 record;
9
begin
10
if (tg_op = 'DELETE') then
11
    for r1 in select * from old
12
    loop
13
        raise notice 'old: %', r1;
14
    end loop;
15
elsif (tg_op = 'UPDATE') then
16
    for r1 in select * from new
17
    loop
18
        raise notice 'new: %', r1;
19
    end loop;
20
    for r1 in select * from old
21
    loop
22
        raise notice 'old: %', r1;
23
    end loop;
24
elsif (tg_op = 'INSERT') THEN
25
    for r1 in select * from new
26
    loop
27
        raise notice 'new: %', r1;
28
    end loop;
29
end if;
30
return new;
31
end;
32
$$ language plpgsql;
33

34
create trigger log_books_inserted
35
after insert on books
36
referencing new table as new
37
for each statement
38
execute function show_modified_tups ();
39

40
create trigger log_books_updated
41
after update on books
42
referencing new table as new old table as old
43
for each statement
44
execute function show_modified_tups ();
45

46
create trigger log_books_deleted
47
after delete on books
48
referencing old table as old
49
for each statement
50
execute function show_modified_tups ();

And testing it..

1
insert into books (name) values
2
('The Great Gatsby'),
3
('The Catcher in the Rye'),
4
('Moonraker');
5

6
NOTICE:  new: (1,"The Great Gatsby")
7
NOTICE:  new: (2,"The Catcher in the Rye")
8
NOTICE:  new: (3,Moonraker)

1
update books
2
  set name = 'THE GREAT GATSBY'
3
where id = 1;
4

5
NOTICE:  new: (1,"THE GREAT GATSBY")
6
NOTICE:  old: (1,"The Great Gatsby")

1
delete from books
2
where id = 2;
3

4
NOTICE:  old: (2,"The Catcher in the Rye")

Additional Notes

An operation that modifies zero rows will still result in the execution of a statement trigger.
Instead Of triggers on views can only be for each row.
Statement triggers can be called for Truncate statements.
The when condition is borderline useless in statement level triggers, because you can’t refer to any values in the table.
Transition relations are only available in After triggers that are not constraint triggers.
- If the trigger is an update trigger, you can’t specify a column_name list.

This table from the Postgres documentation is a good summary of the differences between row and statement triggers:

When	Event	Row-level	Statement-level
Before	Insert / Update / Delete	Tables and foreign tables	Tables, views, and foreign tables
Before	Truncate	—	Tables and foreign tables
After	Insert / Update / Delete	Tables and foreign tables	Tables, views, and foreign tables
After	Truncate	—	Tables and foreign tables
Instead Of	Insert / Update / Delete	Views	—
Instead Of	Truncate	—	—

Revisiting The Pyramid Scheme

In my last blog post, I created an example of triggers killing performance and how to work around it.

Let’s see if statement triggers would have been a useful solution. The full example would look like this:

1
drop table if exists members;
2

3
create table members (
4
  id serial primary key,
5
  name text not null,
6
  revenue numeric(10, 2) not null default 0,
7
  referred_revenue numeric(10, 2) not null default 0,
8

9
  referred_by integer references members (id)
10
);
11

12
create or replace function update_referred_revenue_on_insert() returns trigger as $$
13
begin
14
  update members
15
    set referred_revenue = referred_revenue + total_revenue
16
  from (
17
    select referred_by, sum(revenue) as total_revenue
18
    from new
19
    group by referred_by
20
    having referred_by is not null
21
  ) as new_referrals
22
  where members.id = new_referrals.referred_by;
23

24
  return new;
25
end;
26
$$ language plpgsql;
27

28
create trigger update_referred_revenue_on_insert
29
after insert on members
30
referencing new table as new
31
execute function update_referred_revenue_on_insert ();
32

33
create or replace function update_referred_revenue_on_update() returns trigger as $$
34
declare
35
  count_updated integer;
36
begin
37
  select count(*) into count_updated
38
  from new;
39

40
  if count_updated = 0 then
41
    return new;
42
  end if;
43

44
  update members
45
    set referred_revenue = referred_revenue + new_referrals.new_total_revenue
46
  from (
47
    select
48
      n.referred_by,
49
      sum(n.revenue - o.revenue) + sum(n.referred_revenue - o.referred_revenue) as new_total_revenue
50
    from new n
51
    join old o on n.id = o.id
52
    group by n.referred_by
53
    having n.referred_by is not null
54
  ) as new_referrals
55
  where members.id = new_referrals.referred_by;
56

57
  return new;
58
end;
59
$$ language plpgsql;
60

61
create trigger update_referred_revenue_on_update
62
after update on members
63
referencing new table as new old table as old
64
execute function update_referred_revenue_on_update ();

And then create a few members:

1
insert into members (name, revenue) values ('me', 100);
2
insert into members (name, revenue, referred_by) values ('Stella', 100, 1);
3
insert into members (name, revenue, referred_by) values ('Cornelius', 100, 1);
4
insert into members (name, revenue, referred_by) values ('Rita', 100, 2);
5
insert into members (name, revenue, referred_by) values ('Conrad', 100, 2);
6
insert into members (name, revenue, referred_by) values ('Helmut', 100, 3);
7
insert into members (name, revenue, referred_by) values ('Manfried', 100, 3);

We need to make sure we can handle bulk queries - where multiple rows affect a single referrer. Let’s test that.

1
insert into members (name, revenue, referred_by)
2
select 'member' || i::text, 10, 3
3
from generate_series(1, 5) as i;

Let’s take a look at the data:

Name	Revenue	Referred Revenue
me	100.00	650.00
Stella	100.00	200.00
Cornelius	100.00	250.00
Rita	100.00	0.00
Conrad	100.00	0.00
Helmut	100.00	0.00
Manfried	100.00	0.00
member1	10.00	0.00
member2	10.00	0.00
member3	10.00	0.00
member4	10.00	0.00
member5	10.00	0.00

Then let’s do another bulk update:

1
update members
2
  set revenue = 0
3
where id > 7;

Name	Revenue	Referred Revenue
me	100.00	600.00
Stella	100.00	200.00
Cornelius	100.00	200.00
Rita	100.00	0.00
Conrad	100.00	0.00
Helmut	100.00	0.00
Manfried	100.00	0.00
member1	0.00	0.00
member2	0.00	0.00
member3	0.00	0.00
member4	0.00	0.00
member5	0.00	0.00

Lastly, let’s add a ton of new members, to show that our new triggers can handle it performance-wise.

1
insert into members (name, revenue, referred_by)
2
select 'member' || i::text, 10, 3
3
from generate_series(1, 1000000) as i;

That takes ~6s, which is basically what the insert with no trigger takes.

What makes this complicated?

There are a couple new challenges.

The actual update calls need to handle the case where multiple new rows affect the same referrer. This is why we’re doing group by in the update statements.

e.g.

1
update members
2
  set referred_revenue = referred_revenue + total_revenue
3
from (
4
  select referred_by, sum(revenue) as total_revenue
5
  from new
6
  group by referred_by
7
  having referred_by is not null
8
) as new_referrals
9
where members.id = new_referrals.referred_by;
10

11
-- and
12
update members
13
  set referred_revenue = referred_revenue + new_referrals.new_total_revenue
14
from (
15
  select
16
    n.referred_by,
17
    sum(n.revenue - o.revenue) + sum(n.referred_revenue - o.referred_revenue) as new_total_revenue
18
  from new n
19
  join old o on n.id = o.id
20
  group by n.referred_by
21
  having n.referred_by is not null
22
) as new_referrals
23
where members.id = new_referrals.referred_by;

Furthermore, the update trigger is susceptible to an infinite loop, because it will get called even if no rows are updated. This is why we’re checking count_updated in the update trigger.

1
select count(*) into count_updated
2
from new;
3

4
if count_updated = 0 then
5
  return new;
6
end if;

Conclusion

In summary, statement triggers can be more performant than row level triggers. They’re likely an underused feature in Postgres.

Wow! You read the whole thing. People who make it this far sometimes want to receive emails when I post something new.

I also have an RSS feed.