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