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

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?
>
> http://archives.postgresql.org

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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?
>>
>> http://archives.postgresql.org
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)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?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

This 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?
>
> http://archives.postgresql.org

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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:
> 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?
>>
>> http://archives.postgresql.org
>
>

>>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(at)hub(dot)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(at)hub(dot)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(at)hub(dot)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(at)hub(dot)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(at)rhyme(dot)com(dot)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(at)rhyme(dot)com(dot)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(at)rhyme(dot)com(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Tom Lane wrote:
> Philip Warner <pjw(at)rhyme(dot)com(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Tom Lane wrote:
> Philip Warner <pjw(at)rhyme(dot)com(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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?

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)cri(dot)ensmp(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

> 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;-)

Have a nice day, thanks for your answer,

--
Fabien Coelho - coelho(at)cri(dot)ensmp(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Fabien COELHO <coelho(at)cri(dot)ensmp(dot)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(dot)coelho(at)ensmp(dot)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

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(at)cri(dot)ensmp(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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?
>
> http://archives.postgresql.org

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On 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(at)linuxworld(dot)com(dot)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?
>
> http://archives.postgresql.org

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Thanks, removed.

---------------------------------------------------------------------------

Tom Lane wrote:
> Gavin Sherry <swm(at)linuxworld(dot)com(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073