On Mon, Aug 3, 2026 at 1:17 AM Chao Li <[email protected]> wrote:
>
> PFA v13: addressed Alberto’s comment on 0001; 0002 is the same as v12.
I started taking a look at this and found several other properties
that are dropped (or rather, not saved and restored) for leaf
partitions after an ALTER COLUMN TYPE (or ALTER COLUMN SET EXPRESSION)
rebuild. Comments, stats targets, and reloptions in addition to the
name, replica identity marker, and cluster-on marker (see repro at
bottom of email).
This made me think we should save all of these in a data structure on
the IndexStmt and then update the catalog tables after creating the
new index instead of doing the deferred sub-command execution (as your
v13-0002). I've attached a patch that implements my idea. It edits the
same locations as the custom name save-and-restore approach in
v13-0001 but does it for all the missing properties.
There is precedent for doing this -- index_concurrently_swap() does it
this way for indisreplident/indisclustered already.
It is possible to use the deferred sub-command method for RI, cluster,
and comment, but it won't work for name (bc name doesn't have an alter
sub-command for renaming an index) nor for stat-target and reloptions
(bc those lookup the index via oid and we need the old index oid which
is no longer around by the time we are executing a deferred
subcommand).
I don't love that the root partition properties are remembered as part
of ATExecAlterColumnType() and the child partitions as part of
ATPostAlterTypeParse(), but there didn't seem to be a good way of
moving either one. The IndexStmt doesn't exist yet in
ATExecAlterColumnType(). This is common to all the patches (v13 and my
attached patches).
The first patch is to preserve stats targets in general -- it wasn't
lost just for partition child indexes but also for regular indexes.
This is basically the same as a patch Zsolt proposed in [1]. Its test
might be able to be minimized or incorporated into an existing test,
but I haven't tried to do that yet.
The second patch handles preserving the properties for child
partitions. It also includes an idea for a regression test that
compares the catalog table rows before and after ALTER COLUMN TYPE to
make sure we are restoring everything we expect to be the same. I had
an LLM write it and it suggested using jsonb and a sql function so we
could subtract the columns we expect to change but select everything
else. The idea is to avoid regressions. If someone adds a new
property, they'll have to explicitly allow not transferring it after
ALTER COLUMN TYPE. The test is a little hard to read, so maybe there's
a way to simplify it. I'm not sure.
I'm not convinced this needs to be backpatched. I don't see anything
in the docs saying that after an ALTER COLUMN TYPE these various
properties would be preserved (and definitely nothing about them being
preserved on partition leaves). So, users most likely would have
scripts doing the follow-up alter tables themselves. And, for the
replica identity, you'll have to do the schema change on the replica
for it to work anyway, so it is already a multi-step process. I can be
convinced otherwise if, for example, not preserving these properties
poses a security risk like the one fixed in 6713a6e04cb.
It seems like there are some inaccuracies in the docs around what
operations recurse to leaves and which don't and potentially some
inconsistencies or even bugs in the behavior itself.
ALTER COLUMN ... SET (attribute_option) doesn't recurse to partitions
while SET STATISTICS does.
SET COMPRESSION doesn't recurse to partitions while SET STORAGE does.
ALTER TABLE parent RENAME CONSTRAINT on a UNIQUE/PK doesn't rename the
leaf's backing index (that one is debatable).
In the docs it says
"The actions for identity columns (ADD GENERATED, SET etc., DROP
IDENTITY), as well as the actions CLUSTER, OWNER, and TABLESPACE never
recurse to descendant tables; that is, they always act as though ONLY
were specified."
but identity does recurse to descendant tables.
And then there is Zsolt's other patch in [1] which keeps extended
statistics from losing their stats targets.
Anyway, I started to feel a bit defeated so I stopped looking.
Frankly, all of this made me wonder if we even know what behavior we
want in all these cases, and maybe I should just leave it the way it
is. I see you have started a thread where you try to define the
behavior [2] and mention that people like Robert Haas have been saying
for a long time that we should define consistent semantics for it all.
I don't think I'm up for trying to fix everything, but I do want to
make sure that I won't be making things worse by committing this
series of patches.
Repro for lost comment, stats target, and index reloption:
CREATE TABLE t (id int, val int) PARTITION BY RANGE (id);
CREATE TABLE t1 PARTITION OF t FOR VALUES FROM (0) TO (100);
CREATE INDEX t_expr ON t ((val + 1));
COMMENT ON INDEX t1_val_1_idx IS 'important note';
ALTER INDEX t1_val_1_idx ALTER COLUMN 1 SET STATISTICS 321;
ALTER INDEX t1_val_1_idx SET (fillfactor = 42);
SELECT obj_description('t1_val_1_idx'::regclass) AS comment,
(SELECT attstattarget FROM pg_attribute
WHERE attrelid = 't1_val_1_idx'::regclass AND attnum = 1) AS
stat_target,
(SELECT reloptions FROM pg_class WHERE relname =
't1_val_1_idx') AS reloptions;
ALTER TABLE t ALTER COLUMN val TYPE int;
SELECT obj_description('t1_val_1_idx'::regclass) AS comment,
(SELECT attstattarget FROM pg_attribute
WHERE attrelid = 't1_val_1_idx'::regclass AND attnum = 1) AS
stat_target,
(SELECT reloptions FROM pg_class WHERE relname =
't1_val_1_idx') AS reloptions;
- Melanie
[1]
https://www.postgresql.org/message-id/flat/CAN4CZFNZwcCgi-igaD%3DLH1ubxMBqJJS%2Bp4ZnOKKdCi9duaMu_w%40mail.gmail.com
[2]
https://www.postgresql.org/message-id/[email protected]
From 8bd97da9fcfe6587978697bff48b700723eab8b3 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <[email protected]>
Date: Fri, 21 Aug 2026 12:59:21 -0400
Subject: [PATCH v14 1/2] Preserve index per-column statistics targets across
ALTER COLUMN TYPE
A per-column statistics target set on an index (ALTER INDEX ... ALTER
COLUMN n SET STATISTICS) was silently lost when ALTER TABLE ... ALTER
COLUMN TYPE rebuilt the index. A statistics target is not expressible as
a CREATE INDEX clause, so it is not reproduced by the
pg_get_indexdef_string() that recreates the index.
Capture the stats target before dropping the index and then reapply it
after creating the new index.
Only the statistics target, not the collected statistics data, which
would be a compatibility concern with the new column type.
Author: Zsolt Parragi <[email protected]>
Co-authored-by: Melanie Plageman <[email protected]>
Discussion: https://postgr.es/m/CAN4CZFNZwcCgi-igaD=lh1ubxmbqjjs+p4znokkdci9duam...@mail.gmail.com
Discussion: https://postgr.es/m/[email protected]
---
src/backend/commands/indexcmds.c | 50 ++++++++++++++++++++++-
src/backend/commands/tablecmds.c | 43 +++++++++++++++++++
src/include/nodes/parsenodes.h | 20 ++++++++-
src/test/regress/expected/alter_table.out | 20 +++++++++
src/test/regress/sql/alter_table.sql | 14 +++++++
src/tools/pgindent/typedefs.list | 1 +
6 files changed, 146 insertions(+), 2 deletions(-)
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 5a0312fe772..4e592ea7785 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -83,6 +83,7 @@ typedef struct CIEN_context
/* non-export function prototypes */
static bool CompareOpclassOptions(const Datum *opts1, const Datum *opts2, int natts);
static void CheckPredicate(Expr *predicate);
+static void SetIndexStatTargets(Oid indexRelationId, List *stattargets);
static void ComputeIndexAttrs(ParseState *pstate,
IndexInfo *indexInfo,
Oid *typeOids,
@@ -510,6 +511,46 @@ WaitForOlderSnapshots(TransactionId limitXmin, bool progress)
}
+/*
+ * Update the required catalog entries to restore the list of statistics
+ * targets to the index passed in as indexRelationId. stattargets is a list of
+ * IndexStatTarget nodes, one per column that had a target set.
+ */
+static void
+SetIndexStatTargets(Oid indexRelationId, List *stattargets)
+{
+ Relation attrelation = table_open(AttributeRelationId, RowExclusiveLock);
+
+ foreach_node(IndexStatTarget, st, stattargets)
+ {
+ HeapTuple attup;
+ HeapTuple newtuple;
+ Datum repl_val[Natts_pg_attribute];
+ bool repl_null[Natts_pg_attribute];
+ bool repl_repl[Natts_pg_attribute];
+
+ attup = SearchSysCacheCopy2(ATTNUM,
+ ObjectIdGetDatum(indexRelationId),
+ Int16GetDatum(st->attnum));
+ if (!HeapTupleIsValid(attup))
+ continue;
+ memset(repl_null, false, sizeof(repl_null));
+ memset(repl_repl, false, sizeof(repl_repl));
+ repl_val[Anum_pg_attribute_attstattarget - 1] =
+ Int16GetDatum(st->stattarget);
+ repl_repl[Anum_pg_attribute_attstattarget - 1] = true;
+ newtuple = heap_modify_tuple(attup,
+ RelationGetDescr(attrelation),
+ repl_val, repl_null, repl_repl);
+ CatalogTupleUpdate(attrelation, &newtuple->t_self, newtuple);
+ heap_freetuple(newtuple);
+ heap_freetuple(attup);
+ }
+
+ table_close(attrelation, RowExclusiveLock);
+}
+
+
/*
* DefineIndex
* Creates a new index.
@@ -1319,11 +1360,18 @@ DefineIndex(ParseState *pstate,
root_save_nestlevel = NewGUCNestLevel();
RestrictSearchPath();
- /* Add any requested comment */
+ /*
+ * Restore index properties that were not able to be recreated as part of
+ * CREATE INDEX. These were saved in the IndexStmt so we could restore
+ * them now by updating the correct catalog tables.
+ */
if (stmt->idxcomment != NULL)
CreateComments(indexRelationId, RelationRelationId, 0,
stmt->idxcomment);
+ if (stmt->idxstattargets != NIL)
+ SetIndexStatTargets(indexRelationId, stmt->idxstattargets);
+
if (partitioned)
{
PartitionDesc partdesc;
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 9b911310f05..f9b1b0aa82d 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -707,6 +707,7 @@ static void RebuildConstraintComment(AlteredTableInfo *tab, AlterTablePass pass,
Oid objid, Relation rel, List *domname,
const char *conname);
static void TryReuseIndex(Oid oldId, IndexStmt *stmt);
+static List *GetIndexStatTargets(Oid indexOid);
static void TryReuseForeignKey(Oid oldId, Constraint *con);
static ObjectAddress ATExecAlterColumnGenericOptions(Relation rel, const char *colName,
List *options, LOCKMODE lockmode);
@@ -16439,6 +16440,8 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, Oid ownerId,
stmt->reset_default_tblspc = true;
/* keep the index's comment */
stmt->idxcomment = GetComment(oldId, RelationRelationId, 0);
+ /* keep the index's per-column statistics targets */
+ stmt->idxstattargets = GetIndexStatTargets(oldId);
newcmd = makeNode(AlterTableCmd);
newcmd->subtype = AT_ReAddIndex;
@@ -16468,6 +16471,8 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, Oid ownerId,
/* keep any comment on the index */
indstmt->idxcomment = GetComment(indoid,
RelationRelationId, 0);
+ /* keep the index's per-column statistics targets */
+ indstmt->idxstattargets = GetIndexStatTargets(indoid);
indstmt->reset_default_tblspc = true;
cmd->subtype = AT_ReAddIndex;
@@ -16615,6 +16620,44 @@ RebuildConstraintComment(AlteredTableInfo *tab, AlterTablePass pass, Oid objid,
tab->subcmds[pass] = lappend(tab->subcmds[pass], newcmd);
}
+/*
+ * Collect the per-column statistics targets of an index into a list of
+ * IndexStatTarget nodes. Returns NIL if none are set.
+ */
+static List *
+GetIndexStatTargets(Oid indexOid)
+{
+ List *result = NIL;
+ Relation irel;
+
+ irel = index_open(indexOid, AccessShareLock);
+ for (int i = 1; i <= IndexRelationGetNumberOfAttributes(irel); i++)
+ {
+ HeapTuple atup;
+ Datum d;
+ bool isnull;
+
+ atup = SearchSysCache2(ATTNUM, ObjectIdGetDatum(indexOid),
+ Int16GetDatum(i));
+ if (!HeapTupleIsValid(atup))
+ continue;
+ d = SysCacheGetAttr(ATTNUM, atup,
+ Anum_pg_attribute_attstattarget, &isnull);
+ if (!isnull)
+ {
+ IndexStatTarget *st = makeNode(IndexStatTarget);
+
+ st->attnum = i;
+ st->stattarget = DatumGetInt16(d);
+ result = lappend(result, st);
+ }
+ ReleaseSysCache(atup);
+ }
+ index_close(irel, AccessShareLock);
+
+ return result;
+}
+
/*
* Subroutine for ATPostAlterTypeParse(). Calls out to CheckIndexCompatible()
* for the real analysis, then mutates the IndexStmt based on that verdict.
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 8a9df884276..84f7f0e7d3b 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3640,7 +3640,6 @@ typedef struct IndexStmt
List *options; /* WITH clause options: a list of DefElem */
Node *whereClause; /* qualification (partial-index predicate) */
List *excludeOpNames; /* exclusion operator names, or NIL if none */
- char *idxcomment; /* comment to apply to index, or NULL */
Oid indexOid; /* OID of an existing index, if any */
RelFileNumber oldNumber; /* relfilenumber of existing storage, if any */
SubTransactionId oldCreateSubid; /* rd_createSubid of oldNumber */
@@ -3658,8 +3657,27 @@ typedef struct IndexStmt
bool if_not_exists; /* just do nothing if index already exists? */
bool reset_default_tblspc; /* reset default_tablespace prior to
* executing */
+
+ /*
+ * When doing an operation on the index that causes it to be dropped and
+ * recreated, these properties are not automatically cloned from the old
+ * index to the new and must be explicitly saved before dropping the old
+ * index and restored after creating the new index.
+ */
+ char *idxcomment; /* comment to apply to index, or NULL */
+ List *idxstattargets; /* list of IndexStatTarget to restore */
} IndexStmt;
+/* one per-column statistics target carried across an index rebuild */
+typedef struct IndexStatTarget
+{
+ pg_node_attr(no_equal, no_query_jumble)
+
+ NodeTag type;
+ int attnum; /* index column number (1-based) */
+ int stattarget; /* attstattarget value to restore */
+} IndexStatTarget;
+
/* ----------------------
* Create Statistics Statement
* ----------------------
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index e167a41ce79..8b6d85461f9 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -2326,6 +2326,26 @@ select conname, obj_description(oid, 'pg_constraint') as desc
-- Don't remove this DROP, it exposes bug #15672
drop table at_partitioned;
+-- Per-column statistics targets should still exist after an ALTER COLUMN TYPE
+create table at_reb_plain (id int not null, val int not null);
+create index at_reb_plain_expr on at_reb_plain ((val + 1));
+create unique index at_reb_plain_u on at_reb_plain ((id + 0), (val + 0));
+alter index at_reb_plain_expr alter column 1 set statistics 321;
+alter index at_reb_plain_u alter column 1 set statistics 111;
+alter index at_reb_plain_u alter column 2 set statistics 222;
+alter table at_reb_plain alter column val type bigint;
+select c.relname, a.attnum, a.attstattarget
+ from pg_attribute a join pg_class c on c.oid = a.attrelid
+ where c.relname in ('at_reb_plain_expr', 'at_reb_plain_u') and a.attnum > 0
+ order by c.relname, a.attnum;
+ relname | attnum | attstattarget
+-------------------+--------+---------------
+ at_reb_plain_expr | 1 | 321
+ at_reb_plain_u | 1 | 111
+ at_reb_plain_u | 2 | 222
+(3 rows)
+
+drop table at_reb_plain;
-- disallow recursive containment of row types
create temp table recur1 (f1 int);
alter table recur1 add column f2 recur1; -- fails
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 9f6c2a4bb08..8bdfc4262ef 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -1531,6 +1531,20 @@ select conname, obj_description(oid, 'pg_constraint') as desc
-- Don't remove this DROP, it exposes bug #15672
drop table at_partitioned;
+-- Per-column statistics targets should still exist after an ALTER COLUMN TYPE
+create table at_reb_plain (id int not null, val int not null);
+create index at_reb_plain_expr on at_reb_plain ((val + 1));
+create unique index at_reb_plain_u on at_reb_plain ((id + 0), (val + 0));
+alter index at_reb_plain_expr alter column 1 set statistics 321;
+alter index at_reb_plain_u alter column 1 set statistics 111;
+alter index at_reb_plain_u alter column 2 set statistics 222;
+alter table at_reb_plain alter column val type bigint;
+select c.relname, a.attnum, a.attstattarget
+ from pg_attribute a join pg_class c on c.oid = a.attrelid
+ where c.relname in ('at_reb_plain_expr', 'at_reb_plain_u') and a.attnum > 0
+ order by c.relname, a.attnum;
+drop table at_reb_plain;
+
-- disallow recursive containment of row types
create temp table recur1 (f1 int);
alter table recur1 add column f2 recur1; -- fails
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 6c366d3a523..6a464f80de1 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1350,6 +1350,7 @@ IndexScanDesc
IndexScanDescData
IndexScanInstrumentation
IndexScanState
+IndexStatTarget
IndexStateFlagsAction
IndexStmt
IndexTuple
--
2.47.3
From a13ea4c768ec9233d0eede3e197d165c58957f28 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <[email protected]>
Date: Fri, 21 Aug 2026 13:01:21 -0400
Subject: [PATCH v14 2/2] Preserve leaf partition index properties across ALTER
COLUMN TYPE
When ALTER TABLE ... ALTER COLUMN TYPE (or SET EXPRESSION) rebuilds a
partitioned index, it drops each old leaf partition index and recreates it
by cloning the parent index's structure. The old leaf index's catalog rows
are deleted, and the clone reproduces only the DDL-expressible structure, so
the leaf index lost its non-DDL properties: custom name, comment, replica
identity, cluster-on marking, and any reloptions set independently of the
parent (e.g. ALTER INDEX ... SET (fillfactor=...)).
A leaf index created directly on the partition or a plain table's index kept
these via the existing top-level remember/restore path; only indexes
descended from a partitioned index via CREATE INDEX ON parent or ATTACH were
affected.
Capture each old leaf index's non-DDL properties before the drop, transfer
the matching entry onto each child IndexStmt during the partition recursion,
and re-apply them after creating the new index and its catalog tuples.
---
src/backend/commands/indexcmds.c | 106 ++++++++++++++++++++++
src/backend/commands/tablecmds.c | 88 ++++++++++++++++++
src/include/nodes/parsenodes.h | 33 +++++++
src/test/regress/expected/alter_table.out | 75 ++++++++++++++-
src/test/regress/sql/alter_table.sql | 71 +++++++++++++++
src/tools/pgindent/typedefs.list | 1 +
6 files changed, 370 insertions(+), 4 deletions(-)
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 4e592ea7785..79025f61d12 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -106,6 +106,8 @@ static char *ChooseIndexName(const char *tabname, Oid namespaceId,
const List *colnames, const List *exclusionOpNames,
bool primary, bool isconstraint);
static char *ChooseIndexNameAddition(const List *colnames);
+static void TransferPartitionIndexProps(const IndexStmt *stmt, Oid childRelid,
+ IndexStmt *childStmt);
static List *ChooseIndexColumnNames(Relation rel, const List *indexElems);
static char *ChooseIndexExpressionName(Relation rel, Node *indexExpr);
static bool ChooseIndexExpressionName_walker(Node *node,
@@ -551,6 +553,46 @@ SetIndexStatTargets(Oid indexRelationId, List *stattargets)
}
+/*
+ * Copy this partition's non-DDL properties (name, comment, replica identity,
+ * cluster-on, per-column stats targets) from the list of all leaf partitions'
+ * properties into the correct scalar fields in the newly created IndexStmt
+ * for the child partition index as part of an ALTER COLUMN TYPE (or SET
+ * EXPRESSION) operation.
+ *
+ * These were saved in a list before dropping the index in an earlier phase.
+ * After we create the child partition index, we'll update the catalog table
+ * entries according to these values.
+ */
+static void
+TransferPartitionIndexProps(const IndexStmt *stmt, Oid childRelid,
+ IndexStmt *childStmt)
+{
+ foreach_node(PartitionIndexProps, props, stmt->oldPartIndexProps)
+ {
+ /*
+ * Match entries by the partition table's OID (stable), since the old
+ * index's OID is gone.
+ */
+ if (props->partrelid != childRelid)
+ continue;
+
+ childStmt->idxname = pstrdup(props->idxname);
+ childStmt->idxcomment = props->idxcomment;
+ childStmt->idxisreplident = props->isreplident;
+ childStmt->idxisclustered = props->isclustered;
+ childStmt->idxstattargets = props->stattargets;
+
+ /*
+ * Override the parent's reloptions with the leaf's own, so
+ * independently-set leaf options survive.
+ */
+ childStmt->options = props->reloptions;
+ return;
+ }
+}
+
+
/*
* DefineIndex
* Creates a new index.
@@ -1372,6 +1414,58 @@ DefineIndex(ParseState *pstate,
if (stmt->idxstattargets != NIL)
SetIndexStatTargets(indexRelationId, stmt->idxstattargets);
+ /*
+ * We set indisclustered/indisreplident with a direct single-row pg_index
+ * update, not mark_index_clustered()/relation_mark_replica_identity()
+ * because those clear the flag on sibling indexes which are mid-drop
+ * here. That's safe to skip because at most one index per table carries
+ * each flag and it is this newly-created one.
+ */
+ if (stmt->idxisclustered || stmt->idxisreplident)
+ {
+ Relation pg_index;
+ HeapTuple idxtuple;
+ Form_pg_index indexForm;
+
+ pg_index = table_open(IndexRelationId, RowExclusiveLock);
+ idxtuple = SearchSysCacheCopy1(INDEXRELID,
+ ObjectIdGetDatum(indexRelationId));
+ if (!HeapTupleIsValid(idxtuple))
+ elog(ERROR, "cache lookup failed for index %u", indexRelationId);
+ indexForm = (Form_pg_index) GETSTRUCT(idxtuple);
+
+ if (stmt->idxisclustered)
+ indexForm->indisclustered = true;
+ if (stmt->idxisreplident)
+ indexForm->indisreplident = true;
+
+ CatalogTupleUpdate(pg_index, &idxtuple->t_self, idxtuple);
+ heap_freetuple(idxtuple);
+ table_close(pg_index, RowExclusiveLock);
+ }
+
+ /* Replica identity also marks the owning table's relreplident. */
+ if (stmt->idxisreplident)
+ {
+ Relation pg_class;
+ Oid heapId = IndexGetRelation(indexRelationId, false);
+ HeapTuple ctup;
+ Form_pg_class classForm;
+
+ pg_class = table_open(RelationRelationId, RowExclusiveLock);
+ ctup = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(heapId));
+ if (!HeapTupleIsValid(ctup))
+ elog(ERROR, "cache lookup failed for relation %u", heapId);
+ classForm = (Form_pg_class) GETSTRUCT(ctup);
+ if (classForm->relreplident != REPLICA_IDENTITY_INDEX)
+ {
+ classForm->relreplident = REPLICA_IDENTITY_INDEX;
+ CatalogTupleUpdate(pg_class, &ctup->t_self, ctup);
+ }
+ heap_freetuple(ctup);
+ table_close(pg_class, RowExclusiveLock);
+ }
+
if (partitioned)
{
PartitionDesc partdesc;
@@ -1594,6 +1688,18 @@ DefineIndex(ParseState *pstate,
attmap,
NULL);
+ /*
+ * generateClonedIndexStmt() only clones DDL-expressible
+ * properties, so transfer the old leaf index's non-DDL
+ * properties into the child IndexStmt. The child also
+ * needs a pointer to the list in case it is an
+ * intermediate partitioned index and needs its own
+ * children to be able to find their entries upon
+ * recursing.
+ */
+ childStmt->oldPartIndexProps = stmt->oldPartIndexProps;
+ TransferPartitionIndexProps(stmt, childRelid, childStmt);
+
/*
* Recurse as the starting user ID. Callee will use that
* for permission checks, then switch again.
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index f9b1b0aa82d..e5fe941e632 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -707,6 +707,7 @@ static void RebuildConstraintComment(AlteredTableInfo *tab, AlterTablePass pass,
Oid objid, Relation rel, List *domname,
const char *conname);
static void TryReuseIndex(Oid oldId, IndexStmt *stmt);
+static void RememberPartitionIndexProps(Oid indoid, IndexStmt *stmt);
static List *GetIndexStatTargets(Oid indexOid);
static void TryReuseForeignKey(Oid oldId, Constraint *con);
static ObjectAddress ATExecAlterColumnGenericOptions(Relation rel, const char *colName,
@@ -15981,6 +15982,13 @@ RememberWholeRowDependentForRebuilding(AlteredTableInfo *tab, AlterTableType sub
/*
* Subroutine for ATExecAlterColumnType: remember that a replica identity
* needs to be reset.
+ *
+ * We save the index by name and restore it later via an AT_ReplicaIdentity
+ * subcommand (see ATPostAlterTypeCleanup), rather than stamping
+ * indisreplident at creation like the partition-leaf path. The top-level
+ * index may not be rebuilt at all, or may have live siblings whose flag must
+ * be cleared, so it needs the relation-wide relation_mark_replica_identity()
+ * run after the rebuild.
*/
static void
RememberReplicaIdentityForRebuilding(Oid indoid, AlteredTableInfo *tab)
@@ -16435,6 +16443,8 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, Oid ownerId,
IndexStmt *stmt = (IndexStmt *) stm;
AlterTableCmd *newcmd;
+ /* capture leaf indexes' non-DDL properties before they're dropped */
+ RememberPartitionIndexProps(oldId, stmt);
if (!rewrite)
TryReuseIndex(oldId, stmt);
stmt->reset_default_tblspc = true;
@@ -16466,6 +16476,7 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, Oid ownerId,
indstmt = castNode(IndexStmt, cmd->def);
indoid = get_constraint_index(oldId);
+ RememberPartitionIndexProps(indoid, indstmt);
if (!rewrite)
TryReuseIndex(indoid, indstmt);
/* keep any comment on the index */
@@ -16620,6 +16631,83 @@ RebuildConstraintComment(AlteredTableInfo *tab, AlterTablePass pass, Oid objid,
tab->subcmds[pass] = lappend(tab->subcmds[pass], newcmd);
}
+/*
+ * Before a partitioned index is dropped and rebuilt as part of an ALTER
+ * COLUMN TYPE (or SET EXPRESSION), capture each of its leaf partition indexes'
+ * non-DDL properties (name, comment, replica identity, cluster-on, per-column
+ * stat targets) into a list saved on the parent index's IndexStmt. These are
+ * not reproduced by the CREATE INDEX round-trip and would otherwise be lost
+ * when recreating the leaf index.
+ *
+ * Must run before the drop, while the old leaf indexes still exist.
+ */
+static void
+RememberPartitionIndexProps(Oid indoid, IndexStmt *stmt)
+{
+ List *indexOids;
+
+ if (get_rel_relkind(indoid) != RELKIND_PARTITIONED_INDEX)
+ return;
+
+ indexOids = find_all_inheritors(indoid, NoLock, NULL);
+ foreach_oid(leafIndexOid, indexOids)
+ {
+ PartitionIndexProps *props;
+ HeapTuple idxtup;
+ HeapTuple classtup;
+ Form_pg_index idxform;
+ Form_pg_class classform;
+ Datum reloptions;
+ bool rel_isnull;
+
+ if (leafIndexOid == indoid)
+ continue;
+
+ idxtup = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(leafIndexOid));
+ if (!HeapTupleIsValid(idxtup))
+ continue;
+ idxform = (Form_pg_index) GETSTRUCT(idxtup);
+
+ classtup = SearchSysCache1(RELOID, ObjectIdGetDatum(leafIndexOid));
+ if (!HeapTupleIsValid(classtup))
+ {
+ ReleaseSysCache(idxtup);
+ continue;
+ }
+ classform = (Form_pg_class) GETSTRUCT(classtup);
+
+ /* only direct/indirect leaf (storage) indexes carry these props */
+ if (classform->relkind == RELKIND_PARTITIONED_INDEX)
+ {
+ ReleaseSysCache(classtup);
+ ReleaseSysCache(idxtup);
+ continue;
+ }
+
+ props = makeNode(PartitionIndexProps);
+ props->partrelid = idxform->indrelid;
+ props->idxname = pstrdup(NameStr(classform->relname));
+ props->idxcomment = GetComment(leafIndexOid, RelationRelationId, 0);
+ props->isreplident = idxform->indisreplident;
+ props->isclustered = idxform->indisclustered;
+ props->stattargets = NIL;
+ props->reloptions = NIL;
+
+ reloptions = SysCacheGetAttr(RELOID, classtup,
+ Anum_pg_class_reloptions, &rel_isnull);
+ if (!rel_isnull)
+ props->reloptions = untransformRelOptions(reloptions);
+
+ ReleaseSysCache(classtup);
+ ReleaseSysCache(idxtup);
+
+ props->stattargets = GetIndexStatTargets(leafIndexOid);
+
+ stmt->oldPartIndexProps = lappend(stmt->oldPartIndexProps, props);
+ }
+ list_free(indexOids);
+}
+
/*
* Collect the per-column statistics targets of an index into a list of
* IndexStatTarget nodes. Returns NIL if none are set.
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 84f7f0e7d3b..eddafc9920e 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3664,8 +3664,21 @@ typedef struct IndexStmt
* index to the new and must be explicitly saved before dropping the old
* index and restored after creating the new index.
*/
+ bool idxisreplident; /* restore this index as REPLICA IDENTITY */
+ bool idxisclustered; /* restore CLUSTER ON this index */
char *idxcomment; /* comment to apply to index, or NULL */
List *idxstattargets; /* list of IndexStatTarget to restore */
+
+ /*
+ * For a partitioned index, oldPartIndexProps holds one entry per old leaf
+ * index (across all partition levels). The whole list is propagated to
+ * every IndexStmt in DefineIndex()'s recursion so that intermediate
+ * levels can still find deeper leaves' entries. Each stmt then copies its
+ * own matching entry into the corresponding scalar fields in the
+ * IndexStmt (see TransferPartitionIndexProps).
+ */
+ List *oldPartIndexProps; /* list of PartitionIndexProps
+ * (partitioned index rebuild only) */
} IndexStmt;
/* one per-column statistics target carried across an index rebuild */
@@ -3678,6 +3691,26 @@ typedef struct IndexStatTarget
int stattarget; /* attstattarget value to restore */
} IndexStatTarget;
+/*
+ * Non-DDL properties of one old leaf partition index, captured before it is
+ * dropped during ALTER COLUMN TYPE or SET EXPRESSION so they can be re-applied
+ * to the rebuilt child index.
+ */
+typedef struct PartitionIndexProps
+{
+ pg_node_attr(no_equal, no_query_jumble)
+
+ NodeTag type;
+ Oid partrelid; /* partition table owning this index */
+ char *idxname; /* index name to restore */
+ char *idxcomment; /* comment, or NULL */
+ bool isreplident; /* was replica identity */
+ bool isclustered; /* was clustered on */
+ List *stattargets; /* list of IndexStatTarget */
+ List *reloptions; /* index's own reloptions (untransformed
+ * DefElem list), or NIL */
+} PartitionIndexProps;
+
/* ----------------------
* Create Statistics Statement
* ----------------------
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 8b6d85461f9..bcca6499d63 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -2304,13 +2304,13 @@ select relname,
from pg_class c left join old_oids using (relname)
where relname like 'at_partitioned%'
order by relname;
- relname | orig_oid | storage | desc
-------------------------------+----------+---------+--------------
+ relname | orig_oid | storage | desc
+------------------------------+----------+---------+---------------
at_partitioned | t | none |
at_partitioned_0 | t | own |
- at_partitioned_0_id_name_key | f | own |
+ at_partitioned_0_id_name_key | f | own | child 0 index
at_partitioned_1 | t | own |
- at_partitioned_1_id_name_key | f | own |
+ at_partitioned_1_id_name_key | f | own | child 1 index
at_partitioned_id_name_key | f | none | parent index
(6 rows)
@@ -2346,6 +2346,73 @@ select c.relname, a.attnum, a.attstattarget
(3 rows)
drop table at_reb_plain;
+-- Partitioned tables' leaf partitions should all preserve their properties
+-- across an ALTER COLUMN TYPE-triggered rebuild.
+create table at_reb (id int not null, val int not null) partition by range (id);
+create table at_reb_1 partition of at_reb for values from (0) to (100);
+create index at_reb_expr on at_reb ((val + 1)) with (fillfactor = 71);
+create unique index at_reb_uniq on at_reb (id, val);
+-- give the leaf indexes stable, custom names (also exercises name preservation)
+alter index at_reb_1_val_1_idx rename to at_reb_expr_leaf;
+alter index at_reb_1_id_val_idx rename to at_reb_uniq_leaf;
+-- load the leaf indexes with every non-DDL property
+comment on index at_reb_expr_leaf is 'leaf expr index comment';
+alter index at_reb_expr_leaf alter column 1 set statistics 543;
+alter index at_reb_expr_leaf set (fillfactor = 55);
+alter table at_reb_1 replica identity using index at_reb_uniq_leaf;
+alter table at_reb_1 cluster on at_reb_expr_leaf;
+-- Snapshot each catalog row describing the two leaf indexes as jsonb. Remove
+-- the columns that legitimately differ across the rebuild: the physical
+-- identity (oid, relfilenode, and their indexrelid/indrelid/attrelid echoes),
+-- and the size estimates (relpages, reltuples, relallvisible, relallfrozen),
+-- which are recomputed by the fresh index build rather than carried over. Any
+-- other difference fails the test.
+create function at_reb_snapshot() returns table(cat text, disc text, body jsonb)
+language sql stable as $$
+ select 'pg_class', c.relname,
+ to_jsonb(c) - '{oid,relfilenode,relpages,reltuples,relallvisible,
+ relallfrozen}'::text[]
+ from pg_class c
+ where c.relname in ('at_reb_expr_leaf', 'at_reb_uniq_leaf')
+ union all
+ select 'pg_index', c.relname,
+ to_jsonb(i) - '{indexrelid,indrelid}'::text[]
+ from pg_index i join pg_class c on c.oid = i.indexrelid
+ where c.relname in ('at_reb_expr_leaf', 'at_reb_uniq_leaf')
+ union all
+ select 'pg_attribute', c.relname || '.' || a.attnum,
+ to_jsonb(a) - '{attrelid}'::text[]
+ from pg_attribute a join pg_class c on c.oid = a.attrelid
+ where c.relname in ('at_reb_expr_leaf', 'at_reb_uniq_leaf')
+ and a.attnum > 0
+ union all
+ select 'comment', c.relname, to_jsonb(obj_description(c.oid, 'pg_class'))
+ from pg_class c
+ where c.relname in ('at_reb_expr_leaf', 'at_reb_uniq_leaf')
+ union all
+ -- owning table row, for relreplident (replica identity is recorded here too)
+ select 'pg_class_owner', tc.relname, to_jsonb(tc.relreplident)
+ from pg_index i join pg_class c on c.oid = i.indexrelid
+ join pg_class tc on tc.oid = i.indrelid
+ where c.relname in ('at_reb_expr_leaf', 'at_reb_uniq_leaf');
+$$;
+create temp table at_reb_before as select * from at_reb_snapshot();
+-- No-op type change: same type, but still rebuilds the leaf indexes.
+alter table at_reb alter column val type int;
+create temp table at_reb_after as select * from at_reb_snapshot();
+-- Both directions must be empty: nothing lost, nothing unexpectedly changed.
+select 'lost/changed' as dir, cat, disc from (
+ select * from at_reb_before except select * from at_reb_after) d
+union all
+select 'appeared/changed', cat, disc from (
+ select * from at_reb_after except select * from at_reb_before) d
+order by 1, 2, 3;
+ dir | cat | disc
+-----+-----+------
+(0 rows)
+
+drop function at_reb_snapshot();
+drop table at_reb;
-- disallow recursive containment of row types
create temp table recur1 (f1 int);
alter table recur1 add column f2 recur1; -- fails
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 8bdfc4262ef..017e20a4436 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -1545,6 +1545,77 @@ select c.relname, a.attnum, a.attstattarget
order by c.relname, a.attnum;
drop table at_reb_plain;
+-- Partitioned tables' leaf partitions should all preserve their properties
+-- across an ALTER COLUMN TYPE-triggered rebuild.
+create table at_reb (id int not null, val int not null) partition by range (id);
+create table at_reb_1 partition of at_reb for values from (0) to (100);
+create index at_reb_expr on at_reb ((val + 1)) with (fillfactor = 71);
+create unique index at_reb_uniq on at_reb (id, val);
+-- give the leaf indexes stable, custom names (also exercises name preservation)
+alter index at_reb_1_val_1_idx rename to at_reb_expr_leaf;
+alter index at_reb_1_id_val_idx rename to at_reb_uniq_leaf;
+
+-- load the leaf indexes with every non-DDL property
+comment on index at_reb_expr_leaf is 'leaf expr index comment';
+alter index at_reb_expr_leaf alter column 1 set statistics 543;
+alter index at_reb_expr_leaf set (fillfactor = 55);
+alter table at_reb_1 replica identity using index at_reb_uniq_leaf;
+alter table at_reb_1 cluster on at_reb_expr_leaf;
+
+-- Snapshot each catalog row describing the two leaf indexes as jsonb. Remove
+-- the columns that legitimately differ across the rebuild: the physical
+-- identity (oid, relfilenode, and their indexrelid/indrelid/attrelid echoes),
+-- and the size estimates (relpages, reltuples, relallvisible, relallfrozen),
+-- which are recomputed by the fresh index build rather than carried over. Any
+-- other difference fails the test.
+create function at_reb_snapshot() returns table(cat text, disc text, body jsonb)
+language sql stable as $$
+ select 'pg_class', c.relname,
+ to_jsonb(c) - '{oid,relfilenode,relpages,reltuples,relallvisible,
+ relallfrozen}'::text[]
+ from pg_class c
+ where c.relname in ('at_reb_expr_leaf', 'at_reb_uniq_leaf')
+ union all
+ select 'pg_index', c.relname,
+ to_jsonb(i) - '{indexrelid,indrelid}'::text[]
+ from pg_index i join pg_class c on c.oid = i.indexrelid
+ where c.relname in ('at_reb_expr_leaf', 'at_reb_uniq_leaf')
+ union all
+ select 'pg_attribute', c.relname || '.' || a.attnum,
+ to_jsonb(a) - '{attrelid}'::text[]
+ from pg_attribute a join pg_class c on c.oid = a.attrelid
+ where c.relname in ('at_reb_expr_leaf', 'at_reb_uniq_leaf')
+ and a.attnum > 0
+ union all
+ select 'comment', c.relname, to_jsonb(obj_description(c.oid, 'pg_class'))
+ from pg_class c
+ where c.relname in ('at_reb_expr_leaf', 'at_reb_uniq_leaf')
+ union all
+ -- owning table row, for relreplident (replica identity is recorded here too)
+ select 'pg_class_owner', tc.relname, to_jsonb(tc.relreplident)
+ from pg_index i join pg_class c on c.oid = i.indexrelid
+ join pg_class tc on tc.oid = i.indrelid
+ where c.relname in ('at_reb_expr_leaf', 'at_reb_uniq_leaf');
+$$;
+
+create temp table at_reb_before as select * from at_reb_snapshot();
+
+-- No-op type change: same type, but still rebuilds the leaf indexes.
+alter table at_reb alter column val type int;
+
+create temp table at_reb_after as select * from at_reb_snapshot();
+
+-- Both directions must be empty: nothing lost, nothing unexpectedly changed.
+select 'lost/changed' as dir, cat, disc from (
+ select * from at_reb_before except select * from at_reb_after) d
+union all
+select 'appeared/changed', cat, disc from (
+ select * from at_reb_after except select * from at_reb_before) d
+order by 1, 2, 3;
+
+drop function at_reb_snapshot();
+drop table at_reb;
+
-- disallow recursive containment of row types
create temp table recur1 (f1 int);
alter table recur1 add column f2 recur1; -- fails
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 6a464f80de1..14a5b66f579 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2218,6 +2218,7 @@ PartitionDispatch
PartitionElem
PartitionHashBound
PartitionIndexExtDepEntry
+PartitionIndexProps
PartitionKey
PartitionListValue
PartitionMap
--
2.47.3
