Re: PG10 transition tables, wCTEs and multiple operations on the same table

On Fri, Jun 2, 2017 at 10:48 PM, Marko Tiikkaja <marko(at)joh(dot)to> wrote:
> Since the subject of transition tables came up, I thought I'd test how this
> case works:
>
> =# create table qwr(a int);
> CREATE TABLE
>
> =# create function qq() returns trigger as $$ begin raise notice '%',
> (select count(*) from oo); return null; end $$ language plpgsql;
> CREATE FUNCTION
>
> =# create trigger xx after insert on qwr referencing new table as oo for
> each statement execute procedure qq();
> CREATE TRIGGER
>
> =# with t as (insert into qwr values (1)) insert into qwr values (2), (3);
> NOTICE: 3
> NOTICE: 3
> INSERT 0 2
>
> to me, this means that it doesn't work. Surely one of the trigger
> invocations should say 1, and the other 2. Or was this intentional?

I would have expected that to be handled by separate transition tables
at different query levels, but evidently it isn't. The following
crashes:

create table table1(a int);
create table table2(a text);

create function trigfunc() returns trigger as $$ begin raise notice
'got: %', (select oo from oo); return null; end $$ language plpgsql;

create trigger trig1 after insert on table1 referencing new table as
oo for each statement execute procedure trigfunc();

create trigger trig2 after insert on table2 referencing new table as
oo for each statement execute procedure trigfunc();

with t as (insert into table1 values (1)) insert into table2 values ('hello');

I've run out of day today but will investigate.

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