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

Commitcef2b8d

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 parent0e708d3 commitcef2b8d

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+
staticvoid
9445+
RememberClusterOnForRebuilding(Oidindoid,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
returnresult;
31653165
}
3166+
3167+
/*
3168+
* get_index_isclustered
3169+
*
3170+
*Given the index OID, return pg_index.indisclustered.
3171+
*/
3172+
bool
3173+
get_index_isclustered(Oidindex_oid)
3174+
{
3175+
boolisclustered;
3176+
HeapTupletuple;
3177+
Form_pg_indexrd_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+
returnisclustered;
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
externOidget_range_subtype(OidrangeOid);
180180
externOidget_range_collation(OidrangeOid);
181181
externboolget_index_isreplident(Oidindex_oid);
182+
externboolget_index_isclustered(Oidindex_oid);
182183

183184
#definetype_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
droptable at_test_sql_partop;
24642464
dropoperator class at_test_sql_partop using btree;
24652465
dropfunction at_test_sql_partop;
2466+
2467+
-- Test that ALTER TABLE rewrite preserves a clustered index
2468+
-- for normal indexes and indexes on constraints.
2469+
createtablealttype_cluster (aint);
2470+
altertable alttype_cluster addprimary key (a);
2471+
createindexalttype_cluster_indon alttype_cluster (a);
2472+
altertable alttype_cluster clusteron alttype_cluster_ind;
2473+
-- Normal index remains clustered.
2474+
select indexrelid::regclass, indisclusteredfrom pg_index
2475+
where indrelid='alttype_cluster'::regclass
2476+
order by indexrelid::regclass::text;
2477+
altertable alttype_cluster alter a typebigint;
2478+
select indexrelid::regclass, indisclusteredfrom pg_index
2479+
where indrelid='alttype_cluster'::regclass
2480+
order by indexrelid::regclass::text;
2481+
-- Constraint index remains clustered.
2482+
altertable alttype_cluster clusteron alttype_cluster_pkey;
2483+
select indexrelid::regclass, indisclusteredfrom pg_index
2484+
where indrelid='alttype_cluster'::regclass
2485+
order by indexrelid::regclass::text;
2486+
altertable alttype_cluster alter a typeint;
2487+
select indexrelid::regclass, indisclusteredfrom pg_index
2488+
where indrelid='alttype_cluster'::regclass
2489+
order by indexrelid::regclass::text;
2490+
droptable alttype_cluster;

0 commit comments

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