Skip to content

Commit cef2b8d

Browse files
michaelpqmichaelpq
committed
Preserve clustered index after rewrites with ALTER TABLE
A table rewritten by ALTER TABLE would lose tracking of an index usable for CLUSTER. This setting is tracked by pg_index.indisclustered and is controlled by ALTER TABLE, so some extra work was needed to restore it properly. Note that ALTER TABLE only marks the index that can be used for clustering, and does not do the actual operation. Author: Amit Langote, Justin Pryzby Reviewed-by: Ibrar Ahmed, Michael Paquier Discussion: https://postgr.es/m/20200202161718.GI13621@telsasoft.com Backpatch-through: 9.5
1 parent 0e708d3 commit cef2b8d

File tree

5 files changed

+143
-0
lines changed

5 files changed

+143
-0
lines changed

‎src/backend/commands/tablecmds.c

Lines changed: 46 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -174,6 +174,7 @@ typedef struct AlteredTableInfo
174174
List *changedIndexOids; /* OIDs of indexes to rebuild */
175175
List *changedIndexDefs; /* string definitions of same */
176176
char *replicaIdentityIndex; /* index to reset as REPLICA IDENTITY */
177+
char *clusterOnIndex; /* index to use for CLUSTER */
177178
} AlteredTableInfo;
178179

179180
/* Struct describing one new constraint to check in Phase 3 scan */
@@ -9437,6 +9438,21 @@ RememberReplicaIdentityForRebuilding(Oid indoid, AlteredTableInfo *tab)
94379438
tab->replicaIdentityIndex = get_rel_name(indoid);
94389439
}
94399440

9441+
/*
9442+
* Subroutine for ATExecAlterColumnType: remember any clustered index.
9443+
*/
9444+
static void
9445+
RememberClusterOnForRebuilding(Oid indoid, AlteredTableInfo *tab)
9446+
{
9447+
if (!get_index_isclustered(indoid))
9448+
return;
9449+
9450+
if (tab->clusterOnIndex)
9451+
elog(ERROR, "relation %u has multiple clustered indexes", tab->relid);
9452+
9453+
tab->clusterOnIndex = get_rel_name(indoid);
9454+
}
9455+
94409456
/*
94419457
* Subroutine for ATExecAlterColumnType: remember that a constraint needs
94429458
* to be rebuilt (which we might already know).
@@ -9481,9 +9497,18 @@ RememberConstraintForRebuilding(Oid conoid, AlteredTableInfo *tab,
94819497
defstring);
94829498
}
94839499

9500+
/*
9501+
* For the index of a constraint, if any, remember if it is used for
9502+
* the table's replica identity or if it is a clustered index, so that
9503+
* ATPostAlterTypeCleanup() can queue up commands necessary to restore
9504+
* those properties.
9505+
*/
94849506
indoid = get_constraint_index(conoid);
94859507
if (OidIsValid(indoid))
9508+
{
94869509
RememberReplicaIdentityForRebuilding(indoid, tab);
9510+
RememberClusterOnForRebuilding(indoid, tab);
9511+
}
94879512
}
94889513
}
94899514

@@ -9528,7 +9553,13 @@ RememberIndexForRebuilding(Oid indoid, AlteredTableInfo *tab)
95289553
tab->changedIndexDefs = lappend(tab->changedIndexDefs,
95299554
defstring);
95309555

9556+
/*
9557+
* Remember if this index is used for the table's replica identity
9558+
* or if it is a clustered index, so that ATPostAlterTypeCleanup()
9559+
* can queue up commands necessary to restore those properties.
9560+
*/
95319561
RememberReplicaIdentityForRebuilding(indoid, tab);
9562+
RememberClusterOnForRebuilding(indoid, tab);
95329563
}
95339564
}
95349565
}
@@ -9739,6 +9770,21 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
97399770
lappend(tab->subcmds[AT_PASS_OLD_CONSTR], cmd);
97409771
}
97419772

9773+
/*
9774+
* Queue up command to restore marking of index used for cluster.
9775+
*/
9776+
if (tab->clusterOnIndex)
9777+
{
9778+
AlterTableCmd *cmd = makeNode(AlterTableCmd);
9779+
9780+
cmd->subtype = AT_ClusterOn;
9781+
cmd->name = tab->clusterOnIndex;
9782+
9783+
/* do it after indexes and constraints */
9784+
tab->subcmds[AT_PASS_OLD_CONSTR] =
9785+
lappend(tab->subcmds[AT_PASS_OLD_CONSTR], cmd);
9786+
}
9787+
97429788
/*
97439789
* Now we can drop the existing constraints and indexes --- constraints
97449790
* first, since some of them might depend on the indexes. In fact, we

‎src/backend/utils/cache/lsyscache.c

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3163,3 +3163,26 @@ get_index_isreplident(Oid index_oid)
31633163

31643164
return result;
31653165
}
3166+
3167+
/*
3168+
* get_index_isclustered
3169+
*
3170+
* Given the index OID, return pg_index.indisclustered.
3171+
*/
3172+
bool
3173+
get_index_isclustered(Oid index_oid)
3174+
{
3175+
bool isclustered;
3176+
HeapTuple tuple;
3177+
Form_pg_index rd_index;
3178+
3179+
tuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(index_oid));
3180+
if (!HeapTupleIsValid(tuple))
3181+
elog(ERROR, "cache lookup failed for index %u", index_oid);
3182+
3183+
rd_index = (Form_pg_index) GETSTRUCT(tuple);
3184+
isclustered = rd_index->indisclustered;
3185+
ReleaseSysCache(tuple);
3186+
3187+
return isclustered;
3188+
}

‎src/include/utils/lsyscache.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -179,6 +179,7 @@ extern char *get_namespace_name_or_temp(Oid nspid);
179179
extern Oid get_range_subtype(Oid rangeOid);
180180
extern Oid get_range_collation(Oid rangeOid);
181181
extern bool get_index_isreplident(Oid index_oid);
182+
extern bool get_index_isclustered(Oid index_oid);
182183

183184
#define type_is_array(typid) (get_element_type(typid) != InvalidOid)
184185
/* type_is_array_domain accepts both plain arrays and domains over arrays */

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

Lines changed: 48 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3788,3 +3788,51 @@ alter table at_test_sql_partop attach partition at_test_sql_partop_1 for values
37883788
drop table at_test_sql_partop;
37893789
drop operator class at_test_sql_partop using btree;
37903790
drop function at_test_sql_partop;
3791+
-- Test that ALTER TABLE rewrite preserves a clustered index
3792+
-- for normal indexes and indexes on constraints.
3793+
create table alttype_cluster (a int);
3794+
alter table alttype_cluster add primary key (a);
3795+
create index alttype_cluster_ind on alttype_cluster (a);
3796+
alter table alttype_cluster cluster on alttype_cluster_ind;
3797+
-- Normal index remains clustered.
3798+
select indexrelid::regclass, indisclustered from pg_index
3799+
where indrelid = 'alttype_cluster'::regclass
3800+
order by indexrelid::regclass::text;
3801+
indexrelid | indisclustered
3802+
----------------------+----------------
3803+
alttype_cluster_ind | t
3804+
alttype_cluster_pkey | f
3805+
(2 rows)
3806+
3807+
alter table alttype_cluster alter a type bigint;
3808+
select indexrelid::regclass, indisclustered from pg_index
3809+
where indrelid = 'alttype_cluster'::regclass
3810+
order by indexrelid::regclass::text;
3811+
indexrelid | indisclustered
3812+
----------------------+----------------
3813+
alttype_cluster_ind | t
3814+
alttype_cluster_pkey | f
3815+
(2 rows)
3816+
3817+
-- Constraint index remains clustered.
3818+
alter table alttype_cluster cluster on alttype_cluster_pkey;
3819+
select indexrelid::regclass, indisclustered from pg_index
3820+
where indrelid = 'alttype_cluster'::regclass
3821+
order by indexrelid::regclass::text;
3822+
indexrelid | indisclustered
3823+
----------------------+----------------
3824+
alttype_cluster_ind | f
3825+
alttype_cluster_pkey | t
3826+
(2 rows)
3827+
3828+
alter table alttype_cluster alter a type int;
3829+
select indexrelid::regclass, indisclustered from pg_index
3830+
where indrelid = 'alttype_cluster'::regclass
3831+
order by indexrelid::regclass::text;
3832+
indexrelid | indisclustered
3833+
----------------------+----------------
3834+
alttype_cluster_ind | f
3835+
alttype_cluster_pkey | t
3836+
(2 rows)
3837+
3838+
drop table alttype_cluster;

‎src/test/regress/sql/alter_table.sql

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2463,3 +2463,28 @@ alter table at_test_sql_partop attach partition at_test_sql_partop_1 for values
24632463
drop table at_test_sql_partop;
24642464
drop operator class at_test_sql_partop using btree;
24652465
drop function at_test_sql_partop;
2466+
2467+
-- Test that ALTER TABLE rewrite preserves a clustered index
2468+
-- for normal indexes and indexes on constraints.
2469+
create table alttype_cluster (a int);
2470+
alter table alttype_cluster add primary key (a);
2471+
create index alttype_cluster_ind on alttype_cluster (a);
2472+
alter table alttype_cluster cluster on alttype_cluster_ind;
2473+
-- Normal index remains clustered.
2474+
select indexrelid::regclass, indisclustered from pg_index
2475+
where indrelid = 'alttype_cluster'::regclass
2476+
order by indexrelid::regclass::text;
2477+
alter table alttype_cluster alter a type bigint;
2478+
select indexrelid::regclass, indisclustered from pg_index
2479+
where indrelid = 'alttype_cluster'::regclass
2480+
order by indexrelid::regclass::text;
2481+
-- Constraint index remains clustered.
2482+
alter table alttype_cluster cluster on alttype_cluster_pkey;
2483+
select indexrelid::regclass, indisclustered from pg_index
2484+
where indrelid = 'alttype_cluster'::regclass
2485+
order by indexrelid::regclass::text;
2486+
alter table alttype_cluster alter a type int;
2487+
select indexrelid::regclass, indisclustered from pg_index
2488+
where indrelid = 'alttype_cluster'::regclass
2489+
order by indexrelid::regclass::text;
2490+
drop table alttype_cluster;

0 commit comments

Comments
 (0)