Обсуждение: Tablespaces

Hi all,

I've been looking at implementing table spaces for 7.5. Some notes and
implementation details follow.

------

Type of table space:

There are many different table space implementations in relational
database management systems. In my implementation, a table space in
PostgreSQL will be the location of a directory on the file system in
which files backing database objects can be stored. Global tables and
non 'data' objects (WAL, CLOG, config files) will all remain in $PGDATA.
$PGDATA/base will be the default table space.

A given table space will be identified by a unique table space name. I
haven't decided if 'unique' should mean database-wide unique or
cross-database unique. It seems to me that we might run into problems
with CREATE DATABASE ... TEMPLATE = <database with table spaces> if the
uniqueness of table spaces is limited to the database level.

A table space parameter will be added to DDL commands which create
physical database objects (CREATE DATABASE/INDEX/TABLE/SEQUENCE) and to
CREATE SCHEMA. The associated routines, as well as the corresponding DROP
commands will need to be updated. Adding the ability to ALTER <object>
TABLESPACE <name> seems a little painful. Would people use it? Comments?

When an object is created the system will resolve the table space the
object is stored in as follows: if the table space paramater is passed to
the DDL command, then the object is stored in that table space (given
validation of the table space, etc). If it is not passed, the object
inherits its "parent's" table space where the parent/child hierarchy is as
follows: database > schema > table > [index|sequence]. So, if you issued:
create table foo.bar (...);

We would first not that there is no TABLESPACE <name>, then cascade to
the table space for the schema 'foo' (and possibly cascade to the table
space for the database). A database which wasn't created with an explicit
table space will be created under the default table space. This ensures
backward compatibility.


Creating a table space:

A table space is a directory structure. The directory structure is as
follows:

[swm@dev /path/to/tblspc]$ ls
OID1/    OID2/

OID1 and OID2 are the OIDs of databases which have created a table space
against this file system location. In this respect, a table space
resembles $PGDATA/base. I thought it useful to keep this kind of
namespace mechanism in place so that administrators do not need to create
hierarchies of names on different partitions if they want multiple
databases to use the same partition.

The actual creation of the table space will be done with:
CREATE TABLE SPACE <name> LOCATION </path/to/tblspc>;

Before creating the table space we must:

1) Check if the directory exists. If it does, create a sub directory as
the OID of the current database.

2) Alternatively, if the directory doesn't exist, attempt to create it,
then the sub directory.

I wonder if a file, such as PG_TBLSPC, should be added to the table space
directory so that, in the case of an existing non-empty directory, we can
attempt to test if the directory is being used for something else and
error out. Seems like:

CREATE TABLESPACE tbl1 LOCATION '/var/'

which will result in something like '/var/123443' is a bad idea. Then
again, the user should know better. Comments?

If everything goes well, we add an entry to pg_tablespace with the table
space location and name (and and OID).


Tying it all together:

The catalogs pg_database, pg_namespace, and pg_class will have a 'tblspc'
field. This will be the OID of the table space the object resides in, or 0
(default table space). Since we can then resolve relid/relname, schema and
database to a tablespace, there aren't too many cases when extra logic
needs to be added to the IO framework. In fact, most of it is taken care
of because of the abstraction of relpath().

The creation of table spaces will need to be recorded in xlog in the same
way that files are in heap_create() with the corresponding delete logic
incase of ABORT.


Postmaster startup:

Ideally, the postmaster at startup should go into each tblspc/databaseoid
directory and check for a postmaster.pid file to see if some other
instance is touching the files we're interested in. This will require a
control file listing tblspc/databaseoid paths and it will need to plug
into WAL in case we die during CREATE TABLESPACE. Comments?


Creating a database

I think that createdb() is going to have to be reworked if pg_tablespace
isn't shared (ie, tablespaces are only database unique). The reason being
that if we create a database which has a table space, pg_tablespace in the
new database will have to be updated and that cannot be done atomically
with the `cp` based mechanism we currently use.

I think I'm going to have to get my hands dirty before I can tell the
extent to which createdb() will need reworking.


pg_dump

Obviously pg_dump will need to be able to dump table spaces. pg_dump
running against <7.5 will DDL commands without a table space parameter and
as such the database's physical layout, when loaded into 7.5, will be the
same as for <7.5.

---

Comments? Questions? Suggestions?

Thanks,

Gavin

On Thursday 26 February 2004 15:37, Gavin Sherry wrote:
> Tying it all together:
>
> The catalogs pg_database, pg_namespace, and pg_class will have a 'tblspc'
> field. This will be the OID of the table space the object resides in, or 0
> (default table space). Since we can then resolve relid/relname, schema and
> database to a tablespace, there aren't too many cases when extra logic
> needs to be added to the IO framework. In fact, most of it is taken care
> of because of the abstraction of relpath().
>
> The creation of table spaces will need to be recorded in xlog in the same
> way that files are in heap_create() with the corresponding delete logic
> incase of ABORT.

Is tablespace some sort of copyrighted? Last I remembered, the discussion was 
about location/storage
Just a thought..
Shridhar

Gavin Sherry wrote:
> Hi all,
> 
> I've been looking at implementing table spaces for 7.5. Some notes and
> implementation details follow.
> 
> ------
> 
> Type of table space:
> 
> There are many different table space implementations in relational
> database management systems. In my implementation, a table space in
> PostgreSQL will be the location of a directory on the file system in
> which files backing database objects can be stored. Global tables and
> non 'data' objects (WAL, CLOG, config files) will all remain in $PGDATA.
> $PGDATA/base will be the default table space.

Is it possible to put WALs and CLOGs into different tablespaces? (maybe 
different RAID systems). Some companies want that ...


> A given table space will be identified by a unique table space name. I
> haven't decided if 'unique' should mean database-wide unique or
> cross-database unique. It seems to me that we might run into problems
> with CREATE DATABASE ... TEMPLATE = <database with table spaces> if the
> uniqueness of table spaces is limited to the database level.


I strongly vote for database cluster wide unique names because somebody 
could have a tablespace "webusers" or something like that. To me this 
makes far more sense.


> A table space parameter will be added to DDL commands which create
> physical database objects (CREATE DATABASE/INDEX/TABLE/SEQUENCE) and to
> CREATE SCHEMA. The associated routines, as well as the corresponding DROP
> commands will need to be updated. Adding the ability to ALTER <object>
> TABLESPACE <name> seems a little painful. Would people use it? Comments?

I think people won't need it in first place because this seems to be 
really painful.
What really matters is that the number of tablespaces and file / 
tablespace is unlimited. SAP DB has limited the number of devspaces to 
32 (I think). This is real bull.... because if your database grows 
unexpectedly you are in deep trouble (expert database design by SAP, 
MySQL and 100000....0000 others).


> When an object is created the system will resolve the table space the
> object is stored in as follows: if the table space paramater is passed to
> the DDL command, then the object is stored in that table space (given
> validation of the table space, etc). If it is not passed, the object
> inherits its "parent's" table space where the parent/child hierarchy is as
> follows: database > schema > table > [index|sequence]. So, if you issued:
> 
>     create table foo.bar (...);
> 
> We would first not that there is no TABLESPACE <name>, then cascade to
> the table space for the schema 'foo' (and possibly cascade to the table
> space for the database). A database which wasn't created with an explicit
> table space will be created under the default table space. This ensures
> backward compatibility.

Will users automatically be assigned to a certain table space? How is 
this going to work?

> Creating a table space:
> 
> A table space is a directory structure. The directory structure is as
> follows:
> 
> [swm@dev /path/to/tblspc]$ ls
> OID1/    OID2/
> 
> OID1 and OID2 are the OIDs of databases which have created a table space
> against this file system location. In this respect, a table space
> resembles $PGDATA/base. I thought it useful to keep this kind of
> namespace mechanism in place so that administrators do not need to create
> hierarchies of names on different partitions if they want multiple
> databases to use the same partition.
> 
> The actual creation of the table space will be done with:
> 
>     CREATE TABLE SPACE <name> LOCATION </path/to/tblspc>;
> 
> Before creating the table space we must:
> 
> 1) Check if the directory exists. If it does, create a sub directory as
> the OID of the current database.
> 
> 2) Alternatively, if the directory doesn't exist, attempt to create it,
> then the sub directory.
> 
> I wonder if a file, such as PG_TBLSPC, should be added to the table space
> directory so that, in the case of an existing non-empty directory, we can
> attempt to test if the directory is being used for something else and
> error out. Seems like:
> 
> CREATE TABLESPACE tbl1 LOCATION '/var/'
> 
> which will result in something like '/var/123443' is a bad idea. Then
> again, the user should know better. Comments?
> 
> If everything goes well, we add an entry to pg_tablespace with the table
> space location and name (and and OID).
> 
> 
> Tying it all together:
> 
> The catalogs pg_database, pg_namespace, and pg_class will have a 'tblspc'
> field. This will be the OID of the table space the object resides in, or 0
> (default table space). Since we can then resolve relid/relname, schema and
> database to a tablespace, there aren't too many cases when extra logic
> needs to be added to the IO framework. In fact, most of it is taken care
> of because of the abstraction of relpath().
> 
> The creation of table spaces will need to be recorded in xlog in the same
> way that files are in heap_create() with the corresponding delete logic
> incase of ABORT.
> 
> 
> Postmaster startup:
> 
> Ideally, the postmaster at startup should go into each tblspc/databaseoid
> directory and check for a postmaster.pid file to see if some other
> instance is touching the files we're interested in. This will require a
> control file listing tblspc/databaseoid paths and it will need to plug
> into WAL in case we die during CREATE TABLESPACE. Comments?
> 
> 
> Creating a database
> 
> I think that createdb() is going to have to be reworked if pg_tablespace
> isn't shared (ie, tablespaces are only database unique). The reason being
> that if we create a database which has a table space, pg_tablespace in the
> new database will have to be updated and that cannot be done atomically
> with the `cp` based mechanism we currently use.
> 
> I think I'm going to have to get my hands dirty before I can tell the
> extent to which createdb() will need reworking.
> 
> 
> pg_dump
> 
> Obviously pg_dump will need to be able to dump table spaces. pg_dump
> running against <7.5 will DDL commands without a table space parameter and
> as such the database's physical layout, when loaded into 7.5, will be the
> same as for <7.5.
> 
> ---
> 
> Comments? Questions? Suggestions?
> 
> Thanks,
> 
> Gavin


Do you plan support for limiting the size of a tablespace? ISPs will 
vote for that because they can limit the size of a database on the 
database level rather than on the operating system level.  Of course 
this can and (should???) be done on the operation system level but 
people will definitely ask for that.
If sizing is not supported we should definitely provide minor 
documentation which tells people how to do that on the operating system 
level (at least poting to some useful information).
Best regards,
    Hans


-- 
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/2952/30706 or +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at

On Thursday 26 February 2004 10:07, Gavin Sherry wrote:
>
> CREATE TABLESPACE tbl1 LOCATION '/var/'
>
> which will result in something like '/var/123443' is a bad idea. Then
> again, the user should know better. Comments?

The LOCATION should have the same owner and permissions as $PGDATA - that 
should catch mistyping.

Unless you're running as root, of course. In which case you clearly know 
better than everyone else, so off you go!

> Comments? Questions? Suggestions?

Presumably I'm using this to deal with performance/space issues, so there 
clearly needs to be something in the pg_stat_xxx system to show figures based 
on tablespace - not sure what you'd measure though - disk I/O, number of 
nodes?

Perhaps something in contrib/ too - tablespace_disk_usage.pl or some such.
--  Richard Huxton Archonet Ltd

> Is it possible to put WALs and CLOGs into different tablespaces? (maybe
> different RAID systems). Some companies want that ...

I wasn't going to look at that just yet.

There is of course the temporary hack of symlinking WAL else where.

I'd be interested to see the performance difference between WAL and data
on the same RAID/controller and WAL and data on different RAID/controller
with Jan's improvements to the buffer management.

Gavin

On Thu, 26 Feb 2004, Gavin Sherry wrote:

> Comments? Questions? Suggestions?

Is that plan that in the future one can split a single table into 
different table spaces? Like storing all rows with year < 1999 in one 
tablespace and the rest in another?

With the rule system and two underlying tables one could make it work by 
hand I think.

I've never used tablespaces in oracle so I don't know what it can offer. I 
though it could do things like the above. True? What is the syntax and 
for example, how does it effect indexes (not at all maybe).

If you don't want to discuss this now, I understand. It's not part of the
design as it is now. I'm just curious at what direction we are moving and
what is possible to do.

-- 
/Dennis Björklund

On Thu, 26 Feb 2004, Dennis Bjorklund wrote:

> On Thu, 26 Feb 2004, Gavin Sherry wrote:
>
> > Comments? Questions? Suggestions?
>
> Is that plan that in the future one can split a single table into
> different table spaces? Like storing all rows with year < 1999 in one
> tablespace and the rest in another?

These are called partitions in oracle. You can approximate this with table
spaces by using a partial index and putting it in a different table space.
The problem, of course, is seq scans.


>
> With the rule system and two underlying tables one could make it work by
> hand I think.
>
> I've never used tablespaces in oracle so I don't know what it can offer. I

Certainly, table spaces are used in many ways in oracle, db2, etc. You can
mirror data across them, have different buffer sizes for example.
In some implementations, they can be raw disk partitions (no file system).
I don't intend going this far, however.

> If you don't want to discuss this now, I understand. It's not part of the
> design as it is now. I'm just curious at what direction we are moving and
> what is possible to do.

Well, partitions are something else entirely. Mirroring would be
interesting, but RAID designers are better at parallelisation of IO than
(some) database developers. Might be better to keep the problem seperate.

Gavin

> Is it possible to put WALs and CLOGs into different tablespaces? (maybe 
> different RAID systems). Some companies want that ...

You can do this now, but it would be nice to be able to have it actually 
configurable versus the hacked linked method.


J



> 

-- 
Co-Founder
Command Prompt, Inc.
The wheel's spinning but the hamster's dead

Gavin Sherry wrote:
>>Is it possible to put WALs and CLOGs into different tablespaces? (maybe
>>different RAID systems). Some companies want that ...
> 
> 
> I wasn't going to look at that just yet.
> 
> There is of course the temporary hack of symlinking WAL else where.

that's what we do now.
we symlink databases and wals ...


> I'd be interested to see the performance difference between WAL and data
> on the same RAID/controller and WAL and data on different RAID/controller
> with Jan's improvements to the buffer management.
> 
> Gavin

yes, that's what i am looking for. i should do some testing.

in case of enough i/o power additional cpus scale almost linearily 
(depending on the application of course; i have done some testing on a 
customer's aix box ...).
it would be interesting to see what jan's buffer strategy does (and bg 
writer) ...


-- 
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/2952/30706 or +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at

On Thu, Feb 26, 2004 at 11:22:28PM +1100, Gavin Sherry wrote:

> Certainly, table spaces are used in many ways in oracle, db2, etc. You can
> mirror data across them, have different buffer sizes for example.
> In some implementations, they can be raw disk partitions (no file system).
> I don't intend going this far, however.

Perhaps now would be a good time to bring up my directio on Solaris question
from a year or so back? Is there any interest in the ability to use raw
disk?

Alex (who is overjoyed to hear discussion of tablespaces again)

--
alex@posixnap.net
Alex J. Avriette, Unix Systems Gladiator
Sep 25 12:52:39 buggle /bsd: wsdisplay0 at vga1: removing /dev/radio/*

Gavin Sherry <swm@linuxworld.com.au> writes:
> A table space is a directory structure. The directory structure is as
> follows:
> [swm@dev /path/to/tblspc]$ ls
> OID1/    OID2/
> OID1 and OID2 are the OIDs of databases which have created a table space
> against this file system location. In this respect, a table space
> resembles $PGDATA/base. I thought it useful to keep this kind of
> namespace mechanism in place ...

Actually, this is *necessary* AFAICT.  The case that forces it is DROP
DATABASE.  Since you have to execute that from another database, there's
no reasonable way to look into the target database's catalogs.  That
means that the OID of the database has to be sufficient information to
get rid of all its files.  You can do this fairly easily if in each
tablespace (whose locations you know from the shared pg_tablespace
table) you can look for a subdirectory matching the target database's
OID.  If we tried to put the database's files just "loose" in each
tablespace directory then we'd be in trouble.

I think this is also an implementation reason for favoring cluster-wide
tablespaces over database-local ones.  I'm not sure how you drop a
database from outside if you can't see where its tablespaces are.

I believe that it will be necessary to expand RelFileNode to three OIDs
(tablespace, database, relation).  I had once hoped that it could be
kept at two (tablespace, relation) but with a physical layout like this
you more or less have to have three.

One issue that needs to be agreed on early is how the low-level file
access code finds a tablespace.  What I would personally like is for
$PGDATA to contain symlinks to the tablespace top directories.  The
actual access path for any relation could then be built trivially from
its RelFileNode:$PGDATA/tablespaces/TBOID/DBOID/RELFILENODE       -------------------------
The underlined part references a symlink that leads to the directory
containing the per-database subdirectories.

I am expecting to hear some bleating about this from people whose
preferred platforms don't support symlinks ;-).  However, if we don't
rely on symlinks for this then the tablespace-OID-to-physical-path
mapping has to be explicitly known at very low levels of the system
(md.c in particular).  We can't expect md.c to get that information by
reading pg_tablespace.  It would have to rely on some backdoor path,
such as a flat text file it could read at backend startup.  I think
this approach will leave us fighting a lot of problems with locking
and out-of-date information.

Speaking of locking, can we do anything to prevent people from shooting
themselves in the foot by changing active tablespaces?  Are we even
going to have a DROP TABLESPACE command, and if so what would it do?
        regards, tom lane

Gavin,

After creating a tablespace what (if any) changes can be done to it. 
Can you DROP a tablespace, or once created will it always exist?  Can 
you RENAME a tablespace?  Can you change the location of a tablespace 
(i.e you did a disk reorg and move the contents to a different location 
and now want to point to the new location)?  What are the permissions 
necessary to create a tablespace (can any use connected to the database 
create a tablespace, or only superuser, or ...)?

Overall this will be a great addition to postgres.  I am looking forward 
to this feature.

thanks,
--Barry


Gavin Sherry wrote:
> Hi all,
> 
> I've been looking at implementing table spaces for 7.5. Some notes and
> implementation details follow.
> 
> ------
> 
> Type of table space:
> 
> There are many different table space implementations in relational
> database management systems. In my implementation, a table space in
> PostgreSQL will be the location of a directory on the file system in
> which files backing database objects can be stored. Global tables and
> non 'data' objects (WAL, CLOG, config files) will all remain in $PGDATA.
> $PGDATA/base will be the default table space.
> 
> A given table space will be identified by a unique table space name. I
> haven't decided if 'unique' should mean database-wide unique or
> cross-database unique. It seems to me that we might run into problems
> with CREATE DATABASE ... TEMPLATE = <database with table spaces> if the
> uniqueness of table spaces is limited to the database level.
> 
> A table space parameter will be added to DDL commands which create
> physical database objects (CREATE DATABASE/INDEX/TABLE/SEQUENCE) and to
> CREATE SCHEMA. The associated routines, as well as the corresponding DROP
> commands will need to be updated. Adding the ability to ALTER <object>
> TABLESPACE <name> seems a little painful. Would people use it? Comments?
> 
> When an object is created the system will resolve the table space the
> object is stored in as follows: if the table space paramater is passed to
> the DDL command, then the object is stored in that table space (given
> validation of the table space, etc). If it is not passed, the object
> inherits its "parent's" table space where the parent/child hierarchy is as
> follows: database > schema > table > [index|sequence]. So, if you issued:
> 
>     create table foo.bar (...);
> 
> We would first not that there is no TABLESPACE <name>, then cascade to
> the table space for the schema 'foo' (and possibly cascade to the table
> space for the database). A database which wasn't created with an explicit
> table space will be created under the default table space. This ensures
> backward compatibility.
> 
> 
> Creating a table space:
> 
> A table space is a directory structure. The directory structure is as
> follows:
> 
> [swm@dev /path/to/tblspc]$ ls
> OID1/    OID2/
> 
> OID1 and OID2 are the OIDs of databases which have created a table space
> against this file system location. In this respect, a table space
> resembles $PGDATA/base. I thought it useful to keep this kind of
> namespace mechanism in place so that administrators do not need to create
> hierarchies of names on different partitions if they want multiple
> databases to use the same partition.
> 
> The actual creation of the table space will be done with:
> 
>     CREATE TABLE SPACE <name> LOCATION </path/to/tblspc>;
> 
> Before creating the table space we must:
> 
> 1) Check if the directory exists. If it does, create a sub directory as
> the OID of the current database.
> 
> 2) Alternatively, if the directory doesn't exist, attempt to create it,
> then the sub directory.
> 
> I wonder if a file, such as PG_TBLSPC, should be added to the table space
> directory so that, in the case of an existing non-empty directory, we can
> attempt to test if the directory is being used for something else and
> error out. Seems like:
> 
> CREATE TABLESPACE tbl1 LOCATION '/var/'
> 
> which will result in something like '/var/123443' is a bad idea. Then
> again, the user should know better. Comments?
> 
> If everything goes well, we add an entry to pg_tablespace with the table
> space location and name (and and OID).
> 
> 
> Tying it all together:
> 
> The catalogs pg_database, pg_namespace, and pg_class will have a 'tblspc'
> field. This will be the OID of the table space the object resides in, or 0
> (default table space). Since we can then resolve relid/relname, schema and
> database to a tablespace, there aren't too many cases when extra logic
> needs to be added to the IO framework. In fact, most of it is taken care
> of because of the abstraction of relpath().
> 
> The creation of table spaces will need to be recorded in xlog in the same
> way that files are in heap_create() with the corresponding delete logic
> incase of ABORT.
> 
> 
> Postmaster startup:
> 
> Ideally, the postmaster at startup should go into each tblspc/databaseoid
> directory and check for a postmaster.pid file to see if some other
> instance is touching the files we're interested in. This will require a
> control file listing tblspc/databaseoid paths and it will need to plug
> into WAL in case we die during CREATE TABLESPACE. Comments?
> 
> 
> Creating a database
> 
> I think that createdb() is going to have to be reworked if pg_tablespace
> isn't shared (ie, tablespaces are only database unique). The reason being
> that if we create a database which has a table space, pg_tablespace in the
> new database will have to be updated and that cannot be done atomically
> with the `cp` based mechanism we currently use.
> 
> I think I'm going to have to get my hands dirty before I can tell the
> extent to which createdb() will need reworking.
> 
> 
> pg_dump
> 
> Obviously pg_dump will need to be able to dump table spaces. pg_dump
> running against <7.5 will DDL commands without a table space parameter and
> as such the database's physical layout, when loaded into 7.5, will be the
> same as for <7.5.
> 
> ---
> 
> Comments? Questions? Suggestions?
> 
> Thanks,
> 
> Gavin
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

On Thu, 26 Feb 2004, Alex J. Avriette wrote:

> On Thu, Feb 26, 2004 at 11:22:28PM +1100, Gavin Sherry wrote:
>
> > Certainly, table spaces are used in many ways in oracle, db2, etc. You can
> > mirror data across them, have different buffer sizes for example.
> > In some implementations, they can be raw disk partitions (no file system).
> > I don't intend going this far, however.
>
> Perhaps now would be a good time to bring up my directio on Solaris question
> from a year or so back? Is there any interest in the ability to use raw
> disk?

I do not intend to undertake raw disk tablespaces for 7.5. I'd be
interested if anyone could provide some real world benchmarking of file
system vs. raw disk. Postgres benefits a lot from kernel file system cache
at the moment. Also, I believe that database designers have traditionally
made bad file system designers. Raw database partitions often lack the
tools essential to a scalable environment. For example, the ability to
resize partitions.

Gavin

On Thu, 26 Feb 2004, Tom Lane wrote:

> Gavin Sherry <swm@linuxworld.com.au> writes:
> > A table space is a directory structure. The directory structure is as
> > follows:
> > [swm@dev /path/to/tblspc]$ ls
> > OID1/    OID2/
> > OID1 and OID2 are the OIDs of databases which have created a table space
> > against this file system location. In this respect, a table space
> > resembles $PGDATA/base. I thought it useful to keep this kind of
> > namespace mechanism in place ...
>
> Actually, this is *necessary* AFAICT.  The case that forces it is DROP
> DATABASE.  Since you have to execute that from another database, there's
> no reasonable way to look into the target database's catalogs.  That
> means that the OID of the database has to be sufficient information to
> get rid of all its files.  You can do this fairly easily if in each
> tablespace (whose locations you know from the shared pg_tablespace
> table) you can look for a subdirectory matching the target database's
> OID.  If we tried to put the database's files just "loose" in each
> tablespace directory then we'd be in trouble.

Ahhh. Yes.

>
> I think this is also an implementation reason for favoring cluster-wide
> tablespaces over database-local ones.  I'm not sure how you drop a
> database from outside if you can't see where its tablespaces are.

Naturally.

>
> I believe that it will be necessary to expand RelFileNode to three OIDs
> (tablespace, database, relation).  I had once hoped that it could be
> kept at two (tablespace, relation) but with a physical layout like this
> you more or less have to have three.

Yes. I agree.

>
> One issue that needs to be agreed on early is how the low-level file
> access code finds a tablespace.  What I would personally like is for
> $PGDATA to contain symlinks to the tablespace top directories.  The
> actual access path for any relation could then be built trivially from
> its RelFileNode:
>     $PGDATA/tablespaces/TBOID/DBOID/RELFILENODE
>         -------------------------
> The underlined part references a symlink that leads to the directory
> containing the per-database subdirectories.
>
> I am expecting to hear some bleating about this from people whose
> preferred platforms don't support symlinks ;-).  However, if we don't

Actually, I think that's a pretty good idea :-). I'd solves a bunch of
issues in the backend (postmaster start up can recurse through
$PGDATA/tablespaces looking for postmaster.pid files) and will also assist
admins with complex configurations (perhaps).

> Speaking of locking, can we do anything to prevent people from shooting
> themselves in the foot by changing active tablespaces?  Are we even
> going to have a DROP TABLESPACE command, and if so what would it do?

Ahh. I forgot to detail my ideas on this. It seems to me that we cannot
drop a table space until the directory is empty. We will need a shared
invalidation message so that backends do not attempt to create an object
just after we drop the table space.

Thanks,

Gavin

On Thu, 2004-02-26 at 13:22, Gavin Sherry wrote:
> Postgres benefits a lot from kernel file system cache
> at the moment.


With the implementation of much smarter and more adaptive cache
replacement algorithm i.e. ARC, I would expect the benefit of using the
kernel file system cache to diminish significantly.  It appears to me,
and I could be wrong, that the reason Postgres has depended on the
kernel file system cache isn't that this is obviously better in some
absolute sense (though it might be depending on the deployment
scenario), but that the original cache replacement algorithm in Postgres
was sufficiently poor that the better cache replacement algorithms in
the kernel cache more than offset any sub-optimality that might result
from doing so.

I would expect that with ARC and the redesign of some of the buffer
management bits for more scalability, you might very well get better
performance by allocating most of the memory to the buffer cache rather
than leaving it to the kernel file cache.

I'm actually fairly curious to see what the new buffer management scheme
will mean in terms of real world performance and parameter tuning.

-James Rogersjrogers@neopolitan.com

>Gavin Sherry
> The creation of table spaces will need to be recorded in xlog in the
same
> way that files are in heap_create() with the corresponding delete
logic
> incase of ABORT.

Overall, sounds very cool.

Please could we record the OID of the tablespace in the WAL logs, not
the path to the tablespace? That way, we run no risks of having the WAL
logs not work correctly should things change slightly...

.. need to record drop tablespaces in the WAL logs also. 

I'm sure you meant both of those, just checking.

Can drop tablespace require a specific privelege too? It's too easy to
drop parts of a database without thinking...
Best Regards, Simon Riggs

Gavin Sherry <swm@linuxworld.com.au> writes:
>> Speaking of locking, can we do anything to prevent people from shooting
>> themselves in the foot by changing active tablespaces?  Are we even
>> going to have a DROP TABLESPACE command, and if so what would it do?

> Ahh. I forgot to detail my ideas on this. It seems to me that we cannot
> drop a table space until the directory is empty.

How would it get to be empty?  Are you thinking of some sort of "connect
database to tablespace" and "disconnect database from tablespace"
commands that would respectively create and delete the per-database
subdirectory?  That seems moderately reasonable to me.  We could then
invent a locking protocol that requires backends to lock a tablespace
before they can execute either of these operations (or delete the
tablespace of course).
        regards, tom lane

On Fri, Feb 27, 2004 at 08:22:25AM +1100, Gavin Sherry wrote:

> interested if anyone could provide some real world benchmarking of file
> system vs. raw disk. Postgres benefits a lot from kernel file system cache
> at the moment. Also, I believe that database designers have traditionally
> made bad file system designers. Raw database partitions often lack the
> tools essential to a scalable environment. For example, the ability to
> resize partitions.

The only reason I mentioned it to begin with was the recommendation of
directio for databases in the Sun Blueprint, _Tuning Databases on the
Solaris Platform_ (and being a Solaris geek, I asked, but apparently
nobody else is worried enough about performance or not using Solaris
enough to care).

It's not critical, of course. I think, however, that many of us would
like to see some of the features of Oracle and DB2 available to users
of postgres.  Some of these features are raw disk, tablespaces, and
replication. We're getting there, and making really terrific progress
(I fully expect replication to be ready for primetime in the 8-12 mos
timeframe), but we're not quite there yet.

As I said, I'm very glad to hear tablespaces mentioned again and see
what looks like work being done on it.

Thanks!
Alex

--
alex@posixnap.net
Alex J. Avriette, Solaris Artillery Officer
"Among the many misdeeds of the British rule in India, history will look upon the act of depriving a whole nation of
arms,as the blackest." - Mahatma Gandhi
 

James Rogers <jrogers@neopolitan.com> writes:
> With the implementation of much smarter and more adaptive cache
> replacement algorithm i.e. ARC, I would expect the benefit of using the
> kernel file system cache to diminish significantly.  It appears to me,
> and I could be wrong, that the reason Postgres has depended on the
> kernel file system cache isn't that this is obviously better in some
> absolute sense (though it might be depending on the deployment
> scenario), but that the original cache replacement algorithm in Postgres
> was sufficiently poor that the better cache replacement algorithms in
> the kernel cache more than offset any sub-optimality that might result
> from doing so.

The question of optimality of replacement algorithm is only one of the
arguments for using a small buffer cache.  IMHO a considerably stronger
argument is that the kernel's memory management is more flexible: it can
use that memory for either disk cache or program workspace, and it can
change the allocation on-the-fly as load demands.  If you dedicate most
of RAM to Postgres buffers then you are likely to be wasting RAM or
swapping heavily.  Possibly both :-(

Another gotcha is that unless the OS allows you to lock shared memory
into RAM, the shared buffers themselves could get swapped out, which is
a no-win scenario by any measure.  Keeping the shared buffer arena small
helps prevent that by ensuring all the buffers are "hot".

Of course, this is all speculation until we get some real-world
experience with ARC.  But I don't expect it to be a magic bullet.
        regards, tom lane

> > I am expecting to hear some bleating about this from people whose
> > preferred platforms don't support symlinks ;-).  However, if we don't

Well, one option would be to have the low level filesystem storage (md.c?)
routines implement a kind of symlink themselves. Just a file with a special
magic number followed by a path.

I'm normally against reimplementing OS services but symlinks are really a very
simple concept and simple to implement. Especially if you can make a few
simplifying assumptions: they only ever need to appear as the final path
element not as parent directories and tablespaces don't need symlinks pointing
to symlinks. Ideally postgres also doesn't need to implement relative links
either.

-- 
greg

> > Ahh. I forgot to detail my ideas on this. It seems to me that we cannot
> > drop a table space until the directory is empty.

Agreed.

>
> How would it get to be empty?  Are you thinking of some sort of "connect
> database to tablespace" and "disconnect database from tablespace"
> commands that would respectively create and delete the per-database
> subdirectory?  That seems moderately reasonable to me.  We could then

I would only allow the drop if the directory only contains empty db oid
directories.

Andreas

> I do not intend to undertake raw disk tablespaces for 7.5. I'd be
> interested if anyone could provide some real world benchmarking of file
> system vs. raw disk. Postgres benefits a lot from kernel file system cache
> at the moment.

Yes, and don't forget that pg also relys on the OS for grouping and
sorting the physical writes and doing readahead where appropriate.

The use of raw disks is usually paired with the use of kernel aio.
The difference is said to be up to 30% on Solaris. I can assert, that
it made the difference between a bogged down system and a much better behaved
DB on Sun here.

My experience with kaio on AIX Informix is, that kaio is faster as long as IO
is not the bottleneck (disk 100% busy is the metric to watch, not Mb/s), while
for an IO bound system the Informix builtin IO threads that can be used instead
win. (Since they obviously do better at grouping, sorting and readahead
than the AIX kernel does for kaio)

Overall I think the price and komplexity is too high, especially since there are
enough platforms where the kernel does a pretty good job at grouping, sorting and
readahead. Additionally the kernel takes non PostgreSQL IO into account.

Andreas

On Fri, 27 Feb 2004, Zeugswetter Andreas SB SD wrote:

> 
> > > Ahh. I forgot to detail my ideas on this. It seems to me that we cannot
> > > drop a table space until the directory is empty.
> 
> Agreed.
> 
> > 
> > How would it get to be empty?  Are you thinking of some sort of "connect
> > database to tablespace" and "disconnect database from tablespace"
> > commands that would respectively create and delete the per-database
> > subdirectory?  That seems moderately reasonable to me.  We could then
> 
> I would only allow the drop if the directory only contains empty db oid 
> directories.

Wouldn't this be better tracked in the dependency tracking that's already 
built into postgresql?  Checking to see if the directory is empty is open 
to race conditions, but locking the dependency tracking while dropping a 
tablespace isn't.

On Fri, 27 Feb 2004, Gavin Sherry wrote:

> On Thu, 26 Feb 2004, Alex J. Avriette wrote:
> 
> > On Thu, Feb 26, 2004 at 11:22:28PM +1100, Gavin Sherry wrote:
> >
> > > Certainly, table spaces are used in many ways in oracle, db2, etc. You can
> > > mirror data across them, have different buffer sizes for example.
> > > In some implementations, they can be raw disk partitions (no file system).
> > > I don't intend going this far, however.
> >
> > Perhaps now would be a good time to bring up my directio on Solaris question
> > from a year or so back? Is there any interest in the ability to use raw
> > disk?
> 
> I do not intend to undertake raw disk tablespaces for 7.5. I'd be
> interested if anyone could provide some real world benchmarking of file
> system vs. raw disk. Postgres benefits a lot from kernel file system cache
> at the moment. Also, I believe that database designers have traditionally
> made bad file system designers. Raw database partitions often lack the
> tools essential to a scalable environment. For example, the ability to
> resize partitions.

Is possible / reasonable / smart and or dumb to look at implementing the 
tablespaces as riding atop the initlocation handled stuff.  I.e. 
postgresql can only create tablespaces in areas that are created by 
initlocation, thus keeping it in its box, so to speak?

"scott.marlowe" <scott.marlowe@ihs.com> writes:
> On Fri, 27 Feb 2004, Zeugswetter Andreas SB SD wrote:
>>> How would it get to be empty?  Are you thinking of some sort of "connect
>>> database to tablespace" and "disconnect database from tablespace"
>>> commands that would respectively create and delete the per-database
>>> subdirectory?  That seems moderately reasonable to me.  We could then
>> 
>> I would only allow the drop if the directory only contains empty db oid 
>> directories.

That's subject to race conditions (ie, someone creating a table about
the same time you are deciding it's okay to drop the tablespace).  There
needs to be some interlock, and I think that associating that lock with
infrequently executed connect/disconnect operations would be good from a
performance standpoint.

> Wouldn't this be better tracked in the dependency tracking that's already 
> built into postgresql?

No, because dependencies are local to individual databases.
        regards, tom lane

"scott.marlowe" <scott.marlowe@ihs.com> writes:
> Is possible / reasonable / smart and or dumb to look at implementing the 
> tablespaces as riding atop the initlocation handled stuff.

In my mind, one of the main benefits of this work will be that we'll be
able to get *rid* of the initlocation stuff.  It's a crock.
        regards, tom lane

On Fri, 27 Feb 2004, Tom Lane wrote:

> "scott.marlowe" <scott.marlowe@ihs.com> writes:
> > Is possible / reasonable / smart and or dumb to look at implementing the 
> > tablespaces as riding atop the initlocation handled stuff.
> 
> In my mind, one of the main benefits of this work will be that we'll be
> able to get *rid* of the initlocation stuff.  It's a crock.

OK, that's fine, but I keep thinking that a superuser should have to 
create the tablespace itself, and then tables can be assigned by users 
based on the rights assigned by the dba / superuser.  Is that how we're 
looking at doing it, or will any user be able to create a tablespace 
anywhere postgresql has write permission, or will only dbas be able to 
create AND use table spaces. I'm just not sure how that's gonna be 
handled, and haven't seen it addressed.

"scott.marlowe" <scott.marlowe@ihs.com> writes:
> On Fri, 27 Feb 2004, Tom Lane wrote:
>> In my mind, one of the main benefits of this work will be that we'll be
>> able to get *rid* of the initlocation stuff.  It's a crock.

> OK, that's fine, but I keep thinking that a superuser should have to 
> create the tablespace itself, and then tables can be assigned by users 
> based on the rights assigned by the dba / superuser.

Yeah, we haven't yet gotten to the issue of permissions, but certainly
creating or deleting a tablespace has to be a superuser-only operation,
if only because you probably have also got some manual filesystem work
to do to set up the associated directory; and that has to be done as
root or postgres.

It might be a good idea to restrict connect/disconnect (if we use those
operations) to superusers as well.  
        regards, tom lane

>
>> I do not intend to undertake raw disk tablespaces for 7.5. I'd be
>> interested if anyone could provide some real world benchmarking of file
>> system vs. raw disk. Postgres benefits a lot from kernel file system
>> cache
>> at the moment.
>
> Yes, and don't forget that pg also relys on the OS for grouping and
> sorting the physical writes and doing readahead where appropriate.
>
>

Most people I know want tablespaces in order to limit or preallocate the
disk space used by a table or database in addition to controlling the
physical location of a table or database.

I know on linux, there is the option of creating an empty file or a
specific size using dd, mounting it through loopback, formatting it,
symlinking the appropriate OID/TID (or mounting the lpb device in the
appropriate directory) and then you control how much space that
directory/mount point can contain.

Of course, with MVCC you would have to vacuum frequently, as you could
miss some updates if there weren't enough tuples marked as free.  If there
were "in-place" updates, the preallocation and limitation much easier, but
that's not how PG works.

If the tablespace disk space allocation is exceeded there would need to be
some graceful reporting condition back to the client.  "UPDATE/INSERT
failed (tablespace size exceeded)", "(tablespace full)", "(disk full)" or
some other error may need to be handled/reported.

On Fri, 27 Feb 2004 tswan@idigx.com wrote:

> >
> >> I do not intend to undertake raw disk tablespaces for 7.5. I'd be
> >> interested if anyone could provide some real world benchmarking of file
> >> system vs. raw disk. Postgres benefits a lot from kernel file system
> >> cache
> >> at the moment.
> >
> > Yes, and don't forget that pg also relys on the OS for grouping and
> > sorting the physical writes and doing readahead where appropriate.
> >
> >
>
> Most people I know want tablespaces in order to limit or preallocate the
> disk space used by a table or database in addition to controlling the
> physical location of a table or database.
>
> I know on linux, there is the option of creating an empty file or a
> specific size using dd, mounting it through loopback, formatting it,
> symlinking the appropriate OID/TID (or mounting the lpb device in the
> appropriate directory) and then you control how much space that
> directory/mount point can contain.
>
> Of course, with MVCC you would have to vacuum frequently, as you could
> miss some updates if there weren't enough tuples marked as free.  If there
> were "in-place" updates, the preallocation and limitation much easier, but
> that's not how PG works.

I do not intend to work on such a system for the initial introduction of
table spaces. The problem is, of course, knowing when you're actually out
of space in a table space in any given transaction. Given that WAL is on a
different partition (at least for the moment) the table space will not
have transaction X's data written to it until after transaction X is
finished. And we cannot error out a transaction which is already commited.

The solution is to keep track of free space and error out at some
percentage of free space remaining. But I don't want to complicate
tablespaces too much in 7.5.

Thanks,

Gavin

Gavin Sherry <swm@linuxworld.com.au> writes:
> I do not intend to work on such a system for the initial introduction of
> table spaces. The problem is, of course, knowing when you're actually out
> of space in a table space in any given transaction.

It should not be that hard, at least not on local filesystems.  When PG
realizes that a new page must be added to a table, it does a write() to
append a page of zeroes to the physical table.  This happens
immediately.  It's true that actual data may not be written into that
section of the file till long after commit, but the kernel should do
space allocation checking upon the first write.

I have heard tell that this may not happen when you are dealing with NFS
(yet another reason not to run databases across NFS) but on all local
filesystems I know of, out-of-space should result in a failure before
transaction commit.

I say "should" because I suspect this isn't a very heavily tested code
path in Postgres.  But in theory it should work.  Feel free to submit
bug reports if you find it doesn't.
        regards, tom lane

Gavin,

#1:  I really think that we should have a way to set a "default tablespace"
for any database in a cluster.    This property would be vitally important
for anyone wishing to use tablespaces to impose quotas.   First, the
superuser would:
ALTER DATABASE db1 ALTER DEFAULT_TABLESPACE partition2;
then any regular users creating tables in that database would, by default,
have TABLESPACE partition2 automatically appended to them by the parser
unless overridden in the creation statement by specifying another, specific,
tablespace.

Alternately, the default tablespace could be set through a GUC.   In my mind,
this would be inferior on 2 counts:
1) It would require adding Yet Another Miscellaneos GUC Variable.
2) It would preclude large, multisuer installations from seamlessly using
tablespaces for quotas, becuase there would be no way to transparently set
the GUC differently for each user or database.


#2: Permissions:
I see the permissions issue as quite transparent.   First, I agree that only
the superuser should have the right to create, alter, or drop tablespaces.
'nuff said.
Second, as far as I can see, there is only one relevant permission for regular
users:  USE.   Either the user is permitted to create objects in that
tablespace, or he/she is not.  Other permissions, such as read access, should
NOT be set by tablespace, as such permissions are already governed by
database, table, and schema; to add a SELECT restriction to tablespaces would
frequently result in paralytic snarls of conflicting permissions on complex
installations.
Thus, by my proposal, the only GRANT for tablespaces (executed by a superuser)
would be:
GRANT USE ON tablespace1 TO user;
This permission would ONLY be accessed for CREATE/ALTER TABLE, and CREATE
INDEX statements.
Easy, neh?

#3: ALTER TABLE .... CHANGE TABLESPACE:
This is strictly in the class of "would be a very nice & useful feature if
it's not too difficult".  

Given how painful it is to drop & replace a table with multiple dependencies
(on some databases, only possible by droping & re-loading the entire
database) it would be nice to have an ALTER TABLE command that moved the
table to another tablespace.    It doesn't *seem* to me that this would be a
very challenging bit of programming, as the operation would be very similar
to REINDEX in the manipulation of files.   (But what I know, really?)

Once tablespaces are a feature and some users start using them for quota
management, there will quickly develop situations where the original
tablespace for a db runs out of room and can't be resized.   Being able to
move the table "in situ" then becomes vital, especially on very large
databases ... and when someday combined with partitioned tables, will become
essential.

Further, we will get an *immediate* flurry of requests from users who just
upgraded to 7.5 and want to make use of the tablespaces feature on an
existing production database.

ALTER INDEX ... CHANGE TABLESPACE is *not* needed, though, as there are no
issues other than time which I know of with dropping & re-creating an index.

If ALTER TABLE CHANGE TABLESPACE has some major technical hurdles, then I
think it's one of those things that could be put off until the next version
of tablespaces, or even held until Partition Tables is developed for a
combined solution.    But it would be nice to have.

--
Josh Berkus
Aglio Database Solutions
San Francisco

On Thu, Feb 26, 2004 at 05:28:41PM -0500, Alex J. Avriette wrote:

> The only reason I mentioned it to begin with was the recommendation of
> directio for databases in the Sun Blueprint, _Tuning Databases on the
> Solaris Platform_ (and being a Solaris geek, I asked, but apparently
> nobody else is worried enough about performance or not using Solaris
> enough to care).

That recommendation itself is a few years old.  While it may still be
true that directio is still fastest for Oracle on Solaris, I'd sure
like to see some recent evidence.  I've a funny feeling that this is
an old rule of thumb which is now true in the sense that everyone
believes it, but maybe not in the sense that a test would reveal it
to be a sensible rule.

> like to see some of the features of Oracle and DB2 available to users
> of postgres.  Some of these features are raw disk, tablespaces, and
> replication. We're getting there, and making really terrific progress

I don't think we want features for their own sake, though, and I'm
not convinced that raw filesystems are actually useful.  Course, it's
not my itch, and PostgreSQL _is_ free software.

A

-- 
Andrew Sullivan 

>Gavin Sherry
> On Fri, 27 Feb 2004 tswan@idigx.com wrote:
> > Most people I know want tablespaces in order to limit or preallocate
the
> > disk space used by a table or database in addition to controlling
the
> > physical location of a table or database.

> I do not intend to work on such a system for the initial introduction
of
> table spaces. The problem is, of course, knowing when you're actually
out
> of space in a table space in any given transaction. Given that WAL is
on a
> different partition (at least for the moment) the table space will not
> have transaction X's data written to it until after transaction X is
> finished. And we cannot error out a transaction which is already
commited.
> 
> The solution is to keep track of free space and error out at some
> percentage of free space remaining. But I don't want to complicate
> tablespaces too much in 7.5.

You're absolutely right about the not-knowing when you're out of space
issue. However, if the xlog has been written then it is not desirable,
but at least acceptable that the checkpoint/bgwriter cannot complete on
an already committed txn. It's not the txn which is getting the error,
that's all.

Hmmm...I'm not sure that we'll be able or should avoid the out of space
situation completely. The question is...what will we do when we hit it?
It doesn't matter whether you stop at 100% or 90% or whatever, you still
have to stop and then what? Stay up as long as possible hopefully: If
there wasn't enough space to write to the tablespace, going into
recovery won't help the situation either; youre still out of space until
you fix that. We now have the option not to crash, since it might be
perfectly viable to keep on chugging away on one Tablespace even though
all txn work on the out-of-space tablespace is frozen/barred etc. Sounds
like a refinement, but something to keep in mind at the design stage if
we can.

The problem is that tablespaces do complicate space management (that's
what people want though, so that's OK). That complicates admin and so pg
will hit many more out of space errors than we've seen previously.
Trying to work out how to spot these ahead of time, accept user defined
limits on each tablespace etc sounds like extra complexity for the
initial drop. I guess my own suggested approach is to start by handling
the error cases, then go back and try to avoid some of them.

All of this exposes for me the complication that doing PITR and
tablespaces at the same time is likely to be more complex for us both
than either had envisaged. The reduced complexity for PITR was what I
was shooting for, also! I'm happy to work together on any issues that
arise.

For PITR, I think we would need:
- a very accessible list of tablespace locations, so taking a full
physical database backup can be easily accomplished using OS utilities.
Hopefully a list maintained external to the database? We have the
equivalent now with env variables.
- decisions about what occurs when for-whatever-reason one or more
tablespaces are not recoverable from backup?
- it might be desirable to allow recovery with less than all of the
original tablespces
- it might also be desirable to allow recovery when the tablespaces txn
Ids don't match (though that is forbidden on many other dbms)

Best Regards, Simon Riggs

"Simon Riggs" <simon@2ndquadrant.com> writes:
> Gavin Sherry wrote:
>> I do not intend to work on such a system for the initial introduction of
>> table spaces. The problem is, of course, knowing when you're actually out
>> of space in a table space in any given transaction. Given that WAL is on a
>> different partition (at least for the moment) the table space will not
>> have transaction X's data written to it until after transaction X is
>> finished. And we cannot error out a transaction which is already
>> commited.

As long as the kernel doesn't lie about file extension, we will not
commit any transaction that requires a disallowed increase in the
allocated size of data files, because allocation of another table page
is checked with the kernel during the transaction.  So on most
filesystems (maybe not NFS) the problem Gavin is worried about doesn't
exist.

> You're absolutely right about the not-knowing when you're out of space
> issue. However, if the xlog has been written then it is not desirable,
> but at least acceptable that the checkpoint/bgwriter cannot complete on
> an already committed txn. It's not the txn which is getting the error,
> that's all.

Right.  This is in fact not a fatal situation, as long as you don't run
out of preallocated WAL space.  For a recent practical example of our
behavior under zero-free-space conditions, see this thread:
http://archives.postgresql.org/pgsql-hackers/2004-01/msg00530.php
particularly the post-mortem here:
http://archives.postgresql.org/pgsql-hackers/2004-01/msg00606.php
Barring one small bug, the database would likely have stayed up, and
continued to service at least the read-only transactions, until Chris
got around to freeing some disk space.

I think it is sufficient (at least in the near term) to expect people to
use partition size limits if they want to control database size --- that
is, make a partition of the desired size and put the database directory
in there.  Tablespaces as per the design we are discussing would make it
easier to apply such a policy to a sub-area of a database cluster than
it is today, but they needn't in themselves implement the restriction.
        regards, tom lane

Tom Lane [mailto:tgl@sss.pgh.pa.us]
> "Simon Riggs" <simon@2ndquadrant.com> writes:
> > You're absolutely right about the not-knowing when you're out of
space
> > issue. However, if the xlog has been written then it is not
desirable,
> > but at least acceptable that the checkpoint/bgwriter cannot complete
on
> > an already committed txn. It's not the txn which is getting the
error,
> > that's all.
> 
> Right.  This is in fact not a fatal situation, as long as you don't
run
> out of preallocated WAL space.  

...following on also from thoughts on [PERFORM] list...

Clearly running out of pre-allocated WAL space is likely to be the next
issue. Running out of space in the first place is likely to be because
of an intense workload, which is exactly the thing which also makes you
run out of pre-allocated WAL space. Does that make sense?

Best regards, Simon Riggs

> A table space parameter will be added to DDL commands which create
> physical database objects (CREATE DATABASE/INDEX/TABLE/SEQUENCE) and to
> CREATE SCHEMA. The associated routines, as well as the corresponding DROP
> commands will need to be updated. Adding the ability to ALTER <object>
> TABLESPACE <name> seems a little painful. Would people use it? Comments?

How about allowing the specification on schemas and databases of 
different default tablespaces for TEMP, TABLE and INDEX??  Is there any 
point to that?

Chris

> I've been looking at implementing table spaces for 7.5. Some notes and
> implementation details follow.

Ah sorry, other things you might need to consider:

Privileges on tablespaces:

GRANT USAGE ON TABLESPACE tbsp TO ...;

Different disk settings for different tablespaces (since they will 
likely be on different disks):

ALTER TABLESPACE tbsp SET random_page_cost TO 2.5;

Chris

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> How about allowing the specification on schemas and databases of 
> different default tablespaces for TEMP, TABLE and INDEX??  Is there any 
> point to that?

TEMP tables are not local to any particular schema, so it wouldn't make
sense to have a schema-level default for their placement.

The other five combinations are at least theoretically sensible, but
do we need 'em all?  It seems to me that a reasonable compromise is to
offer database-level default tablespaces for TEMP, TABLE, and INDEX,
ignoring the schema level.  This is simple and understandable, and if
you don't like it, you're probably the kind of guy who will want to
override it per-table anyway ...

BTW, another dimension to think about is where TOAST tables and their
indexes will get placed.
        regards, tom lane

"Simon Riggs" <simon@2ndquadrant.com> writes:
> Tom Lane wrote:
>> Right.  This is in fact not a fatal situation, as long as you don't
>> run out of preallocated WAL space.  

> Clearly running out of pre-allocated WAL space is likely to be the next
> issue. Running out of space in the first place is likely to be because
> of an intense workload, which is exactly the thing which also makes you
> run out of pre-allocated WAL space. Does that make sense?

I think one of the first things people would do with tablespaces is
stick the data files onto a separate partition from the WAL and clog
files.  (Actually you can do this today with a simple symlink hack, but
tablespaces will make it easier and clearer.)  The space usage for WAL
is really pretty predictable, because of the checkpoint-at-least-
every-N-segments setting.  clog is not exactly a space hog either.
Once you have that separation established, out-of-disk-space can kill
individual transactions but never the database as a whole.

One of the things that bothers me about the present PITR design is that
it presumes that individual WAL log segments can be kept until the
external archiver process feels like writing them somewhere.  If there's
no guarantee that that happens within X amount of time, then you can't
bound the amount of space needed on the WAL drive, and so you are back
facing the possibility of an out-of-WAL-space panic.  I suspect that we
cannot really do anything about that, but it's annoying.  Any bright
ideas out there?
        regards, tom lane

Gavin Sherry wrote:
> The actual creation of the table space will be done with:
> 
>     CREATE TABLE SPACE <name> LOCATION </path/to/tblspc>;

Seems you should use CREATE TABLESPACE (no space) so it is more distinct
from CREATE TABLE.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

Richard Huxton wrote:
> On Thursday 26 February 2004 10:07, Gavin Sherry wrote:
> >
> > CREATE TABLESPACE tbl1 LOCATION '/var/'
> >
> > which will result in something like '/var/123443' is a bad idea. Then
> > again, the user should know better. Comments?
> 
> The LOCATION should have the same owner and permissions as $PGDATA - that 
> should catch mistyping.
> 
> Unless you're running as root, of course. In which case you clearly know 
> better than everyone else, so off you go!

FYI, you can't run the postmaster as root.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

Joshua D. Drake wrote:
> > Is it possible to put WALs and CLOGs into different tablespaces? (maybe 
> > different RAID systems). Some companies want that ...
> 
> You can do this now, but it would be nice to be able to have it actually 
> configurable versus the hacked linked method.

Agreed, but because the system has to be down to move pg_xlog, I think
we should write a command-line utility to assist with this, perhaps.  It
could check permissions and stuff.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

Tom Lane wrote:
> Gavin Sherry <swm@linuxworld.com.au> writes:
> > A table space is a directory structure. The directory structure is as
> > follows:
> > [swm@dev /path/to/tblspc]$ ls
> > OID1/    OID2/
> > OID1 and OID2 are the OIDs of databases which have created a table space
> > against this file system location. In this respect, a table space
> > resembles $PGDATA/base. I thought it useful to keep this kind of
> > namespace mechanism in place ...
> 
> Actually, this is *necessary* AFAICT.  The case that forces it is DROP
> DATABASE.  Since you have to execute that from another database, there's
> no reasonable way to look into the target database's catalogs.  That
> means that the OID of the database has to be sufficient information to
> get rid of all its files.  You can do this fairly easily if in each
> tablespace (whose locations you know from the shared pg_tablespace
> table) you can look for a subdirectory matching the target database's
> OID.  If we tried to put the database's files just "loose" in each
> tablespace directory then we'd be in trouble.
> 

Gavin, let us know if you want us to create the global pg_tablespace for
you.  Some of us have done a lot of system catalog work.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

On Tue, 2 Mar 2004, Bruce Momjian wrote:

> Gavin Sherry wrote:
> > The actual creation of the table space will be done with:
> >
> >     CREATE TABLE SPACE <name> LOCATION </path/to/tblspc>;
>
> Seems you should use CREATE TABLESPACE (no space) so it is more distinct
> from CREATE TABLE.

Oops. Typo.

>
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
>
>
> !DSPAM:40455de0297537578347468!
>
>

Tom Lane wrote:
> Gavin Sherry <swm@linuxworld.com.au> writes:
> >> Speaking of locking, can we do anything to prevent people from shooting
> >> themselves in the foot by changing active tablespaces?  Are we even
> >> going to have a DROP TABLESPACE command, and if so what would it do?
> 
> > Ahh. I forgot to detail my ideas on this. It seems to me that we cannot
> > drop a table space until the directory is empty.
> 
> How would it get to be empty?  Are you thinking of some sort of "connect
> database to tablespace" and "disconnect database from tablespace"
> commands that would respectively create and delete the per-database
> subdirectory?  That seems moderately reasonable to me.  We could then
> invent a locking protocol that requires backends to lock a tablespace
> before they can execute either of these operations (or delete the
> tablespace of course).

One crude solution would be to remove the tablespace oid directory only
when the database is dropped, and require an empty tablespace directory
to drop the tablespace.  This allows a lock only on tablespace creation,
and not a lock on object creation in each tablespace.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

Tom Lane wrote:
> One of the things that bothers me about the present PITR design is that
> it presumes that individual WAL log segments can be kept until the
> external archiver process feels like writing them somewhere.  If there's
> no guarantee that that happens within X amount of time, then you can't
> bound the amount of space needed on the WAL drive, and so you are back
> facing the possibility of an out-of-WAL-space panic.  I suspect that we
> cannot really do anything about that, but it's annoying.  Any bright
> ideas out there?

Maybe specify an archive location (that of course could be on a separate 
partition) that the external archiver should check in addition to the 
normal WAL location. At some predetermined interval, push WAL log 
segments no longer needed to the archive location.

Joe

Greg Stark wrote:
>
> > > I am expecting to hear some bleating about this from people whose
> > > preferred platforms don't support symlinks ;-).  However, if we don't
>
> Well, one option would be to have the low level filesystem storage (md.c?)
> routines implement a kind of symlink themselves. Just a file with a special
> magic number followed by a path.
>
> I'm normally against reimplementing OS services but symlinks are really a very
> simple concept and simple to implement. Especially if you can make a few
> simplifying assumptions: they only ever need to appear as the final path
> element not as parent directories and tablespaces don't need symlinks pointing
> to symlinks. Ideally postgres also doesn't need to implement relative links
> either.

I just checked from the MinGW console and I see:

    # touch a
    # ln -s a b
    # echo test >a
    # cat b
    # l ?
    -rw-r--r--    1 Bruce Mo Administ        5 Mar  2 23:30 a
    -rw-r--r--    1 Bruce Mo Administ        0 Mar  2 23:30 b
    # cat a
    test
    # cat b
    #

It accepts ln -s, but does nothing with it.

For tablespaces on OS's that don't support it, I think we will have to
store the path name in the file and read it via the backend.  Somehow we
should cache those lookups.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tom Lane wrote:
> "scott.marlowe" <scott.marlowe@ihs.com> writes:
> > Is possible / reasonable / smart and or dumb to look at implementing the 
> > tablespaces as riding atop the initlocation handled stuff.
> 
> In my mind, one of the main benefits of this work will be that we'll be
> able to get *rid* of the initlocation stuff.  It's a crock.

Agreed. It should be ripped out once we have tablespaces, and if we keep
it for one extra release, there will be confusion over which to use.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

Josh Berkus wrote:
> #3: ALTER TABLE .... CHANGE TABLESPACE:
> This is strictly in the class of "would be a very nice & useful feature if 
> it's not too difficult". ? 
> 
> Given how painful it is to drop & replace a table with multiple dependencies 
> (on some databases, only possible by droping & re-loading the entire 
> database) it would be nice to have an ALTER TABLE command that moved the 
> table to another tablespace. ? ?It doesn't *seem* to me that this would be a 
> very challenging bit of programming, as the operation would be very similar 
> to REINDEX in the manipulation of files. ? (But what I know, really?)
> 
> Once tablespaces are a feature and some users start using them for quota 
> management, there will quickly develop situations where the original 
> tablespace for a db runs out of room and can't be resized. ? Being able to 
> move the table "in situ" then becomes vital, especially on very large 
> databases ... and when someday combined with partitioned tables, will become 
> essential.
> 
> Further, we will get an *immediate* flurry of requests from users who just 
> upgraded to 7.5 and want to make use of the tablespaces feature on an 
> existing production database.

If we don't implement moving tables between tablespaces, we should add a
stub for it in the grammer and mention it is not implemented yet,
because if we don't, we will get tons of questions.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

Gavin Sherry wrote:
> Actually, I think that's a pretty good idea :-). I'd solves a bunch of
> issues in the backend (postmaster start up can recurse through
> $PGDATA/tablespaces looking for postmaster.pid files) and will also assist
> admins with complex configurations (perhaps).

Why are you asking about postmaster.pid files.  That file goes in the
top level /data directory, no?

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

On Tue, 2 Mar 2004, Bruce Momjian wrote:

> Gavin Sherry wrote:
> > Actually, I think that's a pretty good idea :-). I'd solves a bunch of
> > issues in the backend (postmaster start up can recurse through
> > $PGDATA/tablespaces looking for postmaster.pid files) and will also assist
> > admins with complex configurations (perhaps).
>
> Why are you asking about postmaster.pid files.  That file goes in the
> top level /data directory, no?

I was trying to be paranoid about users who have multiple postmasters on
the same machine and want to share a table space while both systems are
live. There'd be no mechanism to test for that situation if we didn't have
something like a postmaster.pid file. Is this being a little too paranoid?

Gavin

Joe Conway <mail@joeconway.com> writes:
> Tom Lane wrote:
>> facing the possibility of an out-of-WAL-space panic.  I suspect that we
>> cannot really do anything about that, but it's annoying.  Any bright
>> ideas out there?

> Maybe specify an archive location (that of course could be on a separate 
> partition) that the external archiver should check in addition to the 
> normal WAL location. At some predetermined interval, push WAL log 
> segments no longer needed to the archive location.

Does that really help?  The panic happens when you fill the "normal" and
"archive" partitions, how's that different from one partition?
        regards, tom lane

Gavin Sherry <swm@linuxworld.com.au> writes:
> I was trying to be paranoid about users who have multiple postmasters on
> the same machine and want to share a table space while both systems are
> live. There'd be no mechanism to test for that situation if we didn't have
> something like a postmaster.pid file. Is this being a little too paranoid?

Hm.  AFAICS there is no safe situation in which a tablespace directory
could be shared by two different installations (== toplevel $PGDATA
directories).  I don't think we need a dynamic postmaster.pid-type lock
to protect them.  What might make sense is some sort of marker file in a
tablespace directory that links back to the owning $PGDATA directory.
CREATE TABLESPACE should create this, or reject if it already exists.
        regards, tom lane

Gavin Sherry wrote:
> On Tue, 2 Mar 2004, Bruce Momjian wrote:
> 
> > Gavin Sherry wrote:
> > > Actually, I think that's a pretty good idea :-). I'd solves a bunch of
> > > issues in the backend (postmaster start up can recurse through
> > > $PGDATA/tablespaces looking for postmaster.pid files) and will also assist
> > > admins with complex configurations (perhaps).
> >
> > Why are you asking about postmaster.pid files.  That file goes in the
> > top level /data directory, no?
> 
> I was trying to be paranoid about users who have multiple postmasters on
> the same machine and want to share a table space while both systems are
> live. There'd be no mechanism to test for that situation if we didn't have
> something like a postmaster.pid file. Is this being a little too paranoid?

Oh, yikes, I see.  Right now we have the interlock on the /data
directory, but once you start moving stuff out from under /data using
tablespaces, we do perhaps loose the interlock.  However, I assume the
CREATE TABLESPACE is going to create the tablespace directory, so I
don't see how two postmasters could both create the directory.

For example, if you say 
CREATE TABLESPACE tb IN '/var/tb1'

I assume you have to create:
/var/tb1/pgsql_tablespace

and then
/var/tb1/pgsql_tablespace/oid1/var/tb1/pgsql_tablespace/oid2

or something like that, and set the proper permissions on
pgsql_tablespace.  We will have write permission on the directory they
pass to us, but we might not have permissions to change the mode of the
directory they pass, so we have to create a subdirectory anyway, and
that is our interlock.

For example:# run as root$ chmod a+w .$ ls -ld .drwxrwxrwx  2 root  wheel  512 Mar  2 23:51 .# run as the postmaster$
mkdirnew$ ls -ld newdrwxr-xr-x  2 postgres  wheel  512 Mar  2 23:52 new$ chmod 700 new$ chmod 700 .chmod: .: Operation
notpermittedchmod: .: Operation not permitted
 

As you can see, I have permission to create the /new directory, but no
ability to set its mode, so we have to create a directory that matches
the permissions of /data:
drwx------  6 postgres  postgres  512 Mar  2 12:48 /u/pg/data/


We could require the admin to create a directory that we own instead of
just one that we have write permission in, but why bother when we can
use the new directory as an interlock from multiple postmasters anyway.

Right now we do require the directory used as /data be one where we can
create a /data subdirectory, so this seems similar.  We don't put the
data directly in the passed directory, but in /data under that.  In
fact, we could just call it /var/tb1/data instead of
/var/tb1/pgsql_tablespace.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
>>Maybe specify an archive location (that of course could be on a separate 
>>partition) that the external archiver should check in addition to the 
>>normal WAL location. At some predetermined interval, push WAL log 
>>segments no longer needed to the archive location.
> 
> Does that really help?  The panic happens when you fill the "normal" and
> "archive" partitions, how's that different from one partition?

I see your point. But it would allow you to use a relatively modest 
local partition for WAL segments, while you might be using a 1TB netapp 
tray over NFS for the archive segments. I guess if the archive partition 
fills up, I would err on the side of dropping archive segments on the 
floor. That would mean a new full backup would be needed, but at least 
it wouldn't result in a corrupt, or shut down, database.

Joe

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> For tablespaces on OS's that don't support it, I think we will have to
> store the path name in the file and read it via the backend.  Somehow we
> should cache those lookups.

My feeling is that we need not support tablespaces on OS's without
symlinks.

            regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > For tablespaces on OS's that don't support it, I think we will have to
> > store the path name in the file and read it via the backend.  Somehow we
> > should cache those lookups.
>
> My feeling is that we need not support tablespaces on OS's without
> symlinks.

Agreed, but are we going to support non-tablespace installs?  I wasn't
sure that was an option.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Wed, 3 Mar 2004, Tom Lane wrote:

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > For tablespaces on OS's that don't support it, I think we will have to
> > store the path name in the file and read it via the backend.  Somehow we
> > should cache those lookups.
>
> My feeling is that we need not support tablespaces on OS's without
> symlinks.

I'm going to focus on implementing this on the system(s) I'm used to
developing on (ie, those which support symlinks). Once that is done, I'll
talk with the Win32 guys about what, if anything, we can do about getting
this to work on Win32 (and possibly other non-symlink supporting OSs).

Thanks,

Gavin

Joe Conway <mail@joeconway.com> writes:
> Tom Lane wrote:
>> Joe Conway <mail@joeconway.com> writes:
>>> Maybe specify an archive location (that of course could be on a separate 
>>> partition) that the external archiver should check in addition to the 
>>> normal WAL location. At some predetermined interval, push WAL log 
>>> segments no longer needed to the archive location.
>> 
>> Does that really help?  The panic happens when you fill the "normal" and
>> "archive" partitions, how's that different from one partition?

> I see your point. But it would allow you to use a relatively modest 
> local partition for WAL segments, while you might be using a 1TB netapp 
> tray over NFS for the archive segments.

Fair enough, but it seems to me that that sort of setup really falls in
the category of a user-defined archiving process --- that is, the hook
that Postgres calls will push WAL segments from the local partition to
the NFS server, and then pushing them off NFS to tape is the
responsibility of some other user-defined subprocess.  Database panic
happens if and only if the local partition overflows.  I don't see that
making Postgres explicitly aware of the secondary NFS arrangement will
buy anything.

> I guess if the archive partition fills up, I would err on the side of
> dropping archive segments on the floor.

That should be user-scriptable policy, in my worldview.

We haven't yet talked much about what the WAL-segment-archiving API
should look like, but if it cannot support implementing the above kind
of arrangement outside the database, then we've dropped the ball.
IMHO anyway.
        regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> My feeling is that we need not support tablespaces on OS's without
>> symlinks.

> Agreed, but are we going to support non-tablespace installs?  I wasn't
> sure that was an option.

A setup containing only the default tablespace cannot use any symlinks.
That doesn't seem hard though.

            regards, tom lane

Bruce Momjian writes:
> I just checked from the MinGW console and I see:
> [snip]
> It accepts ln -s, but does nothing with it.

And even if it had worked, it wouldn't really matter, since we don't
actually want to *run* the system under MinGW/msys, just build it.

I think the idea of implementing in symlinks for non-compliant platforms in
md.c has some merit. FWIW, looks like that is how cygwin implements
symlinks...

Cheers,
Claudio

---
Certain disclaimers and policies apply to all email sent from Memetrics.
For the full text of these disclaimers and policies see
<a
href="http://www.memetrics.com/emailpolicy.html">http://www.memetrics.com/em
ailpolicy.html</a>

On Wed, 2004-03-03 at 04:59, Tom Lane wrote:
>    What might make sense is some sort of marker file in a
> tablespace directory that links back to the owning $PGDATA directory.
> CREATE TABLESPACE should create this, or reject if it already exists.

It will not be enough for the marker to list the path of the parent
$PGDATA, since that path might get changed by system administration
action.  The marker should contain some sort of unique string which
would match the same string somewhere in $PGDATA.  Then, if either
tablespace or $PGDATA were moved, it would be possible to tie the two
back together.  It wouldn't be an issue on most normal systems, but
might be of crucial importance for an ISP running numerous separate
clusters.
-- 
Oliver Elphick <olly@lfix.co.uk>
LFIX Ltd

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> My feeling is that we need not support tablespaces on OS's without
> >> symlinks.
>
> > Agreed, but are we going to support non-tablespace installs?  I wasn't
> > sure that was an option.
>
> A setup containing only the default tablespace cannot use any symlinks.
> That doesn't seem hard though.

Yea, I think you are right.  We just disable CREATE TABLESPACE and the
rest should just work.  Basically, pg_tablespace will only have one
entry on those platforms.  The initdb directory structure will have a
single tablespace, but that doesn't use symlinks.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Claudio Natoli wrote:
>
> Bruce Momjian writes:
> > I just checked from the MinGW console and I see:
> > [snip]
> > It accepts ln -s, but does nothing with it.
>
> And even if it had worked, it wouldn't really matter, since we don't
> actually want to *run* the system under MinGW/msys, just build it.
>
> I think the idea of implementing in symlinks for non-compliant platforms in
> md.c has some merit. FWIW, looks like that is how cygwin implements
> symlinks...

Why can't we use MS Win32 shortcut files to simulate symlinks?  MinGW
doesn't do it, so I suppose it isn't possible.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Oliver Elphick <olly@lfix.co.uk> writes:
> It will not be enough for the marker to list the path of the parent
> $PGDATA, since that path might get changed by system administration
> action.  The marker should contain some sort of unique string which
> would match the same string somewhere in $PGDATA.

We have already added a notion of a "unique installation identifier"
for PITR purposes (look in pg_control).  So we could use that for this
purpose if we wanted to.

But I'm not sure how important it really is.  AFAICS the behavior of
CREATE TABLESPACE will be "create marker file, if it already exists
then abort".  It has no need to actually look in the file and so there's
no need for the contents to be unique.
        regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

> Greg Stark wrote:
> > 
> > > > I am expecting to hear some bleating about this from people whose
> > > > preferred platforms don't support symlinks ;-).  However, if we don't
> > 
> > Well, one option would be to have the low level filesystem storage (md.c?)
> > routines implement a kind of symlink themselves. Just a file with a special
> > magic number followed by a path.

On further contemplation it doesn't seem like using symlinks really ought to
be necessary. It should be possible to drive everything off the catalog tables
while avoidin having the low level filesystem code know anything about them.

Instead of having the low level code fetch the pg_* records themselves, some
piece of higher level code would do the query and call down to storage layer
to inform it of the locations for everything. It would have to do this on
database initialization and on any subsequent object creation.

Basically maintain an in-memory hash table of oid -> path, and call down to
the low level code whenever that hash changes. (Or more likely oid->ts_id and
a separate list of ts_id -> path.)

-- 
greg

Oliver Elphick wrote:
> On Wed, 2004-03-03 at 04:59, Tom Lane wrote:
> 
>>   What might make sense is some sort of marker file in a
>>tablespace directory that links back to the owning $PGDATA directory.
>>CREATE TABLESPACE should create this, or reject if it already exists.
> 
> 
> It will not be enough for the marker to list the path of the parent
> $PGDATA, since that path might get changed by system administration
> action.  The marker should contain some sort of unique string which
> would match the same string somewhere in $PGDATA.  Then, if either
> tablespace or $PGDATA were moved, it would be possible to tie the two
> back together.  It wouldn't be an issue on most normal systems, but
> might be of crucial importance for an ISP running numerous separate
> clusters.

Taking this one step further would be to do something like Oracle does.  Every datafile in Oracle (because the Oracle
storagemanager stores 
 
multiple objects inside datafiles, one could say there is some 
similarity between Oracle datafiles and the proposed pg tablespaces), 
has meta info that tells it which database instance it belongs to and 
the last checkpoint that occured (It might actually be more granular 
than checkpoint, such that on a clean shutdown you can tell that all 
datafiles are consistent with each other and form a consistent database 
instance).  So Oracle on every checkpoint updates all datafiles with an 
identifier.  Now you might ask why is this useful.  Well in normal day 
to day operation it isn't, but it can be usefull in disaster recovery. 
If you loose a disk and need to restore the entire database from backups 
it can be difficult to make sure you have done it all correctly (do I 
have all the necessary files/directories? did I get the right ones from 
the right tapes?)  Especially if you have directories spread across 
various different disks that might be backed up to different tapes.  So 
by having additional information stored in each datafile Oracle can 
provide additional checks that the set of files that are being used when 
the database starts up are consistent and all belong together.  Oracle 
also ensures that all the datafiles that are suposed to exist actually 
do as well.

So what might this mean for postgres and tablespaces?  It could mean 
that on startup the database checks to verify that all the tablespaces 
that are registered actually exist.  And that the data in each 
tablespace is consistent with the current WAL status.  (i.e. someone 
didn't restore a tablespace from backup while the database was down that  is old and needs recovery.

A lot of what I am talking about here become PITR issues.  But since 
PITR and tablespaces are both potential features for 7.5, how they 
interact probably should be thought about in the designs for each.

thanks,
--Barry

Greg Stark wrote:
> 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> 
> > Greg Stark wrote:
> > > 
> > > > > I am expecting to hear some bleating about this from people whose
> > > > > preferred platforms don't support symlinks ;-).  However, if we don't
> > > 
> > > Well, one option would be to have the low level filesystem storage (md.c?)
> > > routines implement a kind of symlink themselves. Just a file with a special
> > > magic number followed by a path.
> 
> On further contemplation it doesn't seem like using symlinks really ought to
> be necessary. It should be possible to drive everything off the catalog tables
> while avoidin having the low level filesystem code know anything about them.
> 
> Instead of having the low level code fetch the pg_* records themselves, some
> piece of higher level code would do the query and call down to storage layer
> to inform it of the locations for everything. It would have to do this on
> database initialization and on any subsequent object creation.
> 
> Basically maintain an in-memory hash table of oid -> path, and call down to
> the low level code whenever that hash changes. (Or more likely oid->ts_id and
> a separate list of ts_id -> path.)

The advantage of symlinks is that an administrator could see how things
are laid out from the command line.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

>Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Joe Conway <mail@joeconway.com> writes:
> > Tom Lane wrote:
> >> Joe Conway <mail@joeconway.com> writes:
> >>> Maybe specify an archive location (that of course could be on a
> separate
> >>> partition) that the external archiver should check in addition to
the
> >>> normal WAL location. At some predetermined interval, push WAL log
> >>> segments no longer needed to the archive location.
> >>
> >> Does that really help?  The panic happens when you fill the
"normal"
> and
> >> "archive" partitions, how's that different from one partition?
> 
> > I see your point. But it would allow you to use a relatively modest
> > local partition for WAL segments, while you might be using a 1TB
netapp
> > tray over NFS for the archive segments.
> 
> Fair enough, but it seems to me that that sort of setup really falls
in
> the category of a user-defined archiving process --- that is, the hook
> that Postgres calls will push WAL segments from the local partition to
> the NFS server, and then pushing them off NFS to tape is the
> responsibility of some other user-defined subprocess.  Database panic
> happens if and only if the local partition overflows.  I don't see
that
> making Postgres explicitly aware of the secondary NFS arrangement will
> buy anything.

Tom's last sentence there summarises the design I was working with. I
had considered Joe's suggested approach (which was Oracle's also).

However, the PITR design will come with a usable low-function program
which can easily copy logs from pg_xlog to another archive directory.
That's needed as a test harness anyway, so it may as well be part of the
package. You'd be able to use that in production to copy xlogs to
another larger directory as a staging area to tape/failover on another
system: effectively Joe's idea is catered for in the basic package.

Anyway I'm answering questions before publishing the design as
stands...though people do keep spurring me to refine it as I'm writing
it down! That's why its good to document it I guess.
> > I guess if the archive partition fills up, I would err on the side
of
> > dropping archive segments on the floor.
> 
> That should be user-scriptable policy, in my worldview.

Hmmm. Very difficult that one.

My experience is in commercial systems. Dropping archive segments on the
floor is just absolutely NOT GOOD, if that is the only behaviour. The
whole purpose of having a dbms is so that you can protect your business
data, while using it. Such behaviour would most likely be a barrier to
wider commercial adoption. [Oracle and other dbms will freeze when this
situation is hit, rather than continue and drop archive logs.]
User-selectable behaviour? OK. That's how we deal with fsync; I can
relate to that. That hadn't been part of my thinking because of the
importance I'd attached to the log files themselves, but I can go with
that, if that's what was meant.

So, if we had a parameter called Wal_archive_policy that has 3 settings:
None = no archiving
Optimistic = archive, but if for some reason log space runs out then
make space by dropping the oldest archive logs 
Strict = if log space runs out, stop further write transactions from
committing, by whatever means, even if this takes down dbms.

That way, we've got something akin to transaction isolation level with
various levels of protection.

Best Regards, Simon Riggs

Joe Conway <mail@joeconway.com> writes:
> Simon Riggs wrote:
>> O... and other dbms will freeze when this situation is hit, rather
>> than continue and drop archive logs.]

> Been there, done that, don't see how it's any better. I hesitate to be
> real specific here, but let's just say the end result was restore from 
> backup :-(

It's hard for me to imagine a situation in which killing the database
would be considered a more attractive option than dropping old log
data.  You may or may not ever need the old log data, but you darn well
do need a functioning database.  (If you don't, you wouldn't be going to
all this work.)

I think also that Simon completely misunderstood my intent in saying
that this could be "user-scriptable policy".  By that I meant that the
*user* could write the code to behave whichever way he liked.  Not that
we were going to go into a mad rush of feature invention and try to
support every combination we could think of.  I repeat: code that pushes
logs into a secondary area is not ours to write.  We should concentrate
on providing an API that lets users write it.  We have only limited
manpower for this project and we need to spend it on getting the core
functionality done right, not on inventing frammishes.
        regards, tom lane

Simon Riggs wrote:
> User-selectable behaviour? OK. That's how we deal with fsync; I can
> relate to that. That hadn't been part of my thinking because of the
> importance I'd attached to the log files themselves, but I can go with
> that, if that's what was meant.
> 
> So, if we had a parameter called Wal_archive_policy that has 3 settings:
> None = no archiving
> Optimistic = archive, but if for some reason log space runs out then
> make space by dropping the oldest archive logs 
> Strict = if log space runs out, stop further write transactions from
> committing, by whatever means, even if this takes down dbms.
> 
> That way, we've got something akin to transaction isolation level with
> various levels of protection.

Yep, we will definately need something like that.  Basically whenever
the logs are being archived, you have to stop the database if you can't
archive, no?

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

Simon Riggs wrote:
>> Tom Lane [mailto:tgl@sss.pgh.pa.us] That should be user-scriptable
>> policy, in my worldview.

> O... and other dbms will freeze when this situation is hit, rather
> than continue and drop archive logs.]

Been there, done that, don't see how it's any better. I hesitate to be
real specific here, but let's just say the end result was restore from 
backup :-(

> So, if we had a parameter called Wal_archive_policy that has 3
> settings: None = no archiving Optimistic = archive, but if for some
> reason log space runs out then make space by dropping the oldest
> archive logs Strict = if log space runs out, stop further write
> transactions from committing, by whatever means, even if this takes
> down dbms.

That sounds good to me. For the "Optimistic" case, we need to yell 
loudly if we do find ourselves needing to drop segments. For the 
"Strict" case, we just need to be sure it works correctly ;-)

Joe

Bruce Momjian wrote:

>Greg Stark wrote:
>  
>
>>Bruce Momjian <pgman@candle.pha.pa.us> writes:
>>
>>    
>>
>>>Greg Stark wrote:
>>>      
>>>
>>>>>>I am expecting to hear some bleating about this from people whose
>>>>>>preferred platforms don't support symlinks ;-).  However, if we don't
>>>>>>            
>>>>>>
>>>>Well, one option would be to have the low level filesystem storage (md.c?)
>>>>routines implement a kind of symlink themselves. Just a file with a special
>>>>magic number followed by a path.
>>>>        
>>>>
>>On further contemplation it doesn't seem like using symlinks really ought to
>>be necessary. It should be possible to drive everything off the catalog tables
>>while avoidin having the low level filesystem code know anything about them.
>>
>>Instead of having the low level code fetch the pg_* records themselves, some
>>piece of higher level code would do the query and call down to storage layer
>>to inform it of the locations for everything. It would have to do this on
>>database initialization and on any subsequent object creation.
>>
>>Basically maintain an in-memory hash table of oid -> path, and call down to
>>the low level code whenever that hash changes. (Or more likely oid->ts_id and
>>a separate list of ts_id -> path.)
>>    
>>
>
>The advantage of symlinks is that an administrator could see how things
>are laid out from the command line.
>
>  
>
That's a poor reason to require symlinks.  The administrator can just as
easily open up psql and query pg_tablespace to see that same
information.  Besides, the postgres doesn't know where to look on the
filesystem for the /path/to/oid without a system catalog lookup.  There
doesn't seem to be any sensible reason to force a filesystem requirement
when the core operations are diffferent to begin with. 

If a more global view of all databases is necessary, perhaps there ought
to be a system wide view which could display all of that information at
once: dbname, relation name, and physical location.

Thomas Swan wrote:
> >The advantage of symlinks is that an administrator could see how things
> >are laid out from the command line.
> >
> >  
> >
> That's a poor reason to require symlinks.  The administrator can just as
> easily open up psql and query pg_tablespace to see that same
> information.  Besides, the postgres doesn't know where to look on the
> filesystem for the /path/to/oid without a system catalog lookup.  There
> doesn't seem to be any sensible reason to force a filesystem requirement
> when the core operations are diffferent to begin with. 
> 
> If a more global view of all databases is necessary, perhaps there ought
> to be a system wide view which could display all of that information at
> once: dbname, relation name, and physical location.

Who doesn't have symlinks these days, and is going to be using
tablespaces?  Even Win32 has them.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

Thomas Swan <tswan@idigx.com> writes:
> Bruce Momjian wrote:
>> The advantage of symlinks is that an administrator could see how things
>> are laid out from the command line.
>> 
> That's a poor reason to require symlinks.  The administrator can just as
> easily open up psql and query pg_tablespace to see that same
> information.

Something to keep in mind here is that one of the times you would most
likely need that information is when the database is broken and you
*can't* simply "open up psql" and inspect system catalogs.  I like the
fact that a symlink implementation can be inspected without depending on
a working database.

If we were going to build a non-symlink implementation, I'd want the
highlevel-to-lowlevel data transfer to take the form of a flat ASCII
file that could be inspected by hand, rather than some hidden in-memory
datastructure.  But given the previous discussion in this thread,
I cannot see any strong reason not to rely on symlinks for the purpose.
We are not in the business of building replacements for OS features.
        regards, tom lane

Tom Lane wrote:

>Thomas Swan <tswan@idigx.com> writes:
>  
>
>>Bruce Momjian wrote:
>>    
>>
>>>The advantage of symlinks is that an administrator could see how things
>>>are laid out from the command line.
>>>
>>>      
>>>
>>That's a poor reason to require symlinks.  The administrator can just as
>>easily open up psql and query pg_tablespace to see that same
>>information.
>>    
>>
>
>Something to keep in mind here is that one of the times you would most
>likely need that information is when the database is broken and you
>*can't* simply "open up psql" and inspect system catalogs.  I like the
>fact that a symlink implementation can be inspected without depending on
>a working database.
>
>  
>
That's a sufficient argument, to allow for it.  Recoverability would be
one reason.

>If we were going to build a non-symlink implementation, I'd want the
>highlevel-to-lowlevel data transfer to take the form of a flat ASCII
>file that could be inspected by hand, rather than some hidden in-memory
>datastructure.  But given the previous discussion in this thread,
>I cannot see any strong reason not to rely on symlinks for the purpose.
>We are not in the business of building replacements for OS features.
>
>  
>
I do like the flat file output at least for a record of what went
where.  Regardless of whether or not symlinks are used, the admin would
need to know what directories/files/filesystems are to be backed up.

I am concerned as to what extent different filesystems do when you back
the directories up.    Would NTFS containing symlinks be able to be
backed up with a tar/zip command, or is something more elaborate needed? In the past, before upgrading, I have had to
tarthe pgdata directory
 
with the postmaster shutdown to insure a quick restoration of the
database in case an upgrade didn't proceed uneventfully.  Also, in the
event of a major version upgrade the restored information may or may not
proceed uneventfully.    I just wanted to point out something I thought
might be an issue further down the road.  

Perhaps the system catalog / flat file approach would be a more solid
approach, both of which would not involve replacing or duplicating OS
features.

Tom Lane wrote:
> I think also that Simon completely misunderstood my intent in saying
> that this could be "user-scriptable policy".  By that I meant that the
> *user* could write the code to behave whichever way he liked.  Not that
> we were going to go into a mad rush of feature invention and try to
> support every combination we could think of.  I repeat: code that pushes
> logs into a secondary area is not ours to write.  We should concentrate
> on providing an API that lets users write it.  We have only limited
> manpower for this project and we need to spend it on getting the core
> functionality done right, not on inventing frammishes.

Hmm... I totally agree. I think the backend could just offer a shared memory 
segment and a marker message to another process to allow copy from it. then it 
is the applications business to do the things.

Of course there has to be a two way agreement about it but an API is a real nice 
thing rather than an application.
 Shridhar

On 3 March 2004, at 19:52, Bruce Momjian wrote:
> The advantage of symlinks is that an administrator could see how things
> are laid out from the command line.

One thing to keep in mind is that system administrators don't see
symlinks as being informational -- they see them as the actual UI
for the redirection in question. So their expectation is that they'll
be able to move the actual directory around at will (as long as they
update the symlink to match).

If symlinks are used, the rule of least surprise would mean that
no information whatsoever about the physical location of a tablespace
should be stored in the system catalogs. Otherwise their relationship
with the information stored in the symlink is ambiguous.

mk

On Mon, Mar 08, 2004 at 02:07:35AM +0200, Marko Karppinen wrote:
> One thing to keep in mind is that system administrators don't see
> symlinks as being informational -- they see them as the actual UI
> for the redirection in question. So their expectation is that they'll
> be able to move the actual directory around at will (as long as they
> update the symlink to match).

This is a good point.  It's worth keeping in mind, too, that in large
shops, the DBAs and the sysadmins often are in separate departments
with separate management, precisely because the database system has
traditionally been somewhat divorced from the OS (as an aside, I
suspect that this sort of separation is part of the reason for the
popularity of raw filesystems among DBAs.  Even if they didn't
provide better speed, it's just preferable not to have to involve
another department).  System administrators in such places have been
known to decide to "reorganise the disks", assuming that the database
just has its own home.  For such a sysadmin, a pile of symlinks would
be fair game for reorganisation.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca

Andrew Sullivan wrote:

>eorganise the disks", assuming that the database
>just has its own home.  For such a sysadmin, a pile of symlinks would
>be fair game for reorganisation.
>  
>

Please take into consideration that symlinks might be every day work for 
*nix admins, but for win admins it's very uncommon. Additionally, win 
admins are accustomed to gui tools, and many of them will stumble if 
forced to use a command line. For worse, junctions are not 
distinguishable in explorer; only the shell's dir command knows about 
junctions. This obfuscation makes junctions quite a bad choice for admin 
purposes.

IMHO there are only two viable options:
- no tablespaces for win32, i.e. recommend *ix for bigger installations
- a different tablespace storage approach., e.g. simple desktop links 
(or alike) redirecting to a directory.


Regards,
Andreas

Andrew Sullivan wrote:
> On Mon, Mar 08, 2004 at 02:07:35AM +0200, Marko Karppinen wrote:
> > One thing to keep in mind is that system administrators don't see
> > symlinks as being informational -- they see them as the actual UI
> > for the redirection in question. So their expectation is that they'll
> > be able to move the actual directory around at will (as long as they
> > update the symlink to match).
> 
> This is a good point.  It's worth keeping in mind, too, that in large
> shops, the DBAs and the sysadmins often are in separate departments
> with separate management, precisely because the database system has
> traditionally been somewhat divorced from the OS (as an aside, I
> suspect that this sort of separation is part of the reason for the
> popularity of raw filesystems among DBAs.  Even if they didn't
> provide better speed, it's just preferable not to have to involve
> another department).  System administrators in such places have been
> known to decide to "reorganise the disks", assuming that the database
> just has its own home.  For such a sysadmin, a pile of symlinks would
> be fair game for reorganisation.

Agreed.  I think the idea is to use lstat to query the symlink, rather
than storing that information in the database.  My idea was to create an
lstat server-side function that could be used by pg_dump and friends.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

>Joe Conway [mailto:mail@joeconway.com]
> Simon Riggs wrote:
> >> Tom Lane [mailto:tgl@sss.pgh.pa.us] That should be user-scriptable
> >> policy, in my worldview.
> 
> > O... and other dbms will freeze when this situation is hit, rather
> > than continue and drop archive logs.]
> 
> Been there, done that, don't see how it's any better. I hesitate to be
> real specific here, but let's just say the end result was restore from
> backup :-(
> 
> > So, if we had a parameter called Wal_archive_policy that has 3
> > settings: None = no archiving Optimistic = archive, but if for some
> > reason log space runs out then make space by dropping the oldest
> > archive logs Strict = if log space runs out, stop further write
> > transactions from committing, by whatever means, even if this takes
> > down dbms.
> 
> That sounds good to me. For the "Optimistic" case, we need to yell
> loudly if we do find ourselves needing to drop segments. For the
> "Strict" case, we just need to be sure it works correctly ;-)

Good.

Yell loudly really needs to happen sometime earlier, which is as Gavin
originally thought something to do with tablespaces.

Strict behaviour is fairly straightforward, you just PANIC!

I'd think we could rename these to
Fail Operational rather than Optimistic
Fail Safe rather than Strict
...the other names were a bit like "I'm right" and "but I'll do yours
too" ;}

Best Regards, Simon Riggs

>Bruce Momjian
> Simon Riggs wrote:
> > User-selectable behaviour? OK. That's how we deal with fsync; I can
> > relate to that. That hadn't been part of my thinking because of the
> > importance I'd attached to the log files themselves, but I can go
with
> > that, if that's what was meant.
> >
> > So, if we had a parameter called Wal_archive_policy that has 3
settings:
> > None = no archiving
> > Optimistic = archive, but if for some reason log space runs out then
> > make space by dropping the oldest archive logs
> > Strict = if log space runs out, stop further write transactions from
> > committing, by whatever means, even if this takes down dbms.
> >
> > That way, we've got something akin to transaction isolation level
with
> > various levels of protection.
> 
> Yep, we will definately need something like that.  Basically whenever
> the logs are being archived, you have to stop the database if you
can't
> archive, no?

That certainly was my initial feeling, though I believe it is possible
to accommodate both viewpoints. I would not want to have only the
alternative viewpoint, I must confess.

Best Regards, Simon Riggs

Please excuse the delay in replying..

>Tom Lane
> Joe Conway <mail@joeconway.com> writes:
> > Simon Riggs wrote:
> >> O... and other dbms will freeze when this situation is hit, rather
> >> than continue and drop archive logs.]
> 
> > Been there, done that, don't see how it's any better. I hesitate to
be
> > real specific here, but let's just say the end result was restore
from
> > backup :-(

Myself also. I accept your experience and insight, I apologise if my own
seemed overblown. My take on that is that if you're in a situation that
has a high probability of going bad, the last thing you would want is to
drop xlogs. Same technical experience, different viewpoint on what to
learn from it. 

> It's hard for me to imagine a situation in which killing the database
> would be considered a more attractive option than dropping old log
> data.  You may or may not ever need the old log data, but you darn
well
> do need a functioning database.  (If you don't, you wouldn't be going
to
> all this work.)

The main point here for me is that the choice of keeping archived (not
old) log files against keeping the database up isn't actually mine to
make; that choice belongs to the owner of the database, not me as
developer or administrator, consultant or whatever. 

Although I admit I did not at first comprehend that such a view was
possible, I did flex to allow yours and Joe's perspective when that was
voiced.

The point is one of risk: does the owner wish to risk the possibility
that a transaction may be lost in order to keep the database up? The
possibility of lost rows must be balanced against the probably higher
possibility of being unable to write new data. But which is worse? Who
can say?

In some environments where I have worked, (again forgive any seeming
personal arrogance or posturing), such as banks or finance generally, it
has been desirable to stop the system rather than risk losing even a
single row. In other situations, lost rows must be balanced against the
money lost through downtime. Guess it depends whether you've got a
contract for uptime or for data integrity?? ;)

> I repeat: code that pushes
> logs into a secondary area is not ours to write.  We should
concentrate
> on providing an API that lets users write it.  

Agreed.

> We have only limited
> manpower for this project and we need to spend it on getting the core
> functionality done right, not on inventing frammishes.

Love that word "frammish"...seriously, I understand and agree.

My understanding is that existing logic will cause a PANIC if the xlog
directory cannot be written to. Helping the database stay up by dropping
logs would require extra code...

This was an edge case anyhow...

Best Regards, Simon Riggs

Simon Riggs wrote:
> >Bruce Momjian
> > Simon Riggs wrote:
> > > User-selectable behaviour? OK. That's how we deal with fsync; I can
> > > relate to that. That hadn't been part of my thinking because of the
> > > importance I'd attached to the log files themselves, but I can go
> with
> > > that, if that's what was meant.
> > >
> > > So, if we had a parameter called Wal_archive_policy that has 3
> settings:
> > > None = no archiving
> > > Optimistic = archive, but if for some reason log space runs out then
> > > make space by dropping the oldest archive logs
> > > Strict = if log space runs out, stop further write transactions from
> > > committing, by whatever means, even if this takes down dbms.
> > >
> > > That way, we've got something akin to transaction isolation level
> with
> > > various levels of protection.
> > 
> > Yep, we will definately need something like that.  Basically whenever
> > the logs are being archived, you have to stop the database if you
> can't
> > archive, no?
> 
> That certainly was my initial feeling, though I believe it is possible
> to accommodate both viewpoints. I would not want to have only the
> alternative viewpoint, I must confess.
> 

Added to PITR TODO list.  Anything else to add:
http://momjian.postgresql.org/main/writings/pgsql/project


--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 

"Simon Riggs" <simon@2ndquadrant.com> writes:

> Strict behaviour is fairly straightforward, you just PANIC!

There is another mode possible as well. Oracle for example neither panics nor
continues, it just freezes. It keeps retrying the transaction until it finds
it has space.  

The sysadmin or dba just has to somehow create additional space by removing
old files or however and the database will continue where it left off. That
seems a bit nicer than panicing.

When I first heard that I was shocked. It means implementing archive logs
*created* a new failure mode where there was none before. I thought that was
the dumbest idea in the world: who needed a backup process that increased the
chances of an outage? Now I can see the logic, but I'm still not sure which
mode I would pick if it was up to me. As others have said, I guess it would
depend on the situation.

-- 
greg

Dennis Bjorklund <db@zigo.dhs.org> writes:

> On Thu, 26 Feb 2004, Gavin Sherry wrote:
> 
> > Comments? Questions? Suggestions?
> 
> Is that plan that in the future one can split a single table into 
> different table spaces? Like storing all rows with year < 1999 in one 
> tablespace and the rest in another?

That's a separate orthogonal feature called "partitioned tables". There's some
amount of resistance to the idea amongst postgres people, and there's nobody
who has spoken up interested in implementing it, but there's also lots of
interest from users. A good patch would probably go a long way to convincing
people :)

Table spaces are being able to store different tables in different physical
locations on disk. A first version of this has actually been implemented for
7.5 using symlinks.

Partitioned tables and tablespaces do indeed have a certain amount of synergy.
But even in a single tablespace your example makes sense.

> With the rule system and two underlying tables one could make it work by 
> hand I think.

The rule system could be used to do this, but there was some discussion of
using inherited tables to handle it. However neither handles the really hard
part of detecting queries that use only a part of the table and taking that
into account in generating the plan.

-- 
greg

> > With the rule system and two underlying tables one could make it work by
> > hand I think.
>
> The rule system could be used to do this, but there was some discussion of
> using inherited tables to handle it. However neither handles the really hard
> part of detecting queries that use only a part of the table and taking that
> into account in generating the plan.

I think the consensus should be to add smarts to the planner to include
static constraint information to reduce table access.

e.g if you have a constraint "acol integer, check acol < 5"
and you have a query with a "where acol = 10" you could reduce that
to "where false". This would help in all sorts of situations not only
partitioned/inherited tables. I am not sure what the runtime cost of
such an inclusion would be, so maybe it needs smarts to only try in certain
cases ?

Andreas

"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:

> e.g if you have a constraint "acol integer, check acol < 5"
> and you have a query with a "where acol = 10" you could reduce that
> to "where false". 

I think part of the question is how much work do you put into checking this.
Checking constant known values like above is probably not too expensive.
Checking for ranges like "where acol between 5 and 10" is probably doable. And
that might be enough for partitioned tables. I think that's about all Oracle
bothers to check, for example. More complex where clauses and check
expressions might be hard to prove are true or false.

But then the work's still not done, you still have to add an optimization that
prunes members of a UNION ALL (or equivalent if it's done using inherited
tables or some other infrastructure) if they are known to provably produce
zero rows.

And then there are more subtle cases. Like if the query is "where acol = ?".
Then you know it only has to read one partition, but you don't know which one
at compile time. And it's important to handle that case because that might be
the only clause. So knowing that you only need one partition might be the
difference between a sequential scan of one partition, or an index scan of
many thousands of records because they're only a small percentage of the
entire table.

-- 
greg

> I don't think we want features for their own sake, though, and I'm
> not convinced that raw filesystems are actually useful.  Course, it's
> not my itch, and PostgreSQL _is_ free software.
>

I agree that raw file systems are seldom useful with one caveat, more
advanced file systems are sometimes detrimental to database access.

Conceptually, a file system and a database are redundant, both are doing
their best to preserve data integrity. This is especially true with
journalling file systems. Not to mention technologies like reiserfs which
attempts to do sub-block allocation.

What I think would go a long way to improving database performance on
non-raw partitions would be a simplified file system -- SFS anyone? The
simplified file system would not track access time. It would not overly
try to manage disk space. The target applications are going to allocate
disk space on a block level, rather than quibble about 4K here or 8K here,
have a user defined standard allocation unit of 64K, 128K, or so on.
Reduction on allocation overhead also reduces meta-data updating I/O. I
can almost imagine 32BIT FAT with large clusers, only with real inodes.
The idea would be that a database, like PostgreSQL, would be managing the
data not the file system. The file systems job would only to be the most
minimalist interface to the OS.

The benefts would be awesome, near-raw partition access and standard OS
tools for maintainence.