On Wed, Jun 7, 2017 at 10:47 AM, Peter Geoghegan <[email protected]> wrote:
> On Mon, Jun 5, 2017 at 6:40 PM, Thomas Munro
> <[email protected]> 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
