Waiting for PostgreSQL 10 – Implement syntax for transition tables in AFTER triggers.

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

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