Re: Big 7.1 open items

[ Charset ISO-8859-1 unsupported, converting... ]
> 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.

Yes, I didn't like the environment variable stuff. In fact, I would
like to not mention the symlink location anywhere in the database, so it
can be changed without changing it in the database.

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

I was think of storing the relfilename as dbloc/mytab32332.

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

So you mix tables from different database in the same tablespace? Seems
better to keep them in separate directories for efficiency and clarity.

We could use tablespace/dbname/table so that a tablespace would have
a directory for each database that uses the tablespace.
>
> (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
>

Seems better to use the top directory for 0, and have extents in
subdirectories like Extent-2, etc. Easier for administrators and new
people.

However, one problem is that tables created in a database without a
location are put under pgsql directory. You would have to symlink the
actual database directory. Maybe that is why I had separate database
locations. I realize that is bad.

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

What I was suggesting is not to catalog the symlink locations, but to
use lstat when dumping, so that admins can move files around using
symlinks and not have to udpate the database.

--
Bruce Momjian | http://www.op.net/~candle
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026