This patch implements ALTER SCHEMA ... SET TABLESPACE.

This changes the default schema tablespace but does not attempted to move
objects within the schema to the new tablespace.

Docs, regression test and tab-completion included.

There were a few places which conflicted with my alter index patch and
I've tried to edit the diff where possible to allow for easy applying.

Gavin
Index: doc/src/sgml/ref/alter_schema.sgml
===================================================================
RCS file: /usr/local/cvsroot/pgsql-server/doc/src/sgml/ref/alter_schema.sgml,v
retrieving revision 1.7
diff -2 -c -r1.7 alter_schema.sgml
*** doc/src/sgml/ref/alter_schema.sgml  25 Jun 2004 21:55:50 -0000      1.7
--- doc/src/sgml/ref/alter_schema.sgml  15 Aug 2004 10:43:10 -0000
***************
*** 23,26 ****
--- 23,27 ----
  ALTER SCHEMA <replaceable>name</replaceable> RENAME TO 
<replaceable>newname</replaceable>
  ALTER SCHEMA <replaceable>name</replaceable> OWNER TO 
<replaceable>newowner</replaceable>
+ ALTER SCHEMA <replaceable>name</replaceable> SET TABLESPACE 
<replaceable>tablespace_name</replaceable>
  </synopsis>
   </refsynopsisdiv>
***************
*** 69,73 ****
      </listitem>
     </varlistentry>
!   </variablelist>
   </refsect1>
  
--- 70,87 ----
      </listitem>
     </varlistentry>
! 
!    <varlistentry>
!     <term><replaceable class="parameter">tablespace_name</replaceable></term>
!       <listitem>
!        <para>
!         The name of a new default tablespace for the schema. Tables and indexes
!         created underneath this schema which are not explicitly created in
!         a different tablespace will be created in this tablespace. Existing 
!         tables and indexes are not affected: they will remain in their
!         existing tablespaces. 
!        </para>
!       </listitem>
!    </varlistentry>
!  </variablelist>
   </refsect1>
  
***************
*** 87,90 ****
--- 101,105 ----
     <member><xref linkend="sql-createschema" 
endterm="sql-createschema-title"></member>
     <member><xref linkend="sql-dropschema" endterm="sql-dropschema-title"></member>
+    <member><xref linkend="sql-createtablespace" 
endterm="sql-createtablespace-title"></member>
    </simplelist>
   </refsect1>
Index: src/backend/commands/schemacmds.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql-server/src/backend/commands/schemacmds.c,v
retrieving revision 1.22
diff -2 -c -r1.22 schemacmds.c
*** src/backend/commands/schemacmds.c   2 Aug 2004 01:30:40 -0000       1.22
--- src/backend/commands/schemacmds.c   15 Aug 2004 10:24:55 -0000
***************
*** 382,383 ****
--- 382,455 ----
        heap_close(rel, NoLock);
  }
+ 
+ /*
+  * ALTER SCHEMA 
+  *
+  * ALTER SCHEMA [ RENAME | OWNER TO ] are handled seperately
+  * This is currently coded pretty specifically to ALTER SCHEMA SET TABLESPACE
+  * but there's no other use for alter schema on the radar at the moment.
+  */
+ 
+ void
+ AlterSchema(AlterSchemaStmt *stmt)
+ {
+       HeapTuple       tup;
+       Relation        rel;
+       char       *name = stmt->name;
+       List       *cmds = stmt->cmds;
+       ListCell   *tcmd;
+       bool            new_tablespace = false;
+ 
+       rel = heap_openr(NamespaceRelationName, RowExclusiveLock);
+ 
+       tup = SearchSysCacheCopy(NAMESPACENAME,
+                                                        CStringGetDatum(name),
+                                                        0, 0, 0);
+       if (!HeapTupleIsValid(tup))
+               ereport(ERROR,
+                               (errcode(ERRCODE_UNDEFINED_SCHEMA),
+                                errmsg("schema \"%s\" does not exist", name)));
+ 
+       /* must be owner */
+       if (!pg_namespace_ownercheck(HeapTupleGetOid(tup), GetUserId()))
+               aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_NAMESPACE,
+                                          name);
+ 
+       if (!allowSystemTableMods && IsReservedName(name))
+               ereport(ERROR,
+                               (errcode(ERRCODE_RESERVED_NAME),
+                                errmsg("reserved schema \"%s\" cannot be modified", 
name)));
+ 
+ 
+       foreach(tcmd, cmds)
+       {
+               AlterSchemaCmd *n = lfirst(tcmd);
+ 
+               if(n->subtype == AS_SetTableSpace)
+               {
+                       Oid                     new_tablespaceid;
+ 
+                       /* Have we parsed this option already? */
+                       if(new_tablespace)
+                ereport(ERROR,
+                         (errcode(ERRCODE_SYNTAX_ERROR),
+                          errmsg("conflicting or redundant options")));
+                       new_tablespace = true;
+                       new_tablespaceid = get_tablespace_oid(n->name);
+                       if(!OidIsValid(new_tablespaceid))
+                               ereport(ERROR,
+                                       (errcode(ERRCODE_UNDEFINED_OBJECT),
+                                        errmsg("tablespace \"%s\" does not exist", 
n->name)));
+ 
+                       /* XXX: do we need CREATE privilege on the tablespace? */
+ 
+                       
+                       ((Form_pg_namespace)GETSTRUCT(tup))->nsptablespace = 
+                                       new_tablespaceid;
+               }
+       }
+       simple_heap_update(rel, &tup->t_self, tup);
+       CatalogUpdateIndexes(rel, tup);
+       heap_close(rel, NoLock);
+       heap_freetuple(tup);
+ }
Index: src/backend/nodes/copyfuncs.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql-server/src/backend/nodes/copyfuncs.c,v
retrieving revision 1.289
diff -2 -c -r1.289 copyfuncs.c
*** src/backend/nodes/copyfuncs.c       2 Aug 2004 04:26:05 -0000       1.289
--- src/backend/nodes/copyfuncs.c       15 Aug 2004 10:02:59 -0000
***************
*** 1645,1648 ****
--- 1645,1670 ----
  }
  
+ static AlterSchemaStmt *
+ _copyAlterSchemaStmt(AlterSchemaStmt *from)
+ {
+       AlterSchemaStmt *newnode = makeNode(AlterSchemaStmt);
+ 
+       COPY_NODE_FIELD(name);
+       COPY_NODE_FIELD(cmds);
+ 
+       return newnode;
+ }
+ 
+ static AlterSchemaCmd *
+ _copyAlterSchemaCmd(AlterSchemaCmd *from)
+ {
+       AlterSchemaCmd *newnode = makeNode(AlterSchemaCmd);
+ 
+       COPY_SCALAR_FIELD(subtype);
+       COPY_STRING_FIELD(name);
+ 
+       return newnode;
+ }
+ 
  static AlterDomainStmt *
  _copyAlterDomainStmt(AlterDomainStmt *from)
***************
*** 2815,2818 ****
--- 2837,2846 ----
                case T_AlterTableCmd:
                        retval = _copyAlterTableCmd(from);
+                       break;
+               case T_AlterSchemaStmt:
+                       retval = _copyAlterSchemaStmt(from);
+                       break;
+               case T_AlterSchemaCmd:
+                       retval = _copyAlterSchemaCmd(from);
                        break;
                case T_AlterDomainStmt:
Index: src/backend/nodes/equalfuncs.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql-server/src/backend/nodes/equalfuncs.c,v
retrieving revision 1.228
diff -2 -c -r1.228 equalfuncs.c
*** src/backend/nodes/equalfuncs.c      2 Aug 2004 04:26:05 -0000       1.228
--- src/backend/nodes/equalfuncs.c      15 Aug 2004 10:05:39 -0000
***************
*** 745,748 ****
--- 745,765 ----
  
  static bool
+ _equalAlterSchemaStmt(AlterSchemaStmt *a, AlterSchemaStmt *b)
+ {
+       COMPARE_NODE_FIELD(name);
+       COMPARE_NODE_FIELD(cmds);
+ 
+       return true;
+ }
+ 
+ static bool
+ _equalAlterSchemaCmd(AlterSchemaCmd *a, AlterSchemaCmd *b)
+ {
+       COMPARE_SCALAR_FIELD(subtype);
+       COMPARE_STRING_FIELD(name);
+       return true;
+ }
+ 
+ static bool
  _equalAlterDomainStmt(AlterDomainStmt *a, AlterDomainStmt *b)
  {
***************
*** 1943,1946 ****
--- 1960,1969 ----
                case T_SetOperationStmt:
                        retval = _equalSetOperationStmt(a, b);
+                       break;
+               case T_AlterSchemaStmt:
+                       retval = _equalAlterSchemaStmt(a, b);
+                       break;
+               case T_AlterSchemaCmd:
+                       retval = _equalAlterSchemaCmd(a, b);
                        break;
                case T_AlterTableStmt:
Index: src/backend/parser/gram.y
===================================================================
RCS file: /usr/local/cvsroot/pgsql-server/src/backend/parser/gram.y,v
retrieving revision 2.471
diff -2 -c -r2.471 gram.y
*** src/backend/parser/gram.y   12 Aug 2004 21:00:28 -0000      2.471
--- src/backend/parser/gram.y   15 Aug 2004 06:28:31 -0000
***************
*** 130,134 ****
  %type <node>  stmt schema_stmt
                AlterDatabaseSetStmt AlterDomainStmt AlterGroupStmt AlterOwnerStmt
!               AlterSeqStmt AlterTableStmt AlterUserStmt AlterUserSetStmt
                AnalyzeStmt ClosePortalStmt ClusterStmt CommentStmt
                ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt
--- 130,135 ----
  %type <node>  stmt schema_stmt
                AlterDatabaseSetStmt AlterDomainStmt AlterGroupStmt AlterOwnerStmt
!               AlterSeqStmt AlterTableStmt AlterSchemaStmt 
!               AlterUserStmt AlterUserSetStmt
                AnalyzeStmt ClosePortalStmt ClusterStmt CommentStmt
                ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt
***************
*** 157,162 ****
  %type <ival>  add_drop
  
! %type <node>  alter_table_cmd
! %type <list>  alter_table_cmds
  
  %type <dbehavior>     opt_drop_behavior
--- 158,163 ----
  %type <ival>  add_drop
  
! %type <node>  alter_table_cmd alter_rel_cmd alter_schema_cmd
! %type <list>  alter_table_cmds alter_rel_cmds alter_schema_cmds
  
  %type <dbehavior>     opt_drop_behavior
***************
*** 489,492 ****
--- 490,494 ----
                        | AlterOwnerStmt
                        | AlterSeqStmt
+                       | AlterSchemaStmt
                        | AlterTableStmt
                        | AlterUserSetStmt
***************
*** 1136,1143 ****
                ;
  
  
  /*****************************************************************************
   *
!  *    ALTER TABLE variations
   *
   *****************************************************************************/
--- 1138,1168 ----
                ;
  
+ AlterSchemaStmt:
+                       ALTER SCHEMA name alter_schema_cmds
+                               {
+                                       AlterSchemaStmt *n = makeNode(AlterSchemaStmt);
+                                       n->name = $3;
+                                       n->cmds = $4;
+                                       $$ = (Node *) n;
+                               }
+               ;
+ 
+ alter_schema_cmds:
+                       alter_schema_cmd                                               
 { $$ = list_make1($1); }
+                       | alter_schema_cmds ',' alter_schema_cmd { $$ = lappend($1, 
$3); }
+               ;
+ 
+ alter_schema_cmd: SET TABLESPACE name
+                               {
+                                       AlterSchemaCmd *n = makeNode(AlterSchemaCmd);
+                                       n->subtype = AS_SetTableSpace;
+                                       n->name = $3;
+                                       $$ = (Node *) n;
+                               }
+               ;
  
  /*****************************************************************************
   *
!  *    ALTER [ TABLE | INDEX ] variations
   *
   *****************************************************************************/
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql-server/src/backend/tcop/utility.c,v
retrieving revision 1.225
diff -2 -c -r1.225 utility.c
*** src/backend/tcop/utility.c  12 Aug 2004 21:00:34 -0000      1.225
--- src/backend/tcop/utility.c  15 Aug 2004 09:24:36 -0000
***************
*** 574,577 ****
--- 574,581 ----
                        break;
  
+               case T_AlterSchemaStmt:
+                       AlterSchema((AlterSchemaStmt *) parsetree);
+                       break;
+ 
                case T_AlterDomainStmt:
                        {
***************
*** 1333,1336 ****
--- 1340,1347 ----
                case T_AlterTableStmt:
                        tag = "ALTER TABLE";
+                       break;
+ 
+               case T_AlterSchemaStmt:
+                       tag = "ALTER SCHEMA";
                        break;
  
Index: src/include/commands/schemacmds.h
===================================================================
RCS file: /usr/local/cvsroot/pgsql-server/src/include/commands/schemacmds.h,v
retrieving revision 1.7
diff -2 -c -r1.7 schemacmds.h
*** src/include/commands/schemacmds.h   25 Jun 2004 21:55:58 -0000      1.7
--- src/include/commands/schemacmds.h   15 Aug 2004 06:32:41 -0000
***************
*** 25,28 ****
--- 25,30 ----
  extern void RenameSchema(const char *oldname, const char *newname);
  extern void AlterSchemaOwner(const char *name, AclId newOwnerSysId);
+ extern void AlterSchema(AlterSchemaStmt *stmt);
+ 
  
  #endif   /* SCHEMACMDS_H */
Index: src/include/nodes/nodes.h
===================================================================
RCS file: /usr/local/cvsroot/pgsql-server/src/include/nodes/nodes.h,v
retrieving revision 1.159
diff -2 -c -r1.159 nodes.h
*** src/include/nodes/nodes.h   25 Jun 2004 21:55:59 -0000      1.159
--- src/include/nodes/nodes.h   14 Aug 2004 09:18:40 -0000
***************
*** 208,211 ****
--- 208,213 ----
        T_AlterTableStmt,
        T_AlterTableCmd,
+       T_AlterSchemaStmt,
+       T_AlterSchemaCmd,
        T_AlterDomainStmt,
        T_SetOperationStmt,
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /usr/local/cvsroot/pgsql-server/src/include/nodes/parsenodes.h,v
retrieving revision 1.265
diff -2 -c -r1.265 parsenodes.h
*** src/include/nodes/parsenodes.h      4 Aug 2004 21:34:24 -0000       1.265
--- src/include/nodes/parsenodes.h      14 Aug 2004 09:24:22 -0000
***************
*** 823,826 ****
--- 824,855 ----
  } AlterTableCmd;
  
+ /* ---------------------
+  * Alter Schema
+  *
+  * Currently we only support ALTER SCHEMA <name> SET TABLESPACE <name>
+  * with this node at the moment as OWNER TO and RENAME are handled 
+  * else where. Still, handle it in a reasonably generic way.
+  * ----------------------
+  */
+ 
+ 
+ typedef struct AlterSchemaStmt
+ {
+       NodeTag         type;
+       char       *name;
+       List       *cmds;
+ } AlterSchemaStmt;
+ 
+ typedef enum AlterSchemaType
+ {
+       AS_SetTableSpace                        /* SET TABLESPACE */
+ } AlterSchemaType;
+ 
+ typedef struct AlterSchemaCmd
+ {
+       NodeTag         type;
+       AlterSchemaType subtype;
+       char       *name;
+ } AlterSchemaCmd;
  
  /* ----------------------
Index: src/test/regress/input/tablespace.source
===================================================================
RCS file: /usr/local/cvsroot/pgsql-server/src/test/regress/input/tablespace.source,v
retrieving revision 1.1
diff -2 -c -r1.1 tablespace.source
*** src/test/regress/input/tablespace.source    18 Jun 2004 06:14:29 -0000      1.1
--- src/test/regress/input/tablespace.source    15 Aug 2004 10:23:05 -0000
***************
*** 33,36 ****
--- 33,45 ----
  DROP SCHEMA testschema CASCADE;
  
+ 
+ -- Now test ALTER SCHEMA SET TABLESPACE
+ CREATE SCHEMA testschema;
+ SELECT * FROM pg_namespace WHERE nspname = 'testschema';
+ ALTER SCHEMA testschema SET TABLESPACE testspace;
+ SELECT spcname FROM pg_tablespace t JOIN pg_namespace n ON(t.oid = n.nsptablespace);
+ -- should fail
+ ALTER SCHEMA testschema SET TABLESPACE nosuchtablespace;
+ DROP SCHEMA testschema;
  -- Should succeed
  DROP TABLESPACE testspace;
Index: src/test/regress/output/tablespace.source
===================================================================
RCS file: /usr/local/cvsroot/pgsql-server/src/test/regress/output/tablespace.source,v
retrieving revision 1.1
diff -2 -c -r1.1 tablespace.source
*** src/test/regress/output/tablespace.source   18 Jun 2004 06:14:31 -0000      1.1
--- src/test/regress/output/tablespace.source   15 Aug 2004 10:30:13 -0000
***************
*** 42,45 ****
--- 42,64 ----
  DROP SCHEMA testschema CASCADE;
  NOTICE:  drop cascades to table testschema.foo
+ -- Now test ALTER SCHEMA SET TABLESPACE
+ CREATE SCHEMA testschema;
+ SELECT * FROM pg_namespace WHERE nspname = 'testschema';
+   nspname   | nspowner | nsptablespace | nspacl 
+ ------------+----------+---------------+--------
+  testschema |        1 |             0 | 
+ (1 row)
+ 
+ ALTER SCHEMA testschema SET TABLESPACE testspace;
+ SELECT spcname FROM pg_tablespace t JOIN pg_namespace n ON(t.oid = n.nsptablespace);
+   spcname  
+ -----------
+  testspace
+ (1 row)
+ 
+ -- should fail
+ ALTER SCHEMA testschema SET TABLESPACE nosuchtablespace;
+ ERROR:  tablespace "nosuchtablespace" does not exist
+ DROP SCHEMA testschema;
  -- Should succeed
  DROP TABLESPACE testspace;
Index: src/bin/psql/tab-complete.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql-server/src/bin/psql/tab-complete.c,v
retrieving revision 1.109
diff -2 -c -r1.109 tab-complete.c
*** src/bin/psql/tab-complete.c 28 Jul 2004 14:23:30 -0000      1.109
--- src/bin/psql/tab-complete.c 15 Aug 2004 10:58:06 -0000
***************
*** 651,654 ****
--- 651,663 ----
                         pg_strcasecmp(prev2_wd, "TRIGGER") == 0)
                COMPLETE_WITH_CONST("ON");
+       /* ALTER SCHEMA <name> */
+       else if (pg_strcasecmp(prev3_wd, "ALTER") == 0 &&
+              pg_strcasecmp(prev2_wd, "SCHEMA") == 0)
+     {
+         static const char *const list_ALTERSCHEMA[] =
+         {"SET TABLESPACE", "OWNER TO", "RENAME TO", NULL};
+         COMPLETE_WITH_LIST(list_ALTERSCHEMA);
+     }
+ 
  
        /*
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

Reply via email to