public inbox for [email protected]
help / color / mirror / Atom feedFrom: Matheus Alcantara <[email protected]>
To: Kirill Reshke <[email protected]>
To: pgsql-hackers <[email protected]>
Subject: Re: MERGE PARTITIONS and DEPENDS ON EXTENSION.
Date: Tue, 07 Apr 2026 11:14:05 -0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <CALdSSPjXtzGM7Uk4fWRwRMXcCczge5uNirPQcYCHKPAWPkp9iQ@mail.gmail.com>
<[email protected]>
On Wed Mar 11, 2026 at 12:12 PM -03, Matheus Alcantara wrote:
> On 10/03/26 14:53, Kirill Reshke wrote:
>> Today, while reviewing another patch, I spotted PostgreSQL behaviour
>> which I cannot tell if is correct.
>>
>> -- create relation
>> reshke=# create table pt (i int) partition by range ( i);
>> CREATE TABLE
>>
>> -- create partitions.
>> reshke=# create table pt1 partition of pt for values from ( 1 ) to (2) ;
>> CREATE TABLE
>> reshke=# create table pt2 partition of pt for values from ( 2 ) to (3) ;
>> CREATE TABLE
>>
>> -- manually add dependency on extension.
>> reshke=# alter index pt1_i_idx depends on extension btree_gist ;
>> ALTER INDEX
>> reshke=# alter index pt2_i_idx depends on extension btree_gist ;
>> ALTER INDEX
>>
>> At this point, `drop extension btree_gist` fails due to existing
>> dependencies. However, after `alter table pt merge partitions ( pt1 ,
>> pt2 ) into pt3;` there are no dependencies, and drop extension
>> executes successfully.
>>
>> My first impression was that there is no issue as the user created a
>> new database object, so should manually add dependency on extension.
>> However I am not 100% in this reasoning.
>>
>> Any thoughts?
>>
>
> I'm also not sure if it's correct to assume that the dependency should
> be manually added after a partition is merged or splited but I was
> checking ATExecMergePartitions() and ATExecSplitPartition() and I
> think that it's not complicated to implement this.
>
> IIUC we just need to collect the extension dependencies before an
> index is detached on MERGE and SPLIT operations and then apply the
> dependency after the index is created on the new merged/splited
> partition. The attached patch implement this.
>
> Note that I'm using two different extensions for partition_merge and
> partition_split tests because I was having deadlock issues when
> running these tests in parallel using the same extension as a dependency.
>
Attaching a new rebased version, no changes compared with v1.
--
Matheus Alcantara
EDB: https://www.enterprisedb.com
From 44c37e30e60b53d4478dbf32d4aae2a7d6b9b17f Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email protected]>
Date: Wed, 11 Mar 2026 11:39:56 -0300
Subject: [PATCH v2] Preserve extension dependencies on indexes during
partition merge/split
When using ALTER TABLE ... MERGE PARTITIONS or ALTER TABLE ... SPLIT
PARTITION, extension dependencies on partition indexes (created via
ALTER INDEX ... DEPENDS ON EXTENSION) were being lost. This happened
because the new partition indexes are created fresh from the parent
partitioned table's indexes, while the old partition indexes (with
their extension dependencies) are dropped.
Fix this by collecting extension dependencies from source partition
indexes before detaching them, then applying those dependencies to
the corresponding new partition indexes after they're created. The
mapping between old and new indexes is done via their common parent
partitioned index.
Discussion: https://www.postgresql.org/message-id/CALdSSPjXtzGM7Uk4fWRwRMXcCczge5uNirPQcYCHKPAWPkp9iQ%40mail.gma...
Reported-by: Kirill Reshke
---
src/backend/commands/tablecmds.c | 218 ++++++++++++++++++
src/test/regress/expected/partition_merge.out | 40 ++++
src/test/regress/expected/partition_split.out | 41 ++++
src/test/regress/sql/partition_merge.sql | 38 +++
src/test/regress/sql/partition_split.sql | 39 ++++
src/tools/pgindent/typedefs.list | 1 +
6 files changed, 377 insertions(+)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index eec09ba1ded..afa89f8b3ce 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -40,6 +40,7 @@
#include "catalog/pg_collation.h"
#include "catalog/pg_constraint.h"
#include "catalog/pg_depend.h"
+#include "catalog/pg_extension_d.h"
#include "catalog/pg_foreign_table.h"
#include "catalog/pg_inherits.h"
#include "catalog/pg_largeobject.h"
@@ -365,6 +366,16 @@ typedef enum addFkConstraintSides
addFkBothSides,
} addFkConstraintSides;
+/*
+ * Hold extension dependencies for a partitioned index. Used by
+ * collectPartitionIndexExtDeps and applyPartitionIndexExtDeps.
+ */
+typedef struct PartitionIndexExtDepEntry
+{
+ Oid parentIndexOid; /* OID of the parent partitioned index */
+ List *extensionOids; /* List of extension OIDs this index depends */
+} PartitionIndexExtDepEntry;
+
/*
* Partition tables are expected to be dropped when the parent partitioned
* table gets dropped. Hence for partitioning we use AUTO dependency.
@@ -760,6 +771,9 @@ static void ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation
static void ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab,
Relation rel, PartitionCmd *cmd,
AlterTableUtilityContext *context);
+static List *collectPartitionIndexExtDeps(List *partitionOids);
+static void applyPartitionIndexExtDeps(Oid newPartOid, List *extDepState);
+static void freePartitionIndexExtDeps(List *extDepState);
/* ----------------------------------------------------------------
* DefineRelation
@@ -23000,6 +23014,171 @@ detachPartitionTable(Relation parent_rel, Relation child_rel, Oid defaultPartOid
PopActiveSnapshot();
}
+/*
+ * collectPartitionIndexExtDeps: collect extension dependencies from indexes
+ * on the given partitions.
+ *
+ * For each index on the source partitions that has extension dependencies, we
+ * collect a mapping from the parent partitioned index OID to the list of
+ * extension OIDs.
+ *
+ * Returns a list of PartitionIndexExtDepEntry structs.
+ */
+static List *
+collectPartitionIndexExtDeps(List *partitionOids)
+{
+ List *result = NIL;
+
+ foreach_oid(partOid, partitionOids)
+ {
+ Relation partRel;
+ List *indexList;
+
+ /*
+ * Use NoLock since the caller already holds AccessExclusiveLock on
+ * these partitions.
+ */
+ partRel = table_open(partOid, NoLock);
+ indexList = RelationGetIndexList(partRel);
+
+ foreach_oid(indexOid, indexList)
+ {
+ Oid parentIdxOid;
+ List *extDeps;
+ PartitionIndexExtDepEntry *entry = NULL;
+ ListCell *lc;
+
+ /* Get the parent index if this is a partition index */
+ parentIdxOid = get_partition_parent(indexOid, true);
+ if (!OidIsValid(parentIdxOid))
+ continue;
+
+ /* Get extension dependencies for this index */
+ extDeps = getAutoExtensionsOfObject(RelationRelationId, indexOid);
+ if (extDeps == NIL)
+ continue;
+
+ /*
+ * Look for existing entry for this parent index.
+ */
+ foreach(lc, result)
+ {
+ PartitionIndexExtDepEntry *e = lfirst(lc);
+
+ if (e->parentIndexOid == parentIdxOid)
+ {
+ entry = e;
+ break;
+ }
+ }
+
+ if (entry != NULL)
+ {
+ /* Add extension dependencies avoiding duplicates */
+ foreach_oid(extOid, extDeps)
+ {
+ if (!list_member_oid(entry->extensionOids, extOid))
+ entry->extensionOids = lappend_oid(entry->extensionOids,
+ extOid);
+ }
+ list_free(extDeps);
+ }
+ else
+ {
+ /* Create new entry */
+ entry = palloc(sizeof(PartitionIndexExtDepEntry));
+ entry->parentIndexOid = parentIdxOid;
+ entry->extensionOids = extDeps;
+ result = lappend(result, entry);
+ }
+ }
+
+ list_free(indexList);
+ table_close(partRel, NoLock);
+ }
+
+ return result;
+}
+
+/*
+ * applyPartitionIndexExtDeps: apply collected extension dependencies to
+ * indexes on a new partition.
+ *
+ * For each index on the new partition, look up its parent index in the
+ * extDepState list. If found, record extension dependencies on the new index.
+ */
+static void
+applyPartitionIndexExtDeps(Oid newPartOid, List *extDepState)
+{
+ Relation partRel;
+ List *indexList;
+
+ if (extDepState == NIL)
+ return;
+
+ /*
+ * Use NoLock since the caller already holds AccessExclusiveLock on the
+ * new partition.
+ */
+ partRel = table_open(newPartOid, NoLock);
+ indexList = RelationGetIndexList(partRel);
+
+ foreach_oid(indexOid, indexList)
+ {
+ Oid parentIdxOid;
+ ListCell *lc;
+
+ /* Get the parent index if this is a partition index */
+ parentIdxOid = get_partition_parent(indexOid, true);
+ if (!OidIsValid(parentIdxOid))
+ continue;
+
+ /* Look for extension dependencies to apply */
+ foreach(lc, extDepState)
+ {
+ PartitionIndexExtDepEntry *entry = lfirst(lc);
+
+ if (entry->parentIndexOid == parentIdxOid)
+ {
+ ObjectAddress indexAddr;
+
+ ObjectAddressSet(indexAddr, RelationRelationId, indexOid);
+
+ foreach_oid(extOid, entry->extensionOids)
+ {
+ ObjectAddress extAddr;
+
+ ObjectAddressSet(extAddr, ExtensionRelationId, extOid);
+ recordDependencyOn(&indexAddr, &extAddr,
+ DEPENDENCY_AUTO_EXTENSION);
+ }
+ break;
+ }
+ }
+ }
+
+ list_free(indexList);
+ table_close(partRel, NoLock);
+}
+
+/*
+ * freePartitionIndexExtDeps: free memory allocated by collectPartitionIndexExtDeps.
+ */
+static void
+freePartitionIndexExtDeps(List *extDepState)
+{
+ ListCell *lc;
+
+ foreach(lc, extDepState)
+ {
+ PartitionIndexExtDepEntry *entry = lfirst(lc);
+
+ list_free(entry->extensionOids);
+ pfree(entry);
+ }
+ list_free(extDepState);
+}
+
/*
* ALTER TABLE <name> MERGE PARTITIONS <partition-list> INTO <partition-name>
*/
@@ -23009,6 +23188,7 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel,
{
Relation newPartRel;
List *mergingPartitions = NIL;
+ List *extDepState = NIL;
Oid defaultPartOid;
Oid existingRelid;
Oid ownerId = InvalidOid;
@@ -23098,6 +23278,13 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel,
defaultPartOid =
get_default_oid_from_partdesc(RelationGetPartitionDesc(rel, true));
+ /*
+ * Collect extension dependencies from indexes on the merging partitions.
+ * We must do this before detaching them, so we can restore the
+ * dependencies on the new partition's indexes later.
+ */
+ extDepState = collectPartitionIndexExtDeps(mergingPartitions);
+
/* Detach all merging partitions. */
foreach_oid(mergingPartitionOid, mergingPartitions)
{
@@ -23175,6 +23362,15 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel,
*/
attachPartitionTable(NULL, rel, newPartRel, cmd->bound);
+ /*
+ * Apply extension dependencies to the new partition's indexes. This
+ * preserves any "DEPENDS ON EXTENSION" settings from the merged
+ * partitions.
+ */
+ applyPartitionIndexExtDeps(RelationGetRelid(newPartRel), extDepState);
+
+ freePartitionIndexExtDeps(extDepState);
+
/* Keep the lock until commit. */
table_close(newPartRel, NoLock);
@@ -23469,11 +23665,13 @@ ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel,
bool isSameName = false;
char tmpRelName[NAMEDATALEN];
List *newPartRels = NIL;
+ List *extDepState = NIL;
ObjectAddress object;
Oid defaultPartOid;
Oid save_userid;
int save_sec_context;
int save_nestlevel;
+ List *splitPartList;
defaultPartOid = get_default_oid_from_partdesc(RelationGetPartitionDesc(rel, true));
@@ -23506,6 +23704,16 @@ ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel,
errmsg("relation \"%s\" already exists", sps->name->relname));
}
+ /*
+ * Collect extension dependencies from indexes on the split partition. We
+ * must do this before detaching it, so we can restore the dependencies on
+ * the new partitions' indexes later.
+ */
+ splitPartList = list_make1_oid(splitRelOid);
+
+ extDepState = collectPartitionIndexExtDeps(splitPartList);
+ list_free(splitPartList);
+
/* Detach the split partition. */
detachPartitionTable(rel, splitRel, defaultPartOid);
@@ -23585,10 +23793,20 @@ ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel,
* needed.
*/
attachPartitionTable(NULL, rel, newPartRel, sps->bound);
+
+ /*
+ * Apply extension dependencies to the new partition's indexes. This
+ * preserves any "DEPENDS ON EXTENSION" settings from the split
+ * partition.
+ */
+ applyPartitionIndexExtDeps(RelationGetRelid(newPartRel), extDepState);
+
/* Keep the lock until commit. */
table_close(newPartRel, NoLock);
}
+ freePartitionIndexExtDeps(extDepState);
+
/* Drop the split partition. */
object.classId = RelationRelationId;
object.objectId = splitRelOid;
diff --git a/src/test/regress/expected/partition_merge.out b/src/test/regress/expected/partition_merge.out
index 925fe4f570a..00aa16964ce 100644
--- a/src/test/regress/expected/partition_merge.out
+++ b/src/test/regress/expected/partition_merge.out
@@ -1091,6 +1091,46 @@ SELECT count(*) FROM t WHERE i = 15 AND g IN (SELECT g + 10 FROM t WHERE i = 5);
(1 row)
DROP TABLE t;
+--
+-- Test that extension dependencies on partition indexes are preserved
+-- after MERGE PARTITIONS.
+--
+CREATE EXTENSION if not exists btree_gist;
+CREATE TABLE t_merge_extdep (i int) PARTITION BY RANGE (i);
+CREATE TABLE t_merge_extdep_1 PARTITION OF t_merge_extdep FOR VALUES FROM (1) TO (2);
+CREATE TABLE t_merge_extdep_2 PARTITION OF t_merge_extdep FOR VALUES FROM (2) TO (3);
+CREATE INDEX t_merge_extdep_idx ON t_merge_extdep USING gist (i);
+-- Add extension dependency on partition indexes
+ALTER INDEX t_merge_extdep_1_i_idx DEPENDS ON EXTENSION btree_gist;
+ALTER INDEX t_merge_extdep_2_i_idx DEPENDS ON EXTENSION btree_gist;
+-- Should fail: dependencies exist
+DROP EXTENSION btree_gist;
+ERROR: cannot drop extension btree_gist because other objects depend on it
+DETAIL: index t_merge_extdep_idx depends on operator class gist_int4_ops for access method gist
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+-- Merge partitions
+ALTER TABLE t_merge_extdep MERGE PARTITIONS (t_merge_extdep_1, t_merge_extdep_2) INTO t_merge_extdep_merged;
+-- Should still fail: dependencies should be preserved on the new partition's index
+DROP EXTENSION btree_gist;
+ERROR: cannot drop extension btree_gist because other objects depend on it
+DETAIL: index t_merge_extdep_idx depends on operator class gist_int4_ops for access method gist
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+-- Verify the dependency exists in pg_depend
+SELECT COUNT(*) > 0 AS has_ext_dep
+FROM pg_depend d
+JOIN pg_class c ON d.objid = c.oid
+JOIN pg_extension e ON d.refobjid = e.oid
+WHERE c.relname = 't_merge_extdep_merged_i_idx'
+ AND e.extname = 'btree_gist'
+ AND d.deptype = 'x';
+ has_ext_dep
+-------------
+ t
+(1 row)
+
+-- Clean up
+DROP TABLE t_merge_extdep;
+DROP EXTENSION btree_gist;
RESET search_path;
--
DROP SCHEMA partitions_merge_schema;
diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out
index 13ca733f9fa..a89c2dadc97 100644
--- a/src/test/regress/expected/partition_split.out
+++ b/src/test/regress/expected/partition_split.out
@@ -1589,6 +1589,47 @@ SELECT count(*) FROM t WHERE i = 0 AND tab_id IN (SELECT tab_id FROM t WHERE i =
(1 row)
DROP TABLE t;
+--
+-- Test that extension dependencies on partition indexes are preserved
+-- after SPLIT PARTITION.
+--
+CREATE EXTENSION citext;
+CREATE TABLE t_extdep (i int) PARTITION BY RANGE (i);
+CREATE TABLE t_extdep_1_3 PARTITION OF t_extdep FOR VALUES FROM (1) TO (3);
+CREATE INDEX t_extdep_idx ON t_extdep (i);
+-- Add extension dependency on partition index
+ALTER INDEX t_extdep_1_3_i_idx DEPENDS ON EXTENSION citext;
+-- Should fail: dependency exists
+DROP EXTENSION citext;
+ERROR: cannot drop index t_extdep_1_3_i_idx because index t_extdep_idx requires it
+HINT: You can drop index t_extdep_idx instead.
+-- Split partition
+ALTER TABLE t_extdep SPLIT PARTITION t_extdep_1_3 INTO
+ (PARTITION t_extdep_1 FOR VALUES FROM (1) TO (2),
+ PARTITION t_extdep_2 FOR VALUES FROM (2) TO (3));
+-- Should still fail: dependencies should be preserved on all new partitions' indexes
+DROP EXTENSION citext;
+ERROR: cannot drop index t_extdep_2_i_idx because index t_extdep_idx requires it
+HINT: You can drop index t_extdep_idx instead.
+-- Verify the dependencies exist in pg_depend for both new partitions
+SELECT c.relname, COUNT(*) > 0 AS has_ext_dep
+FROM pg_depend d
+JOIN pg_class c ON d.objid = c.oid
+JOIN pg_extension e ON d.refobjid = e.oid
+WHERE c.relname IN ('t_extdep_1_i_idx', 't_extdep_2_i_idx')
+ AND e.extname = 'citext'
+ AND d.deptype = 'x'
+GROUP BY c.relname
+ORDER BY c.relname;
+ relname | has_ext_dep
+------------------+-------------
+ t_extdep_1_i_idx | t
+ t_extdep_2_i_idx | t
+(2 rows)
+
+-- Clean up
+DROP TABLE t_extdep;
+DROP EXTENSION citext;
RESET search_path;
--
DROP SCHEMA partition_split_schema;
diff --git a/src/test/regress/sql/partition_merge.sql b/src/test/regress/sql/partition_merge.sql
index a211fee2ad1..4864c66636d 100644
--- a/src/test/regress/sql/partition_merge.sql
+++ b/src/test/regress/sql/partition_merge.sql
@@ -784,6 +784,44 @@ SELECT count(*) FROM t WHERE i = 15 AND g IN (SELECT g + 10 FROM t WHERE i = 5);
DROP TABLE t;
+--
+-- Test that extension dependencies on partition indexes are preserved
+-- after MERGE PARTITIONS.
+--
+CREATE EXTENSION if not exists btree_gist;
+
+CREATE TABLE t_merge_extdep (i int) PARTITION BY RANGE (i);
+CREATE TABLE t_merge_extdep_1 PARTITION OF t_merge_extdep FOR VALUES FROM (1) TO (2);
+CREATE TABLE t_merge_extdep_2 PARTITION OF t_merge_extdep FOR VALUES FROM (2) TO (3);
+CREATE INDEX t_merge_extdep_idx ON t_merge_extdep USING gist (i);
+
+-- Add extension dependency on partition indexes
+ALTER INDEX t_merge_extdep_1_i_idx DEPENDS ON EXTENSION btree_gist;
+ALTER INDEX t_merge_extdep_2_i_idx DEPENDS ON EXTENSION btree_gist;
+
+-- Should fail: dependencies exist
+DROP EXTENSION btree_gist;
+
+-- Merge partitions
+ALTER TABLE t_merge_extdep MERGE PARTITIONS (t_merge_extdep_1, t_merge_extdep_2) INTO t_merge_extdep_merged;
+
+-- Should still fail: dependencies should be preserved on the new partition's index
+DROP EXTENSION btree_gist;
+
+-- Verify the dependency exists in pg_depend
+SELECT COUNT(*) > 0 AS has_ext_dep
+FROM pg_depend d
+JOIN pg_class c ON d.objid = c.oid
+JOIN pg_extension e ON d.refobjid = e.oid
+WHERE c.relname = 't_merge_extdep_merged_i_idx'
+ AND e.extname = 'btree_gist'
+ AND d.deptype = 'x';
+
+-- Clean up
+DROP TABLE t_merge_extdep;
+DROP EXTENSION btree_gist;
+
+
RESET search_path;
--
diff --git a/src/test/regress/sql/partition_split.sql b/src/test/regress/sql/partition_split.sql
index 37c6d730840..ea35aa591a8 100644
--- a/src/test/regress/sql/partition_split.sql
+++ b/src/test/regress/sql/partition_split.sql
@@ -1127,6 +1127,45 @@ SELECT count(*) FROM t WHERE i = 0 AND tab_id IN (SELECT tab_id FROM t WHERE i =
DROP TABLE t;
+--
+-- Test that extension dependencies on partition indexes are preserved
+-- after SPLIT PARTITION.
+--
+CREATE EXTENSION citext;
+
+CREATE TABLE t_extdep (i int) PARTITION BY RANGE (i);
+CREATE TABLE t_extdep_1_3 PARTITION OF t_extdep FOR VALUES FROM (1) TO (3);
+CREATE INDEX t_extdep_idx ON t_extdep (i);
+
+-- Add extension dependency on partition index
+ALTER INDEX t_extdep_1_3_i_idx DEPENDS ON EXTENSION citext;
+
+-- Should fail: dependency exists
+DROP EXTENSION citext;
+
+-- Split partition
+ALTER TABLE t_extdep SPLIT PARTITION t_extdep_1_3 INTO
+ (PARTITION t_extdep_1 FOR VALUES FROM (1) TO (2),
+ PARTITION t_extdep_2 FOR VALUES FROM (2) TO (3));
+
+-- Should still fail: dependencies should be preserved on all new partitions' indexes
+DROP EXTENSION citext;
+
+-- Verify the dependencies exist in pg_depend for both new partitions
+SELECT c.relname, COUNT(*) > 0 AS has_ext_dep
+FROM pg_depend d
+JOIN pg_class c ON d.objid = c.oid
+JOIN pg_extension e ON d.refobjid = e.oid
+WHERE c.relname IN ('t_extdep_1_i_idx', 't_extdep_2_i_idx')
+ AND e.extname = 'citext'
+ AND d.deptype = 'x'
+GROUP BY c.relname
+ORDER BY c.relname;
+
+-- Clean up
+DROP TABLE t_extdep;
+DROP EXTENSION citext;
+
RESET search_path;
--
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 9e6a39f5608..50c3cc6f64c 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2198,6 +2198,7 @@ PartitionDirectoryEntry
PartitionDispatch
PartitionElem
PartitionHashBound
+PartitionIndexExtDepEntry
PartitionKey
PartitionListValue
PartitionMap
--
2.52.0
Attachments:
[text/plain] v2-0001-Preserve-extension-dependencies-on-indexes-during.patch (17.9K, 2-v2-0001-Preserve-extension-dependencies-on-indexes-during.patch)
download | inline diff:
From 44c37e30e60b53d4478dbf32d4aae2a7d6b9b17f Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email protected]>
Date: Wed, 11 Mar 2026 11:39:56 -0300
Subject: [PATCH v2] Preserve extension dependencies on indexes during
partition merge/split
When using ALTER TABLE ... MERGE PARTITIONS or ALTER TABLE ... SPLIT
PARTITION, extension dependencies on partition indexes (created via
ALTER INDEX ... DEPENDS ON EXTENSION) were being lost. This happened
because the new partition indexes are created fresh from the parent
partitioned table's indexes, while the old partition indexes (with
their extension dependencies) are dropped.
Fix this by collecting extension dependencies from source partition
indexes before detaching them, then applying those dependencies to
the corresponding new partition indexes after they're created. The
mapping between old and new indexes is done via their common parent
partitioned index.
Discussion: https://www.postgresql.org/message-id/CALdSSPjXtzGM7Uk4fWRwRMXcCczge5uNirPQcYCHKPAWPkp9iQ%40mail.gmail.com
Reported-by: Kirill Reshke
---
src/backend/commands/tablecmds.c | 218 ++++++++++++++++++
src/test/regress/expected/partition_merge.out | 40 ++++
src/test/regress/expected/partition_split.out | 41 ++++
src/test/regress/sql/partition_merge.sql | 38 +++
src/test/regress/sql/partition_split.sql | 39 ++++
src/tools/pgindent/typedefs.list | 1 +
6 files changed, 377 insertions(+)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index eec09ba1ded..afa89f8b3ce 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -40,6 +40,7 @@
#include "catalog/pg_collation.h"
#include "catalog/pg_constraint.h"
#include "catalog/pg_depend.h"
+#include "catalog/pg_extension_d.h"
#include "catalog/pg_foreign_table.h"
#include "catalog/pg_inherits.h"
#include "catalog/pg_largeobject.h"
@@ -365,6 +366,16 @@ typedef enum addFkConstraintSides
addFkBothSides,
} addFkConstraintSides;
+/*
+ * Hold extension dependencies for a partitioned index. Used by
+ * collectPartitionIndexExtDeps and applyPartitionIndexExtDeps.
+ */
+typedef struct PartitionIndexExtDepEntry
+{
+ Oid parentIndexOid; /* OID of the parent partitioned index */
+ List *extensionOids; /* List of extension OIDs this index depends */
+} PartitionIndexExtDepEntry;
+
/*
* Partition tables are expected to be dropped when the parent partitioned
* table gets dropped. Hence for partitioning we use AUTO dependency.
@@ -760,6 +771,9 @@ static void ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation
static void ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab,
Relation rel, PartitionCmd *cmd,
AlterTableUtilityContext *context);
+static List *collectPartitionIndexExtDeps(List *partitionOids);
+static void applyPartitionIndexExtDeps(Oid newPartOid, List *extDepState);
+static void freePartitionIndexExtDeps(List *extDepState);
/* ----------------------------------------------------------------
* DefineRelation
@@ -23000,6 +23014,171 @@ detachPartitionTable(Relation parent_rel, Relation child_rel, Oid defaultPartOid
PopActiveSnapshot();
}
+/*
+ * collectPartitionIndexExtDeps: collect extension dependencies from indexes
+ * on the given partitions.
+ *
+ * For each index on the source partitions that has extension dependencies, we
+ * collect a mapping from the parent partitioned index OID to the list of
+ * extension OIDs.
+ *
+ * Returns a list of PartitionIndexExtDepEntry structs.
+ */
+static List *
+collectPartitionIndexExtDeps(List *partitionOids)
+{
+ List *result = NIL;
+
+ foreach_oid(partOid, partitionOids)
+ {
+ Relation partRel;
+ List *indexList;
+
+ /*
+ * Use NoLock since the caller already holds AccessExclusiveLock on
+ * these partitions.
+ */
+ partRel = table_open(partOid, NoLock);
+ indexList = RelationGetIndexList(partRel);
+
+ foreach_oid(indexOid, indexList)
+ {
+ Oid parentIdxOid;
+ List *extDeps;
+ PartitionIndexExtDepEntry *entry = NULL;
+ ListCell *lc;
+
+ /* Get the parent index if this is a partition index */
+ parentIdxOid = get_partition_parent(indexOid, true);
+ if (!OidIsValid(parentIdxOid))
+ continue;
+
+ /* Get extension dependencies for this index */
+ extDeps = getAutoExtensionsOfObject(RelationRelationId, indexOid);
+ if (extDeps == NIL)
+ continue;
+
+ /*
+ * Look for existing entry for this parent index.
+ */
+ foreach(lc, result)
+ {
+ PartitionIndexExtDepEntry *e = lfirst(lc);
+
+ if (e->parentIndexOid == parentIdxOid)
+ {
+ entry = e;
+ break;
+ }
+ }
+
+ if (entry != NULL)
+ {
+ /* Add extension dependencies avoiding duplicates */
+ foreach_oid(extOid, extDeps)
+ {
+ if (!list_member_oid(entry->extensionOids, extOid))
+ entry->extensionOids = lappend_oid(entry->extensionOids,
+ extOid);
+ }
+ list_free(extDeps);
+ }
+ else
+ {
+ /* Create new entry */
+ entry = palloc(sizeof(PartitionIndexExtDepEntry));
+ entry->parentIndexOid = parentIdxOid;
+ entry->extensionOids = extDeps;
+ result = lappend(result, entry);
+ }
+ }
+
+ list_free(indexList);
+ table_close(partRel, NoLock);
+ }
+
+ return result;
+}
+
+/*
+ * applyPartitionIndexExtDeps: apply collected extension dependencies to
+ * indexes on a new partition.
+ *
+ * For each index on the new partition, look up its parent index in the
+ * extDepState list. If found, record extension dependencies on the new index.
+ */
+static void
+applyPartitionIndexExtDeps(Oid newPartOid, List *extDepState)
+{
+ Relation partRel;
+ List *indexList;
+
+ if (extDepState == NIL)
+ return;
+
+ /*
+ * Use NoLock since the caller already holds AccessExclusiveLock on the
+ * new partition.
+ */
+ partRel = table_open(newPartOid, NoLock);
+ indexList = RelationGetIndexList(partRel);
+
+ foreach_oid(indexOid, indexList)
+ {
+ Oid parentIdxOid;
+ ListCell *lc;
+
+ /* Get the parent index if this is a partition index */
+ parentIdxOid = get_partition_parent(indexOid, true);
+ if (!OidIsValid(parentIdxOid))
+ continue;
+
+ /* Look for extension dependencies to apply */
+ foreach(lc, extDepState)
+ {
+ PartitionIndexExtDepEntry *entry = lfirst(lc);
+
+ if (entry->parentIndexOid == parentIdxOid)
+ {
+ ObjectAddress indexAddr;
+
+ ObjectAddressSet(indexAddr, RelationRelationId, indexOid);
+
+ foreach_oid(extOid, entry->extensionOids)
+ {
+ ObjectAddress extAddr;
+
+ ObjectAddressSet(extAddr, ExtensionRelationId, extOid);
+ recordDependencyOn(&indexAddr, &extAddr,
+ DEPENDENCY_AUTO_EXTENSION);
+ }
+ break;
+ }
+ }
+ }
+
+ list_free(indexList);
+ table_close(partRel, NoLock);
+}
+
+/*
+ * freePartitionIndexExtDeps: free memory allocated by collectPartitionIndexExtDeps.
+ */
+static void
+freePartitionIndexExtDeps(List *extDepState)
+{
+ ListCell *lc;
+
+ foreach(lc, extDepState)
+ {
+ PartitionIndexExtDepEntry *entry = lfirst(lc);
+
+ list_free(entry->extensionOids);
+ pfree(entry);
+ }
+ list_free(extDepState);
+}
+
/*
* ALTER TABLE <name> MERGE PARTITIONS <partition-list> INTO <partition-name>
*/
@@ -23009,6 +23188,7 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel,
{
Relation newPartRel;
List *mergingPartitions = NIL;
+ List *extDepState = NIL;
Oid defaultPartOid;
Oid existingRelid;
Oid ownerId = InvalidOid;
@@ -23098,6 +23278,13 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel,
defaultPartOid =
get_default_oid_from_partdesc(RelationGetPartitionDesc(rel, true));
+ /*
+ * Collect extension dependencies from indexes on the merging partitions.
+ * We must do this before detaching them, so we can restore the
+ * dependencies on the new partition's indexes later.
+ */
+ extDepState = collectPartitionIndexExtDeps(mergingPartitions);
+
/* Detach all merging partitions. */
foreach_oid(mergingPartitionOid, mergingPartitions)
{
@@ -23175,6 +23362,15 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel,
*/
attachPartitionTable(NULL, rel, newPartRel, cmd->bound);
+ /*
+ * Apply extension dependencies to the new partition's indexes. This
+ * preserves any "DEPENDS ON EXTENSION" settings from the merged
+ * partitions.
+ */
+ applyPartitionIndexExtDeps(RelationGetRelid(newPartRel), extDepState);
+
+ freePartitionIndexExtDeps(extDepState);
+
/* Keep the lock until commit. */
table_close(newPartRel, NoLock);
@@ -23469,11 +23665,13 @@ ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel,
bool isSameName = false;
char tmpRelName[NAMEDATALEN];
List *newPartRels = NIL;
+ List *extDepState = NIL;
ObjectAddress object;
Oid defaultPartOid;
Oid save_userid;
int save_sec_context;
int save_nestlevel;
+ List *splitPartList;
defaultPartOid = get_default_oid_from_partdesc(RelationGetPartitionDesc(rel, true));
@@ -23506,6 +23704,16 @@ ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel,
errmsg("relation \"%s\" already exists", sps->name->relname));
}
+ /*
+ * Collect extension dependencies from indexes on the split partition. We
+ * must do this before detaching it, so we can restore the dependencies on
+ * the new partitions' indexes later.
+ */
+ splitPartList = list_make1_oid(splitRelOid);
+
+ extDepState = collectPartitionIndexExtDeps(splitPartList);
+ list_free(splitPartList);
+
/* Detach the split partition. */
detachPartitionTable(rel, splitRel, defaultPartOid);
@@ -23585,10 +23793,20 @@ ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel,
* needed.
*/
attachPartitionTable(NULL, rel, newPartRel, sps->bound);
+
+ /*
+ * Apply extension dependencies to the new partition's indexes. This
+ * preserves any "DEPENDS ON EXTENSION" settings from the split
+ * partition.
+ */
+ applyPartitionIndexExtDeps(RelationGetRelid(newPartRel), extDepState);
+
/* Keep the lock until commit. */
table_close(newPartRel, NoLock);
}
+ freePartitionIndexExtDeps(extDepState);
+
/* Drop the split partition. */
object.classId = RelationRelationId;
object.objectId = splitRelOid;
diff --git a/src/test/regress/expected/partition_merge.out b/src/test/regress/expected/partition_merge.out
index 925fe4f570a..00aa16964ce 100644
--- a/src/test/regress/expected/partition_merge.out
+++ b/src/test/regress/expected/partition_merge.out
@@ -1091,6 +1091,46 @@ SELECT count(*) FROM t WHERE i = 15 AND g IN (SELECT g + 10 FROM t WHERE i = 5);
(1 row)
DROP TABLE t;
+--
+-- Test that extension dependencies on partition indexes are preserved
+-- after MERGE PARTITIONS.
+--
+CREATE EXTENSION if not exists btree_gist;
+CREATE TABLE t_merge_extdep (i int) PARTITION BY RANGE (i);
+CREATE TABLE t_merge_extdep_1 PARTITION OF t_merge_extdep FOR VALUES FROM (1) TO (2);
+CREATE TABLE t_merge_extdep_2 PARTITION OF t_merge_extdep FOR VALUES FROM (2) TO (3);
+CREATE INDEX t_merge_extdep_idx ON t_merge_extdep USING gist (i);
+-- Add extension dependency on partition indexes
+ALTER INDEX t_merge_extdep_1_i_idx DEPENDS ON EXTENSION btree_gist;
+ALTER INDEX t_merge_extdep_2_i_idx DEPENDS ON EXTENSION btree_gist;
+-- Should fail: dependencies exist
+DROP EXTENSION btree_gist;
+ERROR: cannot drop extension btree_gist because other objects depend on it
+DETAIL: index t_merge_extdep_idx depends on operator class gist_int4_ops for access method gist
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+-- Merge partitions
+ALTER TABLE t_merge_extdep MERGE PARTITIONS (t_merge_extdep_1, t_merge_extdep_2) INTO t_merge_extdep_merged;
+-- Should still fail: dependencies should be preserved on the new partition's index
+DROP EXTENSION btree_gist;
+ERROR: cannot drop extension btree_gist because other objects depend on it
+DETAIL: index t_merge_extdep_idx depends on operator class gist_int4_ops for access method gist
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+-- Verify the dependency exists in pg_depend
+SELECT COUNT(*) > 0 AS has_ext_dep
+FROM pg_depend d
+JOIN pg_class c ON d.objid = c.oid
+JOIN pg_extension e ON d.refobjid = e.oid
+WHERE c.relname = 't_merge_extdep_merged_i_idx'
+ AND e.extname = 'btree_gist'
+ AND d.deptype = 'x';
+ has_ext_dep
+-------------
+ t
+(1 row)
+
+-- Clean up
+DROP TABLE t_merge_extdep;
+DROP EXTENSION btree_gist;
RESET search_path;
--
DROP SCHEMA partitions_merge_schema;
diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out
index 13ca733f9fa..a89c2dadc97 100644
--- a/src/test/regress/expected/partition_split.out
+++ b/src/test/regress/expected/partition_split.out
@@ -1589,6 +1589,47 @@ SELECT count(*) FROM t WHERE i = 0 AND tab_id IN (SELECT tab_id FROM t WHERE i =
(1 row)
DROP TABLE t;
+--
+-- Test that extension dependencies on partition indexes are preserved
+-- after SPLIT PARTITION.
+--
+CREATE EXTENSION citext;
+CREATE TABLE t_extdep (i int) PARTITION BY RANGE (i);
+CREATE TABLE t_extdep_1_3 PARTITION OF t_extdep FOR VALUES FROM (1) TO (3);
+CREATE INDEX t_extdep_idx ON t_extdep (i);
+-- Add extension dependency on partition index
+ALTER INDEX t_extdep_1_3_i_idx DEPENDS ON EXTENSION citext;
+-- Should fail: dependency exists
+DROP EXTENSION citext;
+ERROR: cannot drop index t_extdep_1_3_i_idx because index t_extdep_idx requires it
+HINT: You can drop index t_extdep_idx instead.
+-- Split partition
+ALTER TABLE t_extdep SPLIT PARTITION t_extdep_1_3 INTO
+ (PARTITION t_extdep_1 FOR VALUES FROM (1) TO (2),
+ PARTITION t_extdep_2 FOR VALUES FROM (2) TO (3));
+-- Should still fail: dependencies should be preserved on all new partitions' indexes
+DROP EXTENSION citext;
+ERROR: cannot drop index t_extdep_2_i_idx because index t_extdep_idx requires it
+HINT: You can drop index t_extdep_idx instead.
+-- Verify the dependencies exist in pg_depend for both new partitions
+SELECT c.relname, COUNT(*) > 0 AS has_ext_dep
+FROM pg_depend d
+JOIN pg_class c ON d.objid = c.oid
+JOIN pg_extension e ON d.refobjid = e.oid
+WHERE c.relname IN ('t_extdep_1_i_idx', 't_extdep_2_i_idx')
+ AND e.extname = 'citext'
+ AND d.deptype = 'x'
+GROUP BY c.relname
+ORDER BY c.relname;
+ relname | has_ext_dep
+------------------+-------------
+ t_extdep_1_i_idx | t
+ t_extdep_2_i_idx | t
+(2 rows)
+
+-- Clean up
+DROP TABLE t_extdep;
+DROP EXTENSION citext;
RESET search_path;
--
DROP SCHEMA partition_split_schema;
diff --git a/src/test/regress/sql/partition_merge.sql b/src/test/regress/sql/partition_merge.sql
index a211fee2ad1..4864c66636d 100644
--- a/src/test/regress/sql/partition_merge.sql
+++ b/src/test/regress/sql/partition_merge.sql
@@ -784,6 +784,44 @@ SELECT count(*) FROM t WHERE i = 15 AND g IN (SELECT g + 10 FROM t WHERE i = 5);
DROP TABLE t;
+--
+-- Test that extension dependencies on partition indexes are preserved
+-- after MERGE PARTITIONS.
+--
+CREATE EXTENSION if not exists btree_gist;
+
+CREATE TABLE t_merge_extdep (i int) PARTITION BY RANGE (i);
+CREATE TABLE t_merge_extdep_1 PARTITION OF t_merge_extdep FOR VALUES FROM (1) TO (2);
+CREATE TABLE t_merge_extdep_2 PARTITION OF t_merge_extdep FOR VALUES FROM (2) TO (3);
+CREATE INDEX t_merge_extdep_idx ON t_merge_extdep USING gist (i);
+
+-- Add extension dependency on partition indexes
+ALTER INDEX t_merge_extdep_1_i_idx DEPENDS ON EXTENSION btree_gist;
+ALTER INDEX t_merge_extdep_2_i_idx DEPENDS ON EXTENSION btree_gist;
+
+-- Should fail: dependencies exist
+DROP EXTENSION btree_gist;
+
+-- Merge partitions
+ALTER TABLE t_merge_extdep MERGE PARTITIONS (t_merge_extdep_1, t_merge_extdep_2) INTO t_merge_extdep_merged;
+
+-- Should still fail: dependencies should be preserved on the new partition's index
+DROP EXTENSION btree_gist;
+
+-- Verify the dependency exists in pg_depend
+SELECT COUNT(*) > 0 AS has_ext_dep
+FROM pg_depend d
+JOIN pg_class c ON d.objid = c.oid
+JOIN pg_extension e ON d.refobjid = e.oid
+WHERE c.relname = 't_merge_extdep_merged_i_idx'
+ AND e.extname = 'btree_gist'
+ AND d.deptype = 'x';
+
+-- Clean up
+DROP TABLE t_merge_extdep;
+DROP EXTENSION btree_gist;
+
+
RESET search_path;
--
diff --git a/src/test/regress/sql/partition_split.sql b/src/test/regress/sql/partition_split.sql
index 37c6d730840..ea35aa591a8 100644
--- a/src/test/regress/sql/partition_split.sql
+++ b/src/test/regress/sql/partition_split.sql
@@ -1127,6 +1127,45 @@ SELECT count(*) FROM t WHERE i = 0 AND tab_id IN (SELECT tab_id FROM t WHERE i =
DROP TABLE t;
+--
+-- Test that extension dependencies on partition indexes are preserved
+-- after SPLIT PARTITION.
+--
+CREATE EXTENSION citext;
+
+CREATE TABLE t_extdep (i int) PARTITION BY RANGE (i);
+CREATE TABLE t_extdep_1_3 PARTITION OF t_extdep FOR VALUES FROM (1) TO (3);
+CREATE INDEX t_extdep_idx ON t_extdep (i);
+
+-- Add extension dependency on partition index
+ALTER INDEX t_extdep_1_3_i_idx DEPENDS ON EXTENSION citext;
+
+-- Should fail: dependency exists
+DROP EXTENSION citext;
+
+-- Split partition
+ALTER TABLE t_extdep SPLIT PARTITION t_extdep_1_3 INTO
+ (PARTITION t_extdep_1 FOR VALUES FROM (1) TO (2),
+ PARTITION t_extdep_2 FOR VALUES FROM (2) TO (3));
+
+-- Should still fail: dependencies should be preserved on all new partitions' indexes
+DROP EXTENSION citext;
+
+-- Verify the dependencies exist in pg_depend for both new partitions
+SELECT c.relname, COUNT(*) > 0 AS has_ext_dep
+FROM pg_depend d
+JOIN pg_class c ON d.objid = c.oid
+JOIN pg_extension e ON d.refobjid = e.oid
+WHERE c.relname IN ('t_extdep_1_i_idx', 't_extdep_2_i_idx')
+ AND e.extname = 'citext'
+ AND d.deptype = 'x'
+GROUP BY c.relname
+ORDER BY c.relname;
+
+-- Clean up
+DROP TABLE t_extdep;
+DROP EXTENSION citext;
+
RESET search_path;
--
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 9e6a39f5608..50c3cc6f64c 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2198,6 +2198,7 @@ PartitionDirectoryEntry
PartitionDispatch
PartitionElem
PartitionHashBound
+PartitionIndexExtDepEntry
PartitionKey
PartitionListValue
PartitionMap
--
2.52.0
view thread (21+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: MERGE PARTITIONS and DEPENDS ON EXTENSION.
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox