On Wed, Jun 7, 2017 at 10:47 AM, Peter Geoghegan <p...@bowt.ie> wrote: > On Mon, Jun 5, 2017 at 6:40 PM, Thomas Munro > <thomas.mu...@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 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers