Thread: 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()

--