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