Unexpected behavior with transition tables in update statement trigger

From: Tom Kazimiers <tom(at)voodoo-arts(dot)net>
To: PG-General Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: Unexpected behavior with transition tables in update statement trigger
Date: 2018-02-24 03:47:48
Message-ID: 20180224034748.bixarv6632vbxgeb@dewberry.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

Hi all,

I am on Postgres 10.2 and try to get a statement level trigger to work
that is executed after UPDATE statements on a particular table. This
trigger references both the old and new transition table and for some
reason I am unable to reference each transition table multiple times in
a CTE or subquery. E.g. forming a UNION ALL with all rows of the new
transition table with itself, does only use the new table row once. I
don't understand why and would appreciate some insight.

My problem is probably better described with some SQL, so here is a
little test setup:

CREATE TABLE test (id serial, data int);
INSERT INTO test VALUES (0, 1);

CREATE OR REPLACE FUNCTION on_edit() RETURNS trigger
LANGUAGE plpgsql AS
$$
DECLARE
tmp text;
BEGIN
WITH test AS (
SELECT row_to_json(a)::text FROM new_test a
UNION ALL
SELECT '----'
UNION ALL
SELECT row_to_json(b)::text FROM new_test b
)
SELECT array_to_string(array(SELECT row_to_json(t)::text FROM test t), ', ')::text INTO tmp;

PERFORM pg_notify('update', tmp::text);

WITH test AS (
SELECT row_to_json(a)::text FROM new_test a
UNION ALL
SELECT '----'
UNION ALL
SELECT row_to_json(b)::text FROM old_test b
)
SELECT array_to_string(array(SELECT row_to_json(t)::text FROM test t), ', ')::text INTO tmp;

PERFORM pg_notify('update', tmp::text);

RETURN NEW;
END;
$$;

CREATE TRIGGER on_edit AFTER UPDATE ON test
REFERENCING NEW TABLE AS new_test OLD TABLE as old_test
FOR EACH STATEMENT EXECUTE PROCEDURE on_edit();

LISTEN update;

UPDATE test SET data = 2;

This will create a new table test with one entry, adds the statement
level trigger, registers a NOTIFY listener and updates the table. The
trigger will first NOTIFY the result of a UNION ALL with the new
transition table with itself. The second NOTIFY has the result of the
UNION ALL with the new and old transition tables as payload. This is the
output:

Asynchronous notification "update" with payload "{"id":0,"data":2}, ----" received from server process with PID 6695.
Asynchronous notification "update" with payload "{"id":0,"data":2}, ----, {"id":0,"data":1}" received from server process with PID 6695.

I would have expected the first line to be

Asynchronous notification "update" with payload "{"id":0,"data":2}, ----, {"id":0,"data":2}" received from server process with PID 6695.

Why isn't it? It's the same result with a subquery. What do I overlook
here?

Cheers,
Tom

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ron Johnson 2018-02-24 03:57:26 pg_update to a new machine?
Previous Message Ken Tanzer 2018-02-24 02:11:51 Re: Given a set of daterange, finding the continuous range that includes a particular date (aggregates)

Browse pgsql-hackers by date

  From Date Subject
Next Message Vitaliy Garnashevich 2018-02-24 08:45:14 Re: Bitmap scan is undercosted?
Previous Message Thomas Munro 2018-02-24 03:30:28 Updating parallel.sgml's treatment of parallel joins