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(at)postgresql(dot)org)
>

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