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.

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

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

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

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

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

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

andrew

AgentM wrote:

"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

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:

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

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

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.

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

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/

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

I thought Tablespaces were already implemented. Are they not?

Apparently not.

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

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:

No, they're not.

Chris

On 27 Jun 2003, Austin Gonyou wrote:

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

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

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.

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