Thread: Unexpected behavior with transition tables in update statementtrigger

Unexpected behavior with transition tables in update statementtrigger

From
Tom Kazimiers
Date:
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
processwith 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
processwith PID 6695.
 

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

Cheers,
Tom


Re: Unexpected behavior with transition tables in update statement trigger

From
Thomas Munro
Date:
On Sat, Feb 24, 2018 at 4:47 PM, Tom Kazimiers <tom@voodoo-arts.net> wrote:
> 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.

Thanks for the reproducer.  Yeah, that seems to be a bug.
nodeNamedTuplestorescan.c allocates a new read pointer for each
separate scan of the named tuplestore, but it doesn't call
tuplestore_select_read_pointer() so that the two scans that appear in
your UNION ALL plan are sharing the same read pointer.  At first
glance the attached seems to fix the problem, but I'll need to look
more carefully tomorrow.

-- 
Thomas Munro
http://www.enterprisedb.com

Attachment

Re: Unexpected behavior with transition tables in update statementtrigger

From
Tom Kazimiers
Date:
Hi Thomas,

On Mon, Feb 26, 2018 at 11:15:44PM +1300, Thomas Munro wrote:
>On Sat, Feb 24, 2018 at 4:47 PM, Tom Kazimiers <tom@voodoo-arts.net> wrote:
>Thanks for the reproducer.  Yeah, that seems to be a bug.
>nodeNamedTuplestorescan.c allocates a new read pointer for each
>separate scan of the named tuplestore, but it doesn't call
>tuplestore_select_read_pointer() so that the two scans that appear in
>your UNION ALL plan are sharing the same read pointer.  At first
>glance the attached seems to fix the problem, but I'll need to look
>more carefully tomorrow.

Thanks very much for investigating this. I can confirm that applying 
your patch results in the tuples I expected in both my test trigger and 
my actual trigger function.

It would be great if this or a similar fix would make it into the next 
official release.

Cheers,
Tom