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

Commit41faafb

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 parent1ca97af commit41faafb

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

181182
/* Struct describing one new constraint to check in Phase 3 scan */
@@ -10331,6 +10332,21 @@ RememberReplicaIdentityForRebuilding(Oid indoid, AlteredTableInfo *tab)
1033110332
tab->replicaIdentityIndex=get_rel_name(indoid);
1033210333
}
1033310334

10335+
/*
10336+
* Subroutine for ATExecAlterColumnType: remember any clustered index.
10337+
*/
10338+
staticvoid
10339+
RememberClusterOnForRebuilding(Oidindoid,AlteredTableInfo*tab)
10340+
{
10341+
if (!get_index_isclustered(indoid))
10342+
return;
10343+
10344+
if (tab->clusterOnIndex)
10345+
elog(ERROR,"relation %u has multiple clustered indexes",tab->relid);
10346+
10347+
tab->clusterOnIndex=get_rel_name(indoid);
10348+
}
10349+
1033410350
/*
1033510351
* Subroutine for ATExecAlterColumnType: remember that a constraint needs
1033610352
* to be rebuilt (which we might already know).
@@ -10356,9 +10372,18 @@ RememberConstraintForRebuilding(Oid conoid, AlteredTableInfo *tab)
1035610372
tab->changedConstraintDefs=lappend(tab->changedConstraintDefs,
1035710373
defstring);
1035810374

10375+
/*
10376+
* For the index of a constraint, if any, remember if it is used for
10377+
* the table's replica identity or if it is a clustered index, so that
10378+
* ATPostAlterTypeCleanup() can queue up commands necessary to restore
10379+
* those properties.
10380+
*/
1035910381
indoid=get_constraint_index(conoid);
1036010382
if (OidIsValid(indoid))
10383+
{
1036110384
RememberReplicaIdentityForRebuilding(indoid,tab);
10385+
RememberClusterOnForRebuilding(indoid,tab);
10386+
}
1036210387
}
1036310388
}
1036410389

@@ -10402,7 +10427,13 @@ RememberIndexForRebuilding(Oid indoid, AlteredTableInfo *tab)
1040210427
tab->changedIndexDefs=lappend(tab->changedIndexDefs,
1040310428
defstring);
1040410429

10430+
/*
10431+
* Remember if this index is used for the table's replica identity
10432+
* or if it is a clustered index, so that ATPostAlterTypeCleanup()
10433+
* can queue up commands necessary to restore those properties.
10434+
*/
1040510435
RememberReplicaIdentityForRebuilding(indoid,tab);
10436+
RememberClusterOnForRebuilding(indoid,tab);
1040610437
}
1040710438
}
1040810439
}
@@ -10635,6 +10666,21 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
1063510666
lappend(tab->subcmds[AT_PASS_OLD_CONSTR],cmd);
1063610667
}
1063710668

10669+
/*
10670+
* Queue up command to restore marking of index used for cluster.
10671+
*/
10672+
if (tab->clusterOnIndex)
10673+
{
10674+
AlterTableCmd*cmd=makeNode(AlterTableCmd);
10675+
10676+
cmd->subtype=AT_ClusterOn;
10677+
cmd->name=tab->clusterOnIndex;
10678+
10679+
/* do it after indexes and constraints */
10680+
tab->subcmds[AT_PASS_OLD_CONSTR]=
10681+
lappend(tab->subcmds[AT_PASS_OLD_CONSTR],cmd);
10682+
}
10683+
1063810684
/*
1063910685
* It should be okay to use DROP_RESTRICT here, since nothing else should
1064010686
* 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
@@ -3181,3 +3181,26 @@ get_index_isreplident(Oid index_oid)
31813181

31823182
returnresult;
31833183
}
3184+
3185+
/*
3186+
* get_index_isclustered
3187+
*
3188+
*Given the index OID, return pg_index.indisclustered.
3189+
*/
3190+
bool
3191+
get_index_isclustered(Oidindex_oid)
3192+
{
3193+
boolisclustered;
3194+
HeapTupletuple;
3195+
Form_pg_indexrd_index;
3196+
3197+
tuple=SearchSysCache1(INDEXRELID,ObjectIdGetDatum(index_oid));
3198+
if (!HeapTupleIsValid(tuple))
3199+
elog(ERROR,"cache lookup failed for index %u",index_oid);
3200+
3201+
rd_index= (Form_pg_index)GETSTRUCT(tuple);
3202+
isclustered=rd_index->indisclustered;
3203+
ReleaseSysCache(tuple);
3204+
3205+
returnisclustered;
3206+
}

‎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
@@ -4212,3 +4212,51 @@ create trigger xtrig
42124212
update bar1 set a = a + 1;
42134213
INFO: a=1, b=1
42144214
/* End test case for bug #16242 */
4215+
-- Test that ALTER TABLE rewrite preserves a clustered index
4216+
-- for normal indexes and indexes on constraints.
4217+
create table alttype_cluster (a int);
4218+
alter table alttype_cluster add primary key (a);
4219+
create index alttype_cluster_ind on alttype_cluster (a);
4220+
alter table alttype_cluster cluster on alttype_cluster_ind;
4221+
-- Normal index remains clustered.
4222+
select indexrelid::regclass, indisclustered from pg_index
4223+
where indrelid = 'alttype_cluster'::regclass
4224+
order by indexrelid::regclass::text;
4225+
indexrelid | indisclustered
4226+
----------------------+----------------
4227+
alttype_cluster_ind | t
4228+
alttype_cluster_pkey | f
4229+
(2 rows)
4230+
4231+
alter table alttype_cluster alter a type bigint;
4232+
select indexrelid::regclass, indisclustered from pg_index
4233+
where indrelid = 'alttype_cluster'::regclass
4234+
order by indexrelid::regclass::text;
4235+
indexrelid | indisclustered
4236+
----------------------+----------------
4237+
alttype_cluster_ind | t
4238+
alttype_cluster_pkey | f
4239+
(2 rows)
4240+
4241+
-- Constraint index remains clustered.
4242+
alter table alttype_cluster cluster on alttype_cluster_pkey;
4243+
select indexrelid::regclass, indisclustered from pg_index
4244+
where indrelid = 'alttype_cluster'::regclass
4245+
order by indexrelid::regclass::text;
4246+
indexrelid | indisclustered
4247+
----------------------+----------------
4248+
alttype_cluster_ind | f
4249+
alttype_cluster_pkey | t
4250+
(2 rows)
4251+
4252+
alter table alttype_cluster alter a type int;
4253+
select indexrelid::regclass, indisclustered from pg_index
4254+
where indrelid = 'alttype_cluster'::regclass
4255+
order by indexrelid::regclass::text;
4256+
indexrelid | indisclustered
4257+
----------------------+----------------
4258+
alttype_cluster_ind | f
4259+
alttype_cluster_pkey | t
4260+
(2 rows)
4261+
4262+
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
@@ -2802,3 +2802,28 @@ create trigger xtrig
28022802
update bar1set a= a+1;
28032803

28042804
/* End test case for bug #16242*/
2805+
2806+
-- Test that ALTER TABLE rewrite preserves a clustered index
2807+
-- for normal indexes and indexes on constraints.
2808+
createtablealttype_cluster (aint);
2809+
altertable alttype_cluster addprimary key (a);
2810+
createindexalttype_cluster_indon alttype_cluster (a);
2811+
altertable alttype_cluster clusteron alttype_cluster_ind;
2812+
-- Normal index remains clustered.
2813+
select indexrelid::regclass, indisclusteredfrom pg_index
2814+
where indrelid='alttype_cluster'::regclass
2815+
order by indexrelid::regclass::text;
2816+
altertable alttype_cluster alter a typebigint;
2817+
select indexrelid::regclass, indisclusteredfrom pg_index
2818+
where indrelid='alttype_cluster'::regclass
2819+
order by indexrelid::regclass::text;
2820+
-- Constraint index remains clustered.
2821+
altertable alttype_cluster clusteron alttype_cluster_pkey;
2822+
select indexrelid::regclass, indisclusteredfrom pg_index
2823+
where indrelid='alttype_cluster'::regclass
2824+
order by indexrelid::regclass::text;
2825+
altertable alttype_cluster alter a typeint;
2826+
select indexrelid::regclass, indisclusteredfrom pg_index
2827+
where indrelid='alttype_cluster'::regclass
2828+
order by indexrelid::regclass::text;
2829+
droptable alttype_cluster;

0 commit comments

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