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+250-24
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 |/
