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 msAs 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.