Skip to content

Commit 432b9b0

Browse files
bmomjianbmomjian
committed
Add ALTER TABLE <tablename> CLUSTER ON <indexname>
Alvaro Herrera
1 parent db5d7cc commit 432b9b0

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/20 03:34:55 momjian Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/commands/tablecmds.c,v 1.69 2003/03/20 18: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(Oid relOid, const char *indexName)
3805+
{
3806+
Relation rel,
3807+
pg_index;
3808+
List *index;
3809+
Oid indexOid;
3810+
HeapTuple indexTuple;
3811+
Form_pg_index indexForm;
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+
HeapTuple idxtuple;
3850+
Form_pg_index idxForm;
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+
else if (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/20 07:02:08 momjian Exp $
14+
* $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.408 2003/03/20 18:52:47 momjian Exp $
1515
*
1616
* HISTORY
1717
* AUTHOR DATE MAJOR EVENT
@@ -1217,6 +1217,15 @@ AlterTableStmt:
12171217
n->name = $6;
12181218
$$ = (Node *)n;
12191219
}
1220+
/* ALTER TABLE <name> CLUSTER ON <indexname> */
1221+
| ALTER TABLE qualified_name CLUSTER ON name
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/20 07:02:11 momjian Exp $
13+
* $Header: /cvsroot/pgsql/src/backend/tcop/utility.c,v 1.197 2003/03/20 18: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
const char *constrName,
4444
DropBehavior behavior);
4545

46+
extern void AlterTableClusterOn(Oid relOid, const char *indexName);
47+
4648
extern void AlterTableCreateToastTable(Oid relOid, bool silent);
4749

4850
extern void AlterTableOwner(Oid relationOid, int32 newOwnerSysId);

‎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/20 07:02:11 momjian Exp $
10+
* $Id: parsenodes.h,v 1.235 2003/03/20 18: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
AND pg_class_2.relname = 'clstr_tst'
8888
AND indisclustered;
8989

90+
-- Try changing indisclustered
91+
ALTER TABLE clstr_tst CLUSTER ON clstr_tst_b_c;
92+
SELECT pg_class.relname FROM pg_index, pg_class, pg_class AS pg_class_2
93+
WHERE pg_class.oid=indexrelid
94+
AND indrelid=pg_class_2.oid
95+
AND pg_class_2.relname = 'clstr_tst'
96+
AND indisclustered;
97+
9098
-- Verify that clustering all tables does in fact cluster the right ones
9199
CREATE USER clstr_user;
92100
CREATE TABLE clstr_1 (a INT PRIMARY KEY);

0 commit comments

Comments
 (0)