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

Commit432b9b0

Browse files
committed
Add ALTER TABLE <tablename> CLUSTER ON <indexname>
Alvaro Herrera
1 parentdb5d7cc commit432b9b0

File tree

8 files changed

+146
-6
lines changed

8 files changed

+146
-6
lines changed

‎doc/src/sgml/ref/alter_table.sgml

Lines changed: 22 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.56 2003/02/19 04:06:28 momjian Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.57 2003/03/20 18:52:47 momjian Exp $
33
PostgreSQL documentation
44
-->
55

@@ -46,6 +46,8 @@ ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
4646
DROP CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> [ RESTRICT | CASCADE ]
4747
ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
4848
OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
49+
ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
50+
CLUSTER ON <replaceable class="PARAMETER">index_name</replaceable>
4951
</synopsis>
5052

5153
<refsect2 id="R2-SQL-ALTERTABLE-1">
@@ -138,6 +140,15 @@ ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
138140
</listitem>
139141
</varlistentry>
140142

143+
<varlistentry>
144+
<term><replaceable class="PARAMETER"> index_name </replaceable></term>
145+
<listitem>
146+
<para>
147+
The index name on which the table should be marked for clustering.
148+
</para>
149+
</listitem>
150+
</varlistentry>
151+
141152
<varlistentry>
142153
<term>CASCADE</term>
143154
<listitem>
@@ -344,6 +355,16 @@ ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
344355
</listitem>
345356
</varlistentry>
346357

358+
<varlistentry>
359+
<term>CLUSTER</term>
360+
<listitem>
361+
<para>
362+
This form marks a table for future <xref linkend="SQL-CLUSTER" endterm="sql-cluster-title">
363+
operations.
364+
</para>
365+
</listitem>
366+
</varlistentry>
367+
347368
</variablelist>
348369

349370
<para>

‎src/backend/commands/tablecmds.c

Lines changed: 85 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/commands/tablecmds.c,v 1.68 2003/03/2003:34:55 momjian Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/commands/tablecmds.c,v 1.69 2003/03/2018:52:47 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -3795,6 +3795,90 @@ CheckTupleType(Form_pg_class tuple_class)
37953795
}
37963796
}
37973797

3798+
/*
3799+
* ALTER TABLE CLUSTER ON
3800+
*
3801+
* The only thing we have to do is to change the indisclustered bits.
3802+
*/
3803+
void
3804+
AlterTableClusterOn(OidrelOid,constchar*indexName)
3805+
{
3806+
Relationrel,
3807+
pg_index;
3808+
List*index;
3809+
OidindexOid;
3810+
HeapTupleindexTuple;
3811+
Form_pg_indexindexForm;
3812+
3813+
rel=heap_open(relOid,AccessExclusiveLock);
3814+
3815+
indexOid=get_relname_relid(indexName,rel->rd_rel->relnamespace);
3816+
3817+
if (!OidIsValid(indexOid))
3818+
elog(ERROR,"ALTER TABLE: cannot find index \"%s\" for table \"%s\"",
3819+
indexName,NameStr(rel->rd_rel->relname));
3820+
3821+
indexTuple=SearchSysCache(INDEXRELID,
3822+
ObjectIdGetDatum(indexOid),
3823+
0,0,0);
3824+
3825+
if (!HeapTupleIsValid(indexTuple))
3826+
elog(ERROR,"Cache lookup failed for index %u",
3827+
indexOid);
3828+
indexForm= (Form_pg_index)GETSTRUCT(indexTuple);
3829+
3830+
/*
3831+
* If this is the same index the relation was previously
3832+
* clustered on, no need to do anything.
3833+
*/
3834+
if (indexForm->indisclustered)
3835+
{
3836+
elog(NOTICE,"ALTER TABLE: table \"%s\" is already being clustered on index \"%s\"",
3837+
NameStr(rel->rd_rel->relname),indexName);
3838+
heap_close(rel,AccessExclusiveLock);
3839+
return;
3840+
}
3841+
3842+
pg_index=heap_openr(IndexRelationName,RowExclusiveLock);
3843+
3844+
/*
3845+
* Now check each index in the relation and set the bit where needed.
3846+
*/
3847+
foreach (index,RelationGetIndexList(rel))
3848+
{
3849+
HeapTupleidxtuple;
3850+
Form_pg_indexidxForm;
3851+
3852+
indexOid=lfirsto(index);
3853+
idxtuple=SearchSysCacheCopy(INDEXRELID,
3854+
ObjectIdGetDatum(indexOid),
3855+
0,0,0);
3856+
if (!HeapTupleIsValid(idxtuple))
3857+
elog(ERROR,"Cache lookup failed for index %u",indexOid);
3858+
idxForm= (Form_pg_index)GETSTRUCT(idxtuple);
3859+
/*
3860+
* Unset the bit if set. We know it's wrong because we checked
3861+
* this earlier.
3862+
*/
3863+
if (idxForm->indisclustered)
3864+
{
3865+
idxForm->indisclustered= false;
3866+
simple_heap_update(pg_index,&idxtuple->t_self,idxtuple);
3867+
CatalogUpdateIndexes(pg_index,idxtuple);
3868+
}
3869+
elseif (idxForm->indexrelid==indexForm->indexrelid)
3870+
{
3871+
idxForm->indisclustered= true;
3872+
simple_heap_update(pg_index,&idxtuple->t_self,idxtuple);
3873+
CatalogUpdateIndexes(pg_index,idxtuple);
3874+
}
3875+
heap_freetuple(idxtuple);
3876+
}
3877+
ReleaseSysCache(indexTuple);
3878+
heap_close(rel,AccessExclusiveLock);
3879+
heap_close(pg_index,RowExclusiveLock);
3880+
}
3881+
37983882
/*
37993883
* ALTER TABLE CREATE TOAST TABLE
38003884
*/

‎src/backend/parser/gram.y

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
*
1212
*
1313
* IDENTIFICATION
14-
* $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.407 2003/03/2007:02:08 momjian Exp $
14+
* $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.408 2003/03/2018:52:47 momjian Exp $
1515
*
1616
* HISTORY
1717
* AUTHORDATEMAJOR EVENT
@@ -1217,6 +1217,15 @@ AlterTableStmt:
12171217
n->name =$6;
12181218
$$ = (Node *)n;
12191219
}
1220+
/* ALTER TABLE <name> CLUSTER ON <indexname>*/
1221+
|ALTERTABLEqualified_nameCLUSTERONname
1222+
{
1223+
AlterTableStmt *n = makeNode(AlterTableStmt);
1224+
n->subtype ='L';
1225+
n->relation =$3;
1226+
n->name =$6;
1227+
$$ = (Node *)n;
1228+
}
12201229
;
12211230

12221231
alter_column_default:

‎src/backend/tcop/utility.c

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
*
1111
*
1212
* IDENTIFICATION
13-
* $Header: /cvsroot/pgsql/src/backend/tcop/utility.c,v 1.196 2003/03/2007:02:11 momjian Exp $
13+
* $Header: /cvsroot/pgsql/src/backend/tcop/utility.c,v 1.197 2003/03/2018:52:48 momjian Exp $
1414
*
1515
*-------------------------------------------------------------------------
1616
*/
@@ -609,6 +609,9 @@ ProcessUtility(Node *parsetree,
609609
AlterTableOwner(relid,
610610
get_usesysid(stmt->name));
611611
break;
612+
case'L':/* CLUSTER ON */
613+
AlterTableClusterOn(relid,stmt->name);
614+
break;
612615
case'o':/* ADD OIDS */
613616
AlterTableAlterOids(relid,
614617
interpretInhOption(stmt->relation->inhOpt),

‎src/include/commands/tablecmds.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $Id: tablecmds.h,v 1.11 2003/02/13 05:20:03 momjian Exp $
10+
* $Id: tablecmds.h,v 1.12 2003/03/20 18:52:48 momjian Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -43,6 +43,8 @@ extern void AlterTableDropConstraint(Oid myrelid, bool recurse,
4343
constchar*constrName,
4444
DropBehaviorbehavior);
4545

46+
externvoidAlterTableClusterOn(OidrelOid,constchar*indexName);
47+
4648
externvoidAlterTableCreateToastTable(OidrelOid,boolsilent);
4749

4850
externvoidAlterTableOwner(OidrelationOid,int32newOwnerSysId);

‎src/include/nodes/parsenodes.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $Id: parsenodes.h,v 1.234 2003/03/2007:02:11 momjian Exp $
10+
* $Id: parsenodes.h,v 1.235 2003/03/2018:52:48 momjian Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -704,6 +704,7 @@ typedef struct AlterTableStmt
704704
*X = drop constraint
705705
*E = create toast table
706706
*U = change owner
707+
*L = CLUSTER ON
707708
* o = DROP OIDS
708709
*------------
709710
*/

‎src/test/regress/expected/cluster.out

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -285,6 +285,18 @@ WHERE pg_class.oid=indexrelid
285285
clstr_tst_c
286286
(1 row)
287287

288+
-- Try changing indisclustered
289+
ALTER TABLE clstr_tst CLUSTER ON clstr_tst_b_c;
290+
SELECT pg_class.relname FROM pg_index, pg_class, pg_class AS pg_class_2
291+
WHERE pg_class.oid=indexrelid
292+
AND indrelid=pg_class_2.oid
293+
AND pg_class_2.relname = 'clstr_tst'
294+
AND indisclustered;
295+
relname
296+
---------------
297+
clstr_tst_b_c
298+
(1 row)
299+
288300
-- Verify that clustering all tables does in fact cluster the right ones
289301
CREATE USER clstr_user;
290302
CREATE TABLE clstr_1 (a INT PRIMARY KEY);

‎src/test/regress/sql/cluster.sql

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -87,6 +87,14 @@ WHERE pg_class.oid=indexrelid
8787
ANDpg_class_2.relname='clstr_tst'
8888
AND indisclustered;
8989

90+
-- Try changing indisclustered
91+
ALTERTABLE clstr_tst CLUSTERON clstr_tst_b_c;
92+
SELECTpg_class.relnameFROM pg_index, pg_class, pg_classAS pg_class_2
93+
WHEREpg_class.oid=indexrelid
94+
AND indrelid=pg_class_2.oid
95+
ANDpg_class_2.relname='clstr_tst'
96+
AND indisclustered;
97+
9098
-- Verify that clustering all tables does in fact cluster the right ones
9199
CREATEUSERclstr_user;
92100
CREATETABLEclstr_1 (aINTPRIMARY KEY);

0 commit comments

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