Re: Big 7.1 open items - Mailing list pgsql-hackers

Bruce Momjian writes:

> If we have a new CREATE DATABASE LOCATION command, we can say:
> 
>     CREATE DATABASE LOCATION dbloc IN '/var/private/pgsql';
>     CREATE DATABASE newdb IN dbloc;

We kind of have this already, with CREATE DATABASE foo WITH LOCATION =
'bar'; but of course with environment variable kludgery. But it's a start.

>     mkdir /var/private/pgsql/dbloc
>     ln -s /var/private/pgsql/dbloc data/base/dbloc

I think the problem with this was that you'd have to do an extra lookup
into, say, pg_location to resolve this. Some people are talking about
blind writes, this is not really blind.

>     CREATE LOCATION tabloc IN '/var/private/pgsql';
>     CREATE TABLE newtab ... IN tabloc;

Okay, so we'd have "table spaces" and "database spaces". Seems like one
"space" ought to be enough. I was thinking that the database "space" would
serve as a default "space" for tables created within it but you could
still create tables in other "spaces" than were the database really is. In
fact, the database wouldn't show up at all in the file names anymore,
which may or may not be a good thing.

I think Tom suggested something more or less like this:

$PGDATA/base/tablespace/segment/table

(leaving the details of "table" aside for now). pg_class would get a
column storing the table space somehow, say an oid reference to
pg_location. There would have to be a default tablespace that's created by
initdb and it's indicated by oid 0. So if you create a simple little table
"foo" it ends up in

$PGDATA/base/0/0/foo

That is pretty manageable. Now to create a table space you do

CREATE LOCATION "name" AT '/some/where';

which would make an entry in pg_location and, similar to how you
suggested, create a symlink from

$PGDATA/base/newoid -> /some/where

Then when you create a new table at that new location this gets simply
noted in pg_class with an oid reference, the rest works completely
transparently and no lookup outside of pg_class required. The system would
create the segment 0 subdirectory automatically.

When tables get segmented the system would simply create subdirectories 1,
2, 3, etc. as needed, just as it created the 0 as need, no extra code.

pg_dump doesn't need to use lstat or whatever at all because the locations
are catalogued. Administrators don't even need to know about the linking
business, they just make sure the target directory exists.

Two more items to ponder:

* per-location transaction logs

* pg_upgrade


-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden