Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit3e62dd3

Browse files
committed
Preserve clustered index after rewrites with ALTER TABLE
A table rewritten by ALTER TABLE would lose tracking of an index usablefor CLUSTER. This setting is tracked by pg_index.indisclustered and iscontrolled by ALTER TABLE, so some extra work was needed to restore itproperly. Note that ALTER TABLE only marks the index that can be usedfor clustering, and does not do the actual operation.Author: Amit Langote, Justin PryzbyReviewed-by: Ibrar Ahmed, Michael PaquierDiscussion:https://postgr.es/m/20200202161718.GI13621@telsasoft.comBackpatch-through: 9.5
1 parent23a0cf2 commit3e62dd3

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
returnisvalid;
32753275
}
3276+
3277+
/*
3278+
* get_index_isclustered
3279+
*
3280+
*Given the index OID, return pg_index.indisclustered.
3281+
*/
3282+
bool
3283+
get_index_isclustered(Oidindex_oid)
3284+
{
3285+
boolisclustered;
3286+
HeapTupletuple;
3287+
Form_pg_indexrd_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+
returnisclustered;
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 Oidget_range_collation(Oid rangeOid);
183183
externOidget_index_column_opclass(Oidindex_oid,intattno);
184184
externboolget_index_isreplident(Oidindex_oid);
185185
externboolget_index_isvalid(Oidindex_oid);
186+
externboolget_index_isclustered(Oidindex_oid);
186187

187188
#definetype_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 bar1set 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+
createtablealttype_cluster (aint);
2821+
altertable alttype_cluster addprimary key (a);
2822+
createindexalttype_cluster_indon alttype_cluster (a);
2823+
altertable alttype_cluster clusteron alttype_cluster_ind;
2824+
-- Normal index remains clustered.
2825+
select indexrelid::regclass, indisclusteredfrom pg_index
2826+
where indrelid='alttype_cluster'::regclass
2827+
order by indexrelid::regclass::text;
2828+
altertable alttype_cluster alter a typebigint;
2829+
select indexrelid::regclass, indisclusteredfrom pg_index
2830+
where indrelid='alttype_cluster'::regclass
2831+
order by indexrelid::regclass::text;
2832+
-- Constraint index remains clustered.
2833+
altertable alttype_cluster clusteron alttype_cluster_pkey;
2834+
select indexrelid::regclass, indisclusteredfrom pg_index
2835+
where indrelid='alttype_cluster'::regclass
2836+
order by indexrelid::regclass::text;
2837+
altertable alttype_cluster alter a typeint;
2838+
select indexrelid::regclass, indisclusteredfrom pg_index
2839+
where indrelid='alttype_cluster'::regclass
2840+
order by indexrelid::regclass::text;
2841+
droptable alttype_cluster;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp