Re: Storage Location Patch Proposal for V7.3 - Mailing list pgsql-hackers

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