Re: Storage Location Patch Proposal for V7.3

Tom,

Yes, locations = tablespaces (I really don't care if we call them
locaitons or tablespaces, I was just using LOCATIONS because that's what
we have now...)  is there a SQL standard for this???.

As for locations and user,  Under Oracle a user is assigned a default
tablespace and a temporary tablespace via the "CREATE USER" command. 
Also "CREATE DATABASE" allows you to specify the SYSTEM tablespace where
all objects will go unless a storage clause is added duration object
creation.  "CREATE TABLE" and "CREATE INDEX" both take a storage clause.

 As for the actual data file location, I believe under each loc oid we
would have pg_port #/DB OID/pg_class OID might be the way to go.  

The example below has 3 tablespaces/locations PGDATA/DB1/DB2
PG_LOCATIONS (or PG_TABLESPACES) would have the following rows
PGDATA | /usr/local/pgsql/data
DB1    | /db1
DB2    | /db2


/usr/local/pgsql/data/5432/1       <<template1                  ^-----------    <<default location/tablespace
          ^---------  <<Default PG Port
 

/db1/data/5432     ^-------------------------<< second location default PG PORT
/db1/data/5432/65894834/99999999                                ^------<< somedb/sometable
/db1/data/5432/65894834/88888888                        ^------<< somedb/someindex                
/db2/data/5432      ^-------------------------<< DB2


> "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
> 
>