Another one missed, quite a long time ago, too..:
On 4th of November 2016, Kevin Grittner committed patch:
Implement syntax for transition tables in AFTER triggers. This is infrastructure for the complete SQL standard feature. No support is included at this point for execution nodes or PLs. The intent is to add that soon. As this patch leaves things, standard syntax can create tuplestores to contain old and/or new versions of rows affected by a statement. References to these tuplestores are in the TriggerData structure. C triggers can access the tuplestores directly, so they are usable, but they cannot yet be referenced within a SQL statement.
It looks that we will now have ability to see rows that got changed, in statement-level triggers.
Let's test the idea:
$ CREATE TABLE test (id serial PRIMARY KEY, payload text); CREATE TABLE $ INSERT INTO test (payload) VALUES ( 'boor' ), ( 'burble' ), ( 'debits' ), ( 'dwells' ), ( 'gloat' ), ( 'lurked' ), ( 'mambo' ), ( 'mush' ), ( 'noised' ), ( 'peddle' ); INSERT 0 10
Now, let us quickly write test function:
$ CREATE FUNCTION test_trigger() RETURNS TRIGGER AS $$ DECLARE temprec record; BEGIN FOR temprec IN SELECT * FROM v_old_table LOOP raise notice 'OLD: %', temprec; END loop; FOR temprec IN SELECT * FROM v_new_table LOOP raise notice 'NEW: %', temprec; END loop; RETURN NEW; END; $$ LANGUAGE plpgsql;
This function, when used as trigger, will show us all records from “v_old_table" and “v_new_table". Of course, I don't have any such tables, as I only have test:
$ \d List OF relations Schema | Name | TYPE | Owner --------+-------------+----------+-------- public | test | TABLE | depesz public | test_id_seq | SEQUENCE | depesz (2 ROWS)
So, let's create the trigger:
$ CREATE TRIGGER test_it AFTER UPDATE ON test REFERENCING NEW TABLE AS v_new_table OLD TABLE AS v_old_table FOR EACH STATEMENT EXECUTE PROCEDURE test_trigger(); CREATE TRIGGER
And now, let's see how it works:
$ UPDATE test SET payload = 'depesz' WHERE id = 1; NOTICE: OLD: (1,boor) NOTICE: NEW: (1,depesz) UPDATE 1
What about larger updates?
$ UPDATE test SET payload = payload || 'xx' WHERE id > 6 AND payload <> 'noised'; NOTICE: OLD: (7,mambo) NOTICE: OLD: (8,mush) NOTICE: OLD: (10,peddle) NOTICE: NEW: (7,mamboxx) NOTICE: NEW: (8,mushxx) NOTICE: NEW: (10,peddlexx) UPDATE 3
I'm not testing for insert or delete triggers, as these seem obvious.
This is (was?) huge. Whole classes of setups will now become easier and faster because you will be able to run summaries or materialization triggers once per statement, and not once per row. In my own history it would be a big win many, many times. Thanks a lot to all involved, and sorry for missing it.
Also, thanks to albertus1 on irc for reminding me about it.
2 thoughts on “Waiting for PostgreSQL 10 – Implement syntax for transition tables in AFTER triggers.”
This is the most underrated feature of PG 10.
Completely missed this one… And thanks for elaborating the use case. Whoever’s ever been troubled by performance issues using row triggers, would inherently see it’s value.
Comments are closed.