Thread: BUG #16185: Trigger fires twice and with wrong TG_OP when updating partion key on partitioned table
BUG #16185: Trigger fires twice and with wrong TG_OP when updating partion key on partitioned table
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 16185 Logged by: Henry Hinze Email address: henry.hinze@gmail.com PostgreSQL version: 12.1 Operating system: Ubuntu 16.04.6 LTS Description: If a trigger is defined on a partitioned table and the partition key is updated that the row is moved to another partition, then the trigger is fired twice first with TG_OP = 'DELETE' followed by TG_OP = 'INSERT'. As a user I would expect that the trigger fires only once with TG_OP = 'UPDATE'. To reproduce: **************** create table t (id int, p_key int) partition by list (p_key); create table t1 partition of t for values in (1); create table t2 partition of t for values in (2); insert into t (id,p_key) values (1,1); create or replace function f() RETURNS TRIGGER LANGUAGE plpgsql AS $function$ BEGIN RAISE NOTICE 'TG_OP: %', TG_OP; RETURN NULL; END; $function$; CREATE TRIGGER f_trig AFTER INSERT or UPDATE or DELETE ON t FOR EACH ROW EXECUTE PROCEDURE f(); postgres=# update t set p_key = 2 where id = 1; NOTICE: TG_OP: DELETE NOTICE: TG_OP: INSERT UPDATE 1