Request for Comments: ALTER [OBJECT] SET SCHEMA
I've took a look at the TODO item
Allow objects to be moved to different schemas
I've done some code so far which implements the syntax
ALTER [OBJECT] name SET SCHEMA name
where OBJECT currently is
SEQUENCE
TABLE
FUNCTION
DOMAIN
TYPE
Missing are (and i'm planning to add support for this):
AGGREGATE
OPERATOR
OPERATOR CLASS
CONVERSION
You can find a preliminary patch attached to this posting and i'm looking
for comments, critics and perhaps some proposals for improvements /
necessary changes i didn't consider yet.
One issue that comes to my mind is what to do when dealing with tables that
have assigned triggers and sequences (serials). Do we want to move them as
well or leave them in the source namespace?
TIA
--
Bernd
Attachments:
pgsql_alter_object_set_schema.patchapplication/octet-stream; name=pgsql_alter_object_set_schema.patchDownload
Index: src/backend/catalog/pg_depend.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/catalog/pg_depend.c,v
retrieving revision 1.13
diff -B -b -c -r1.13 pg_depend.c
*** src/backend/catalog/pg_depend.c 14 Apr 2005 20:03:23 -0000 1.13
--- src/backend/catalog/pg_depend.c 8 Jun 2005 18:02:35 -0000
***************
*** 211,213 ****
--- 211,294 ----
return ret;
}
+
+ bool
+ changeDependencyFor( Oid classId,
+ Oid objectId,
+ Oid oldrefobjectId,
+ Oid newrefobjectId ) {
+
+ ScanKeyData key[2];
+ SysScanDesc scan;
+ HeapTuple tup;
+ Relation depRel;
+ bool result = false; // nothing changed
+
+ Assert( OidIsValid( classId )
+ && OidIsValid( objectId )
+ && OidIsValid( oldrefobjectId )
+ && OidIsValid( newrefobjectId ) );
+
+ depRel = heap_open( DependRelationId, RowExclusiveLock );
+
+ ScanKeyInit(&key[0],
+ Anum_pg_depend_classid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(classId));
+ ScanKeyInit(&key[1],
+ Anum_pg_depend_objid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(objectId));
+
+ scan = systable_beginscan(depRel, DependDependerIndexId, true,
+ SnapshotNow, 2, key);
+
+ while ( HeapTupleIsValid( ( tup = systable_getnext( scan ) ) ) ) {
+
+ Form_pg_depend depend_class = (Form_pg_depend) GETSTRUCT( tup );
+
+ if ( depend_class->refobjid == oldrefobjectId ) {
+
+ ObjectAddress objAddr;
+
+ objAddr.classId = classId;
+ objAddr.objectId = oldrefobjectId;
+ objAddr.objectSubId = 0;
+
+ if ( isObjectPinned( &objAddr,
+ depRel ) ) {
+
+ elog( ERROR, "attempt to change dependency on a system object!" );
+
+ }
+
+ /*
+ * Change dependency record *on the fly*
+ */
+
+ // need a modifiable copy of current tuple
+ tup = heap_copytuple( tup );
+ depend_class = (Form_pg_depend) GETSTRUCT( tup );
+
+ depend_class->refobjid = newrefobjectId;
+ simple_heap_update( depRel, &tup->t_self, tup );
+ CatalogUpdateIndexes( depRel, tup );
+
+ /*
+ * Assume that the specified object/classId couldn't reference the
+ * changed object twice, so exit the loop immediately.
+ */
+
+ result = true;
+ break;
+
+ }
+
+ }
+
+ systable_endscan( scan );
+ heap_close( depRel, RowExclusiveLock );
+
+ return result;
+
+ }
Index: src/backend/commands/alter.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/alter.c,v
retrieving revision 1.12
diff -B -b -c -r1.12 alter.c
*** src/backend/commands/alter.c 31 Dec 2004 21:59:41 -0000 1.12
--- src/backend/commands/alter.c 8 Jun 2005 18:02:35 -0000
***************
*** 37,42 ****
--- 37,82 ----
#include "utils/syscache.h"
+ void
+ ExecRenameObjSchemaStmt( RenameObjSchemaStmt *stmt ) {
+
+ Oid relid = InvalidOid;
+
+ switch (stmt->renameType)
+
+ {
+
+ case OBJECT_TYPE:
+ case OBJECT_DOMAIN:
+
+ AlterDomainNamespace( stmt->object,
+ stmt->newname );
+ break;
+
+ case OBJECT_FUNCTION:
+
+ RenameFunctionNamespace( stmt->object,
+ stmt->objarg,
+ stmt->newname );
+ break;
+
+ case OBJECT_SEQUENCE:
+ case OBJECT_TABLE:
+
+ CheckRelationOwnership( stmt->relation, true );
+ relid = RangeVarGetRelid( stmt->relation, false );
+
+ RenameTableNamespace( relid, stmt->newname );
+ break;
+
+ default:
+ elog(ERROR, "unrecognized rename schema stmt type: %d",
+ (int) stmt->renameType);
+
+ }
+
+ }
+
/*
* Executes an ALTER OBJECT / RENAME TO statement. Based on the object
* type, the function appropriate to that type is executed.
Index: src/backend/commands/functioncmds.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/functioncmds.c,v
retrieving revision 1.61
diff -B -b -c -r1.61 functioncmds.c
*** src/backend/commands/functioncmds.c 14 Apr 2005 20:03:23 -0000 1.61
--- src/backend/commands/functioncmds.c 8 Jun 2005 18:02:36 -0000
***************
*** 1414,1416 ****
--- 1414,1541 ----
systable_endscan(scan);
heap_close(relation, RowExclusiveLock);
}
+
+ void
+ RenameFunctionNamespace( List *name, List *argtypes, const char *newnpname ) {
+
+ Oid procOid = InvalidOid;
+ Oid oldnpOid = InvalidOid;
+ Oid newnpOid = InvalidOid;
+ HeapTuple tup = NULL;
+ HeapTuple nptup = NULL;
+ Relation rel = NULL;
+ Form_pg_proc proc;
+ AclResult aclrc;
+
+ Assert( ( newnpname != NULL )
+ && ( name != NIL ) );
+
+ rel = heap_open( ProcedureRelationId, RowExclusiveLock );
+ procOid = LookupFuncNameTypeNames( name, argtypes, false );
+
+ /*
+ * Cache lookup for Form_pg_proc tuple of requested function
+ */
+
+ tup = SearchSysCacheCopy( PROCOID,
+ ObjectIdGetDatum( procOid ),
+ 0, 0, 0 );
+
+ if ( !HeapTupleIsValid( tup ) )
+ elog( ERROR, "cache lookup failed for function %u", procOid );
+
+ /*
+ * Target schema exists?
+ */
+
+ nptup = SearchSysCacheCopy( NAMESPACENAME,
+ CStringGetDatum( newnpname ),
+ 0, 0, 0 );
+
+ if ( ! HeapTupleIsValid( nptup ) )
+ ereport( ERROR,
+ (errcode(ERRCODE_UNDEFINED_SCHEMA),
+ errmsg("schema \"%s\" doesn't exists", newnpname ) ) );
+
+ /*
+ * We need CREATE privilege on target schema ...
+ */
+
+ newnpOid = HeapTupleGetOid( nptup );
+
+ if ( ( aclrc = pg_namespace_aclcheck( newnpOid,
+ GetUserId(),
+ ACL_CREATE ) ) != ACLCHECK_OK )
+ aclcheck_error( aclrc, ACL_KIND_NAMESPACE, newnpname );
+
+
+ proc = (Form_pg_proc) GETSTRUCT( tup );
+ oldnpOid = proc->pronamespace;
+
+ /*
+ * ...and we need CREATE privilege on the old namespace, too
+ */
+
+ if ( ( aclrc = pg_namespace_aclcheck( oldnpOid,
+ GetUserId(),
+ ACL_CREATE ) ) != ACLCHECK_OK ) {
+ char *oldnpname = get_namespace_name( oldnpOid );
+ aclcheck_error( aclrc, ACL_KIND_NAMESPACE, oldnpname );
+ }
+
+ /*
+ * and last but not least, we should be owner of the function itself.
+ */
+ if ( !pg_proc_ownercheck( HeapTupleGetOid( tup ), GetUserId() ) ) {
+
+ aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_PROC,
+ NameListToString(name));
+
+ }
+
+ /*
+ * Does this function already exists in new namespace?
+ */
+
+ if ( SearchSysCacheExists( PROCNAMEARGSNSP,
+ CStringGetDatum( NameStr(proc->proname) ),
+ PointerGetDatum( &proc->proargtypes ),
+ ObjectIdGetDatum( newnpOid ),
+ 0 ) ) {
+
+ char *newnpname = get_namespace_name( newnpOid );
+ ereport( ERROR,
+ (errcode(ERRCODE_DUPLICATE_FUNCTION),
+ errmsg("function \"%s\" already exists in schema \"%s\"",
+ NameStr(proc->proname), newnpname ) ) );
+
+ }
+
+ /*
+ * Update function schema now, because tup is
+ * a copied tuple...
+ */
+
+ proc->pronamespace = newnpOid;
+ simple_heap_update( rel, &tup->t_self, tup );
+
+ /* keep catalog indexes up-to-date */
+ CatalogUpdateIndexes( rel, tup );
+
+ /*
+ * Update dependency for new schema
+ */
+
+ if ( changeDependencyFor( ProcedureRelationId,
+ procOid,
+ oldnpOid,
+ newnpOid ) ) {
+
+ elog( NOTICE, "changed dependency to new schema \"%s\"", newnpname );
+
+ }
+
+ heap_freetuple( tup );
+ heap_close( rel, NoLock );
+
+ }
Index: src/backend/commands/tablecmds.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablecmds.c,v
retrieving revision 1.161
diff -B -b -c -r1.161 tablecmds.c
*** src/backend/commands/tablecmds.c 6 Jun 2005 20:22:57 -0000 1.161
--- src/backend/commands/tablecmds.c 8 Jun 2005 18:02:39 -0000
***************
*** 6184,6186 ****
--- 6184,6337 ----
}
}
}
+
+ /*
+ * Changes the namespace of the specified table
+ * Ownership if the specified relation (relid) should be checked
+ * by the caller.
+ */
+ void
+ RenameTableNamespace( Oid relid,
+ const char *newnpname ) {
+
+ HeapTuple tup = NULL;
+ Relation rel = NULL;
+ Relation relRel = NULL;
+ char *relname = NULL;
+ Oid oldnpOid = InvalidOid;
+ Oid newnpOid = InvalidOid;
+ Form_pg_class class;
+ AclResult aclrc;
+
+ /*
+ * Grab an exclusive lock on the relation and do *NOT*
+ * release until EOT
+ */
+
+ rel = relation_open( relid, AccessExclusiveLock );
+
+ /*
+ * Namespace really exists?
+ */
+
+ tup = SearchSysCacheCopy( NAMESPACENAME,
+ CStringGetDatum( newnpname ),
+ 0, 0, 0 );
+
+ if ( !HeapTupleIsValid( tup ) ) {
+
+ ereport( ERROR,
+ (errcode(ERRCODE_UNDEFINED_SCHEMA),
+ errmsg("schema \"%s\" doesn't exists", newnpname ) ) );
+
+ }
+
+ newnpOid = HeapTupleGetOid( tup );
+
+ /*
+ * We need to have CREATE permissions on the target namespace...
+ */
+
+ if ( ( aclrc = pg_namespace_aclcheck( HeapTupleGetOid(tup),
+ GetUserId(),
+ ACL_CREATE ) ) != ACLCHECK_OK )
+ aclcheck_error( aclrc, ACL_KIND_NAMESPACE, newnpname );
+
+ /*
+ * ...and the old namespace.
+ */
+
+ oldnpOid = get_rel_namespace( relid );
+
+ if ( ( aclrc = pg_namespace_aclcheck( oldnpOid,
+ GetUserId(),
+ ACL_CREATE ) ) != ACLCHECK_OK ) {
+ char *oldnpname = get_namespace_name( oldnpOid );
+ aclcheck_error( aclrc, ACL_KIND_NAMESPACE, oldnpname );
+ }
+
+ /*
+ * Check out if the new namespace already has a relation with this name...
+ */
+
+ relname = get_rel_name( relid );
+ if ( get_relname_relid( relname, newnpOid ) != InvalidOid ) {
+
+ ereport( ERROR,
+ (errcode(ERRCODE_DUPLICATE_TABLE),
+ errmsg("relation \"%s\" already exists in schema \"%s\"",
+ relname, newnpname ) ) );
+
+ }
+
+ /*
+ * Pull out the table's pg_class tuple
+ */
+
+ relRel = heap_open( RelationRelationId, RowExclusiveLock );
+
+ tup = SearchSysCacheCopy( RELOID,
+ PointerGetDatum( relid ),
+ 0, 0, 0 );
+
+ if ( !HeapTupleIsValid( tup ) )
+ /* this shouldn't happen */
+ elog( ERROR, "cache lookup failed for relation %u", relid );
+
+ /*
+ * Change schema
+ */
+
+ class = (Form_pg_class) GETSTRUCT(tup);
+ class->relnamespace = newnpOid;
+ simple_heap_update( relRel, &tup->t_self, tup );
+
+ /* keep catalog indexes up-to-date */
+ CatalogUpdateIndexes( relRel, tup );
+
+ /*
+ * Change the namespace for the table's type, too
+ */
+
+ if ( class->relkind == 'r' ) {
+
+ Relation relType = NULL;
+ TypeName *typename = NULL;
+
+ relType = heap_open( TypeRelationId, RowExclusiveLock );
+
+ typename = makeNode( TypeName );
+ typename->names = NIL;
+ typename->typeid = class->reltype;
+ typename->typmod = -1;
+ typename->arrayBounds = NIL;
+
+ ApplyTypeNamespace( class->reltype,
+ relType,
+ newnpname,
+ typename,
+ false /* don't complain about table type */ );
+
+ heap_close( relType, NoLock );
+
+ }
+
+ heap_freetuple( tup );
+ heap_close( relRel, RowExclusiveLock );
+
+ /*
+ * Keep dependency table pg_depend up to date...
+ */
+
+ if ( changeDependencyFor( RelationRelationId,
+ relid,
+ oldnpOid,
+ newnpOid ) )
+ elog( NOTICE, "changed dependency to new schema \"%s\"", newnpname );
+
+ /*
+ * Note: keep the exclusive lock until EOT!
+ */
+ relation_close( rel, NoLock );
+
+ }
Index: src/backend/commands/typecmds.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/typecmds.c,v
retrieving revision 1.72
diff -B -b -c -r1.72 typecmds.c
*** src/backend/commands/typecmds.c 6 May 2005 17:24:53 -0000 1.72
--- src/backend/commands/typecmds.c 8 Jun 2005 18:02:40 -0000
***************
*** 2085,2087 ****
--- 2085,2302 ----
/* Clean up */
heap_close(rel, RowExclusiveLock);
}
+
+ /*
+ * Apply new namespace to specified type. Assume TypeRelationId
+ * is already opened by the caller.
+ *
+ * Specifying errorOnTableType to TRUE causes the function
+ * to error out if an attempt to rename a table type namespace occurs.
+ * This is necessary, since we want users to use ALTER TABLE to rename
+ * the table type's namespace.
+ */
+ void
+ ApplyTypeNamespace( Oid typeOid,
+ const Relation rel,
+ const char *newnpname,
+ const TypeName *typename,
+ const bool errorOnTableType ) {
+
+ HeapTuple tup = NULL;
+ HeapTuple nsptup = NULL;
+ Form_pg_type mytype;
+ AclResult aclrc;
+ Oid oldnpOid;
+ Oid relTypeOid;
+ Oid classId;
+
+ Assert( OidIsValid( typeOid )
+ && ( typename != NULL )
+ && ( rel != NULL )
+ && ( newnpname != NULL ) );
+
+ tup = SearchSysCacheCopy( TYPEOID,
+ ObjectIdGetDatum( typeOid ),
+ 0, 0, 0 );
+
+ if ( !HeapTupleIsValid( tup ) )
+ elog(ERROR, "cache lookup failed for type %u", typeOid);
+
+ /* get type information */
+ mytype = (Form_pg_type) GETSTRUCT(tup);
+
+ /* we need to be the type owner */
+ if ( !pg_type_ownercheck( typeOid, GetUserId() ) ) {
+
+ aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_TYPE,
+ TypeNameToString(typename));
+
+ }
+
+ /* need to have CREATE privilege on old namespace */
+ if ( ( aclrc = pg_namespace_aclcheck( mytype->typnamespace,
+ GetUserId(),
+ ACL_CREATE ) ) != ACLCHECK_OK ) {
+ char *oldnpname = get_namespace_name( mytype->typnamespace );
+ aclcheck_error( aclrc, ACL_KIND_NAMESPACE, oldnpname );
+ }
+
+ /* ...and on the new namespace */
+
+ nsptup = SearchSysCacheCopy( NAMESPACENAME,
+ CStringGetDatum( newnpname ),
+ 0, 0, 0 );
+
+ if ( !HeapTupleIsValid( nsptup ) ) {
+
+ ereport( ERROR,
+ (errcode(ERRCODE_UNDEFINED_SCHEMA),
+ errmsg( "schema \"%s\" doesn't exists", newnpname ) ) );
+
+ }
+
+ if ( ( aclrc = pg_namespace_aclcheck( HeapTupleGetOid( nsptup ),
+ GetUserId(),
+ ACL_CREATE ) ) != ACLCHECK_OK ) {
+ aclcheck_error( aclrc, ACL_KIND_NAMESPACE, newnpname );
+ }
+
+ /*
+ * if errorOnTableType is requested, we don't want to allow renaming
+ * the namespace of a table type. If such an attempt occurs, we error
+ * out....
+ */
+
+ if ( mytype->typtype == 'c'
+ && errorOnTableType
+ && get_rel_relkind( mytype->typrelid ) != 'c' ) {
+
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("\"%s\" is a table's row type",
+ TypeNameToString(typename))));
+
+ }
+
+ /*
+ * Since tup was copied, we can change the namespace OID
+ * without fear...
+ */
+
+ oldnpOid = mytype->typnamespace;
+ mytype->typnamespace = HeapTupleGetOid( nsptup );
+ simple_heap_update( rel, &tup->t_self, tup );
+
+ CatalogUpdateIndexes( rel, tup );
+
+ /*
+ * Keep dependencies up-to-date
+ */
+
+ if ( ( mytype->typtype == 'c' )
+ && ( get_rel_relkind( mytype->typrelid ) == 'c' ) ) {
+
+ /*
+ * Composite types creates dependencies on their pg_class
+ * entry. We need to modify the pg_class tuple as well to
+ * reflect the changes of its schema.
+ */
+
+ Form_pg_class pg_class;
+ Relation relClass = heap_open( RelationRelationId, RowExclusiveLock );
+ HeapTuple relTup = SearchSysCacheCopy( RELOID,
+ ObjectIdGetDatum(
+ mytype->typrelid ),
+ 0, 0, 0 );
+
+ if ( !HeapTupleIsValid( relTup ) )
+ elog( ERROR, "cache lookup for relation \"%s\" failed",
+ TypeNameToString( typename ) );
+
+ /*
+ * Set new schema OID. This has no negative effect, since
+ * relTup is copied
+ */
+
+ pg_class = (Form_pg_class) GETSTRUCT( relTup );
+ pg_class->relnamespace = HeapTupleGetOid( nsptup );
+
+ /*
+ * Update relation
+ */
+
+ simple_heap_update( relClass, &relTup->t_self, relTup );
+ CatalogUpdateIndexes( relClass, relTup );
+
+ /*
+ * cleanup heap tuple, but keep lock until EOT
+ */
+ heap_close( relClass, NoLock );
+ heap_freetuple( relTup );
+
+ relTypeOid = mytype->typrelid;
+ classId = RelationRelationId;
+
+ } else {
+
+ relTypeOid = typeOid;
+ classId = TypeRelationId;
+
+ }
+
+ if ( changeDependencyFor( classId,
+ relTypeOid,
+ oldnpOid,
+ HeapTupleGetOid( nsptup ) ) ) {
+
+ elog( NOTICE, "changed dependency to new schema \"%s\"", newnpname );
+
+ }
+
+ heap_freetuple( tup );
+ heap_freetuple( nsptup );
+
+ }
+
+ void
+ AlterDomainNamespace( List *names, const char *newnpname ) {
+
+ Oid typeOid = InvalidOid;
+ TypeName *typename = NULL;
+ Relation rel = NULL;
+
+ Assert( ( names != NIL )
+ && ( newnpname != NULL ) );
+
+ /*
+ * As usual, create the type stuff so we can use
+ * standard type functions.
+ */
+
+ typename = makeNode(TypeName);
+ typename->names = names;
+ typename->typmod = -1;
+ typename->arrayBounds = NIL;
+
+ rel = heap_open( TypeRelationId, RowExclusiveLock );
+
+ /* get type OID */
+ typeOid = LookupTypeName( typename );
+
+ if ( !OidIsValid( typeOid ) ) {
+
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("type \"%s\" does not exist",
+ TypeNameToString(typename))));
+
+ }
+
+ ApplyTypeNamespace( typeOid, rel, newnpname, typename, true );
+
+ /* keep lock until EOT */
+ heap_close( rel, NoLock );
+
+ }
+
+
Index: src/backend/parser/gram.y
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.491
diff -B -b -c -r2.491 gram.y
*** src/backend/parser/gram.y 7 May 2005 02:22:46 -0000 2.491
--- src/backend/parser/gram.y 8 Jun 2005 18:02:43 -0000
***************
*** 132,138 ****
%type <node> stmt schema_stmt
AlterDatabaseSetStmt AlterDomainStmt AlterGroupStmt AlterOwnerStmt
! AlterSeqStmt AlterTableStmt AlterUserStmt AlterUserSetStmt
AnalyzeStmt ClosePortalStmt ClusterStmt CommentStmt
ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt
CreateDomainStmt CreateGroupStmt CreateOpClassStmt CreatePLangStmt
--- 132,138 ----
%type <node> stmt schema_stmt
AlterDatabaseSetStmt AlterDomainStmt AlterGroupStmt AlterOwnerStmt
! AlterSeqStmt AlterTableStmt AlterUserStmt AlterUserSetStmt AlterObjectSchemaStmt
AnalyzeStmt ClosePortalStmt ClusterStmt CommentStmt
ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt
CreateDomainStmt CreateGroupStmt CreateOpClassStmt CreatePLangStmt
***************
*** 490,495 ****
--- 490,496 ----
stmt :
AlterDatabaseSetStmt
+ | AlterObjectSchemaStmt
| AlterDomainStmt
| AlterFunctionStmt
| AlterGroupStmt
***************
*** 3806,3811 ****
--- 3807,3869 ----
| /*EMPTY*/ { $$ = 0; }
;
+ /*****************************************************************************
+ *
+ * ALTER THING name SET SCHEMA name
+ *
+ *****************************************************************************/
+
+ AlterObjectSchemaStmt: ALTER TABLE relation_expr SET SCHEMA name
+ {
+ RenameObjSchemaStmt *n = makeNode(RenameObjSchemaStmt);
+ n->renameType = OBJECT_TABLE;
+ n->relation = $3;
+ n->object = NIL;
+ n->objarg = NIL;
+ n->newname = $6;
+ $$ = (Node *)n;
+ }
+ | ALTER SEQUENCE relation_expr SET SCHEMA name
+ {
+ RenameObjSchemaStmt *n = makeNode(RenameObjSchemaStmt);
+ n->renameType = OBJECT_SEQUENCE;
+ n->relation = $3;
+ n->object = NIL;
+ n->objarg = NIL;
+ n->newname = $6;
+ $$ = (Node *)n;
+ }
+ | ALTER FUNCTION func_name func_args SET SCHEMA name
+ {
+ RenameObjSchemaStmt *n = makeNode(RenameObjSchemaStmt);
+ n->renameType = OBJECT_FUNCTION;
+ n->relation = NULL;
+ n->object = $3;
+ n->objarg = extractArgTypes($4);
+ n->newname = $7;
+ $$ = (Node *)n;
+ }
+ | ALTER DOMAIN_P any_name SET SCHEMA name
+ {
+ RenameObjSchemaStmt *n = makeNode(RenameObjSchemaStmt);
+ n->renameType = OBJECT_DOMAIN;
+ n->relation = NULL;
+ n->object = $3;
+ n->objarg = NIL;
+ n->newname = $6;
+ $$ = (Node *)n;
+ }
+ | ALTER TYPE_P any_name SET SCHEMA name
+ {
+ RenameObjSchemaStmt *n = makeNode(RenameObjSchemaStmt);
+ n->renameType = OBJECT_TYPE;
+ n->relation = NULL;
+ n->object = $3;
+ n->objarg = NIL;
+ n->newname = $6;
+ $$ = (Node *)n;
+ }
+ ;
/*****************************************************************************
*
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/tcop/utility.c,v
retrieving revision 1.236
diff -B -b -c -r1.236 utility.c
*** src/backend/tcop/utility.c 28 Apr 2005 21:47:15 -0000 1.236
--- src/backend/tcop/utility.c 8 Jun 2005 18:02:44 -0000
***************
*** 285,290 ****
--- 285,291 ----
case T_AlterUserStmt:
case T_AlterUserSetStmt:
case T_RenameStmt:
+ case T_RenameObjSchemaStmt:
case T_CommentStmt:
case T_DefineStmt:
case T_CreateCastStmt:
***************
*** 607,612 ****
--- 608,617 ----
ExecRenameStmt((RenameStmt *) parsetree);
break;
+ case T_RenameObjSchemaStmt:
+ ExecRenameObjSchemaStmt( (RenameObjSchemaStmt *)parsetree );
+ break;
+
case T_AlterOwnerStmt:
ExecAlterOwnerStmt((AlterOwnerStmt *) parsetree);
break;
***************
*** 1302,1307 ****
--- 1307,1341 ----
tag = "COPY";
break;
+ case T_RenameObjSchemaStmt:
+
+ switch( ( (RenameObjSchemaStmt *) parsetree)->renameType )
+ {
+
+ case OBJECT_AGGREGATE:
+ tag = "ALTER AGGREGATE";
+ break;
+
+ case OBJECT_CONVERSION:
+ tag = "ALTER CONVERSION";
+
+ case OBJECT_DOMAIN:
+ tag = "ALTER DOMAIN";
+ break;
+
+ case OBJECT_FUNCTION:
+ tag = "ALTER FUNCTION";
+ break;
+
+ case OBJECT_SEQUENCE:
+ tag = "ALTER SEQUENCE";
+ break;
+
+ default:
+ tag = "ALTER TABLE";
+ break;
+ }
+
case T_RenameStmt:
switch (((RenameStmt *) parsetree)->renameType)
{
Index: src/include/commands/alter.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/commands/alter.h,v
retrieving revision 1.6
diff -B -b -c -r1.6 alter.h
*** src/include/commands/alter.h 31 Dec 2004 22:03:28 -0000 1.6
--- src/include/commands/alter.h 8 Jun 2005 18:02:44 -0000
***************
*** 16,21 ****
--- 16,23 ----
#include "nodes/parsenodes.h"
+ extern void ExecRenameObjSchemaStmt( RenameObjSchemaStmt *stmt );
+
extern void ExecRenameStmt(RenameStmt *stmt);
extern void ExecAlterOwnerStmt(AlterOwnerStmt *stmt);
Index: src/include/commands/defrem.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/commands/defrem.h,v
retrieving revision 1.64
diff -B -b -c -r1.64 defrem.h
*** src/include/commands/defrem.h 14 Apr 2005 01:38:21 -0000 1.64
--- src/include/commands/defrem.h 8 Jun 2005 18:02:44 -0000
***************
*** 54,59 ****
--- 54,61 ----
extern void CreateCast(CreateCastStmt *stmt);
extern void DropCast(DropCastStmt *stmt);
extern void DropCastById(Oid castOid);
+ extern void RenameFunctionNamespace( List *name, List *argtypes,
+ const char *newnpname );
/* commands/operatorcmds.c */
extern void DefineOperator(List *names, List *parameters);
Index: src/include/commands/tablecmds.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/commands/tablecmds.h,v
retrieving revision 1.22
diff -B -b -c -r1.22 tablecmds.h
*** src/include/commands/tablecmds.h 27 Jan 2005 03:18:24 -0000 1.22
--- src/include/commands/tablecmds.h 8 Jun 2005 18:02:44 -0000
***************
*** 16,21 ****
--- 16,23 ----
#include "nodes/parsenodes.h"
+ extern void RenameTableNamespace( Oid relid,
+ const char *newnpname );
extern Oid DefineRelation(CreateStmt *stmt, char relkind);
Index: src/include/commands/typecmds.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/commands/typecmds.h,v
retrieving revision 1.10
diff -B -b -c -r1.10 typecmds.h
*** src/include/commands/typecmds.h 31 Dec 2004 22:03:28 -0000 1.10
--- src/include/commands/typecmds.h 8 Jun 2005 18:02:44 -0000
***************
*** 19,24 ****
--- 19,30 ----
#define DEFAULT_TYPDELIM ','
+ extern void ApplyTypeNamespace( Oid typeOid,
+ const Relation rel,
+ const char *newnpname,
+ const TypeName *typename,
+ const bool errorOnTableType );
+ extern void AlterDomainNamespace( List *names, const char *newnpname );
extern void DefineType(List *names, List *parameters);
extern void RemoveType(List *names, DropBehavior behavior);
extern void RemoveTypeById(Oid typeOid);
Index: src/include/nodes/nodes.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/nodes/nodes.h,v
retrieving revision 1.169
diff -B -b -c -r1.169 nodes.h
*** src/include/nodes/nodes.h 5 Jun 2005 22:32:57 -0000 1.169
--- src/include/nodes/nodes.h 8 Jun 2005 18:02:44 -0000
***************
*** 285,290 ****
--- 285,291 ----
T_CreateTableSpaceStmt,
T_DropTableSpaceStmt,
T_AlterOwnerStmt,
+ T_RenameObjSchemaStmt,
T_A_Expr = 800,
T_ColumnRef,
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.281
diff -B -b -c -r1.281 parsenodes.h
*** src/include/nodes/parsenodes.h 5 Jun 2005 22:32:57 -0000 1.281
--- src/include/nodes/parsenodes.h 8 Jun 2005 18:02:45 -0000
***************
*** 1467,1472 ****
--- 1467,1487 ----
} RemoveOpClassStmt;
/* ----------------------
+ * Alter Object Rename Schema
+ * ----------------------
+ */
+ typedef struct RenameObjSchemaStmt
+ {
+ NodeTag type;
+ RangeVar *relation;
+ List *object;
+ List *objarg;
+
+ char *newname;
+ ObjectType renameType;
+ } RenameObjSchemaStmt;
+
+ /* ----------------------
* Alter Object Rename Statement
* ----------------------
*/
On Wed, Jun 08, 2005 at 08:25:12PM +0200, Bernd Helmle wrote:
One issue that comes to my mind is what to do when dealing with tables that
have assigned triggers and sequences (serials). Do we want to move them as
well or leave them in the source namespace?
I'd think it's important that the ALTER TABLE leaves things just like
what you'd end up with if you created the table in the new schema in the
first place. i.e., indexes, triggers, sequences should be moved too.
One issue to check is what happens if you move the table and trigger but
the function remains in the original namespace. Is this a problem if
the new namespace is not in the search path?
--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"This is a foot just waiting to be shot" (Andrew Dunstan)
Bernd Helmle <mailings@oopsware.de> writes:
You can find a preliminary patch attached to this posting and i'm looking
for comments, critics and perhaps some proposals for improvements /
necessary changes i didn't consider yet.
The code seems fairly schizoid about whether the operation is an "alter
namespace" or a "rename". Please be consistent. I'd say it is *not*
a rename, but I suppose you could make an argument the other way ...
The locking you are doing is inconsistent with the rest of the backend.
We generally don't hold locks on catalogs longer than necessary.
Applying "const" to pointers that point to things that are not const,
as in
+ void
+ ApplyTypeNamespace( Oid typeOid,
+ const Relation rel,seems to me to be horrible style, even if the compiler lets you do it.
It's too easy to misread it as a promise not to alter the pointed-to
object.
(In general I dislike consts on parameters, as that seems to me to be
conflating interface and implementation --- it's certainly no business
of a caller's whether your routine modifies the parameter internally.
Of course this is C's fault not yours, but one has to work with the
language one has.)
regards, tom lane
One issue that comes to my mind is what to do when dealing with tables
that have assigned triggers and sequences (serials). Do we want to move
them as well or leave them in the source namespace?
They should all be moved. Remember nasties like indexes should be moved
as well as toast tables.
Chris
--On Donnerstag, Juni 09, 2005 10:33:08 +0800 Christopher Kings-Lynne
<chriskl@familyhealth.com.au> wrote:
One issue that comes to my mind is what to do when dealing with tables
that have assigned triggers and sequences (serials). Do we want to move
them as well or leave them in the source namespace?They should all be moved. Remember nasties like indexes should be moved
as well as toast tables.
Oh, i thought toast tables should live in the pg_toast namespace?
--
Bernd
--On Mittwoch, Juni 08, 2005 14:49:56 -0400 Tom Lane <tgl@sss.pgh.pa.us>
wrote:
The code seems fairly schizoid about whether the operation is an "alter
namespace" or a "rename". Please be consistent. I'd say it is *not*
a rename, but I suppose you could make an argument the other way ...
No, i totally agree. Well, the Rename* stuff was influenced by my first
shot, that follows the syntax ALTER OBJECT name RENAME SCHEMA TO name....
The locking you are doing is inconsistent with the rest of the backend.
We generally don't hold locks on catalogs longer than necessary.
Okay, needs to be adjusted.
Applying "const" to pointers that point to things that are not const,
as in+ void + ApplyTypeNamespace( Oid typeOid, + const Relation rel,seems to me to be horrible style, even if the compiler lets you do it.
It's too easy to misread it as a promise not to alter the pointed-to
object.
Well, i thought there *should* be a promise, not to alter *rel in that
specific case.
--
Bernd
--On Mittwoch, Juni 08, 2005 14:48:55 -0400 Alvaro Herrera
<alvherre@surnet.cl> wrote:
On Wed, Jun 08, 2005 at 08:25:12PM +0200, Bernd Helmle wrote:
One issue that comes to my mind is what to do when dealing with tables
that have assigned triggers and sequences (serials). Do we want to move
them as well or leave them in the source namespace?I'd think it's important that the ALTER TABLE leaves things just like
what you'd end up with if you created the table in the new schema in the
first place. i.e., indexes, triggers, sequences should be moved too.
That leads me to the question what gets attached to a table:
SEQUENCE, INDEX, TRIGGER (function), CONSTRAINT, .... ?
One issue to check is what happens if you move the table and trigger but
the function remains in the original namespace. Is this a problem if
the new namespace is not in the search path?
Hmm have triggers an own namespace? I can see in pg_trigger that they are
attached to pg_proc, but can't see an own namespace specification...
However, lets have a look at this example:
bernd@[local]:bernd #= CREATE SCHEMA B;
CREATE SCHEMA
bernd@[local]:bernd #= set search_path TO b;
SET
bernd@[local]:bernd #= CREATE TABLE test ( id integer not null primary key
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey"
for table "test"
CREATE TABLE
bernd@[local]:bernd #= CREATE TABLE log_test ( usr text, log_time timestamp
default NOW() );
CREATE TABLE ^
bernd@[local]:bernd #= CREATE OR REPLACE FUNCTION trigger_log_update()
RETURNS TRIGGER AS $$ BEGIN INSERT INTO log_test VALUES( current_user );
RETURN new; END; $$ LANGUAGE PLPGSQL;
CREATE FUNCTION
bernd@[local]:bernd #= CREATE TRIGGER t_log_update AFTER UPDATE OR DELETE
OR INSERT ON test FOR STATEMENT EXECUTE PROCEDURE trigger_log_update();
CREATE TRIGGER
bernd@[local]:bernd #= INSERT INTO test VALUES (2);
INSERT 0 1
bernd@[local]:bernd #= CREATE SCHEMA C;
CREATE SCHEMA
bernd@[local]:bernd #= ALTER TABLE test SET SCHEMA C;
NOTICE: changed dependency to new schema "c"
ALTER TABLE
bernd@[local]:bernd #= SET search_path TO C;
SET
bernd@[local]:bernd #= INSERT INTO test VALUES (4);
INSERT 0 1
So that works, but let's move the trigger function as well:
bernd@[local]:bernd #= ALTER FUNCTION B.trigger_log_update() SET SCHEMA C;
NOTICE: changed dependency to new schema "c"
ALTER TABLE
bernd@[local]:bernd #= INSERT INTO test VALUES (5);
ERROR: relation "log_test" does not exist
CONTEXT: SQL statement "INSERT INTO log_test VALUES( current_user )"
PL/pgSQL function "trigger_log_update" line 1 at SQL statement
So that doesn't work and it's likely that someone can mess up his schema
with this, because the trigger function no longer finds its "log table".
Don't know how to deal with that.....
--
Bernd
They should all be moved. Remember nasties like indexes should be moved
as well as toast tables.Oh, i thought toast tables should live in the pg_toast namespace?
Oh yes, you're probably right. Indexes should move though I think?
Chris
--On Donnerstag, Juni 09, 2005 21:05:59 +0800 Christopher Kings-Lynne
<chriskl@familyhealth.com.au> wrote:
Oh yes, you're probably right. Indexes should move though I think?
Yes, i think so, too.
--
Bernd
What about:
ALTER [OBJECT] RENAME TO [schema.]name [CASCADE]
This has somewhat less new syntax.
CASCADE would also move dependant objects.
Perhaps trigger functions should not be moved, since it
is not really obvious how to do this right.
Warning should be issued in this case.
Command basically frees user from having to hack system
castalogs; Is moving dependant object really such a big deal?
PostgreSQL does not really care where they are and just
keeps working - maybe this should be left up to DBA.
Bernd Helmle <mailings@oopsware.de> writes:
--On Mittwoch, Juni 08, 2005 14:49:56 -0400 Tom Lane <tgl@sss.pgh.pa.us>
wrote:Applying "const" to pointers that point to things that are not const,
as in+ void + ApplyTypeNamespace( Oid typeOid, + const Relation rel,seems to me to be horrible style, even if the compiler lets you do it.
It's too easy to misread it as a promise not to alter the pointed-to
object.
Well, i thought there *should* be a promise, not to alter *rel in that
specific case.
Hmm? You're planning to write into the relation in question. It's
hardly likely that the structure can be expected to remain virgin...
in practice I don't think we guarantee that even for read operations.
regards, tom lane
Bernd Helmle <mailings@oopsware.de> writes:
--On Donnerstag, Juni 09, 2005 21:05:59 +0800 Christopher Kings-Lynne
Oh yes, you're probably right. Indexes should move though I think?
Yes, i think so, too.
I don't think you have any choice about that --- I'm pretty sure that
there are places that assume a table's indexes are in the same schema
the table is. Constraints ditto.
regards, tom lane
--On Donnerstag, Juni 09, 2005 12:05:45 -0400 Tom Lane <tgl@sss.pgh.pa.us>
wrote:
I don't think you have any choice about that --- I'm pretty sure that
there are places that assume a table's indexes are in the same schema
the table is. Constraints ditto.
Okay, then the consenus is to go for it.
--
Bernd
--On Donnerstag, Juni 09, 2005 10:17:33 -0400 Tom Lane <tgl@sss.pgh.pa.us>
wrote:
Hmm? You're planning to write into the relation in question. It's
hardly likely that the structure can be expected to remain virgin...
in practice I don't think we guarantee that even for read operations.
Oh, my stupid fault. Of course, that was a lack of understanding what
Relation is on my side :( Will fix that.
Thanks for your comments.
--
Bernd
Wouldn't
ALTER [OBJECT] RENAME TO [schema.][name]
be a better?
After all, this is essentially a rename operation,
so maybe it is better to extend existing syntax...
5WD-02-Foundation-2003-09.pdf doesn't seem
to specify any renaming with ALTER TABLE...
--On Freitag, Juni 10, 2005 21:20:33 +0200 ziga@mail.ljudmila.org wrote:
Wouldn't
ALTER [OBJECT] RENAME TO [schema.][name]
be a better?
After all, this is essentially a rename operation,
so maybe it is better to extend existing syntax...
I don't think it's a good idea to merge two different semantics: Renaming a
table and "moving" a table to a different schema should be distinguished.
Furthermore, i think it's too error prone, because people could accidently
issue a "schema move" and renaming a table by a typo....
--
Bernd