Skip to content

Commit 09d4e96

Browse files
bmomjianbmomjian
committed
Add ALTER INDEX, particularly for moving tablespaces.
Gavin Sherry
1 parent daa076c commit 09d4e96

File tree

5 files changed

+266
-19
lines changed

5 files changed

+266
-19
lines changed

‎doc/src/sgml/ref/alter_index.sgml

Lines changed: 188 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,188 @@
1+
<!--
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_index.sgml,v 1.1 2004/08/20 04:29:32 momjian Exp $
3+
PostgreSQL documentation
4+
-->
5+
6+
<refentry id="SQL-ALTERINDEX">
7+
<refmeta>
8+
<refentrytitle id="sql-alterindex-title">ALTER INDEX</refentrytitle>
9+
<refmiscinfo>SQL - Language Statements</refmiscinfo>
10+
</refmeta>
11+
12+
<refnamediv>
13+
<refname>ALTER INDEX</refname>
14+
<refpurpose>change the definition of an index</refpurpose>
15+
</refnamediv>
16+
17+
<indexterm zone="sql-alterindex">
18+
<primary>ALTER INDEX</primary>
19+
</indexterm>
20+
21+
<refsynopsisdiv>
22+
<synopsis>
23+
ALTER INDEX <replaceable class="PARAMETER">name</replaceable>
24+
<replaceable class="PARAMETER">action</replaceable> [, ... ]
25+
ALTER INDEX <replaceable class="PARAMETER">name</replaceable>
26+
RENAME TO <replaceable class="PARAMETER">new_name</replaceable>
27+
28+
where <replaceable class="PARAMETER">action</replaceable> is one of:
29+
30+
OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
31+
SET INDEXSPACE <replaceable class="PARAMETER">indexspace_name</replaceable>
32+
</synopsis>
33+
</refsynopsisdiv>
34+
35+
<refsect1>
36+
<title>Description</title>
37+
38+
<para>
39+
<command>ALTER INDEX</command> changes the definition of an existing index.
40+
There are several subforms:
41+
42+
<variablelist>
43+
44+
<varlistentry>
45+
<term><literal>OWNER</literal></term>
46+
<listitem>
47+
<para>
48+
This form changes the owner of the index to the
49+
specified user.
50+
</para>
51+
</listitem>
52+
</varlistentry>
53+
54+
<varlistentry>
55+
<term><literal>SET TABLESPACE</literal></term>
56+
<listitem>
57+
<para>
58+
This form changes the index's tablespace to the specified tablespace and
59+
moves the data file(s) associated with the index to the new tablespace.
60+
See also
61+
<xref linkend="SQL-CREATETABLESPACE" endterm="sql-createtablespace-title">.
62+
</para>
63+
</listitem>
64+
</varlistentry>
65+
66+
<varlistentry>
67+
<term><literal>RENAME</literal></term>
68+
<listitem>
69+
<para>
70+
The <literal>RENAME</literal> forms change the name of the index.
71+
There is no effect on the stored data.
72+
</para>
73+
</listitem>
74+
</varlistentry>
75+
76+
</variablelist>
77+
</para>
78+
79+
<para>
80+
All the actions except <literal>RENAME</literal> can be combined into
81+
a list of multiple alterations to apply in parallel.
82+
</para>
83+
84+
</refsect1>
85+
86+
<refsect1>
87+
<title>Parameters</title>
88+
89+
<variablelist>
90+
91+
<varlistentry>
92+
<term><replaceable class="PARAMETER">name</replaceable></term>
93+
<listitem>
94+
<para>
95+
The name (possibly schema-qualified) of an existing index to
96+
alter.
97+
</para>
98+
</listitem>
99+
</varlistentry>
100+
101+
102+
<varlistentry>
103+
<term><replaceable class="PARAMETER">new_name</replaceable></term>
104+
<listitem>
105+
<para>
106+
New name for the index.
107+
</para>
108+
</listitem>
109+
</varlistentry>
110+
111+
112+
<varlistentry>
113+
<term><replaceable class="PARAMETER">new_owner</replaceable></term>
114+
<listitem>
115+
<para>
116+
The user name of the new owner of the index.
117+
</para>
118+
</listitem>
119+
</varlistentry>
120+
121+
<varlistentry>
122+
<term><replaceable class="PARAMETER">tablespace_name</replaceable></term>
123+
<listitem>
124+
<para>
125+
The tablespace name to which the index will be moved.
126+
</para>
127+
</listitem>
128+
</varlistentry>
129+
130+
</variablelist>
131+
</refsect1>
132+
133+
<refsect1>
134+
<title>Notes</title>
135+
136+
<para>
137+
This same operations are supported by <literal>ALTER TABLE</>. See also
138+
<xref linkend="SQL-ALTERTABLE" endterm="SQL-ALTERTABLE-TITLE">.
139+
</para>
140+
141+
<para>
142+
Changing any part of a system catalog index is not permitted.
143+
</para>
144+
</refsect1>
145+
146+
<refsect1>
147+
<title>Examples</title>
148+
<para>
149+
To rename an existing index:
150+
<programlisting>
151+
ALTER INDEX distributors RENAME TO suppliers;
152+
</programlisting>
153+
</para>
154+
155+
<para>
156+
To move a index to a different tablespace:
157+
<programlisting>
158+
ALTER INDEX distributors SET TABLESPACE fasttablespace;
159+
</programlisting>
160+
</para>
161+
162+
</refsect1>
163+
164+
<refsect1>
165+
<title>Compatibility</title>
166+
167+
<para>
168+
<literal>ALTER INDEX</> is a PostgreSQL extension.
169+
</para>
170+
</refsect1>
171+
</refentry>
172+
173+
<!-- Keep this comment at the end of the file
174+
Local variables:
175+
mode: sgml
176+
sgml-omittag:nil
177+
sgml-shorttag:t
178+
sgml-minimize-attributes:nil
179+
sgml-always-quote-attributes:t
180+
sgml-indent-step:1
181+
sgml-indent-data:t
182+
sgml-parent-document:nil
183+
sgml-default-dtd-file:"../reference.ced"
184+
sgml-exposed-tags:nil
185+
sgml-local-catalogs:"/usr/lib/sgml/catalog"
186+
sgml-local-ecat-files:nil
187+
End:
188+
-->

‎src/backend/parser/gram.y

Lines changed: 45 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
*
1212
*
1313
* IDENTIFICATION
14-
* $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.471 2004/08/12 21:00:28 tgl Exp $
14+
* $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.472 2004/08/20 04:29:32 momjian Exp $
1515
*
1616
* HISTORY
1717
* AUTHOR DATE MAJOR EVENT
@@ -156,8 +156,8 @@ static void doNegateFloat(Value *v);
156156
%type <node> alter_column_default opclass_item alter_using
157157
%type <ival> add_drop
158158

159-
%type <node> alter_table_cmd
160-
%type <list> alter_table_cmds
159+
%type <node> alter_table_cmd alter_rel_cmd
160+
%type <list> alter_table_cmds alter_rel_cmds
161161

162162
%type <dbehavior> opt_drop_behavior
163163

@@ -1138,7 +1138,7 @@ CheckPointStmt:
11381138

11391139
/*****************************************************************************
11401140
*
1141-
* ALTER TABLE variations
1141+
* ALTER [ TABLE | INDEX ] variations
11421142
*
11431143
*****************************************************************************/
11441144

@@ -1148,6 +1148,15 @@ AlterTableStmt:
11481148
AlterTableStmt *n = makeNode(AlterTableStmt);
11491149
n->relation = $3;
11501150
n->cmds = $4;
1151+
n->relkind = OBJECT_TABLE;
1152+
$$ = (Node *)n;
1153+
}
1154+
| ALTER INDEX relation_expr alter_rel_cmds
1155+
{
1156+
AlterTableStmt *n = makeNode(AlterTableStmt);
1157+
n->relation = $3;
1158+
n->cmds = $4;
1159+
n->relkind = OBJECT_INDEX;
11511160
$$ = (Node *)n;
11521161
}
11531162
;
@@ -1262,14 +1271,6 @@ alter_table_cmd:
12621271
n->subtype = AT_ToastTable;
12631272
$$ = (Node *)n;
12641273
}
1265-
/* ALTER TABLE <name> OWNER TO UserId */
1266-
| OWNER TO UserId
1267-
{
1268-
AlterTableCmd *n = makeNode(AlterTableCmd);
1269-
n->subtype = AT_ChangeOwner;
1270-
n->name = $3;
1271-
$$ = (Node *)n;
1272-
}
12731274
/* ALTER TABLE <name> CLUSTER ON <indexname> */
12741275
| CLUSTER ON name
12751276
{
@@ -1286,7 +1287,27 @@ alter_table_cmd:
12861287
n->name = NULL;
12871288
$$ = (Node *)n;
12881289
}
1289-
/* ALTER TABLE <name> SET TABLESPACE <tablespacename> */
1290+
| alter_rel_cmd
1291+
{
1292+
$$ = $1;
1293+
}
1294+
;
1295+
1296+
alter_rel_cmds: alter_rel_cmd { $$ = list_make1($1); }
1297+
| alter_rel_cmds ',' alter_rel_cmd { $$ = lappend($1, $3); }
1298+
;
1299+
1300+
1301+
alter_rel_cmd:
1302+
/* ALTER [ TABLE | INDEX ] <name> OWNER TO UserId */
1303+
OWNER TO UserId
1304+
{
1305+
AlterTableCmd *n = makeNode(AlterTableCmd);
1306+
n->subtype = AT_ChangeOwner;
1307+
n->name = $3;
1308+
$$ = (Node *)n;
1309+
}
1310+
/* ALTER [ TABLE | INDEX ] <name> SET TABLESPACE <tablespacename> */
12901311
| SET TABLESPACE name
12911312
{
12921313
AlterTableCmd *n = makeNode(AlterTableCmd);
@@ -1319,6 +1340,8 @@ alter_using:
13191340
| /* EMPTY */ { $$ = NULL; }
13201341
;
13211342

1343+
1344+
13221345
/*****************************************************************************
13231346
*
13241347
* QUERY :
@@ -3660,6 +3683,15 @@ RenameStmt: ALTER AGGREGATE func_name '(' aggr_argtype ')' RENAME TO name
36603683
n->newname = $6;
36613684
$$ = (Node *)n;
36623685
}
3686+
| ALTER INDEX relation_expr RENAME TO name
3687+
{
3688+
RenameStmt *n = makeNode(RenameStmt);
3689+
n->renameType = OBJECT_INDEX;
3690+
n->relation = $3;
3691+
n->subname = NULL;
3692+
n->newname = $6;
3693+
$$ = (Node *)n;
3694+
}
36633695
| ALTER TABLE relation_expr RENAME opt_column name TO name
36643696
{
36653697
RenameStmt *n = makeNode(RenameStmt);

‎src/backend/tcop/utility.c

Lines changed: 18 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
*
1111
*
1212
* IDENTIFICATION
13-
* $PostgreSQL: pgsql/src/backend/tcop/utility.c,v 1.225 2004/08/12 21:00:34 tgl Exp $
13+
* $PostgreSQL: pgsql/src/backend/tcop/utility.c,v 1.226 2004/08/20 04:29:32 momjian Exp $
1414
*
1515
*-------------------------------------------------------------------------
1616
*/
@@ -1269,6 +1269,9 @@ CreateCommandTag(Node *parsetree)
12691269
case OBJECT_GROUP:
12701270
tag = "ALTER GROUP";
12711271
break;
1272+
case OBJECT_INDEX:
1273+
tag = "ALTER INDEX";
1274+
break;
12721275
case OBJECT_LANGUAGE:
12731276
tag = "ALTER LANGUAGE";
12741277
break;
@@ -1331,9 +1334,21 @@ CreateCommandTag(Node *parsetree)
13311334
break;
13321335

13331336
case T_AlterTableStmt:
1334-
tag = "ALTER TABLE";
1335-
break;
1337+
{
1338+
AlterTableStmt *stmt = (AlterTableStmt *) parsetree;
1339+
1340+
/*
1341+
* We might be supporting ALTER INDEX here, so
1342+
* set the completion table appropriately.
1343+
* Catch all other possibilities with ALTER TABLE
1344+
*/
13361345

1346+
if(stmt->relkind == OBJECT_INDEX)
1347+
tag = "ALTER INDEX";
1348+
else
1349+
tag = "ALTER TABLE";
1350+
}
1351+
break;
13371352
case T_AlterDomainStmt:
13381353
tag = "ALTER DOMAIN";
13391354
break;

‎src/bin/psql/tab-complete.c

Lines changed: 13 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
*
44
* Copyright (c) 2000-2003, PostgreSQL Global Development Group
55
*
6-
* $PostgreSQL: pgsql/src/bin/psql/tab-complete.c,v 1.109 2004/07/28 14:23:30 tgl Exp $
6+
* $PostgreSQL: pgsql/src/bin/psql/tab-complete.c,v 1.110 2004/08/20 04:29:32 momjian Exp $
77
*/
88

99
/*----------------------------------------------------------------------
@@ -632,7 +632,8 @@ psql_completion(char *text, int start, int end)
632632
pg_strcasecmp(prev3_wd, "TABLE") != 0)
633633
{
634634
static const char *const list_ALTER[] =
635-
{"DATABASE", "GROUP", "SCHEMA", "TABLE", "TRIGGER", "USER", NULL};
635+
{"DATABASE", "GROUP", "SCHEMA", "TABLE", "TRIGGER", "USER", "INDEX",
636+
NULL};
636637

637638
COMPLETE_WITH_LIST(list_ALTER);
638639
}
@@ -646,6 +647,16 @@ psql_completion(char *text, int start, int end)
646647

647648
COMPLETE_WITH_LIST(list_ALTERDATABASE);
648649
}
650+
/* ALTER INDEX <name> */
651+
else if (pg_strcasecmp(prev3_wd, "ALTER") == 0 &&
652+
pg_strcasecmp(prev2_wd, "INDEX") == 0)
653+
{
654+
static const char *const list_ALTERINDEX[] =
655+
{"SET TABLESPACE", "OWNER TO", "RENAME TO", NULL};
656+
657+
COMPLETE_WITH_LIST(list_ALTERINDEX);
658+
}
659+
649660
/* ALTER TRIGGER <name>, add ON */
650661
else if (pg_strcasecmp(prev3_wd, "ALTER") == 0 &&
651662
pg_strcasecmp(prev2_wd, "TRIGGER") == 0)

‎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-2003, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.266 2004/08/19 20:57:41 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.267 2004/08/20 04:29:33 momjian Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -797,6 +797,7 @@ typedef struct AlterTableStmt
797797
NodeTag type;
798798
RangeVar *relation; /* table to work on */
799799
List *cmds; /* list of subcommands */
800+
ObjectType relkind; /* type of object */
800801
} AlterTableStmt;
801802

802803
typedef enum AlterTableType

0 commit comments

Comments
 (0)