Can modified tuples be accessed using a statement-level trigger in Postgres?

Well, the answer is yes, they can be accessed without any workaround. Thanks to the new feature - "Transition tables for Triggers" introduced in PostgreSQL-v10 which allows us to create transition tables using the REFERENCING clause in the CREATE TRIGGER statement to store the old and new version of tuples. In short, it gives us the ability to see the rows that got changed, in statement-level AFTER triggers. This makes statement-level triggers extremely useful and highly performant. Prior to this feature, statement-level triggers were very much underutilized just because it didn't have an access to the modified rows, unlike row-level triggers.

Let's see with the help of some examples how this new feature can be used with the statement-level AFTER trigger to see the rows that were changed.

Let's create a table called “t1” and insert some data in it.

CREATE TABLE t1 (c1 text NOT NULL, c2 integer);
INSERT INTO t1 VALUES('tup1', 10), ('tup2', 20);

Now, let's create a trigger function that will show the old and new tuples.

CREATE FUNCTION show_modified_tups() RETURNS TRIGGER AS $$
DECLARE
r1 record;
BEGIN
IF (TG_OP = 'DELETE') THEN
    FOR r1 IN SELECT * FROM old_table
    LOOP
        raise notice 'OLD: %', r1;
    END loop;
ELSIF (TG_OP = 'UPDATE') THEN
    FOR r1 IN SELECT * FROM new_table
    LOOP
        raise notice 'NEW: %', r1;
    END loop;
    FOR r1 IN SELECT * FROM old_table
    LOOP
        raise notice 'OLD: %', r1;
    END loop;
ELSIF (TG_OP = 'INSERT') THEN
    FOR r1 IN SELECT * FROM new_table
    LOOP
        raise notice 'NEW: %', r1;
    END loop;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

When the trigger function (created above) is used with the trigger, it will show us all the records from the "old_table" and "new_table" whenever insert/update/delete operation is performed on table t1. The old_table and new_table are just in-memory tables (known as transition tables) used to store the old and new version of tuples which would disappear as soon as the query has finished its execution. We'll define these shortly. Let's verify it by running "\d" command.

edb=# \d
   List of relations
Schema | Name | Type  | Owner
--------------+-------+--------
public | t1   | table | ashu
(1 row)

So, let's create some statement-level AFTER trigger with referencing clause for INSERT/UPDATE/DELETE operation on table “t1”.

CREATE TRIGGER t1_ins
 AFTER INSERT ON t1 REFERENCING NEW TABLE AS new_table
 FOR EACH STATEMENT EXECUTE PROCEDURE show_modified_tups();

CREATE TRIGGER t1_upd
 AFTER UPDATE ON t1 REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
 FOR EACH STATEMENT EXECUTE PROCEDURE show_modified_tups();

CREATE TRIGGER t1_del
 AFTER DELETE ON t1 REFERENCING OLD TABLE AS old_table
 FOR EACH STATEMENT EXECUTE PROCEDURE show_modified_tups();

Now, let's see how it works.

edb=# UPDATE t1 SET c1='tup1_new' WHERE c1='tup1';
NOTICE:  NEW: (tup1_new,10)
NOTICE:  OLD: (tup1,10)
UPDATE 1

edb=# UPDATE t1 SET c1='tup2_new' WHERE c1='tup2';
NOTICE:  NEW: (tup2_new,20)
NOTICE:  OLD: (tup2,20)
UPDATE 1

edb=# DELETE FROM t1;
NOTICE:  OLD: (tup1_new,10)
NOTICE:  OLD: (tup2_new,20)
DELETE 2

As seen from above example, when an UPDATE/DELETE operation was performed on “t1”, the trigger function fired and we could see all the modified tuples with the help of the statement-level trigger having a REFERENCING clause in it.  The above example showed just one simple use-case of transition tables in triggers. It can be used for various other purposes like - auditing some user tables or ensuring that when a tuple is inserted in some “xyz” table then it automatically gets inserted in some other dependent table as well.

So, to conclude, the most important aspect of transition tables in statement-level triggers is that it helps us to achieve something that we used to get with the help of row-level trigger with a lot of performance benefit. For example, let me try to explain the performance gain achieved when implementing table auditing with the help of a statement-level trigger compared to a row-level trigger

First, let's try out with the help of a row-level trigger.

Let's create an "emp" table with a row-level trigger on it which would ensure that any insert, update or delete of a row in the emp table is recorded in the emp_audit table. (Note: this example has been taken from the PostgreSQL documentation).

CREATE TABLE emp (empname text NOT NULL, salary integer);

CREATE TABLE emp_audit(
operation      char(1)   NOT NULL,
stamp          timestamp NOT NULL,
userid         text   NOT NULL,
empname        text   NOT NULL,
salary integer);

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
BEGIN
    IF (TG_OP = 'DELETE') THEN
        INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
    END IF;
    RETURN NULL;
END;
$emp_audit$ LANGUAGE plpgsql;

CREATE TRIGGER emp_audit
    AFTER INSERT OR UPDATE OR DELETE ON emp
    FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();

Now, let's try running insert, update or delete statements on the emp table with \timing on; Here are the results,

edb=# insert into emp select generate_series(1, 100000);
INSERT 0 100000
Time: 2420.499 ms

Finally, let's try doing what we did above with the help of transition tables.

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
BEGIN
    IF (TG_OP = 'DELETE') THEN
        INSERT INTO emp_audit
            SELECT 'D', now(), user, o.* FROM old_table o;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO emp_audit
            SELECT 'U', now(), user, n.* FROM new_table n;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO emp_audit
            SELECT 'I', now(), user, n.* FROM new_table n;
    END IF;
    RETURN NULL;
END;
$emp_audit$ LANGUAGE plpgsql;

CREATE TRIGGER emp_audit_ins
     AFTER INSERT ON emp
     REFERENCING NEW TABLE AS new_table
     FOR EACH STATEMENT EXECUTE PROCEDURE process_emp_audit();

CREATE TRIGGER emp_audit_upd
     AFTER UPDATE ON emp
     REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
     FOR EACH STATEMENT EXECUTE PROCEDURE process_emp_audit();

CREATE TRIGGER emp_audit_del
     AFTER DELETE ON emp
     REFERENCING OLD TABLE AS old_table
     FOR EACH STATEMENT EXECUTE PROCEDURE process_emp_audit();

Here are the results with timing on,

edb=# insert into emp select generate_series(1, 100000);
INSERT 0 100000
Time: 245.243 ms

As seen from the above two examples, it is clear that, when the same thing (i.e. table auditing in our case) is done with the help of transition tables, the execution time of the query reduces by 10 times which is a huge gain.