Big 7.1 open items

On Tue, 13 Jun 2000, Bruce Momjian wrote:

Here is the list I have gotten of open 7.1 items:

bit type
inheritance
drop column
vacuum index speed
cached query plans

^^^^^^^^^^^^^^^^^

I have already down it and I send patch for _testing_ next week (or
later), but I think that not will for 7.1, but 7.2.

memory context cleanup
TOAST
WAL
fmgr redesign
encrypt pg_shadow passwords
redesign pg_hba.conf password file option
new location for config files

+ new ACL? (please :-)

BTW. --- really cool list.

Karel

On Tue, 13 Jun 2000, Bruce Momjian wrote:

Here is the list I have gotten of open 7.1 items:

encrypt pg_shadow passwords

This will be for 7.1? For some reason I thought it was being pushed
off to 7.2.

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================

Bruce Momjian <pgman@candle.pha.pa.us> el d�a Tue, 13 Jun 2000 05:05:53
-0400 (EDT), escribi�:

[...]

new location for config files

can I suggest /etc/postgresql ?

sergio

On Tue, 13 Jun 2000, Bruce Momjian wrote:

Here is the list I have gotten of open 7.1 items:

bit type
inheritance
drop column
vacuum index speed
cached query plans
memory context cleanup
TOAST
WAL
fmgr redesign
encrypt pg_shadow passwords
redesign pg_hba.conf password file option

Any details?

new location for config files

Are you referring to pushing internal files to `$PGDATA/global'?

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

On Tue, 13 Jun 2000, Sergio A. Kessler wrote:

Bruce Momjian <pgman@candle.pha.pa.us> el d���a Tue, 13 Jun 2000 05:05:53
-0400 (EDT), escribi���:

[...]

new location for config files

can I suggest /etc/postgresql ?

you can ... but everything related to postgresql has always been designed
not to require any special permissions to install, and /etc/postgresql
would definitely require root access to install :(

On Tue, 13 Jun 2000, The Hermit Hacker wrote:

On Tue, 13 Jun 2000, Sergio A. Kessler wrote:

Bruce Momjian <pgman@candle.pha.pa.us> el d���a Tue, 13 Jun 2000 05:05:53
-0400 (EDT), escribi���:

[...]

new location for config files

can I suggest /etc/postgresql ?

you can ... but everything related to postgresql has always been designed
not to require any special permissions to install, and /etc/postgresql
would definitely require root access to install :(

~postgres/etc ??

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================

On Tue, 13 Jun 2000, Vince Vielhaber wrote:

new location for config files

can I suggest /etc/postgresql ?

you can ... but everything related to postgresql has always been designed
not to require any special permissions to install, and /etc/postgresql
would definitely require root access to install :(

~postgres/etc ??

You need root access to create a postgres user. What's wrong with just
keeping it in $PGDATA and making symlinks whereever you would prefer it?

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

that one works ...

On Tue, 13 Jun 2000, Vince Vielhaber wrote:

On Tue, 13 Jun 2000, The Hermit Hacker wrote:

On Tue, 13 Jun 2000, Sergio A. Kessler wrote:

Bruce Momjian <pgman@candle.pha.pa.us> el d���a Tue, 13 Jun 2000 05:05:53
-0400 (EDT), escribi���:

[...]

new location for config files

can I suggest /etc/postgresql ?

you can ... but everything related to postgresql has always been designed
not to require any special permissions to install, and /etc/postgresql
would definitely require root access to install :(

~postgres/etc ??

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org

Vince Vielhaber <vev@michvhf.com> writes:

encrypt pg_shadow passwords

This will be for 7.1? For some reason I thought it was being pushed
off to 7.2.

I don't know of anything that would force delaying it --- it's not
dependent on querytree redesign, for example. The real question is,
do we have anyone who's committed to do the work? I heard a lot of
discussion but I didn't hear anyone taking responsibility for it...

regards, tom lane

On Tue, 13 Jun 2000, Tom Lane wrote:

Vince Vielhaber <vev@michvhf.com> writes:

encrypt pg_shadow passwords

This will be for 7.1? For some reason I thought it was being pushed
off to 7.2.

I don't know of anything that would force delaying it --- it's not
dependent on querytree redesign, for example. The real question is,
do we have anyone who's committed to do the work? I heard a lot of
discussion but I didn't hear anyone taking responsibility for it...

I offered to do the work and I have the md5 routine here and tested on
a number of platforms. But as I said, I thought someone wanted to delay
it until 7.2, if that's not the case then I'll get to it. There was also
a lack of interest in testing it, but I think we have most platforms
covered.

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================

On Tue, 13 Jun 2000, Ed Loehr wrote:

Vince Vielhaber wrote:

[...]

new location for config files

can I suggest /etc/postgresql ?

you can ... but everything related to postgresql has always been designed
not to require any special permissions to install, and /etc/postgresql
would definitely require root access to install :(

~postgres/etc ??

I would suggest you don't *require* or assume the creation of a postgres
user, except as an overridable default.

I *knew* somebody would bring this up. Before I sent that I tried to
describe the intent a few ways and just opted for simple. PostgreSQL
has to run as SOMEONE. Substitute that SOMEONE for ~postgres above.

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================

Vince Vielhaber wrote:

[...]

new location for config files

can I suggest /etc/postgresql ?

you can ... but everything related to postgresql has always been designed
not to require any special permissions to install, and /etc/postgresql
would definitely require root access to install :(

~postgres/etc ??

I would suggest you don't *require* or assume the creation of a postgres
user, except as an overridable default.

Regards,
Ed Loehr

The Hermit Hacker <scrappy@hub.org> writes:

new location for config files

can I suggest /etc/postgresql ?

you can ... but everything related to postgresql has always been designed
not to require any special permissions to install, and /etc/postgresql
would definitely require root access to install :(

Even more to the point, the config files are always kept in the data
directory so that it's possible to run multiple installations on the
same machine. Keeping the config files under /etc (or any other fixed
location) would destroy that capability.

regards, tom lane

Vince Vielhaber <vev@michvhf.com> writes:

do we have anyone who's committed to do the work? I heard a lot of
discussion but I didn't hear anyone taking responsibility for it...

I offered to do the work and I have the md5 routine here and tested on
a number of platforms. But as I said, I thought someone wanted to delay
it until 7.2, if that's not the case then I'll get to it.

Far as I can see, you should go for it.

regards, tom lane

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

Here is the list I have gotten of open 7.1 items:

There were a whole bunch of issues about the type system --- automatic
coercion rules, default type selection for both numeric and string
literals, etc. Not sure how to describe this in five words or less...

regards, tom lane

Here is the list I have gotten of open 7.1 items:

I thought that someone was working on
outer joins
better views (or rewriting the rules system, not sure what the direction was)
better SQL92 compliance
also, I think that at some time there was discussion about a better interface
for procedures, enabling them to work on several tuples. May be wrong though.

But if all, or just most, of the items on your list will be finished, it ought
to be a 8.0 release :-)

--
Kaare Rasmussen --Linux, spil,-- Tlf: 3816 2582
Kaki Data tshirts, merchandize Fax: 3816 2582
Howitzvej 75 �ben 14.00-18.00 Email: kar@webline.dk
2000 Frederiksberg L�rdag 11.00-17.00 Web: www.suse.dk

Since there are several people interested in contributing, we should
list:

Support multiple simultaneous character sets, per SQL92

- Thomas

memory context cleanup
TOAST
WAL
fmgr redesign
encrypt pg_shadow passwords
redesign pg_hba.conf password file option
new location for config files

+ new ACL? (please :-)

BTW. --- really cool list.

Updated TODO.

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

+ new ACL? (please :-)

Updated TODO.

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

[ Charset ISO-8859-1 unsupported, converting... ]

On Tue, 13 Jun 2000, Bruce Momjian wrote:

Here is the list I have gotten of open 7.1 items:

bit type
inheritance
drop column
vacuum index speed
cached query plans
memory context cleanup
TOAST
WAL
fmgr redesign
encrypt pg_shadow passwords
redesign pg_hba.conf password file option

Any details?

I would like to remove our pg_passwd script that allows
username/passwords to be specified in a file, change that file to lists
of users, or allow lists of users in pg_hba.conf.

new location for config files

Are you referring to pushing internal files to `$PGDATA/global'?

Yes.

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

that one works ...

On Tue, 13 Jun 2000, Vince Vielhaber wrote:

On Tue, 13 Jun 2000, The Hermit Hacker wrote:

On Tue, 13 Jun 2000, Sergio A. Kessler wrote:

Bruce Momjian <pgman@candle.pha.pa.us> el d���a Tue, 13 Jun 2000 05:05:53
-0400 (EDT), escribi���:

[...]

new location for config files

can I suggest /etc/postgresql ?

you can ... but everything related to postgresql has always been designed
not to require any special permissions to install, and /etc/postgresql
would definitely require root access to install :(

~postgres/etc ??

Remember, that file has to be specific for each data tree, so it has to
be under /data.

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

Vince Vielhaber <vev@michvhf.com> writes:

encrypt pg_shadow passwords

This will be for 7.1? For some reason I thought it was being pushed
off to 7.2.

I don't know of anything that would force delaying it --- it's not
dependent on querytree redesign, for example. The real question is,
do we have anyone who's committed to do the work? I heard a lot of
discussion but I didn't hear anyone taking responsibility for it...

Agreed. No reason not to be in 7.1.

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

I just kept your e-mails. I will make a TODO.detail mailbox with them.

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

Here is the list I have gotten of open 7.1 items:

There were a whole bunch of issues about the type system --- automatic
coercion rules, default type selection for both numeric and string
literals, etc. Not sure how to describe this in five words or less...

regards, tom lane

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

Here is the list I have gotten of open 7.1 items:

I thought that someone was working on
outer joins
better views (or rewriting the rules system, not sure what the direction was)
better SQL92 compliance
also, I think that at some time there was discussion about a better interface
for procedures, enabling them to work on several tuples. May be wrong though.

But if all, or just most, of the items on your list will be finished, it ought
to be a 8.0 release :-)

Most of these are planned for 7.2.

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

Added to TODO.

Since there are several people interested in contributing, we should
list:

Support multiple simultaneous character sets, per SQL92

- Thomas

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

While people are working on that, they might want to add some sanity checking
to the multibyte character decoders. Currently they fail to check for
"illegal" character sequences (i.e. sequences with no valid multibyte mapping),
and fail to do something reasonable (like return an error, silently drop the
offending characters, or anything else besides just returning random garbage
and crashing the backend).

The last time this failed to get on the TODO list because Bruce wanted
more than one person to verify that it was an issue. If several people
are going to work on the NATIONAL CHARACTER stuff, maybe they could look
into this issue, too.

-Michael Robinson

On Tue, 13 Jun 2000, Bruce Momjian wrote:

Here is the list I have gotten of open 7.1 items:

Rolling back a transaction after dropping a table creates a corrupted
database. (Yes, I know it warns you not to do that, but users are
fallible and sometimes just plain stupid.) Although the system catalog
entries are rolled back, the file on disk is permanently destroyed.

I suggest that DROP TABLE in a transaction should not be allowed.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"I beseech you therefore, brethren, by the mercies of
God, that ye present your bodies a living sacrifice,
holy, acceptable unto God, which is your reasonable
service." Romans 12:1

While people are working on that, they might want to add some sanity checking
to the multibyte character decoders. Currently they fail to check for
"illegal" character sequences (i.e. sequences with no valid multibyte mapping),
and fail to do something reasonable (like return an error, silently drop the
offending characters, or anything else besides just returning random garbage
and crashing the backend).

The last time this failed to get on the TODO list because Bruce wanted
more than one person to verify that it was an issue. If several people
are going to work on the NATIONAL CHARACTER stuff, maybe they could look
into this issue, too.

The issue is that some people felt we shouldn't be performing such
checks, and some did.

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

[ Charset ISO-8859-1 unsupported, converting... ]

On Tue, 13 Jun 2000, Karel Zak wrote:

+ new ACL? (please :-)

Not if we're shipping in August. :(

I hear you.
-- 
  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

"Oliver Elphick" <olly@lfix.co.uk> writes:

I suggest that DROP TABLE in a transaction should not be allowed.

I had actually made it do that for a short time early this year,
and was shouted down. On reflection I have to agree; it's too useful
to be able to do

begin;
drop table foo;
create table foo(new schema);
...
end;

You do indeed lose big if you suffer an error partway through, but
the answer to that is to fix our file naming conventions so that we
can support rollback of drop table.

Also note the complaints we've been getting about CREATE USER not
working inside a transaction block. That is a case where someone
(Peter IIRC) took the more hard-line approach of emitting an error
instead of a warning. I think it was not the right choice to make.

regards, tom lane

Tom Lane writes:

Also note the complaints we've been getting about CREATE USER not
working inside a transaction block. That is a case where someone
(Peter IIRC) took the more hard-line approach of emitting an error
instead of a warning. I think it was not the right choice to make.

Probably. Remember that you can claim your lunch any time. :)

In all truth, the problem is that the ODBC driver isn't very flexible
about putting BEGIN/END blocks around things. Perhaps that is also
something to look at.

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

Tom Lane wrote:

"Oliver Elphick" <olly@lfix.co.uk> writes:

I suggest that DROP TABLE in a transaction should not be allowed.

I had actually made it do that for a short time early this year,
and was shouted down. On reflection I have to agree; it's too useful
to be able to do

begin;
drop table foo;
create table foo(new schema);
...
end;

You do indeed lose big if you suffer an error partway through, but
the answer to that is to fix our file naming conventions so that we
can support rollback of drop table.

Belongs IMHO to the discussion to keep separate what is
separate (having indices/toast-relations/etc. in separate
directories and whatnot).

I've never been really happy with the file naming
conventions. The need of a filesystem entry to have the same
name of the DB object that is associated with it isn't right.
I know, some people love to be able to easily identify the
files with ls(1). OTOH what is that good for?

Well, someone can easily see how big the disk footprint of
his data is. Whow - what an info. Anything else?

Why not changing the naming to be something like this:

<dbroot>/catalog_tables/pg_...
<dbroot>/catalog_index/pg_...
<dbroot>/user_tables/oid_...
<dbroot>/user_index/oid_...
<dbroot>/temp_tables/oid_...
<dbroot>/temp_index/oid_...
<dbroot>/toast_tables/oid_...
<dbroot>/toast_index/oid_...
<dbroot>/whatnot_???/...

This way, it would be much easier to separate all the
different object types to different physical media. We would
loose some transparency, but I've allways wondered what
people USE that for (except for just wanna know). For
convinience we could implement another little utility that
tells the object size like

DESCRIBE TABLE/VIEW/whatnot <object-name>

that returns the physical location and storage details of the
object. And psql could use it to print this info additional
on the \d commands. Would give unprivileged users access to
this info, so be it, it's not a security issue IMHO.

The subdirectory an object goes into has to be controlled by
the relkind. So we need to tidy up that a little too. I think
it's worth it.

The objects storage location (the bare file) now would
contain the OID. So we avoid naming conflicts for temp
tables, naming conflicts during DROP/CREATE in a transaction
and all the like.

Comments?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

Tom Lane wrote:

"Oliver Elphick" <olly@lfix.co.uk> writes:

I suggest that DROP TABLE in a transaction should not be allowed.

I had actually made it do that for a short time early this year,
and was shouted down. On reflection I have to agree; it's too useful
to be able to do

begin;
drop table foo;
create table foo(new schema);
...
end;

You do indeed lose big if you suffer an error partway through, but
the answer to that is to fix our file naming conventions so that we
can support rollback of drop table.

Belongs IMHO to the discussion to keep separate what is
separate (having indices/toast-relations/etc. in separate
directories and whatnot).

I've never been really happy with the file naming
conventions. The need of a filesystem entry to have the same
name of the DB object that is associated with it isn't right.
I know, some people love to be able to easily identify the
files with ls(1). OTOH what is that good for?

Well, I have no problem just appending some serial number to the end of
our existing names. That solves both purposes, no? Seems Vadim is
going to have a new storage manager in 7.2 anyway.

If/when we lose file name/object mapping, we will have to write
command-line utilities to report the mappings so people can do
administration properly. It certainly makes it hard for administrators.

Well, someone can easily see how big the disk footprint of
his data is. Whow - what an info. Anything else?

Why not changing the naming to be something like this:

<dbroot>/catalog_tables/pg_...
<dbroot>/catalog_index/pg_...
<dbroot>/user_tables/oid_...
<dbroot>/user_index/oid_...
<dbroot>/temp_tables/oid_...
<dbroot>/temp_index/oid_...
<dbroot>/toast_tables/oid_...
<dbroot>/toast_index/oid_...
<dbroot>/whatnot_???/...

This way, it would be much easier to separate all the
different object types to different physical media. We would
loose some transparency, but I've allways wondered what
people USE that for (except for just wanna know). For
convinience we could implement another little utility that
tells the object size like

Yes, we could do that.

DESCRIBE TABLE/VIEW/whatnot <object-name>

that returns the physical location and storage details of the
object. And psql could use it to print this info additional
on the \d commands. Would give unprivileged users access to
this info, so be it, it's not a security issue IMHO.

You need something that works from the command line, and something that
works if PostgreSQL is not running. How would you restore one file from
a tape. I guess you could bring back the whole thing, then do the
query, and move the proper table file back in, but that is a pain.

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

At 07:13 PM 6/14/00 -0400, Bruce Momjian wrote:

This way, it would be much easier to separate all the
different object types to different physical media. We would
loose some transparency, but I've allways wondered what
people USE that for (except for just wanna know). For
convinience we could implement another little utility that
tells the object size like

Yes, we could do that.

It's a poor man's substitute for a proper create tablespace on
storage 'filesystem' - style dml statement, but it's a step in
the right direction.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

JanWieck@t-online.de (Jan Wieck) writes:

I've never been really happy with the file naming
conventions. The need of a filesystem entry to have the same
name of the DB object that is associated with it isn't right.
I know, some people love to be able to easily identify the
files with ls(1). OTOH what is that good for?

I agree with Jan on this: let's just change the file names over to
be OIDs. Then we can have rollbackable DROP and RENAME TABLE easily.
Naming the files after the logical names of the tables is nice if it
doesn't cost anything, but it is *not* worth the trouble to preserve
a relationship between filename and tablename when it is costing us.
And it's costing us big time. That single feature is hurting us on
functionality, robustness, and portability, and for what benefit?
Not nearly enough. It's time to just let go of it.

Why not changing the naming to be something like this:

<dbroot>/catalog_tables/pg_...
<dbroot>/catalog_index/pg_...
<dbroot>/user_tables/oid_...
<dbroot>/user_index/oid_...
<dbroot>/temp_tables/oid_...
<dbroot>/temp_index/oid_...
<dbroot>/toast_tables/oid_...
<dbroot>/toast_index/oid_...
<dbroot>/whatnot_???/...

I don't see a lot of value in that. Better to do something like
tablespaces:

<dbroot>/<oidoftablespace>/<oidofobject>

regards, tom lane

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

You need something that works from the command line, and something that
works if PostgreSQL is not running. How would you restore one file from
a tape.

"Restore one file from a tape"? How are you going to do that anyway?
You can't save and restore portions of a database like that, because
of transaction commit status problems. To restore table X correctly,
you'd have to restore pg_log as well, and then your other tables are
hosed --- unless you also restore all of them from the backup. Only
a complete database restore from tape would work, and for that you
don't need to tell which file is which. So the above argument is a
red herring.

I realize it's nice to be able to tell which table file is which by
eyeball, but the price we are paying for that small convenience is
just too high. Give that up, and we can have rollbackable DROP and
RENAME now (I'll personally commit to making it happen for 7.1).
Continue to insist on it, and I don't think we'll *ever* have those
features in a really robust form. It's just not possible to do
multiple file renames atomically.

regards, tom lane

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

You need something that works from the command line, and something that
works if PostgreSQL is not running. How would you restore one file from
a tape.

"Restore one file from a tape"? How are you going to do that anyway?
You can't save and restore portions of a database like that, because
of transaction commit status problems. To restore table X correctly,
you'd have to restore pg_log as well, and then your other tables are
hosed --- unless you also restore all of them from the backup. Only
a complete database restore from tape would work, and for that you
don't need to tell which file is which. So the above argument is a
red herring.

I realize it's nice to be able to tell which table file is which by
eyeball, but the price we are paying for that small convenience is
just too high. Give that up, and we can have rollbackable DROP and
RENAME now (I'll personally commit to making it happen for 7.1).
Continue to insist on it, and I don't think we'll *ever* have those
features in a really robust form. It's just not possible to do
multiple file renames atomically.

OK, I am flexible. (Yea, right.) :-)

But seriously, let me give some background. I used Ingres, that used
the VMS file system, but used strange sequential AAAF324 numbers for
tables. When someone deleted a table, or we were looking at what tables
were using disk space, it was impossible to find the Ingres table names
that went with the file. There was a system table that showed it, but
it was poorly documented, and if you deleted the table, there was no way
to look on the tape to find out which file to restore.

As far as pg_log, you certainly would not expect to get any information
back from the time of the backup table to current, so the current pg_log
would be just fine.

Basically, I guess we have to do it, but we have to print the proper
error messages for cases in the backend we just print the file name.
Also, we have to now replace the 'ls -l' command with something that
will be meaningful.

Right now, we use 'ps' with args to display backend information, and ls
-l to show disk information. We are going to lose that here.

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

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

But seriously, let me give some background. I used Ingres, that used
the VMS file system, but used strange sequential AAAF324 numbers for
tables. When someone deleted a table, or we were looking at what tables
were using disk space, it was impossible to find the Ingres table names
that went with the file. There was a system table that showed it, but
it was poorly documented, and if you deleted the table, there was no way
to look on the tape to find out which file to restore.

Fair enough, but it seems to me that the answer is to expend some effort
on system admin support tools. We could do a lot in that line with less
effort than trying to make a fundamentally mismatched filesystem
representation do what we need.

regards, tom lane

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

But seriously, let me give some background. I used Ingres, that used
the VMS file system, but used strange sequential AAAF324 numbers for
tables. When someone deleted a table, or we were looking at what tables
were using disk space, it was impossible to find the Ingres table names
that went with the file. There was a system table that showed it, but
it was poorly documented, and if you deleted the table, there was no way
to look on the tape to find out which file to restore.

Fair enough, but it seems to me that the answer is to expend some effort
on system admin support tools. We could do a lot in that line with less
effort than trying to make a fundamentally mismatched filesystem
representation do what we need.

That was my point --- that in doing this change, we are taking on more
TODO items, that may detract from our main TODO items. I am also
concerned that the filename/tablename mapping is supported by so many
Unix toolks like ls, lsof/fstat, and tar, that we could be in for
needing to support tons of utilities to enable administrators to do what
they can so easily do now.

Even gdb shows us the filename/tablename in backtraces. We are never
going to be able to reproduce that. I guess I didn't want to bit off
that much work until we had a _convincing_ need. I guess I don't
consider table schema commands inside transactions and such to be as big
an items as the utility features we will need to build.

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

At 10:28 PM 6/14/00 -0400, Bruce Momjian wrote:

As far as pg_log, you certainly would not expect to get any information
back from the time of the backup table to current, so the current pg_log
would be just fine.

In reality, very few people are going to be interested in restoring
a table in a way that breaks referential integrity and other
normal assumptions about what exists in the database. The reality
is that most people are going to engage in a little time travel
to a past, consistent backup rather than do as you suggest.

This is going to be more and more true as Postgres gains more and
more acceptance in (no offense intended) the real world.

Right now, we use 'ps' with args to display backend information, and ls
-l to show disk information. We are going to lose that here.

Dependence on "ls -l" is, IMO, a very weak argument.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

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

That was my point --- that in doing this change, we are taking on more
TODO items, that may detract from our main TODO items.

True, but they are also TODO items that could be handled by people other
than the inner circle of key developers. The actual rejiggering of
table-to-filename mapping is going to have to be done by one of the
small number of people who are fully up to speed on backend internals.
But we've got a lot more folks who would be able (and, hopefully,
willing) to design and code whatever tools are needed to make the
dbadmin's job easier in the face of the new filesystem layout. I'd
rather not expend a lot of core time to avoid needing those tools,
especially when I feel the old approach is fatally flawed anyway.

Even gdb shows us the filename/tablename in backtraces. We are never
going to be able to reproduce that.

Backtraces from *what*, exactly? 99% of the backend is still going
to be dealing with the same data as ever. It might be that poking
around in fd.c will be a little harder, but considering that fd.c
doesn't really know or care what the files it's manipulating are
anyway, I'm not convinced that this is a real issue.

I guess I don't consider table schema commands inside transactions and
such to be as big an items as the utility features we will need to
build.

You've *got* to be kidding. We're constantly seeing complaints about
the fact that rolling back DROP or RENAME TABLE fails --- and worse,
leaves the table in a corrupted/inconsistent state. As far as I can
tell, that's one of the worst robustness problems we've got left to
fix. This is a big deal IMHO, and I want it to be fixed and fixed
right. I don't see how to fix it right if we try to keep physical
filenames tied to logical tablenames.

Moreover, that restriction will continue to hurt us if we try to
preserve it while implementing tablespaces, ANSI schemas, etc.

regards, tom lane

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

That was my point --- that in doing this change, we are taking on more
TODO items, that may detract from our main TODO items.

True, but they are also TODO items that could be handled by people other
than the inner circle of key developers. The actual rejiggering of
table-to-filename mapping is going to have to be done by one of the
small number of people who are fully up to speed on backend internals.
But we've got a lot more folks who would be able (and, hopefully,
willing) to design and code whatever tools are needed to make the
dbadmin's job easier in the face of the new filesystem layout. I'd
rather not expend a lot of core time to avoid needing those tools,
especially when I feel the old approach is fatally flawed anyway.

Yes, it is clearly fatally flawed. I agree.

Even gdb shows us the filename/tablename in backtraces. We are never
going to be able to reproduce that.

Backtraces from *what*, exactly? 99% of the backend is still going
to be dealing with the same data as ever. It might be that poking
around in fd.c will be a little harder, but considering that fd.c
doesn't really know or care what the files it's manipulating are
anyway, I'm not convinced that this is a real issue.

I was just throwing gdb out as an example. The bigger ones are ls,
lsof/fstat, and tar.

I guess I don't consider table schema commands inside transactions and
such to be as big an items as the utility features we will need to
build.

You've *got* to be kidding. We're constantly seeing complaints about
the fact that rolling back DROP or RENAME TABLE fails --- and worse,
leaves the table in a corrupted/inconsistent state. As far as I can
tell, that's one of the worst robustness problems we've got left to
fix. This is a big deal IMHO, and I want it to be fixed and fixed
right. I don't see how to fix it right if we try to keep physical
filenames tied to logical tablenames.

Moreover, that restriction will continue to hurt us if we try to
preserve it while implementing tablespaces, ANSI schemas, etc.

Well, we did have someone do a test implementation of oid file names,
and their report was that is looked pretty ugly. However, if people are
convinced it has to be done, we can get started. I guess I was waiting
for Vadim's storage manager, where the whole idea of separate files is
going to go away anyway, I suspect. We would then have to re-write all
our admin tools for the new format.

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

Bruce Momjian wrote:

DESCRIBE TABLE/VIEW/whatnot <object-name>

that returns the physical location and storage details of the
object. And psql could use it to print this info additional
on the \d commands. Would give unprivileged users access to
this info, so be it, it's not a security issue IMHO.

You need something that works from the command line, and something that
works if PostgreSQL is not running. How would you restore one file from
a tape. I guess you could bring back the whole thing, then do the
query, and move the proper table file back in, but that is a pain.

Think you messed up some basics of PG here.

It's totally useless to restore single files of a PostgreSQL
database. You could either put back anything below ./data, or
nothing - the reason is pg_log.

You don't need something that work's if PostgreSQL is not
running. You cannot restore ONE file from a tape! You can
restore a PostgreSQL instance (only a complete one - not a
single DB, nor a single table or any other object). While
your backup is writing to the tape, each number of backends
could concurrently modify single blocks of the heap, it's
indices and pg_log. So what does the tape contain the?

I'd like to ask you, are you sure the backups you're making
are worth the power consumption of the tape drive? You're
talking about restoring a file - and sould be aware of the
fact, that any file based backup would never be able to get
consistent snapshot of the database, like pg_dump is able to.

As long as you don't take the postmaster down during the
entire saving of ./data, you aren't in a safe position. And
the only safe RESTORE is to restore ./data completely or
nothing. It's not even (easily) possible to initdb and
restore a single DB from tape (it is, but requires some deep
knowledge and more than just restoring some files from tape).

YOU REALLY DON'T NEED ANY FILENAMES IN THERE!

The more I think about it, the more I feel these file names,
easily associatable with the objects they represent, are more
dangerous than useful in practice. Maybe we should obfuscate
the entire ./data like Oracle does with it's tablespace
files. Just that our tablespaces will be directories,
containing totally cryptic named files.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

Tom Lane wrote:

JanWieck@t-online.de (Jan Wieck) writes:

Why not changing the naming to be something like this:

<dbroot>/catalog_tables/pg_...
<dbroot>/catalog_index/pg_...
<dbroot>/user_tables/oid_...
<dbroot>/user_index/oid_...
<dbroot>/temp_tables/oid_...
<dbroot>/temp_index/oid_...
<dbroot>/toast_tables/oid_...
<dbroot>/toast_index/oid_...
<dbroot>/whatnot_???/...

I don't see a lot of value in that. Better to do something like
tablespaces:

<dbroot>/<oidoftablespace>/<oidofobject>

*Slap* - yes!

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

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

The issue is that some people felt we shouldn't be performing such
checks, and some did.

Well, more precisely, the issue was stalemated at "one person felt we
should perform such checks" and "one person (who, incidentally, wrote the
code) felt we shouldn't".

Obviously, as it stands, the issue is going nowhere.

I was just hoping to encourage more people to examine the problem, so that
we might get a consensus one way or the other.

-Michael Robinson

On Wed, Jun 14, 2000 at 11:21:15PM -0400, Bruce Momjian wrote:

Well, we did have someone do a test implementation of oid file names,
and their report was that is looked pretty ugly.

That someone would be me. Did my mails from this morning fall into a black
hole? I've got a patch that does either oid filenames or relname_<oid>,
take your pick. It doesn't do tablespaces, just leaves the files where
they are. TO do relname_<oid>, I add a relphysname field to pg_class.

I'll update it to current and throw it at the PATCHES list this weekend,
unless someone more central wants to do tablespaces first. I tried
out rollinging back ALTER TABLE RENAME. Works fine. Biggest problem
with it is that I played silly buggers with the relcache for no good
reason. Hiroshi stripped that out and said it works fone, otherwise. I
also haven't touched DROP TABLE yet. The physical file be deleted at
transaction commit time, then? Hmm, we're the 'things to do at commit'
queue?

convinced it has to be done, we can get started. I guess I was waiting
for Vadim's storage manager, where the whole idea of separate files is
going to go away anyway, I suspect. We would then have to re-write all
our admin tools for the new format.

Any strong objections to the mixed relname_oid solution? It gets us
everything oids does, and still lets Bruce use 'ls -l' to find the big
tables, putting off writing any admin tools that'll need to be rewritten,
anyway.

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005

But seriously, let me give some background. I used Ingres, that used
the VMS file system, but used strange sequential AAAF324 numbers for
tables. When someone deleted a table, or we were looking at what
tables were using disk space, it was impossible to find the Ingres
table names that went with the file. There was a system table that
showed it, but it was poorly documented, and if you deleted the table,
there was no way to look on the tape to find out which file to
restore.

I had the same experience, but let's put the blame where it belongs: it
wasn't the filename's fault, it was poor design and support from the
Ingres company.

- Thomas

"Ross J. Reedstrom" wrote:

Any strong objections to the mixed relname_oid solution? It gets us
everything oids does, and still lets Bruce use 'ls -l' to find the big
tables, putting off writing any admin tools that'll need to be rewritten,
anyway.

Doesn't relname_oid defeat the purpose of oid file names, which is that
they don't change when the table is renamed? Wasn't it going to be oids
with a tool to create a symlink of relname -> oid ?

The issue is that some people felt we shouldn't be performing such
checks, and some did.

Well, more precisely, the issue was stalemated at "one person felt we
should perform such checks" and "one person (who, incidentally, wrote
the code) felt we shouldn't".
I was just hoping to encourage more people to examine the problem, so
that we might get a consensus one way or the other.

I hope that the issue is clearer once we have a trial implementation to
play with.

- Thomas

"Ross J. Reedstrom" <reedstrm@rice.edu> writes:

Any strong objections to the mixed relname_oid solution?

Yes!

You cannot make it work reliably unless the relname part is the original
relname and does not track ALTER TABLE RENAME. IMHO having an obsolete
relname in the filename is worse than not having the relname at all;
it's a recipe for confusion, it means you still need admin tools to tell
which end is really up, and what's worst is you might think you don't.

Furthermore it requires an additional column in pg_class to keep track
of the original relname, which is a waste of space and effort.

It also creates a portability risk, or at least fails to remove one,
since you are critically dependent on the assumption that the OS
supports long filenames --- on a filesystem that truncates names to less
than about 45 characters you're in very deep trouble. An OID-only
approach still works on traditional 14-char-filename Unix filesystems
(it'd mostly even work on DOS 8+3, though I doubt we care about that).

Finally, one of the reasons I want to go to filenames based only on OID
is that that'll make life easier for mdblindwrt. Original relname + OID
doesn't help, in fact it makes life harder (more shmem space needed to
keep track of the filename for each buffer).

Can we *PLEASE JUST LET GO* of this bad idea? No relname in the
filename. Period.

regards, tom lane

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

Well, we did have someone do a test implementation of oid file names,
and their report was that is looked pretty ugly. However, if people are
convinced it has to be done, we can get started. I guess I was waiting
for Vadim's storage manager, where the whole idea of separate files is
going to go away anyway, I suspect. We would then have to re-write all
our admin tools for the new format.

I seem to recall him saying that he wanted to go to filename == OID
just like I'm suggesting. But I agree we probably ought to hold off
doing anything until he gets back from Russia and can let us know
whether that's still his plan. If he is planning one-huge-file or
something like that, we might as well let these issues go unfixed
for one more release cycle.

regards, tom lane

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

You need something that works from the command line, and

something that

works if PostgreSQL is not running. How would you restore

one file from

a tape.

"Restore one file from a tape"? How are you going to do that anyway?
You can't save and restore portions of a database like that, because
of transaction commit status problems. To restore table X correctly,
you'd have to restore pg_log as well, and then your other tables are
hosed --- unless you also restore all of them from the backup. Only
a complete database restore from tape would work, and for that you
don't need to tell which file is which. So the above argument is a
red herring.

From what I know it is possible to simply restore one table file
since pg_log keeps all tid's. Of course it cannot guarantee integrity
and does not work if the table was altered.

I realize it's nice to be able to tell which table file is which by
eyeball, but the price we are paying for that small convenience is
just too high. Give that up, and we can have rollbackable DROP and
RENAME now (I'll personally commit to making it happen for 7.1).
Continue to insist on it, and I don't think we'll *ever* have those
features in a really robust form. It's just not possible to do
multiple file renames atomically.

In the last proposal Bruce and I had it all layed out for tabname + oid
with no overhead in the normal situation, and little overhead if a rename
table crashed or was not rolled back or committed properly
which imho had all advantages combined.

Andreas

On Wed, 14 Jun 2000, Peter Eisentraut wrote:

On Tue, 13 Jun 2000, Karel Zak wrote:

+ new ACL? (please :-)

Not if we're shipping in August. :(

I understand you. I said it as dream :-)

BTW. --- Are you sure, how idea for ACL will good?

1/ your original idea with one-line-for-one-privilage in pg_privilage
(IMHO it's good idea).

2/ more priv. in one-line.

Karel

In reality, very few people are going to be interested in restoring
a table in a way that breaks referential integrity and other
normal assumptions about what exists in the database.

This is not true. In my DBA history it would have saved me manweeks
of work if an easy and efficient restore of one single table from backup
would have been available in Informix and Oracle.
We allways had to restore most of the whole system to another machine only
to get back at some table info that would then be manually re-added
to the production system.
A restore of one table to a different/new tablename would have been
very convenient, and this is currently possible in PostgreSQL.
(create new table with same schema, then replace new table data file
with file from backup)

The reality
is that most people are going to engage in a little time travel
to a past, consistent backup rather than do as you suggest.

No, this is what is done most of the time, but it is very inconvenient
to tell people that they loose all work from past days, so it is usually
done as I noted above if possible. We once had a situation where all data
was deleted from a table, but the problem was only noticed 3 weeks later.

This is going to be more and more true as Postgres gains more and
more acceptance in (no offense intended) the real world.

Right now, we use 'ps' with args to display backend

information, and ls

-l to show disk information. We are going to lose that here.

Dependence on "ls -l" is, IMO, a very weak argument.

In normal situations where everything works I agree, it is the
error situations where it really helps if you see what data is where.
debugging, lsof, Bruce already named them.

Andreas

"Ross J. Reedstrom" <reedstrm@rice.edu> writes:

Any strong objections to the mixed relname_oid solution?

Yes!

You cannot make it work reliably unless the relname part is
the original
relname and does not track ALTER TABLE RENAME.

It does, or should at least. Only problem case is where db crashes during
alter or commit/rollback. This could be fixed by first open that fails to
find the file
or vacuum, or some other utility.

IMHO having
an obsolete
relname in the filename is worse than not having the relname at all;
it's a recipe for confusion, it means you still need admin
tools to tell
which end is really up, and what's worst is you might think you don't.

Furthermore it requires an additional column in pg_class to keep track
of the original relname, which is a waste of space and effort.

it does not.

Finally, one of the reasons I want to go to filenames based
only on OID
is that that'll make life easier for mdblindwrt. Original
relname + OID
doesn't help, in fact it makes life harder (more shmem space needed to
keep track of the filename for each buffer).

I do not see this. filename is constructed from relname+oid.
if not found, do directory scan for *_<OID>.dat, if found --> rename.

Andreas

It's just not possible to do
multiple file renames atomically.

This is not necessary, since *_<OID> is unique regardless of relname prefix.

Andreas

While people are working on that, they might want to add some sanity checking
to the multibyte character decoders. Currently they fail to check for
"illegal" character sequences (i.e. sequences with no valid multibyte mapping),
and fail to do something reasonable (like return an error, silently drop the
offending characters, or anything else besides just returning random garbage
and crashing the backend).

Hum.. I thought Michael Robinson is the one who is against the idea of
rejecting "illegal" character sequences before they are put in the DB.
I like the idea but I haven't time to do that (However I'm not sure I
would like to do it for EUC-CN, since he dislikes the codes I write).

Bruce, I would like to see followings in the TODO. I also would like
to hear from Thomas and Peter or whoever being interested in
implementing NATIONAL CHARACTER stuffs if they are reasonable.

o Don't accept character sequences those are not valid as their charset
(signaling ERROR seems appropriate IMHO)

o Make PostgreSQL more multibyte aware (for example, TRIM function and
NAME data type)

o Regard n of CHAR(n)/VARCHAR(n) as the number of letters, rather than
the number of bytes
--
Tatsuo Ishii

On Wed, 14 Jun 2000, Jan Wieck wrote:

Why not changing the naming to be something like this:

<dbroot>/catalog_tables/pg_...
<dbroot>/catalog_index/pg_...
<dbroot>/user_tables/oid_...
<dbroot>/user_index/oid_...
<dbroot>/temp_tables/oid_...
<dbroot>/temp_index/oid_...
<dbroot>/toast_tables/oid_...
<dbroot>/toast_index/oid_...
<dbroot>/whatnot_???/...

This way, it would be much easier to separate all the
different object types to different physical media. We would
loose some transparency, but I've allways wondered what
people USE that for (except for just wanna know). For
convinience we could implement another little utility that
tells the object size like

Wow, I've been advocating this one for how many months now? :) You won't
get any arguments from me ...

On Wed, 14 Jun 2000, Bruce Momjian wrote:

Backtraces from *what*, exactly? 99% of the backend is still going
to be dealing with the same data as ever. It might be that poking
around in fd.c will be a little harder, but considering that fd.c
doesn't really know or care what the files it's manipulating are
anyway, I'm not convinced that this is a real issue.

I was just throwing gdb out as an example. The bigger ones are ls,
lsof/fstat, and tar.

You've lost me on this one ... if someone does an lsof of the process, it
will still provide them a list of open files ... are you complaining about
the extra step required to translate the file name to a "valid table"?

Oh, one point here ... this whole 'filenaming issue' ... as far as ls is
concerned, at least, only affects the superuser, since he's the only one
that can go 'ls'ng around i nthe directories ...

And, ummm, how hard would it be to have \d in psql display the "physical
table name" as part of its output?

Slight tangent here:

One thing that I think would be great if we could add is some sort of:

SELECT db_name, disk_space;

query wher a database owner, not the superuser, could see how much disk
space their tables are using up ... possible?

Ross J. Reedstrom wrote:

Any strong objections to the mixed relname_oid solution? It gets us
everything oids does, and still lets Bruce use 'ls -l' to find the big
tables, putting off writing any admin tools that'll need to be rewritten,
anyway.

I would object to the mixed name.

Consider:

CREATE TABLE FOO ....
ALTER TABLE FOO RENAME FOO_OLD;
CREATE TABLE FOO ....

For the same atomicity reason, rename can't change the
name of the files. So, which foo_<oid> is the FOO_OLD
and which is FOO?

In other words, in the presence of rename, putting
relname in the filename is misleading at best.

--

Mark Hollomon
mhh@nortelnetworks.com
ESN 451-9008 (302)454-9008

Then <tgl@sss.pgh.pa.us> spoke up and said:

Precedence: bulk

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

But seriously, let me give some background. I used Ingres, that used
the VMS file system, but used strange sequential AAAF324 numbers for
tables. When someone deleted a table, or we were looking at what tables
were using disk space, it was impossible to find the Ingres table names
that went with the file. There was a system table that showed it, but
it was poorly documented, and if you deleted the table, there was no way
to look on the tape to find out which file to restore.

Fair enough, but it seems to me that the answer is to expend some effort
on system admin support tools. We could do a lot in that line with less
effort than trying to make a fundamentally mismatched filesystem
representation do what we need.

We've been an Ingres shop as long as there's been an Ingres. While
we've also had the problem Bruce noticed with table names, we've
*also* used the trivial fix of running a (simple) Report Writer job
each night, immediately before the backup, that lists all of the
database tables/indicies and the underlying files.

True, if someone drops/recreates a table twice between backups we
can't find the intermediate file name, but since we also haven't
backed up that filename, this isn't an issue.

Also, the consistency issue is really not as important as you would
think. If you are restoring a table, you want the information in it,
whether or not it's consistent with anything else. I've done hundreds
of table restores (can you say "modify table to heap"?) and never once
has inconsistency been an issue. Oh, yeah, and we don't shut the
database down for this, either. (That last isn't my choice, BTW.)

--
=====================================================================
| JAVA must have been developed in the wilds of West Virginia. |
| After all, why else would it support only single inheritance?? |
=====================================================================
| Finger geek@cmu.edu for my public key. |
=====================================================================

At 10:04 AM 6/15/00 +0200, Zeugswetter Andreas SB wrote:

In reality, very few people are going to be interested in restoring
a table in a way that breaks referential integrity and other
normal assumptions about what exists in the database.

This is not true. In my DBA history it would have saved me manweeks
of work if an easy and efficient restore of one single table from backup
would have been available in Informix and Oracle.
We allways had to restore most of the whole system to another machine only
to get back at some table info that would then be manually re-added
to the production system.

I'm missing something, I guess. You would do a createdb, do a filesystem
copy of pg_log and one file into it, and then read data from the table
without having to restore the other tables in the database?

I'm just curious - when was the last time you restored a Postgres
database in this piecemeal manner, and how often do you do it?

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

But seriously, let me give some background. I used Ingres, that used
the VMS file system, but used strange sequential AAAF324 numbers for
tables. When someone deleted a table, or we were looking at what
tables were using disk space, it was impossible to find the Ingres
table names that went with the file. There was a system table that
showed it, but it was poorly documented, and if you deleted the table,
there was no way to look on the tape to find out which file to
restore.

I had the same experience, but let's put the blame where it belongs: it
wasn't the filename's fault, it was poor design and support from the
Ingres company.

Yes, that certainly was part of the cause. Also, if the PostgreSQL
files are backed up using tar while no database activity is happening,
there is no reason the tar restore will not work. You just create a
table with the same schema, stop the postmaster, have the backup file
replace the newly created table file, and restart the postmaster.

I can't tell you how many times I have said, "Man, whoever did this
Ingres naming schema was an idiot. Do they know how many problems they
caused for us?"

Also, Informix standard engine uses the tablename_oid setup for its
table names, and it works fine. It grabs the first 8 characters of the
table, and plops some unique number on the end of it. Works fine for
administrators.

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

Finally, one of the reasons I want to go to filenames based
only on OID
is that that'll make life easier for mdblindwrt. Original
relname + OID
doesn't help, in fact it makes life harder (more shmem space needed to
keep track of the filename for each buffer).

I do not see this. filename is constructed from relname+oid.
if not found, do directory scan for *_<OID>.dat, if found --> rename.

That is kind if nifty.

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

Hum.. I thought Michael Robinson is the one who is against the idea of
rejecting "illegal" character sequences before they are put in the DB.
I like the idea but I haven't time to do that (However I'm not sure I
would like to do it for EUC-CN, since he dislikes the codes I write).

Bruce, I would like to see followings in the TODO. I also would like
to hear from Thomas and Peter or whoever being interested in
implementing NATIONAL CHARACTER stuffs if they are reasonable.

o Don't accept character sequences those are not valid as their charset
(signaling ERROR seems appropriate IMHO)

o Make PostgreSQL more multibyte aware (for example, TRIM function and
NAME data type)

o Regard n of CHAR(n)/VARCHAR(n) as the number of letters, rather than
the number of bytes

Added to TODO:

* Reject character sequences those are not valid in their charset
* Make functions more multi-byte aware, i.e. trim()
* Make n of CHAR(n)/VARCHAR(n) the number of letters, not bytes

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

o Don't accept character sequences those are not valid as their
charset (signaling ERROR seems appropriate IMHO)
o Make PostgreSQL more multibyte aware (for example, TRIM function and
NAME data type)
o Regard n of CHAR(n)/VARCHAR(n) as the number of letters, rather than
the number of bytes

All good, and important features when we are done.

One issue: I can see (or imagine ;) how we can use the Postgres type
system to manage multiple character sets. But allowing arbitrary
character sets in, say, table names forces us to cope with allowing a
mix of character sets in a single column of a system table. afaik this
general capability is not mandated by SQL9x (the SQL_TEXT character set
is used for all system resources??). Would it be acceptable to have a
"default database character set" which is allowed to creep into the
pg_xxx tables? Even that seems to be a difficult thing to accomplish at
the moment (we'd need to get some of the text manipulation functions
from the catalogs, not from hardcoded references as we do now).

We should itemize all of these issues so we can keep track of what is
necessary, possible, and/or "easy".

- Thomas

In reality, very few people are going to be interested in restoring
a table in a way that breaks referential integrity and other
normal assumptions about what exists in the database.

This is not true. In my DBA history it would have saved me manweeks
of work if an easy and efficient restore of one single table

from backup

would have been available in Informix and Oracle.
We allways had to restore most of the whole system to

another machine only

to get back at some table info that would then be manually re-added
to the production system.

I'm missing something, I guess. You would do a createdb, do
a filesystem
copy of pg_log and one file into it, and then read data from the table
without having to restore the other tables in the database?

No if you want to restore to a separate postgres instance you need to
restore all pg system tables as well.
What I meant is create a new table in your production server and replace
the new 0 byte file with your backup file (rename it accordingly).

Andreas

On Thu, Jun 15, 2000 at 03:11:52AM -0400, Tom Lane wrote:

"Ross J. Reedstrom" <reedstrm@rice.edu> writes:

Any strong objections to the mixed relname_oid solution?

Yes!

You cannot make it work reliably unless the relname part is the original
relname and does not track ALTER TABLE RENAME. IMHO having an obsolete
relname in the filename is worse than not having the relname at all;
it's a recipe for confusion, it means you still need admin tools to tell
which end is really up, and what's worst is you might think you don't.

The plan here was to let VACUUM handle renaming the file, since it
will already have all the necessary locks. This shortens the window
of confusion. ALTER TABLE RENAME doesn't happen that often, really -
the relname is there just for human consumption, then.

Furthermore it requires an additional column in pg_class to keep track
of the original relname, which is a waste of space and effort.

I actually started down this path thinking about implementing SCHEMA,
since tables in the same DB but in different schema can have the same
relname, I figured I needed to change that. We'll need something in
pg_class to keep track of what schema a relation is in, instead.

It also creates a portability risk, or at least fails to remove one,
since you are critically dependent on the assumption that the OS
supports long filenames --- on a filesystem that truncates names to less
than about 45 characters you're in very deep trouble. An OID-only
approach still works on traditional 14-char-filename Unix filesystems
(it'd mostly even work on DOS 8+3, though I doubt we care about that).

Actually, no. Since I store the filename in a name attribute, I used this
nifty function somebody wrote, makeObjectName, to trim the relname part,
but leave the oid. (Yes, I know it's yours ;-)

Finally, one of the reasons I want to go to filenames based only on OID
is that that'll make life easier for mdblindwrt. Original relname + OID
doesn't help, in fact it makes life harder (more shmem space needed to
keep track of the filename for each buffer).

Can you explain in more detail how this helps? Not by letting the bufmgr
know that oid == filename, I hope. We need to improving the abstraction
of the smgr, not add another violation. Ah, sorry, mdblindwrt _is_
in the smgr.

Hmm, grovelling through that code, I see how it could be simpler if reloid
== filename. Heck, we even get to save shmem in the buffdesc.blind part,
since we only need the dbname in there, now.

Hmm, I see I missed the relpath_blind() in my patch - oops. (relpath()
is always called with RelationGetPhysicalRelationName(), and that's
where I was putting in the relphysname)

Hmm, what's all this with functions in catalog.c that are only called by
smgr/md.c? seems to me that anything having to do with physical storage
(like the path!) belongs in the smgr abstraction.

Can we *PLEASE JUST LET GO* of this bad idea? No relname in the
filename. Period.

Gee, so dogmatic. No one besides Bruce and Hiroshi discussed this _at
all_ when I first put up patches two month ago. O.K., I'll do the oids
only version (and fix up relpath_blind)

Ross

--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005

Can we *PLEASE JUST LET GO* of this bad idea? No relname in the
filename. Period.

Gee, so dogmatic. No one besides Bruce and Hiroshi discussed this _at
all_ when I first put up patches two month ago. O.K., I'll do the oids
only version (and fix up relpath_blind)

Hold on. I don't think we want that work done yet. Seems even Tom is
thinking that if Vadim is going to re-do everything later anyway, we may
be better with a relname/oid solution that does require additional
administration apps.

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

-----Original Message-----
From: pgsql-hackers-owner@hub.org
[mailto:pgsql-hackers-owner@hub.org]On Behalf Of Bruce Momjian

Can we *PLEASE JUST LET GO* of this bad idea? No relname in the
filename. Period.

Gee, so dogmatic. No one besides Bruce and Hiroshi discussed this _at
all_ when I first put up patches two month ago. O.K., I'll do the oids
only version (and fix up relpath_blind)

Hold on. I don't think we want that work done yet. Seems even Tom is
thinking that if Vadim is going to re-do everything later anyway, we may
be better with a relname/oid solution that does require additional
administration apps.

Hmm,why is naming rule first ?

I've never enphasized naming rule except that it should be unique.
It has been my main point to reduce the necessity of naming rule
as possible. IIRC,by keeping the stored place in pg_class,Ross's
trial patch remains only 2 places where naming rule is required.
So wouldn't we be free from naming rule(it would not be so difficult
to change naming rule if the rule is found to be bad) ?

I've also mentioned many times neither relname nor oid is sufficient
for the uniqueness. In addiiton neither relname nor oid would be
necessary for the uniqueness.
IMHO,it's bad to rely on the item which is neither necessary nor
sufficient.
I proposed relname+unique_id naming once. The unique_id is
independent from oid. The relname is only for convinience for
DBA and so we don't have to change it due to RENAME.
Db's consistency is much more important than dba's satis-
faction.

Comments ?

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

I've also mentioned many times neither relname nor oid is sufficient
for the uniqueness. In addiiton neither relname nor oid would be
necessary for the uniqueness.
IMHO,it's bad to rely on the item which is neither necessary nor
sufficient.
I proposed relname+unique_id naming once. The unique_id is
independent from oid. The relname is only for convinience for
DBA and so we don't have to change it due to RENAME.
Db's consistency is much more important than dba's satis-
faction.

Comments ?

I am happy not to rename the file on 'RENAME', but seems no one likes
that.

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

Don Baccus wrote:

At 10:04 AM 6/15/00 +0200, Zeugswetter Andreas SB wrote:

This is not true. In my DBA history it would have saved me manweeks
of work if an easy and efficient restore of one single table from backup
would have been available in Informix and Oracle.
We allways had to restore most of the whole system to another machine only
to get back at some table info that would then be manually re-added
to the production system.

I'm missing something, I guess. You would do a createdb, do a filesystem
copy of pg_log and one file into it, and then read data from the table
without having to restore the other tables in the database?

I'm just curious - when was the last time you restored a Postgres
database in this piecemeal manner, and how often do you do it?

More curios to me is that people seem to use physical file
based backup at all. Do they shutdown the postmaster during
backup or do they live with the fact that maybe not every
backup is a vital one?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

On Thu, Jun 15, 2000 at 05:48:59PM -0400, Bruce Momjian wrote:

I've also mentioned many times neither relname nor oid is sufficient
for the uniqueness. In addiiton neither relname nor oid would be
necessary for the uniqueness.
IMHO,it's bad to rely on the item which is neither necessary nor
sufficient.
I proposed relname+unique_id naming once. The unique_id is
independent from oid. The relname is only for convinience for
DBA and so we don't have to change it due to RENAME.
Db's consistency is much more important than dba's satis-
faction.

Comments ?

I am happy not to rename the file on 'RENAME', but seems no one likes
that.

Good, 'cause that's how I've implemented it so far. Actually, all
I've done is port my previous patch to current, with one little
change: I added a macro RelationGetRealRelationName which does what
RelationGetPhysicalRelationName used to do: i.e. return the relname with
no temptable funny business, and used that for the relcache macros. It
passes all the serial regression tests: I haven't run the parallel tests
yet. ALTER TABLE RENAME rollsback nicely. I'll need to learn some omre
about xacts to get DROP TABLE rolling back.

I'll drop it on PATCHES right now, for comment.

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005

Here's the patch I promised on HACKERS. Comments anyone?

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005

I'm just curious - when was the last time you restored a Postgres
database in this piecemeal manner, and how often do you do it?

More curios to me is that people seem to use physical file
based backup at all. Do they shutdown the postmaster during
backup or do they live with the fact that maybe not every
backup is a vital one?

I sure hope they shut down the postmaster, or know that nothing is
happening during the backup.

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

"Ross J. Reedstrom" <reedstrm@rice.edu> writes:

On Thu, Jun 15, 2000 at 03:11:52AM -0400, Tom Lane wrote:

"Ross J. Reedstrom" <reedstrm@rice.edu> writes:

Any strong objections to the mixed relname_oid solution?

Yes!

The plan here was to let VACUUM handle renaming the file, since it
will already have all the necessary locks. This shortens the window
of confusion. ALTER TABLE RENAME doesn't happen that often, really -
the relname is there just for human consumption, then.

Yeah, I've seen tons of discussion of how if we do this, that, and
the other thing, and be prepared to fix up some other things in case
of crash recovery, we can make it work with filename == relname + OID
(where relname tracks logical name, at least at some remove).

Probably. Assuming nobody forgets anything.

I'm just trying to point out that that's a huge amount of pretty
delicate mechanism. The amount of work required to make it trustworthy
looks to me to dwarf the admin tools that Bruce is complaining about.
And we only have a few people competent to do the work. (With all
due respect, Ross, if you weren't already aware of the implications
for mdblindwrt, I have to wonder what else you missed.)

Filename == OID is so simple, reliable, and straightforward by
comparison that I think the decision is a no-brainer.

If we could afford to sink unlimited time into this one issue then
it might make sense to do it the hard way, but we have enough
important stuff on our TODO list to keep us all busy for years ---
I cannot believe that it's an effective use of our time to do this.

Hmm, what's all this with functions in catalog.c that are only called by
smgr/md.c? seems to me that anything having to do with physical storage
(like the path!) belongs in the smgr abstraction.

Yeah, there's a bunch of stuff that should have been implemented by
adding new smgr entry points, but wasn't. It should be pushed down.
(I can't resist pointing out that one of those things is physical
relation rename, which will go away and not *need* to be pushed down
if we do it the way I want.)

regards, tom lane

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

Gee, so dogmatic. No one besides Bruce and Hiroshi discussed this _at
all_ when I first put up patches two month ago. O.K., I'll do the oids
only version (and fix up relpath_blind)

Hold on. I don't think we want that work done yet. Seems even Tom is
thinking that if Vadim is going to re-do everything later anyway, we may
be better with a relname/oid solution that does require additional
administration apps.

Don't put words in my mouth, please. If we are going to throw the
work away later, it'd be foolish to do the much greater amount of
work needed to make filename=relname+OID fly than is needed for
filename=OID.

However, I'm pretty sure I recall Vadim stating that he thought
filename=OID would be required for his smgr changes anyway...

regards, tom lane

-----Original Message-----
From: pgsql-hackers-owner@hub.org [mailto:pgsql-hackers-owner@hub.org]On
Behalf Of Tom Lane

"Ross J. Reedstrom" <reedstrm@rice.edu> writes:

On Thu, Jun 15, 2000 at 03:11:52AM -0400, Tom Lane wrote:

"Ross J. Reedstrom" <reedstrm@rice.edu> writes:

Any strong objections to the mixed relname_oid solution?

Yes!

The plan here was to let VACUUM handle renaming the file, since it
will already have all the necessary locks. This shortens the window
of confusion. ALTER TABLE RENAME doesn't happen that often, really -
the relname is there just for human consumption, then.

Yeah, I've seen tons of discussion of how if we do this, that, and
the other thing, and be prepared to fix up some other things in case
of crash recovery, we can make it work with filename == relname + OID
(where relname tracks logical name, at least at some remove).

I've seen little discussion of how to avoid the use of naming rule.
I've proposed many times that we should keep the information
where the table is stored in our database itself. I've never seen
clear objections to it. So I could understand my proposal is OK ?
Isn't it much more important than naming rule ? Under the
mechanism,we could easily replace bad naming rule.
And I believe that Ross's work is mostly around the mechanism
not naming rule.

Now I like neither relname nor oid because it's not sufficient
for my purpose.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:

Now I like neither relname nor oid because it's not sufficient
for my purpose.

We should probably not do much of anything with this issue until
we have a clearer understanding of what we want to do about
tablespaces and schemas.

My gut feeling is that we will end up with pathnames that look
something like

.../data/base/DBNAME/TABLESPACE/OIDOFRELATION

(with .N attached if a segment of a large relation, of course).

The TABLESPACE "name" should likely be an OID itself, but it wouldn't
have to be if you are willing to say that tablespaces aren't renamable.
(Come to think of it, does anyone care about being able to rename
databases? ;-)) Note that the TABLESPACE will often be a symlink
to storage on another drive, rather than a plain subdirectory of the
DBNAME, but that shouldn't be an issue at this level of discussion.

I think that schemas probably don't enter into this. We should instead
rely on the uniqueness of OIDs to prevent filename collisions. However,
OIDs aren't really unique: different databases in an installation will
use the same OIDs for their system tables. My feeling is that we can
live with a restriction like "you can't store the system tables of
different databases in the same tablespace". Alternatively we could
avoid that issue by inverting the pathname order:

.../data/base/TABLESPACE/DBNAME/OIDOFRELATION

Note that in any case, system tables will have to live in a
predetermined tablespace, since you can't very well look in pg_class
to find out which tablespace pg_class lives in. Perhaps we should
just reserve a tablespace per database for system tables and forget
the whole issue. If we do that, there's not really any need for
the database in the path! Just

.../data/base/TABLESPACE/OIDOFRELATION

would do fine and help reduce lookup overhead.

BTW, schemas do make things interesting for the other camp:
is it possible for the same table to be referenced by different
names in different schemas? If so, just how useful is it to pick
one of those names arbitrarily for the filename? This is an advanced
version of the main objection to using the original relname and not
updating it at RENAME TABLE --- sooner or later, the filenames are
going to be more confusing than helpful.

Comments? Have I missed something important about schemas?

regards, tom lane

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:

Now I like neither relname nor oid because it's not sufficient
for my purpose.

We should probably not do much of anything with this issue until
we have a clearer understanding of what we want to do about
tablespaces and schemas.

Here is an analysis of our options:

Work required Disadvantages
----------------------------------------------------------------------------

Keep current system no work rename/create no rollback

relname/oid but less work new pg_class column,
no rename change filename not accurate on
rename

relname/oid with more work complex code
rename change during
vacuum

oid filename less work, but confusing to admins
need admin tools

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

Sorry for my previous mail. It was posted by my mistake.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:

Now I like neither relname nor oid because it's not sufficient
for my purpose.

We should probably not do much of anything with this issue until
we have a clearer understanding of what we want to do about
tablespaces and schemas.

My gut feeling is that we will end up with pathnames that look
something like

.../data/base/DBNAME/TABLESPACE/OIDOFRELATION

Schema is a logical concept and irrevant to physical location.
I strongly object your suggestion unless above means *default*
location.
Tablespace is an encapsulation of table allocation and the
name should be irrevant to the location basically. So above
seems very bad for me.

Anyway I don't see any advantage in fixed mapping impleme
ntation. After renewal,we should at least have a possibility to
allocate a specific table in arbitrary separate directory.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:

Now I like neither relname nor oid because it's not sufficient
for my purpose.

We should probably not do much of anything with this issue until
we have a clearer understanding of what we want to do about
tablespaces and schemas.

Here is an analysis of our options:

Work required Disadvantages
------------------------------------------------------------------
----------

Keep current system no work rename/create
no rollback

relname/oid but less work new pg_class column,
no rename change filename not
accurate on
rename

relname/oid with more work complex code
rename change during
vacuum

oid filename less work, but confusing to admins
need admin tools

Please add my opinion for naming rule.

relname/unique_id but need some work new pg_class column,
no relname change. for unique-id generation filename not relname

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:

Tablespace is an encapsulation of table allocation and the
name should be irrevant to the location basically. So above
seems very bad for me.
Anyway I don't see any advantage in fixed mapping impleme
ntation. After renewal,we should at least have a possibility to
allocate a specific table in arbitrary separate directory.

Call a "directory" a "tablespace" and we're on the same page,
aren't we? Actually I'd envision some kind of admin command
"CREATE TABLESPACE foo AS /path/to/wherever". That would make
appropriate system catalog entries and also create a symlink
from ".../data/base/foo" (or some such place) to the target
directory. Then when we make a table in that tablespace,
it's in the right place. Problem solved, no?

It gets a little trickier if you want to be able to split
multi-gig tables across several tablespaces, though, since
you couldn't just append ".N" to the base table path in that
scenario.

I'd be interested to know what sort of facilities Oracle
provides for managing huge tables...

regards, tom lane

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:

Please add my opinion for naming rule.

relname/unique_id but need some work new pg_class column,
no relname change. for unique-id generation filename not relname

Why is a unique ID better than --- or even different from ---
using the relation's OID? It seems pointless to me...

regards, tom lane

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:

Please add my opinion for naming rule.

relname/unique_id but need some work new

pg_class column,

no relname change. for unique-id generation filename not relname

Why is a unique ID better than --- or even different from ---
using the relation's OID? It seems pointless to me...

For example,in the implementation of CLUSTER command,
we would need another new file for the target relation in
order to put sorted rows but don't we want to change the
OID ? It would be needed for table re-construction generally.
If I remember correectly,you once proposed OID+version
naming for the cases.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:

Tablespace is an encapsulation of table allocation and the
name should be irrevant to the location basically. So above
seems very bad for me.
Anyway I don't see any advantage in fixed mapping impleme
ntation. After renewal,we should at least have a possibility to
allocate a specific table in arbitrary separate directory.

Call a "directory" a "tablespace" and we're on the same page,
aren't we? Actually I'd envision some kind of admin command
"CREATE TABLESPACE foo AS /path/to/wherever".

Yes,I think 'tablespace -> directory' is the most natural
extension under current file_per_table storage manager.
If many_tables_in_a_file storage manager is introduced,we
may be able to change the definiiton of TABLESPACE
to 'tablespace -> files' like Oracle.

That would make
appropriate system catalog entries and also create a symlink
from ".../data/base/foo" (or some such place) to the target
directory.
Then when we make a table in that tablespace,
it's in the right place. Problem solved, no?

I don't like symlink for dbms data files. However it may
be OK,If symlink are limited to 'tablespace->directory'
corrspondence and all tablespaces(including default
etc) are symlink. It is simple and all debugging would
be processed under tablespace_is_symlink environment.

It gets a little trickier if you want to be able to split
multi-gig tables across several tablespaces, though, since
you couldn't just append ".N" to the base table path in that
scenario.

This seems to be not that easy to solve now.
Ross doesn't change this naming rule for multi-gig
tables either in his trial.

I'd be interested to know what sort of facilities Oracle
provides for managing huge tables...

In my knowledge about old Oracle,one TABLESPACE
could have many DATAFILEs which could contain
many tables.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Tom Lane wrote:

<dbroot>/catalog_tables/pg_...
<dbroot>/catalog_index/pg_...
<dbroot>/user_tables/oid_...
<dbroot>/user_index/oid_...
<dbroot>/temp_tables/oid_...
<dbroot>/temp_index/oid_...
<dbroot>/toast_tables/oid_...
<dbroot>/toast_index/oid_...
<dbroot>/whatnot_???/...

I don't see a lot of value in that. Better to do something like
tablespaces:
I don't see a lot of value in that. Better to do something like
tablespaces:

<dbroot>/<oidoftablespace>/<oidofobject>

What is the benefit of having oidoftablespace in the directory path?
Isn't tablespace an idea so you can store it somewhere completely
different?
Or is there some symlink idea or something?

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:

Why is a unique ID better than --- or even different from ---
using the relation's OID? It seems pointless to me...

For example,in the implementation of CLUSTER command,
we would need another new file for the target relation in
order to put sorted rows but don't we want to change the
OID ? It would be needed for table re-construction generally.
If I remember correectly,you once proposed OID+version
naming for the cases.

Hmm, so you are thinking that the pg_class row for the table would
include this uniqueID, and then committing the pg_class update would
be the atomic action that replaces the old table contents with the
new? It does have some attraction now that I think about it.

But there are other ways we could do the same thing. If we want to
have tablespaces, there will need to be a tablespace identifier in
each pg_class row. So we could do CLUSTER in the same way as we'd
move a table from one tablespace to another: create the new files in
the new tablespace directory, and the commit of the new pg_class row
with the new tablespace value is the atomic action that makes the new
files valid and the old files not.

You will probably say "but I didn't want to move my table to a new
tablespace just to cluster it!" I think we could live with that,
though. A tablespace doesn't need to have any existence more concrete
than a subdirectory, in my vision of the way things would work. We
could do something like making two subdirectories of each place that
the dbadmin designates as a "tablespace", so that we make two logical
tablespaces out of what the dbadmin thinks of as one. Then we can
ping-pong between those directories to do things like clustering "in
place".

Basically I want to keep the bottom-level mechanisms as simple and
reliable as we possibly can. The fewer concepts are known down at
the bottom, the better. If we can keep the pathname constituents
to just "tablespace" and "relation OID" we'll be in great shape ---
but each additional concept that has to be known down there is
another potential problem.

regards, tom lane

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:

Why is a unique ID better than --- or even different from ---
using the relation's OID? It seems pointless to me...

For example,in the implementation of CLUSTER command,
we would need another new file for the target relation in
order to put sorted rows but don't we want to change the
OID ? It would be needed for table re-construction generally.
If I remember correectly,you once proposed OID+version
naming for the cases.

Hmm, so you are thinking that the pg_class row for the table would
include this uniqueID,

No,I just include the place where the table is stored(pathname under
current file_per_table storage manager) in the pg_class row because
I don't want to rely on table allocating rule(naming rule for current)
to access existent relation files. This has always been my main point.
Many_tables_in_a_file storage manager wouldn't be able to live without
keeping this kind of infomation.
This information(where it is stored) is diffrent from tablespace(where
to store) information. There was an idea to keep the information into
opaque entry in pg_class which only a specific storage manager
could handle. There was an idea to have a new system table which
keeps the information. and so on...

and then committing the pg_class update would
be the atomic action that replaces the old table contents with the
new? It does have some attraction now that I think about it.

But there are other ways we could do the same thing. If we want to
have tablespaces, there will need to be a tablespace identifier in
each pg_class row. So we could do CLUSTER in the same way as we'd
move a table from one tablespace to another: create the new files in
the new tablespace directory, and the commit of the new pg_class row
with the new tablespace value is the atomic action that makes the new
files valid and the old files not.

You will probably say "but I didn't want to move my table to a new
tablespace just to cluster it!"

Yes.

I think we could live with that,
though. A tablespace doesn't need to have any existence more concrete
than a subdirectory, in my vision of the way things would work. We
could do something like making two subdirectories of each place that
the dbadmin designates as a "tablespace", so that we make two logical
tablespaces out of what the dbadmin thinks of as one.

Certainly we could design TABLESPACE(where to store) as above.

Then we can
ping-pong between those directories to do things like clustering "in
place".

But maybe we must keep the directory information where the table was
*ping-ponged* in (e.g.) pg_class. Is such an implementation cleaner or
more extensible than mine(keeping the stored place exactly) ?

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

Chris Bitmead <chrisb@nimrod.itg.telstra.com.au> writes:

Tom Lane wrote:

I don't see a lot of value in that. Better to do something like
tablespaces:

<dbroot>/<oidoftablespace>/<oidofobject>

What is the benefit of having oidoftablespace in the directory path?
Isn't tablespace an idea so you can store it somewhere completely
different?
Or is there some symlink idea or something?

Exactly --- I'm assuming that the tablespace "directory" is likely
to be a symlink to some other mounted volume. The point here is
to keep the low-level file access routines from having to know very
much about tablespaces or file organization. In the above proposal,
all they need to know is the relation's OID and the name (or OID)
of the tablespace the relation's assigned to; then they can form
a valid path using a hardwired rule. There's still plenty of
flexibility of organization, but it's not necessary to know that
where the rubber meets the road (eg, when you're down inside mdblindwrt
trying to dump a dirty buffer to disk with no spare resources to find
out anything about the relation the page belongs to...)

regards, tom lane

Tom Lane wrote:

It gets a little trickier if you want to be able to split
multi-gig tables across several tablespaces, though, since
you couldn't just append ".N" to the base table path in that
scenario.

I'd be interested to know what sort of facilities Oracle
provides for managing huge tables...

Oracle tablespaces are a collection of 1...n preallocated
files. Each table then is bound to a tablespace and
allocates extents (chunks) from those files.

There are some per table attributes that control the extent
sizes with default values coming from the tablespace. The
initial extent size, the nextextent and the pctincrease.
There is a hardcoded limit for the number of extents a table
can have at all. In Oracle7 it was 512 (or somewhat below -
don't recall correct). Maybe that's gone with Oracle8, don't
know.

This storage concept has IMHO a couple of advatages over
ours.

The tablespace files are preallocated, so there will
never be a change in block allocation during runtime and
that's the base for fdatasync() beeing sufficient at
syncpoints. All what might be inaccurate after a crash is
the last modified time in the inode, and that's totally
irrelevant for Oracle. The fsck will never fail, and
anything is up to Oracle's recovery.

The number of total tablespace files is limited to a
value that ensures, that the backends can keep them all
open all the time. It's hard to exceed that limit. A
typical SAP installation with more than 20,000
tables/indices doesn't need more than 30 or 40 of them.

It is perfectly prepared for raw devices, since a
tablespace in a raw device installation is simply an area
of blocks on a disk.

There are also disadvantages.

You can run out of space even if there are plenty GB's
free on your disks. You have to create tablespaces
explicitly.

If you've choosen inadequate extent size parameters, you
end up with high fragmented tables (slowing down) or get
stuck with running against maxextents, where only a reorg
(export/import) helps.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

o Don't accept character sequences those are not valid as their
charset (signaling ERROR seems appropriate IMHO)
o Make PostgreSQL more multibyte aware (for example, TRIM function and
NAME data type)
o Regard n of CHAR(n)/VARCHAR(n) as the number of letters, rather than
the number of bytes

All good, and important features when we are done.

Glad to hear that.

One issue: I can see (or imagine ;) how we can use the Postgres type
system to manage multiple character sets. But allowing arbitrary
character sets in, say, table names forces us to cope with allowing a
mix of character sets in a single column of a system table. afaik this
general capability is not mandated by SQL9x (the SQL_TEXT character set
is used for all system resources??). Would it be acceptable to have a
"default database character set" which is allowed to creep into the
pg_xxx tables? Even that seems to be a difficult thing to accomplish at
the moment (we'd need to get some of the text manipulation functions
from the catalogs, not from hardcoded references as we do now).

"default database character set" idea does not seem to be the solution
for cross-db relations such as pg_database. The only solution I can
imagine so far is using SQL_TEXT.

BTW, I've been thinking about SQL_TEXT for a while and it seems
mule_internal_code or Unicode(UTF-8) would be the candidates for
it. Mule_internal_code looks more acceptable for Asian multi-byte
users like me than Unicode. It's clean, simple and does not require
huge conversion tables between Unicode and other encodings. However,
Unicode has a stronger political power in the real world and for most
single-byte users probably it would be enough. My idea is let users
choose one of them. I mean making it a compile time option.

We should itemize all of these issues so we can keep track of what is
necessary, possible, and/or "easy".

You are right, probably there would be tons of issues in implementing
multiple charsets support.
--
Tatsuo Ishii

"default database character set" idea does not seem to be the solution
for cross-db relations such as pg_database. The only solution I can
imagine so far is using SQL_TEXT.
BTW, I've been thinking about SQL_TEXT for a while and it seems
mule_internal_code or Unicode(UTF-8) would be the candidates for
it. Mule_internal_code looks more acceptable for Asian multi-byte
users like me than Unicode. It's clean, simple and does not require
huge conversion tables between Unicode and other encodings. However,
Unicode has a stronger political power in the real world and for most
single-byte users probably it would be enough. My idea is let users
choose one of them. I mean making it a compile time option.

Oh. I was recalling SQL_TEXT as being a "subset" character set which
contains only the characters (more or less) that are required for
implementing the SQL92 query language and standard features.

Are you seeing it as being a "superset" character set which can
represent all other character sets??

And, how would you suggest we start tracking this discussion in a design
document? I could put something into the developer's guide, or we could
have a plain-text FAQ, or ??

I'd propose that we start accumulating a feature list, perhaps ordering
it into categories like

o required/suggested by SQL9x
o required/suggested by experience in the real world
o sure would be nice to have
o really bad idea ;)

- Thomas

JanWieck@t-online.de (Jan Wieck) writes:

There are also disadvantages.

You can run out of space even if there are plenty GB's
free on your disks. You have to create tablespaces
explicitly.

Not to mention the reverse: if I read this right, you have to suck
up your GB's long in advance of actually needing them. That's OK
for a machine that's dedicated to Oracle ... not so OK for smaller
installations, playpens, etc.

I'm not convinced that there's anything fundamentally wrong with
doing storage allocation in Unix files the way we have been.

(At least not when we're sitting atop a well-done filesystem,
which may leave the Linux folk out in the cold ;-).)

regards, tom lane

(At least not when we're sitting atop a well-done filesystem,
which may leave the Linux folk out in the cold ;-).)

Those who live in HP houses should not throw stones :))

- Thomas

JanWieck@t-online.de (Jan Wieck) writes:

Tom Lane wrote:

It gets a little trickier if you want to be able to split
multi-gig tables across several tablespaces, though, since
you couldn't just append ".N" to the base table path in that
scenario.

I'd be interested to know what sort of facilities Oracle
provides for managing huge tables...

Oracle tablespaces are a collection of 1...n preallocated
files. Each table then is bound to a tablespace and
allocates extents (chunks) from those files.

OK, to get back to the point here: so in Oracle, tables can't cross
tablespace boundaries, but a tablespace itself could span multiple
disks?

Not sure if I like that better or worse than equating a tablespace
with a directory (so, presumably, all the files within it live on
one filesystem) and then trying to make tables able to span
tablespaces. We will need to do one or the other though, if we want
to have any significant improvement over the current state of affairs
for large tables.

One way is to play the flip-the-path-ordering game some more,
and access multiple-segment tables with pathnames like this:

.../TABLESPACE/RELATION -- first or only segment
.../TABLESPACE/N/RELATION -- N'th extension segment

This isn't any harder for md.c to deal with than what we do now,
but by making the /N subdirectories be symlinks, the dbadmin could
easily arrange for extension segments to go on different filesystems.
Also, since /N subdirectory symlinks can be added as needed,
expanding available space by attaching more disks isn't hard.
(If the admin hasn't pre-made a /N symlink when it's needed,
I'd envision the backend just automatically creating a plain
subdirectory so that it can extend the table.)

A limitation is that the N'th extension segments of all the relations
in a given tablespace have to be in the same place, but I don't see
that as a major objection. Worst case is you make a separate tablespace
for each of your multi-gig relations ... you're probably not going to
have a very large number of such relations, so this doesn't seem like
unmanageable admin complexity.

We'd still want to create some tools to help the dbadmin with slinging
all these symlinks around, of course. But I think it's critical to keep
the low-level file access protocol simple and reliable, which really
means minimizing the amount of information the backend needs to know to
figure out which file to write a page in. With something like the above
you only need to know the tablespace name (or more likely OID), the
relation OID (+name or not, depending on outcome of other argument),
and the offset in the table. No worse than now from the software's
point of view.

Comments?

regards, tom lane

... But I think it's critical to keep
the low-level file access protocol simple and reliable, which really
means minimizing the amount of information the backend needs to know
to figure out which file to write a page in. With something like the
above you only need to know the tablespace name (or more likely OID),
the relation OID (+name or not, depending on outcome of other
argument), and the offset in the table. No worse than now from the
software's point of view.
Comments?

I'm probably missing the context a bit, but imho we should try hard to
stay away from symlinks as the general solution for anything.

Sorry for being behind here, but to make sure I'm on the right page:
o tablespaces decouple storage from logical tables
o a database lives in a default tablespace, unless specified
o by default, a table will live in the default tablespace
o (eventually) a table can be split across tablespaces

Some thoughts:
o the ability to split single tables across disks was essential for
scalability when disks were small. But with RAID, NAS, etc etc isn't
that a smaller issue now?
o "tablespaces" would implement our less-developed "with location"
feature, right? Splitting databases, whole indices and whole tables
across storage is the biggest win for this work since more users will
use the feature.
o location information needs to travel with individual tables anyway.

There are also disadvantages.

You can run out of space even if there are plenty GB's
free on your disks. You have to create tablespaces
explicitly.

If you've choosen inadequate extent size parameters, you
end up with high fragmented tables (slowing down) or get
stuck with running against maxextents, where only a reorg
(export/import) helps.

Also, Tom Lane pointed out to me that file system read-ahead does not
help if your table is spread around in tablespaces.

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

On Thu, 15 Jun 2000, Bruce Momjian wrote:

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:

Now I like neither relname nor oid because it's not sufficient
for my purpose.

We should probably not do much of anything with this issue until
we have a clearer understanding of what we want to do about
tablespaces and schemas.

Here is an analysis of our options:

Work required Disadvantages
----------------------------------------------------------------------------

Keep current system no work rename/create no rollback

relname/oid but less work new pg_class column,
no rename change filename not accurate on
rename

relname/oid with more work complex code
rename change during
vacuum

oid filename less work, but confusing to admins
need admin tools

My vote is with Tom on this one ... oid only ... the admin should be able
to do a quick SELECT on a table to find out the OID->table mapping, and I
believe its already been pointed out that you cant' just restore one file
anyway, so it kinda negates the "server isn't running problem" ...

On Thu, 15 Jun 2000, Tom Lane wrote:

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:

Please add my opinion for naming rule.

relname/unique_id but need some work new pg_class column,
no relname change. for unique-id generation filename not relname

Why is a unique ID better than --- or even different from ---
using the relation's OID? It seems pointless to me...

just to open up a whole new bucket of worms here, but ... if we do use OID
(which up until this thought I endorse 100%) ... do we not run a risk if
we run out of OIDs? As far as I know, those are still a finite resource,
no?

or, do we just assume that by the time that comes, everyone will be pretty
much using 64bit machines? :)

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:

... But I think it's critical to keep
the low-level file access protocol simple and reliable, which really
means minimizing the amount of information the backend needs to know
to figure out which file to write a page in. With something like the
above you only need to know the tablespace name (or more likely OID),
the relation OID (+name or not, depending on outcome of other
argument), and the offset in the table. No worse than now from the
software's point of view.
Comments?

I'm probably missing the context a bit, but imho we should try hard to
stay away from symlinks as the general solution for anything.

Why?

regards, tom lane

On Thu, 15 Jun 2000, Bruce Momjian wrote:

I'm just curious - when was the last time you restored a Postgres
database in this piecemeal manner, and how often do you do it?

More curios to me is that people seem to use physical file
based backup at all. Do they shutdown the postmaster during
backup or do they live with the fact that maybe not every
backup is a vital one?

I sure hope they shut down the postmaster, or know that nothing is
happening during the backup.

I do a backup based on a pg_dump snapshot at the time of the backup
...

The Hermit Hacker <scrappy@hub.org> writes:

just to open up a whole new bucket of worms here, but ... if we do use OID
(which up until this thought I endorse 100%) ... do we not run a risk if
we run out of OIDs? As far as I know, those are still a finite resource,
no?

They are, and there is some risk involved, but OID collisions in the
system tables will cause you just as much headache. There's not only
the pg_class row to think of, but the pg_attribute rows, etc etc.

If you did have an OID collision with an existing table you'd have to
keep trying until you got a set of OID assignments with no conflicts.
(Now that we have unique indexes on the system tables, this should
work properly, ie, you will hear about it if you have a conflict.)
I don't think the physical table names make this noticeably worse.
Of course we'd better be careful to create table files with O_EXCL,
so as not to tromp on existing files, but we do that already IIRC.

or, do we just assume that by the time that comes, everyone will be pretty
much using 64bit machines? :)

I think we are not too far away from being able to offer 64-bit OID as
a compile-time option (on machines where there is a 64-bit integer type
that is). It's just a matter of someone putting it at the head of their
todo list.

Bottom line is I'm not real worried about this issue.

But having said all that, I am coming round to agree with Hiroshi's idea
anyway. See upcoming message.

regards, tom lane

At 11:46 AM 6/16/00 -0400, Tom Lane wrote:

OK, to get back to the point here: so in Oracle, tables can't cross
tablespace boundaries,

Right, the construct AFAIK is "create table/index foo on tablespace ..."

but a tablespace itself could span multiple
disks?

Right.

Not sure if I like that better or worse than equating a tablespace
with a directory (so, presumably, all the files within it live on
one filesystem) and then trying to make tables able to span
tablespaces. We will need to do one or the other though, if we want
to have any significant improvement over the current state of affairs
for large tables.

Oracle's way does a reasonable job of isolating the datamodel
from the details of the physical layout.

Take the OpenACS web toolkit, for instance. We could take
each module's tables and indices and assign them appropriately
to various dataspaces, then provide a separate .sql files with
only "create tablespace" statements in there.

By modifying that one central file, the toolkit installation
could be customized to run anything from a small site (one
disk with everything on it, ala my own personal webserver at
birdnotes.net) or a very large site with many spindles, with
various index and table structures spread out widely hither
and thither.

Given that the OpenACS datamodel is nearly 10K lines long (including
many comments, of course), being able to customize an installation
to such a degree by modifying a single file filled with "create
tablespaces" would be very attractive.

One way is to play the flip-the-path-ordering game some more,
and access multiple-segment tables with pathnames like this:

.../TABLESPACE/RELATION -- first or only segment
.../TABLESPACE/N/RELATION -- N'th extension segment

This isn't any harder for md.c to deal with than what we do now,
but by making the /N subdirectories be symlinks, the dbadmin could
easily arrange for extension segments to go on different filesystems.

I personally dislike depending on symlinks to move stuff around.
Among other things, a pg_dump/restore (and presumably future
backup tools?) can't recreate the disk layout automatically.

We'd still want to create some tools to help the dbadmin with slinging
all these symlinks around, of course.

OK, if symlinks are simply an implementation detail hidden from the
dbadmin, and if the physical structure is kept in the db so it can
be rebuilt if necessary automatically, then I don't mind symlinks.

But I think it's critical to keep
the low-level file access protocol simple and reliable, which really
means minimizing the amount of information the backend needs to know to
figure out which file to write a page in. With something like the above
you only need to know the tablespace name (or more likely OID), the
relation OID (+name or not, depending on outcome of other argument),
and the offset in the table. No worse than now from the software's
point of view.

Make the code that creates and otherwise manipulates tablespaces
do the work, while keeping the low-level file access protocol simple.

Yes, this approach sounds very good to me.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

At 04:27 PM 6/16/00 +0000, Thomas Lockhart wrote:

Sorry for being behind here, but to make sure I'm on the right page:
o tablespaces decouple storage from logical tables
o a database lives in a default tablespace, unless specified
o by default, a table will live in the default tablespace
o (eventually) a table can be split across tablespaces

Or tablespaces across filesystems/mountpoints whatever.

Some thoughts:
o the ability to split single tables across disks was essential for
scalability when disks were small. But with RAID, NAS, etc etc isn't
that a smaller issue now?

Yes for size issues, I should think, especially if you have the
money for a large RAID subsystem. But for throughput performance,
control over which spindles particularly busy tables and indices
go on would still seem to be pretty relevant, when they're being
updated a lot. In order to minimize seek times.

I really can't say how important this is in reality. Oracle-world
folks still talk about this kind of optimization being important,
but I'm not personally running any kind of database-backed website
that's busy enough or contains enough storage to worry about it.

o "tablespaces" would implement our less-developed "with location"
feature, right? Splitting databases, whole indices and whole tables
across storage is the biggest win for this work since more users will
use the feature.
o location information needs to travel with individual tables anyway.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

Don Baccus <dhogaza@pacifier.com> writes:

This isn't any harder for md.c to deal with than what we do now,
but by making the /N subdirectories be symlinks, the dbadmin could
easily arrange for extension segments to go on different filesystems.

I personally dislike depending on symlinks to move stuff around.
Among other things, a pg_dump/restore (and presumably future
backup tools?) can't recreate the disk layout automatically.

Good point, we'd need some way of saving/restoring the tablespace
structures.

We'd still want to create some tools to help the dbadmin with slinging
all these symlinks around, of course.

OK, if symlinks are simply an implementation detail hidden from the
dbadmin, and if the physical structure is kept in the db so it can
be rebuilt if necessary automatically, then I don't mind symlinks.

I'm not sure about keeping it in the db --- creates a bit of a
chicken-and-egg problem doesn't it? Maybe there needs to be a
"system database" that has nailed-down pathnames (no tablespaces
for you baby) and contains the critical installation-wide tables
like pg_database, pg_user, pg_tablespace. A restore would have
to restore these tables first anyway.

Make the code that creates and otherwise manipulates tablespaces
do the work, while keeping the low-level file access protocol simple.

Right, that's the bottom line for me.

regards, tom lane

Some thoughts:
o the ability to split single tables across disks was essential for
scalability when disks were small. But with RAID, NAS, etc etc isn't
that a smaller issue now?

Yes for size issues, I should think, especially if you have the
money for a large RAID subsystem. But for throughput performance,
control over which spindles particularly busy tables and indices
go on would still seem to be pretty relevant, when they're being
updated a lot. In order to minimize seek times.

I really can't say how important this is in reality. Oracle-world
folks still talk about this kind of optimization being important,
but I'm not personally running any kind of database-backed website
that's busy enough or contains enough storage to worry about it.

It is important when you have a few big tables that must be fast. One
objection I have always had to the HP logical volume manager is that it
is difficult to know what drives are being assigned to each logical
volume.

Seems if they don't have RAID, we should allow such drive partitioning.

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

On Fri, Jun 16, 2000 at 04:27:22PM +0000, Thomas Lockhart wrote:

... But I think it's critical to keep
the low-level file access protocol simple and reliable, which really
means minimizing the amount of information the backend needs to know
to figure out which file to write a page in. With something like the
above you only need to know the tablespace name (or more likely OID),
the relation OID (+name or not, depending on outcome of other
argument), and the offset in the table. No worse than now from the
software's point of view.
Comments?

I think the backend needs a per table token that indicates how
to get at the physical bits of the file. Whether that's a filename
alone, filename with path, oid, key to a smgr hash table or something
else, it's opaque above the smgr routines.

Hmm, now I'm thinking, since the tablespace discussion has been reopened,
the way to go about coding all this is to reactivate the smgr code: how
about I leave the existing md smgr as is, and clone it, call it md2 or
something, and start messing with adding features there?

I'm probably missing the context a bit, but imho we should try hard to
stay away from symlinks as the general solution for anything.

Sorry for being behind here, but to make sure I'm on the right page:
o tablespaces decouple storage from logical tables
o a database lives in a default tablespace, unless specified
o by default, a table will live in the default tablespace
o (eventually) a table can be split across tablespaces

Some thoughts:
o the ability to split single tables across disks was essential for
scalability when disks were small. But with RAID, NAS, etc etc isn't
that a smaller issue now?
o "tablespaces" would implement our less-developed "with location"
feature, right? Splitting databases, whole indices and whole tables
across storage is the biggest win for this work since more users will
use the feature.
o location information needs to travel with individual tables anyway.

I was juist thinking that that discussion needed some summation.

Some links to historic discussion:

This one is Vadim saying WAL will need oids names:
http://www.postgresql.org/mhonarc/pgsql-hackers/1999-11/msg00809.html

A longer discussion kicked off by Don Baccus:
http://www.postgresql.org/mhonarc/pgsql-hackers/2000-01/msg00510.html

Tom suggesting OIDs to allow rollback:
http://www.postgresql.org/mhonarc/pgsql-hackers/2000-03/msg00119.html

Martin Neumann posted an question on dataspaces:

(can't find it in the offical archives: looks like March 2000, 10-29 is
missing. here's my copy: don't beat on it! n particular, since I threw
it together for local access, it's one _big_ index page)

http://cooker.ir.rice.edu/postgresql/msg20257.html
(in that thread is a post where I mention blindwrites and getting rid
of GetRawDatabaseInfo)

Martin later posted an RFD on tablespaces:

http://cooker.ir.rice.edu/postgresql/msg20490.html

Here's Hor�k Daniel with a patch for discussion, implementing dataspaces
on a per database level:

http://cooker.ir.rice.edu/postgresql/msg20498.html

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005

At 03:00 PM 6/16/00 -0400, Tom Lane wrote:

OK, if symlinks are simply an implementation detail hidden from the
dbadmin, and if the physical structure is kept in the db so it can
be rebuilt if necessary automatically, then I don't mind symlinks.

I'm not sure about keeping it in the db --- creates a bit of a
chicken-and-egg problem doesn't it?

Not if the tablespace creates preceeds the tables stored in them.

Maybe there needs to be a
"system database" that has nailed-down pathnames (no tablespaces
for you baby) and contains the critical installation-wide tables
like pg_database, pg_user, pg_tablespace. A restore would have
to restore these tables first anyway.

Oh, I see. Yes, when I've looked into this and have thought about
it I've assumed that there would always be a known starting point
which would contain the installation-wide tables.

From a practical point of view, I don't think that's really a
problem.

I've not looked into how Oracle does this, I assume it builds
a system tablespace on one of the initial mount points you give
it when you install the thing. The paths to the mount points
are stored in specific files known to Oracle, I think. It's
been over a year (not long enough!) since I've set up Oracle...

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

On Thu, Jun 15, 2000 at 07:53:52PM -0400, Tom Lane wrote:

"Ross J. Reedstrom" <reedstrm@rice.edu> writes:

On Thu, Jun 15, 2000 at 03:11:52AM -0400, Tom Lane wrote:

"Ross J. Reedstrom" <reedstrm@rice.edu> writes:

Any strong objections to the mixed relname_oid solution?

Yes!

The plan here was to let VACUUM handle renaming the file, since it
will already have all the necessary locks. This shortens the window
of confusion. ALTER TABLE RENAME doesn't happen that often, really -
the relname is there just for human consumption, then.

Yeah, I've seen tons of discussion of how if we do this, that, and
the other thing, and be prepared to fix up some other things in case
of crash recovery, we can make it work with filename == relname + OID
(where relname tracks logical name, at least at some remove).

Probably. Assuming nobody forgets anything.

I agree, it seems a major undertaking, at first glance. And second. Even
third. Especially for someone who hasn't 'earned his spurs' yet. as
it were.

I'm just trying to point out that that's a huge amount of pretty
delicate mechanism. The amount of work required to make it trustworthy
looks to me to dwarf the admin tools that Bruce is complaining about.
And we only have a few people competent to do the work. (With all
due respect, Ross, if you weren't already aware of the implications
for mdblindwrt, I have to wonder what else you missed.)

Ah, you knew that comment would come back to haunt me (I have a
tendency to think out loud, even if checking and coming back latter
would be better;-) In fact, there's no problem, and never was, since the
buffer->blind.relname is filled in via RelationGetPhysicalRelationName,
just like every other path that requires direct file access. I just
didn't remember that I had in fact checked it (it's been a couple months,
and I just got back from vacation ;-)

Actually, Once I re-checked it, the code looked very familiar. I had
spent time looking at the blind write code in the context of getting
rid of the only non-startup use of GetRawDatabaseInfo.

As to missing things: I'm leaning heavily on Bruce's previous
work for temp tables, to seperate the two uses of relname, via the
RelationGetRelationName and RelationGetPhysicalRelationName. There are
102 uses of the first in the current code (many in elog messages), and
only 11 of the second. If I'd had to do the original work of finding
every use of relname, and catagorizing it, I agree I'm not (yet) up to
it, but I have more confidence in Bruce's (already tested) work.

Filename == OID is so simple, reliable, and straightforward by
comparison that I think the decision is a no-brainer.

Perhaps. Changing the label of the file on disk still requires finding
all the code that assumes it knows what that name is, and changing it.
Same work.

If we could afford to sink unlimited time into this one issue then
it might make sense to do it the hard way, but we have enough
important stuff on our TODO list to keep us all busy for years ---
I cannot believe that it's an effective use of our time to do this.

The joys of Open Development. You've spent a fair amount of time trying
to convince _me_ not to waste my time. Thanks, but I'm pretty bull headed
sometimes. Since I've already done something of the work, take a look
at what I've got, and then tell me I'm wasting my time, o.k.?

Hmm, what's all this with functions in catalog.c that are only called by
smgr/md.c? seems to me that anything having to do with physical storage
(like the path!) belongs in the smgr abstraction.

Yeah, there's a bunch of stuff that should have been implemented by
adding new smgr entry points, but wasn't. It should be pushed down.
(I can't resist pointing out that one of those things is physical
relation rename, which will go away and not *need* to be pushed down
if we do it the way I want.)

Oh, I agree completely. In fact, As I said to Hiroshi last time this came
up, I think of the field in pg_class an an opaque token, to be filled in
by the smgr, and only used by code further up to hand back to the smgr
routines. Same should be true of the buffer->blind struct.

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005

(At least not when we're sitting atop a well-done filesystem,
which may leave the Linux folk out in the cold ;-).)

Exactly what fs of Linux are you talking about? I believe that for a database
server, ReiserFS would be a natural choice.

--
Kaare Rasmussen --Linux, spil,-- Tlf: 3816 2582
Kaki Data tshirts, merchandize Fax: 3816 2582
Howitzvej 75 �ben 14.00-18.00 Email: kar@webline.dk
2000 Frederiksberg L�rdag 11.00-17.00 Web: www.suse.dk

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]

JanWieck@t-online.de (Jan Wieck) writes:

There are also disadvantages.

You can run out of space even if there are plenty GB's
free on your disks. You have to create tablespaces
explicitly.

Not to mention the reverse: if I read this right, you have to suck
up your GB's long in advance of actually needing them. That's OK
for a machine that's dedicated to Oracle ... not so OK for smaller
installations, playpens, etc.

I've had an anxiety about the way like Oracle's preallocation.
It had not been easy for me to estimate the extent size in
Oracle. Maybe it would lose the simplicity of environment
settings which is one of the biggest advantage of PostgreSQL.
It seems that we should also provide not_preallocated DATAFILE
when many_tables_in_a_file storage manager is introduced.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

"Ross J. Reedstrom" <reedstrm@rice.edu> writes:

I think the backend needs a per table token that indicates how
to get at the physical bits of the file. Whether that's a filename
alone, filename with path, oid, key to a smgr hash table or something
else, it's opaque above the smgr routines.

Except to the commands that provide the user interface for tablespaces
and so forth. And there aren't all that many places that deal with
physical filenames anyway. It would be a good idea to try to be a
little stricter about this, but I'm not sure you can make the separation
a whole lot cleaner than it is now ... with the exception of the obvious
bogosities like "rename table" being done above the smgr level. (But,
as I said, I want to see that code go away, not just get moved into
smgr...)

Hmm, now I'm thinking, since the tablespace discussion has been reopened,
the way to go about coding all this is to reactivate the smgr code: how
about I leave the existing md smgr as is, and clone it, call it md2 or
something, and start messing with adding features there?

Um, well, you can't have it both ways. If you're going to change/fix
the assumptions of code above the smgr, then you've got to update md
at the same time to match your new definition of the smgr interface.
Won't do much good to have a playpen smgr if the "standard" one is
broken.

One thing I have been thinking would be a good idea is to take the
relcache out of the bufmgr/smgr interfaces. The relcache is a
higher-level concept and ought not be known to bufmgr or smgr; they
ought to work with some low-level data structure or token for relations.
We might be able to eliminate the whole concept of "blind write" if we
do that. There are other problems with the relcache dependency: entries
in relcache can get blown away at inopportune times due to shared cache
inval, and it doesn't provide a good home for tokens for multiple
"versions" of a relation if we go with the fill-a-new-physical-file
approach to CLUSTER and so on.

Hmm, if you replace relcache in the smgr interfaces with pointers to
an smgr-maintained data structure, that might be the same thing that
you are alluding to above about an smgr hash table.

One thing *not* to do is add yet a third layer of data structure on
top of the ones already maintained in fd.c and md.c. Whatever extra
data might be needed here should be added to md.c's tables, I think,
and then the tokens used in the smgr interface would be pointers into
that table.

regards, tom lane

Thomas,

A few (hopefully relevant) comments regarding character sets, code pages,
I18N, and all that:

1) I've seen databases (DB2 if memory serves) that allowed the client
side to declare itself to the database back-end engine as being in a
particular code page. For instance, one could have a CP850 Latin-1 client
and an ISO 8859-1 database. The database engine did appropriate
translations in both directions.

2) Mixing code pages in a single column and then having the database
engine support it is not trivial. Either each CHAR/VARCHAR would have to
have some code page settable per row (eg either as a separate column or
as something like mycolumnname.encoding).
Even if you could handle all that you'd still be faced with the issue
is collating sequence. Each individual code page will have a collating
sequence. But how do you collate across code pages? There'd be letters
that were only in a single code page. Plus, it gets messy because with,
for instance, a simple umlauted a that occurs in CP850, CP1252, and ISO
8859-1 (and likely in other code pages as well). That letter is really
the same letter in all those code pages and should treated as such when
sorting.

3) I think it is more important for a database to support lots of
languages in the stored data than in the field names and table names. If
a programmer has to deal with A-Za-z for naming identifiers and that
perseon is Korean or Japanese then that is certain is an imposition on
them. But its a far far bigger imposition if that programmer can't build
a database that will store the letters of his national language and sort
and index and search them in convenient ways.

4) The real solution to the multiple code page dilemma is Unicode.
Yes, its more space. But the can of worms of dealing with multiple
code pages in a column is really no fun and the result is not great.
BTDTHTTS.

5) The problem with enforcing
I've built a database in DB2 where particular columns in it contained
data from many different code pages (each row had a code page field as
well as a text field). For some applications that is okay if that field
is not going to be part of an index.
However, if a database is going to be defined as being in a particular
code page, and if the database engine is going to reject characters that
are not recognized as part of that code page then you can't play the sort
of game I just described _unless_ there is a different datatype that is
similar to CHAR/VARCHAR but for which the RDBMS does not enforce code
page legality on each character. Otherwise you choose some code page for
a column, you go merrily stuffing in all sorts of rows in all sorts of
code pages, and then along come some character that is of a value that is
not a value for some other character in the code page that the RDBMS
thinks it is.

Anyway, I've done lots of I18N database stuff and hopefully a few of my
comments will be useful to the assembled brethren <g>.

In news:<3948E4D7.A3B722E9@alumni.caltech.edu>,
lockhart@alumni.caltech.edu says...

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:

It seems that we should also provide not_preallocated DATAFILE
when many_tables_in_a_file storage manager is introduced.

Several people in this thread have been talking like a
single-physical-file storage manager is in our future, but I can't
recall anyone saying that they were going to do such a thing or even
presenting reasons why it'd be a good idea.

Seems to me that physical file per relation is considerably better for
our purposes. It's easier to figure out what's going on for admin and
debug work, it means less lock contention among different backends
appending concurrently to different relations, and it gives the OS a
better shot at doing effective read-ahead on sequential scans.

So why all the enthusiasm for multi-tables-per-file?

regards, tom lane

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:

It seems that we should also provide not_preallocated DATAFILE
when many_tables_in_a_file storage manager is introduced.

Several people in this thread have been talking like a
single-physical-file storage manager is in our future, but I can't
recall anyone saying that they were going to do such a thing or even
presenting reasons why it'd be a good idea.

Seems to me that physical file per relation is considerably better for
our purposes. It's easier to figure out what's going on for admin and
debug work, it means less lock contention among different backends
appending concurrently to different relations, and it gives the OS a
better shot at doing effective read-ahead on sequential scans.

So why all the enthusiasm for multi-tables-per-file?

No idea. I thought Vadim mentioned it, but I am not sure anymore. I
certainly like our current system.

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

So why all the enthusiasm for multi-tables-per-file?

It allows you to use raw partitions which stop the OS double buffering
and wasting half of memory, as well as removing the overhead of indirect
blocks in the file system.

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]

So why all the enthusiasm for multi-tables-per-file?

No idea. I thought Vadim mentioned it, but I am not sure anymore. I
certainly like our current system.

Oops,I'm not so enthusiastic for multi_tables_per_file smgr.
I believe that Ross and I have taken a practical way that doesn't
break current file_per_table smgr.

However it seems very natural to take multi_tables_per_file
smgr into account when we consider TABLESPACE concept.
Because TABLESPACE is an encapsulation,it should have
a possibility to handle multi_tables_per_file smgr IMHO.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:

However it seems very natural to take multi_tables_per_file
smgr into account when we consider TABLESPACE concept.
Because TABLESPACE is an encapsulation,it should have
a possibility to handle multi_tables_per_file smgr IMHO.

OK, I see: you're just saying that the tablespace stuff should be
designed in such a way that it would work with a non-file-per-table
smgr. Agreed, that'd be a good check of a clean design, and someday
we might need it...

regards, tom lane

Not to mention the reverse: if I read this right, you have to suck
up your GB's long in advance of actually needing them. That's OK
for a machine that's dedicated to Oracle ... not so OK for smaller
installations, playpens, etc.

To me it looks like a way to make Oracle work on VMS machines. This is the way
files are allocated on Digital hardware.

--
Kaare Rasmussen --Linux, spil,-- Tlf: 3816 2582
Kaki Data tshirts, merchandize Fax: 3816 2582
Howitzvej 75 �ben 14.00-18.00 Email: kar@webline.dk
2000 Frederiksberg L�rdag 11.00-17.00 Web: www.suse.dk

[This followup was posted to comp.databases.postgresql.hackers and a copy
was sent to the cited author.]

A few thoughts:

1) There may be reasons why someone might not want to use RAID.
For instance, suppose one wants to put different tables on different
drives so that the seeks for one table doesn't move the drive heads away
from the disk area for another table.
Also, suppose someone wants to use a particular drive for a particular
purpose (eg certain indexes) because it is faster at seeking vs another
drive that is faster at sustained transfer rates.
Also, someone may want to span a drive across multiple SCSI
controllers. Most RAID arrays I'm aware of are per SCSI controller.
I think it is fair to say that there will always be instances where
people want to have more control over where stuff goes because they are
willing to put the effort into more subtle tuning games. Well, there
ought to be a way.

2) Some OSs do not support symlinks. The ability to list a bunch of
devices for where things will go would be of value.
Also, if you aren't putting your data on a real file system (say on
raw partitions instead) you are going to need a way to specify that
anyway.

In news:<394A556A.4EAC8B9A@alumni.caltech.edu>,
lockhart@alumni.caltech.edu says...

Tom Lane wrote:

JanWieck@t-online.de (Jan Wieck) writes:

There are also disadvantages.

You can run out of space even if there are plenty GB's
free on your disks. You have to create tablespaces
explicitly.

Not to mention the reverse: if I read this right, you have to suck
up your GB's long in advance of actually needing them. That's OK
for a machine that's dedicated to Oracle ... not so OK for smaller
installations, playpens, etc.

Right, the design is perfect for a few databases with a more
or less stable size and schema (slow to medium growth). The
problem is, that production databases tend to fall into that
behaviour and that might be a reason for so many people
asking for Oracle compatibility - they want to do development
in the high flexible Postgres environment, while running
their production server under Oracle :-(.

I'm not convinced that there's anything fundamentally wrong with
doing storage allocation in Unix files the way we have been.

(At least not when we're sitting atop a well-done filesystem,
which may leave the Linux folk out in the cold ;-).)

I'm with you on that, even if I'm one of the Linux loosers.
The only point that really strikes me is that in our system
you might end up with a corrupted file system because some
inode changes didn't make it to the disk before a crash. Even
if using fsync() instead of fdatasync() (what we cannot use
at all and that's a pain from the performance PoV). In the
Oracle world, that could only happen during

ALTER TABLESPACE <tsname> ADD DATAFILE ...

which is a fairly seldom command, issued usually by the DB
admin (at least it requires admin privileges) and thus
ensures the "admin is there and already paying attention". A
little detail not to underestimate IMHO.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

Thomas Lockhart wrote:

(At least not when we're sitting atop a well-done filesystem,
which may leave the Linux folk out in the cold ;-).)

Those who live in HP houses should not throw stones :))

Huh? Up to HPUX-9 they used to have BSD-FFS - even if it was
a 4.2 BSD one - no?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

Tom Lane wrote:

JanWieck@t-online.de (Jan Wieck) writes:

Tom Lane wrote:

It gets a little trickier if you want to be able to split
multi-gig tables across several tablespaces, though, since
you couldn't just append ".N" to the base table path in that
scenario.

I'd be interested to know what sort of facilities Oracle
provides for managing huge tables...

Oracle tablespaces are a collection of 1...n preallocated
files. Each table then is bound to a tablespace and
allocates extents (chunks) from those files.

OK, to get back to the point here: so in Oracle, tables can't cross
tablespace boundaries, but a tablespace itself could span multiple
disks?

They can. The path in

ALTER TABLESPACE <tsname> ADD DATAFILE ...

can point to any location the db system has access to.

Not sure if I like that better or worse than equating a tablespace
with a directory (so, presumably, all the files within it live on
one filesystem) and then trying to make tables able to span
tablespaces. We will need to do one or the other though, if we want
to have any significant improvement over the current state of affairs
for large tables.

One way is to play the flip-the-path-ordering game some more,
and access multiple-segment tables with pathnames like this:

.../TABLESPACE/RELATION -- first or only segment
.../TABLESPACE/N/RELATION -- N'th extension segment

[...]

In most cases all objects in one database are bound to one or
two tablespaces (data and indices). So you do an estimation
of the size required, create the tablespaces (and probably
all their extension files), then create the schema and load
it. The only reason not to do so is if your DB exceeds some
size where you have to fear not beeing able to finish online
backups before getting into Online-Relolog stuck. Has to do
the the online backup procedure of Oracle.

This isn't any harder for md.c to deal with than what we do now,
but by making the /N subdirectories be symlinks, the dbadmin could
easily arrange for extension segments to go on different filesystems.
Also, since /N subdirectory symlinks can be added as needed,
expanding available space by attaching more disks isn't hard.
(If the admin hasn't pre-made a /N symlink when it's needed,
I'd envision the backend just automatically creating a plain
subdirectory so that it can extend the table.)

So the admin allways have to leave enough freespace in the
default location to keep the DB running until he can take it
offline, move the autocreated files and create the symlinks.
What a pain for 24/7 systems.

We'd still want to create some tools to help the dbadmin with slinging
all these symlinks around, of course. But I think it's critical to keep
the low-level file access protocol simple and reliable, which really
means minimizing the amount of information the backend needs to know to
figure out which file to write a page in. With something like the above
you only need to know the tablespace name (or more likely OID), the
relation OID (+name or not, depending on outcome of other argument),
and the offset in the table. No worse than now from the software's
point of view.

Exactly the "low-level file access" protocol is highly
complicated in Postgres. Because nearly every object needs
his own file, we need to deal with virtual file descriptors.
With an Oracle-like tablespace concept and a fixed limit of
total tablespace files (this time OS or installation
specific), we could keep them all open all the time. IMHO a
big win.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

Bruce Momjian wrote:

There are also disadvantages.

You can run out of space even if there are plenty GB's
free on your disks. You have to create tablespaces
explicitly.

If you've choosen inadequate extent size parameters, you
end up with high fragmented tables (slowing down) or get
stuck with running against maxextents, where only a reorg
(export/import) helps.

Also, Tom Lane pointed out to me that file system read-ahead does not
help if your table is spread around in tablespaces.

Not with our HEAP concept. With the Oracle EXTENT concept it
does pretty good, because they have different block/extent
sizes. Usually an extent spans multiple blocks, so in the
case of sequential reads they read each extent of probably
hundreds of K sequential. And in the case of indexed reads,
they know the extent and offset of the tuple inside of the
extent, so they know the exact location of the record inside
the tablespace to read.

The big problem we allways had (why we need TOAST at all) is
that the logical blocksize (extent size) of a table is bound
to your physical blocksize used in the shared cache. This is
fixed so deeply in the heap storage architecture, that I'm
scared about it.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

Don Baccus wrote:

At 11:46 AM 6/16/00 -0400, Tom Lane wrote:

I personally dislike depending on symlinks to move stuff around.
Among other things, a pg_dump/restore (and presumably future
backup tools?) can't recreate the disk layout automatically.

Most impact from this one, IMHO.

Not that Oracle tools are able to do it either. But I think
it's more trivial to recreate a 30+ tablespace layout on the
disks than to recreate all symlinks for a 20,000+
tables/indices database like an SAP R/3 one.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

Thomas Lockhart wrote:

(At least not when we're sitting atop a well-done filesystem,
which may leave the Linux folk out in the cold ;-).)

Those who live in HP houses should not throw stones :))

Huh? Up to HPUX-9 they used to have BSD-FFS - even if it was
a 4.2 BSD one - no?

It's still there, along with VxFS from Veritas.

Ciao,

Giles

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

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

Seems we have three tablespace needs:

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

If we have a new CREATE DATABASE LOCATION command, we can say:

CREATE DATABASE LOCATION dbloc IN '/var/private/pgsql';
CREATE DATABASE newdb IN dbloc;

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

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

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

To handle placing tables, we can use:

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

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

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

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

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

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

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

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

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

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

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

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

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

I feel this solution is very flexible.

Comments?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@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

JanWieck@t-online.de (Jan Wieck) writes:

Thomas Lockhart wrote:

Those who live in HP houses should not throw stones :))

Huh? Up to HPUX-9 they used to have BSD-FFS - even if it was
a 4.2 BSD one - no?

Yeah, the standard HPUX filesystem is still BSD ... and it still runs
rings around Linux extfs2 in my experience. (I've been informed that
Linux has better filesystems than extfs2, but that seems to be what
the average Linux user is running.) I have a realtime data collection
program that usually wants to write several thousand small files during
shutdown. The shutdown typically takes about 3 minutes on an HP 715/75,
upwards of 10 minutes on a Linux box with nominally-faster hardware.

BTW, HP is trying to sell people on using a new journaling filesystem
that they claim outperforms BSD, but my few experiments with it
haven't encouraged me to pursue it.

regards, tom lane

Oh. I was recalling SQL_TEXT as being a "subset" character set which
contains only the characters (more or less) that are required for
implementing the SQL92 query language and standard features.

Are you seeing it as being a "superset" character set which can
represent all other character sets??

Yes, it's my understanding from the section 19.3.1 of Date's book
(fourth edition). Please correct me if I am wrong.

And, how would you suggest we start tracking this discussion in a design
document? I could put something into the developer's guide, or we could
have a plain-text FAQ, or ??

I'd propose that we start accumulating a feature list, perhaps ordering
it into categories like

o required/suggested by SQL9x
o required/suggested by experience in the real world
o sure would be nice to have
o really bad idea ;)

Sounds good. Could I put "CREATE CHARACTER SET" as the first item of
the list and start a discussion for that?

I have a feeling that you have an idea to treat user defined charset
as a PostgreSQL new data type. So probably "CREATE CHARACTER SET"
could be traslated to our "CREATE TYPE" by the parer, right?
--
Tatsuo Ishii

On Sun, 18 Jun 2000, Jan Wieck wrote:
...

ALTER TABLESPACE <tsname> ADD DATAFILE ...

which is a fairly seldom command, issued usually by the DB
admin (at least it requires admin privileges) and thus
ensures the "admin is there and already paying attention". A
little detail not to underestimate IMHO.

...
Esp. in the R/3 area this will become no longer be true the more commonly
commands like "AUTOEXTEND" and "RESIZE" are used (automated at worst).

Bye!
----
Michael Reifenberger
^.*Plaut.*$, IT, R/3 Basis, GPS

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

OK, I have an extent idea. It is:

CREATE LOCATION tabloc IN '/var/private/pgsql' EXTENT2
'/usr/pg'.

This creates an /extents directory in the location, with extents/2
symlinked to /usr/pg:

data/base/mydb/tabloc
data/base/mydb/tabloc/extents/2

When extending a table, it looks for an extents/2 directory and uses
that if it exists. Same for extents3. We could even get fancy and
round-robin through all the extents directories, looping around to the
beginning when we run out of them. That sounds nice.

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

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

OK, I have an extent idea. It is:

CREATE LOCATION tabloc IN '/var/private/pgsql' EXTENT2
'/usr/pg'.

Even better:

CREATE LOCATION tabloc IN '/var/private/pgsql'
EXTENT '/usr/pg', '/usr1/pg'

This will create extent/2 and extent/3, and the system can rotate
extents between the primary storage area, and 2 and 3.

Also, CREATE INDEX will need a location specification added.

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

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

... We could even get fancy and
round-robin through all the extents directories, looping around to the
beginning when we run out of them. That sounds nice.

That sounds horrible. There's no way to tell which extent directory
extent N goes into except by scanning the location directory to find
out how many extent subdirectories there are (so that you can compute
N modulo number-of-directories). Do you want to pay that price on every
file open?

Worse, what happens when you add another extent directory? You can't
find your old extents anymore, that's what, because they're not in the
right place (N modulo number-of-directories just changed). Since the
extents are presumably on different volumes, you're talking about
physical file moves to get them where they should be. You probably
can't add a new extent without shutting down the entire database while
you reshuffle files --- at the very least you'd need to get exclusive
locks on all the tables in that tablespace.

Also, you'll get filename conflicts from multiple extents of a single
table appearing in one of the recycled extent dirs. You could work
around it by using the non-modulo'd N as part of the final file name,
but that just adds more complexity and makes the filename-generation
machinery that much more closely tied to this specific way of doing
things.

The right way to do this is that extent N goes into extents subdirectory
N, period. If there's no such subdirectory, create one on-the-fly as a
plain subdirectory of the location directory. The dbadmin can easily
create secondary extent symlinks *in advance of their being needed*.
Reorganizing later is much more painful since it requires moving
physical files, but I think that'd be true no matter what. At least
we should see to it that adding more space in advance of needing it is
painless.

It's possible to do it that way (auto-create extent subdir if needed)
without tying the md.c machinery real closely to a specific filename
creation procedure: it's just the same sort of thing as install programs
customarily do. "If you fail to create a file, try creating its
ancestor directory." We'd have to think about whether it'd be a good
idea to allow auto-creation of more than one level of directory; offhand
it seems that needing to make more than one level is probably a sign of
an erroneous path, not need for another extent subdirectory.

regards, tom lane

If we eliminate the round-robin idea, what did people think of the rest
of the ideas?

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

... We could even get fancy and
round-robin through all the extents directories, looping around to the
beginning when we run out of them. That sounds nice.

That sounds horrible. There's no way to tell which extent directory
extent N goes into except by scanning the location directory to find
out how many extent subdirectories there are (so that you can compute
N modulo number-of-directories). Do you want to pay that price on every
file open?

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

Not to mention the reverse: if I read this right, you have to suck
up your GB's long in advance of actually needing them. That's OK
for a machine that's dedicated to Oracle ... not so OK for smaller
installations, playpens, etc.

To me it looks like a way to make Oracle work on VMS machines. This is the way
files are allocated on Digital hardware.

Agreed.

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

At 06:50 PM 6/18/00 -0400, Bruce Momjian wrote:

If we eliminate the round-robin idea, what did people think of the rest
of the ideas?

Why invent new syntax when "create tablespace" is something a lot
of folks will recognize?

And why not use "create table ... using ... "? In other words,
Oracle-compatible for this construct? Sure, Postgres doesn't
have to follow Oraclisms but picking an existing contruct means
at least SOME folks can import a datamodel without having to
edit it.

Does your proposal break the smgr abstraction, i.e. does it
preclude later efforts to (say) implement an (optional)
raw-device storage manager?

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

At 06:50 PM 6/18/00 -0400, Bruce Momjian wrote:

If we eliminate the round-robin idea, what did people think of the rest
of the ideas?

Why invent new syntax when "create tablespace" is something a lot
of folks will recognize?

And why not use "create table ... using ... "? In other words,
Oracle-compatible for this construct? Sure, Postgres doesn't
have to follow Oraclisms but picking an existing contruct means
at least SOME folks can import a datamodel without having to
edit it.

Sure, use another syntax. My idea was to use symlinks, and allow their
moving using symlinks and preserve them during dump.

Does your proposal break the smgr abstraction, i.e. does it
preclude later efforts to (say) implement an (optional)
raw-device storage manager?

Seeing very few want that done, I don't see it as an issue at this
point.

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

At 08:08 PM 6/18/00 -0400, Bruce Momjian wrote:

Does your proposal break the smgr abstraction, i.e. does it
preclude later efforts to (say) implement an (optional)
raw-device storage manager?

Seeing very few want that done, I don't see it as an issue at this
point.

Sorry, I disagree. There's excuse for breaking existing abstractions
unless there's a compelling reason to do so.

My question should make it clear I was using a raw-device storage
manager as an example. There are other possbilities, like a
many-tables-per-file storage manager.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

JanWieck@t-online.de (Jan Wieck) writes:

Thomas Lockhart wrote:

Those who live in HP houses should not throw stones :))

Huh? Up to HPUX-9 they used to have BSD-FFS - even if it was
a 4.2 BSD one - no?

Yeah, the standard HPUX filesystem is still BSD ... and it still runs
rings around Linux extfs2 in my experience. (I've been informed that
Linux has better filesystems than extfs2, but that seems to be what
the average Linux user is running.) I have a realtime data collection
program that usually wants to write several thousand small files during
shutdown. The shutdown typically takes about 3 minutes on an HP 715/75,
upwards of 10 minutes on a Linux box with nominally-faster hardware.

BTW, HP is trying to sell people on using a new journaling filesystem
that they claim outperforms BSD, but my few experiments with it
haven't encouraged me to pursue it.

You should really try the BSD4.4 FFS with soft updates. It re-orders
disk flushes to greatly improve performance. It really is great.

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

On Sun, Jun 18, 2000 at 05:12:22PM -0700, Don Baccus wrote:

At 08:08 PM 6/18/00 -0400, Bruce Momjian wrote:

Does your proposal break the smgr abstraction, i.e. does it
preclude later efforts to (say) implement an (optional)
raw-device storage manager?

Seeing very few want that done, I don't see it as an issue at this
point.

Sorry, I disagree. There's excuse for breaking existing abstractions
unless there's a compelling reason to do so.

My question should make it clear I was using a raw-device storage
manager as an example. There are other possbilities, like a
many-tables-per-file storage manager.

Don, I see Bruce's proposal as implementation details within the sotrage
manager. In fact, we should probably implement the tablespace commands
with an extention of the smgr api. One different smgr I've been thinking
a little about is the persistent RAM smgr: I've heard there's some
new technologies coming up that may make large amounts cheaper, soon.
And there's always PostgreSQL for PalmOS, right? (Hey, IBM's got a Pocket
DB2, why shouldn't we?)

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005

At 08:08 PM 6/18/00 -0400, Bruce Momjian wrote:

Does your proposal break the smgr abstraction, i.e. does it
preclude later efforts to (say) implement an (optional)
raw-device storage manager?

Seeing very few want that done, I don't see it as an issue at this
point.

Sorry, I disagree. There's excuse for breaking existing abstractions
unless there's a compelling reason to do so.

My question should make it clear I was using a raw-device storage
manager as an example. There are other possbilities, like a
many-tables-per-file storage manager.

I agree it is nice to keep things as abstract as possible. I just don't
know if the abstraction will cause added complexity.

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

My basic proposal is that we optionally allow symlinks when creating
tablespace directories, and that we interrogate those symlinks during a
dump so administrators can move tablespaces around without having to
modify environment variables or system tables.

I also suggested creating an extent directory to hold extents, like
extent/2 and extent/3. This will allow administration for smaller sites
to be simpler.

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

At 11:13 PM 6/18/00 -0400, Bruce Momjian wrote:

My basic proposal is that we optionally allow symlinks when creating
tablespace directories, and that we interrogate those symlinks during a
dump so administrators can move tablespaces around without having to
modify environment variables or system tables.

If they can move them around from within the db, they'll have no need to
move them around from outside the db.

I don't quite understand your devotion to using filesystem commands
outside the database to do database administration.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

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

I also suggested creating an extent directory to hold extents, like
extent/2 and extent/3. This will allow administration for smaller sites
to be simpler.

I don't see the value in creating an extra level of directory --- seems
that just adds one more Unix directory-lookup cycle to each file open,
without any apparent return. What's wrong with extent directory names
like extent2, extent3, etc?

Obviously the extent dirnames must be chosen so they can't conflict
with table filenames, but that's easily done. For example, if table
files are named like 'OID_xxx' then 'extentN' will never conflict.

regards, tom lane

Don Baccus <dhogaza@pacifier.com> writes:

If they can move them around from within the db, they'll have no need to
move them around from outside the db.
I don't quite understand your devotion to using filesystem commands
outside the database to do database administration.

Being *able* to use filesystem commands to see/fix what's going on is a
good thing, particularly from a development/debugging standpoint. But
I agree we want to have within-the-system admin commands to do the same
things.

regards, tom lane

At 12:28 AM 6/19/00 -0400, Tom Lane wrote:

Being *able* to use filesystem commands to see/fix what's going on is a
good thing, particularly from a development/debugging standpoint.

Of course it's a crutch for development, but outside of development
circles few users will know how to use the OS in regard to the
database.

Assuming PG takes off. Of course, if it remains the realm of the
dedicated hard-core hacker, I'm wrong.

I have nothing against preserving the ability to use filesystem
commands if there's no significant costs inherent with this approach.
I'd view the breaking of smgr abstraction as a significant cost (though
I agree with Ross that it Bruce's proposal shouldn't require that, I
asked my question to flush Bruce out, if you will, because he's
devoted to a particular outside-the-db management model).

But
I agree we want to have within-the-system admin commands to do the same
things.

MUST have, I should think.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

Don Baccus <dhogaza@pacifier.com> writes:

If they can move them around from within the db, they'll have no need to
move them around from outside the db.
I don't quite understand your devotion to using filesystem commands
outside the database to do database administration.

Being *able* to use filesystem commands to see/fix what's going on is a
good thing, particularly from a development/debugging standpoint. But
I agree we want to have within-the-system admin commands to do the same
things.

Yes, I like to have db commands to do it. I just like to allow things
outside too, if possible. It also prevents things from getting out of
sync because the database doesn't need to store the symlink location.

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

Don Baccus <dhogaza@pacifier.com> writes:

I'd view the breaking of smgr abstraction as a significant cost

Actually, the "smgr abstraction" has *been* broken for a long time,
due to sloppy implementation of features like relation rename.
But I agree we should try to re-establish a clean separation.

But
I agree we want to have within-the-system admin commands to do the same
things.

MUST have, I should think.

No argument from this quarter. It seems to me that once a PG
installation has been set up, it ought to be possible to do routine
admin tasks remotely --- and that means no direct access to the
server's filesystem.

regards, tom lane

BTW, schemas do make things interesting for the other camp:
is it possible for the same table to be referenced by different
names in different schemas? If so, just how useful is it to pick
one of those names arbitrarily for the filename? This is an advanced
version of the main objection to using the original relname and not
updating it at RENAME TABLE --- sooner or later, the filenames are
going to be more confusing than helpful.

Comments? Have I missed something important about schemas?

I think we have to agree on the way we want schemas to be.
Imho (and in other db's) the schema is simply the owner of a table.

The owner is an optional part of the table keyword ( select * from
"owner".tabname ).
It also implys that different owners can have a table with the same name
in the same database. (this is only implemented in some other db Systems)

Our database concept is and imho should not be altered, thus we keep the
hierarchy dbname --> owner(=schema) --> tablename.

Andreas

OK, to get back to the point here: so in Oracle, tables can't cross
tablespace boundaries,

This is only true if you don't insert more coins and buy the Partitioning
Option,
or you use those coins to switch to Informix.

but a tablespace itself could span multiple
disks?

Yes

Not sure if I like that better or worse than equating a tablespace
with a directory (so, presumably, all the files within it live on
one filesystem) and then trying to make tables able to span
tablespaces. We will need to do one or the other though, if we want
to have any significant improvement over the current state of affairs
for large tables.

You can currently use a union all view and write appropriate rules
for insert, update and delete in Postgresql. This has the only disadvantage,
that Partitions (fragments, table parts) cannot be optimized away,
but we could fix that if we fixed the optimizer to take check constraints
into account (like check (year = 2000) and select * where year=1999).

Andreas

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:

Please add my opinion for naming rule.

relname/unique_id but need some work new

pg_class column,

no relname change. for unique-id generation

filename not relname

Why is a unique ID better than --- or even different from ---
using the relation's OID? It seems pointless to me...

just to open up a whole new bucket of worms here, but ... if
we do use OID
(which up until this thought I endorse 100%) ... do we not
run a risk if
we run out of OIDs? As far as I know, those are still a
finite resource,
no?

or, do we just assume that by the time that comes, everyone
will be pretty
much using 64bit machines? :)

I think the idea is to have an option to remove oid's from
user tables. I don't think you will run out of oid's if you have your bulk
data
not use up oid's.

Andreas

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

I also suggested creating an extent directory to hold extents, like
extent/2 and extent/3. This will allow administration for smaller sites
to be simpler.

I don't see the value in creating an extra level of directory --- seems
that just adds one more Unix directory-lookup cycle to each file open,
without any apparent return. What's wrong with extent directory names
like extent2, extent3, etc?

Obviously the extent dirnames must be chosen so they can't conflict
with table filenames, but that's easily done. For example, if table
files are named like 'OID_xxx' then 'extentN' will never conflict.