Re: [HACKERS] PG10 transition tables, wCTEs and multiple operationson the same table

On Wed, Jun 7, 2017 at 10:47 AM, Peter Geoghegan <pg@bowt.ie> wrote:
> On Mon, Jun 5, 2017 at 6:40 PM, Thomas Munro
> <thomas.munro@enterprisedb.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
bya) 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)
fromnew_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
proceduredump_insert();
 

create trigger my_update_trigger after update on my_table referencing old table as old_table new table as new_table for
eachstatement 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