Unused triggers and trigger firing always on partitioned table

From: Soni M <diptatapa(at)gmail(dot)com>
To: Postgres Bug <pgsql-bugs(at)postgresql(dot)org>
Subject: Unused triggers and trigger firing always on partitioned table
Date: 2021-03-26 03:01:06
Message-ID: CAAMgDXmTF=OYBb=MFreuCFHmEFjN-ezBNo5te4yqF=4Rvh8LoQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2021-03-26 10:50:41 BUG #16944: when creating postGIS 2.5.4 extension for PostgreSQL10 is missing
Previous Message PG Bug reporting form 2021-03-25 08:14:25 BUG #16943: Permanent restart of the cluster after "EXECUTE 'alter subscription ... refresh publication' "