Hi all,
Attached is an updated ALTER TABLE ... SET TABLESPACE patch.
It uses the block by block copy mechanism proposed by Tom and handles i)
ALTER TABLE <index> .... and ii) Copying of TOAST tables and the TOAST
table's index.
It doesn't handle copying of system tables (pg_largeobject) and, in the
interests of code reuse, the patch fiddles with the code used by CLUSTER.
This isn't great but I wanted to get a patch in before 1 July since I
think the feature is very important -- even for the first release.
Thanks,
Gavin
Index: doc/src/sgml/ref/alter_table.sgml
===================================================================
RCS file: /usr/local/cvsroot/pgsql-server/doc/src/sgml/ref/alter_table.sgml,v
retrieving revision 1.72
diff -2 -c -r1.72 alter_table.sgml
*** doc/src/sgml/ref/alter_table.sgml 2 Jun 2004 21:04:40 -0000 1.72
--- doc/src/sgml/ref/alter_table.sgml 20 Jun 2004 02:54:30 -0000
***************
*** 44,47 ****
--- 44,48 ----
CLUSTER ON <replaceable class="PARAMETER">index_name</replaceable>
SET WITHOUT CLUSTER
+ SET TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable>
</synopsis>
</refsynopsisdiv>
***************
*** 233,237 ****
</listitem>
</varlistentry>
!
<varlistentry>
<term><literal>RENAME</literal></term>
--- 234,250 ----
</listitem>
</varlistentry>
!
! <varlistentry>
! <term><literal>SET TABLESPACE</literal></term>
! <listitem>
! <para>
! This form changes the table's tablespace to the specified tablespace and
! moves the data file(s) associated with the table to the new tablespace.
! See also
! <xref linkend="SQL-CREATETABLESPACE" endterm="sql-createtablespace-title">.
! </para>
! </listitem>
! </varlistentry>
!
<varlistentry>
<term><literal>RENAME</literal></term>
***************
*** 358,361 ****
--- 371,382 ----
<varlistentry>
+ <term><replaceable class="PARAMETER">tablespace_name</replaceable></term>
+ <listitem>
+ <para>
+ The tablespace name to which the table will be moved.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
<term><literal>CASCADE</literal></term>
<listitem>
***************
*** 552,555 ****
--- 573,584 ----
</programlisting>
</para>
+
+ <para>
+ To move a table to a different tablespace:
+ <programlisting>
+ ALTER TABLE distributors SET TABLESPACE fasttablespace;
+ </programlisting>
+ </para>
+
</refsect1>
Index: src/backend/commands/cluster.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql-server/src/backend/commands/cluster.c,v
retrieving revision 1.126
diff -2 -c -r1.126 cluster.c
*** src/backend/commands/cluster.c 18 Jun 2004 06:13:22 -0000 1.126
--- src/backend/commands/cluster.c 30 Jun 2004 14:48:06 -0000
***************
*** 506,510 ****
snprintf(NewHeapName, sizeof(NewHeapName), "pg_temp_%u", tableOid);
! OIDNewHeap = make_new_heap(tableOid, NewHeapName);
/*
--- 506,512 ----
snprintf(NewHeapName, sizeof(NewHeapName), "pg_temp_%u", tableOid);
! OIDNewHeap = make_new_heap(tableOid, NewHeapName,
! OldHeap->rd_rel->reltablespace, allowSystemTableMods,
! true);
/*
***************
*** 522,526 ****
/* Swap the relfilenodes of the old and new heaps. */
! swap_relfilenodes(tableOid, OIDNewHeap);
CommandCounterIncrement();
--- 524,528 ----
/* Swap the relfilenodes of the old and new heaps. */
! swap_relfilenodes(tableOid, OIDNewHeap, true);
CommandCounterIncrement();
***************
*** 551,555 ****
*/
Oid
! make_new_heap(Oid OIDOldHeap, const char *NewName)
{
TupleDesc OldHeapDesc,
--- 553,558 ----
*/
Oid
! make_new_heap(Oid OIDOldHeap, const char *NewName, Oid newtablespaceId,
! bool allow_system_table_mods, bool create_toast)
{
TupleDesc OldHeapDesc,
***************
*** 558,562 ****
Relation OldHeap;
! OldHeap = heap_open(OIDOldHeap, AccessExclusiveLock);
OldHeapDesc = RelationGetDescr(OldHeap);
--- 561,565 ----
Relation OldHeap;
! OldHeap = relation_open(OIDOldHeap, AccessExclusiveLock);
OldHeapDesc = RelationGetDescr(OldHeap);
***************
*** 568,580 ****
OIDNewHeap = heap_create_with_catalog(NewName,
!
RelationGetNamespace(OldHeap),
! OldHeap->rd_rel->reltablespace,
!
tupdesc,
!
OldHeap->rd_rel->relkind,
!
OldHeap->rd_rel->relisshared,
! true,
! 0,
!
ONCOMMIT_NOOP,
!
allowSystemTableMods);
/*
--- 571,584 ----
OIDNewHeap = heap_create_with_catalog(NewName,
! RelationGetNamespace(OldHeap),
! newtablespaceId,
! tupdesc,
! OldHeap->rd_rel->relkind ==
RELKIND_INDEX ?
! RELKIND_RELATION :
OldHeap->rd_rel->relkind,
! OldHeap->rd_rel->relisshared,
! true,
! 0,
! ONCOMMIT_NOOP,
! allow_system_table_mods);
/*
***************
*** 589,595 ****
* that the TOAST table will be visible for insertion.
*/
! AlterTableCreateToastTable(OIDNewHeap, true);
! heap_close(OldHeap, NoLock);
return OIDNewHeap;
--- 593,600 ----
* that the TOAST table will be visible for insertion.
*/
! if(create_toast)
! AlterTableCreateToastTable(OIDNewHeap, true);
! relation_close(OldHeap, NoLock);
return OIDNewHeap;
***************
*** 649,657 ****
* Swap the relfilenodes for two given relations.
*
! * Also swap any TOAST links, so that the toast data moves along with
! * the main-table data.
*/
void
! swap_relfilenodes(Oid r1, Oid r2)
{
Relation relRelation,
--- 654,661 ----
* Swap the relfilenodes for two given relations.
*
! * Swap tablespace oids, since we use this for ALTER TALE SET TABLESPACE
*/
void
! swap_relfilenodes(Oid r1, Oid r2, bool swap_toast)
{
Relation relRelation,
***************
*** 696,700 ****
/*
! * Actually swap the filenode and TOAST fields in the two tuples
*/
swaptemp = relform1->relfilenode;
--- 700,704 ----
/*
! * Actually swap the filenode and tablespace in the two tuples
*/
swaptemp = relform1->relfilenode;
***************
*** 702,708 ****
relform2->relfilenode = swaptemp;
! swaptemp = relform1->reltoastrelid;
! relform1->reltoastrelid = relform2->reltoastrelid;
! relform2->reltoastrelid = swaptemp;
/* we should not swap reltoastidxid */
--- 706,719 ----
relform2->relfilenode = swaptemp;
! if(swap_toast)
! {
! swaptemp = relform1->reltoastrelid;
! relform1->reltoastrelid = relform2->reltoastrelid;
! relform2->reltoastrelid = swaptemp;
! }
!
! swaptemp = relform1->reltablespace;
! relform1->reltablespace = relform2->reltablespace;
! relform2->reltablespace = swaptemp;
/* we should not swap reltoastidxid */
Index: src/backend/commands/tablecmds.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql-server/src/backend/commands/tablecmds.c,v
retrieving revision 1.117
diff -2 -c -r1.117 tablecmds.c
*** src/backend/commands/tablecmds.c 25 Jun 2004 21:55:53 -0000 1.117
--- src/backend/commands/tablecmds.c 30 Jun 2004 14:48:50 -0000
***************
*** 53,56 ****
--- 53,57 ----
#include "parser/parse_type.h"
#include "rewrite/rewriteHandler.h"
+ #include "storage/smgr.h"
#include "utils/acl.h"
#include "utils/builtins.h"
***************
*** 231,234 ****
--- 232,236 ----
bool recurse, bool
recursing,
AlterTableCmd *cmd);
+ static void ATPrepSetTablespace(List **wqueue, Relation rel, char *name);
static void ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
const char *colName,
TypeName *typename);
***************
*** 238,241 ****
--- 240,244 ----
static void ATExecClusterOn(Relation rel, const char *indexName);
static void ATExecDropCluster(Relation rel);
+ static void ATExecSetTablespace(Relation rel, char *name);
static int ri_trigger_type(Oid tgfoid);
static void update_ri_trigger_args(Oid relid,
***************
*** 244,247 ****
--- 247,253 ----
bool fk_scan,
bool update_relname);
+ static void copy_heap(Relation old, Relation new);
+ static Oid make_new_table(Oid OIDOldHeap, const char *NewName, Oid newtablespaceId);
+ static void create_new_rel(Relation old, Oid tablespaceId);
***************
*** 1947,1950 ****
--- 1953,1961 ----
pass = AT_PASS_DROP;
break;
+ case AT_SetTablespace: /* SET TABLESPACE */
+ /* Don't do permissions check if we're recursing */
+ ATPrepSetTablespace(wqueue, rel, cmd->name);
+ pass = AT_PASS_MISC;
+ break;
default: /* oops */
elog(ERROR, "unrecognized alter table type: %d",
***************
*** 2098,2101 ****
--- 2109,2115 ----
*/
break;
+ case AT_SetTablespace: /* SET TABLESPACE */
+ ATExecSetTablespace(rel, cmd->name);
+ break;
default: /* oops */
elog(ERROR, "unrecognized alter table type: %d",
***************
*** 2171,2175 ****
"pg_temp_%u", tab->relid);
! OIDNewHeap = make_new_heap(tab->relid, NewHeapName);
/*
--- 2185,2190 ----
"pg_temp_%u", tab->relid);
! OIDNewHeap = make_new_heap(tab->relid, NewHeapName,
! OldHeap->rd_rel->reltablespace, allowSystemTableMods,
true);
/*
***************
*** 2181,2185 ****
/* Swap the relfilenodes of the old and new heaps. */
! swap_relfilenodes(tab->relid, OIDNewHeap);
CommandCounterIncrement();
--- 2196,2200 ----
/* Swap the relfilenodes of the old and new heaps. */
! swap_relfilenodes(tab->relid, OIDNewHeap, true);
CommandCounterIncrement();
***************
*** 4614,4617 ****
--- 4629,4668 ----
}
+
+ /* ALTER TABLE SET TABLESPACE */
+
+ static void
+ ATPrepSetTablespace(List **wqueue, Relation rel, char *name)
+ {
+ AclResult aclresult;
+ Oid tablespaceId;
+
+ /* Permissions checks */
+ if (!pg_class_ownercheck(RelationGetRelid(rel), GetUserId()))
+ aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS,
+ RelationGetRelationName(rel));
+
+ if (!allowSystemTableMods && IsSystemRelation(rel))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied: \"%s\" is a system
catalog",
+ RelationGetRelationName(rel))));
+
+ /* Check that the tablespace exists */
+
+ tablespaceId = get_tablespace_oid(name);
+ if (!OidIsValid(tablespaceId))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("tablespace \"%s\" does not exist", name)));
+
+ /* check permissions */
+ aclresult = pg_tablespace_aclcheck(tablespaceId, GetUserId(),
+
ACL_CREATE);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, ACL_KIND_TABLESPACE,
+ name);
+ }
+
static void
ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
***************
*** 5186,5189 ****
--- 5237,5379 ----
}
+ /*
+ * ALTER TABLE SET TABLESPACE
+ *
+ * Update the table with new tablespace and move table (heap) data
+ * to new tablespace
+ */
+ static void
+ ATExecSetTablespace(Relation rel, char *name)
+ {
+ Oid tableOid = RelationGetRelid(rel);
+ Oid tablespaceId;
+ Oid toastoid = InvalidOid;
+
+ tablespaceId = get_tablespace_oid(name);
+ if(!OidIsValid(tablespaceId))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("tablespace \"%s\" does not exist",
+ name)));
+
+ /*
+ * See if its already in the tablespace
+ * XXX: is there an SQL state for this?
+ */
+
+ if(tablespaceId == rel->rd_rel->reltablespace)
+ elog(ERROR, "table \"%s\" is already in tablespace \"%s\"",
+ RelationGetRelationName(rel), name);
+
+
+ if(rel->rd_rel->relkind != RELKIND_RELATION &&
+ rel->rd_rel->relkind != RELKIND_INDEX)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("object \"%s\" must be a table or index",
+ RelationGetRelationName(rel))));
+ /* Save toast OID now as we can't rely on it after create_new_rel() */
+
+ toastoid = rel->rd_rel->reltoastrelid;
+ create_new_rel(rel, tablespaceId);
+
+ /* may not have toast or may be an index */
+ if(OidIsValid(toastoid))
+ {
+ Relation toastrel;
+ Oid toastidxoid;
+
+ toastrel = relation_open(toastoid, AccessExclusiveLock);
+ toastidxoid = toastrel->rd_rel->reltoastidxid;
+ create_new_rel(toastrel, tablespaceId);
+
+ if(OidIsValid(toastidxoid))
+ {
+ Relation toastidx;
+ toastidx = relation_open(toastidxoid, AccessExclusiveLock);
+ create_new_rel(toastidx, tablespaceId);
+ }
+ }
+ }
+
+ /*
+ * Copy data in one relnode to another without losing OIDs and other defaults
+ */
+
+ static void
+ create_new_rel(Relation old, Oid tablespaceId)
+ {
+ Relation new;
+ Oid newoid,
+ oldoid;
+ ObjectAddress object;
+
+ char newname[NAMEDATALEN];
+
+ oldoid = RelationGetRelid(old);
+
+ snprintf(newname, sizeof(newname), "pg_temp_%u",
+ oldoid);
+
+ /*
+ * We tell make_new_heap() NOT to create a TOAST table as we will
+ * continue to use the existing one
+ */
+ newoid = make_new_heap(oldoid, newname, tablespaceId, true, false);
+
+ new = relation_open(newoid, AccessExclusiveLock);
+
+ copy_heap(old, new);
+
+ /* We have to clcose becore we call swap_relfilenodes() */
+
+ relation_close(old, NoLock);
+ relation_close(new, NoLock);
+
+ /* Swap the relfilenodes of the old and new heaps. */
+ swap_relfilenodes(oldoid, newoid, false);
+
+ CommandCounterIncrement();
+
+ /* Destroy new heap with old filenode */
+
+ object.classId = RelOid_pg_class;
+ object.objectId = newoid;
+ object.objectSubId = 0;
+
+ /*
+ * The new relation is local to our transaction and we know nothing
+ * depends on it, so DROP_RESTRICT should be OK.
+ */
+ performDeletion(&object, DROP_RESTRICT);
+
+ /* performDeletion does CommandCounterIncrement at end */
+ }
+
+ /*
+ * Copy old, block by block, to new
+ */
+
+ static void
+ copy_heap(Relation old, Relation new)
+ {
+ BlockNumber b;
+
+ if(!old->rd_smgr)
+ old->rd_smgr = smgropen(old->rd_node);
+
+ if(!new->rd_smgr)
+ new->rd_smgr = smgropen(new->rd_node);
+
+ for(b = 0; b < RelationGetNumberOfBlocks(old); b++)
+ {
+ char buf[BLCKSZ];
+
+ smgrread(old->rd_smgr, b, buf);
+ smgrwrite(new->rd_smgr, b, buf, new->rd_istemp);
+
+ /* Add WAL */
+ }
+ }
/*
Index: src/backend/parser/gram.y
===================================================================
RCS file: /usr/local/cvsroot/pgsql-server/src/backend/parser/gram.y,v
retrieving revision 2.465
diff -2 -c -r2.465 gram.y
*** src/backend/parser/gram.y 28 Jun 2004 01:19:11 -0000 2.465
--- src/backend/parser/gram.y 28 Jun 2004 05:56:11 -0000
***************
*** 1287,1290 ****
--- 1287,1297 ----
$$ = (Node *)n;
}
+ | SET TABLESPACE name
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_SetTablespace;
+ n->name = $3;
+ $$ = (Node *)n;
+ }
;
Index: src/include/commands/cluster.h
===================================================================
RCS file: /usr/local/cvsroot/pgsql-server/src/include/commands/cluster.h,v
retrieving revision 1.23
diff -2 -c -r1.23 cluster.h
*** src/include/commands/cluster.h 8 May 2004 00:34:49 -0000 1.23
--- src/include/commands/cluster.h 30 Jun 2004 14:47:46 -0000
***************
*** 22,27 ****
extern void check_index_is_clusterable(Relation OldHeap, Oid indexOid);
extern void mark_index_clustered(Relation rel, Oid indexOid);
! extern Oid make_new_heap(Oid OIDOldHeap, const char *NewName);
! extern void swap_relfilenodes(Oid r1, Oid r2);
#endif /* CLUSTER_H */
--- 22,28 ----
extern void check_index_is_clusterable(Relation OldHeap, Oid indexOid);
extern void mark_index_clustered(Relation rel, Oid indexOid);
! extern Oid make_new_heap(Oid OIDOldHeap, const char *NewName,
! Oid tablespaceId, bool allow_system_table_mods, bool create_toast);
! extern void swap_relfilenodes(Oid r1, Oid r2, bool swap_toast);
#endif /* CLUSTER_H */
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /usr/local/cvsroot/pgsql-server/src/include/nodes/parsenodes.h,v
retrieving revision 1.260
diff -2 -c -r1.260 parsenodes.h
*** src/include/nodes/parsenodes.h 25 Jun 2004 21:55:59 -0000 1.260
--- src/include/nodes/parsenodes.h 28 Jun 2004 05:56:18 -0000
***************
*** 807,811 ****
AT_ClusterOn, /* CLUSTER ON */
AT_DropCluster, /* SET WITHOUT CLUSTER */
! AT_DropOids /* SET WITHOUT OIDS */
} AlterTableType;
--- 807,812 ----
AT_ClusterOn, /* CLUSTER ON */
AT_DropCluster, /* SET WITHOUT CLUSTER */
! AT_DropOids, /* SET WITHOUT OIDS */
! AT_SetTablespace /* SET TABLESPACE */
} AlterTableType;
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
