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

Commitaf4de81

Browse files
committed
ALTER TABLE SET TABLESPACE. Gavin Sherry, some rework by Tom Lane.
1 parent08d89db commitaf4de81

File tree

6 files changed

+392
-63
lines changed

6 files changed

+392
-63
lines changed

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

Lines changed: 66 additions & 34 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.72 2004/06/02 21:04:40 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.73 2004/07/11 23:13:51 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -39,10 +39,11 @@ where <replaceable class="PARAMETER">action</replaceable> is one of:
3939
ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
4040
ADD <replaceable class="PARAMETER">table_constraint</replaceable>
4141
DROP CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> [ RESTRICT | CASCADE ]
42-
SET WITHOUT OIDS
43-
OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
4442
CLUSTER ON <replaceable class="PARAMETER">index_name</replaceable>
4543
SET WITHOUT CLUSTER
44+
SET WITHOUT OIDS
45+
OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
46+
SET TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable>
4647
</synopsis>
4748
</refsynopsisdiv>
4849

@@ -181,6 +182,29 @@ where <replaceable class="PARAMETER">action</replaceable> is one of:
181182
</listitem>
182183
</varlistentry>
183184

185+
<varlistentry>
186+
<term><literal>CLUSTER</literal></term>
187+
<listitem>
188+
<para>
189+
This form selects the default index for future
190+
<xref linkend="SQL-CLUSTER" endterm="sql-cluster-title">
191+
operations. It does not actually re-cluster the table.
192+
</para>
193+
</listitem>
194+
</varlistentry>
195+
196+
<varlistentry>
197+
<term><literal>SET WITHOUT CLUSTER</literal></term>
198+
<listitem>
199+
<para>
200+
This form removes the most recently used
201+
<xref linkend="SQL-CLUSTER" endterm="sql-cluster-title">
202+
index specification from the table. This affects
203+
future cluster operations that don't specify an index.
204+
</para>
205+
</listitem>
206+
</varlistentry>
207+
184208
<varlistentry>
185209
<term><literal>SET WITHOUT OIDS</literal></term>
186210
<listitem>
@@ -211,28 +235,19 @@ where <replaceable class="PARAMETER">action</replaceable> is one of:
211235
</varlistentry>
212236

213237
<varlistentry>
214-
<term><literal>CLUSTER</literal></term>
238+
<term><literal>SET TABLESPACE</literal></term>
215239
<listitem>
216240
<para>
217-
This form selects the default index for future
218-
<xref linkend="SQL-CLUSTER" endterm="sql-cluster-title">
219-
operations.
241+
This form changes the table's tablespace to the specified tablespace and
242+
moves the data file(s) associated with the table to the new tablespace.
243+
Indexes on the table, if any, are not moved; but they can be moved
244+
separately with additional <literal>SET TABLESPACE</literal> commands.
245+
See also
246+
<xref linkend="SQL-CREATETABLESPACE" endterm="sql-createtablespace-title">.
220247
</para>
221248
</listitem>
222249
</varlistentry>
223250

224-
<varlistentry>
225-
<term><literal>SET WITHOUT CLUSTER</literal></term>
226-
<listitem>
227-
<para>
228-
This form removes the most recently used
229-
<xref linkend="SQL-CLUSTER" endterm="sql-cluster-title">
230-
index specification from the table. This affects
231-
future cluster operations that don't specify an index.
232-
</para>
233-
</listitem>
234-
</varlistentry>
235-
236251
<varlistentry>
237252
<term><literal>RENAME</literal></term>
238253
<listitem>
@@ -293,29 +308,29 @@ where <replaceable class="PARAMETER">action</replaceable> is one of:
293308
</varlistentry>
294309

295310
<varlistentry>
296-
<term><replaceable class="PARAMETER">type</replaceable></term>
311+
<term><replaceable class="PARAMETER">new_column</replaceable></term>
297312
<listitem>
298313
<para>
299-
Data type of the new column, or new data type for an existing
300-
column.
314+
New name for an existing column.
301315
</para>
302316
</listitem>
303317
</varlistentry>
304318

305319
<varlistentry>
306-
<term><replaceable class="PARAMETER">new_column</replaceable></term>
320+
<term><replaceable class="PARAMETER">new_name</replaceable></term>
307321
<listitem>
308322
<para>
309-
New name foran existing column.
323+
New name forthe table.
310324
</para>
311325
</listitem>
312326
</varlistentry>
313327

314328
<varlistentry>
315-
<term><replaceable class="PARAMETER">new_name</replaceable></term>
329+
<term><replaceable class="PARAMETER">type</replaceable></term>
316330
<listitem>
317331
<para>
318-
New name for the table.
332+
Data type of the new column, or new data type for an existing
333+
column.
319334
</para>
320335
</listitem>
321336
</varlistentry>
@@ -339,10 +354,21 @@ where <replaceable class="PARAMETER">action</replaceable> is one of:
339354
</varlistentry>
340355

341356
<varlistentry>
342-
<term><replaceable class="PARAMETER">new_owner</replaceable></term>
357+
<term><literal>CASCADE</literal></term>
343358
<listitem>
344359
<para>
345-
The user name of the new owner of the table.
360+
Automatically drop objects that depend on the dropped column
361+
or constraint (for example, views referencing the column).
362+
</para>
363+
</listitem>
364+
</varlistentry>
365+
366+
<varlistentry>
367+
<term><literal>RESTRICT</literal></term>
368+
<listitem>
369+
<para>
370+
Refuse to drop the column or constraint if there are any dependent
371+
objects. This is the default behavior.
346372
</para>
347373
</listitem>
348374
</varlistentry>
@@ -357,21 +383,19 @@ where <replaceable class="PARAMETER">action</replaceable> is one of:
357383
</varlistentry>
358384

359385
<varlistentry>
360-
<term><literal>CASCADE</literal></term>
386+
<term><replaceable class="PARAMETER">new_owner</replaceable></term>
361387
<listitem>
362388
<para>
363-
Automatically drop objects that depend on the dropped column
364-
or constraint (for example, views referencing the column).
389+
The user name of the new owner of the table.
365390
</para>
366391
</listitem>
367392
</varlistentry>
368393

369394
<varlistentry>
370-
<term><literal>RESTRICT</literal></term>
395+
<term><replaceable class="PARAMETER">tablespace_name</replaceable></term>
371396
<listitem>
372397
<para>
373-
Refuse to drop the column or constraint if there are any dependent
374-
objects. This is the default behavior.
398+
The tablespace name to which the table will be moved.
375399
</para>
376400
</listitem>
377401
</varlistentry>
@@ -551,6 +575,14 @@ ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zip
551575
ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
552576
</programlisting>
553577
</para>
578+
579+
<para>
580+
To move a table to a different tablespace:
581+
<programlisting>
582+
ALTER TABLE distributors SET TABLESPACE fasttablespace;
583+
</programlisting>
584+
</para>
585+
554586
</refsect1>
555587

556588
<refsect1>

‎src/backend/commands/cluster.c‎

Lines changed: 27 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
*
1212
*
1313
* IDENTIFICATION
14-
* $PostgreSQL: pgsql/src/backend/commands/cluster.c,v 1.126 2004/06/18 06:13:22 tgl Exp $
14+
* $PostgreSQL: pgsql/src/backend/commands/cluster.c,v 1.127 2004/07/11 23:13:53 tgl Exp $
1515
*
1616
*-------------------------------------------------------------------------
1717
*/
@@ -485,6 +485,7 @@ static void
485485
rebuild_relation(RelationOldHeap,OidindexOid)
486486
{
487487
OidtableOid=RelationGetRelid(OldHeap);
488+
OidtableSpace=OldHeap->rd_rel->reltablespace;
488489
OidOIDNewHeap;
489490
charNewHeapName[NAMEDATALEN];
490491
ObjectAddressobject;
@@ -505,7 +506,7 @@ rebuild_relation(Relation OldHeap, Oid indexOid)
505506
*/
506507
snprintf(NewHeapName,sizeof(NewHeapName),"pg_temp_%u",tableOid);
507508

508-
OIDNewHeap=make_new_heap(tableOid,NewHeapName);
509+
OIDNewHeap=make_new_heap(tableOid,NewHeapName,tableSpace);
509510

510511
/*
511512
* We don't need CommandCounterIncrement() because make_new_heap did
@@ -520,8 +521,8 @@ rebuild_relation(Relation OldHeap, Oid indexOid)
520521
/* To make the new heap's data visible (probably not needed?). */
521522
CommandCounterIncrement();
522523

523-
/* Swap therelfilenodes of the old and new heaps. */
524-
swap_relfilenodes(tableOid,OIDNewHeap);
524+
/* Swap thephysical files of the old and new heaps. */
525+
swap_relation_files(tableOid,OIDNewHeap);
525526

526527
CommandCounterIncrement();
527528

@@ -550,7 +551,7 @@ rebuild_relation(Relation OldHeap, Oid indexOid)
550551
* Create the new table that we will fill with correctly-ordered data.
551552
*/
552553
Oid
553-
make_new_heap(OidOIDOldHeap,constchar*NewName)
554+
make_new_heap(OidOIDOldHeap,constchar*NewName,OidNewTableSpace)
554555
{
555556
TupleDescOldHeapDesc,
556557
tupdesc;
@@ -568,7 +569,7 @@ make_new_heap(Oid OIDOldHeap, const char *NewName)
568569

569570
OIDNewHeap=heap_create_with_catalog(NewName,
570571
RelationGetNamespace(OldHeap),
571-
OldHeap->rd_rel->reltablespace,
572+
NewTableSpace,
572573
tupdesc,
573574
OldHeap->rd_rel->relkind,
574575
OldHeap->rd_rel->relisshared,
@@ -646,13 +647,16 @@ copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex)
646647
}
647648

648649
/*
649-
* Swap the relfilenodes for two given relations.
650+
* Swap the physical files of two given relations.
651+
*
652+
* We swap the physical identity (reltablespace and relfilenode) while
653+
* keeping the same logical identities of the two relations.
650654
*
651655
* Also swap any TOAST links, so that the toast data moves along with
652656
* the main-table data.
653657
*/
654658
void
655-
swap_relfilenodes(Oidr1,Oidr2)
659+
swap_relation_files(Oidr1,Oidr2)
656660
{
657661
RelationrelRelation,
658662
rel;
@@ -695,12 +699,16 @@ swap_relfilenodes(Oid r1, Oid r2)
695699
relation_close(rel,NoLock);
696700

697701
/*
698-
* Actually swap thefilenode and TOASTfields in the two tuples
702+
* Actually swap the fields in the two tuples
699703
*/
700704
swaptemp=relform1->relfilenode;
701705
relform1->relfilenode=relform2->relfilenode;
702706
relform2->relfilenode=swaptemp;
703707

708+
swaptemp=relform1->reltablespace;
709+
relform1->reltablespace=relform2->reltablespace;
710+
relform2->reltablespace=swaptemp;
711+
704712
swaptemp=relform1->reltoastrelid;
705713
relform1->reltoastrelid=relform2->reltoastrelid;
706714
relform2->reltoastrelid=swaptemp;
@@ -793,13 +801,16 @@ swap_relfilenodes(Oid r1, Oid r2)
793801

794802
/*
795803
* Blow away the old relcache entries now.We need this kluge because
796-
* relcache.c indexes relcache entries by rd_node as well as OID. It
797-
* will get confused if it is asked to (re)build an entry with a new
798-
* rd_node value when there is still another entry laying about with
799-
* that same rd_node value. (Fortunately, since one of the entries is
800-
* local in our transaction, it's sufficient to clear out our own
801-
* relcache this way; the problem cannot arise for other backends when
802-
* they see our update on the non-local relation.)
804+
* relcache.c keeps a link to the smgr relation for the physical file,
805+
* and that will be out of date as soon as we do CommandCounterIncrement.
806+
* Whichever of the rels is the second to be cleared during cache
807+
* invalidation will have a dangling reference to an already-deleted smgr
808+
* relation. Rather than trying to avoid this by ordering operations
809+
* just so, it's easiest to not have the relcache entries there at all.
810+
* (Fortunately, since one of the entries is local in our transaction,
811+
* it's sufficient to clear out our own relcache this way; the problem
812+
* cannot arise for other backends when they see our update on the
813+
* non-local relation.)
803814
*/
804815
RelationForgetRelation(r1);
805816
RelationForgetRelation(r2);

0 commit comments

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