Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit6949b92

Browse files
committed
Avoid failure when altering state of partitioned foreign-key triggers.
Beginning in v15, if you apply ALTER TABLE ENABLE/DISABLE TRIGGER toa partitioned table, it also affects the partitions' cloned versionsof the affected trigger(s). The initial implementation of thislocated the clones by name, but that fails on foreign-key triggerswhich have names incorporating their own OIDs. We can fix that, andalso make the behavior more bulletproof in the face of user-initiatedtrigger renames, by identifying the cloned triggers by tgparentid.Following the lead of earlier commits in this area, I took care notto break ABI in the v15 branch, even though I rather doubt thereare any external callers of EnableDisableTrigger.While here, update the documentation, which was not touched whenthe semantics were changed.Per bug #17817 from Alan Hodgson. Back-patch to v15; older versionsdo not have this behavior.Discussion:https://postgr.es/m/17817-31dfb7c2100d9f3d@postgresql.org
1 parentf62975b commit6949b92

File tree

6 files changed

+76
-12
lines changed

6 files changed

+76
-12
lines changed

‎doc/src/sgml/ref/alter_table.sgml

Lines changed: 16 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -576,12 +576,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
576576
<para>
577577
These forms configure the firing of trigger(s) belonging to the table.
578578
A disabled trigger is still known to the system, but is not executed
579-
when its triggering event occurs. For a deferred trigger, the enable
579+
when its triggering event occurs.(For a deferred trigger, the enable
580580
status is checked when the event occurs, not when the trigger function
581-
is actually executed. One can disable or enable a single
581+
is actually executed.) One can disable or enable a single
582582
trigger specified by name, or all triggers on the table, or only
583583
user triggers (this option excludes internally generated constraint
584-
triggers such as those that are used to implement foreign key
584+
triggers, such as those that are used to implement foreign key
585585
constraints or deferrable uniqueness and exclusion constraints).
586586
Disabling or enabling internally generated constraint triggers
587587
requires superuser privileges; it should be done with caution since
@@ -603,14 +603,20 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
603603
The effect of this mechanism is that in the default configuration,
604604
triggers do not fire on replicas. This is useful because if a trigger
605605
is used on the origin to propagate data between tables, then the
606-
replication system will also replicate the propagated data, and the
606+
replication system will also replicate the propagated data; so the
607607
trigger should not fire a second time on the replica, because that would
608608
lead to duplication. However, if a trigger is used for another purpose
609609
such as creating external alerts, then it might be appropriate to set it
610610
to <literal>ENABLE ALWAYS</literal> so that it is also fired on
611611
replicas.
612612
</para>
613613

614+
<para>
615+
When this command is applied to a partitioned table, the states of
616+
corresponding clone triggers in the partitions are updated too,
617+
unless <literal>ONLY</literal> is specified.
618+
</para>
619+
614620
<para>
615621
This command acquires a <literal>SHARE ROW EXCLUSIVE</literal> lock.
616622
</para>
@@ -1239,7 +1245,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
12391245
<para>
12401246
Disable or enable all triggers belonging to the table.
12411247
(This requires superuser privilege if any of the triggers are
1242-
internally generated constraint triggers such as those that are used
1248+
internally generated constraint triggers, such as those that are used
12431249
to implement foreign key constraints or deferrable uniqueness and
12441250
exclusion constraints.)
12451251
</para>
@@ -1251,7 +1257,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
12511257
<listitem>
12521258
<para>
12531259
Disable or enable all triggers belonging to the table except for
1254-
internally generated constraint triggers such as those that are used
1260+
internally generated constraint triggers, such as those that are used
12551261
to implement foreign key constraints or deferrable uniqueness and
12561262
exclusion constraints.
12571263
</para>
@@ -1504,9 +1510,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
15041510
The actions for identity columns (<literal>ADD
15051511
GENERATED</literal>, <literal>SET</literal> etc., <literal>DROP
15061512
IDENTITY</literal>), as well as the actions
1507-
<literal>TRIGGER</literal>, <literal>CLUSTER</literal>, <literal>OWNER</literal>,
1513+
<literal>CLUSTER</literal>, <literal>OWNER</literal>,
15081514
and <literal>TABLESPACE</literal> never recurse to descendant tables;
15091515
that is, they always act as though <literal>ONLY</literal> were specified.
1516+
Actions affecting trigger states recurse to partitions of partitioned
1517+
tables (unless <literal>ONLY</literal> is specified), but never to
1518+
traditional-inheritance descendants.
15101519
Adding a constraint recurses only for <literal>CHECK</literal> constraints
15111520
that are not marked <literal>NO INHERIT</literal>.
15121521
</para>

‎src/backend/commands/tablecmds.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -14726,7 +14726,8 @@ ATExecEnableDisableTrigger(Relation rel, const char *trigname,
1472614726
char fires_when, bool skip_system, bool recurse,
1472714727
LOCKMODE lockmode)
1472814728
{
14729-
EnableDisableTrigger(rel, trigname, fires_when, skip_system, recurse,
14729+
EnableDisableTrigger(rel, trigname, InvalidOid,
14730+
fires_when, skip_system, recurse,
1473014731
lockmode);
1473114732
}
1473214733

‎src/backend/commands/trigger.c

Lines changed: 8 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1715,7 +1715,8 @@ renametrig_partition(Relation tgrel, Oid partitionId, Oid parentTriggerOid,
17151715
*to change 'tgenabled' field for the specified trigger(s)
17161716
*
17171717
* rel: relation to process (caller must hold suitable lock on it)
1718-
* tgname: trigger to process, or NULL to scan all triggers
1718+
* tgname: name of trigger to process, or NULL to scan all triggers
1719+
* tgparent: if not zero, process only triggers with this tgparentid
17191720
* fires_when: new value for tgenabled field. In addition to generic
17201721
* enablement/disablement, this also defines when the trigger
17211722
* should be fired in session replication roles.
@@ -1727,7 +1728,7 @@ renametrig_partition(Relation tgrel, Oid partitionId, Oid parentTriggerOid,
17271728
* system triggers
17281729
*/
17291730
void
1730-
EnableDisableTrigger(Relationrel,constchar*tgname,
1731+
EnableDisableTrigger(Relationrel,constchar*tgname,Oidtgparent,
17311732
charfires_when,boolskip_system,boolrecurse,
17321733
LOCKMODElockmode)
17331734
{
@@ -1766,6 +1767,9 @@ EnableDisableTrigger(Relation rel, const char *tgname,
17661767
{
17671768
Form_pg_triggeroldtrig= (Form_pg_trigger)GETSTRUCT(tuple);
17681769

1770+
if (OidIsValid(tgparent)&&tgparent!=oldtrig->tgparentid)
1771+
continue;
1772+
17691773
if (oldtrig->tgisinternal)
17701774
{
17711775
/* system trigger ... ok to process? */
@@ -1816,7 +1820,8 @@ EnableDisableTrigger(Relation rel, const char *tgname,
18161820
Relationpart;
18171821

18181822
part=relation_open(partdesc->oids[i],lockmode);
1819-
EnableDisableTrigger(part,NameStr(oldtrig->tgname),
1823+
/* Match on child triggers' tgparentid, not their name */
1824+
EnableDisableTrigger(part,NULL,oldtrig->oid,
18201825
fires_when,skip_system,recurse,
18211826
lockmode);
18221827
table_close(part,NoLock);/* keep lock till commit */

‎src/include/commands/trigger.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -170,7 +170,7 @@ extern Oidget_trigger_oid(Oid relid, const char *trigname, bool missing_ok);
170170

171171
externObjectAddressrenametrig(RenameStmt*stmt);
172172

173-
externvoidEnableDisableTrigger(Relationrel,constchar*tgname,
173+
externvoidEnableDisableTrigger(Relationrel,constchar*tgname,Oidtgparent,
174174
charfires_when,boolskip_system,boolrecurse,
175175
LOCKMODElockmode);
176176

‎src/test/regress/expected/triggers.out

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2718,6 +2718,40 @@ select tgrelid::regclass, tgname, tgenabled from pg_trigger
27182718
parent | tg_stmt | A
27192719
(3 rows)
27202720

2721+
drop table parent, child1;
2722+
-- Check processing of foreign key triggers
2723+
create table parent (a int primary key, f int references parent)
2724+
partition by list (a);
2725+
create table child1 partition of parent for values in (1);
2726+
select tgrelid::regclass, rtrim(tgname, '0123456789') as tgname,
2727+
tgfoid::regproc, tgenabled
2728+
from pg_trigger where tgrelid in ('parent'::regclass, 'child1'::regclass)
2729+
order by tgrelid::regclass::text, tgfoid;
2730+
tgrelid | tgname | tgfoid | tgenabled
2731+
---------+-------------------------+------------------------+-----------
2732+
child1 | RI_ConstraintTrigger_c_ | "RI_FKey_check_ins" | O
2733+
child1 | RI_ConstraintTrigger_c_ | "RI_FKey_check_upd" | O
2734+
parent | RI_ConstraintTrigger_c_ | "RI_FKey_check_ins" | O
2735+
parent | RI_ConstraintTrigger_c_ | "RI_FKey_check_upd" | O
2736+
parent | RI_ConstraintTrigger_a_ | "RI_FKey_noaction_del" | O
2737+
parent | RI_ConstraintTrigger_a_ | "RI_FKey_noaction_upd" | O
2738+
(6 rows)
2739+
2740+
alter table parent disable trigger all;
2741+
select tgrelid::regclass, rtrim(tgname, '0123456789') as tgname,
2742+
tgfoid::regproc, tgenabled
2743+
from pg_trigger where tgrelid in ('parent'::regclass, 'child1'::regclass)
2744+
order by tgrelid::regclass::text, tgfoid;
2745+
tgrelid | tgname | tgfoid | tgenabled
2746+
---------+-------------------------+------------------------+-----------
2747+
child1 | RI_ConstraintTrigger_c_ | "RI_FKey_check_ins" | D
2748+
child1 | RI_ConstraintTrigger_c_ | "RI_FKey_check_upd" | D
2749+
parent | RI_ConstraintTrigger_c_ | "RI_FKey_check_ins" | D
2750+
parent | RI_ConstraintTrigger_c_ | "RI_FKey_check_upd" | D
2751+
parent | RI_ConstraintTrigger_a_ | "RI_FKey_noaction_del" | D
2752+
parent | RI_ConstraintTrigger_a_ | "RI_FKey_noaction_upd" | D
2753+
(6 rows)
2754+
27212755
drop table parent, child1;
27222756
-- Verify that firing state propagates correctly on creation, too
27232757
CREATE TABLE trgfire (i int) PARTITION BY RANGE (i);

‎src/test/regress/sql/triggers.sql

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1883,6 +1883,21 @@ select tgrelid::regclass, tgname, tgenabled from pg_trigger
18831883
order by tgrelid::regclass::text, tgname;
18841884
droptable parent, child1;
18851885

1886+
-- Check processing of foreign key triggers
1887+
createtableparent (aintprimary key, fintreferences parent)
1888+
partition by list (a);
1889+
createtablechild1 partition of parent forvaluesin (1);
1890+
select tgrelid::regclass, rtrim(tgname,'0123456789')as tgname,
1891+
tgfoid::regproc, tgenabled
1892+
from pg_triggerwhere tgrelidin ('parent'::regclass,'child1'::regclass)
1893+
order by tgrelid::regclass::text, tgfoid;
1894+
altertable parent disable trigger all;
1895+
select tgrelid::regclass, rtrim(tgname,'0123456789')as tgname,
1896+
tgfoid::regproc, tgenabled
1897+
from pg_triggerwhere tgrelidin ('parent'::regclass,'child1'::regclass)
1898+
order by tgrelid::regclass::text, tgfoid;
1899+
droptable parent, child1;
1900+
18861901
-- Verify that firing state propagates correctly on creation, too
18871902
CREATETABLEtrgfire (iint) PARTITION BY RANGE (i);
18881903
CREATETABLEtrgfire1 PARTITION OF trgfire FORVALUESFROM (1) TO (10);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp