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

Commit0b83c47

Browse files
committed
Fix detaching partitions with cloned row triggers
When a partition is detached, any triggers that had been cloned from itsparent were not properly disentangled from its parent triggers.This resulted in triggers that could not be dropped because theydepended on the trigger in the trigger in the no-longer-parent table: ALTER TABLE t DETACH PARTITION t1; DROP TRIGGER trig ON t1; ERROR: cannot drop trigger trig on table t1 because trigger trig on table t requires it HINT: You can drop trigger trig on table t instead.Moreover the table can no longer be re-attached to its parent, becausethe trigger name is already taken: ALTER TABLE t ATTACH PARTITION t1 FOR VALUES FROM (1)TO(2); ERROR: trigger "trig" for relation "t1" already existsThe former is a bug introduced in commit86f5759. (The latter isnot necessarily a bug, but it makes the bug more uncomfortable.)To avoid the complexity that would be needed to tell whether the triggerhas a local definition that has to be merged with the one coming fromthe parent table, establish the behavior that the trigger is removedwhen the table is detached.Backpatch to pg11.Author: Justin Pryzby <pryzby@telsasoft.com>Reviewed-by: Amit Langote <amitlangote09@gmail.com>Reviewed-by: Álvaro Herrera <alvherre@alvh.no-ip.org>Discussion:https://www.postgresql.org/message-id/flat/20200408152412.GZ2228@telsasoft.com
1 parentef27e30 commit0b83c47

File tree

4 files changed

+131
-0
lines changed

4 files changed

+131
-0
lines changed

‎doc/src/sgml/ref/create_trigger.sgml‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -524,6 +524,7 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</
524524
Creating a row-level trigger on a partitioned table will cause identical
525525
triggers to be created in all its existing partitions; and any partitions
526526
created or attached later will contain an identical trigger, too.
527+
If the partition is detached from its parent, the trigger is removed.
527528
Triggers on partitioned tables may only be <literal>AFTER</literal>.
528529
</para>
529530

‎src/backend/commands/tablecmds.c‎

Lines changed: 64 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -494,6 +494,7 @@ static void QueuePartitionConstraintValidation(List **wqueue, Relation scanrel,
494494
List*partConstraint,
495495
boolvalidate_default);
496496
staticvoidCloneRowTriggersToPartition(Relationparent,Relationpartition);
497+
staticvoidDropClonedTriggersFromPartition(OidpartitionId);
497498
staticObjectAddressATExecDetachPartition(Relationrel,RangeVar*name);
498499
staticObjectAddressATExecAttachPartitionIdx(List**wqueue,Relationrel,
499500
RangeVar*name);
@@ -15656,6 +15657,9 @@ ATExecDetachPartition(Relation rel, RangeVar *name)
1565615657
}
1565715658
heap_close(classRel,RowExclusiveLock);
1565815659

15660+
/* Drop any triggers that were cloned on creation/attach. */
15661+
DropClonedTriggersFromPartition(RelationGetRelid(partRel));
15662+
1565915663
/*
1566015664
* Detach any foreign keys that are inherited. This includes creating
1566115665
* additional action triggers.
@@ -15718,6 +15722,66 @@ ATExecDetachPartition(Relation rel, RangeVar *name)
1571815722
returnaddress;
1571915723
}
1572015724

15725+
/*
15726+
* DropClonedTriggersFromPartition
15727+
*subroutine for ATExecDetachPartition to remove any triggers that were
15728+
*cloned to the partition when it was created-as-partition or attached.
15729+
*This undoes what CloneRowTriggersToPartition did.
15730+
*/
15731+
staticvoid
15732+
DropClonedTriggersFromPartition(OidpartitionId)
15733+
{
15734+
ScanKeyDataskey;
15735+
SysScanDescscan;
15736+
HeapTupletrigtup;
15737+
Relationtgrel;
15738+
ObjectAddresses*objects;
15739+
15740+
objects=new_object_addresses();
15741+
15742+
/*
15743+
* Scan pg_trigger to search for all triggers on this rel.
15744+
*/
15745+
ScanKeyInit(&skey,Anum_pg_trigger_tgrelid,BTEqualStrategyNumber,
15746+
F_OIDEQ,ObjectIdGetDatum(partitionId));
15747+
tgrel=heap_open(TriggerRelationId,RowExclusiveLock);
15748+
scan=systable_beginscan(tgrel,TriggerRelidNameIndexId,
15749+
true,NULL,1,&skey);
15750+
while (HeapTupleIsValid(trigtup=systable_getnext(scan)))
15751+
{
15752+
Oidtrigoid=HeapTupleGetOid(trigtup);
15753+
ObjectAddresstrig;
15754+
15755+
/* Ignore triggers that weren't cloned */
15756+
if (!isPartitionTrigger(trigoid))
15757+
continue;
15758+
15759+
/*
15760+
* This is ugly, but necessary: remove the dependency markings on the
15761+
* trigger so that it can be removed.
15762+
*/
15763+
deleteDependencyRecordsForClass(TriggerRelationId,trigoid,
15764+
TriggerRelationId,
15765+
DEPENDENCY_INTERNAL_AUTO);
15766+
deleteDependencyRecordsForClass(TriggerRelationId,trigoid,
15767+
RelationRelationId,
15768+
DEPENDENCY_INTERNAL_AUTO);
15769+
15770+
/* remember this trigger to remove it below */
15771+
ObjectAddressSet(trig,TriggerRelationId,trigoid);
15772+
add_exact_object_address(&trig,objects);
15773+
}
15774+
15775+
/* make the dependency removal visible to the deletion below */
15776+
CommandCounterIncrement();
15777+
performMultipleDeletions(objects,DROP_RESTRICT,PERFORM_DELETION_INTERNAL);
15778+
15779+
/* done */
15780+
free_object_addresses(objects);
15781+
systable_endscan(scan);
15782+
heap_close(tgrel,RowExclusiveLock);
15783+
}
15784+
1572115785
/*
1572215786
* Before acquiring lock on an index, acquire the same lock on the owning
1572315787
* table.

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

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2025,6 +2025,51 @@ select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger
20252025
---------+--------+--------
20262026
(0 rows)
20272027

2028+
-- check detach behavior
2029+
create trigger trg1 after insert on trigpart for each row execute procedure trigger_nothing();
2030+
\d trigpart3
2031+
Table "public.trigpart3"
2032+
Column | Type | Collation | Nullable | Default
2033+
--------+---------+-----------+----------+---------
2034+
a | integer | | |
2035+
b | integer | | |
2036+
Partition of: trigpart FOR VALUES FROM (2000) TO (3000)
2037+
Triggers:
2038+
trg1 AFTER INSERT ON trigpart3 FOR EACH ROW EXECUTE PROCEDURE trigger_nothing()
2039+
2040+
alter table trigpart detach partition trigpart3;
2041+
drop trigger trg1 on trigpart3; -- fail due to "does not exist"
2042+
ERROR: trigger "trg1" for table "trigpart3" does not exist
2043+
alter table trigpart detach partition trigpart4;
2044+
drop trigger trg1 on trigpart41; -- fail due to "does not exist"
2045+
ERROR: trigger "trg1" for table "trigpart41" does not exist
2046+
drop table trigpart4;
2047+
alter table trigpart attach partition trigpart3 for values from (2000) to (3000);
2048+
alter table trigpart detach partition trigpart3;
2049+
alter table trigpart attach partition trigpart3 for values from (2000) to (3000);
2050+
drop table trigpart3;
2051+
select tgrelid::regclass::text, tgname, tgfoid::regproc, tgenabled, tgisinternal from pg_trigger
2052+
where tgname ~ '^trg1' order by 1;
2053+
tgrelid | tgname | tgfoid | tgenabled | tgisinternal
2054+
-----------+--------+-----------------+-----------+--------------
2055+
trigpart | trg1 | trigger_nothing | O | f
2056+
trigpart1 | trg1 | trigger_nothing | O | t
2057+
(2 rows)
2058+
2059+
create table trigpart3 (like trigpart);
2060+
create trigger trg1 after insert on trigpart3 for each row execute procedure trigger_nothing();
2061+
\d trigpart3
2062+
Table "public.trigpart3"
2063+
Column | Type | Collation | Nullable | Default
2064+
--------+---------+-----------+----------+---------
2065+
a | integer | | |
2066+
b | integer | | |
2067+
Triggers:
2068+
trg1 AFTER INSERT ON trigpart3 FOR EACH ROW EXECUTE PROCEDURE trigger_nothing()
2069+
2070+
alter table trigpart attach partition trigpart3 FOR VALUES FROM (2000) to (3000); -- fail
2071+
ERROR: trigger "trg1" for relation "trigpart3" already exists
2072+
drop table trigpart3;
20282073
drop table trigpart;
20292074
drop function trigger_nothing();
20302075
--

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

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1387,6 +1387,27 @@ drop trigger trg1 on trigpart;-- ok, all gone
13871387
select tgrelid::regclass, tgname, tgfoid::regprocfrom pg_trigger
13881388
where tgrelid::regclass::textlike'trigpart%'order by tgrelid::regclass::text;
13891389

1390+
-- check detach behavior
1391+
createtriggertrg1 after inserton trigpart for each row execute procedure trigger_nothing();
1392+
\d trigpart3
1393+
altertable trigpart detach partition trigpart3;
1394+
droptrigger trg1on trigpart3;-- fail due to "does not exist"
1395+
altertable trigpart detach partition trigpart4;
1396+
droptrigger trg1on trigpart41;-- fail due to "does not exist"
1397+
droptable trigpart4;
1398+
altertable trigpart attach partition trigpart3 forvaluesfrom (2000) to (3000);
1399+
altertable trigpart detach partition trigpart3;
1400+
altertable trigpart attach partition trigpart3 forvaluesfrom (2000) to (3000);
1401+
droptable trigpart3;
1402+
1403+
select tgrelid::regclass::text, tgname, tgfoid::regproc, tgenabled, tgisinternalfrom pg_trigger
1404+
where tgname ~'^trg1'order by1;
1405+
createtabletrigpart3 (like trigpart);
1406+
createtriggertrg1 after inserton trigpart3 for each row execute procedure trigger_nothing();
1407+
\d trigpart3
1408+
altertable trigpart attach partition trigpart3 FORVALUESFROM (2000) to (3000);-- fail
1409+
droptable trigpart3;
1410+
13901411
droptable trigpart;
13911412
dropfunction trigger_nothing();
13921413

0 commit comments

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