Re: Storage Location Patch Proposal for V7.3

"Jim Buttafuoco" <jim@buttafuoco.net> writes:
> 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).

Have you read any of the previous discussions about tablespaces?
This seems to be tablespaces with an off-the-cuff syntax.  I'd
suggest taking a hard look at Oracle's tablespace facility and
seeing how closely we want to duplicate that.

> PG_SHADOW add dat_location, idx_location, tmp_location (all default to 
> PG_DATA)

What does location have to do with users?

> 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).

No, that doesn't scale to arbitrary locations; furthermore it requires
an unseemly amount of knowledge in low-level file access code about
exactly what kind of object each table is.  The symlinks should just
be named after the OIDs of the locations' rows in pg_location.

The direction I've been envisioning for this is that each table has
a logical identification <pg_database OID>, <pg_class OID> as well
as a physical identification <pg_location OID>, <relfilenode OID>.
The path to access the table can be constructed entirely from the
physical identification: $PGDATA/base/<pg_location OID>/<relfilenode OID>.

One problem to be addressed if multiple databases can share a single
physical location is how to prevent relfilenode collisions.  Perhaps
we could avoid the issue by adding another layer of subdirectories:
$PGDATA/base/<pg_location OID>/<pg_database OID>/<relfilenode OID>.
That is, each database would have a subdirectory within each location
that it's ever used.  (This would make DROP DATABASE a lot easier,
among other things.)
        regards, tom lane