Re: Unexpected behavior with transition tables in update statement trigger

[Tom Kazimiers]

On Tue, Feb 27, 2018 at 02:52:02PM +1300, Thomas Munro wrote:

On Tue, Feb 27, 2018 at 4:18 AM, Tom Kazimiers  wrote:
It would be great if this or a similar fix would make it into the 
next official release.


Here's a new version with tuplestore_select_read_pointer() added in
another place where it was lacking, and commit message.  Moving to
-hackers, where patches go.


Thanks and just to confirm the obvious: the new patch still fixes this 
bug in both my test trigger function and my real trigger function.



Here's a shorter repro.  On master it prints:

NOTICE:  count = 1
NOTICE:  count union = 1

With the patch the second number is 2, as it should be.

CREATE TABLE test (i int);
INSERT INTO test VALUES (1);

CREATE OR REPLACE FUNCTION my_trigger_fun() RETURNS trigger
LANGUAGE plpgsql AS
$$
 BEGIN
RAISE NOTICE 'count = %', (SELECT COUNT(*) FROM new_test);
RAISE NOTICE 'count union = %', (SELECT COUNT(*)
 FROM (SELECT * FROM new_test UNION ALL SELECT * FROM new_test) ss);
RETURN NULL;
END;
$$;

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

UPDATE test SET i = i;


That's a much cleaner repro (and test case I suppose), thanks.

Tom

Re: Unexpected behavior with transition tables in update statement trigger

[Tom Kazimiers]

On Tue, Feb 27, 2018 at 03:58:14PM -0500, Tom Lane wrote:

Thomas Munro  writes:

Here's a new version with tuplestore_select_read_pointer() added in
another place where it was lacking, and commit message.  Moving to
-hackers, where patches go.


Pushed, along with a regression test based on your example.
Unfortunately, this came in a bit too late for this week's releases :-(


Ah, so close. :-) Regardless, thanks both of you for fixing this and 
committing the fix to master. I am looking forward to the release 
including this.


Cheers,
Tom

Re: Unexpected behavior with transition tables in update statement trigger

[Tom Kazimiers]

On Wed, Feb 28, 2018 at 10:27:23AM +1300, Thomas Munro wrote:

Tom K, if you need a workaround before 10.4 comes out in May[1], you
could try selecting the whole transition table into a CTE up front.
Something like WITH my_copy AS (SELECT * FROM new_table) SELECT * FROM
my_copy UNION ALL SELECT * FROM my_copy should work.

[1] https://www.postgresql.org/developer/roadmap/


Thanks, that's what I am going to do.

Cheers,
Tom