> BTW we are about to take in tablespace concept. You would
> need another information(the name of the symlink to a directory
> ,would be = tablespaceOID) for WAL logging.

Do we need *both* database & tablespace to find table file ?!
Imho, database shouldn't be used...

?

Vadim

Mon, 26 Jun 2000 11:25:50 -0700
"Mikheev, Vadim" <vmikheev@SECTORBASE.COM>
RE: Big 7.1 open items 
"Mikheev, Vadim" <vmikheev@SECTORBASE.COM> writes:
> Do we need *both* database & tablespace to find table file ?!
> Imho, database shouldn't be used...

That'd work fine for me, but I think Bruce was arguing for paths that
included the database name.  We'd end up with paths that go something
like
	..../data/tablespaces/TABLESPACEOID/RELATIONOID
(plus some kind of decoration for segment and version), so you'd have
a hard time telling which files in a tablespace belong to which
database.  Doesn't bother me a whole lot, personally --- if one wants
to know that one could just as well assign separate tablespaces to
different databases.  They're only subdirectories anyway...

			regards, tom lane

Mon, 26 Jun 2000 18:03:42 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Big 7.1 open items 
Mikheev, Vadim writes:

> Do we need *both* database & tablespace to find table file ?!
> Imho, database shouldn't be used...

Then the system tables from different databases would collide.


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


Tue, 27 Jun 2000 20:07:34 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
RE: Big 7.1 open items 
Peter Eisentraut <peter_e@gmx.net> writes:
> Mikheev, Vadim writes:
>> Do we need *both* database & tablespace to find table file ?!
>> Imho, database shouldn't be used...

> Then the system tables from different databases would collide.

I've been assuming that we would create a separate tablespace for
each database, which would be the location of that database's
system tables.  It's probably also the default tablespace for user
tables created in that database, though it wouldn't have to be.

There should also be a known tablespace for the installation-wide tables
(pg_shadow et al).

With this approach tablespace+relation would indeed be a sufficient
identifier.  We could even eliminate the knowledge that certain
tables are installation-wide from the bufmgr and below (currently
that knowledge is hardwired in places that I'd rather didn't know
about it...)

			regards, tom lane

Tue, 27 Jun 2000 17:00:11 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Big 7.1 open items 
> Peter Eisentraut <peter_e@gmx.net> writes:
> > Mikheev, Vadim writes:
> >> Do we need *both* database & tablespace to find table file ?!
> >> Imho, database shouldn't be used...
> 
> > Then the system tables from different databases would collide.
> 
> I've been assuming that we would create a separate tablespace for
> each database, which would be the location of that database's
> system tables.  It's probably also the default tablespace for user
> tables created in that database, though it wouldn't have to be.
> 
> There should also be a known tablespace for the installation-wide tables
> (pg_shadow et al).
> 
> With this approach tablespace+relation would indeed be a sufficient
> identifier.  We could even eliminate the knowledge that certain
> tables are installation-wide from the bufmgr and below (currently
> that knowledge is hardwired in places that I'd rather didn't know
> about it...)

Well, if we did that, I can see a good reason to not use per-database
directories in the tablepspace.

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

Tue, 27 Jun 2000 17:16:49 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Big 7.1 open items
Tom Lane writes:

> I've been assuming that we would create a separate tablespace for
> each database, which would be the location of that database's
> system tables.

Then I can't put more than one database into a table space? But I can put
more than one table space into a database? I think that's the wrong
hierarchy. More specifically, I think it's wrong that there is a hierarchy
here at all. Table spaces and databases don't have to know about each
other in any predefined way.


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


Wed, 28 Jun 2000 20:37:35 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Big 7.1 open items 
Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> I've been assuming that we would create a separate tablespace for
>> each database, which would be the location of that database's
>> system tables.

> Then I can't put more than one database into a table space? But I can put
> more than one table space into a database?

You can put *user* tables from more than one database into a table space.
The restriction is just on *system* tables.

Admittedly this is a tradeoff.  We could avoid it along the lines you
suggest (name table files like DBOID.RELOID.VERSION instead of just
RELOID.VERSION) but is it really worth it?  Vadim's concerned about
every byte that has to go into the WAL log, and I think he's got a
good point.

> I think that's the wrong
> hierarchy. More specifically, I think it's wrong that there is a hierarchy
> here at all. Table spaces and databases don't have to know about each
> other in any predefined way.

They don't, at least not at the smgr level.  In my view of how this
should work, the smgr *only* knows about tablespaces and tables.
Databases are a higher-level construct.

			regards, tom lane

Wed, 28 Jun 2000 21:05:02 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Big 7.1 open items 
Tom Lane writes:

> You can put *user* tables from more than one database into a table space.
> The restriction is just on *system* tables.

I think my understanding as a user would be that a table space represents
a storage location. If I want to put a table/object/entire database on a
fancy disk somewhere I create a table space for it there. But if I want to
store all my stuff under /usr/local/pgsql/data then I wouldn't expect to
have to create more than one table space. So the table spaces become at
that point affected by the logical hierarchy: I must make sure to have
enough table spaces to have many databases.

More specifically, what would the user interface to this look like?
Clearly there has to be some sort of CREATE TABLESPACE command. Now does
CREATE DATABASE imply a CREATE TABLESPACE? I think not. Do you have to
create a table space before creating each database? I think not.

> We could avoid it along the lines you suggest (name table files like
> DBOID.RELOID.VERSION instead of just RELOID.VERSION) but is it really
> worth it?

I only intended that for pg_class and other bootstrap-sort-of tables,
maybe all system tables. Normal heap files could look like RELOID.VERSION,
whereas system tables would look like "name.DBOID". Clearly there's no
market for renaming system tables or dropping any of their columns. We're
obviously going to have to treat pg_class special anyway.

> Vadim's concerned about every byte that has to go into the WAL log,
> and I think he's got a good point.

True. But if you only do it for the system tables then it might take less
space than keeping track of lots of table spaces that are unneeded. :-)


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



Fri, 30 Jun 2000 01:32:20 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Big 7.1 open items 
Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> You can put *user* tables from more than one database into a table space.
>> The restriction is just on *system* tables.

> More specifically, what would the user interface to this look like?
> Clearly there has to be some sort of CREATE TABLESPACE command. Now does
> CREATE DATABASE imply a CREATE TABLESPACE? I think not. Do you have to
> create a table space before creating each database? I think not.

I would say that CREATE DATABASE just implicitly creates a new
tablespace that's physically located right under the toplevel data
directory of the installation, no symlink.  What's wrong with that?
You need not keep anything except the system tables of the DB there
if you don't want to.  In practice, for someone who doesn't need to
worry about tablespaces (because they put the installation on a disk
with enough room for their purposes), the whole thing acts exactly
the same as it does now.

>> We could avoid it along the lines you suggest (name table files like
>> DBOID.RELOID.VERSION instead of just RELOID.VERSION) but is it really
>> worth it?

> I only intended that for pg_class and other bootstrap-sort-of tables,
> maybe all system tables. Normal heap files could look like RELOID.VERSION,
> whereas system tables would look like "name.DBOID".

That would imply that the very bottom levels of the system know all
about which tables are system tables and which are not (and, if you
are really going to insist on the "name" part of that, that they
know what name goes with each system-table OID).  I'd prefer to avoid
that.  The less the smgr knows about the upper levels of the system,
the better.

> Clearly there's no market for renaming system tables or dropping any
> of their columns.

No, but there is a market for compacting indexes on system relations,
and I haven't heard a good proposal for doing index compaction in place.
So we need versioning for system indexes.

>> Vadim's concerned about every byte that has to go into the WAL log,
>> and I think he's got a good point.

> True. But if you only do it for the system tables then it might take less
> space than keeping track of lots of table spaces that are unneeded. :-)

Again, WAL should not need to distinguish system and user tables.

And as for the keeping track, the tablespace OID will simply replace the
database OID in the log and in the smgr interfaces.  There's no "extra"
cost, except maybe by comparison to a system with neither tablespaces
nor multiple databases.

			regards, tom lane

Thu, 29 Jun 2000 20:43:32 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Big 7.1 open items 
Tom Lane writes:

> In practice, for someone who doesn't need to worry about tablespaces
> (because they put the installation on a disk with enough room for
> their purposes), the whole thing acts exactly the same as it does now.

But I'd venture the guess that for someone who wants to use tablespaces it
wouldn't work as expected. Table spaces should represent a physical
storage location. Creation of table spaces should be a restricted
operation, possibly more than, but at least differently from, databases.
Eventually, table spaces probably will have attributes, such as
optimization parameters (random_page_cost). This will not work as expected
if you intermix them with the databases.

I'd expect that if I have three disks and 50 databases, then I make three
tablespaces and assign the databases to them. I'll bet lunch that if we
don't do it that way that before long people will come along and ask for
something that does work this way.


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


Sat, 1 Jul 2000 17:03:42 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Big 7.1 open items 
Peter Eisentraut <peter_e@gmx.net> writes:
> I'd expect that if I have three disks and 50 databases, then I make three
> tablespaces and assign the databases to them.

In our last installment, you were complaining that you didn't want to
be bothered with that ;-)

But I don't see any reason why CREATE DATABASE couldn't take optional
parameters indicating where to create the new DB's default tablespace.
We already have a LOCATION option for it that does something close to
that.

Come to think of it, it would probably make sense to adapt the existing
notion of "location" (cf initlocation script) into something meaning
"directory that users are allowed to create tablespaces (including
databases) in".  If there were an explicit table of allowed locations,
it could be used to address the protection issues you raise --- for
example, a location could be restricted so that only some users could
create tablespaces/databases in it.  $PGDATA/data would be just the
first location in every installation.

			regards, tom lane

Sat, 01 Jul 2000 13:37:21 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Big 7.1 open items 
Tom Lane writes:

> Come to think of it, it would probably make sense to adapt the existing
> notion of "location" (cf initlocation script) into something meaning
> "directory that users are allowed to create tablespaces (including
> databases) in".

This is what I've been trying to push all along. But note that this
mechanism does allow multiple databases per location. :)


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


Sun, 2 Jul 2000 17:22:43 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Big 7.1 open items 