Skip to content

Commit 3e62dd3

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 23a0cf2 commit 3e62dd3

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
@@ -175,6 +175,7 @@ typedef struct AlteredTableInfo
175175
List *changedIndexOids; /* OIDs of indexes to rebuild */
176176
List *changedIndexDefs; /* string definitions of same */
177177
char *replicaIdentityIndex; /* index to reset as REPLICA IDENTITY */
178+
char *clusterOnIndex; /* index to use for CLUSTER */
178179
} AlteredTableInfo;
179180

180181
/* Struct describing one new constraint to check in Phase 3 scan */
@@ -11155,6 +11156,21 @@ RememberReplicaIdentityForRebuilding(Oid indoid, AlteredTableInfo *tab)
1115511156
tab->replicaIdentityIndex = get_rel_name(indoid);
1115611157
}
1115711158

11159+
/*
11160+
* Subroutine for ATExecAlterColumnType: remember any clustered index.
11161+
*/
11162+
static void
11163+
RememberClusterOnForRebuilding(Oid indoid, AlteredTableInfo *tab)
11164+
{
11165+
if (!get_index_isclustered(indoid))
11166+
return;
11167+
11168+
if (tab->clusterOnIndex)
11169+
elog(ERROR, "relation %u has multiple clustered indexes", tab->relid);
11170+
11171+
tab->clusterOnIndex = get_rel_name(indoid);
11172+
}
11173+
1115811174
/*
1115911175
* Subroutine for ATExecAlterColumnType: remember that a constraint needs
1116011176
* to be rebuilt (which we might already know).
@@ -11180,9 +11196,18 @@ RememberConstraintForRebuilding(Oid conoid, AlteredTableInfo *tab)
1118011196
tab->changedConstraintDefs = lappend(tab->changedConstraintDefs,
1118111197
defstring);
1118211198

11199+
/*
11200+
* For the index of a constraint, if any, remember if it is used for
11201+
* the table's replica identity or if it is a clustered index, so that
11202+
* ATPostAlterTypeCleanup() can queue up commands necessary to restore
11203+
* those properties.
11204+
*/
1118311205
indoid = get_constraint_index(conoid);
1118411206
if (OidIsValid(indoid))
11207+
{
1118511208
RememberReplicaIdentityForRebuilding(indoid, tab);
11209+
RememberClusterOnForRebuilding(indoid, tab);
11210+
}
1118611211
}
1118711212
}
1118811213

@@ -11226,7 +11251,13 @@ RememberIndexForRebuilding(Oid indoid, AlteredTableInfo *tab)
1122611251
tab->changedIndexDefs = lappend(tab->changedIndexDefs,
1122711252
defstring);
1122811253

11254+
/*
11255+
* Remember if this index is used for the table's replica identity
11256+
* or if it is a clustered index, so that ATPostAlterTypeCleanup()
11257+
* can queue up commands necessary to restore those properties.
11258+
*/
1122911259
RememberReplicaIdentityForRebuilding(indoid, tab);
11260+
RememberClusterOnForRebuilding(indoid, tab);
1123011261
}
1123111262
}
1123211263
}
@@ -11353,6 +11384,21 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
1135311384
lappend(tab->subcmds[AT_PASS_OLD_CONSTR], cmd);
1135411385
}
1135511386

11387+
/*
11388+
* Queue up command to restore marking of index used for cluster.
11389+
*/
11390+
if (tab->clusterOnIndex)
11391+
{
11392+
AlterTableCmd *cmd = makeNode(AlterTableCmd);
11393+
11394+
cmd->subtype = AT_ClusterOn;
11395+
cmd->name = tab->clusterOnIndex;
11396+
11397+
/* do it after indexes and constraints */
11398+
tab->subcmds[AT_PASS_OLD_CONSTR] =
11399+
lappend(tab->subcmds[AT_PASS_OLD_CONSTR], cmd);
11400+
}
11401+
1135611402
/*
1135711403
* It should be okay to use DROP_RESTRICT here, since nothing else should
1135811404
* be depending on these objects.

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

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3273,3 +3273,26 @@ get_index_isvalid(Oid index_oid)
32733273

32743274
return isvalid;
32753275
}
3276+
3277+
/*
3278+
* get_index_isclustered
3279+
*
3280+
* Given the index OID, return pg_index.indisclustered.
3281+
*/
3282+
bool
3283+
get_index_isclustered(Oid index_oid)
3284+
{
3285+
bool isclustered;
3286+
HeapTuple tuple;
3287+
Form_pg_index rd_index;
3288+
3289+
tuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(index_oid));
3290+
if (!HeapTupleIsValid(tuple))
3291+
elog(ERROR, "cache lookup failed for index %u", index_oid);
3292+
3293+
rd_index = (Form_pg_index) GETSTRUCT(tuple);
3294+
isclustered = rd_index->indisclustered;
3295+
ReleaseSysCache(tuple);
3296+
3297+
return isclustered;
3298+
}

‎src/include/utils/lsyscache.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -183,6 +183,7 @@ extern Oid get_range_collation(Oid rangeOid);
183183
extern Oid get_index_column_opclass(Oid index_oid, int attno);
184184
extern bool get_index_isreplident(Oid index_oid);
185185
extern bool get_index_isvalid(Oid index_oid);
186+
extern bool get_index_isclustered(Oid index_oid);
186187

187188
#define type_is_array(typid) (get_element_type(typid) != InvalidOid)
188189
/* 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
@@ -4186,3 +4186,51 @@ create trigger xtrig
41864186
update bar1 set a = a + 1;
41874187
INFO: a=1, b=1
41884188
/* End test case for bug #16242 */
4189+
-- Test that ALTER TABLE rewrite preserves a clustered index
4190+
-- for normal indexes and indexes on constraints.
4191+
create table alttype_cluster (a int);
4192+
alter table alttype_cluster add primary key (a);
4193+
create index alttype_cluster_ind on alttype_cluster (a);
4194+
alter table alttype_cluster cluster on alttype_cluster_ind;
4195+
-- Normal index remains clustered.
4196+
select indexrelid::regclass, indisclustered from pg_index
4197+
where indrelid = 'alttype_cluster'::regclass
4198+
order by indexrelid::regclass::text;
4199+
indexrelid | indisclustered
4200+
----------------------+----------------
4201+
alttype_cluster_ind | t
4202+
alttype_cluster_pkey | f
4203+
(2 rows)
4204+
4205+
alter table alttype_cluster alter a type bigint;
4206+
select indexrelid::regclass, indisclustered from pg_index
4207+
where indrelid = 'alttype_cluster'::regclass
4208+
order by indexrelid::regclass::text;
4209+
indexrelid | indisclustered
4210+
----------------------+----------------
4211+
alttype_cluster_ind | t
4212+
alttype_cluster_pkey | f
4213+
(2 rows)
4214+
4215+
-- Constraint index remains clustered.
4216+
alter table alttype_cluster cluster on alttype_cluster_pkey;
4217+
select indexrelid::regclass, indisclustered from pg_index
4218+
where indrelid = 'alttype_cluster'::regclass
4219+
order by indexrelid::regclass::text;
4220+
indexrelid | indisclustered
4221+
----------------------+----------------
4222+
alttype_cluster_ind | f
4223+
alttype_cluster_pkey | t
4224+
(2 rows)
4225+
4226+
alter table alttype_cluster alter a type int;
4227+
select indexrelid::regclass, indisclustered from pg_index
4228+
where indrelid = 'alttype_cluster'::regclass
4229+
order by indexrelid::regclass::text;
4230+
indexrelid | indisclustered
4231+
----------------------+----------------
4232+
alttype_cluster_ind | f
4233+
alttype_cluster_pkey | t
4234+
(2 rows)
4235+
4236+
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
@@ -2814,3 +2814,28 @@ create trigger xtrig
28142814
update bar1 set a = a + 1;
28152815

28162816
/* End test case for bug #16242 */
2817+
2818+
-- Test that ALTER TABLE rewrite preserves a clustered index
2819+
-- for normal indexes and indexes on constraints.
2820+
create table alttype_cluster (a int);
2821+
alter table alttype_cluster add primary key (a);
2822+
create index alttype_cluster_ind on alttype_cluster (a);
2823+
alter table alttype_cluster cluster on alttype_cluster_ind;
2824+
-- Normal index remains clustered.
2825+
select indexrelid::regclass, indisclustered from pg_index
2826+
where indrelid = 'alttype_cluster'::regclass
2827+
order by indexrelid::regclass::text;
2828+
alter table alttype_cluster alter a type bigint;
2829+
select indexrelid::regclass, indisclustered from pg_index
2830+
where indrelid = 'alttype_cluster'::regclass
2831+
order by indexrelid::regclass::text;
2832+
-- Constraint index remains clustered.
2833+
alter table alttype_cluster cluster on alttype_cluster_pkey;
2834+
select indexrelid::regclass, indisclustered from pg_index
2835+
where indrelid = 'alttype_cluster'::regclass
2836+
order by indexrelid::regclass::text;
2837+
alter table alttype_cluster alter a type int;
2838+
select indexrelid::regclass, indisclustered from pg_index
2839+
where indrelid = 'alttype_cluster'::regclass
2840+
order by indexrelid::regclass::text;
2841+
drop table alttype_cluster;

0 commit comments

Comments
 (0)