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.