I know the current method for specifying alternate drives for PG tables is
by using symlinks.  I had some ideas for simple ways to do this in PG
code, but wanted to know if anyone was working on this right now.  I'd
hate to take the time to start messing with this if others were already on
it.

Jon


Tue, 24 Jun 2003 14:48:35 -0700 (PDT)
Jonathan Bartlett <johnnyb@eskimo.com>
Physical Database Configuration
On 24 Jun 2003 at 14:48, Jonathan Bartlett wrote:

> I know the current method for specifying alternate drives for PG tables is
> by using symlinks.  I had some ideas for simple ways to do this in PG
> code, but wanted to know if anyone was working on this right now.  I'd
> hate to take the time to start messing with this if others were already on
> it.

Well, correct solution is to implement tablespaces on which objects like 
databases, tables and indexes can be put. 

There was a long discussion on this and there was a tablespaces patch. It was 
agreed that tablespace as a set of directories would be a good point to start.

I have no idea what is the status of that effort right now. You can search the 
archives or I hope this kicks a fresh discussion..:-)

Please correct me if I am wrong. I am quoting from off my head.. not a trusted 
source..

Bye
 Shridhar

--
Harriet's Dining Observation:	In every restaurant, the hardness of the butter 
pats	increases in direct proportion to the softness of the bread.


Wed, 25 Jun 2003 12:06:37 +0530
"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
Re: Physical Database Configuration
> Well, correct solution is to implement tablespaces on which objects like
> databases, tables and indexes can be put.

I have started working on tablespaces (to the extent that I am capable!),
based not on the rejected patch, but on Jim's eventual syntax proposal that
was never developed.

eg.

CREATE LOCATION blah AS '/exports/indexes'

CREATE DATABASE db WITH LOCATION loc;

CREAT TABLE foo (a PRIMARY KEY LOCATION blah) LOCATION blah;

..etc...

> There was a long discussion on this and there was a tablespaces patch. It
was
> agreed that tablespace as a set of directories would be a good point to
start.

If anyone wants to help me (as I've not had time to code on it for a while
due to phpPgAdmin), then they can email
me!

I'm working from a top-down perspective - eg. adding new catalog and grammar
and support functions before mucking about with low level storage...

Chris


Wed, 25 Jun 2003 14:55:41 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: [GENERAL] Physical Database Configuration
On 25 Jun 2003 at 14:55, Christopher Kings-Lynne wrote:

> > Well, correct solution is to implement tablespaces on which objects like
> > databases, tables and indexes can be put.
> 
> I have started working on tablespaces (to the extent that I am capable!),
> based not on the rejected patch, but on Jim's eventual syntax proposal that
> was never developed.

Hmm... Remember feature freeze is 1st of July. So unless you send out a 
minimally working patch before that, it won't be considered for 7.4.

> CREATE LOCATION blah AS '/exports/indexes'
> 
> CREATE DATABASE db WITH LOCATION loc;
> 
> CREAT TABLE foo (a PRIMARY KEY LOCATION blah) LOCATION blah;
> 
> ..etc...
> 
> > There was a long discussion on this and there was a tablespaces patch. It
> was
> > agreed that tablespace as a set of directories would be a good point to
> start.
> 
> If anyone wants to help me (as I've not had time to code on it for a while
> due to phpPgAdmin), then they can email
> me!
> 
> I'm working from a top-down perspective - eg. adding new catalog and grammar
> and support functions before mucking about with low level storage...

I would love to hack this one. Especially putting WAL in a location that is 
configurable, I mean PG knowing where to find it's WAL.

If you complete the syntactic part, I guess a very very rough patch should be 
possible before feature freeze but that is way tooooooo optimistic.

Besides I don't know what core thinks about introducing such a feature right 
now. I think it would be a tough sell at this point of time..


Bye
 Shridhar

--
Virtue is a relative term.		-- Spock, "Friday's Child", stardate 3499.1


Wed, 25 Jun 2003 12:30:09 +0530
"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
Re: [GENERAL] Physical Database Configuration
> > I have started working on tablespaces (to the extent that I am
capable!),
> > based not on the rejected patch, but on Jim's eventual syntax proposal
that
> > was never developed.
>
> Hmm... Remember feature freeze is 1st of July. So unless you send out a
> minimally working patch before that, it won't be considered for 7.4.

I have no intention of having it ready anywhere near 7.4 :)

> > I'm working from a top-down perspective - eg. adding new catalog and
grammar
> > and support functions before mucking about with low level storage...
>
> I would love to hack this one. Especially putting WAL in a location that
is
> configurable, I mean PG knowing where to find it's WAL.

This patch won't affect WAL location - that's a separate issue.

> If you complete the syntactic part, I guess a very very rough patch should
be
> possible before feature freeze but that is way tooooooo optimistic.

Not going to happen :)  I haven't done very much on it yet.

Chris


Wed, 25 Jun 2003 15:11:00 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: [GENERAL] Physical Database Configuration
On 25 Jun 2003 at 12:30, Shridhar Daithankar wrote:

> On 25 Jun 2003 at 14:55, Christopher Kings-Lynne wrote:
> 
> > > Well, correct solution is to implement tablespaces on which objects like
> > > databases, tables and indexes can be put.
> > 
> > I have started working on tablespaces (to the extent that I am capable!),
> > based not on the rejected patch, but on Jim's eventual syntax proposal that
> > was never developed.

For reference, 

http://archives.postgresql.org/pgsql-hackers/2002-09/msg01780.php

Bye
 Shridhar

--
Rules for Academic Deans:	(1)  HIDE!!!!	(2)  If they find you, LIE!!!!		-- 
Father Damian C. Fandal


Wed, 25 Jun 2003 14:59:36 +0530
"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
Re: [GENERAL] Physical Database Configuration
> Well, correct solution is to implement tablespaces on which objects like 
> databases, tables and indexes can be put. 

I've not looked at the SQL standard, but it seems to me like the order 
should be:

Databases
   Tablespaces
      Schemas
         Objects (tables, indexes, functions, etc.)

And it really isn't hierarchical.  As I understand them (based on my 
Oracle background), tablespaces, unlike schemas, do NOT create a layer 
of data abstraction.   That is to say, while the same table name
can exist in multiple schemas, only one instance of a given table name
within a given schema can exist, regardless of what tablespace it is in.

That makes the tablespace a property of an object.

Whether or not two databases can share tablespaces isn't clear to me, 
though as a DBA I can think of good reasons why they probably shouldn't
do so, I'm not sure if that is an absolute.

> I have no idea what is the status of that effort right now. You can search the 
> archives or I hope this kicks a fresh discussion..:-)

I'm game, though I'm also not ready to lead such a project, probably not
even the discussion on it.
--
Mike Nolan


Wed, 25 Jun 2003 10:19:42 -0500 (CDT)
nolan@celery.tssi.com
Re: Physical Database Configuration

DB2 looks good. I have horrid, horrid memories of wrestling with the Oracle
"extent" madness.

andrew

AgentM wrote:
> On Wednesday, June 25, 2003, at 12:10 PM, johnnnnnn wrote:
>> On Wed, Jun 25, 2003 at 11:34:14AM -0400, Tom Lane wrote:
>>> Has anyone looked at the syntaxes used by other databases to control
>>> tablespaces (Oracle, DB2, etc)?  I have no strong desire to
>>> slavishly follow Oracle, but it would be a shame to miss out on any
>>> good ideas.
>>
>> DB2:
>> CREATE TABLESPACE spacename ...
>> ALTER TABLESPACE spacename ...
>> RENAME TABLESPACE spacename TO newspacename
>> CREATE TABLE name ... IN spacename [INDEX IN spacename] [LONG IN
>> spacename]
>> "INDEX IN" and "LONG IN" refer to the tablespace used to store the
>> indices and the LOB values for that table, respectively.
>> The create syntax revolves around nodegroups and such which are DB2
>> concepts i don't fully grok (i'm a programmer, not a DBA).
>> But, yeah, those are really the only entrypoints. You can't create an
>> index in a specific tablespace -- it will go wherever the table is set
>> to put indices.
>> I like the syntax ("IN spacename"), though. It's simple and
>> straightforward.
> Oracle 8 examples:
>
> CREATE TABLE name(dud INTEGER) storage 8M next 4M pctincrease 0
> minextents 1 maxextents 200 tablespace TSNAME;
>
> where storage, next, pctincrease, minextents, and maxentents are table
> space usage granularity requests.
>
> CREATE TABLESPACE TSNAME datafile '/path/file.dbf' size 100M,
> '/another/file.dbf' size 50M default storage (initial 1M next 1M
> pctincrease 0 maxentents 249);
>
> where each comma-delimited item is an "extent"- simply put, a block
> which Oracle is allowed to use for storage.
>
> ALTER TABLESPACE TEMP ...;
>
> allows for arbitrary placement of temporary table storage (higher-speed
>  area?)
>
> ALTER TABLESPACE TSNAME default storage (...);
>
> changes settings for tablespace.
>
> ALTER TABLESPACE TSNAME coalesce;
>
> more extent "optimization" granularlity.
>
> CREATE ROLLBACK SEGMENT R1 tablespace TSNAME2 storage (...);
>
> which allocates space for a rollback area.
>
> ALTER ROLLBACK SEGMENT R1 offline/online;
>
> allows for cleanup of rollback segment's area.
>
> CREATE TABLE name(dud INTEGER PRIMARY KEY USING INDEX ....);
>
> allows for pointing an index to a tablespace.
>
> CREATE INDEX ind ON table(col) global/local partition by range(col)
> (partition PART1 values less than (11) tablespace TS1,
> partition PART2 values less than (21) tablespace TS2,
> ....
> partition PART3 values less than (MAXVALUE) tablespace TS3);
>
> allows for a partioned index across tablespaces, but whose grammar
> setup could use some work.
>
> ALTER TABLE table MODIFY PARTITION part1storage (...) logging/nologging
> 	MOVE PARTITION
> 	ADD PARTITION part1 values less than (...)
> 	DROP PARTITION
> 	TRUNCATE PARTITION
> 	SPLIT PARTITION ... INTO ...
> 	EXCHANGE PARTITION
>
> a nasty alter table command related to partitions (a tablespace can
> have multiple partitions).
>
> I post this just so there a flavor of how many "optimization" options
> are available in Oracle 8. Personally, I would prefer not to have so
> many options but this listing should help folks so they don't paint
> themselves into a corner while coding on the tablespaces.
>
> All examples courtesy of "Oracle 8: Advanced Tuning and
> Administration", Aronoff, Eyal, et al.  ASIN: 0078822416 (c) 1998.
> (perhaps a little outdated)
>
>
>
> ><><><><><><><><><
> AgentM
> agentm@cmu.edu
>
>
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 5: Have you checked our
> extensive FAQ?
>
>               http://www.postgresql.org/docs/faqs/FAQ.html




Wed, 25 Jun 2003 10:30:31 -0500 (EST)
"Andrew Dunstan" <andrew@dunslane.net>
Re: [GENERAL] Physical Database Configuration
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> I have started working on tablespaces (to the extent that I am capable!),
> based not on the rejected patch, but on Jim's eventual syntax proposal that
> was never developed.

Has anyone looked at the syntaxes used by other databases to control
tablespaces (Oracle, DB2, etc)?  I have no strong desire to slavishly
follow Oracle, but it would be a shame to miss out on any good ideas.

			regards, tom lane

Wed, 25 Jun 2003 11:34:14 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [GENERAL] Physical Database Configuration 
My solution did not involve tablespaces, but was more of a quick solution
to make it easier for admins to do _some_ sort of physical configuration.

The idea is that the developer could do something like

'create alternate location ALTERNATE_LOCATION_NAME for
DATABASE_OBJECT_NAME at "/PATH/TO/PHYSICAL/FILE";'

We would have a system table holding theses values. Then, all database
commands which create a file for an object, call open_object(oid,
object_name) or something to create the file object.  This will first look
in the new system table to see if there is a mapping for an object of this
name.  If so, it will create a symlink to "/PATH/TO/PHYSICAL/FILE" for the
oid before opening the file.

Anyway, if people are working on tablespaces, I'll defer to them.  This
small fix is something that I might actually have time to do, but
tablespaces definitely not.

Jon

On Wed, 25 Jun 2003 nolan@celery.tssi.com wrote:

> > Well, correct solution is to implement tablespaces on which objects like
> > databases, tables and indexes can be put.
>
> I've not looked at the SQL standard, but it seems to me like the order
> should be:
>
> Databases
>    Tablespaces
>       Schemas
>          Objects (tables, indexes, functions, etc.)
>
> And it really isn't hierarchical.  As I understand them (based on my
> Oracle background), tablespaces, unlike schemas, do NOT create a layer
> of data abstraction.   That is to say, while the same table name
> can exist in multiple schemas, only one instance of a given table name
> within a given schema can exist, regardless of what tablespace it is in.
>
> That makes the tablespace a property of an object.
>
> Whether or not two databases can share tablespaces isn't clear to me,
> though as a DBA I can think of good reasons why they probably shouldn't
> do so, I'm not sure if that is an absolute.
>
> > I have no idea what is the status of that effort right now. You can search the
> > archives or I hope this kicks a fresh discussion..:-)
>
> I'm game, though I'm also not ready to lead such a project, probably not
> even the discussion on it.
> --
> Mike Nolan
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>


Wed, 25 Jun 2003 08:51:30 -0700 (PDT)
Jonathan Bartlett <johnnyb@eskimo.com>
Re: Physical Database Configuration
On Wednesday 25 June 2003 20:49, nolan@celery.tssi.com wrote:
> > Well, correct solution is to implement tablespaces on which objects like
> > databases, tables and indexes can be put.
>
> I've not looked at the SQL standard, but it seems to me like the order
> should be:
>
> Databases
>    Tablespaces
>       Schemas
>          Objects (tables, indexes, functions, etc.)

That should be 

Tablespaces
  databases
   schemas
    objects

with each of them implemented as a directory and data files under it. If we 
could get a quota check propogated in both direction, that would be pretty 
good, may be a warning when things start getting close to limit. 

> And it really isn't hierarchical.  As I understand them (based on my
> Oracle background), tablespaces, unlike schemas, do NOT create a layer
> of data abstraction.   That is to say, while the same table name
> can exist in multiple schemas, only one instance of a given table name
> within a given schema can exist, regardless of what tablespace it is in.

Well, if same table name exists in two different databases under same 
tablespace, what's the problem?

> Whether or not two databases can share tablespaces isn't clear to me,
> though as a DBA I can think of good reasons why they probably shouldn't
> do so, I'm not sure if that is an absolute.

Well, I would say they should be allowed to.

 Shridhar


Wed, 25 Jun 2003 21:24:58 +0530
Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
Re: [GENERAL] Physical Database Configuration
On Wed, Jun 25, 2003 at 11:34:14AM -0400, Tom Lane wrote:
> Has anyone looked at the syntaxes used by other databases to control
> tablespaces (Oracle, DB2, etc)?  I have no strong desire to
> slavishly follow Oracle, but it would be a shame to miss out on any
> good ideas.

DB2:

CREATE TABLESPACE spacename ...

ALTER TABLESPACE spacename ...

RENAME TABLESPACE spacename TO newspacename

CREATE TABLE name ... IN spacename [INDEX IN spacename] [LONG IN spacename]


"INDEX IN" and "LONG IN" refer to the tablespace used to store the
indices and the LOB values for that table, respectively.

The create syntax revolves around nodegroups and such which are DB2
concepts i don't fully grok (i'm a programmer, not a DBA).

But, yeah, those are really the only entrypoints. You can't create an
index in a specific tablespace -- it will go wherever the table is set
to put indices.

I like the syntax ("IN spacename"), though. It's simple and
straightforward.

-johnnnnnnnnnn


Wed, 25 Jun 2003 11:10:22 -0500
johnnnnnn <john@phaedrusdeinus.org>
Re: [GENERAL] Physical Database Configuration
En un mensaje anterior, nolan@celery.tssi.com escribiï¿½:
> > Well, correct solution is to implement tablespaces on which objects like 
> > databases, tables and indexes can be put. 
> 
> I've not looked at the SQL standard, but it seems to me like the order 
> should be:
> 
> Databases
>    Tablespaces
>       Schemas
>          Objects (tables, indexes, functions, etc.)
> 

I'm not well versed in the SQL standard here, so maybe this is plain wrong, but
I think it would be nice to have some kind of separation between the logical
structure of the table (developer concern) and the physical disposition (DBA
concern), unlike what Oracle does (CREATE TABLE ... TABLESPACE ... OTHER
PHYSICAL PARAMETERS HERE).

Maybe a way is having storage classes:

CREATE TABLE ... STORAGE CLASS <name>.
STORAGE CLASS <name> TABLESPACE ...

Example of use:

Developer:

CREATE TABLE a (...) STORAGE CLASS big_tuples;
CREATE TABLE b (...) STORAGE CLASS heavy_use;

DBA:

STORAGE CLASS big_tuples TABLESPACE x;
STORAGE CLASS heavy_use TABLESPACE y;

Regards.

Fernando.

Wed, 25 Jun 2003 13:13:14 -0300
Fernando Schapachnik <fernando@mecon.gov.ar>
Re: Physical Database Configuration
On Wednesday, June 25, 2003, at 12:10 PM, johnnnnnn wrote:
> On Wed, Jun 25, 2003 at 11:34:14AM -0400, Tom Lane wrote:
>> Has anyone looked at the syntaxes used by other databases to control
>> tablespaces (Oracle, DB2, etc)?  I have no strong desire to
>> slavishly follow Oracle, but it would be a shame to miss out on any
>> good ideas.
>
> DB2:
> CREATE TABLESPACE spacename ...
> ALTER TABLESPACE spacename ...
> RENAME TABLESPACE spacename TO newspacename
> CREATE TABLE name ... IN spacename [INDEX IN spacename] [LONG IN 
> spacename]
> "INDEX IN" and "LONG IN" refer to the tablespace used to store the
> indices and the LOB values for that table, respectively.
> The create syntax revolves around nodegroups and such which are DB2
> concepts i don't fully grok (i'm a programmer, not a DBA).
> But, yeah, those are really the only entrypoints. You can't create an
> index in a specific tablespace -- it will go wherever the table is set
> to put indices.
> I like the syntax ("IN spacename"), though. It's simple and
> straightforward.
Oracle 8 examples:

CREATE TABLE name(dud INTEGER) storage 8M next 4M pctincrease 0 
minextents 1 maxextents 200 tablespace TSNAME;

where storage, next, pctincrease, minextents, and maxentents are table 
space usage granularity requests.

CREATE TABLESPACE TSNAME datafile '/path/file.dbf' size 100M, 
'/another/file.dbf' size 50M default storage (initial 1M next 1M 
pctincrease 0 maxentents 249);

where each comma-delimited item is an "extent"- simply put, a block 
which Oracle is allowed to use for storage.

ALTER TABLESPACE TEMP ...;

allows for arbitrary placement of temporary table storage (higher-speed 
area?)

ALTER TABLESPACE TSNAME default storage (...);

changes settings for tablespace.

ALTER TABLESPACE TSNAME coalesce;

more extent "optimization" granularlity.

CREATE ROLLBACK SEGMENT R1 tablespace TSNAME2 storage (...);

which allocates space for a rollback area.

ALTER ROLLBACK SEGMENT R1 offline/online;

allows for cleanup of rollback segment's area.

CREATE TABLE name(dud INTEGER PRIMARY KEY USING INDEX ....);

allows for pointing an index to a tablespace.

CREATE INDEX ind ON table(col) global/local partition by range(col)
(partition PART1 values less than (11) tablespace TS1,
partition PART2 values less than (21) tablespace TS2,
....
partition PART3 values less than (MAXVALUE) tablespace TS3);

allows for a partioned index across tablespaces, but whose grammar 
setup could use some work.

ALTER TABLE table MODIFY PARTITION part1storage (...) logging/nologging
	MOVE PARTITION
	ADD PARTITION part1 values less than (...)
	DROP PARTITION
	TRUNCATE PARTITION
	SPLIT PARTITION ... INTO ...
	EXCHANGE PARTITION

a nasty alter table command related to partitions (a tablespace can 
have multiple partitions).

I post this just so there a flavor of how many "optimization" options 
are available in Oracle 8. Personally, I would prefer not to have so 
many options but this listing should help folks so they don't paint 
themselves into a corner while coding on the tablespaces.

All examples courtesy of "Oracle 8: Advanced Tuning and 
Administration", Aronoff, Eyal, et al.  ASIN: 0078822416 (c) 1998. 
(perhaps a little outdated)
	


 ><><><><><><><><><
AgentM
agentm@cmu.edu



Wed, 25 Jun 2003 14:48:49 -0400
AgentM <agentm@cmu.edu>
Re: [GENERAL] Physical Database Configuration
On Wed, 25 Jun 2003, Tom Lane wrote:

> Has anyone looked at the syntaxes used by other databases to control
> tablespaces (Oracle, DB2, etc)?  I have no strong desire to slavishly
> follow Oracle, but it would be a shame to miss out on any good ideas.
>
Informix is pretty bad.
First, you use an external app to create the tablespace (known as a
dbspace to informix). Lets call the new one 'newspace'.  (the syntax is
onspaces -c -d newspace -p /path/to/space -s size_in_kb -o
offset_in_file I'll cry if we have something liek that in pg)

then to 'use' the space:

create table|index ... in newspace

There's a bizzare syntax for copying a table from one space to another,
but it is mostly useless since it runs in a transaction and if you have a
big table.. well you get the idea.

Where it gets more interesting is table fragments. Informix is able to
fragment a table based on a few different criteria.  Each fragment goes in
a separate dbspace and the idea is the planner is smart enough to realize
that it can parellelize seq scans and various other IO operations... but
given the nature of postgres I don't think we could build something like
that...

(for the record, the fragment types are round robin and expression. You
can fragment based on a limited-edition where clause.. )


--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/



Wed, 25 Jun 2003 15:23:16 -0400 (EDT)
Jeff <threshar@torgo.978.org>
Re: [GENERAL] Physical Database Configuration
On Wed, 2003-06-25 at 10:51, Jonathan Bartlett wrote:
> My solution did not involve tablespaces, but was more of a quick solution
> to make it easier for admins to do _some_ sort of physical configuration.
> 
> The idea is that the developer could do something like
> 
> 'create alternate location ALTERNATE_LOCATION_NAME for
> DATABASE_OBJECT_NAME at "/PATH/TO/PHYSICAL/FILE";'
> 
> We would have a system table holding theses values. Then, all database
> commands which create a file for an object, call open_object(oid,
> object_name) or something to create the file object.  This will first look
> in the new system table to see if there is a mapping for an object of this
> name.  If so, it will create a symlink to "/PATH/TO/PHYSICAL/FILE" for the
> oid before opening the file.
> 
> Anyway, if people are working on tablespaces, I'll defer to them.  This
> small fix is something that I might actually have time to do, but
> tablespaces definitely not.

IMHO, this is all that's *really* necessary.  That, and an ALTER 
that (takes an exclusive lock on the relevant objects and) lets the
DBA move ALTERNATE_LOCATION_NAME from "/PATH/TO/PHYSICAL/FILE" to
"/NEW/PATH/TO/PHYSICAL/FILE".

On question, though.  I've noticed that once a file grows beyond
1GB, PostgreSQL creates a new file and starts appending to a new
file.  Presumably, that same mode of operation would still occur.

> On Wed, 25 Jun 2003 nolan@celery.tssi.com wrote:
> 
> > > Well, correct solution is to implement tablespaces on which objects like
> > > databases, tables and indexes can be put.
> >
> > I've not looked at the SQL standard, but it seems to me like the order
> > should be:
> >
> > Databases
> >    Tablespaces
> >       Schemas
> >          Objects (tables, indexes, functions, etc.)
> >
> > And it really isn't hierarchical.  As I understand them (based on my
> > Oracle background), tablespaces, unlike schemas, do NOT create a layer
> > of data abstraction.   That is to say, while the same table name
> > can exist in multiple schemas, only one instance of a given table name
> > within a given schema can exist, regardless of what tablespace it is in.
> >
> > That makes the tablespace a property of an object.
> >
> > Whether or not two databases can share tablespaces isn't clear to me,
> > though as a DBA I can think of good reasons why they probably shouldn't
> > do so, I'm not sure if that is an absolute.
> >
> > > I have no idea what is the status of that effort right now. You can search the
> > > archives or I hope this kicks a fresh discussion..:-)
> >
> > I'm game, though I'm also not ready to lead such a project, probably not
> > even the discussion on it.
> > --
> > Mike Nolan

-- 
+-----------------------------------------------------------+
| Ron Johnson, Jr.     Home: ron.l.johnson@cox.net          |
| Jefferson, LA  USA   http://members.cox.net/ron.l.johnson |
|                                                           |
| "Oh, great altar of passive entertainment, bestow upon me |
|  thy discordant images at such speed as to render linear  |
|  thought impossible" (Calvin, regarding TV)               |
+-----------------------------------------------------------


25 Jun 2003 14:33:58 -0500
Ron Johnson <ron.l.johnson@cox.net>
Re: Physical Database Configuration
On Wed, Jun 25, 2003 at 10:30:31AM -0500, Andrew Dunstan wrote:
> DB2 looks good. I have horrid, horrid memories of wrestling with the
> Oracle "extent" madness.

I do think that it's worth providing additional access points to
tablespaces, though. That is, it would make sense to me to allow
"CREATE INDEX indexname IN spacename", instead of attaching an
indexspace to a table.

This is especially true with postgresql, since i've seen more than one
proposal for multi-table indices. If we're spacing indices based on
the table, it's unclear where a given multi-table index should go.

It would also allow for other flexibilities, like putting join indices
(on foreign keys) in one tablespace, with indices for aggregation or
sorting in another tablespace.

So, my vote, as a non-code-contributing member, would be for a
DB2-style syntax, without the "INDEX IN" and "LONG IN" extensions, but
with the ability to put indices explicitly into a tablespace.

-johnnnnnn


Wed, 25 Jun 2003 14:56:24 -0500
johnnnnnn <john@phaedrusdeinus.org>
Re: [GENERAL] Physical Database Configuration
> DB2 looks good. I have horrid, horrid memories of wrestling with the Oracle
> "extent" madness.

I think Oracle's extents came from their fixed size data file legacy, in 9i 
the extent limits appear to be completely overridable and sometimes even
ignored, such as the next extent size.  I agree that the 128 extent limit 
was a pain, and the default for each new extent to be larger than the 
previous one created many problems.

Oracle also took physical abstraction one level beyond 'tablespaces'.
I think if each tablespace pointed to a specific directory, that'd be 
sufficient for me.  And since I envision the tablespace as an attribute 
of the table that should take care of the 1GB file rollover issue, as 
the rollover would occur in the same directory as the first file.

Without having delved into the code yet, setting up entries for user 
default tablespaces and system information is probably at least as much 
work as getting a tablespace to point to a specific directory for the 
purposes of opening or creating files for an object.

My personal preference would be to have four tablespaces predefined as part 
of a new database, though initially they could all point to the same place:

SYSTEM
USER
TEMP
INDEXES

What about the concepts of a 'read-only' tablespace, or taking tablespaces
offline?  
--
Mike Nolan

Wed, 25 Jun 2003 15:02:47 -0500 (CDT)
nolan@celery.tssi.com
Re: [GENERAL] Physical Database Configuration
johnnnnnn wrote:

>On Wed, Jun 25, 2003 at 10:30:31AM -0500, Andrew Dunstan wrote:
>  
>
>>DB2 looks good. I have horrid, horrid memories of wrestling with the
>>Oracle "extent" madness.
>>    
>>
>
>I do think that it's worth providing additional access points to
>tablespaces, though. That is, it would make sense to me to allow
>"CREATE INDEX indexname IN spacename", instead of attaching an
>indexspace to a table.
>
>This is especially true with postgresql, since i've seen more than one
>proposal for multi-table indices. If we're spacing indices based on
>the table, it's unclear where a given multi-table index should go.
>
>It would also allow for other flexibilities, like putting join indices
>(on foreign keys) in one tablespace, with indices for aggregation or
>sorting in another tablespace.
>  
>
I wonder why an index spanning multiple tables should be stored in a 
different location than the tables itself. If we're talking about 
derived tables, all data/index must be available at the same time to be 
meaningful, so why not restrict them to the same tablespace? This sounds 
like more flexibility than really useful to me.

The philosophy of pgsql is to let the os and the io system distribute 
the load over disks and other resources, not to do it in the backend. 
That's why we need much less organizational effort than other systems 
that try to implement everything themselves, on raw devices etc.

Regards,
Andreas


Wed, 25 Jun 2003 22:43:06 +0200
Andreas Pflug <Andreas.Pflug@web.de>
Re: [GENERAL] Physical Database Configuration
> That should be 
> 
> Tablespaces
>   databases
>    schemas
>     objects
> 
> with each of them implemented as a directory and data files under it. If we 
> could get a quota check propogated in both direction, that would be pretty 
> good, may be a warning when things start getting close to limit. 

I disagree.  Just as you can have multiple schemas within one database
you can have multiple tablespaces within one database.  

And the tablespace is irrelevant as far as specifying an object is concerned.
A fully qualified object would be: 
    database.schema.object,
not tablespace.database.schema.object or database.tablespace.schema.object.
--
Mike Nolan

Thu, 26 Jun 2003 09:25:19 -0500 (CDT)
nolan@celery.tssi.com
Re: [GENERAL] Physical Database Configuration
nolan@celery.tssi.com writes:
> I disagree.  Just as you can have multiple schemas within one database
> you can have multiple tablespaces within one database.  

> And the tablespace is irrelevant as far as specifying an object is concerned.
> A fully qualified object would be: 
>     database.schema.object,
> not tablespace.database.schema.object or database.tablespace.schema.object.

Right, the tablespace structure is really orthogonal to the
database/schema structure.

I would envision tablespaces as being named by database-cluster-wide
names, just as users and groups are.  Any given table could be placed
in any tablespace (although perhaps we want to invent some permission
mechanism here).

Physically a tablespace is a directory with sub-directories for
databases under it --- so $PGDATA/base plays the role of the default
tablespace for a cluster.  (The reason you need per-database
sub-directories is mostly to support DROP DATABASE, which has to be
able to nuke a database without knowing exactly what's in it.)  But
this structure doesn't have anything to do with the logical structure
of the database cluster.

There are a bunch of interesting locking issues to be solved, but the
storage layout ideas are pretty clear in my mind.

			regards, tom lane

Thu, 26 Jun 2003 10:52:02 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [GENERAL] Physical Database Configuration 
On Thursday 26 June 2003 20:22, Tom Lane wrote:
> nolan@celery.tssi.com writes:
> > I disagree.  Just as you can have multiple schemas within one database
> > you can have multiple tablespaces within one database.
> >
> > And the tablespace is irrelevant as far as specifying an object is
> > concerned. A fully qualified object would be:
> >     database.schema.object,
> > not tablespace.database.schema.object or
> > database.tablespace.schema.object.
>
> Right, the tablespace structure is really orthogonal to the
> database/schema structure.

Well, consider this. Keep in mind that all of them are directories..

Tablespace1
 db1
  schema1
   table1
   table2
 db2
  schema1
   table1
   table2

Tablespace2
 db1
  schema1
   index1


Now, unlike Oracle, I don't remember anybody suggesting to get away with 
$PGDATA entirely because we have tablespaces. $PGDATA is going to be 
available and will continue to host at least one directory for each database. 
So postgresql now where to find all tablespaces that hosts that particular 
database related object.

Certainly does not sound impossible or illogical to me.

And I agree. The paradigm, 'A tablespace can host multiple database and a 
database can span across multiple tablespaces' is absolutely arthgonal. 
Convoluted is what I would term it. But hey, it's the most flexible 
one!!..:-)

> Physically a tablespace is a directory with sub-directories for
> databases under it --- so $PGDATA/base plays the role of the default
> tablespace for a cluster.  (The reason you need per-database
> sub-directories is mostly to support DROP DATABASE, which has to be
> able to nuke a database without knowing exactly what's in it.)  But
> this structure doesn't have anything to do with the logical structure
> of the database cluster.

Well, with above proposal, drop database should be as simple. It's just that 
it would be more than one `rm -rf`rather than just one.

HTH
 
 Shridhar


Thu, 26 Jun 2003 21:18:35 +0530
Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
Re: [GENERAL] Physical Database Configuration
Shridhar Daithankar <shridhar_daithankar@persistent.co.in> writes:
> Well, consider this. Keep in mind that all of them are directories..

I can see no reason that we'd want a level of directory associated with
schemas...

> Well, with above proposal, drop database should be as simple. It's just that 
> it would be more than one `rm -rf`rather than just one.

Right, there would be potentially one per tablespace.  The key point
here is that the tablespace definitions are known cluster-wide, so a
"DROP DATABASE x" command running in database y would still be able
to figure out which subdirectories it needs to zap.

			regards, tom lane

Thu, 26 Jun 2003 11:59:47 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [GENERAL] Physical Database Configuration 
On Thursday 26 June 2003 21:29, Tom Lane wrote:
> Shridhar Daithankar <shridhar_daithankar@persistent.co.in> writes:
> > Well, consider this. Keep in mind that all of them are directories..
>
> I can see no reason that we'd want a level of directory associated with
> schemas...

Moving a multi-hundreds-of-GB table  across schemas would be sooo easy..:-)

I don't know how difficult/time consuming that is right now. Shouldn't be 
actually if PG updates the schema contents in it's catalog but anyway..

I just put it for clarification. If PG can do everything directory has to 
offer, well, we don't need directory for schemas. 

 Shridhar


Thu, 26 Jun 2003 21:37:13 +0530
Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
Re: [GENERAL] Physical Database Configuration
Shridhar Daithankar <shridhar_daithankar@persistent.co.in> writes:
> On Thursday 26 June 2003 21:29, Tom Lane wrote:
>> I can see no reason that we'd want a level of directory associated with
>> schemas...

> Moving a multi-hundreds-of-GB table  across schemas would be sooo easy..:-)

No, it would be harder.

			regards, tom lane

Thu, 26 Jun 2003 12:22:27 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [GENERAL] Physical Database Configuration 
> > Well, with above proposal, drop database should be as simple. It's just that 
> > it would be more than one `rm -rf`rather than just one.
> 
> Right, there would be potentially one per tablespace.  The key point
> here is that the tablespace definitions are known cluster-wide, so a
> "DROP DATABASE x" command running in database y would still be able
> to figure out which subdirectories it needs to zap.

It sounds like you envision tablespaces (or at least the default tablespace)
as being above databases in some contexts.  Reducing other tablespaces
to mere subdirectories under the 'base' directory for a database sounds 
like a practical implementation measure.  

I presume that the 'global' directory is for stuff that is not specific 
to any one database within a database cluster.   If so, there is an 
inconsistency in the current directory structure in that SOME global 
information is in the main /usr/local/pgsql/data directory (or equivalent) 
while other global information is in the global subdirectory.

Being able to zap a database with one or more 'rm -rf' commands assumes 
that there will be files from just ONE database permitted in any given 
tablespace, and ONLY files from that database.  The former is probably 
a good thing to require and enforce, the latter is unenforcable but 
probably still advisable.

Is this doable within the time frame for the 7.4 feature freeze?  
--
Mike Nolan

Thu, 26 Jun 2003 11:26:50 -0500 (CDT)
nolan@celery.tssi.com
Re: [GENERAL] Physical Database Configuration
On Thursday 26 June 2003 21:56, nolan@celery.tssi.com wrote:
> Is this doable within the time frame for the 7.4 feature freeze?

Good question. 

If it gets in 7.4, that would be more than a killer feature to put against 7.4 
release, with due respect to all other enhancements in progress..

 Shridhar


Thu, 26 Jun 2003 22:00:52 +0530
Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
Re: [GENERAL] Physical Database Configuration
nolan@celery.tssi.com writes:
> Being able to zap a database with one or more 'rm -rf' commands assumes 
> that there will be files from just ONE database permitted in any given 
> tablespace, and ONLY files from that database.

I said no such thing.  Look at the structure again:

$PGDATA/base/dboid/...stuff...

sometablespace/dboid/...stuff...

othertablespace/dboid/...stuff...

DROPDB needs to nuke <somepath>/dboid/ for each tablespace's associated
<somepath>.  The other design simplifies DROPDB at the cost of increased
complexity for every other tablespace management operation, since you'd
need to cope with a symlink in each database for each tablespace.

Also, this scheme is at least theoretically amenable to a symlink-free
implementation, though I personally don't give a darn whether
tablespaces are supported on Windows and thus wouldn't expend the extra
effort needed to keep track of full paths.  I'd want
$PGDATA/tablespaces/tboid to be a symlink to the root of the tablespace
with a given OID, and then the actual pathname used to access a table in
tablespace tboid, database dboid, table filenode rfoid would look like
	$PGDATA/tablespaces/tboid/dboid/rfoid
But a Windoze version could in theory keep track of tablespace locations
directly, and replace the first part of this path with the actual
tablespace location.  If we put tablespaces under directories then the
facility has zero functionality without symlinks, because you couldn't
actually do anything to segregate stuff within a database across
different devices.

BTW, we'd probably remove $PGDATA/base in favor of $PGDATA/tablespaces/N
for some fixed-in-advance N that is the system tablespace, and we'd
require all system catalogs to live in this tablespace --- certainly at
least pg_class and its indexes.  Otherwise you have circularity problems
in finding the catalogs ...

			regards, tom lane

Thu, 26 Jun 2003 12:50:36 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [GENERAL] Physical Database Configuration 
Shridhar Daithankar <shridhar_daithankar@persistent.co.in> writes:
> On Thursday 26 June 2003 21:56, nolan@celery.tssi.com wrote:
>> Is this doable within the time frame for the 7.4 feature freeze?

> Good question. 

ROTFL... the answer is no.  Feature freeze is Tuesday, people.  In
practice, the time to start coding new stuff is already long past.
Especially major new stuff.

If you start now you might have something done for 7.5.

			regards, tom lane

Thu, 26 Jun 2003 13:03:48 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [GENERAL] Physical Database Configuration 
> Shridhar Daithankar <shridhar_daithankar@persistent.co.in> writes:
> > On Thursday 26 June 2003 21:56, nolan@celery.tssi.com wrote:
> >> Is this doable within the time frame for the 7.4 feature freeze?
> 
> > Good question. 
> 
> ROTFL... the answer is no.  Feature freeze is Tuesday, people.  In
> practice, the time to start coding new stuff is already long past.
> Especially major new stuff.
> 
> If you start now you might have something done for 7.5.

Forgive us, Tom, we are still learning how the cycle works.  (Or at least
I am.)  

I am also probably grossly underestimating the pervasiveness of 
implementing tablespaces.
--
Mike Nolan

Thu, 26 Jun 2003 13:08:08 -0500 (CDT)
nolan@celery.tssi.com
Re: [GENERAL] Physical Database Configuration
> > Tablespaces
> >   databases
> >    schemas
> >     objects
> >
> > with each of them implemented as a directory and data files under it. If
we
> > could get a quota check propogated in both direction, that would be
pretty
> > good, may be a warning when things start getting close to limit.

Database do not exist inside tablespaces, as they are not relations.  Only
tables, views, sequences and indexes will live in tablespaces.  Even then, I
probably won't bother allowing it for sequences and views since they don't
occupy much space.

A database can specify a DEFAULT tablespace, as can a schema, but they do
not live in tablespaces themselves.

Chris


Fri, 27 Jun 2003 11:06:55 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: [GENERAL] Physical Database Configuration
> Good question.
>
> If it gets in 7.4, that would be more than a killer feature to put against
7.4
> release, with due respect to all other enhancements in progress..

It's not going to happen.

Chris


Fri, 27 Jun 2003 11:08:15 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: [GENERAL] Physical Database Configuration
> ROTFL... the answer is no.  Feature freeze is Tuesday, people.  In
> practice, the time to start coding new stuff is already long past.
> Especially major new stuff.
>
> If you start now you might have something done for 7.5.

Can everyone who is interested in actually coding a tablespaces
implementation please email me, and I will create a list and we'll get
cracking.  Maybe we'll have to run our own cvs or maybe the Postgres guys
will be nice and do a branch for us :)

So far, Shridhar and Gavin seem interested??

Chris


Fri, 27 Jun 2003 11:10:03 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Tablespaces (was Re: [GENERAL] Physical Database Configuration )
I thought Tablespaces were already implemented. Are they not?


On Thu, 2003-06-26 at 22:10, Christopher Kings-Lynne wrote:
> > ROTFL... the answer is no.  Feature freeze is Tuesday, people.  In
> > practice, the time to start coding new stuff is already long past.
> > Especially major new stuff.
> >
> > If you start now you might have something done for 7.5.
> 
> Can everyone who is interested in actually coding a tablespaces
> implementation please email me, and I will create a list and we'll get
> cracking.  Maybe we'll have to run our own cvs or maybe the Postgres guys
> will be nice and do a branch for us :)
> 
> So far, Shridhar and Gavin seem interested??
> 
> Chris
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
-- 
Austin Gonyou <austin@coremetrics.com>
Coremetrics, Inc.

27 Jun 2003 17:07:53 -0500
Austin Gonyou <austin@coremetrics.com>
Re: Tablespaces (was Re: [GENERAL] Physical Database
> I thought Tablespaces were already implemented. Are they not?

Apparently not.  

A group has been formed to work on it, though.
--
Mike Nolan

Fri, 27 Jun 2003 17:13:45 -0500 (CDT)
nolan@celery.tssi.com
Re: Tablespaces (was Re: [GENERAL] Physical Database
No, they're not.  There are some folks who have hacked on them in the 
past, but nothing's been committed.

On 27 Jun 2003, Austin Gonyou wrote:

> I thought Tablespaces were already implemented. Are they not?
> 
> 
> On Thu, 2003-06-26 at 22:10, Christopher Kings-Lynne wrote:
> > > ROTFL... the answer is no.  Feature freeze is Tuesday, people.  In
> > > practice, the time to start coding new stuff is already long past.
> > > Especially major new stuff.
> > >
> > > If you start now you might have something done for 7.5.
> > 
> > Can everyone who is interested in actually coding a tablespaces
> > implementation please email me, and I will create a list and we'll get
> > cracking.  Maybe we'll have to run our own cvs or maybe the Postgres guys
> > will be nice and do a branch for us :)
> > 
> > So far, Shridhar and Gavin seem interested??
> > 
> > Chris
> > 
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 


Fri, 27 Jun 2003 16:15:36 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: Tablespaces (was Re: [GENERAL] Physical Database
No, they're not.

Chris

On 27 Jun 2003, Austin Gonyou wrote:

> I thought Tablespaces were already implemented. Are they not?
>
>
> On Thu, 2003-06-26 at 22:10, Christopher Kings-Lynne wrote:
> > > ROTFL... the answer is no.  Feature freeze is Tuesday, people.  In
> > > practice, the time to start coding new stuff is already long past.
> > > Especially major new stuff.
> > >
> > > If you start now you might have something done for 7.5.
> >
> > Can everyone who is interested in actually coding a tablespaces
> > implementation please email me, and I will create a list and we'll get
> > cracking.  Maybe we'll have to run our own cvs or maybe the Postgres guys
> > will be nice and do a branch for us :)
> >
> > So far, Shridhar and Gavin seem interested??
> >
> > Chris
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> --
> Austin Gonyou <austin@coremetrics.com>
> Coremetrics, Inc.
>


Sat, 28 Jun 2003 12:59:20 +0800 (WST)
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Tablespaces (was Re: [GENERAL] Physical Database
On Wednesday 25 June 2003 21:21, Jonathan Bartlett wrote:
> My solution did not involve tablespaces, but was more of a quick solution
> to make it easier for admins to do _some_ sort of physical configuration.
>
> The idea is that the developer could do something like
>
> 'create alternate location ALTERNATE_LOCATION_NAME for
> DATABASE_OBJECT_NAME at "/PATH/TO/PHYSICAL/FILE";'
>
> We would have a system table holding theses values. Then, all database
> commands which create a file for an object, call open_object(oid,
> object_name) or something to create the file object.  This will first look
> in the new system table to see if there is a mapping for an object of this
> name.  If so, it will create a symlink to "/PATH/TO/PHYSICAL/FILE" for the
> oid before opening the file.

What you are describing is a subset of tablespace functionality we are 
discussing offline. There are few minor differences though.

1. There is no physical file but location would be a directory.
2. The location could also be assigned at the time of object creation.

Otherwise it is exactly what is under consideration..:-)

 Shridhar


Sun, 29 Jun 2003 14:26:45 +0530
Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
Re: [HACKERS] Physical Database Configuration
Tom Lane wrote:
> nolan@celery.tssi.com writes:
> > I disagree.  Just as you can have multiple schemas within one database
> > you can have multiple tablespaces within one database.  
> 
> > And the tablespace is irrelevant as far as specifying an object is concerned.
> > A fully qualified object would be: 
> >     database.schema.object,
> > not tablespace.database.schema.object or database.tablespace.schema.object.
> 
> Right, the tablespace structure is really orthogonal to the
> database/schema structure.
> 
> I would envision tablespaces as being named by database-cluster-wide
> names, just as users and groups are.  Any given table could be placed
> in any tablespace (although perhaps we want to invent some permission
> mechanism here).
> 
> Physically a tablespace is a directory with sub-directories for
> databases under it --- so $PGDATA/base plays the role of the default
> tablespace for a cluster.  (The reason you need per-database
> sub-directories is mostly to support DROP DATABASE, which has to be
> able to nuke a database without knowing exactly what's in it.)  But
> this structure doesn't have anything to do with the logical structure
> of the database cluster.

Another good reason for per-database directories under the tablespace is
to prevent directories from containing too many files.

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

Fri, 18 Jul 2003 22:54:15 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [GENERAL] Physical Database Configuration
> Another good reason for per-database directories under the tablespace is
> to prevent directories from containing too many files.

Actually, I would take that as an reason not to have database
directories.

If the number of files becomes a concern, we would need some kind of a
hashing algorithm to disperse them appropriately.  The database
directory would just get in the way without really adding anything. Is
1000 per files as a directory limit still the rule of thumb for a limit?
A single TB sized db would start to run into those types of limits.
18 Jul 2003 23:06:36 -0400
Rod Taylor <rbt@rbt.ca>
Re: [GENERAL] Physical Database Configuration
On Fri, 18 Jul 2003, Bruce Momjian wrote:

> Tom Lane wrote:
> > nolan@celery.tssi.com writes:
> > > I disagree.  Just as you can have multiple schemas within one database
> > > you can have multiple tablespaces within one database.  
> > 
> > > And the tablespace is irrelevant as far as specifying an object is concerned.
> > > A fully qualified object would be: 
> > >     database.schema.object,
> > > not tablespace.database.schema.object or database.tablespace.schema.object.
> > 
> > Right, the tablespace structure is really orthogonal to the
> > database/schema structure.
> > 
> > I would envision tablespaces as being named by database-cluster-wide
> > names, just as users and groups are.  Any given table could be placed
> > in any tablespace (although perhaps we want to invent some permission
> > mechanism here).
> > 
> > Physically a tablespace is a directory with sub-directories for
> > databases under it --- so $PGDATA/base plays the role of the default
> > tablespace for a cluster.  (The reason you need per-database
> > sub-directories is mostly to support DROP DATABASE, which has to be
> > able to nuke a database without knowing exactly what's in it.)  But
> > this structure doesn't have anything to do with the logical structure
> > of the database cluster.
> 
> Another good reason for per-database directories under the tablespace is
> to prevent directories from containing too many files.

I have a 3/4s finish implementation of table spaces. Per database
directories under the table space are required because CREATE DATABASE
just copies the data directory of the template database. If the databases
were in the same table space, they would need to share system catalogs
and it would also not be possible to create a database in a new table
space because the pg_class and pg_namespace would have references to the
table spaces used by the template database, not the new table space.

I will elaborate further when I have a near complete patch.

Thanks,

Gavin


Sat, 19 Jul 2003 13:19:47 +1000 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: [GENERAL] Physical Database Configuration