ALTER SCHEMA ... SET TABLESPACE
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
Attachments:
alter_schema.difftext/plain; charset=US-ASCII; name=alter_schema.diffDownload
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);
+ }
+
/*
Would people like this applied to 8.0? It addresses another of the
tablespace deficiency.
One item of concern is that it moves the default location for new items
created, and does not move items already created in the tablespace
itself. This conflicts with ALTER TABLE/INDEX which moves the actual
objects. I am not sure how we should address this.
---------------------------------------------------------------------------
Gavin Sherry wrote:
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
Content-Description:
[ Attachment, skipping... ]
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Fri, 20 Aug 2004, Bruce Momjian wrote:
Would people like this applied to 8.0? It addresses another of the
tablespace deficiency.
This is an extension of tablespaces, and is not required to fix a bug ...
therefore, it is a feature, and not eligible for inclusion at this point
in the development cycle ...
One item of concern is that it moves the default location for new items
created, and does not move items already created in the tablespace
itself. This conflicts with ALTER TABLE/INDEX which moves the actual
objects. I am not sure how we should address this.---------------------------------------------------------------------------
Gavin Sherry wrote:
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
Content-Description:
[ Attachment, skipping... ]
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
Marc G. Fournier wrote:
On Fri, 20 Aug 2004, Bruce Momjian wrote:
Would people like this applied to 8.0? It addresses another of the
tablespace deficiency.This is an extension of tablespaces, and is not required to fix a bug ...
therefore, it is a feature, and not eligible for inclusion at this point
in the development cycle ...
I am inclined to agree. ALTER INDEX is an operation that will happen
quite often, but I don't think ALTER SCHEMA will be as frequent, and the
given solution doesn't address the two needs of moving the entire schema
or just future object creation.
Added to TODO:
o Allow databases and schemas to be moved to different tablespaces
One complexity is whether moving a schema should move all existing
schema objects or just define the location for future object creation.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073This has been saved for the 8.1 release:
http:/momjian.postgresql.org/cgi-bin/pgpatches2
---------------------------------------------------------------------------
Gavin Sherry wrote:
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
Content-Description:
[ Attachment, skipping... ]
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
I am inclined to agree. ALTER INDEX is an operation that will happen
quite often, but I don't think ALTER SCHEMA will be as frequent, and the
given solution doesn't address the two needs of moving the entire schema
or just future object creation.
The syntax for the functionality the patch gives should probably be
something like
ALTER SCHEMA s SET DEFAULT TABLESPACE t;
and then we could use "SET TABLESPACE" for a variant that forcibly moves
the contained tables.
regards, tom lane
At 06:07 AM 21/08/2004, Bruce Momjian wrote:
I am inclined to agree. ALTER INDEX is an operation that will happen
quite often,
One argument for doing it in this release is pg_dump/restore. Do we want
pg_dump to dump the CREATE SCHEMA followed by ALTER SCHEMA? Or will the SET
DEFAULT TABLESPACE work on schemas?
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp.mit.edu:11371 |/
I'd like it applied, but change it to 'SET DEFAULT TABLESPACE' perhaps...?
Chris
Bruce Momjian wrote:
Show quoted text
Would people like this applied to 8.0? It addresses another of the
tablespace deficiency.One item of concern is that it moves the default location for new items
created, and does not move items already created in the tablespace
itself. This conflicts with ALTER TABLE/INDEX which moves the actual
objects. I am not sure how we should address this.---------------------------------------------------------------------------
Gavin Sherry wrote:
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
Content-Description:
[ Attachment, skipping... ]
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
This is an extension of tablespaces, and is not required to fix a bug ...
therefore, it is a feature, and not eligible for inclusion at this point
in the development cycle ...I am inclined to agree. ALTER INDEX is an operation that will happen
quite often, but I don't think ALTER SCHEMA will be as frequent, and the
given solution doesn't address the two needs of moving the entire schema
or just future object creation.
No, it implements something that is currently impossible without editing
the system catalogs - clearly an oversight! Especially if we add a
disk-based change to it to avoid those drop tablespace errors.
It will be 5 minutes before someone who has created a schema in 8.0 and
then will want to make it have a different default - there's no way they
can do it...
Chris
On Mon, 23 Aug 2004, Christopher Kings-Lynne wrote:
This is an extension of tablespaces, and is not required to fix a bug ...
therefore, it is a feature, and not eligible for inclusion at this point
in the development cycle ...I am inclined to agree. ALTER INDEX is an operation that will happen
quite often, but I don't think ALTER SCHEMA will be as frequent, and the
given solution doesn't address the two needs of moving the entire schema
or just future object creation.No, it implements something that is currently impossible without editing the
system catalogs - clearly an oversight! Especially if we add a disk-based
change to it to avoid those drop tablespace errors.It will be 5 minutes before someone who has created a schema in 8.0 and then
will want to make it have a different default - there's no way they can do
it...
If they just created the schema, then a dump/reload would fix it ...
----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
It will be 5 minutes before someone who has created a schema in 8.0
and then will want to make it have a different default - there's no
way they can do it...If they just created the schema, then a dump/reload would fix it ...
What the hey? For how long is that going to be our excuse for sucking?
No wonder everyone thinks we're newbie unfriendly and hard to use...
Chris
On Mon, 23 Aug 2004, Christopher Kings-Lynne wrote:
It will be 5 minutes before someone who has created a schema in 8.0 and
then will want to make it have a different default - there's no way they
can do it...If they just created the schema, then a dump/reload would fix it ...
What the hey? For how long is that going to be our excuse for sucking? No
wonder everyone thinks we're newbie unfriendly and hard to use...
We are in a beta freeze ... not having ALTER SCHEMA to move tablespaces is
not a bug, and is not appropriate for adding during a beta freeze ... once
8.0 is released, it can be added for 8.1's release ...
----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
What the hey? For how long is that going to be our excuse for
sucking? No wonder everyone thinks we're newbie unfriendly and hard
to use...We are in a beta freeze ... not having ALTER SCHEMA to move tablespaces
is not a bug, and is not appropriate for adding during a beta freeze ...
once 8.0 is released, it can be added for 8.1's release ...
So what you're saying is that beta freeze should never have been
declared, and in fact it was declared too early based on people's fear
of a "late release", rather than any sort of logical feature-completeness?
In that case, what you say makes perfect sense :)
Chris
On Sunday 22 August 2004 21:34, Marc G. Fournier wrote:
On Mon, 23 Aug 2004, Christopher Kings-Lynne wrote:
This is an extension of tablespaces, and is not required to fix a bug
... therefore, it is a feature, and not eligible for inclusion at this
point in the development cycle ...I am inclined to agree. ALTER INDEX is an operation that will happen
quite often, but I don't think ALTER SCHEMA will be as frequent, and the
given solution doesn't address the two needs of moving the entire schema
or just future object creation.No, it implements something that is currently impossible without editing
the system catalogs - clearly an oversight! Especially if we add a
disk-based change to it to avoid those drop tablespace errors.It will be 5 minutes before someone who has created a schema in 8.0 and
then will want to make it have a different default - there's no way they
can do it...If they just created the schema, then a dump/reload would fix it ...
A dump/reload can fix a lot of problems... of course the people who are most
likely to use tablespaces are probably the same people most likely to not be
able to do a dump/reload just after doing the dump/reload they had to do to
upgrade to 8.0. Hopefully they'll have more forsight than we did...
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
On Mon, 23 Aug 2004, Christopher Kings-Lynne wrote:
What the hey? For how long is that going to be our excuse for sucking?
No wonder everyone thinks we're newbie unfriendly and hard to use...We are in a beta freeze ... not having ALTER SCHEMA to move tablespaces is
not a bug, and is not appropriate for adding during a beta freeze ... once
8.0 is released, it can be added for 8.1's release ...So what you're saying is that beta freeze should never have been declared,
and in fact it was declared too early based on people's fear of a "late
release", rather than any sort of logical feature-completeness?
Nope, what I'm saying is the same thing I've been saying since June 1st,
and you are reenforcing ... tablespaces as a whole should have been held
off until 8.0 was released, since it wasn't complete :)
----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
At 01:04 PM 23/08/2004, Marc G. Fournier wrote:
not having ALTER SCHEMA to move tablespaces is not a bug
But it does make pg_dump/restore more inclined to fail, so increases the
incidence of another bug, which can not be fixed without a global SET
DEFAULT TABLESPACE or an ALTER SCHEMA.
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp.mit.edu:11371 |/
On Mon, 23 Aug 2004, Philip Warner wrote:
At 01:04 PM 23/08/2004, Marc G. Fournier wrote:
not having ALTER SCHEMA to move tablespaces is not a bug
But it does make pg_dump/restore more inclined to fail, so increases the
incidence of another bug, which can not be fixed without a global SET
DEFAULT TABLESPACE or an ALTER SCHEMA.
'k, you lost me on that one ... how can not having ALTER SCHEMA to move a
tablespace cause a pg_dump/restore to fail? Won't the dump/restore
"store" a "CREATE SCHEMA" onto the new tablespace? Why would a
dump/restore issue an ALTER SCHEMA part way through?
Or am I missing something?
----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
At 01:48 PM 23/08/2004, Marc G. Fournier wrote:
Won't the dump/restore "store" a "CREATE SCHEMA" onto the new
tablespace? Why would a dump/restore issue an ALTER SCHEMA part way through?
My *belief* is that if the tablespace does not exist, then the restore
(which creates the schema and refers to the non-existent tablespace) will
fail to create the schema.
We've had the same problem with CREATE TABLE statements. Tom is (I think)
working on a SET DEFAULT_TABLESPACE command so that CREATE TABLE no longer
refers to the tablespace. Not sure if it will apply to databases or schemas
though.
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp.mit.edu:11371 |/
Philip Warner <pjw@rhyme.com.au> writes:
At 01:48 PM 23/08/2004, Marc G. Fournier wrote:
Won't the dump/restore "store" a "CREATE SCHEMA" onto the new
tablespace? Why would a dump/restore issue an ALTER SCHEMA part way through?
We've had the same problem with CREATE TABLE statements. Tom is (I think)
working on a SET DEFAULT_TABLESPACE command so that CREATE TABLE no longer
refers to the tablespace.
There's been some talk of that, but AFAICS it's not related to an ALTER
SCHEMA SET [DEFAULT] TABLESPACE command. (And no, I've not yet lifted
a finger on this, though I'm willing to handle the backend side of it
if there's consensus to handle dumping this way.)
I have mixed emotions about the ALTER SCHEMA patch. It is a pretty
simple and obvious extension --- and we did say that we would cut some
slack on the interpretation of "feature freeze" for stuff related to the
big 7.5/8.0 additions. On the other hand (a) that was two months ago
now, and (b) the recent foulup with the also-simple-and-obvious ALTER
INDEX extension served to remind me why we have a feature-freeze policy
in the first place. I'm kinda leaning to Marc's position at the moment.
regards, tom lane
At 02:28 PM 23/08/2004, Tom Lane wrote:
There's been some talk of that, but AFAICS it's not related to an ALTER
SCHEMA SET [DEFAULT] TABLESPACE command.
So, if you do make the changes, will the schema definition be affected by
those changes, or do you expect the tablespace to be embedded in the CREATE
SCHEMA command?
(And no, I've not yet lifted
a finger on this, though I'm willing to handle the backend side of it
if there's consensus to handle dumping this way.)
Let me know when consensus happens. I've got a patch waiting on the syntax
of the SET command. Otherwise, I'll need to use alter commands.
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp.mit.edu:11371 |/
Philip Warner <pjw@rhyme.com.au> writes:
So, if you do make the changes, will the schema definition be affected by
those changes, or do you expect the tablespace to be embedded in the CREATE
SCHEMA command?
I thought the idea was for pg_dump to emit something like
SET magic_tablespace_variable = some_ts;
CREATE TABLE foo (columns...);
rather than
CREATE TABLE foo (columns...) TABLESPACE some_ts;
the point being no more and no less than this: if "some_ts" doesn't
exist (or you have other problems like insufficient permissions) then
the SET command will fail but CREATE TABLE will still succeed, allowing
the restore to complete in some reasonable fashion.
I am quite unsure why you are pushing this while also insisting that
we need "die_on_errors" mode for pg_restore. If you are going to die
on the first error then these alternatives are equally brittle.
regards, tom lane
At 03:15 PM 23/08/2004, Tom Lane wrote:
I thought the idea was for pg_dump to emit something like
SET magic_tablespace_variable = some_ts;
CREATE TABLE foo (columns...);rather than
CREATE TABLE foo (columns...) TABLESPACE some_ts;
the point being...
Yep.
I am quite unsure why you are pushing this while also insisting that
we need "die_on_errors" mode for pg_restore.
Because I expect scripts to die when they produce errors, and find the
recent change to be a step backward.
If you are going to die
on the first error then these alternatives are equally brittle.
Because I am told that many people like to continue regardless of errors,
in which case allowing tables to be created is way more useful & helpful.
The same is true for database & schema creation.
On the question of schemas, how would you expect:
SET magic_tablespace_variable = some_ts;
CREATE SCHEMA foo;
to behave? Would foo have a default tablespace of some_ts?
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp.mit.edu:11371 |/
Philip Warner <pjw@rhyme.com.au> writes:
On the question of schemas, how would you expect:
SET magic_tablespace_variable = some_ts;
CREATE SCHEMA foo;
to behave? Would foo have a default tablespace of some_ts?
Yeah, I would think so. I sure don't see a value in inventing two
different magic_tablespace_variables for tables and schemas ...
regards, tom lane
Philip Warner wrote:
At 03:15 PM 23/08/2004, Tom Lane wrote:
I thought the idea was for pg_dump to emit something like
SET magic_tablespace_variable = some_ts;
CREATE TABLE foo (columns...);rather than
CREATE TABLE foo (columns...) TABLESPACE some_ts;
the point being...
Yep.
This brings up another issue. We now dump a non-standard clause from
pg_dump when using tablespaces:
CREATE TABLE xx (
y integer
) TABLESPACE tmp;
We avoided this with oids but it seems we have added another. I don't
see a good way of avoiding this like we had with oids.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Tom Lane wrote:
Philip Warner <pjw@rhyme.com.au> writes:
On the question of schemas, how would you expect:
SET magic_tablespace_variable = some_ts;
CREATE SCHEMA foo;
to behave? Would foo have a default tablespace of some_ts?Yeah, I would think so. I sure don't see a value in inventing two
different magic_tablespace_variables for tables and schemas ...
Now that I think of it, if we used 'SET magic_tablespace_variable' for
schemas and tables/indexes we could avoid the non-standard TABLESPACE
clause in CREATE TABLE.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Tom Lane wrote:
Philip Warner <pjw@rhyme.com.au> writes:
So, if you do make the changes, will the schema definition be affected by
those changes, or do you expect the tablespace to be embedded in the CREATE
SCHEMA command?I thought the idea was for pg_dump to emit something like
SET magic_tablespace_variable = some_ts;
CREATE TABLE foo (columns...);
rather than
CREATE TABLE foo (columns...) TABLESPACE some_ts;
the point being no more and no less than this: if "some_ts" doesn't
exist (or you have other problems like insufficient permissions) then
the SET command will fail but CREATE TABLE will still succeed, allowing
the restore to complete in some reasonable fashion.
Right, this would eliminate our non-standard TABLESPACE clause appearing
in pg_dump CREATE TABLEs.
I am quite unsure why you are pushing this while also insisting that
we need "die_on_errors" mode for pg_restore. If you are going to die
on the first error then these alternatives are equally brittle.
I assume he wants to give users maximum flexibility.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073On Mon, 23 Aug 2004, Bruce Momjian wrote:
Philip Warner wrote:
At 03:15 PM 23/08/2004, Tom Lane wrote:
I thought the idea was for pg_dump to emit something like
SET magic_tablespace_variable = some_ts;
CREATE TABLE foo (columns...);rather than
CREATE TABLE foo (columns...) TABLESPACE some_ts;
the point being...
Yep.
This brings up another issue. We now dump a non-standard clause from
pg_dump when using tablespaces:CREATE TABLE xx (
y integer
) TABLESPACE tmp;We avoided this with oids but it seems we have added another. I don't
see a good way of avoiding this like we had with oids.
Isn't that what hte proposed (or implemented?) "SET DEFAULT_TABLESPACE"
was for?
----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
Marc G. Fournier wrote:
On Mon, 23 Aug 2004, Bruce Momjian wrote:
Philip Warner wrote:
At 03:15 PM 23/08/2004, Tom Lane wrote:
I thought the idea was for pg_dump to emit something like
SET magic_tablespace_variable = some_ts;
CREATE TABLE foo (columns...);rather than
CREATE TABLE foo (columns...) TABLESPACE some_ts;
the point being...
Yep.
This brings up another issue. We now dump a non-standard clause from
pg_dump when using tablespaces:CREATE TABLE xx (
y integer
) TABLESPACE tmp;We avoided this with oids but it seems we have added another. I don't
see a good way of avoiding this like we had with oids.Isn't that what hte proposed (or implemented?) "SET DEFAULT_TABLESPACE"
was for?
No. I think it was for allowing the table to be created even if the
tablespace doesn't exist.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Bruce Momjian <pgman@candle.pha.pa.us> writes:
Marc G. Fournier wrote:
We avoided this with oids but it seems we have added another. I don't
see a good way of avoiding this like we had with oids.Isn't that what hte proposed (or implemented?) "SET DEFAULT_TABLESPACE"
was for?
No. I think it was for allowing the table to be created even if the
tablespace doesn't exist.
Yes, that's exactly what it was for. I thought you had figured it out
as of your previous post ...
regards, tom lane
Christopher Kings-Lynne wrote:
This is an extension of tablespaces, and is not required to fix a bug ...
therefore, it is a feature, and not eligible for inclusion at this point
in the development cycle ...I am inclined to agree. ALTER INDEX is an operation that will happen
quite often, but I don't think ALTER SCHEMA will be as frequent, and the
given solution doesn't address the two needs of moving the entire schema
or just future object creation.No, it implements something that is currently impossible without editing
the system catalogs - clearly an oversight! Especially if we add a
disk-based change to it to avoid those drop tablespace errors.It will be 5 minutes before someone who has created a schema in 8.0 and
then will want to make it have a different default - there's no way they
can do it...
This is going to have to wait for 8.1. If it was so important, someone
would have asked for it long before we went beta. It is now on the TODO
list.
Also, I question the value of being able to change the default but not
being able to move the schema contents. In fact, I can't think of any
other case where we change the default for new objects but leave the old
objects with their original settings. This all needs discussion for
8.1.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Dear Bruce,
Just my 0.02 euro cents:
On Sat, 28 Aug 2004, Bruce Momjian wrote:
This is going to have to wait for 8.1. If it was so important, someone
would have asked for it long before we went beta.
I'm not sure it works that way. Not that simply anyway.
Those having a say BEFORE beta are those interested in the implementation
of the feature. For instance I'm not interested in how tablespace are
implemented, mostly because I don't have time and also because I think
that many people may have better ideas than mine on that issue.
Those having a say DURING beta are those interested in using the feature.
I'm interested in using that because I may need it. So I evaluate the
feature wrt how I may use it. The above syntax fix really look useful to
me from that point of view, even if it is not essential to tablespace
implementation.
So what does not seem important to developers may be seen as important to
users. ISTM that it is the case with tablespace, which is a nice feature
mostly implemented, but the small things that may be missing could make
all the difference wrt its usability by database administrators, and could
potentially harm postgresql reputation. That include fixing dump/restore,
being able to move objects between tablespace...
Hence all these arguments so that new features should be "clean" enough,
and that "workable" may not enough.
Have a nice day,
--
Fabien Coelho - coelho@cri.ensmp.fr
Fabien COELHO wrote:
Dear Bruce,
Just my 0.02 euro cents:
On Sat, 28 Aug 2004, Bruce Momjian wrote:
This is going to have to wait for 8.1. If it was so important, someone
would have asked for it long before we went beta.I'm not sure it works that way. Not that simply anyway.
Those having a say BEFORE beta are those interested in the implementation
of the feature. For instance I'm not interested in how tablespace are
implemented, mostly because I don't have time and also because I think
that many people may have better ideas than mine on that issue.Those having a say DURING beta are those interested in using the feature.
I'm interested in using that because I may need it. So I evaluate the
feature wrt how I may use it. The above syntax fix really look useful to
me from that point of view, even if it is not essential to tablespace
implementation.So what does not seem important to developers may be seen as important to
users. ISTM that it is the case with tablespace, which is a nice feature
mostly implemented, but the small things that may be missing could make
all the difference wrt its usability by database administrators, and could
potentially harm postgresql reputation. That include fixing dump/restore,
being able to move objects between tablespace...Hence all these arguments so that new features should be "clean" enough,
and that "workable" may not enough.
Yes, you are correct. Beta folks are more users and can find missing
functionality easier.
Fortunately, I think the change schema default tablespace is unusual
enough that we can wait for 8.1 where people will really want the
ability to move schemas for already-created objects too.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Fortunately, I think the change schema default tablespace is unusual
enough that we can wait for 8.1 where people will really want the
ability to move schemas for already-created objects too.
Ok. I disagree;-) Or one has to consider that tablespace is just
"experimental" and should not be used but for simple tests. If so, maybe a
warning should be output everytime the feature is used...
ISTM that most people don't need tablespace at all. So it is ok for them.
However, those who might consider using tablespace because they need it
will have somehow a lot of data. Their large amount of data is likely to
be precious to them. If they are messed up because they cannot move them
around as they need it, they won't be happy. Saying things like "you can
just pg_dump/pg_restore" or "edit pg_dump output by hand" won't make them
very happy either, esp if the amount of data is huge, and it may well be
the case if they use tablespace.
It seems to me that the current implementation is not completed. For
instance, do you expect someone to be able to remove a tablespace? At the
time it is not always possible, because all objects cannot be moved away
from a tablespace (namely sequence, as it is considered useless to move
them, what looks true from a performance perspective, but not from an
administrator perspective).
So it seems to me that tablespace handling must be looked at very
carefully from a "big data" user perspective and their need.
Well, as usual, I may be quite wrong;-)
Have a nice day, thanks for your answer,
--
Fabien Coelho - coelho@cri.ensmp.fr
Fabien COELHO wrote:
Fortunately, I think the change schema default tablespace is unusual
enough that we can wait for 8.1 where people will really want the
ability to move schemas for already-created objects too.Ok. I disagree;-) Or one has to consider that tablespace is just
"experimental" and should not be used but for simple tests. If so, maybe a
warning should be output everytime the feature is used...ISTM that most people don't need tablespace at all. So it is ok for them.
However, those who might consider using tablespace because they need it
will have somehow a lot of data. Their large amount of data is likely to
be precious to them. If they are messed up because they cannot move them
around as they need it, they won't be happy. Saying things like "you can
just pg_dump/pg_restore" or "edit pg_dump output by hand" won't make them
very happy either, esp if the amount of data is huge, and it may well be
the case if they use tablespace.It seems to me that the current implementation is not completed. For
instance, do you expect someone to be able to remove a tablespace? At the
time it is not always possible, because all objects cannot be moved away
from a tablespace (namely sequence, as it is considered useless to move
them, what looks true from a performance perspective, but not from an
administrator perspective).So it seems to me that tablespace handling must be looked at very
carefully from a "big data" user perspective and their need.Well, as usual, I may be quite wrong;-)
You make a strong argument. All we can tell people is that we did the
best we could given our constraints and that it will be improved in 8.1.
We already have TODO entries for all tablespace limitations and
fortunately most people read that file so hopefully people will prefer
what we gave them rather than nothing. They aren't required to use them
if the limitations are not acceptable. We could mention the limitations
in the release notes if people want that.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Fabien COELHO <coelho@cri.ensmp.fr> writes:
It seems to me that the current implementation is not completed. For
instance, do you expect someone to be able to remove a tablespace?
Not when it still contains objects.
At the
time it is not always possible, because all objects cannot be moved away
from a tablespace (namely sequence, as it is considered useless to move
them, what looks true from a performance perspective, but not from an
administrator perspective).
The sequences are in the same tablespace as the system catalogs of the
database they are in, so this objection is moot. (And no, we are not
accepting any suggestions about an ALTER command to relocate pg_class
on the fly...)
regards, tom lane
Fabien COELHO <fabien.coelho@ensmp.fr> writes:
The sequences are in the same tablespace as the system catalogs of the
database they are in, so this objection is moot.
three (unusual?) commands to reach the "moot" state:
psql> CREATE TABLESPACE foo LOCATION '/tmp/postgres';
psql> CREATE SCHEMA bla TABLESPACE foo;
psql> CREATE TABLE bla.boo(id SERIAL PRIMARY KEY);
Now you have schema bla, table bla.boo, index bla.boo_pkey and sequence
bla.boo_id_seq all in tablespace foo.
Hmm, that's a bug. The intention was that sequences would always be
in the database default tablespace. I'm not sure why this case is
overriding that ... but we can fix it.
regards, tom lane
Import Notes
Reply to msg id not found: Pine.LNX.4.58.0408311046010.28337@sablons.cri.ensmp.fr
Dear Tom,
psql> CREATE TABLE bla.boo(id SERIAL PRIMARY KEY);
Now you have schema bla, table bla.boo, index bla.boo_pkey and sequence
bla.boo_id_seq all in tablespace foo.Hmm, that's a bug.
If you consider that a bug, then you can also add: "CREATE SEQUENCE bla.sg;"
The intention was that sequences would always be in the database default
tablespace. I'm not sure why this case is overriding that ...
It does what the doc says about schema tablespaces:
"The name of the tablespace that is to be the default tablespace for
all new objects created in the schema. If not supplied, the schema will
inherit the default tablespace of the database."
and that looks sound enough to me...
but we can fix it.
Good.
--
Fabien Coelho - coelho@cri.ensmp.fr
Fabien COELHO wrote:
Dear Tom,
psql> CREATE TABLE bla.boo(id SERIAL PRIMARY KEY);
Now you have schema bla, table bla.boo, index bla.boo_pkey and sequence
bla.boo_id_seq all in tablespace foo.Hmm, that's a bug.
If you consider that a bug, then you can also add: "CREATE SEQUENCE bla.sg;"
The intention was that sequences would always be in the database default
tablespace. I'm not sure why this case is overriding that ...It does what the doc says about schema tablespaces:
"The name of the tablespace that is to be the default tablespace for
all new objects created in the schema. If not supplied, the schema will
inherit the default tablespace of the database."and that looks sound enough to me...
Good point. If we put sequences in the database default location it
would be inconsistent with other object location, but this does bring up
the problem of being unable to move sequences to get rid of a
tablespace.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Now you have schema bla, table bla.boo, index bla.boo_pkey and sequence
bla.boo_id_seq all in tablespace foo. ISTM that tablespace foo cannot be
removed without hand-fixing pg_catalog and hand-moving files or links in
the database directory.
Hmmm, I wonder why the tablespace is set to the foo tablespace for the
sequence? Sequences are supposed to always be created in the default
tablespace. Bug?
Import Notes
Reply to msg id not found: Pine.LNX.4.58.0408311118030.28337@sablons.cri.ensmp.frReference msg id not found: Pine.LNX.4.58.0408311118030.28337@sablons.cri.ensmp.fr | Resolved by subject fallback
I am thinking some day we will need:
ALTER SCHEMA ... SET NEW TABLESPACE
and
ALTER SCHEMA ... SET CURRENT TABLESPACE
to specify if existing objects are moved, but at this point we aren't
going to get the later in 8.1, so I guess we will just go with an
unadorned stynax.
In fact, the new syntax might just be:
ALTER SCHEMA ... MOVE TABLESPACE
or
ALTER SCHEMA ... RELOCATE TABLESPACE
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.
---------------------------------------------------------------------------
Gavin Sherry wrote:
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
Content-Description:
[ Attachment, skipping... ]
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073On Sat, 4 Jun 2005, Bruce Momjian wrote:
I am thinking some day we will need:
ALTER SCHEMA ... SET NEW TABLESPACE
and
ALTER SCHEMA ... SET CURRENT TABLESPACE
to specify if existing objects are moved, but at this point we aren't
going to get the later in 8.1, so I guess we will just go with an
unadorned stynax.
I must have missed something (likely, as I've been away from a computer
for 6 weeks) but didn't we scrap the idea of schemas having a default
tablespace?
Gavin
Gavin Sherry <swm@linuxworld.com.au> writes:
I must have missed something (likely, as I've been away from a computer
for 6 weeks) but didn't we scrap the idea of schemas having a default
tablespace?
Yeah, we did, but Bruce has still got that old patch of yours in his
to-do queue. It's irrelevant now, Bruce ...
regards, tom lane
Patch withdrawn by author --- not needed.
---------------------------------------------------------------------------
Gavin Sherry wrote:
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
Content-Description:
[ Attachment, skipping... ]
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Thanks, removed.
---------------------------------------------------------------------------
Tom Lane wrote:
Gavin Sherry <swm@linuxworld.com.au> writes:
I must have missed something (likely, as I've been away from a computer
for 6 weeks) but didn't we scrap the idea of schemas having a default
tablespace?Yeah, we did, but Bruce has still got that old patch of yours in his
to-do queue. It's irrelevant now, Bruce ...regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073