tablespace and sequences?

Fabien COELHO wrote:

(3) psql auto completion does not have "CREATE/DROP TABLESPACE" in
its list.

I have already posted a patch for
this(http://candle.pha.pa.us/mhonarc/patches/msg00000.html) and afaik it
is on Bruce's Beta-TODO list too.

Stefan

(3) psql auto completion does not have "CREATE/DROP TABLESPACE" in
its list.

I have already posted a patch for
this(http://candle.pha.pa.us/mhonarc/patches/msg00000.html)

Good. I should have checked the pending patch queue.

and afaik it is on Bruce's Beta-TODO list too.

Argh, I missed this one! Is it somewhere on line?

--
Fabien Coelho - coelho@cri.ensmp.fr

Fabien COELHO wrote:

Dear hackers,

Some minor comments about the new tablespace feature in 8.0beta1:

It seems to me that tablespaces and sequences are not yet prefectly
integrated.

(1) the "CREATE SEQUENCE foo TABLESPACE disk2" syntax does not seem
to be implemented.

(2) when creating an implicit sequence with SERIAL, the sequence
is created in the tablespace of the schema/database, not the one
of the table, although indexes are added to the tablespace
of the table. It would seem more logical to put it in
the same table space as the table by default?

We decided it didn't make much sense to allow the on-row sequences to be
anywhere but the default tablespace.

(3) psql auto completion does not have "CREATE/DROP TABLESPACE" in
its list.

Maybe these non-important issues could be added to the TODO list.
I've noticed some todos about tablespaces, but not these.

Yep, in patch queue.

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

Fabien COELHO wrote:

(3) psql auto completion does not have "CREATE/DROP TABLESPACE" in
its list.

I have already posted a patch for
this(http://candle.pha.pa.us/mhonarc/patches/msg00000.html)

Good. I should have checked the pending patch queue.

and afaik it is on Bruce's Beta-TODO list too.

Argh, I missed this one! Is it somewhere on line?

Yep, URL at the top:

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

P O S T G R E S Q L

8 . 0 O P E N I T E M S

Current version at ftp://momjian.postgresql.org/pub/postgresql/open_items.

Changes
-------
* Win32
o add binary version stamps?
o fix signal-safe socket handler for SSL
o fix query cancel in psql (?)
o report correct errno codes from native Windows system calls
o shorten timezone for %t log_line_prefix
o start pg_autovacuum easily
o fix users who's timezones are not recognized
o allow installed locales rather than hardcoded one
o update encoding list to include win1250
o synchonize supported encodings and docs
* fix oid2name for tablespaces
* allow libpq to check parameterized data types
* make pgxs install the default
* add xid to log_line_prefix for PITR
* add psql tab completion for tablespaces
* cleanup FRONTEND use in /port, malloc, elog
* fix recovery of DROP TABLESPACE after checkpoint
* fix ambiguity for objects using default tablespaces
* fix case where template db already uses target tablespace
* determine proper crash recovery/logging for pg_subtrans
* remove to_char(interval) if we initdb
* have plpython reject pseudotype arguments because it crashes
* add i386 solaris spinlock code

-- 
  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,

(1) the "CREATE SEQUENCE foo TABLESPACE disk2" syntax does not seem
to be implemented.

(2) when creating an implicit sequence with SERIAL, the sequence
is created in the tablespace of the schema/database, not the one
of the table, although indexes are added to the tablespace
of the table. It would seem more logical to put it in
the same table space as the table by default?

We decided it didn't make much sense to allow the on-row sequences to be
anywhere but the default tablespace.

Hmmm...

I can understand the performance/utility rationale, but I don't like the
lack of orthogonality on principle. I like elegance;-) As a sequence looks
a lot like a table, I guess it should not be that hard to have it anyway.

Well, just my little opinion, and not a big issue.

Thanks for your answer.

--
Fabien Coelho - coelho@cri.ensmp.fr

Fabien COELHO wrote:

Dear Bruce,

(1) the "CREATE SEQUENCE foo TABLESPACE disk2" syntax does not seem
to be implemented.

(2) when creating an implicit sequence with SERIAL, the sequence
is created in the tablespace of the schema/database, not the one
of the table, although indexes are added to the tablespace
of the table. It would seem more logical to put it in
the same table space as the table by default?

We decided it didn't make much sense to allow the on-row sequences to be
anywhere but the default tablespace.

Hmmm...

I can understand the performance/utility rationale, but I don't like the
lack of orthogonality on principle. I like elegance;-) As a sequence looks
a lot like a table, I guess it should not be that hard to have it anyway.

Well, just my little opinion, and not a big issue.

I can't remember why we didn't just make it orthoginal.

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

and afaik it is on Bruce's Beta-TODO list too.

Argh, I missed this one! Is it somewhere on line?

Yep, URL at the top:

Quite an unexpected location! thanks for the pointer.

Current version at ftp://momjian.postgresql.org/pub/postgresql/open_items.

IMVHO, I think the following todo item should make it for 8.0:

Allow database recovery where tablespaces can't be created

When a pg_dump is restored, all tablespaces will attempt to be
created in their original locations. If this fails, the user must
be able to adjust the restore process.

Indeed, if someone step to 8.0, make some use of tablespace, and connot
move its databases because of this issue, I guess she will not going to be
happy at all... I guess something like "--ignore-tablespace" at the
restoration phase would be good. At the dump phase it would be a minimum.

--
Fabien Coelho - coelho@cri.ensmp.fr

Fabien COELHO <coelho@cri.ensmp.fr> writes:

(1) the "CREATE SEQUENCE foo TABLESPACE disk2" syntax does not seem
to be implemented.

This is intentional. Sequences are not large enough to need to be
pushed around among multiple tablespaces. Also, if we did allow
sequences to be associated with tablespaces, we'd be precluding other
implementation changes that are on the wish-list (such as storing all
sequences in a single system table, instead of needing a separate disk
file for each one).

The original patch actually had support for specifying a tablespace for
a sequence. That was deliberately removed, and it's not going to go
back in later.

regards, tom lane

(1) the "CREATE SEQUENCE foo TABLESPACE disk2" syntax does not seem
to be implemented.

This is intentional. Sequences are not large enough to need to be
pushed around among multiple tablespaces. Also, if we did allow
sequences to be associated with tablespaces, we'd be precluding other
implementation changes that are on the wish-list (such as storing all
sequences in a single system table, instead of needing a separate disk
file for each one).

That is a point.

As for the semantics, sequences have a tablespace anyway, which is the
default tablespace of the schema as it seems, and it appears in pg_class,
so it is already implemented somewhere, no doubt about that.

As for the syntax, you could decide to ignore the tablespace part of the
syntax if such evolution would require it, maybe with some warning for the
user that part of its query is no more up to date...

It would no more a big deal than dropping "LOCATION" from "CREATE
DATABASE", which is not a upward compatible change and was performed
remorselessly anyway.

The original patch actually had support for specifying a tablespace for
a sequence. That was deliberately removed, and it's not going to go
back in later.

Too bad for elegance and orthogonality.

Thanks for your clear answer anyway;-)

Have a nice day,

--
Fabien Coelho - coelho@cri.ensmp.fr

We decided it didn't make much sense to allow the on-row sequences to be
anywhere but the default tablespace.

Hmmm...

I can understand the performance/utility rationale, but I don't like the
lack of orthogonality on principle. I like elegance;-) As a sequence looks
a lot like a table, I guess it should not be that hard to have it anyway.

Well, just my little opinion, and not a big issue.

Thanks for your answer.

Well then, should you be able to move composite types to other
tablespaces as well??

IMVHO, I think the following todo item should make it for 8.0:

Allow database recovery where tablespaces can't be created

When a pg_dump is restored, all tablespaces will attempt to be
created in their original locations. If this fails, the user must
be able to adjust the restore process.

Indeed, if someone step to 8.0, make some use of tablespace, and connot
move its databases because of this issue, I guess she will not going to be
happy at all... I guess something like "--ignore-tablespace" at the
restoration phase would be good. At the dump phase it would be a minimum.

How is that at all a problem? It's no different to the requirement to
have installed all your contrib .so's before running your restore,
what's so hard about making a few dirs? It's also no different to the
old database locations support. Personally, I think it's a non-issue.

It's also impossible to do as you suggest and have a --ignore-tablespace
flag. All it could do is at dump time to dump NO tablespace, which is
NOT what you want. At restore time it doesn't do anything since
pg_dumpall is a text format only.

Chris

Dear Christopher,

Allow database recovery where tablespaces can't be created

How is that at all a problem?

It is enough a small problem to be put in the todo list.

It's no different to the requirement to have installed all your contrib
.so's before running your restore, what's so hard about making a few
dirs? It's also no different to the old database locations support.
Personally, I think it's a non-issue.

Well, maybe.

I think the .so comparison is not fully appropriate, as the installation
is quite generic an issue, possibly addressed by packaging or some
scripting.

As for pg_dump/pg_restore, they are performed at the database level.
In the previous situation with "LOCATION", one had to handle the issue of
creating the database before a restoration.

Now with tablespace the issue is more specific, and it is possibly
embedded at the SQL level output by pg_dump/pg_restore, on which one has
much less control.

Well, maybe you suggest I can do some "| sed 's/TABLESPACE \w+//g' |" as a
kludge somewhere, or create dummy tablespaces even if I have only one
disk. That does not look really good, and I won't know what is needed by
looking at a pg_dump compressed generated file.

Maybe the right answer is that disks are now large and cheap, so who will
need tablespace anyway? So indeed there is no problem;-)

It's also impossible to do as you suggest and have a --ignore-tablespace
flag.

I was not arguing about implementation, but about a desirable feature for
a basic database admin.

Anyway, I think it could be implemented, possibly with some twicking in
the format, or with some setting on the server side.

Now I agree that any other feature which provide the ability to handle
this "non" issue would be welcome, I won't stick on this particular
option.

All it could do is at dump time to dump NO tablespace, which is
NOT what you want.

Wow! you know what I may want although I even don't know;-)

If I want to move a database from one server to another, I'm not sure the
disk layout and tablespace issues will have been handled the same way on
both machines. So some help to handle these issues would be welcome.

At restore time it doesn't do anything since pg_dumpall is a text format
only.

I'm not thinking about pg_dumpall but pg_dump/pg_restore.

Have a nice day,

--
Fabien Coelho - coelho@cri.ensmp.fr

At restore time it doesn't do anything since pg_dumpall is a text format
only.

I'm not thinking about pg_dumpall but pg_dump/pg_restore.

Tablespaces are dumped by pg_dumpall, not pg_dump.

Chris

I'm not thinking about pg_dumpall but pg_dump/pg_restore.

Tablespaces are dumped by pg_dumpall, not pg_dump.

If so, indeed it would be a non-issue. However,

shell> pg_dump coelho | grep TABLESPACE
CREATE SCHEMA test AUTHORIZATION coelho TABLESPACE test;

"TABLESPACE" appears in a basic pg_dump SQL output. If the test tablespace
does not exist, the command will fail, and so my whole restoration.

Thus I still stick to my opinion;-)

Have a nice day,

--
Fabien Coelho - coelho@cri.ensmp.fr

shell> pg_dump coelho | grep TABLESPACE
CREATE SCHEMA test AUTHORIZATION coelho TABLESPACE test;

"TABLESPACE" appears in a basic pg_dump SQL output. If the test tablespace
does not exist, the command will fail, and so my whole restoration.

Thus I still stick to my opinion;-)

Your complaint was that you need a way of continuing a restore if the
_tablespace_ cannot be created. ie. If the directory does not exist.

If you have objects in a tablespace, then too bad. It's no different to
if the schema the object in doesn't exist. Or the table the data is in
doesn't exist. Or the functin the view references doesn't exist.

Dear Christopher,

"TABLESPACE" appears in a basic pg_dump SQL output. If the test tablespace
does not exist, the command will fail, and so my whole restoration.

Thus I still stick to my opinion;-)

Your complaint was that you need a way of continuing a restore if the
_tablespace_ cannot be created. ie. If the directory does not exist.

Indeed it is possible that I was not clear enough!

The issue I feel should be addressed is the ability to restore a database
while ignoring tablespace issues, not only their creation but also their
uses.

If you have objects in a tablespace, then too bad.

Well, ISTM that it is the problem I'm discussing...

If I cannot restore a base I see that as a problem, which is indeed a lack
of humour from my side.

It's no different to if the schema the object in doesn't exist.
Or the table the data is in doesn't exist.
Or the functin the view references doesn't exist.

It is a little bit different because a schema, a table or a function are
database application issues and are normally addressed by pg_dump and
pg_restore, although tablespaces are more an administration issue wrt disk
layout and the like, which are likely to be different from one machine to
another (compare with I obviously want the same schema/table/function for
my application). So the notion of dump/restore of a tablespace need
some careful thinking.

But maybe I'm just stupid to dream that I could restore or transfer my
data even if I used a tablespace somewhere? ;-)

It looks that we don't have the same perspective about database
administration.

Anyway, have a nice day,

--
Fabien Coelho - coelho@cri.ensmp.fr

On Wednesday 18 August 2004 04:39, Christopher Kings-Lynne wrote:

shell> pg_dump coelho | grep TABLESPACE
CREATE SCHEMA test AUTHORIZATION coelho TABLESPACE test;

"TABLESPACE" appears in a basic pg_dump SQL output. If the test
tablespace does not exist, the command will fail, and so my whole
restoration.

Thus I still stick to my opinion;-)

Your complaint was that you need a way of continuing a restore if the
_tablespace_ cannot be created. ie. If the directory does not exist.

If you have objects in a tablespace, then too bad. It's no different to
if the schema the object in doesn't exist. Or the table the data is in
doesn't exist. Or the functin the view references doesn't exist.

Chris, help me understand this will you? On my production system I have a few
very large tables I want to move into their own tablespace so I can but them
a a very large disk, and a couple frequently updated tables I would like to
move into their own tablespace so i can put them on their own (small, raid
oriented) disk. I need to do all this from a physical side of things for
performance and administration in production, but when I create test
databases for developers/testing, I don't want to have to recreate the same
physical layout on every system.... it sounds like you are saying that is the
case... or maybe I am misreading you?

--
Robert Treat
Build A Better Lamp :: Linux Apache {middleware} PostgreSQL

Dear Robert,

Chris, help me understand this will you?

I'm not Chris, but it looks like Robert may eventually share my concerns,
so I'm happy not to be alone on this one ;-)

On my production system I have a few very large tables I want to move
into their own tablespace so I can but them a a very large disk, and a
couple frequently updated tables I would like to move into their own
tablespace so i can put them on their own (small, raid oriented) disk.
I need to do all this from a physical side of things for performance and
administration in production, but when I create test databases for
developers/testing, I don't want to have to recreate the same physical
layout on every system....

What you describe is basically the reason why I'm advocating, quite
unsuccessfully at the time, that pg_dump/pg_restore should deal with
tablespace in some careful and appropriate manner even in coming 8.0.

it sounds like you are saying that is the case...

It is indeed the case and the reason for my query about the todo item. The
current status is that you cannot restore a dump if tablespaces where used
if the same tablespaces do not exist in the target system. So it is fine
if you want to restore on the same system, but not on another one. You
would have to create them artificially or to edit them out of the script
if you want a transfer on a different system.

Have a nice day,

--
Fabien Coelho - coelho@cri.ensmp.fr

It is a little bit different because a schema, a table or a function are
database application issues and are normally addressed by pg_dump and
pg_restore, although tablespaces are more an administration issue wrt disk
layout and the like, which are likely to be different from one machine to
another (compare with I obviously want the same schema/table/function for
my application). So the notion of dump/restore of a tablespace need
some careful thinking.

But maybe I'm just stupid to dream that I could restore or transfer my
data even if I used a tablespace somewhere? ;-)

OK, perhaps. It it not easy to implement however, since the tablespace
clause on indexes comes from the pg_get_indexdef() function and isn't
added by pg_dump.

Bruce - pg_dump TODO for --no-tablespace or something?

Chris