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

Commita40caf5

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 parentfc3f445 commita40caf5

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

182183
/* Struct describing one new constraint to check in Phase 3 scan */
@@ -11581,6 +11582,21 @@ RememberReplicaIdentityForRebuilding(Oid indoid, AlteredTableInfo *tab)
1158111582
tab->replicaIdentityIndex = get_rel_name(indoid);
1158211583
}
1158311584

11585+
/*
11586+
* Subroutine for ATExecAlterColumnType: remember any clustered index.
11587+
*/
11588+
static void
11589+
RememberClusterOnForRebuilding(Oid indoid, AlteredTableInfo *tab)
11590+
{
11591+
if (!get_index_isclustered(indoid))
11592+
return;
11593+
11594+
if (tab->clusterOnIndex)
11595+
elog(ERROR, "relation %u has multiple clustered indexes", tab->relid);
11596+
11597+
tab->clusterOnIndex = get_rel_name(indoid);
11598+
}
11599+
1158411600
/*
1158511601
* Subroutine for ATExecAlterColumnType: remember that a constraint needs
1158611602
* to be rebuilt (which we might already know).
@@ -11606,9 +11622,18 @@ RememberConstraintForRebuilding(Oid conoid, AlteredTableInfo *tab)
1160611622
tab->changedConstraintDefs = lappend(tab->changedConstraintDefs,
1160711623
defstring);
1160811624

11625+
/*
11626+
* For the index of a constraint, if any, remember if it is used for
11627+
* the table's replica identity or if it is a clustered index, so that
11628+
* ATPostAlterTypeCleanup() can queue up commands necessary to restore
11629+
* those properties.
11630+
*/
1160911631
indoid = get_constraint_index(conoid);
1161011632
if (OidIsValid(indoid))
11633+
{
1161111634
RememberReplicaIdentityForRebuilding(indoid, tab);
11635+
RememberClusterOnForRebuilding(indoid, tab);
11636+
}
1161211637
}
1161311638
}
1161411639

@@ -11652,7 +11677,13 @@ RememberIndexForRebuilding(Oid indoid, AlteredTableInfo *tab)
1165211677
tab->changedIndexDefs = lappend(tab->changedIndexDefs,
1165311678
defstring);
1165411679

11680+
/*
11681+
* Remember if this index is used for the table's replica identity
11682+
* or if it is a clustered index, so that ATPostAlterTypeCleanup()
11683+
* can queue up commands necessary to restore those properties.
11684+
*/
1165511685
RememberReplicaIdentityForRebuilding(indoid, tab);
11686+
RememberClusterOnForRebuilding(indoid, tab);
1165611687
}
1165711688
}
1165811689
}
@@ -11779,6 +11810,21 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
1177911810
lappend(tab->subcmds[AT_PASS_OLD_CONSTR], cmd);
1178011811
}
1178111812

11813+
/*
11814+
* Queue up command to restore marking of index used for cluster.
11815+
*/
11816+
if (tab->clusterOnIndex)
11817+
{
11818+
AlterTableCmd *cmd = makeNode(AlterTableCmd);
11819+
11820+
cmd->subtype = AT_ClusterOn;
11821+
cmd->name = tab->clusterOnIndex;
11822+
11823+
/* do it after indexes and constraints */
11824+
tab->subcmds[AT_PASS_OLD_CONSTR] =
11825+
lappend(tab->subcmds[AT_PASS_OLD_CONSTR], cmd);
11826+
}
11827+
1178211828
/*
1178311829
* It should be okay to use DROP_RESTRICT here, since nothing else should
1178411830
* 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
@@ -3334,3 +3334,26 @@ get_index_isvalid(Oid index_oid)
33343334

33353335
returnisvalid;
33363336
}
3337+
3338+
/*
3339+
* get_index_isclustered
3340+
*
3341+
*Given the index OID, return pg_index.indisclustered.
3342+
*/
3343+
bool
3344+
get_index_isclustered(Oidindex_oid)
3345+
{
3346+
boolisclustered;
3347+
HeapTupletuple;
3348+
Form_pg_indexrd_index;
3349+
3350+
tuple=SearchSysCache1(INDEXRELID,ObjectIdGetDatum(index_oid));
3351+
if (!HeapTupleIsValid(tuple))
3352+
elog(ERROR,"cache lookup failed for index %u",index_oid);
3353+
3354+
rd_index= (Form_pg_index)GETSTRUCT(tuple);
3355+
isclustered=rd_index->indisclustered;
3356+
ReleaseSysCache(tuple);
3357+
3358+
returnisclustered;
3359+
}

‎src/include/utils/lsyscache.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -185,6 +185,7 @@ extern Oidget_range_collation(Oid rangeOid);
185185
externOidget_index_column_opclass(Oidindex_oid,intattno);
186186
externboolget_index_isreplident(Oidindex_oid);
187187
externboolget_index_isvalid(Oidindex_oid);
188+
externboolget_index_isclustered(Oidindex_oid);
188189

189190
#definetype_is_array(typid) (get_element_type(typid) != InvalidOid)
190191
/* 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
@@ -4302,3 +4302,51 @@ create trigger xtrig
43024302
update bar1 set a = a + 1;
43034303
INFO: a=1, b=1
43044304
/* End test case for bug #16242 */
4305+
-- Test that ALTER TABLE rewrite preserves a clustered index
4306+
-- for normal indexes and indexes on constraints.
4307+
create table alttype_cluster (a int);
4308+
alter table alttype_cluster add primary key (a);
4309+
create index alttype_cluster_ind on alttype_cluster (a);
4310+
alter table alttype_cluster cluster on alttype_cluster_ind;
4311+
-- Normal index remains clustered.
4312+
select indexrelid::regclass, indisclustered from pg_index
4313+
where indrelid = 'alttype_cluster'::regclass
4314+
order by indexrelid::regclass::text;
4315+
indexrelid | indisclustered
4316+
----------------------+----------------
4317+
alttype_cluster_ind | t
4318+
alttype_cluster_pkey | f
4319+
(2 rows)
4320+
4321+
alter table alttype_cluster alter a type bigint;
4322+
select indexrelid::regclass, indisclustered from pg_index
4323+
where indrelid = 'alttype_cluster'::regclass
4324+
order by indexrelid::regclass::text;
4325+
indexrelid | indisclustered
4326+
----------------------+----------------
4327+
alttype_cluster_ind | t
4328+
alttype_cluster_pkey | f
4329+
(2 rows)
4330+
4331+
-- Constraint index remains clustered.
4332+
alter table alttype_cluster cluster on alttype_cluster_pkey;
4333+
select indexrelid::regclass, indisclustered from pg_index
4334+
where indrelid = 'alttype_cluster'::regclass
4335+
order by indexrelid::regclass::text;
4336+
indexrelid | indisclustered
4337+
----------------------+----------------
4338+
alttype_cluster_ind | f
4339+
alttype_cluster_pkey | t
4340+
(2 rows)
4341+
4342+
alter table alttype_cluster alter a type int;
4343+
select indexrelid::regclass, indisclustered from pg_index
4344+
where indrelid = 'alttype_cluster'::regclass
4345+
order by indexrelid::regclass::text;
4346+
indexrelid | indisclustered
4347+
----------------------+----------------
4348+
alttype_cluster_ind | f
4349+
alttype_cluster_pkey | t
4350+
(2 rows)
4351+
4352+
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
@@ -2847,3 +2847,28 @@ create trigger xtrig
28472847
update bar1set a= a+1;
28482848

28492849
/* End test case for bug #16242*/
2850+
2851+
-- Test that ALTER TABLE rewrite preserves a clustered index
2852+
-- for normal indexes and indexes on constraints.
2853+
createtablealttype_cluster (aint);
2854+
altertable alttype_cluster addprimary key (a);
2855+
createindexalttype_cluster_indon alttype_cluster (a);
2856+
altertable alttype_cluster clusteron alttype_cluster_ind;
2857+
-- Normal index remains clustered.
2858+
select indexrelid::regclass, indisclusteredfrom pg_index
2859+
where indrelid='alttype_cluster'::regclass
2860+
order by indexrelid::regclass::text;
2861+
altertable alttype_cluster alter a typebigint;
2862+
select indexrelid::regclass, indisclusteredfrom pg_index
2863+
where indrelid='alttype_cluster'::regclass
2864+
order by indexrelid::regclass::text;
2865+
-- Constraint index remains clustered.
2866+
altertable alttype_cluster clusteron alttype_cluster_pkey;
2867+
select indexrelid::regclass, indisclusteredfrom pg_index
2868+
where indrelid='alttype_cluster'::regclass
2869+
order by indexrelid::regclass::text;
2870+
altertable alttype_cluster alter a typeint;
2871+
select indexrelid::regclass, indisclusteredfrom pg_index
2872+
where indrelid='alttype_cluster'::regclass
2873+
order by indexrelid::regclass::text;
2874+
droptable alttype_cluster;

0 commit comments

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