Minimize PostgreSQL Trigger Code Calls

For the longest time, I was just writing row-level triggers. It was easy and familiar. But if you're working with high-change-traffic tables, it can be a bit inefficient depending on your needs.

For instance, if you're auditing your data changes, those triggers fire your functions on every row affected. You could be potentially spamming your DB with individual queries to write more data.

But, recently, I decided to finally dig into something I've been meaning to learn for years: Statement-Level Triggers. I had been stuck for awhile wondering how the trigger function would deal with data or if the trigger was restricted to other types of activity. But the good folks at PostgreSQL.org had thought of that.

So, how do they work? You have to change your programming style a bit for the trigger functions to operate on tables vs individual atomic records. Instead of having a OLD record and a NEW record, you have access to a OLD and NEW TABLES instead. Similarly to records, these will have the before and after views of all records affected by the statement. These statement-level triggers are applicable only as AFTER triggers and cannot support a column list on UPDATE triggers.

Demonstration! (BTW, These SQL code blocks are intended for copy/paste into a psql session if you want to follow along.)

First, let's create example tables for an employee:

create table employee (
    id serial primary key,
    title text not null,
    surname text not null,
    forename text not null,
    employment_start date not null,
    employment_end date,
    company_acl jsonb not null default '{}'::jsonb
);

create table employee_audit (
    audit_ts timestamptz not null,
    audit_action text not null,
    id integer,
    title text,
    surname text,
    forename text,
    employment_start date,
    employment_end date,
    company_acl jsonb
);

Let's get some proof of the number of executions:

create sequence exec_seq start with 1 no maxvalue increment by 1;

Now, let's create the audit function:

create function emp_audit() returns trigger
as $$
declare
    call_count integer;
begin
    select nextval('exec_seq'::regclass)
      into call_count;
    raise info 'Total Function Executions: %', call_count;

    if TG_OP = 'DELETE'
    then
        insert into employee_audit
        select now(), TG_OP, *
          from old_employee;
    else
        insert into employee_audit
        select now(), TG_OP, *
          from new_employee;
    end if;

    return null;
end;
$$ language plpgsql;

Note that we are using tables named old_employee and new_employee... where'd they come from? Read on, true believer!

So let's create the trigger now. There's a restriction that transition tables cannot be specified for multiple-event triggers, so we will actually need to create three distinct triggers:

create trigger ins_audit_employee
 after insert
    on employee
       referencing new table as new_employee
   for each statement
       execute function emp_audit();

create trigger udt_audit_employee
 after update
    on employee
       referencing old table as old_employee
                   new table as new_employee
   for each statement
       execute function emp_audit();

create trigger del_audit_employee
 after delete
    on employee
       referencing old table as old_employee
   for each statement
       execute function emp_audit();

Note that the old_employee and new_employee table names were specified in the trigger create. These names are made available to the trigger function.

So let's create some records:

insert 
  into employee (
           title,
           surname,
           forename,
           employment_start,
           employment_end,
           company_acl
       )
values
(
    'Captain America',
    'Rogers',
    'Steve',
    '1964-03-01'::date,
    null,
    '{"avengers_compound": true, "weapons": {"shield": true, "Mjolnir": true}}'::jsonb
),
(
    'God of Thunder',
    'Odinson',
    'Thor',
    '1963-11-01'::date,
    null,
    '{"avengers_compound": true, "weapons": {"Stormbreaker": true, "Mjolnir": true}}'::jsonb
),
(
    'Spider-Man',
    'Parker',
    'Peter',
    '1983-06-01'::date,
    null,
    '{"avengers_compound": true, "weapons": {"web-shooters": true}}'::jsonb
);
INFO:  Total Function Executions: 1
INSERT 0 3

Notice that there was a call-count of 1 even though that statement inserted 3 records. That's because there was only 1 statement.

Let's verify the data:

postgres=# select * from employee;
 id |      title      | surname | forename | employment_start | employment_end |                                   company_acl                                   
----+-----------------+---------+----------+------------------+----------------+---------------------------------------------------------------------------------
  1 | Captain America | Rogers  | Steve    | 1964-03-01       |                | {"weapons": {"shield": true, "Mjolnir": true}, "avengers_compound": true}
  2 | God of Thunder  | Odinson | Thor     | 1963-11-01       |                | {"weapons": {"Mjolnir": true, "Stormbreaker": true}, "avengers_compound": true}
  3 | Spider-Man      | Parker  | Peter    | 1983-06-01       |                | {"weapons": {"web-shooters": true}, "avengers_compound": true}
(3 rows)

postgres=# select * from employee_audit;
           audit_ts            | audit_action | id |      title      | surname | forename | employment_start | employment_end |                                   company_acl                                   
-------------------------------+--------------+----+-----------------+---------+----------+------------------+----------------+---------------------------------------------------------------------------------
 2021-06-17 16:16:12.486884+00 | INSERT       |  1 | Captain America | Rogers  | Steve    | 1964-03-01       |                | {"weapons": {"shield": true, "Mjolnir": true}, "avengers_compound": true}
 2021-06-17 16:16:12.486884+00 | INSERT       |  2 | God of Thunder  | Odinson | Thor     | 1963-11-01       |                | {"weapons": {"Mjolnir": true, "Stormbreaker": true}, "avengers_compound": true}
 2021-06-17 16:16:12.486884+00 | INSERT       |  3 | Spider-Man      | Parker  | Peter    | 1983-06-01       |                | {"weapons": {"web-shooters": true}, "avengers_compound": true}
(3 rows)

As you can see, we have our 3 employee records and the one invocation of the trigger function inserted the records as well as the trigger operation and trigger time into the audit table.

Apparently, some googling of Marvel's comic timelines, Spidey left the Avengers in the mid 1980's. I don't know the exact date, but let's assume May 15, 1986 for the sake of this demo:

update employee 
   set employment_end = '1986-05-15'::date
 where id = 3;
INFO:  Total Function Executions: 2
UPDATE 1

Note that the output shows the call count at 2.

Let's check the data:

postgres=# select * from employee where id = 3;
 id |   title    | surname | forename | employment_start | employment_end |                          company_acl                           
---------+------------+---------+----------+------------------+----------------+----------------------------------------------------------------
  3 | Spider-Man | Parker  | Peter    | 1983-06-01       | 1986-05-15     | {"weapons": {"web-shooters": true}, "avengers_compound": true}
(1 row)

postgres=# select * from employee_audit where id = 3 order by audit_ts;
           audit_ts            | audit_action | id |   title    | surname | forename | employment_start | employment_end |                          company_acl                           
------------------------------------+--------------+----+------------+---------+----------+------------------+----------------+----------------------------------------------------------------
 2021-06-17 16:27:23.53353+00  | INSERT       |  3 | Spider-Man | Parker  | Peter    | 1983-06-01       |                | {"weapons": {"web-shooters": true}, "avengers_compound": true}
 2021-06-17 16:29:28.923033+00 | UPDATE       |  3 | Spider-Man | Parker  | Peter    | 1983-06-01       | 1986-05-15     | {"weapons": {"web-shooters": true}, "avengers_compound": true}
(2 rows)

So the employee record has the change recorded and the employee_audit table has 2 records for Spider-Man.

Let's try the delete action:

delete from employee where id > 1;
INFO:  Total Function Executions: 3
DELETE 2

Note the call count is still only executing by statement instead of record.

Let's verify:

postgres=# select * from employee;
 id |      title      | surname | forename | employment_start | employment_end |                                company_acl                                
---------+-----------------+---------+----------+------------------+----------------+---------------------------------------------------------------------------
  1 | Captain America | Rogers  | Steve    | 1964-03-01       |                | {"weapons": {"shield": true, "Mjolnir": true}, "avengers_compound": true}
(1 row)

postgres=# select * from employee_audit order by id, audit_ts;
           audit_ts            | audit_action | id |      title      | surname | forename | employment_start | employment_end |                                   company_acl                                   
------------------------------------+--------------+----+-----------------+---------+----------+------------------+----------------+---------------------------------------------------------------------------------
 2021-06-17 16:27:23.53353+00  | INSERT       |  1 | Captain America | Rogers  | Steve    | 1964-03-01       |                | {"weapons": {"shield": true, "Mjolnir": true}, "avengers_compound": true}
 2021-06-17 16:27:23.53353+00  | INSERT       |  2 | God of Thunder  | Odinson | Thor     | 1963-11-01       |                | {"weapons": {"Mjolnir": true, "Stormbreaker": true}, "avengers_compound": true}
 2021-06-17 16:33:36.526134+00 | DELETE       |  2 | God of Thunder  | Odinson | Thor     | 1963-11-01       |                | {"weapons": {"Mjolnir": true, "Stormbreaker": true}, "avengers_compound": true}
 2021-06-17 16:27:23.53353+00  | INSERT       |  3 | Spider-Man      | Parker  | Peter    | 1983-06-01       |                | {"weapons": {"web-shooters": true}, "avengers_compound": true}
 2021-06-17 16:29:28.923033+00 | UPDATE       |  3 | Spider-Man      | Parker  | Peter    | 1983-06-01       | 1986-05-15     | {"weapons": {"web-shooters": true}, "avengers_compound": true}
 2021-06-17 16:33:36.526134+00 | DELETE       |  3 | Spider-Man      | Parker  | Peter    | 1983-06-01       | 1986-05-15     | {"weapons": {"web-shooters": true}, "avengers_compound": true}
(6 rows)

So this shows that, writing your plpgsql trigger functions to operate on tables vs records, you can cut your number of trigger and trigger code executions down dramatically. This could really improve efficiency in certain operations.

I hope you found this quick dock and demo helpful.