Skip to content

Commit 3f77a67

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 a850063 commit 3f77a67

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
@@ -167,6 +167,7 @@ typedef struct AlteredTableInfo
167167
List *changedIndexOids; /* OIDs of indexes to rebuild */
168168
List *changedIndexDefs; /* string definitions of same */
169169
char *replicaIdentityIndex; /* index to reset as REPLICA IDENTITY */
170+
char *clusterOnIndex; /* index to use for CLUSTER */
170171
} AlteredTableInfo;
171172

172173
/* Struct describing one new constraint to check in Phase 3 scan */
@@ -8583,6 +8584,21 @@ RememberReplicaIdentityForRebuilding(Oid indoid, AlteredTableInfo *tab)
85838584
tab->replicaIdentityIndex = get_rel_name(indoid);
85848585
}
85858586

8587+
/*
8588+
* Subroutine for ATExecAlterColumnType: remember any clustered index.
8589+
*/
8590+
static void
8591+
RememberClusterOnForRebuilding(Oid indoid, AlteredTableInfo *tab)
8592+
{
8593+
if (!get_index_isclustered(indoid))
8594+
return;
8595+
8596+
if (tab->clusterOnIndex)
8597+
elog(ERROR, "relation %u has multiple clustered indexes", tab->relid);
8598+
8599+
tab->clusterOnIndex = get_rel_name(indoid);
8600+
}
8601+
85868602
/*
85878603
* Subroutine for ATExecAlterColumnType: remember that a constraint needs
85888604
* to be rebuilt (which we might already know).
@@ -8627,9 +8643,18 @@ RememberConstraintForRebuilding(Oid conoid, AlteredTableInfo *tab,
86278643
defstring);
86288644
}
86298645

8646+
/*
8647+
* For the index of a constraint, if any, remember if it is used for
8648+
* the table's replica identity or if it is a clustered index, so that
8649+
* ATPostAlterTypeCleanup() can queue up commands necessary to restore
8650+
* those properties.
8651+
*/
86308652
indoid = get_constraint_index(conoid);
86318653
if (OidIsValid(indoid))
8654+
{
86328655
RememberReplicaIdentityForRebuilding(indoid, tab);
8656+
RememberClusterOnForRebuilding(indoid, tab);
8657+
}
86338658
}
86348659
}
86358660

@@ -8674,7 +8699,13 @@ RememberIndexForRebuilding(Oid indoid, AlteredTableInfo *tab)
86748699
tab->changedIndexDefs = lappend(tab->changedIndexDefs,
86758700
defstring);
86768701

8702+
/*
8703+
* Remember if this index is used for the table's replica identity
8704+
* or if it is a clustered index, so that ATPostAlterTypeCleanup()
8705+
* can queue up commands necessary to restore those properties.
8706+
*/
86778707
RememberReplicaIdentityForRebuilding(indoid, tab);
8708+
RememberClusterOnForRebuilding(indoid, tab);
86788709
}
86798710
}
86808711
}
@@ -8886,6 +8917,21 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
88868917
lappend(tab->subcmds[AT_PASS_OLD_CONSTR], cmd);
88878918
}
88888919

8920+
/*
8921+
* Queue up command to restore marking of index used for cluster.
8922+
*/
8923+
if (tab->clusterOnIndex)
8924+
{
8925+
AlterTableCmd *cmd = makeNode(AlterTableCmd);
8926+
8927+
cmd->subtype = AT_ClusterOn;
8928+
cmd->name = tab->clusterOnIndex;
8929+
8930+
/* do it after indexes and constraints */
8931+
tab->subcmds[AT_PASS_OLD_CONSTR] =
8932+
lappend(tab->subcmds[AT_PASS_OLD_CONSTR], cmd);
8933+
}
8934+
88898935
/*
88908936
* Now we can drop the existing constraints and indexes --- constraints
88918937
* 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
@@ -3070,3 +3070,26 @@ get_index_isreplident(Oid index_oid)
30703070

30713071
return result;
30723072
}
3073+
3074+
/*
3075+
* get_index_isclustered
3076+
*
3077+
* Given the index OID, return pg_index.indisclustered.
3078+
*/
3079+
bool
3080+
get_index_isclustered(Oid index_oid)
3081+
{
3082+
bool isclustered;
3083+
HeapTuple tuple;
3084+
Form_pg_index rd_index;
3085+
3086+
tuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(index_oid));
3087+
if (!HeapTupleIsValid(tuple))
3088+
elog(ERROR, "cache lookup failed for index %u", index_oid);
3089+
3090+
rd_index = (Form_pg_index) GETSTRUCT(tuple);
3091+
isclustered = rd_index->indisclustered;
3092+
ReleaseSysCache(tuple);
3093+
3094+
return isclustered;
3095+
}

‎src/include/utils/lsyscache.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -159,6 +159,7 @@ extern char *get_namespace_name_or_temp(Oid nspid);
159159
extern Oid get_range_subtype(Oid rangeOid);
160160
extern Oid get_range_collation(Oid rangeOid);
161161
extern bool get_index_isreplident(Oid index_oid);
162+
extern bool get_index_isclustered(Oid index_oid);
162163

163164
#define type_is_array(typid) (get_element_type(typid) != InvalidOid)
164165
/* 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
@@ -3109,3 +3109,51 @@ ALTER TABLE logged1 SET UNLOGGED; -- silently do nothing
31093109
DROP TABLE logged3;
31103110
DROP TABLE logged2;
31113111
DROP TABLE logged1;
3112+
-- Test that ALTER TABLE rewrite preserves a clustered index
3113+
-- for normal indexes and indexes on constraints.
3114+
create table alttype_cluster (a int);
3115+
alter table alttype_cluster add primary key (a);
3116+
create index alttype_cluster_ind on alttype_cluster (a);
3117+
alter table alttype_cluster cluster on alttype_cluster_ind;
3118+
-- Normal index remains clustered.
3119+
select indexrelid::regclass, indisclustered from pg_index
3120+
where indrelid = 'alttype_cluster'::regclass
3121+
order by indexrelid::regclass::text;
3122+
indexrelid | indisclustered
3123+
----------------------+----------------
3124+
alttype_cluster_ind | t
3125+
alttype_cluster_pkey | f
3126+
(2 rows)
3127+
3128+
alter table alttype_cluster alter a type bigint;
3129+
select indexrelid::regclass, indisclustered from pg_index
3130+
where indrelid = 'alttype_cluster'::regclass
3131+
order by indexrelid::regclass::text;
3132+
indexrelid | indisclustered
3133+
----------------------+----------------
3134+
alttype_cluster_ind | t
3135+
alttype_cluster_pkey | f
3136+
(2 rows)
3137+
3138+
-- Constraint index remains clustered.
3139+
alter table alttype_cluster cluster on alttype_cluster_pkey;
3140+
select indexrelid::regclass, indisclustered from pg_index
3141+
where indrelid = 'alttype_cluster'::regclass
3142+
order by indexrelid::regclass::text;
3143+
indexrelid | indisclustered
3144+
----------------------+----------------
3145+
alttype_cluster_ind | f
3146+
alttype_cluster_pkey | t
3147+
(2 rows)
3148+
3149+
alter table alttype_cluster alter a type int;
3150+
select indexrelid::regclass, indisclustered from pg_index
3151+
where indrelid = 'alttype_cluster'::regclass
3152+
order by indexrelid::regclass::text;
3153+
indexrelid | indisclustered
3154+
----------------------+----------------
3155+
alttype_cluster_ind | f
3156+
alttype_cluster_pkey | t
3157+
(2 rows)
3158+
3159+
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
@@ -1932,3 +1932,28 @@ ALTER TABLE logged1 SET UNLOGGED; -- silently do nothing
19321932
DROP TABLE logged3;
19331933
DROP TABLE logged2;
19341934
DROP TABLE logged1;
1935+
1936+
-- Test that ALTER TABLE rewrite preserves a clustered index
1937+
-- for normal indexes and indexes on constraints.
1938+
create table alttype_cluster (a int);
1939+
alter table alttype_cluster add primary key (a);
1940+
create index alttype_cluster_ind on alttype_cluster (a);
1941+
alter table alttype_cluster cluster on alttype_cluster_ind;
1942+
-- Normal index remains clustered.
1943+
select indexrelid::regclass, indisclustered from pg_index
1944+
where indrelid = 'alttype_cluster'::regclass
1945+
order by indexrelid::regclass::text;
1946+
alter table alttype_cluster alter a type bigint;
1947+
select indexrelid::regclass, indisclustered from pg_index
1948+
where indrelid = 'alttype_cluster'::regclass
1949+
order by indexrelid::regclass::text;
1950+
-- Constraint index remains clustered.
1951+
alter table alttype_cluster cluster on alttype_cluster_pkey;
1952+
select indexrelid::regclass, indisclustered from pg_index
1953+
where indrelid = 'alttype_cluster'::regclass
1954+
order by indexrelid::regclass::text;
1955+
alter table alttype_cluster alter a type int;
1956+
select indexrelid::regclass, indisclustered from pg_index
1957+
where indrelid = 'alttype_cluster'::regclass
1958+
order by indexrelid::regclass::text;
1959+
drop table alttype_cluster;

0 commit comments

Comments
 (0)