On Tue, 2010-12-14 at 19:48 +0000, Simon Riggs wrote:
> On Tue, 2010-12-14 at 11:34 -0800, Josh Berkus wrote:
> > As for the utility of this command: there is no question that I would
> > use it. I'm not sure I like the syntax (I'd prefer REPLACE TABLE ____
> > WITH _____), but that's painting the bike shed.
>
> REPLACE TABLE ying WITH yang
>
> is probably easier to implement than hacking at the ALTER TABLE code
> mountain.
>
> > While the command may
> > appear frivolous and unnecessary syntactical ornamentation to some, I
> > have to say that doing the "table doesy-doe" which this command
> > addresses is something I have written scripts for on at least 50% of
> > my professional clients. It keeps coming up.
>
> Yeh.
Patch. Needs work.
--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index f3bd565..671d2c3 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -8766,3 +8766,102 @@ AtEOSubXact_on_commit_actions(bool isCommit, SubTransactionId mySubid,
}
}
}
+
+/*
+ * ExchangeTable
+ * Swap the contents of two tables, after many checks.
+ * We refer to the two tables as yin and yang to avoid confusion.
+ */
+void
+ExchangeTable(RangeVar *yin, RangeVar *yang)
+{
+ Oid yinrelid, yangrelid;
+ Relation yinrel, yangrel;
+
+ yinrel = heap_openrv(yin, AccessExclusiveLock);
+ yinrelid = RelationGetRelid(yinrel);
+
+ /* Checks on yin table */
+ if (yinrel->rd_rel->relkind != RELKIND_RELATION)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("\"%s\" is not a table",
+ RelationGetRelationName(yinrel))));
+
+ if (!pg_class_ownercheck(yinrelid, GetUserId()))
+ aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS,
+ RelationGetRelationName(yinrel));
+
+ if (IsSystemRelation(yinrel))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied: \"%s\" is a system catalog",
+ RelationGetRelationName(yinrel))));
+
+ yangrel = heap_openrv(yang, AccessExclusiveLock);
+ yangrelid = RelationGetRelid(yangrel);
+
+ /* Check on yin and yang are not the same */
+ if (yangrelid == yinrelid)
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_TABLE),
+ errmsg("relation \"%s\" cannot be exchanged with itself",
+ RelationGetRelationName(yinrel))));
+
+ /* Checks on yin table */
+ if (yangrel->rd_rel->relkind != RELKIND_RELATION)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("\"%s\" is not a table",
+ RelationGetRelationName(yangrel))));
+
+ if (!pg_class_ownercheck(yangrelid, GetUserId()))
+ aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS,
+ RelationGetRelationName(yangrel));
+
+ if (IsSystemRelation(yangrel))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied: \"%s\" is a system catalog",
+ RelationGetRelationName(yangrel))));
+
+ /*
+ * Don't allow exchange on temp tables of other backends ... their local
+ * buffer manager is not going to cope.
+ */
+ if (RELATION_IS_OTHER_TEMP(yinrel) ||
+ RELATION_IS_OTHER_TEMP(yangrel))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot truncate temporary tables of other sessions")));
+
+ /*
+ * Also check for active uses of the relation in the current transaction,
+ * including open scans and pending AFTER trigger events.
+ */
+ CheckTableNotInUse(yinrel, "EXCHANGE");
+ CheckTableNotInUse(yangrel, "EXCHANGE");
+
+ /*
+ * Exchange table contents
+ *
+ * Swap heaps, toast tables, toast indexes
+ * all forks
+ * all indexes
+ *
+ * Checks:
+ * * table definitions must match
+ * * constraints must match
+ * * indexes need not match
+ * * outbound FKs don't need to match
+ * * inbound FKs will be set to not validated
+ *
+ * No Trigger behaviour
+ *
+ * How is it WAL logged? By locks and underlying catalog updates
+ */
+
+ /* keep our locks until commit */
+ heap_close(yangrel, NoLock);
+ heap_close(yinrel, NoLock);
+}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 660947c..67bc432 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -201,7 +201,8 @@ static RangeVar *makeRangeVarFromAnyName(List *names, int position, core_yyscan_
DropGroupStmt DropOpClassStmt DropOpFamilyStmt DropPLangStmt DropStmt
DropAssertStmt DropTrigStmt DropRuleStmt DropCastStmt DropRoleStmt
DropUserStmt DropdbStmt DropTableSpaceStmt DropFdwStmt
- DropForeignServerStmt DropUserMappingStmt ExplainStmt FetchStmt
+ DropForeignServerStmt DropUserMappingStmt
+ ExchangeStmt ExplainStmt FetchStmt
GrantStmt GrantRoleStmt IndexStmt InsertStmt ListenStmt LoadStmt
LockStmt NotifyStmt ExplainableStmt PreparableStmt
CreateFunctionStmt AlterFunctionStmt ReindexStmt RemoveAggrStmt
@@ -488,7 +489,7 @@ static RangeVar *makeRangeVarFromAnyName(List *names, int position, core_yyscan_
DEFERRABLE DEFERRED DEFINER DELETE_P DELIMITER DELIMITERS DESC
DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P DOUBLE_P DROP
- EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ESCAPE EXCEPT
+ EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ESCAPE EXCEPT EXCHANGE
EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT
FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD
@@ -730,6 +731,7 @@ stmt :
| DropUserStmt
| DropUserMappingStmt
| DropdbStmt
+ | ExchangeStmt
| ExecuteStmt
| ExplainStmt
| FetchStmt
@@ -6461,6 +6463,21 @@ AlterOwnerStmt: ALTER AGGREGATE func_name aggr_args OWNER TO RoleId
}
;
+/*****************************************************************************
+ *
+ * EXCHANGE TABLE yin WITH yang
+ *
+ *****************************************************************************/
+
+ExchangeStmt: EXCHANGE TABLE relation_expr TO relation_expr
+ {
+ ExchangeStmt *n = makeNode(ExchangeStmt);
+ n->yin = $3;
+ n->yang = $5;
+ $$ = (Node *)n;
+ }
+ ;
+
/*****************************************************************************
*
@@ -11367,6 +11384,7 @@ unreserved_keyword:
| ENCRYPTED
| ENUM_P
| ESCAPE
+ | EXCHANGE
| EXCLUDE
| EXCLUDING
| EXCLUSIVE
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 9500037..ed6d65f 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -767,6 +767,15 @@ standard_ProcessUtility(Node *parsetree,
}
break;
+ case T_ExchangeStmt:
+ {
+ ExchangeStmt *stmt = (ExchangeStmt *) parsetree;
+
+ ExchangeTable(stmt->yin, stmt->yang);
+ break;
+ }
+ break;
+
case T_AlterDomainStmt:
{
AlterDomainStmt *stmt = (AlterDomainStmt *) parsetree;
@@ -1872,6 +1881,10 @@ CreateCommandTag(Node *parsetree)
}
break;
+ case T_ExchangeStmt:
+ tag = "EXCHANGE";
+ break;
+
case T_AlterDomainStmt:
tag = "ALTER DOMAIN";
break;
@@ -2456,6 +2469,10 @@ GetCommandLogLevel(Node *parsetree)
lev = LOGSTMT_DDL;
break;
+ case T_ExchangeStmt:
+ lev = LOGSTMT_DDL;
+ break;
+
case T_AlterDomainStmt:
lev = LOGSTMT_DDL;
break;
diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h
index d3deffb..bbb0488 100644
--- a/src/include/commands/tablecmds.h
+++ b/src/include/commands/tablecmds.h
@@ -69,4 +69,6 @@ extern void AtEOSubXact_on_commit_actions(bool isCommit,
SubTransactionId mySubid,
SubTransactionId parentSubid);
+extern void ExchangeTable(RangeVar *yin, RangeVar *yang);
+
#endif /* TABLECMDS_H */
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 456e8e2..6e869b1 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -279,6 +279,7 @@ typedef enum NodeTag
T_DefineStmt,
T_DropStmt,
T_TruncateStmt,
+ T_ExchangeStmt,
T_CommentStmt,
T_FetchStmt,
T_IndexStmt,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 3d2ae99..a12eb97 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1183,6 +1183,17 @@ typedef struct AlterTableCmd /* one subcommand of an ALTER TABLE */
bool missing_ok; /* skip error if missing? */
} AlterTableCmd;
+/* ----------------------
+ * Exchange Statement
+ * ----------------------
+ */
+typedef struct ExchangeStmt
+{
+ NodeTag type;
+ ObjectType objectType; /* OBJECT_TABLE, OBJECT_TYPE, etc */
+ RangeVar *yin;
+ RangeVar *yang;
+} ExchangeStmt;
/* ----------------------
* Alter Domain
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 578d3cd..38ec11f 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -150,6 +150,7 @@ PG_KEYWORD("exclusive", EXCLUSIVE, UNRESERVED_KEYWORD)
PG_KEYWORD("execute", EXECUTE, UNRESERVED_KEYWORD)
PG_KEYWORD("exists", EXISTS, COL_NAME_KEYWORD)
PG_KEYWORD("explain", EXPLAIN, UNRESERVED_KEYWORD)
+PG_KEYWORD("exchange", EXCHANGE, UNRESERVED_KEYWORD)
PG_KEYWORD("external", EXTERNAL, UNRESERVED_KEYWORD)
PG_KEYWORD("extract", EXTRACT, COL_NAME_KEYWORD)
PG_KEYWORD("false", FALSE_P, RESERVED_KEYWORD)
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
