Re: Big 7.1 open items

OK, I have thought about tablespaces, and here is my proposal. Maybe
there will some good ideas in my design.

My feeling is that intelligent use of directories and symlinks can allow
PostgreSQL to handle tablespaces and allow administrators to use
symlinks outside of PostgreSQL and have PostgreSQL honor those changes
in a reload.

Seems we have three tablespace needs:

locate database in separate disk
locate tables in separate directory/symlink
locate secondary extents on different drives

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;

The first command makes sure /var/private/pgsql exists and is write-able
by postgres. It then creates a dbloc directory and a symlink:

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

The CREATE DATABASE command creates data/base/dbloc/newdb and creates
the database there. We would have to store the dbloc location in
pg_database.

To handle placing tables, we can use:

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

The first command makes sure /var/private/pgsql exists and is write-able
by postgres. It then creates a directory tabloc in /var/private/pgsql,
and does a symlink:

ln -s /var/private/pgsql/tabloc data/base/dbloc/newdb/tabloc

and creates the table in there. These location names have to be stored
in pg_class.

The difference betweeen CREATE LOCATION and CREATE DATABASE LOCATION is
that the first one puts it in the current database, while the latter
puts the symlinks in data/base.

(Can we remove data/base and just make it data/?)

I would also allow a simpler CREATE LOCATION tabloc2 which just creates
a directory in the database directory. These can be moved later using
symlinks. Of course, CREATE DATABASE LOCATION too.

I haven't figured out extent locations yet. One idea is to allow
administrators to create symlinks for tables >1 gig, and to not remove
the symlinks when a table shrinks. Only remove the file pointed to by
the table, but leave the symlink there so if the table grows again, it
can use the symlink. lstat() would allow this.

Now on to preserving this information. My ideas is that PostgreSQL
should never remove a directory or symlink in the data/base directory.
Those represent locations made by the administrator. So, pg_dump with a
-l option can go through the db directory and output CREATE LOCATION
commands for every database, so when reloaded, the locations will be
preserved, assuming the symlinks point to still-valid directories.

What this does allow is someone to create locations during table
population, but to keep them all on the same drive. If they later move
things around on the disk using cp and symlinks, this will be preserved
by pg_dump.

My problem with many of the tablespace systems is that it requires two
changes. One in the file system using symlinks, and another in the
database to point to the new entries, or it does not preserve them
across backups.

If someone does want to remove a location, they would have to remove all
tables in the directory, and the base directory and symlink can be
removed with DROP LOCATION.

My solution basically stores locations for databases and tables in the
database, but does _not_ store information about what locations exist or
if they are symlinks. However, it does allow for preserving of this
information in dumps.

I feel this solution is very flexible.

Comments?

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