Storage Location / Tablespaces (try 3)

Me again, I have some more details on my storage location patch 



This patch would allow the system admin (DBA) to specify the location of
databases, tables/indexes and temporary objects (temp tables and temp sort
space) independent of the database/system default location.  This patch would
replace the current "LOCATION" code.

Please let me know if you have any questions/comments.  I would like to see
this feature make 7.3.  I believe it will take about 1 month of coding and
testing after I get started.

Thanks
Jim

==============================================================================
Storage Location Patch (Try 3)


(If people like TABLESPACE instead of LOCATION then s/LOCATION/TABLESPACE/g
below)


This patch would add the following NEW commands
----------------------------------------------------   CREATE LOCATION name PATH 'dbpath';   DROP   LOCATION name;

where dbpath is any directory that the postgresql backend can write to.
(I know this is how Oracle works, don't know about the other major db systems)

The following NEW GLOBAL system table would be added.  
-----------------------------------------------------
PG_LOCATION
(      LOC_NAME  name,     LOC_PATH  text    -- This should be able to take any path name.
);
(initdb would add (PGDATA,'/usr/local/pgsql/data')

The following system tables would need to be modified
-----------------------------------------------------
PG_DATABASE drop datpath            add  DATA_LOC_NAME  name or DATA_LOC_OID OID              add  INDEX_LOC_NAME name
orINDEX_LOC_OID OID           add  TEMP_LOC_NAME  name or TEMP_LOC_OID OID
 
PG_CLASS to add LOC_NAME name or LOC_OID OID 

DATA_LOC_* and INDEX_LOC_* would default to PGDATA if not specified.

(I like *LOC_NAME better but I believe the rest of the systems tables use OID)


The following command syntax would be modified
------------------------------------------------------
CREATE DATABASE WITH DATA_LOCATION=XXX INDEX_LOCATION=YYY TEMP_LOCATION=ZZZ
CREATE TABLE aaa (...) WITH LOCATION=XXX;
CREATE TABLE bbb (c1 text primary key location CCC) WITH LOCATION=XXX;
CREATE TABLE ccc (c2 text unique location CCC) WITH LOCATION=XXX;
CREATE INDEX XXX on SAMPLE (C2) WITH LOCATION BBB;



Now for an example
------------------------------------------------------
First:   postgresql is installed at /usr/local/pgsql   userid     postgres   the postgres user also is the owner of
/pg01/pg02 /pg03
 

the dba executes the following script
CREATE LOCATION pg01 PATH '/pg01';
CREATE LOCATION pg02 PATH '/pg02';
CREATE LOCATION pg03 PATH '/pg03';
CREATE LOCATION bigdata  PATH '/bigdata';
CREATE LOCATION bigidx  PATH '/bigidx';
\q

PG_LOCATION now has
pg01    | /pg01
pg02    | /pg02
pg03    | /pg03
bigdata | /bigdata
bigidx  | /bigidx

Now the following command is run
CREATE DATABASE jim1 WITH DATA_LOCATION='pg01' INDEX_LOCATION='pg02'
TEMP_LOCATION='pg03'
-- OID of 'jim1' tuple is 1786146

on disk the directories look like this 
/pg01/1786146       <<-- Default DATA Location 
/pg02/1786146       <<-- Default INDEX Location
/pg03/1786146       <<-- Default Temp Location 

All files from the above directories will have symbolic links to
/usr/local/pgsql/data/base/1786146/ 



Now the system will have 1 BIG table that will get its own disk for data and
its own disk for index
create table big (a text,b text ..., primary key (a,b) location 'bigidx');

oid of big table is 1786150
oid of big table primary key index is 1786151

on disk directories look like this
/bigdata/1786146/1786150
/bigidx/1786146/1786151
/usr/local/pgsql/data/base/1786146/1786150 symbolic link to
/bigdata/1786146/1786150
/usr/local/pgsql/data/base/1786146/1786151 symbolic link to
/bigdata/1786146/1786151



The symbolic links will enable the rest of the software to be location
independent.