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

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

Introducing pg_port into the paths would be a bad idea, since it
would prevent restarting a postmaster with a different port number.
I think if a DBA is running multiple postmasters, it's up to him
to avoid pointing more than one of them at the same "location"
directory. (Maybe we could enforce that with lock files? Not
sure it's worth the trouble though.)

regards, tom lane

On Mon, Nov 05, 2001 at 12:26:17PM -0500, Jim Buttafuoco allegedly wrote:
> 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

<SNIP>

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

Should data/ even be in there? /db2/5432 seems to be the correct value.
Either that or change the location to /db2/data. Implicitly creating an
extra directory isn't something I would like to happen, especially if it
doesn't happen for PGDATA itself.

My $.02,

Mathijs
--
And the beast shall be made legion. Its numbers shall be increased a
thousand thousand fold. The din of a million keyboards like unto a great
storm shall cover the earth, and the followers of Mammon shall tremble.