Storage Location Patch Proposal for V7.3

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.