From f61e60102f08305f3cb9e55a7958b8036a02fe39 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 4 Mar 2023 13:32:35 -0500 Subject: Avoid failure when altering state of partitioned foreign-key triggers. Beginning in v15, if you apply ALTER TABLE ENABLE/DISABLE TRIGGER to a partitioned table, it also affects the partitions' cloned versions of the affected trigger(s). The initial implementation of this located the clones by name, but that fails on foreign-key triggers which have names incorporating their own OIDs. We can fix that, and also make the behavior more bulletproof in the face of user-initiated trigger renames, by identifying the cloned triggers by tgparentid. Following the lead of earlier commits in this area, I took care not to break ABI in the v15 branch, even though I rather doubt there are any external callers of EnableDisableTrigger. While here, update the documentation, which was not touched when the semantics were changed. Per bug #17817 from Alan Hodgson. Back-patch to v15; older versions do not have this behavior. Discussion: https://postgr.es/m/17817-31dfb7c2100d9f3d@postgresql.org --- doc/src/sgml/ref/alter_table.sgml | 23 ++++++++++++++++------- 1 file changed, 16 insertions(+), 7 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 240fbafade4..6fd8faf8c82 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -572,12 +572,12 @@ WITH ( MODULUS numeric_literal, REM These forms configure the firing of trigger(s) belonging to the table. A disabled trigger is still known to the system, but is not executed - when its triggering event occurs. For a deferred trigger, the enable + when its triggering event occurs. (For a deferred trigger, the enable status is checked when the event occurs, not when the trigger function - is actually executed. One can disable or enable a single + is actually executed.) One can disable or enable a single trigger specified by name, or all triggers on the table, or only user triggers (this option excludes internally generated constraint - triggers such as those that are used to implement foreign key + triggers, such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints). Disabling or enabling internally generated constraint triggers requires superuser privileges; it should be done with caution since @@ -599,7 +599,7 @@ WITH ( MODULUS numeric_literal, REM The effect of this mechanism is that in the default configuration, triggers do not fire on replicas. This is useful because if a trigger is used on the origin to propagate data between tables, then the - replication system will also replicate the propagated data, and the + replication system will also replicate the propagated data; so the trigger should not fire a second time on the replica, because that would lead to duplication. However, if a trigger is used for another purpose such as creating external alerts, then it might be appropriate to set it @@ -607,6 +607,12 @@ WITH ( MODULUS numeric_literal, REM replicas. + + When this command is applied to a partitioned table, the states of + corresponding clone triggers in the partitions are updated too, + unless ONLY is specified. + + This command acquires a SHARE ROW EXCLUSIVE lock. @@ -1234,7 +1240,7 @@ WITH ( MODULUS numeric_literal, REM Disable or enable all triggers belonging to the table. (This requires superuser privilege if any of the triggers are - internally generated constraint triggers such as those that are used + internally generated constraint triggers, such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints.) @@ -1246,7 +1252,7 @@ WITH ( MODULUS numeric_literal, REM Disable or enable all triggers belonging to the table except for - internally generated constraint triggers such as those that are used + internally generated constraint triggers, such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints. @@ -1499,9 +1505,12 @@ WITH ( MODULUS numeric_literal, REM The actions for identity columns (ADD GENERATED, SET etc., DROP IDENTITY), as well as the actions - TRIGGER, CLUSTER, OWNER, + CLUSTER, OWNER, and TABLESPACE never recurse to descendant tables; that is, they always act as though ONLY were specified. + Actions affecting trigger states recurse to partitions of partitioned + tables (unless ONLY is specified), but never to + traditional-inheritance descendants. Adding a constraint recurses only for CHECK constraints that are not marked NO INHERIT. -- cgit v1.2.3