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

Commit09d4e96

Browse files
committed
Add ALTER INDEX, particularly for moving tablespaces.
Gavin Sherry
1 parentdaa076c commit09d4e96

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
* AUTHORDATEMAJOR EVENT
@@ -156,8 +156,8 @@ static void doNegateFloat(Value *v);
156156
%type<node>alter_column_defaultopclass_itemalter_using
157157
%type<ival>add_drop
158158

159-
%type<node>alter_table_cmd
160-
%type<list>alter_table_cmds
159+
%type<node>alter_table_cmdalter_rel_cmd
160+
%type<list>alter_table_cmdsalter_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+
|ALTERINDEXrelation_expralter_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-
|OWNERTOUserId
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
|CLUSTERONname
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+
OWNERTOUserId
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
|SETTABLESPACEname
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
caseOBJECT_GROUP:
12701270
tag="ALTER GROUP";
12711271
break;
1272+
caseOBJECT_INDEX:
1273+
tag="ALTER INDEX";
1274+
break;
12721275
caseOBJECT_LANGUAGE:
12731276
tag="ALTER LANGUAGE";
12741277
break;
@@ -1331,9 +1334,21 @@ CreateCommandTag(Node *parsetree)
13311334
break;
13321335

13331336
caseT_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
caseT_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
staticconstchar*constlist_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+
elseif (pg_strcasecmp(prev3_wd,"ALTER")==0&&
652+
pg_strcasecmp(prev2_wd,"INDEX")==0)
653+
{
654+
staticconstchar*constlist_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
elseif (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
NodeTagtype;
798798
RangeVar*relation;/* table to work on */
799799
List*cmds;/* list of subcommands */
800+
ObjectTyperelkind;/* type of object */
800801
}AlterTableStmt;
801802

802803
typedefenumAlterTableType

0 commit comments

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