Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id EF38EB5DBF9 for ; Fri, 7 Oct 2011 12:10:39 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 08018-04 for ; Fri, 7 Oct 2011 15:10:32 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from mail-ey0-f174.google.com (mail-ey0-f174.google.com [209.85.215.174]) by mail.postgresql.org (Postfix) with ESMTP id 4C091B5DBC4 for ; Fri, 7 Oct 2011 12:10:31 -0300 (ADT) Received: by eyg7 with SMTP id 7so1244345eyg.19 for ; Fri, 07 Oct 2011 08:10:30 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=message-id:date:from:user-agent:mime-version:to:subject :content-type; bh=oOXrY4R3hoBl2kNGvdm/y60XJzfG9hb/KgJfHot+lzI=; b=K/Rj6kViT620ClmAIiISe2ooV7t2vQetmiad273fw2cGS85s9rp0eyXxdbzIe3jZK4 t+gqyAoZU3+ZIhH207iSuzS/qk6ih0cAh3OvDrxefvKcxD7oD1ggyZ0aX2U4lU3X3TSN XDjoyX+qzhC8OV1R2AB0pJzH9TTuT2q4HXY+g= Received: by 10.216.133.219 with SMTP id q69mr2481769wei.79.1318000230006; Fri, 07 Oct 2011 08:10:30 -0700 (PDT) Received: from [192.168.0.16] (c2433-1-88-160-112-236.fbx.proxad.net. [88.160.112.236]) by mx.google.com with ESMTPS id h39sm16601085wbo.0.2011.10.07.08.10.27 (version=SSLv3 cipher=OTHER); Fri, 07 Oct 2011 08:10:28 -0700 (PDT) Message-ID: <4E8F1666.2060409@gmail.com> Date: Fri, 07 Oct 2011 17:10:30 +0200 From: Julien Tachoires User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.2.23) Gecko/20110922 Thunderbird/3.1.15 MIME-Version: 1.0 To: pgsql-hackers@postgresql.org Subject: patch : Allow toast tables to be moved to a different tablespace Content-Type: multipart/mixed; boundary="------------080107060000080402000509" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.889 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, T_TO_NO_BRKTS_FREEMAIL=0.01 X-Spam-Level: X-Archive-Number: 201110/370 X-Sequence-Number: 196176 This is a multi-part message in MIME format. --------------080107060000080402000509 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 --------------080107060000080402000509 Content-Type: text/x-patch; name="set_toast_tablespace_v0.6.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="set_toast_tablespace_v0.6.patch" 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 name NOT OF OWNER TO new_owner SET TABLESPACE new_tablespace + SET TABLE TABLESPACE new_tablespace + SET TOAST TABLESPACE new_tablespace and table_constraint_using_index is: @@ -549,6 +551,30 @@ ALTER TABLE name + + + SET TABLE TABLESPACE + + + 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 + + + + + + + SET TOAST TABLESPACE + + + 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 + + + + RENAME 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-TOASTed comparison table, in which all the HTML pages were cut down to 7 kB to fit. + +TOAST table can be moved to a different tablespace with +ALTER TABLE SET TOAST TABLESPACE + + 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 SET TOAST TABLESPACE */ + | SET TOAST TABLESPACE name + { + AlterTableCmd *n = makeNode(AlterTableCmd); + n->subtype = AT_SetToastTableSpace; + n->name = $4; + $$ = (Node *)n; + } + /* ALTER TABLE SET TABLE TABLESPACE */ + | SET TABLE TABLESPACE name + { + AlterTableCmd *n = makeNode(AlterTableCmd); + n->subtype = AT_SetTableTableSpace; + n->name = $4; + $$ = (Node *)n; + } /* ALTER TABLE 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 SET, provide WITHOUT,TABLESPACE and SCHEMA */ + /* + * If we have TABLE 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 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 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 */ 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); --------------080107060000080402000509--