Hi all,  

The following is a description of a patch I am proposing for 7.3.  
Please read and comment.

Thanks
Jim


This proposal covers the ability to allow a DBA (and general users) to 
specify where a database and it's individual objects will reside.  I 
propose to add a default data location, index and temporary locations 
to the pg_shadow table to allow a DBA to specify locations for each 
user when they create databases, tables and indexes or need temporary 
disk storage (either for temporary tables or sort files).  The "CREATE 
DATABASE" command will be changed to also take an INDEX location and 
temporary location. All 3 locations will default to the values from 
pg_shadow for the user that is creating the database.  Both the "CREATE 
TABLE" and "CREATE INDEX" commands will be changed to add "WITH 
LOCATION" optional argument (location will default to values from 
PG_DATABASE which were set by the "CREATE DATABASE" command).

The following system tables will be changed as follows
PG_SHADOW add dat_location, idx_location, tmp_location (all default to 
PG_DATA)
PG_DATABASE add dat_location, idx_location, tmp_location (all default 
to same from PG_SHADOW)
PG_CLASS add rellocation (default to dat_location for tables, 
idx_location for indexes from PG_DATABASE)


Add a GLOBAL table pg_locations to track valid locations

Add the following commands to manage locations
CREATE LOCATION locname PATH 'file system directory';
DROP LOCATION locname;   (this will have to look into each db to make 
sure that any objects are not using it.  Don't know how this will be 
done yet!)

I propose to change the names of the on disk directories from 999999 to 
99999_DATA, 99999_INDEX and 99999_TEMP (where 99999 is the OID from 
PG_DATABASE).  A SYMLINK from 99999_INDEX and 99999_TEMP will be made 
back to 99999_DATA will be made so the WAL functions will continue to 
work.


Again from my earlier attempt at this patch, I believe this capability 
will not only improve performance (see my earlier emails.  Where 
depending on the type of disks the improvement was between 0% and 100% 
performance gain running pg_bench) but also give DBA's the flexibility 
to spread the data files over multiple disks without having to "hack" 
the system using symbolic links. 



Mon, 5 Nov 2001 09:53:12 -0500
"Jim Buttafuoco" <jim@buttafuoco.net>
Storage Location Patch Proposal for V7.3
"Jim Buttafuoco" <jim@buttafuoco.net> writes:
> I propose to add a default data location, index and temporary locations 
> to the pg_shadow table to allow a DBA to specify locations for each 
> user when they create databases, tables and indexes or need temporary 
> disk storage (either for temporary tables or sort files).

Have you read any of the previous discussions about tablespaces?
This seems to be tablespaces with an off-the-cuff syntax.  I'd
suggest taking a hard look at Oracle's tablespace facility and
seeing how closely we want to duplicate that.

> PG_SHADOW add dat_location, idx_location, tmp_location (all default to 
> PG_DATA)

What does location have to do with users?

> I propose to change the names of the on disk directories from 999999 to 
> 99999_DATA, 99999_INDEX and 99999_TEMP (where 99999 is the OID from 
> PG_DATABASE).

No, that doesn't scale to arbitrary locations; furthermore it requires
an unseemly amount of knowledge in low-level file access code about
exactly what kind of object each table is.  The symlinks should just
be named after the OIDs of the locations' rows in pg_location.

The direction I've been envisioning for this is that each table has
a logical identification <pg_database OID>, <pg_class OID> as well
as a physical identification <pg_location OID>, <relfilenode OID>.
The path to access the table can be constructed entirely from the
physical identification: $PGDATA/base/<pg_location OID>/<relfilenode OID>.

One problem to be addressed if multiple databases can share a single
physical location is how to prevent relfilenode collisions.  Perhaps
we could avoid the issue by adding another layer of subdirectories:
$PGDATA/base/<pg_location OID>/<pg_database OID>/<relfilenode OID>.
That is, each database would have a subdirectory within each location
that it's ever used.  (This would make DROP DATABASE a lot easier,
among other things.)

			regards, tom lane

Mon, 05 Nov 2001 11:17:15 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Storage Location Patch Proposal for V7.3 
Tom Lane wrote:
> 
> "Jim Buttafuoco" <jim@buttafuoco.net> writes:
> > I propose to add a default data location, index and temporary locations
> > to the pg_shadow table to allow a DBA to specify locations for each
> > user when they create databases, tables and indexes or need temporary
> > disk storage (either for temporary tables or sort files).
> 
> Have you read any of the previous discussions about tablespaces?
> This seems to be tablespaces with an off-the-cuff syntax.  I'd
> suggest taking a hard look at Oracle's tablespace facility and
> seeing how closely we want to duplicate that.

Sorry I missed the conversation about tablespaces. One of the reasons I think
Postgres is so usable is because it does not require the use of tablespace
files. If by tablespace, you mean to declare a directory on a device as a
tablespace, then cool. If you want to create tablespace "files" ala Oracle, you
are heading toward an administration nightmare. Don't get me wrong, the ability
to use a file as a tablespace would be kind of cool, i.e. you can probably use
raw devices, but please to not abandon the way postgres currently works.

On our Oracle server, we have run out of space on our tablespace files and not
known it was coming. I am the system architect, not the DBA, so I don't have
(nor want) direct control over the oracle database operation. Our newbe DBA did
not make the table correctly, so they did not grow. Alas he was laid off, thus
we were left trying to figure out what was happening.

Postgres is easier to configure and get right. IMHO that is one of its very
important strengths. It is almost trivial to get a working SQL system up and
running which performs well.

Tue, 06 Nov 2001 08:55:14 -0500
mlw <markw@mohawksoft.com>
Re: Storage Location Patch Proposal for V7.3
mlw <markw@mohawksoft.com> writes:
> Tom Lane wrote:
>> This seems to be tablespaces with an off-the-cuff syntax.  I'd
>> suggest taking a hard look at Oracle's tablespace facility and
>> seeing how closely we want to duplicate that.

> Sorry I missed the conversation about tablespaces. One of the reasons I think
> Postgres is so usable is because it does not require the use of tablespace
> files. If by tablespace, you mean to declare a directory on a device as a
> tablespace, then cool. If you want to create tablespace "files" ala Oracle, you
> are heading toward an administration nightmare.

No, that's not one of the parts of Oracle's facility that I want to
duplicate.

I think our idea of a tablespace/location/whatchacallit should just be
a directory somewhere that table files can be created in.  What seems
worthwhile to steal from Oracle is the syntax that assigns particular
tables to particular tablespaces.  If we're compatible on syntax, that
should ease porting of existing applications --- and as far as I can see
at the moment, there's no reason *not* to be compatible at that level.
I don't want to borrow Oracle's ideas about space management semantics,
however.

			regards, tom lane

Tue, 06 Nov 2001 23:49:56 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Storage Location Patch Proposal for V7.3 
I just wanted to affirm that Tom's description sounds like av very good
way to go.

You get the best of two worlds with the possibility to tune servers and
yet still
very easy to manage. i.e. If you don't need it, don't mess with it and
everything
will work just fine.
I don't either see any reason not to use the Oracle syntax since it is
so widely used
and it works very well for those of us that also work on Oracle (but in
postgresql
without the extent and storage clauses).

Regards
Stefan

Tom Lane wrote:

> mlw <markw@mohawksoft.com> writes:
> > Tom Lane wrote:
> >> This seems to be tablespaces with an off-the-cuff syntax.  I'd
> >> suggest taking a hard look at Oracle's tablespace facility and
> >> seeing how closely we want to duplicate that.
>
> > Sorry I missed the conversation about tablespaces. One of the reasons I think
> > Postgres is so usable is because it does not require the use of tablespace
> > files. If by tablespace, you mean to declare a directory on a device as a
> > tablespace, then cool. If you want to create tablespace "files" ala Oracle, you
> > are heading toward an administration nightmare.
>
> No, that's not one of the parts of Oracle's facility that I want to
> duplicate.
>
> I think our idea of a tablespace/location/whatchacallit should just be
> a directory somewhere that table files can be created in.  What seems
> worthwhile to steal from Oracle is the syntax that assigns particular
> tables to particular tablespaces.  If we're compatible on syntax, that
> should ease porting of existing applications --- and as far as I can see
> at the moment, there's no reason *not* to be compatible at that level.
> I don't want to borrow Oracle's ideas about space management semantics,
> however.
>
>                         regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

Thu, 08 Nov 2001 13:52:40 +0100
Stefan Rindeskar <sr@globecom.net>
Re: Storage Location Patch Proposal for V7.3
Stefan Rindeskar wrote:
> 
> I just wanted to affirm that Tom's description sounds like av very good
> way to go.
> 
> You get the best of two worlds with the possibility to tune servers and
> yet still
> very easy to manage. i.e. If you don't need it, don't mess with it and
> everything
> will work just fine.
> I don't either see any reason not to use the Oracle syntax since it is
> so widely used
> and it works very well for those of us that also work on Oracle (but in
> postgresql
> without the extent and storage clauses).
> 

I absolutely agree with the concept of defining a location for data from within
the database. No argument.

The only two issues I can see are:

(1) Do not require the use of files as table spaces ala Oracle. That is an
admin nightmare. (Again, it would be cool, however, to be able to use table
space files so that PostgreSQL could have raw access as long as it is not a
requirement.) I don't think Tom is thinking about table space files, so I'm not
worried.

(2) I have a concern about expected behavior vs existing syntax. If PostgreSQL
uses "create tablespace" in such a way that an Oracle DBA will expect it to
work as Oracle does, it may cause a bit of confusion. We all know that
"confusion" between an open source solution and a "defacto" solution is used as
club.

Thu, 08 Nov 2001 08:52:22 -0500
mlw <markw@mohawksoft.com>
Re: Storage Location Patch Proposal for V7.3

Jim, I see now that you submitted a new version.  Folks, do we have a
direction for this patch.  Discussion of the patch is at:

	http://candle.pha.pa.us/cgi-bin/pgpatches2

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

Jim Buttafuoco wrote:
> Hi all,  
> 
> The following is a description of a patch I am proposing for 7.3.  
> Please read and comment.
> 
> Thanks
> Jim
> 
> 
> This proposal covers the ability to allow a DBA (and general users) to 
> specify where a database and it's individual objects will reside.  I 
> propose to add a default data location, index and temporary locations 
> to the pg_shadow table to allow a DBA to specify locations for each 
> user when they create databases, tables and indexes or need temporary 
> disk storage (either for temporary tables or sort files).  The "CREATE 
> DATABASE" command will be changed to also take an INDEX location and 
> temporary location. All 3 locations will default to the values from 
> pg_shadow for the user that is creating the database.  Both the "CREATE 
> TABLE" and "CREATE INDEX" commands will be changed to add "WITH 
> LOCATION" optional argument (location will default to values from 
> PG_DATABASE which were set by the "CREATE DATABASE" command).
> 
> The following system tables will be changed as follows
> PG_SHADOW add dat_location, idx_location, tmp_location (all default to 
> PG_DATA)
> PG_DATABASE add dat_location, idx_location, tmp_location (all default 
> to same from PG_SHADOW)
> PG_CLASS add rellocation (default to dat_location for tables, 
> idx_location for indexes from PG_DATABASE)
> 
> 
> Add a GLOBAL table pg_locations to track valid locations
> 
> Add the following commands to manage locations
> CREATE LOCATION locname PATH 'file system directory';
> DROP LOCATION locname;   (this will have to look into each db to make 
> sure that any objects are not using it.  Don't know how this will be 
> done yet!)
> 
> I propose to change the names of the on disk directories from 999999 to 
> 99999_DATA, 99999_INDEX and 99999_TEMP (where 99999 is the OID from 
> PG_DATABASE).  A SYMLINK from 99999_INDEX and 99999_TEMP will be made 
> back to 99999_DATA will be made so the WAL functions will continue to 
> work.
> 
> 
> Again from my earlier attempt at this patch, I believe this capability 
> will not only improve performance (see my earlier emails.  Where 
> depending on the type of disks the improvement was between 0% and 100% 
> performance gain running pg_bench) but also give DBA's the flexibility 
> to spread the data files over multiple disks without having to "hack" 
> the system using symbolic links. 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 22 Feb 2002 15:01:19 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Storage Location Patch Proposal for V7.3
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Jim, I see now that you submitted a new version.  Folks, do we have a
> direction for this patch.

I didn't like it at the time, and still don't.  We are not that far away
from having proper tablespaces, and I think that kluges that provide
part of the functionality will just get in the way when it comes time
to do it right.

			regards, tom lane

Fri, 22 Feb 2002 19:20:57 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Storage Location Patch Proposal for V7.3 
On Fri, 22 Feb 2002, Tom Lane wrote:

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Jim, I see now that you submitted a new version.  Folks, do we have a
> > direction for this patch.
>
> I didn't like it at the time, and still don't.  We are not that far away
> from having proper tablespaces, and I think that kluges that provide
> part of the functionality will just get in the way when it comes time
> to do it right.

What kind of time frame is "not that far away"?  For v7.3?

If not, and someone can clarify what I'm understanding this patch will do,
its essentially going to setup a directory structure of:

data/base/<dboid>/<tbloid>.idx/indx

?

If we aren't going to have tablespaces for v7.3, there we are talking 6->8
months before we do, and the above sounds like a reasonable interim
solution for this ...


Fri, 22 Feb 2002 21:52:21 -0400 (AST)
"Marc G. Fournier" <scrappy@hub.org>
Re: Storage Location Patch Proposal for V7.3 
"Marc G. Fournier" <scrappy@hub.org> writes:
> On Fri, 22 Feb 2002, Tom Lane wrote:
>> I didn't like it at the time, and still don't.  We are not that far away
>> from having proper tablespaces, and I think that kluges that provide
>> part of the functionality will just get in the way when it comes time
>> to do it right.

> What kind of time frame is "not that far away"?  For v7.3?

My guess is that any of the inner circle of hackers could make this
happen with about a week's work.  Whether someone will find time before
7.3 is unknown (particularly seeing that we haven't set a target date
for 7.3).  Personally, schemas are a higher priority for me ...

			regards, tom lane

Sat, 23 Feb 2002 11:53:24 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Storage Location Patch Proposal for V7.3 
All,

I still believe that postgresql needs this feature.   I have many postgresql
systems that have over 500GB of data+indexes.  Using symbolic links is a BIG
pain in the A??.  Every time I run vacuum I have to go and fix the links
again.  Also I have many disks that are running out of space.  This patch
would allow me the ability to move my tables and indexes around.   I
personally don't see the difference between my patch and what people are
calling "Tablespaces" .  Oracle's definition is "A group of files that contain
database objects" ,  under my patch tablespaces and locations are the same
thing except postgresql uses file system directories to contain the group of
objects.  

To recap my patch (location = tablespace here)

Allow the DBA to create locations with a CREATE LOCATION command or CREATE
TABLESPACE command if you like tablespace instead of LOCATION.

Then for DATABASES (and schemas when available)  CREATE DATABASE WITH
DATA_LOCATION = XXX and INDEX_LOCATION  = YYY where XXX and YYY are the
DEFAULT values for OBJECT creation if not LOCATION is given.

CREATE TABLE and CREATE INDEX will create tables and indexes in the defaults
from the CREATE DATABASE/SCHEMA commands above.

CREATE TABLE WITH LOCATION=AAA and CREATE INDEX WITH LOCATION BBB would create
the table/index with the alternate location (only if the location was created
with a CREATE LOCATION command)


The create table command would also have to be change to support primary key/
unique index syntax.

create table SAMPLE
(
	c1	text primary key location CCC,
	c2          text unique  location DDD
);


I hope this explains my patch better.   As  I said before and I believe this
to be true,  This patch will enable the DBA to place tables/indexes on any
disk either for performance and/or space reasons.  Also I believe this is
another check off item for people looking at  postgresql when comparing with
Oracle/Sybase/DB2 ... 

Thanks for your time
Jim




> Jim, I see now that you submitted a new version.  Folks, do we have a
> direction for this patch.  Discussion of the patch is at:
> 
> 	http://candle.pha.pa.us/cgi-bin/pgpatches2
> 
> ---------------------------------------------------------------------------
> 
> Jim Buttafuoco wrote:
> > Hi all,  
> > 
> > The following is a description of a patch I am proposing for 7.3.  
> > Please read and comment.
> > 
> > Thanks
> > Jim
> > 
> > 
> > This proposal covers the ability to allow a DBA (and general users) to 
> > specify where a database and it's individual objects will reside.  I 
> > propose to add a default data location, index and temporary locations 
> > to the pg_shadow table to allow a DBA to specify locations for each 
> > user when they create databases, tables and indexes or need temporary 
> > disk storage (either for temporary tables or sort files).  The "CREATE 
> > DATABASE" command will be changed to also take an INDEX location and 
> > temporary location. All 3 locations will default to the values from 
> > pg_shadow for the user that is creating the database.  Both the "CREATE 
> > TABLE" and "CREATE INDEX" commands will be changed to add "WITH 
> > LOCATION" optional argument (location will default to values from 
> > PG_DATABASE which were set by the "CREATE DATABASE" command).
> > 
> > The following system tables will be changed as follows
> > PG_SHADOW add dat_location, idx_location, tmp_location (all default to 
> > PG_DATA)
> > PG_DATABASE add dat_location, idx_location, tmp_location (all default 
> > to same from PG_SHADOW)
> > PG_CLASS add rellocation (default to dat_location for tables, 
> > idx_location for indexes from PG_DATABASE)
> > 
> > 
> > Add a GLOBAL table pg_locations to track valid locations
> > 
> > Add the following commands to manage locations
> > CREATE LOCATION locname PATH 'file system directory';
> > DROP LOCATION locname;   (this will have to look into each db to make 
> > sure that any objects are not using it.  Don't know how this will be 
> > done yet!)
> > 
> > I propose to change the names of the on disk directories from 999999 to 
> > 99999_DATA, 99999_INDEX and 99999_TEMP (where 99999 is the OID from 
> > PG_DATABASE).  A SYMLINK from 99999_INDEX and 99999_TEMP will be made 
> > back to 99999_DATA will be made so the WAL functions will continue to 
> > work.
> > 
> > 
> > Again from my earlier attempt at this patch, I believe this capability 
> > will not only improve performance (see my earlier emails.  Where 
> > depending on the type of disks the improvement was between 0% and 100% 
> > performance gain running pg_bench) but also give DBA's the flexibility 
> > to spread the data files over multiple disks without having to "hack" 
> > the system using symbolic links. 
> > 
> > 
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > 
> 
> -- 
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026




Sun, 3 Mar 2002 10:34:08 -0500
"Jim Buttafuoco" <jim@buttafuoco.net>
Re: Storage Location Patch Proposal for V7.3

I think Jim has some very good points here.  What does his
implementation lack?  Seems pretty valuable to me.

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

Jim Buttafuoco wrote:
> All,
> 
> I still believe that postgresql needs this feature.   I have many postgresql
> systems that have over 500GB of data+indexes.  Using symbolic links is a BIG
> pain in the A??.  Every time I run vacuum I have to go and fix the links
> again.  Also I have many disks that are running out of space.  This patch
> would allow me the ability to move my tables and indexes around.   I
> personally don't see the difference between my patch and what people are
> calling "Tablespaces" .  Oracle's definition is "A group of files that contain
> database objects" ,  under my patch tablespaces and locations are the same
> thing except postgresql uses file system directories to contain the group of
> objects.  
> 
> To recap my patch (location = tablespace here)
> 
> Allow the DBA to create locations with a CREATE LOCATION command or CREATE
> TABLESPACE command if you like tablespace instead of LOCATION.
> 
> Then for DATABASES (and schemas when available)  CREATE DATABASE WITH
> DATA_LOCATION = XXX and INDEX_LOCATION  = YYY where XXX and YYY are the
> DEFAULT values for OBJECT creation if not LOCATION is given.
> 
> CREATE TABLE and CREATE INDEX will create tables and indexes in the defaults
> from the CREATE DATABASE/SCHEMA commands above.
> 
> CREATE TABLE WITH LOCATION=AAA and CREATE INDEX WITH LOCATION BBB would create
> the table/index with the alternate location (only if the location was created
> with a CREATE LOCATION command)
> 
> 
> The create table command would also have to be change to support primary key/
> unique index syntax.
> 
> create table SAMPLE
> (
> 	c1	text primary key location CCC,
> 	c2          text unique  location DDD
> );
> 
> 
> I hope this explains my patch better.   As  I said before and I believe this
> to be true,  This patch will enable the DBA to place tables/indexes on any
> disk either for performance and/or space reasons.  Also I believe this is
> another check off item for people looking at  postgresql when comparing with
> Oracle/Sybase/DB2 ... 
> 
> Thanks for your time
> Jim
> 
> 
> 
> 
> > Jim, I see now that you submitted a new version.  Folks, do we have a
> > direction for this patch.  Discussion of the patch is at:
> > 
> > 	http://candle.pha.pa.us/cgi-bin/pgpatches2
> > 
> > ---------------------------------------------------------------------------
> > 
> > Jim Buttafuoco wrote:
> > > Hi all,  
> > > 
> > > The following is a description of a patch I am proposing for 7.3.  
> > > Please read and comment.
> > > 
> > > Thanks
> > > Jim
> > > 
> > > 
> > > This proposal covers the ability to allow a DBA (and general users) to 
> > > specify where a database and it's individual objects will reside.  I 
> > > propose to add a default data location, index and temporary locations 
> > > to the pg_shadow table to allow a DBA to specify locations for each 
> > > user when they create databases, tables and indexes or need temporary 
> > > disk storage (either for temporary tables or sort files).  The "CREATE 
> > > DATABASE" command will be changed to also take an INDEX location and 
> > > temporary location. All 3 locations will default to the values from 
> > > pg_shadow for the user that is creating the database.  Both the "CREATE 
> > > TABLE" and "CREATE INDEX" commands will be changed to add "WITH 
> > > LOCATION" optional argument (location will default to values from 
> > > PG_DATABASE which were set by the "CREATE DATABASE" command).
> > > 
> > > The following system tables will be changed as follows
> > > PG_SHADOW add dat_location, idx_location, tmp_location (all default to 
> > > PG_DATA)
> > > PG_DATABASE add dat_location, idx_location, tmp_location (all default 
> > > to same from PG_SHADOW)
> > > PG_CLASS add rellocation (default to dat_location for tables, 
> > > idx_location for indexes from PG_DATABASE)
> > > 
> > > 
> > > Add a GLOBAL table pg_locations to track valid locations
> > > 
> > > Add the following commands to manage locations
> > > CREATE LOCATION locname PATH 'file system directory';
> > > DROP LOCATION locname;   (this will have to look into each db to make 
> > > sure that any objects are not using it.  Don't know how this will be 
> > > done yet!)
> > > 
> > > I propose to change the names of the on disk directories from 999999 to 
> > > 99999_DATA, 99999_INDEX and 99999_TEMP (where 99999 is the OID from 
> > > PG_DATABASE).  A SYMLINK from 99999_INDEX and 99999_TEMP will be made 
> > > back to 99999_DATA will be made so the WAL functions will continue to 
> > > work.
> > > 
> > > 
> > > Again from my earlier attempt at this patch, I believe this capability 
> > > will not only improve performance (see my earlier emails.  Where 
> > > depending on the type of disks the improvement was between 0% and 100% 
> > > performance gain running pg_bench) but also give DBA's the flexibility 
> > > to spread the data files over multiple disks without having to "hack" 
> > > the system using symbolic links. 
> > > 
> > > 
> > > 
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 2: you can get off all lists at once with the unregister command
> > >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > > 
> > 
> > -- 
> >   Bruce Momjian                        |  http://candle.pha.pa.us
> >   pgman@candle.pha.pa.us               |  (610) 853-3000
> >   +  If your life is a hard drive,     |  830 Blythe Avenue
> >   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 5 Mar 2002 01:31:42 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Storage Location Patch Proposal for V7.3
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I think Jim has some very good points here.  What does his
> implementation lack?

Forward compatibility to a future tablespace implementation.
If we do this, we'll be stuck with supporting this feature set,
not to mention this syntax; neither of which have garnered any
support from the assembled hackers.

I went back to look at TODO.detail/tablespaces, and find that it's
badly in need of editing.  Much of the discussion there is
back-and-forthing about the question of naming files by OID,
which is now a done deal.  But it is clear that people wanted to
have a notion of tablespaces as objects somewhat orthogonal to
databases.  I didn't see any support for hard-wiring tablespace
assignments on the basis of "tables here, indexes there", either.

			regards, tom lane

Tue, 05 Mar 2002 02:30:07 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Storage Location Patch Proposal for V7.3 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I think Jim has some very good points here.  What does his
> > implementation lack?
> 
> Forward compatibility to a future tablespace implementation.
> If we do this, we'll be stuck with supporting this feature set,
> not to mention this syntax; neither of which have garnered any
> support from the assembled hackers.
> 
> I went back to look at TODO.detail/tablespaces, and find that it's
> badly in need of editing.  Much of the discussion there is
> back-and-forthing about the question of naming files by OID,

Agreed.

> which is now a done deal.  But it is clear that people wanted to
> have a notion of tablespaces as objects somewhat orthogonal to
> databases.  I didn't see any support for hard-wiring tablespace
> assignments on the basis of "tables here, indexes there", either.

OK, I read through it.  Wow, it was long.  Exactly what is missing from
the patch that he can add?  It is mostly having tablespaces independent
of databases?   There were so many proposals in there I am not sure
where it all landed.  The cleaned out version link to from the TODO list
should be slightly easier reading, but it clearly goes all over the
place.  I can try and read through it and distill down the ideas if that
would help.


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 5 Mar 2002 02:51:34 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Storage Location Patch Proposal for V7.3
...
> Forward compatibility to a future tablespace implementation.
> If we do this, we'll be stuck with supporting this feature set,
> not to mention this syntax; neither of which have garnered any
> support from the assembled hackers.

The feature set (in some incarnation) is exactly something we should
have. "Tablespace" could mean almost anything, since (I recall that) we
are not slavishly copying the Oracle features having a similar name. The
syntax (or something similar) seems acceptable to me. I haven't looked
at the implementation itself.

So, I'll guess that the particular objection to this implementation is
along the lines of wanting to be able to manage tablespaces/locations as
a single entity? So that one could issue commands like (forgive the
syntax) "move tablespace xxx to yyy;" and be able to yank the entire
contents from one place to another in a single line?

Jim's patches don't explicitly tie the pieces residing in a single
location together. Is that the objection? In all other respects (and
perhaps in all respects period) it seems to be a good starting point at
least.

I know that you have said that you want to look at "tablespaces" for
7.3. If we get there with a feature set we all find acceptable, then
great. If we don't, then Jim's subset of features would be great to
have.

Comments?

                       - Thomas

Tue, 05 Mar 2002 06:02:47 -0800
Thomas Lockhart <lockhart@fourpalms.org>
Re: Storage Location Patch Proposal for V7.3