Unused triggers and trigger firing always on partitioned table
Hi All,
PG 12.5 on Centos 7.9
We create partitioned table with disabled triggers and triggers firing
always.
Child created before the trigger defined, has the triggers configured
correctly.
But child created after the trigger defined, has all triggers set up as
normal triggers, there are no disabled triggers and no triggers firing
always.
consprod=# \d locate
Partitioned table "public.locate"
Column | Type | Collation |
Nullable | Default
---------------------------+-----------------------------+-----------+----------+---------
locate_id | integer | | not
null |
ticket_id | integer | | not
null |
Partition key: RANGE (ticket_id)
Triggers:
locate_denorm_dirty_trigger AFTER INSERT OR UPDATE ON locate FOR EACH
ROW EXECUTE FUNCTION locate_denorm_dirty_triggerfn()
locate_wh_dirty_trigger AFTER INSERT OR UPDATE ON locate FOR EACH ROW
EXECUTE FUNCTION locate_wh_dirty_table_triggerfn()
Disabled user triggers:
locate_ticket_search_p_trigger AFTER INSERT ON locate DEFERRABLE
INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION
locate_ticket_search_p_triggerfn()
locate_ticket_search_trigger AFTER INSERT ON locate DEFERRABLE
INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION
locate_ticket_search_triggerfn()
Triggers firing always:
locate_ticket_search_dirty_trigger AFTER INSERT ON locate DEFERRABLE
INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION
locate_ticket_search_dirty_triggerfn()
Number of partitions: 24 (Use \d+ to list them.)
consprod=# create table aaab partition of locate for values from
(290000000) to (295000000);
CREATE TABLE
consprod=# \d aaab
Table "public.aaab"
Column | Type | Collation |
Nullable | Default
---------------------------+-----------------------------+-----------+----------+---------
locate_id | integer | | not
null |
ticket_id | integer | | not
null |
Partition of: locate FOR VALUES FROM (290000000) TO (295000000)
Triggers:
locate_denorm_dirty_trigger AFTER INSERT OR UPDATE ON aaab FOR EACH ROW
EXECUTE FUNCTION locate_denorm_dirty_triggerfn()
locate_ticket_search_dirty_trigger AFTER INSERT ON aaab DEFERRABLE
INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION
locate_ticket_search_dirty_triggerfn()
locate_ticket_search_p_trigger AFTER INSERT ON aaab DEFERRABLE
INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION
locate_ticket_search_p_triggerfn()
locate_ticket_search_trigger AFTER INSERT ON aaab DEFERRABLE INITIALLY
DEFERRED FOR EACH ROW EXECUTE FUNCTION locate_ticket_search_triggerfn()
locate_wh_dirty_trigger AFTER INSERT OR UPDATE ON aaab FOR EACH ROW
EXECUTE FUNCTION locate_wh_dirty_table_triggerfn()
--
Regards,
Soni Maula Harriz