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

On Wed, Jun 7, 2017 at 10:47 AM, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> On Mon, Jun 5, 2017 at 6:40 PM, Thomas Munro
> <thomas(dot)munro(at)enterprisedb(dot)com> wrote:
>> After sleeping on it, I don't think we need to make that decision here
>> though. I think it's better to just move the tuplestores into
>> ModifyTableState so that each embedded DML statement has its own, and
>> have ModifyTable pass them to the trigger code explicitly.
>
> I suppose you'll need two tuplestores for the ON CONFLICT DO UPDATE
> case -- one for updated tuples, and the other for inserted tuples.

Hmm. Right. INSERT ... ON CONFLICT DO UPDATE causes both AFTER
INSERT and AFTER UPDATE statement-level triggers to be fired, but then
both kinds see all the tuples -- those that were inserted and those
that were updated. That's not right.

For example:

postgres=# insert into my_table values ('ID1', 1), ('ID2', 1), ('ID3', 1)
postgres-# on conflict (a) do
postgres-# update set counter = my_table.counter + excluded.counter;
NOTICE: trigger = my_update_trigger, old table = (ID1,1), (ID2,1),
new table = (ID1,2), (ID2,2), (ID3,1)
NOTICE: trigger = my_insert_trigger, new table = (ID1,2), (ID2,2), (ID3,1)
INSERT 0 3

That's the result of the following:

create or replace function dump_insert() returns trigger language plpgsql as
$$
begin
raise notice 'trigger = %, new table = %',
TG_NAME,
(select string_agg(new_table::text, ', ' order by a) from new_table);
return null;
end;
$$;

create or replace function dump_update() returns trigger language plpgsql as
$$
begin
raise notice 'trigger = %, old table = %, new table = %',
TG_NAME,
(select string_agg(old_table::text, ', ' order by a) from old_table),
(select string_agg(new_table::text, ', ' order by a) from new_table);
return null;
end;
$$;

create table my_table (a text primary key, counter int);

insert into my_table values ('ID1', 1), ('ID2', 1);

create trigger my_insert_trigger
after insert on my_table
referencing new table as new_table
for each statement
execute procedure dump_insert();

create trigger my_update_trigger
after update on my_table
referencing old table as old_table new table as new_table
for each statement
execute procedure dump_update();

insert into my_table values ('ID1', 1), ('ID2', 1), ('ID3', 1)
on conflict (a) do
update set counter = my_table.counter + excluded.counter;

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