Hi,
Here's a patch to allow TOAST tables to be moved to a different
tablespace. This item has been picked up from the TODO list.
Main idea is to consider that a TOAST table can have its own tablespace.
Regards,
--
JT
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 00a477e..a2360f4 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -66,6 +66,8 @@ ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
NOT OF
OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
SET TABLESPACE <replaceable class="PARAMETER">new_tablespace</replaceable>
+ SET TABLE TABLESPACE <replaceable class="PARAMETER">new_tablespace</replaceable>
+ SET TOAST TABLESPACE <replaceable class="PARAMETER">new_tablespace</replaceable>
<phrase>and <replaceable class="PARAMETER">table_constraint_using_index</replaceable> is:</phrase>
@@ -549,6 +551,30 @@ ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>SET TABLE TABLESPACE</literal></term>
+ <listitem>
+ <para>
+ This form changes only table's tablespace (not associated TOAST table's tablespace)
+ to the specified tablespace and moves the data file(s) associated to the new tablespace.
+ See also
+ <xref linkend="SQL-CREATETABLESPACE">
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>SET TOAST TABLESPACE</literal></term>
+ <listitem>
+ <para>
+ This form changes the TOAST table's tablespace to the specified tablespace and
+ moves the data file(s) associated with the TOAST table to the new tablespace.
+ See also
+ <xref linkend="SQL-CREATETABLESPACE">
+ </para>
+ </listitem>
+ </varlistentry>
<varlistentry>
<term><literal>RENAME</literal></term>
diff --git a/doc/src/sgml/storage.sgml b/doc/src/sgml/storage.sgml
index 0a133bb..d7d4235 100644
--- a/doc/src/sgml/storage.sgml
+++ b/doc/src/sgml/storage.sgml
@@ -422,6 +422,11 @@ pages). There was no run time difference compared to an un-<acronym>TOAST</>ed
comparison table, in which all the HTML pages were cut down to 7 kB to fit.
</para>
+<para>
+TOAST table can be moved to a different tablespace with
+<command>ALTER TABLE SET TOAST TABLESPACE</>
+</para>
+
</sect1>
<sect1 id="storage-fsm">
diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c
index a938c98..7ad965e 100644
--- a/src/backend/catalog/toasting.c
+++ b/src/backend/catalog/toasting.c
@@ -36,7 +36,7 @@ extern Oid binary_upgrade_next_toast_pg_class_oid;
Oid binary_upgrade_next_toast_pg_type_oid = InvalidOid;
static bool create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
- Datum reloptions);
+ Datum reloptions, Oid toastTableSpace);
static bool needs_toast_table(Relation rel);
@@ -53,19 +53,30 @@ static bool needs_toast_table(Relation rel);
* to end with CommandCounterIncrement if it makes any changes.
*/
void
-AlterTableCreateToastTable(Oid relOid, Datum reloptions)
+AlterTableCreateToastTable(Oid relOid, Datum reloptions, Oid toastTableSpace)
{
Relation rel;
-
+ Relation toast_rel;
/*
* Grab a DDL-exclusive lock on the target table, since we'll update the
* pg_class tuple. This is redundant for all present users. Tuple
* toasting behaves safely in the face of a concurrent TOAST table add.
*/
rel = heap_open(relOid, ShareUpdateExclusiveLock);
+
+ /*
+ * if NewToastTableSpace is null then try to find old TOAST table's tablespace
+ */
+ if (!OidIsValid(toastTableSpace) && OidIsValid(rel->rd_rel->reltoastrelid))
+ {
+ toast_rel = relation_open(rel->rd_rel->reltoastrelid, NoLock);
+ if (OidIsValid(toast_rel->rd_rel->reltablespace))
+ toastTableSpace = toast_rel->rd_rel->reltablespace;
+ relation_close(toast_rel, NoLock);
+ }
/* create_toast_table does all the work */
- (void) create_toast_table(rel, InvalidOid, InvalidOid, reloptions);
+ (void) create_toast_table(rel, InvalidOid, InvalidOid, reloptions, toastTableSpace);
heap_close(rel, NoLock);
}
@@ -91,7 +102,7 @@ BootstrapToastTable(char *relName, Oid toastOid, Oid toastIndexOid)
relName)));
/* create_toast_table does all the work */
- if (!create_toast_table(rel, toastOid, toastIndexOid, (Datum) 0))
+ if (!create_toast_table(rel, toastOid, toastIndexOid, (Datum) 0,InvalidOid))
elog(ERROR, "\"%s\" does not require a toast table",
relName);
@@ -107,7 +118,7 @@ BootstrapToastTable(char *relName, Oid toastOid, Oid toastIndexOid)
* bootstrap they can be nonzero to specify hand-assigned OIDs
*/
static bool
-create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid, Datum reloptions)
+create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid, Datum reloptions, Oid toastTableSpace)
{
Oid relOid = RelationGetRelid(rel);
HeapTuple reltup;
@@ -207,10 +218,15 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid, Datum reloptio
toast_typid = binary_upgrade_next_toast_pg_type_oid;
binary_upgrade_next_toast_pg_type_oid = InvalidOid;
}
+
+ /* Use table's tablespace if toastTableSpace is null */
+ if (!OidIsValid(toastTableSpace))
+ toastTableSpace = rel->rd_rel->reltablespace;
+
toast_relid = heap_create_with_catalog(toast_relname,
namespaceid,
- rel->rd_rel->reltablespace,
+ toastTableSpace,
toastOid,
toast_typid,
InvalidOid,
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index 8200d20..bd23c8b 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -543,6 +543,8 @@ rebuild_relation(Relation OldHeap, Oid indexOid,
bool is_system_catalog;
bool swap_toast_by_content;
TransactionId frozenXid;
+ Oid ToastTableSpace;
+ Relation ToastRel;
/* Mark the correct index as clustered */
if (OidIsValid(indexOid))
@@ -551,11 +553,25 @@ rebuild_relation(Relation OldHeap, Oid indexOid,
/* Remember if it's a system catalog */
is_system_catalog = IsSystemRelation(OldHeap);
+ /*
+ * Verifiy if a TOASTed relation exists and is a valid relation
+ * If true, keep its previous tablespace in memory to rebuild it in
+ * the same tablespace
+ */
+ if (OidIsValid(OldHeap->rd_rel->reltoastrelid))
+ {
+ ToastRel = relation_open(OldHeap->rd_rel->reltoastrelid, NoLock);
+ ToastTableSpace = ToastRel->rd_rel->reltablespace;
+ relation_close(ToastRel, NoLock);
+ }
+ else
+ ToastTableSpace = tableSpace;
+
/* Close relcache entry, but keep lock until transaction commit */
heap_close(OldHeap, NoLock);
/* Create the transient table that will receive the re-ordered data */
- OIDNewHeap = make_new_heap(tableOid, tableSpace);
+ OIDNewHeap = make_new_heap(tableOid, tableSpace, ToastTableSpace);
/* Copy the heap data into the new table in the desired order */
copy_heap_data(OIDNewHeap, tableOid, indexOid,
@@ -581,7 +597,7 @@ rebuild_relation(Relation OldHeap, Oid indexOid,
* data, then call finish_heap_swap to complete the operation.
*/
Oid
-make_new_heap(Oid OIDOldHeap, Oid NewTableSpace)
+make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, Oid NewToastTableSpace)
{
TupleDesc OldHeapDesc;
char NewHeapName[NAMEDATALEN];
@@ -679,7 +695,7 @@ make_new_heap(Oid OIDOldHeap, Oid NewTableSpace)
if (isNull)
reloptions = (Datum) 0;
- AlterTableCreateToastTable(OIDNewHeap, reloptions);
+ AlterTableCreateToastTable(OIDNewHeap, reloptions, NewToastTableSpace);
ReleaseSysCache(tuple);
}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 1e8ad2b..fa6d1a8 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -147,7 +147,8 @@ typedef struct AlteredTableInfo
List *newvals; /* List of NewColumnValue */
bool new_notnull; /* T if we added new NOT NULL constraints */
bool rewrite; /* T if a rewrite is forced */
- Oid newTableSpace; /* new tablespace; 0 means no change */
+ Oid newTableSpace; /* new tablespace; 0 means no change */
+ Oid newToastTableSpace; /* new TOAST tablespace; 0 means no change */
/* Objects to rebuild after completing ALTER TYPE operations */
List *changedConstraintOids; /* OIDs of constraints to rebuild */
List *changedConstraintDefs; /* string definitions of same */
@@ -356,8 +357,11 @@ static void change_owner_recurse_to_sequences(Oid relationOid,
static void ATExecClusterOn(Relation rel, const char *indexName, LOCKMODE lockmode);
static void ATExecDropCluster(Relation rel, LOCKMODE lockmode);
static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel,
- char *tablespacename, LOCKMODE lockmode);
-static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode);
+ char *tablespacename, LOCKMODE lockmode, bool table_only);
+static void ATPrepSetToastTableSpace(AlteredTableInfo *tab, Relation rel,
+ char *tablespacename, LOCKMODE lockmode);
+static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode, Oid newToastTableSpace);
+static void ATExecSetToastTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode);
static void ATExecSetRelOptions(Relation rel, List *defList, bool isReset, LOCKMODE lockmode);
static void ATExecEnableDisableTrigger(Relation rel, char *trigname,
char fires_when, bool skip_system, LOCKMODE lockmode);
@@ -2980,7 +2984,19 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
case AT_SetTableSpace: /* SET TABLESPACE */
ATSimplePermissions(rel, ATT_TABLE | ATT_INDEX);
/* This command never recurses */
- ATPrepSetTableSpace(tab, rel, cmd->name, lockmode);
+ ATPrepSetTableSpace(tab, rel, cmd->name, lockmode, false);
+ pass = AT_PASS_MISC; /* doesn't actually matter */
+ break;
+ case AT_SetTableTableSpace: /* SET TABLE TABLESPACE */
+ ATSimplePermissions(rel, ATT_TABLE | ATT_INDEX);
+ /* This command never recurses */
+ ATPrepSetTableSpace(tab, rel, cmd->name, lockmode, true);
+ pass = AT_PASS_MISC; /* doesn't actually matter */
+ break;
+ case AT_SetToastTableSpace: /* SET TOAST TABLESPACE */
+ ATSimplePermissions(rel, ATT_TABLE | ATT_INDEX);
+ /* This command never recurses */
+ ATPrepSetToastTableSpace(tab, rel, cmd->name, lockmode);
pass = AT_PASS_MISC; /* doesn't actually matter */
break;
case AT_SetRelOptions: /* SET (...) */
@@ -3099,7 +3115,7 @@ ATRewriteCatalogs(List **wqueue, LOCKMODE lockmode)
AlteredTableInfo *tab = (AlteredTableInfo *) lfirst(ltab);
if (tab->relkind == RELKIND_RELATION)
- AlterTableCreateToastTable(tab->relid, (Datum) 0);
+ AlterTableCreateToastTable(tab->relid, (Datum) 0, InvalidOid);
}
}
@@ -3227,6 +3243,18 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
* Nothing to do here; Phase 3 does the work
*/
break;
+ case AT_SetTableTableSpace: /* SET TABLE TABLESPACE */
+
+ /*
+ * Nothing to do here; Phase 3 does the work
+ */
+ break;
+ case AT_SetToastTableSpace: /* SET TOAST TABLESPACE */
+
+ /*
+ * Nothing to do here; Phase 3 does the work
+ */
+ break;
case AT_SetRelOptions: /* SET (...) */
ATExecSetRelOptions(rel, (List *) cmd->def, false, lockmode);
break;
@@ -3362,6 +3390,7 @@ ATRewriteTables(List **wqueue, LOCKMODE lockmode)
Relation OldHeap;
Oid OIDNewHeap;
Oid NewTableSpace;
+ Oid NewToastTableSpace;
OldHeap = heap_open(tab->relid, NoLock);
@@ -3393,11 +3422,16 @@ ATRewriteTables(List **wqueue, LOCKMODE lockmode)
NewTableSpace = tab->newTableSpace;
else
NewTableSpace = OldHeap->rd_rel->reltablespace;
+
+ if (tab->newToastTableSpace)
+ NewToastTableSpace = tab->newToastTableSpace;
+ else
+ NewToastTableSpace = InvalidOid;
heap_close(OldHeap, NoLock);
/* Create transient table that will receive the modified data */
- OIDNewHeap = make_new_heap(tab->relid, NewTableSpace);
+ OIDNewHeap = make_new_heap(tab->relid, NewTableSpace, NewToastTableSpace);
/*
* Copy the heap data into the new table with the desired
@@ -3431,7 +3465,9 @@ ATRewriteTables(List **wqueue, LOCKMODE lockmode)
* just do a block-by-block copy.
*/
if (tab->newTableSpace)
- ATExecSetTableSpace(tab->relid, tab->newTableSpace, lockmode);
+ ATExecSetTableSpace(tab->relid, tab->newTableSpace, lockmode, tab->newToastTableSpace);
+ if (tab->newToastTableSpace)
+ ATExecSetToastTableSpace(tab->relid, tab->newToastTableSpace, lockmode);
}
}
@@ -8027,30 +8063,66 @@ ATExecDropCluster(Relation rel, LOCKMODE lockmode)
}
/*
- * ALTER TABLE SET TABLESPACE
+ * Check tablespace's permissions & no multiple SET TABLESPACE subcommands
*/
-static void
-ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel, char *tablespacename, LOCKMODE lockmode)
+extern void CheckTableSpaceAlterTable(char *TableSpaceName, Oid TableSpaceOid, Oid NewTableSpaceOid)
{
- Oid tablespaceId;
AclResult aclresult;
-
- /* Check that the tablespace exists */
- tablespaceId = get_tablespace_oid(tablespacename, false);
-
/* Check its permissions */
- aclresult = pg_tablespace_aclcheck(tablespaceId, GetUserId(), ACL_CREATE);
+ aclresult = pg_tablespace_aclcheck(TableSpaceOid, GetUserId(), ACL_CREATE);
if (aclresult != ACLCHECK_OK)
- aclcheck_error(aclresult, ACL_KIND_TABLESPACE, tablespacename);
+ aclcheck_error(aclresult, ACL_KIND_TABLESPACE, TableSpaceName);
- /* Save info for Phase 3 to do the real work */
- if (OidIsValid(tab->newTableSpace))
+ if (OidIsValid(NewTableSpaceOid))
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("cannot have multiple SET TABLESPACE subcommands")));
+}
+
+/*
+ * ALTER TABLE SET [TABLE] TABLESPACE
+ */
+static void
+ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel, char *tablespacename, LOCKMODE lockmode, bool table_only)
+{
+ Oid tablespaceId;
+
+ /* Check that the tablespace exists */
+ tablespaceId = get_tablespace_oid(tablespacename, false);
+
+ /* Check it */
+ CheckTableSpaceAlterTable(tablespacename, tablespaceId, tab->newTableSpace);
+
+ /* Save tablespace Oid */
tab->newTableSpace = tablespaceId;
+
+ /* The case when we want to move only table location not its TOAST table */
+ if (table_only)
+ tab->newToastTableSpace = 0;
+ else
+ tab->newToastTableSpace = tablespaceId;
+
+}
+
+/*
+ * ALTER TABLE SET TOAST TABLESPACE
+ */
+static void
+ATPrepSetToastTableSpace(AlteredTableInfo *tab, Relation rel, char *tablespacename, LOCKMODE lockmode)
+{
+ Oid tablespaceId;
+
+ /* Check that the tablespace exists */
+ tablespaceId = get_tablespace_oid(tablespacename, false);
+
+ /* Check it */
+ CheckTableSpaceAlterTable(tablespacename, tablespaceId, tab->newToastTableSpace);
+
+ /* Save TOAST tablespace Oid */
+ tab->newToastTableSpace = tablespaceId;
}
+
/*
* ALTER TABLE/INDEX SET (...) or RESET (...)
*/
@@ -8178,12 +8250,42 @@ ATExecSetRelOptions(Relation rel, List *defList, bool isReset, LOCKMODE lockmode
heap_close(pgclass, RowExclusiveLock);
}
+
+extern void
+RelationIsMoveableToNewTablespace(Relation rel, Oid newTableSpace)
+{
+ /*
+ * We cannot support moving mapped relations into different tablespaces.
+ * (In particular this eliminates all shared catalogs.)
+ */
+ if (RelationIsMapped(rel))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot move system relation \"%s\"",
+ RelationGetRelationName(rel))));
+
+ /* Can't move a non-shared relation into pg_global */
+ if (newTableSpace == GLOBALTABLESPACE_OID)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("only shared relations can be placed in pg_global tablespace")));
+
+ /*
+ * Don't allow moving temp tables of other backends ... their local buffer
+ * manager is not going to cope.
+ */
+ if (RELATION_IS_OTHER_TEMP(rel))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot move temporary tables of other sessions")));
+}
+
/*
- * Execute ALTER TABLE SET TABLESPACE for cases where there is no tuple
+ * Execute ALTER TABLE SET [TABLE] TABLESPACE for cases where there is no tuple
* rewriting to be done, so we just want to copy the data as fast as possible.
*/
static void
-ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode)
+ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode, Oid newToastTableSpace)
{
Relation rel;
Oid oldTableSpace;
@@ -8212,31 +8314,8 @@ ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode)
relation_close(rel, NoLock);
return;
}
-
- /*
- * We cannot support moving mapped relations into different tablespaces.
- * (In particular this eliminates all shared catalogs.)
- */
- if (RelationIsMapped(rel))
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot move system relation \"%s\"",
- RelationGetRelationName(rel))));
-
- /* Can't move a non-shared relation into pg_global */
- if (newTableSpace == GLOBALTABLESPACE_OID)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("only shared relations can be placed in pg_global tablespace")));
-
- /*
- * Don't allow moving temp tables of other backends ... their local buffer
- * manager is not going to cope.
- */
- if (RELATION_IS_OTHER_TEMP(rel))
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot move temporary tables of other sessions")));
+
+ RelationIsMoveableToNewTablespace(rel, newTableSpace);
reltoastrelid = rel->rd_rel->reltoastrelid;
reltoastidxid = rel->rd_rel->reltoastidxid;
@@ -8316,12 +8395,69 @@ ATExecSetTableSpace(Oid tableOid, Oid newTableSpace, LOCKMODE lockmode)
CommandCounterIncrement();
/* Move associated toast relation and/or index, too */
- if (OidIsValid(reltoastrelid))
- ATExecSetTableSpace(reltoastrelid, newTableSpace, lockmode);
+ if (newToastTableSpace != 0)
+ {
+ if (OidIsValid(reltoastrelid))
+ ATExecSetTableSpace(reltoastrelid, newToastTableSpace, lockmode, newToastTableSpace);
+ if (OidIsValid(reltoastidxid))
+ ATExecSetTableSpace(reltoastidxid, newToastTableSpace, lockmode, newToastTableSpace);
+ }
+}
+
+
+/*
+ * Execute ALTER TABLE SET TOAST TABLESPACE
+ */
+static void
+ATExecSetToastTableSpace(Oid tableOid, Oid newToastTableSpace, LOCKMODE lockmode)
+{
+ Relation rel;
+ Oid oldToastTableSpace;
+ Oid reltoastrelid;
+ Oid reltoastidxid;
+ Relation relToast;
+ /*
+ * Need lock here in case we are recursing to toast table or index
+ */
+ rel = relation_open(tableOid, lockmode);
+
+ /*
+ * Need to know old TOAST tablespace
+ */
+ if (OidIsValid(rel->rd_rel->reltoastrelid))
+ {
+ relToast = relation_open(rel->rd_rel->reltoastrelid, NoLock);
+
+ oldToastTableSpace = relToast->rd_rel->reltablespace;
+ if (newToastTableSpace == oldToastTableSpace ||
+ (newToastTableSpace == MyDatabaseTableSpace && oldToastTableSpace == 0))
+ {
+ relation_close(rel, NoLock);
+ relation_close(relToast, NoLock);
+ return;
+ }
+ }
+ else
+ {
+ relation_close(rel, NoLock);
+ return;
+ }
+
+ reltoastrelid = rel->rd_rel->reltoastrelid;
+ reltoastidxid = rel->rd_rel->reltoastidxid;
+
+ RelationIsMoveableToNewTablespace(rel, newToastTableSpace);
+
+ relation_close(rel, NoLock);
+ relation_close(relToast, NoLock);
+
+ ATExecSetTableSpace(reltoastrelid, newToastTableSpace, lockmode, newToastTableSpace);
if (OidIsValid(reltoastidxid))
- ATExecSetTableSpace(reltoastidxid, newTableSpace, lockmode);
+ ATExecSetTableSpace(reltoastidxid, newToastTableSpace, lockmode, newToastTableSpace);
+
}
+
/*
* Copy data, block by block
*/
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index fd7a9ed..a5e78bd 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -2509,7 +2509,7 @@ OpenIntoRel(QueryDesc *queryDesc)
(void) heap_reloptions(RELKIND_TOASTVALUE, reloptions, true);
- AlterTableCreateToastTable(intoRelationId, reloptions);
+ AlterTableCreateToastTable(intoRelationId, reloptions, InvalidOid);
/*
* And open the constructed table for writing.
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index e9f3896..47f8905 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -558,7 +558,7 @@ static void processCASbits(int cas_bits, int location, const char *constrType,
SYMMETRIC SYSID SYSTEM_P
TABLE TABLES TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN TIME TIMESTAMP
- TO TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P
+ TO TOAST TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P
TRUNCATE TRUSTED TYPE_P
UNBOUNDED UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN UNLISTEN UNLOGGED
@@ -1985,6 +1985,22 @@ alter_table_cmd:
n->name = $3;
$$ = (Node *)n;
}
+ /* ALTER TABLE <name> SET TOAST TABLESPACE <tablespacename> */
+ | SET TOAST TABLESPACE name
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_SetToastTableSpace;
+ n->name = $4;
+ $$ = (Node *)n;
+ }
+ /* ALTER TABLE <name> SET TABLE TABLESPACE <tablespacename> */
+ | SET TABLE TABLESPACE name
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_SetTableTableSpace;
+ n->name = $4;
+ $$ = (Node *)n;
+ }
/* ALTER TABLE <name> SET (...) */
| SET reloptions
{
@@ -12057,6 +12073,7 @@ unreserved_keyword:
| TEMPLATE
| TEMPORARY
| TEXT_P
+ | TOAST
| TRANSACTION
| TRIGGER
| TRUNCATE
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 0749227..0e65f8e 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -552,7 +552,7 @@ standard_ProcessUtility(Node *parsetree,
(void) heap_reloptions(RELKIND_TOASTVALUE, toast_options,
true);
- AlterTableCreateToastTable(relOid, toast_options);
+ AlterTableCreateToastTable(relOid, toast_options, InvalidOid);
}
else if (IsA(stmt, CreateForeignTableStmt))
{
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index b4ab19d..86ca6fa 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -3832,6 +3832,7 @@ getTables(int *numTables)
int i_owning_tab;
int i_owning_col;
int i_reltablespace;
+ int i_reltoasttablespace;
int i_reloptions;
int i_toastreloptions;
int i_reloftype;
@@ -3859,7 +3860,44 @@ getTables(int *numTables)
* we cannot correctly identify inherited columns, owned sequences, etc.
*/
- if (g_fout->remoteVersion >= 90100)
+ if (g_fout->remoteVersion >= 90200)
+ {
+ /*
+ * Left join to pick up dependency info linking sequences to their
+ * owning column, if any (note this dependency is AUTO as of 8.2)
+ */
+ appendPQExpBuffer(query,
+ "SELECT c.tableoid, c.oid, c.relname, "
+ "c.relacl, c.relkind, c.relnamespace, "
+ "(%s c.relowner) AS rolname, "
+ "c.relchecks, c.relhastriggers, "
+ "c.relhasindex, c.relhasrules, c.relhasoids, "
+ "c.relfrozenxid, tc.oid AS toid, "
+ "tc.relfrozenxid AS tfrozenxid, "
+ "c.relpersistence, "
+ "CASE WHEN c.reloftype <> 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, "
+ "d.refobjid AS owning_tab, "
+ "d.refobjsubid AS owning_col, "
+ "(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, "
+ "array_to_string(c.reloptions, ', ') AS reloptions, "
+ "array_to_string(array(SELECT 'toast.' || x FROM unnest(tc.reloptions) x), ', ') AS toast_reloptions, "
+ "(SELECT spcname FROM pg_tablespace t WHERE t.oid = tc.reltablespace) AS reltoasttablespace "
+ "FROM pg_class c "
+ "LEFT JOIN pg_depend d ON "
+ "(c.relkind = '%c' AND "
+ "d.classid = c.tableoid AND d.objid = c.oid AND "
+ "d.objsubid = 0 AND "
+ "d.refclassid = c.tableoid AND d.deptype = 'a') "
+ "LEFT JOIN pg_class tc ON (c.reltoastrelid = tc.oid) "
+ "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c') "
+ "ORDER BY c.oid",
+ username_subquery,
+ RELKIND_SEQUENCE,
+ RELKIND_RELATION, RELKIND_SEQUENCE,
+ RELKIND_VIEW, RELKIND_COMPOSITE_TYPE,
+ RELKIND_FOREIGN_TABLE);
+ }
+ else if (g_fout->remoteVersion >= 90100)
{
/*
* Left join to pick up dependency info linking sequences to their
@@ -3879,7 +3917,8 @@ getTables(int *numTables)
"d.refobjsubid AS owning_col, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, "
"array_to_string(c.reloptions, ', ') AS reloptions, "
- "array_to_string(array(SELECT 'toast.' || x FROM unnest(tc.reloptions) x), ', ') AS toast_reloptions "
+ "array_to_string(array(SELECT 'toast.' || x FROM unnest(tc.reloptions) x), ', ') AS toast_reloptions, "
+ "NULL AS reltoasttablespace "
"FROM pg_class c "
"LEFT JOIN pg_depend d ON "
"(c.relkind = '%c' AND "
@@ -3915,7 +3954,8 @@ getTables(int *numTables)
"d.refobjsubid AS owning_col, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, "
"array_to_string(c.reloptions, ', ') AS reloptions, "
- "array_to_string(array(SELECT 'toast.' || x FROM unnest(tc.reloptions) x), ', ') AS toast_reloptions "
+ "array_to_string(array(SELECT 'toast.' || x FROM unnest(tc.reloptions) x), ', ') AS toast_reloptions, "
+ "NULL AS reltoasttablespace "
"FROM pg_class c "
"LEFT JOIN pg_depend d ON "
"(c.relkind = '%c' AND "
@@ -3950,7 +3990,8 @@ getTables(int *numTables)
"d.refobjsubid AS owning_col, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, "
"array_to_string(c.reloptions, ', ') AS reloptions, "
- "array_to_string(array(SELECT 'toast.' || x FROM unnest(tc.reloptions) x), ', ') AS toast_reloptions "
+ "array_to_string(array(SELECT 'toast.' || x FROM unnest(tc.reloptions) x), ', ') AS toast_reloptions, "
+ "NULL AS reltoasttablespace "
"FROM pg_class c "
"LEFT JOIN pg_depend d ON "
"(c.relkind = '%c' AND "
@@ -3985,7 +4026,8 @@ getTables(int *numTables)
"d.refobjsubid AS owning_col, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, "
"array_to_string(c.reloptions, ', ') AS reloptions, "
- "NULL AS toast_reloptions "
+ "NULL AS toast_reloptions, "
+ "NULL AS reltoasttablespace "
"FROM pg_class c "
"LEFT JOIN pg_depend d ON "
"(c.relkind = '%c' AND "
@@ -4021,7 +4063,8 @@ getTables(int *numTables)
"d.refobjsubid AS owning_col, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, "
"NULL AS reloptions, "
- "NULL AS toast_reloptions "
+ "NULL AS toast_reloptions, "
+ "NULL AS reltoasttablespace "
"FROM pg_class c "
"LEFT JOIN pg_depend d ON "
"(c.relkind = '%c' AND "
@@ -4056,7 +4099,8 @@ getTables(int *numTables)
"d.refobjsubid AS owning_col, "
"NULL AS reltablespace, "
"NULL AS reloptions, "
- "NULL AS toast_reloptions "
+ "NULL AS toast_reloptions, "
+ "NULL AS reltoasttablespace "
"FROM pg_class c "
"LEFT JOIN pg_depend d ON "
"(c.relkind = '%c' AND "
@@ -4087,7 +4131,8 @@ getTables(int *numTables)
"NULL::int4 AS owning_col, "
"NULL AS reltablespace, "
"NULL AS reloptions, "
- "NULL AS toast_reloptions "
+ "NULL AS toast_reloptions, "
+ "NULL AS reltoasttablespace "
"FROM pg_class "
"WHERE relkind IN ('%c', '%c', '%c') "
"ORDER BY oid",
@@ -4113,7 +4158,8 @@ getTables(int *numTables)
"NULL::int4 AS owning_col, "
"NULL AS reltablespace, "
"NULL AS reloptions, "
- "NULL AS toast_reloptions "
+ "NULL AS toast_reloptions, "
+ "NULL AS reltoasttablespace "
"FROM pg_class "
"WHERE relkind IN ('%c', '%c', '%c') "
"ORDER BY oid",
@@ -4149,7 +4195,8 @@ getTables(int *numTables)
"NULL::int4 AS owning_col, "
"NULL AS reltablespace, "
"NULL AS reloptions, "
- "NULL AS toast_reloptions "
+ "NULL AS toast_reloptions, "
+ "NULL AS reltoasttablespace "
"FROM pg_class c "
"WHERE relkind IN ('%c', '%c') "
"ORDER BY oid",
@@ -4198,6 +4245,7 @@ getTables(int *numTables)
i_reloptions = PQfnumber(res, "reloptions");
i_toastreloptions = PQfnumber(res, "toast_reloptions");
i_reloftype = PQfnumber(res, "reloftype");
+ i_reltoasttablespace = PQfnumber(res, "reltoasttablespace");
if (lockWaitTimeout && g_fout->remoteVersion >= 70300)
{
@@ -4252,6 +4300,7 @@ getTables(int *numTables)
tblinfo[i].reltablespace = strdup(PQgetvalue(res, i, i_reltablespace));
tblinfo[i].reloptions = strdup(PQgetvalue(res, i, i_reloptions));
tblinfo[i].toast_reloptions = strdup(PQgetvalue(res, i, i_toastreloptions));
+ tblinfo[i].reltoasttablespace = strdup(PQgetvalue(res, i, i_reltoasttablespace));
/* other fields were zeroed above */
@@ -12518,7 +12567,14 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
}
}
}
-
+ if (strlen(tbinfo->reltoasttablespace) > 0)
+ {
+ appendPQExpBuffer(q, "ALTER TABLE %s ",
+ fmtId(tbinfo->dobj.name));
+ appendPQExpBuffer(q, "SET TOAST TABLESPACE %s;\n",
+ tbinfo->reltoasttablespace);
+ }
+
if (binary_upgrade)
binary_upgrade_extension_member(q, &tbinfo->dobj, labelq->data);
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 3d5d534..dd9cf4c 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -241,6 +241,7 @@ typedef struct _tableInfo
char relkind;
char relpersistence; /* relation persistence */
char *reltablespace; /* relation tablespace */
+ char *reltoasttablespace; /* TOAST relation tablespace */
char *reloptions; /* options specified by WITH (...) */
char *toast_reloptions; /* ditto, for the TOAST table */
bool hasindex; /* does it have any indexes? */
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index d5466f8..d4e8d2c 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -29,6 +29,7 @@ static bool describeOneTableDetails(const char *schemaname,
bool verbose);
static void add_tablespace_footer(printTableContent *const cont, char relkind,
Oid tablespace, const bool newline);
+static void add_toasttablespace_footer(printTableContent *const cont, Oid toasttablespace);
static void add_role_attribute(PQExpBuffer buf, const char *const str);
static bool listTSParsersVerbose(const char *pattern);
static bool describeOneTSParser(const char *oid, const char *nspname,
@@ -1101,6 +1102,7 @@ describeOneTableDetails(const char *schemaname,
bool hastriggers;
bool hasoids;
Oid tablespace;
+ Oid toasttablespace;
char *reloptions;
char *reloftype;
char relpersistence;
@@ -1123,7 +1125,7 @@ describeOneTableDetails(const char *schemaname,
printfPQExpBuffer(&buf,
"SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
"c.relhastriggers, c.relhasoids, "
- "%s, c.reltablespace, "
+ "%s, c.reltablespace, tc.reltablespace, "
"CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, "
"c.relpersistence\n"
"FROM pg_catalog.pg_class c\n "
@@ -1140,7 +1142,7 @@ describeOneTableDetails(const char *schemaname,
printfPQExpBuffer(&buf,
"SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
"c.relhastriggers, c.relhasoids, "
- "%s, c.reltablespace, "
+ "%s, c.reltablespace, tc.reltablespace, "
"CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END\n"
"FROM pg_catalog.pg_class c\n "
"LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
@@ -1156,7 +1158,7 @@ describeOneTableDetails(const char *schemaname,
printfPQExpBuffer(&buf,
"SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
"c.relhastriggers, c.relhasoids, "
- "%s, c.reltablespace\n"
+ "%s, c.reltablespace, tc.reltablespace\n"
"FROM pg_catalog.pg_class c\n "
"LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
"WHERE c.oid = '%s';",
@@ -1171,7 +1173,7 @@ describeOneTableDetails(const char *schemaname,
printfPQExpBuffer(&buf,
"SELECT relchecks, relkind, relhasindex, relhasrules, "
"reltriggers <> 0, relhasoids, "
- "%s, reltablespace\n"
+ "%s, reltablespace, ''\n"
"FROM pg_catalog.pg_class WHERE oid = '%s';",
(verbose ?
"pg_catalog.array_to_string(reloptions, E', ')" : "''"),
@@ -1182,7 +1184,7 @@ describeOneTableDetails(const char *schemaname,
printfPQExpBuffer(&buf,
"SELECT relchecks, relkind, relhasindex, relhasrules, "
"reltriggers <> 0, relhasoids, "
- "'', reltablespace\n"
+ "'', reltablespace, ''\n"
"FROM pg_catalog.pg_class WHERE oid = '%s';",
oid);
}
@@ -1191,7 +1193,7 @@ describeOneTableDetails(const char *schemaname,
printfPQExpBuffer(&buf,
"SELECT relchecks, relkind, relhasindex, relhasrules, "
"reltriggers <> 0, relhasoids, "
- "'', ''\n"
+ "'', '', ''\n"
"FROM pg_catalog.pg_class WHERE oid = '%s';",
oid);
}
@@ -1219,10 +1221,12 @@ describeOneTableDetails(const char *schemaname,
strdup(PQgetvalue(res, 0, 6)) : 0;
tableinfo.tablespace = (pset.sversion >= 80000) ?
atooid(PQgetvalue(res, 0, 7)) : 0;
- tableinfo.reloftype = (pset.sversion >= 90000 && strcmp(PQgetvalue(res, 0, 8), "") != 0) ?
- strdup(PQgetvalue(res, 0, 8)) : 0;
- tableinfo.relpersistence = (pset.sversion >= 90100 && strcmp(PQgetvalue(res, 0, 9), "") != 0) ?
- PQgetvalue(res, 0, 9)[0] : 0;
+ tableinfo.toasttablespace = (pset.sversion >= 80400) ?
+ atooid(PQgetvalue(res, 0, 8)) : 0;
+ tableinfo.reloftype = (pset.sversion >= 90000 && strcmp(PQgetvalue(res, 0, 9), "") != 0) ?
+ strdup(PQgetvalue(res, 0, 9)) : 0;
+ tableinfo.relpersistence = (pset.sversion >= 90100 && strcmp(PQgetvalue(res, 0, 10), "") != 0) ?
+ PQgetvalue(res, 0, 10)[0] : 0;
PQclear(res);
res = NULL;
@@ -1567,6 +1571,7 @@ describeOneTableDetails(const char *schemaname,
printTableAddFooter(&cont, tmpbuf.data);
add_tablespace_footer(&cont, tableinfo.relkind,
tableinfo.tablespace, true);
+ add_toasttablespace_footer(&cont, tableinfo.toasttablespace);
}
PQclear(result);
@@ -2171,6 +2176,7 @@ describeOneTableDetails(const char *schemaname,
add_tablespace_footer(&cont, tableinfo.relkind, tableinfo.tablespace,
true);
+ add_toasttablespace_footer(&cont, tableinfo.toasttablespace);
}
printTable(&cont, pset.queryFout, pset.logfile);
@@ -2270,6 +2276,37 @@ add_tablespace_footer(printTableContent *const cont, char relkind,
}
/*
+ * Add a TOAST tablespace description to a footer.
+ */
+static void
+add_toasttablespace_footer(printTableContent *const cont, Oid toasttablespace)
+{
+ if (toasttablespace != 0)
+ {
+ PGresult *result = NULL;
+ PQExpBufferData buf;
+
+ initPQExpBuffer(&buf);
+ printfPQExpBuffer(&buf,
+ "SELECT spcname FROM pg_catalog.pg_tablespace\n"
+ "WHERE oid = '%u';", toasttablespace);
+ result = PSQLexec(buf.data, false);
+ if (!result)
+ return;
+ /* Should always be the case, but.... */
+ if (PQntuples(result) > 0)
+ {
+ /* Add the TOAST tablespace as a new footer */
+ printfPQExpBuffer(&buf, _("TOAST Tablespace: \"%s\""),
+ PQgetvalue(result, 0, 0));
+ printTableAddFooter(cont, buf.data);
+ }
+ PQclear(result);
+ termPQExpBuffer(&buf);
+ }
+}
+
+/*
* \du or \dg
*
* Describes roles. Any schema portion of the pattern is ignored.
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 4f7df36..c5f183b 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -811,7 +811,7 @@ psql_completion(char *text, int start, int end)
"GROUP", "INDEX", "LANGUAGE", "LARGE OBJECT", "OPERATOR",
"ROLE", "SCHEMA", "SERVER", "SEQUENCE", "TABLE",
"TABLESPACE", "TEXT SEARCH", "TRIGGER", "TYPE",
- "USER", "USER MAPPING FOR", "VIEW", NULL};
+ "USER", "USER MAPPING FOR", "VIEW", "TOAST TABLESPACE", "TABLE TABLESPACE", NULL};
COMPLETE_WITH_LIST(list_ALTER);
}
@@ -1285,12 +1285,15 @@ psql_completion(char *text, int start, int end)
completion_info_charp = prev3_wd;
COMPLETE_WITH_QUERY(Query_for_index_of_table);
}
- /* If we have TABLE <sth> SET, provide WITHOUT,TABLESPACE and SCHEMA */
+ /*
+ * If we have TABLE <sth> SET, provide WITHOUT,TABLESPACE, TOAST TABLESPACE,
+ * TABLE TABLESPACE and SCHEMA
+ */
else if (pg_strcasecmp(prev3_wd, "TABLE") == 0 &&
pg_strcasecmp(prev_wd, "SET") == 0)
{
static const char *const list_TABLESET[] =
- {"(", "WITHOUT", "TABLESPACE", "SCHEMA", NULL};
+ {"(", "WITHOUT", "TABLESPACE", "SCHEMA", "TOAST TABLESPACE", "TABLE TABLESPACE", NULL};
COMPLETE_WITH_LIST(list_TABLESET);
}
@@ -1299,6 +1302,16 @@ psql_completion(char *text, int start, int end)
pg_strcasecmp(prev2_wd, "SET") == 0 &&
pg_strcasecmp(prev_wd, "TABLESPACE") == 0)
COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces);
+ /* If we have ALTER TABLE <sth> SET TABLE provide TABLESPACE */
+ else if (pg_strcasecmp(prev5_wd, "ALTER") == 0 &&
+ pg_strcasecmp(prev4_wd, "TABLE") == 0 &&
+ pg_strcasecmp(prev2_wd, "SET") == 0 &&
+ pg_strcasecmp(prev_wd, "TABLE") == 0)
+ {
+ static const char *const list_TABLETABLESPACE[] =
+ {"TABLESPACE", NULL};
+ COMPLETE_WITH_LIST(list_TABLETABLESPACE);
+ }
/* If we have TABLE <sth> SET WITHOUT provide CLUSTER or OIDS */
else if (pg_strcasecmp(prev4_wd, "TABLE") == 0 &&
pg_strcasecmp(prev2_wd, "SET") == 0 &&
diff --git a/src/include/catalog/toasting.h b/src/include/catalog/toasting.h
index de3623a..388ce5f 100644
--- a/src/include/catalog/toasting.h
+++ b/src/include/catalog/toasting.h
@@ -17,7 +17,7 @@
/*
* toasting.c prototypes
*/
-extern void AlterTableCreateToastTable(Oid relOid, Datum reloptions);
+extern void AlterTableCreateToastTable(Oid relOid, Datum reloptions, Oid toastTableSpace);
extern void BootstrapToastTable(char *relName,
Oid toastOid, Oid toastIndexOid);
diff --git a/src/include/commands/cluster.h b/src/include/commands/cluster.h
index 518e896..08b6181 100644
--- a/src/include/commands/cluster.h
+++ b/src/include/commands/cluster.h
@@ -25,7 +25,7 @@ extern void check_index_is_clusterable(Relation OldHeap, Oid indexOid,
bool recheck, LOCKMODE lockmode);
extern void mark_index_clustered(Relation rel, Oid indexOid);
-extern Oid make_new_heap(Oid OIDOldHeap, Oid NewTableSpace);
+extern Oid make_new_heap(Oid OIDOldHeap, Oid NewTableSpace, Oid NewToastTableSpace);
extern void finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
bool is_system_catalog,
bool swap_toast_by_content,
diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h
index 333e303..d4aa99c 100644
--- a/src/include/commands/tablecmds.h
+++ b/src/include/commands/tablecmds.h
@@ -24,6 +24,10 @@ extern Oid DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId);
extern void RemoveRelations(DropStmt *drop);
+extern void RelationIsMoveableToNewTablespace(Relation rel, Oid newTableSpace);
+
+extern void CheckTableSpaceAlterTable(char *TableSpaceName, Oid TableSpaceOid, Oid NewTableSpaceOid);
+
extern void AlterTable(AlterTableStmt *stmt);
extern LOCKMODE AlterTableGetLockLevel(List *cmds);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 9998e2f..89a4f7d 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1224,7 +1224,9 @@ typedef enum AlterTableType
AT_DropInherit, /* NO INHERIT parent */
AT_AddOf, /* OF <type_name> */
AT_DropOf, /* NOT OF */
- AT_GenericOptions /* OPTIONS (...) */
+ AT_GenericOptions, /* OPTIONS (...) */
+ AT_SetToastTableSpace, /* SET TOAST TABLESPACE */
+ AT_SetTableTableSpace /* SET TABLE TABLESPACE */
} AlterTableType;
typedef struct AlterTableCmd /* one subcommand of an ALTER TABLE */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 12c2faf..1e37f97 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -363,6 +363,7 @@ PG_KEYWORD("then", THEN, RESERVED_KEYWORD)
PG_KEYWORD("time", TIME, COL_NAME_KEYWORD)
PG_KEYWORD("timestamp", TIMESTAMP, COL_NAME_KEYWORD)
PG_KEYWORD("to", TO, RESERVED_KEYWORD)
+PG_KEYWORD("toast",TOAST, UNRESERVED_KEYWORD)
PG_KEYWORD("trailing", TRAILING, RESERVED_KEYWORD)
PG_KEYWORD("transaction", TRANSACTION, UNRESERVED_KEYWORD)
PG_KEYWORD("treat", TREAT, COL_NAME_KEYWORD)
diff --git a/src/test/regress/GNUmakefile b/src/test/regress/GNUmakefile
index 90aea6c..d8904db 100644
--- a/src/test/regress/GNUmakefile
+++ b/src/test/regress/GNUmakefile
@@ -125,7 +125,9 @@ $(top_builddir)/contrib/dummy_seclabel/dummy_seclabel$(DLSUFFIX): $(top_builddir
.PHONY: tablespace-setup
tablespace-setup:
rm -rf ./testtablespace
+ rm -rf ./testtablespace2
mkdir ./testtablespace
+ mkdir ./testtablespace2
##
@@ -170,4 +172,5 @@ clean distclean maintainer-clean: clean-lib
# things created by various check targets
rm -f $(output_files) $(input_files)
rm -rf testtablespace
+ rm -rf testtablespace2
rm -rf $(pg_regress_clean_files)
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index dba96f4..230acaa 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -11,7 +11,7 @@ ALTER TABLESPACE testspace RESET (random_page_cost, seq_page_cost); -- ok
CREATE SCHEMA testschema;
-- try a table
-CREATE TABLE testschema.foo (i int) TABLESPACE testspace;
+CREATE TABLE testschema.foo (i int, label varchar) TABLESPACE testspace;
SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
where c.reltablespace = t.oid AND c.relname = 'foo';
@@ -54,7 +54,21 @@ CREATE TABLE bar (i int) TABLESPACE nosuchspace;
-- Fail, not empty
DROP TABLESPACE testspace;
+-- ALTER TABLE SET TOAST TABLESPACE
+CREATE TABLESPACE testspace2 LOCATION '@testtablespace2@';
+ALTER TABLE testschema.foo SET TOAST TABLESPACE testspace;
+SELECT spcname FROM pg_class c JOIN pg_class d ON (c.reltoastrelid=d.oid) JOIN pg_tablespace ON (d.reltablespace = pg_tablespace.oid) WHERE c.relname = 'foo';
+
+ALTER TABLE testschema.foo SET TOAST TABLESPACE testspace2;
+SELECT spcname FROM pg_class c JOIN pg_class d ON (c.reltoastrelid=d.oid) JOIN pg_tablespace ON (d.reltablespace = pg_tablespace.oid) WHERE c.relname = 'foo';
+
+ALTER TABLE testschema.foo SET TABLESPACE testspace2;
+ALTER TABLE testschema.foo SET TABLE TABLESPACE testspace;
+SELECT spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c WHERE c.reltablespace = t.oid AND c.relname = 'foo';
+SELECT spcname FROM pg_class c JOIN pg_class d ON (c.reltoastrelid=d.oid) JOIN pg_tablespace ON (d.reltablespace = pg_tablespace.oid) WHERE c.relname = 'foo';
+
DROP SCHEMA testschema CASCADE;
-- Should succeed
DROP TABLESPACE testspace;
+DROP TABLESPACE testspace2;
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index 1260c96..324cb07 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -10,7 +10,7 @@ ALTER TABLESPACE testspace RESET (random_page_cost, seq_page_cost); -- ok
-- create a schema we can use
CREATE SCHEMA testschema;
-- try a table
-CREATE TABLE testschema.foo (i int) TABLESPACE testspace;
+CREATE TABLE testschema.foo (i int, label varchar) TABLESPACE testspace;
SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
where c.reltablespace = t.oid AND c.relname = 'foo';
relname | spcname
@@ -72,6 +72,36 @@ ERROR: tablespace "nosuchspace" does not exist
-- Fail, not empty
DROP TABLESPACE testspace;
ERROR: tablespace "testspace" is not empty
+-- ALTER TABLE SET TOAST TABLESPACE
+CREATE TABLESPACE testspace2 LOCATION '@testtablespace2@';
+ALTER TABLE testschema.foo SET TOAST TABLESPACE testspace;
+SELECT spcname FROM pg_class c JOIN pg_class d ON (c.reltoastrelid=d.oid) JOIN pg_tablespace ON (d.reltablespace = pg_tablespace.oid) WHERE c.relname = 'foo';
+ spcname
+-----------
+ testspace
+(1 row)
+
+ALTER TABLE testschema.foo SET TOAST TABLESPACE testspace2;
+SELECT spcname FROM pg_class c JOIN pg_class d ON (c.reltoastrelid=d.oid) JOIN pg_tablespace ON (d.reltablespace = pg_tablespace.oid) WHERE c.relname = 'foo';
+ spcname
+------------
+ testspace2
+(1 row)
+
+ALTER TABLE testschema.foo SET TABLESPACE testspace2;
+ALTER TABLE testschema.foo SET TABLE TABLESPACE testspace;
+SELECT spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c WHERE c.reltablespace = t.oid AND c.relname = 'foo';
+ spcname
+-----------
+ testspace
+(1 row)
+
+SELECT spcname FROM pg_class c JOIN pg_class d ON (c.reltoastrelid=d.oid) JOIN pg_tablespace ON (d.reltablespace = pg_tablespace.oid) WHERE c.relname = 'foo';
+ spcname
+------------
+ testspace2
+(1 row)
+
DROP SCHEMA testschema CASCADE;
NOTICE: drop cascades to 4 other objects
DETAIL: drop cascades to table testschema.foo
@@ -80,3 +110,4 @@ drop cascades to table testschema.asexecute
drop cascades to table testschema.atable
-- Should succeed
DROP TABLESPACE testspace;
+DROP TABLESPACE testspace2;
diff --git a/src/test/regress/pg_regress.c b/src/test/regress/pg_regress.c
index d9cd053..5ea6099 100644
--- a/src/test/regress/pg_regress.c
+++ b/src/test/regress/pg_regress.c
@@ -421,6 +421,7 @@ static void
convert_sourcefiles_in(char *source_subdir, char *dest_subdir, char *suffix)
{
char testtablespace[MAXPGPATH];
+ char testtablespace2[MAXPGPATH];
char indir[MAXPGPATH];
struct stat st;
int ret;
@@ -447,6 +448,7 @@ convert_sourcefiles_in(char *source_subdir, char *dest_subdir, char *suffix)
exit_nicely(2);
snprintf(testtablespace, MAXPGPATH, "%s/testtablespace", outputdir);
+ snprintf(testtablespace2, MAXPGPATH, "%s/testtablespace2", outputdir);
#ifdef WIN32
@@ -463,6 +465,9 @@ convert_sourcefiles_in(char *source_subdir, char *dest_subdir, char *suffix)
if (directory_exists(testtablespace))
rmtree(testtablespace, true);
make_directory(testtablespace);
+ if (directory_exists(testtablespace2))
+ rmtree(testtablespace2, true);
+ make_directory(testtablespace2);
#endif
/* finally loop on each file and do the replacement */
@@ -507,6 +512,7 @@ convert_sourcefiles_in(char *source_subdir, char *dest_subdir, char *suffix)
replace_string(line, "@abs_srcdir@", inputdir);
replace_string(line, "@abs_builddir@", outputdir);
replace_string(line, "@testtablespace@", testtablespace);
+ replace_string(line, "@testtablespace2@", testtablespace2);
replace_string(line, "@libdir@", dlpath);
replace_string(line, "@DLSUFFIX@", DLSUFFIX);
fputs(line, outfile);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
