Thread: Well, then you keep your darn columns

Let me thank all of those that spoke up in my support and let me tell of
those that were unhappy that I _will_ be here tomorrow as well. To
summarize the points and add a few of my own:

1) This is a TODO item.

2) I have reviewed several mutterings about how to implement this in the
archives and followed the consensus that you need to copy the table over
somehow. It's not like I made this up.

2a) Does anyone have a better idea? (Btw., I'm not too excited about
by-passing the storage manager and writing around in the table file on
disk. If vacuum does that, that doesn't mean it's the right thing to do.)

3) This isn't release software.

4) This isn't done. (But it will be.)

4a) If it won't get done then I add one line and it's disabled. I'm not
that dumb.

5) This isn't documented, so if you don't call the command then your life
goes on.

6) Users have been begging for this but nobody else has moved a finger.

7) If you are concerned about "perfect" implementation, then I invite you
to take a look at the create/drop user and create/drop database code from
6.5 and thank whomever you do thank that your database isn't fried yet.

8) Now that I know how to keep the oids around, they will be kept around.
(Thanks to those that interpreted my message as a starting point for a
discussion and not me laying down the law.)

9) What really gets me though is what your problem is. This is a nearly
SQL-compliant implementation of a very important feature. It doesn't
affect the rest of the code. It doesn't break the regression tests. It
checks for permissions, validity of parameters, etc. and even if it goes
wrong, it doesn't fry your database or any part of it.


Um, anyway, I'm open for implementation specific suggestions. I don't like
the coying either but it works.

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

As a user let me just say that I have been waiting for all the
'ALTER TABLE..' commands for a long time (changing constraints would be great
too!) and it has been mentioned many times that one should not use oids in
application code. The way I do it at the moment is to copy the table, so I
think all users are used to losing their oids when remocing a column from a
table. So while it is certainly better to keep the oids, Peter's code is a
big improvement on the current situation.

While I do understand and subscribe to the concerns about database
reliability, I would very much not like to keep my darn columns and thank
Peter for helping me get rid of them.

Adriaan

On Mon, 24 Jan 2000, Adriaan Joubert wrote:

> As a user let me just say that I have been waiting for all the
> 'ALTER TABLE..' commands for a long time (changing constraints would be great
> too!) and it has been mentioned many times that one should not use oids in
> application code. The way I do it at the moment is to copy the table, so I
> think all users are used to losing their oids when remocing a column from a
> table. So while it is certainly better to keep the oids, Peter's code is a
> big improvement on the current situation.

Except, as Chris Bitmead brought up, OIDs appear to be a key requirement
in ODBMSs ... so, if we want to go what I *think* is 'next generation',
OIDs have to be kept ...

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

> Let me thank all of those that spoke up in my support and let me tell of
> those that were unhappy that I _will_ be here tomorrow as well. To
> summarize the points and add a few of my own:
> 
> 1) This is a TODO item.
> 
> 2) I have reviewed several mutterings about how to implement this in the
> archives and followed the consensus that you need to copy the table over
> somehow. It's not like I made this up.

Yes, as Peter pointed out, he did exactly what I suggested in my e-mail
when he brought up the issue.  I don't even remember sending the
e-mail, so it must have been some time ago, 25 Nov 1999.

> 
> 2a) Does anyone have a better idea? (Btw., I'm not too excited about
> by-passing the storage manager and writing around in the table file on
> disk. If vacuum does that, that doesn't mean it's the right thing to do.)

I totally agree that bypassing the storage manager is the wrong way to
go with this.  All the command/*.c stuff is make to be clean, not fast. 
It is better to put something together that works rather than optimize
things like add user or create database.

Now, I will admit the ALTER DROP is going take much longer than most
command/*.c, so it may be worth it some day to try and do this, but I
don't see this as a priority at this point.  We have many other items to
work on that are more important.


> 6) Users have been begging for this but nobody else has moved a finger.

Totally true.

> 
> 7) If you are concerned about "perfect" implementation, then I invite you
> to take a look at the create/drop user and create/drop database code from
> 6.5 and thank whomever you do thank that your database isn't fried yet.

Yes, that stuff is a mess, and Peter has cleaned it up quite a bit.  And
I have already asked him about CLUSTER, which has serious problems.


> 8) Now that I know how to keep the oids around, they will be kept around.
> (Thanks to those that interpreted my message as a starting point for a
> discussion and not me laying down the law.)

Yes, it seems passing in the oid as part of heap_insert will do a good
job for us in a few other areas like when we want to modify the oid of a
tuple.  Withouth that, we if you delete a tuple, you can't add it back
in with the same oid.  That is pretty bad.

> 
> 9) What really gets me though is what your problem is. This is a nearly
> SQL-compliant implementation of a very important feature. It doesn't
> affect the rest of the code. It doesn't break the regression tests. It
> checks for permissions, validity of parameters, etc. and even if it goes
> wrong, it doesn't fry your database or any part of it.

Yes, I am still totally confused.  Let's hope it is just an aberation.

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

> As a user let me just say that I have been waiting for all the
> 'ALTER TABLE..' commands for a long time (changing constraints would be great
> too!) and it has been mentioned many times that one should not use oids in
> application code. The way I do it at the moment is to copy the table, so I
> think all users are used to losing their oids when remocing a column from a
> table. So while it is certainly better to keep the oids, Peter's code is a
> big improvement on the current situation.
> 

What happens if the dropped column is part of an index?  Just curious.

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

> -----Original Message-----
> From: owner-pgsql-hackers@postgreSQL.org 
> [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Peter Eisentraut
> 
> Let me thank all of those that spoke up in my support and let me tell of
> those that were unhappy that I _will_ be here tomorrow as well. To
> summarize the points and add a few of my own:
> 
> 1) This is a TODO item.
> 
> 2) I have reviewed several mutterings about how to implement this in the
> archives and followed the consensus that you need to copy the table over
> somehow. It's not like I made this up.
> 
> 2a) Does anyone have a better idea? (Btw., I'm not too excited about
> by-passing the storage manager and writing around in the table file on
> disk. If vacuum does that, that doesn't mean it's the right thing to do.)
>

I propose another implementation here. I don't think this is so
important a feature. I'm only afraid of forced implementation
especially using copy() and rename() for such a feature. 

My idea is as follows.

1)add a visibile/invisible flag to pg_attribute
2)DROP COLUMN marks the column as invisible
3)user interface ignores the columns which are marked invisible
4)heap_formtuple() etc treats the column as NULL internally

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

> My idea is as follows.
> 
> 1)add a visibile/invisible flag to pg_attribute
> 2)DROP COLUMN marks the column as invisible
> 3)user interface ignores the columns which are marked invisible
> 4)heap_formtuple() etc treats the column as NULL internally
> 

Yes, but how much code is that going to hit?  Seems it would be a lot.

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

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> My idea is as follows.

> 1)add a visibile/invisible flag to pg_attribute
> 2)DROP COLUMN marks the column as invisible
> 3)user interface ignores the columns which are marked invisible
> 4)heap_formtuple() etc treats the column as NULL internally

That could be a really good idea.  I don't think you'd even need to
touch heap_formtuple (and it'd be better not to mess with the guts
of the system to implement this feature, for both speed and reliability
reasons).

Let's see: DROP COLUMN would have to mark the column invisible, remove
any associated constraints (particularly NOT NULL) and indexes, and
it'd be done.  The parser would then have to ignore the column when
doing column name lookups or expansion of '*', and it would have to
insert a NULL value for the column when transforming INSERT or UPDATE.
And that'd be just about it.  I like it.

The only drawback of this scheme is that the space occupied by the
deleted column wouldn't go away immediately (in any given tuple,
it'd be reclaimed on the next UPDATE of the tuple).  On the other hand,
you could construe that as a feature --- you don't have to wait around
for a DROP COLUMN to finish.  Anyone who did want to reclaim space
immediately could doUPDATE table SET someothercolumn = someothercolumn;
followed by a VACUUM.  But I bet a lot of people would be just as
happy to let it happen in background.
        regards, tom lane

Tom Lane wrote:

> Let's see: DROP COLUMN would have to mark the column invisible, remove
> any associated constraints (particularly NOT NULL) and indexes, and
> it'd be done.  The parser would then have to ignore the column when
> doing column name lookups or expansion of '*', and it would have to
> insert a NULL value for the column when transforming INSERT or UPDATE.
> And that'd be just about it.  I like it.

How would you handle multi-column indices that included the column
being dropped?  E.g.,
create unique index foobar on mytable(foo,bar);

where the 'bar' column is then dropped...

Dropping all of that index would seem to be problematic.

Cheers,
Ed Loehr

Ed Loehr <eloehr@austin.rr.com> writes:
> How would you handle multi-column indices that included the column
> being dropped?  E.g.,
>     create unique index foobar on mytable(foo,bar);
> where the 'bar' column is then dropped...

Good question, but I don't think we had an answer for it in the other
scheme either.

In Hiroshi's scheme it seems like it might "just work" anyway: the index
would still be there, it'd just start filling with all nulls in the bar
column.  AFAIR, a unique index won't complain about that under SQL92
rules.

On the whole it might be better to refuse to do the DROP COLUMN until
the user gets rid of such an index.  It would certainly be wrong for
us to try to replace the index withcreate unique index foobar on mytable(foo);
since the original index did *not* imply uniqueness on foo alone.

Similarly, I'd be very strongly inclined to punt if we find any
constraints that mention both the target column and other columns.
We cannot fix those automatically, and silently dropping them doesn't
sound good either.  Make the user do something with them, instead.
        regards, tom lane

> Let's see: DROP COLUMN would have to mark the column invisible, remove
> any associated constraints (particularly NOT NULL) and indexes, and
> it'd be done.  The parser would then have to ignore the column when
> doing column name lookups or expansion of '*', and it would have to
> insert a NULL value for the column when transforming INSERT or UPDATE.
> And that'd be just about it.  I like it.

On further reflection I can think of a few other places that would have
to be taught to skip over "invisible" columns: COPY and pg_dump would,
and probably there are some others.  But it still seems like this is
a simple and robust scheme with considerable advantages, and many
fewer "I'm not sure how to do that" gaps in it.
        regards, tom lane

On Mon, 24 Jan 2000, Tom Lane wrote:

> "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> > My idea is as follows.
> 
> > 1)add a visibile/invisible flag to pg_attribute
> > 2)DROP COLUMN marks the column as invisible
> > 3)user interface ignores the columns which are marked invisible
> > 4)heap_formtuple() etc treats the column as NULL internally
> 
> That could be a really good idea.  I don't think you'd even need to
> touch heap_formtuple (and it'd be better not to mess with the guts
> of the system to implement this feature, for both speed and reliability
> reasons).
> 
> Let's see: DROP COLUMN would have to mark the column invisible, remove
> any associated constraints (particularly NOT NULL) and indexes, and
> it'd be done.  The parser would then have to ignore the column when
> doing column name lookups or expansion of '*', and it would have to
> insert a NULL value for the column when transforming INSERT or UPDATE.
> And that'd be just about it.  I like it.
> 
> The only drawback of this scheme is that the space occupied by the
> deleted column wouldn't go away immediately (in any given tuple,
> it'd be reclaimed on the next UPDATE of the tuple).  On the other hand,
> you could construe that as a feature --- you don't have to wait around
> for a DROP COLUMN to finish.  Anyone who did want to reclaim space
> immediately could do
>     UPDATE table SET someothercolumn = someothercolumn;
> followed by a VACUUM.  But I bet a lot of people would be just as
> happy to let it happen in background.

Hey Bruce ... Look here ^^^^ :)

Oh, there is a second drawback to it though ...

DROP COLUMN name
ADD COLUMN name <of a different type>

Then what? :(

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

At 11:48 AM 1/24/00 -0500, Bruce Momjian wrote:

>> 2a) Does anyone have a better idea? (Btw., I'm not too excited about
>> by-passing the storage manager and writing around in the table file on
>> disk. If vacuum does that, that doesn't mean it's the right thing to do.)

>I totally agree that bypassing the storage manager is the wrong way to
>go with this.  All the command/*.c stuff is make to be clean, not fast. 
>It is better to put something together that works rather than optimize
>things like add user or create database.

>Now, I will admit the ALTER DROP is going take much longer than most
>command/*.c, so it may be worth it some day to try and do this, but I
>don't see this as a priority at this point.  We have many other items to
>work on that are more important.

Also, by-passing the storage manager would make it more difficult to
replace it with another, for instance a storage manager based on raw
disk I/O, which some folks seem interested in.  Though I don't count
myself in that class, it seems like the storage manager abstraction
has been preserved in order to simplify alternative approaches if
folks want to add them in the future, and it seems wrong to by-pass
that layer of abstraction.



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

At 12:13 PM 1/24/00 -0600, Ed Loehr wrote:
>Tom Lane wrote:
>
>> Let's see: DROP COLUMN would have to mark the column invisible, remove
>> any associated constraints (particularly NOT NULL) and indexes, and
>> it'd be done.  The parser would then have to ignore the column when
>> doing column name lookups or expansion of '*', and it would have to
>> insert a NULL value for the column when transforming INSERT or UPDATE.
>> And that'd be just about it.  I like it.
>
>How would you handle multi-column indices that included the column
>being dropped?  E.g.,
>
>    create unique index foobar on mytable(foo,bar);
>
>where the 'bar' column is then dropped...
>
>Dropping all of that index would seem to be problematic.

Hmmm...dropping the index is what Oracle does, or so claims their
documentation.  It makes sense because getting rid of "bar"
may well mean that the uniquness constraint will no longer be
satisfied, right?  In fact, odds are it won't for a multi-column
index.  Anyway, Oracle drops all indices which reference the 
column.

Also, it turns out that "drop column" in Oracle does reclaim the
space occupied by the data, but there's a "set unused" variant that
does EXACTLY what's being talked about - i.e. marks the column as
unused and makes it invisible to queries.

Interesting.



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

At 12:53 PM 1/24/00 -0500, Tom Lane wrote:
>"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
>> My idea is as follows.
>
>> 1)add a visibile/invisible flag to pg_attribute
>> 2)DROP COLUMN marks the column as invisible
>> 3)user interface ignores the columns which are marked invisible
>> 4)heap_formtuple() etc treats the column as NULL internally
>
>That could be a really good idea.

I'd been thinking along these lines as a possibility, too, but 
didn't want to rock the boat any more than I've done already.  Now
that it's on the table, though...

>Let's see: DROP COLUMN would have to mark the column invisible, remove
>any associated constraints (particularly NOT NULL) and indexes, and
>it'd be done.  The parser would then have to ignore the column when
>doing column name lookups or expansion of '*', and it would have to
>insert a NULL value for the column when transforming INSERT or UPDATE.
>And that'd be just about it.  I like it.

Yep.  

>The only drawback of this scheme is that the space occupied by the
>deleted column wouldn't go away immediately (in any given tuple,
>it'd be reclaimed on the next UPDATE of the tuple).  On the other hand,
>you could construe that as a feature --- you don't have to wait around
>for a DROP COLUMN to finish.  Anyone who did want to reclaim space
>immediately could do
>    UPDATE table SET someothercolumn = someothercolumn;
>followed by a VACUUM.

Or perhaps vacuum could be made smart enough to remove deleted columns?
Perhaps optionally?

>  But I bet a lot of people would be just as happy to let it happen
>  in background.

Well...one reason why this idea arose in my mind is because I'm actually
doing it for a web-based table definer that's part of the (tiresomely
overmentioned) arsDigita Community System.  It lets you add and drop
columns via the web (you're actually defining auxillary tables used
by some canned code so it's not quite as weird as it may sound).  I
simply enforce that user-defined table names begin with a character,
and when a user "drops" a column rename it to an "illegal" name in
a special form, which the rest of the web interface simply doesn't
display.  The user can't tell that the columns not really dropped,
maintaining the illusion that the system's 100% compatible with the
Oracle-based version.

It works just great for this application, though it's a royal kludge.



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

At 03:44 PM 1/24/00 -0400, The Hermit Hacker wrote:

>Oh, there is a second drawback to it though ...
>
>DROP COLUMN name
>ADD COLUMN name <of a different type>
>
>Then what? :(

I don't understand...the idea is to make the old column name
invisible, and therefore "add column" won't see it either.



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

On Mon, 24 Jan 2000, Don Baccus wrote:

> At 03:44 PM 1/24/00 -0400, The Hermit Hacker wrote:
> 
> >Oh, there is a second drawback to it though ...
> >
> >DROP COLUMN name
> >ADD COLUMN name <of a different type>
> >
> >Then what? :(
> 
> I don't understand...the idea is to make the old column name
> invisible, and therefore "add column" won't see it either.

so the pg_* file that maintains the 'fields' in a table would have two
fields of the same name, one enabled, one disabled?

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

Don Baccus wrote:
> 
> >How would you handle multi-column indices that included the column
> >being dropped?  E.g.,
> >
> >       create unique index foobar on mytable(foo,bar);
> >
> >where the 'bar' column is then dropped...
> 
> ...  Oracle drops all indices which reference the column.

Seems like a new 'gotcha'... either way, informative warnings/notices
would be nice.

Along these same lines, how would pre-existing functions that
referenced the just-dropped column be handled?  I'm thinking of
PL/pgSQL...


Cheers,
Ed Loehr

On Mon, 24 Jan 2000, Don Baccus wrote:

> >you could construe that as a feature --- you don't have to wait around
> >for a DROP COLUMN to finish.  Anyone who did want to reclaim space
> >immediately could do
> >    UPDATE table SET someothercolumn = someothercolumn;
> >followed by a VACUUM.
> 
> Or perhaps vacuum could be made smart enough to remove deleted columns?
> Perhaps optionally?

when bruce and I discussed this, that was one thing we both agreed upon
... Vacuum is too slow as it is, let alone adding in more things for it to
do :(


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

At 04:23 PM 1/24/00 -0400, The Hermit Hacker wrote:

>so the pg_* file that maintains the 'fields' in a table would have two
>fields of the same name, one enabled, one disabled?

Or perhaps the name is set to null, whatever.



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

At 02:26 PM 1/24/00 -0600, Ed Loehr wrote:
>Don Baccus wrote:
>> 
>> >How would you handle multi-column indices that included the column
>> >being dropped?  E.g.,
>> >
>> >       create unique index foobar on mytable(foo,bar);
>> >
>> >where the 'bar' column is then dropped...
>> 
>> ...  Oracle drops all indices which reference the column.

>Seems like a new 'gotcha'... either way, informative warnings/notices
>would be nice.

I'm not saying that Oracle's "right", I just offer it as one datapoint.
I have access to an Oracle installation, so it's easy enough for me to
try things out.

If dropping the index were decided upon, a notice would be nice, yes.
Or, as Tom suggested, making the user drop relevant indices by hand
first as a safeguard.

>Along these same lines, how would pre-existing functions that
>referenced the just-dropped column be handled?  I'm thinking of
>PL/pgSQL...

I presume they'd fail just like any client software accessing those
columns via libpq queries, query files fed to psql, etc.  Dropping
a column is something you don't want to do blithely in an existing,
complex application, that's for sure!



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

> > The only drawback of this scheme is that the space occupied by the
> > deleted column wouldn't go away immediately (in any given tuple,
> > it'd be reclaimed on the next UPDATE of the tuple).  On the other hand,
> > you could construe that as a feature --- you don't have to wait around
> > for a DROP COLUMN to finish.  Anyone who did want to reclaim space
> > immediately could do
> >     UPDATE table SET someothercolumn = someothercolumn;
> > followed by a VACUUM.  But I bet a lot of people would be just as
> > happy to let it happen in background.
> 
> Hey Bruce ... Look here ^^^^ :)
> 
> Oh, there is a second drawback to it though ...
> 
> DROP COLUMN name
> ADD COLUMN name <of a different type>
> 
> Then what? :(

Double-yikes.  There goes that idea, or does it?  Attributes are
numbered.  How does a missing attribute get handled for new rows?
My guess is that we have to keep this thing around forever.  Can you
imagine having all those user apps tha query pg_attribute supress that
column.  Sound like too much work to me.

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

The Hermit Hacker wrote:
> 
> On Mon, 24 Jan 2000, Adriaan Joubert wrote:
> 
> > As a user let me just say that I have been waiting for all the
> > 'ALTER TABLE..' commands for a long time (changing constraints would be great
> > too!) and it has been mentioned many times that one should not use oids in
> > application code. The way I do it at the moment is to copy the table, so I
> > think all users are used to losing their oids when remocing a column from a
> > table. So while it is certainly better to keep the oids, Peter's code is a
> > big improvement on the current situation.
> 
> Except, as Chris Bitmead brought up, OIDs appear to be a key requirement
> in ODBMSs ... so, if we want to go what I *think* is 'next generation',
> OIDs have to be kept ...

But the decision was (from Vadim IIRC) to drop them, at least in non system
tables.
The cited reasons were:
* crappy implementation that taxed performance (probably fixed by now)
* nobody else seemed to have them and the push then was to the direction of mainstream bean-counting DB with main
objectiveof getting that base functionality right.
 
* they take up "too much" space (probably a non-issue in current world of dropping disk/memory prices)
* you can always re-implement them at the application level (the same was
cited for dropping time travel) 

If it is now a general opinion that OIDs will remain, it should be stated
somewhere,
as I suspect that much of pg community operates under the impression that they
are 
going away in future as have some other nice but not fully developed features
like
time travel.

I do understand that time travel could be implemented using a bunch of
rules+views
but to be really useful rules views should be much more developed and
integrated 
with inheritance, up to the level where you could say:

create table stock( item_id int, quantity int, price numeric(15,2)
) inherits (time_travel);

and then have all the needed tables/rules/views produced automatically.

If we had that level of sofistication we could also safely drop OIDs as a
built-in 
system feature and still have it by changing the last line to

) inherits (time_travel,odbms_table);



-----------------
Hannu

Bruce Momjian wrote:
> 
> > > The only drawback of this scheme is that the space occupied by the
> > > deleted column wouldn't go away immediately (in any given tuple,
> > > it'd be reclaimed on the next UPDATE of the tuple).  On the other hand,
> > > you could construe that as a feature --- you don't have to wait around
> > > for a DROP COLUMN to finish.  Anyone who did want to reclaim space
> > > immediately could do
> > >     UPDATE table SET someothercolumn = someothercolumn;
> > > followed by a VACUUM.  But I bet a lot of people would be just as
> > > happy to let it happen in background.
> >
> > Hey Bruce ... Look here ^^^^ :)
> >
> > Oh, there is a second drawback to it though ...
> >
> > DROP COLUMN name
> > ADD COLUMN name <of a different type>

or the same type, it will be added at the end anyway.

part of making it invisible should be making its _name_ invisible in pg_*

one way would be to rename it to '\n'+str(next_available_oid) or sone other 
invalid column name.

> >
> > Then what? :(
> 
> Double-yikes.  There goes that idea, or does it?  Attributes are
> numbered.  How does a missing attribute get handled for new rows?

The proposition was to set it always to NULL (takes no additional storage
if there are other null columns, (fieldcnt+31)/8 bytes else.

BTW, the current handling of nulls in storage is a bit weird - the bitvector
for 
null/not null starts at position 31, but if there is a bitmap space is
allocated 
in 4-byte chunks starting at position 32  (at least on linux/x86)

> My guess is that we have to keep this thing around forever.

Or until dump/reload, the renumbering will be automatic there.

> Can you
> imagine having all those user apps tha query pg_attribute supress that
> column.  Sound like too much work to me.

To me it sounds like a thing that _must_ be done at major number change, 
so 7.0 is a good place.

OTOH, we could provide SQL92 ways for getting the info that current user 
apps get by querying pg_attribute.

IIRC it requires a bunch of views on system tables, some of which could be 
lifted straight from psql's \d* commands.

-----------------------------
Hannu

> > Can you
> > imagine having all those user apps tha query pg_attribute supress that
> > column.  Sound like too much work to me.
> 
> To me it sounds like a thing that _must_ be done at major number change, 
> so 7.0 is a good place.
> 
> OTOH, we could provide SQL92 ways for getting the info that current user 
> apps get by querying pg_attribute.
> 
> IIRC it requires a bunch of views on system tables, some of which could be 
> lifted straight from psql's \d* commands.

If this is going to require any significant backend baggage, I say drop
it.  Things are complicated enough.  I did temp tables in one file so we
would not have "I am a temp" and "I am not a temp" floating all over the
backend.  I don't really want "I am not an attribute" around either.

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

At 11:52 PM 1/24/00 +0200, Hannu Krosing wrote:

>But the decision was (from Vadim IIRC) to drop them, at least in non system
>tables.
>The cited reasons were:
>* crappy implementation that taxed performance (probably fixed by now)
>* nobody else seemed to have them and the push then was to the direction of
>  mainstream bean-counting DB with main objective of getting that base
>  functionality right.

Regarding this last, Oracle has an equivalent - rowid.   In the web
toolkit I'm helping port, it's used somewhat often, and having oid
available has been a convenience.

Having said that, its use in this toolkit's could be replaced by 
simply creating a sequence and numbering rows by hand.  Their loss
wouldn't bother me particularly even though it would add a little
work (not much) to this project.

>* they take up "too much" space (probably a non-issue in current world of
>  dropping disk/memory prices)
>* you can always re-implement them at the application level (the same was
>cited
>  for dropping time travel) 

Yep!



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

Don Baccus wrote:
> 
> At 11:52 PM 1/24/00 +0200, Hannu Krosing wrote:
> 
> >But the decision was (from Vadim IIRC) to drop them, at least in non system
> >tables.
> >The cited reasons were:
> >* crappy implementation that taxed performance (probably fixed by now)
> >* nobody else seemed to have them and the push then was to the direction of
> >  mainstream bean-counting DB with main objective of getting that base
> >  functionality right.
> 
> Regarding this last, Oracle has an equivalent - rowid.   In the web
> toolkit I'm helping port, it's used somewhat often, and having oid
> available has been a convenience.

My impression was thet Oracles ROWID is more like our TID - i.e. not a very 
stable thing. I may be wrong of course, as last time I used oracle seriously 
was more than 3 years ago.

> Having said that, its use in this toolkit's could be replaced by
> simply creating a sequence and numbering rows by hand.

Or using 'default nextid()' which seems to be the recommended and portable (?)
way.

----------------
Hannu

Bruce Momjian wrote:
> 
> > OTOH, we could provide SQL92 ways for getting the info that current user
> > apps get by querying pg_attribute.
> >
> > IIRC it requires a bunch of views on system tables, some of which could be
> > lifted straight from psql's \d* commands.
> 
> If this is going to require any significant backend baggage, I say drop
> it.  Things are complicated enough.  I did temp tables in one file so we
> would not have "I am a temp" and "I am not a temp" floating all over the
> backend.  I don't really want "I am not an attribute" around either.

Possible valid complain true for hidden attributes.

But the ANSI/ISO system table views gould probably done even as a add-on 
package that just creates the views - with nothing in backend.

And promoting them as the default way for finding out about schema would free
us 
from concerns about user-level apps when we need to change internal system
table
structures.

It could at least be added to TODO for 7.x 

-----------------
Hannu

Hiroshi Inoue wrote:
> I propose another implementation here. I don't think this is so
> important a feature. I'm only afraid of forced implementation
> especially using copy() and rename() for such a feature.

Hi all....

Can I just point out that the above is THE RIGHT THING(tm). I havn't
followed this thread from the beginning, but can I point out that
implementing drop column as a copy/rename is not good (better than
nothing I guess).

Take a cue from a database like VERSANT (http://www.versant.com). You
can change the database's schema while the database is running. Time
taken to delete a column is roughly 0.0 seconds no matter how large the
table. Going the whole hog, you keep the old schema available so that
when you retrieve an old tuple it can be updated the next time that
tuple is updated.


> 
> My idea is as follows.
> 
> 1)add a visibile/invisible flag to pg_attribute
> 2)DROP COLUMN marks the column as invisible
> 3)user interface ignores the columns which are marked invisible
> 4)heap_formtuple() etc treats the column as NULL internally
> 
> Regards.
> 
> Hiroshi Inoue
> Inoue@tpf.co.jp
> 
> ************

At 01:02 AM 1/25/00 +0200, Hannu Krosing wrote:

>> Regarding this last, Oracle has an equivalent - rowid.   In the web
>> toolkit I'm helping port, it's used somewhat often, and having oid
>> available has been a convenience.
>
>My impression was thet Oracles ROWID is more like our TID - i.e. not a very 
>stable thing. I may be wrong of course, as last time I used oracle seriously 
>was more than 3 years ago.

Hmmm...maybe so.  I'm not sure, either...porting existing Oracle code
to PostgreSQL by no means makes me an Oracle expert.



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

At 15:44 24/01/00 -0400, The Hermit Hacker wrote:
>On Mon, 24 Jan 2000, Tom Lane wrote:
>
>> "Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
>> > My idea is as follows.
>> 
>> > 1)add a visibile/invisible flag to pg_attribute
>> > 2)DROP COLUMN marks the column as invisible
>> > 3)user interface ignores the columns which are marked invisible
>> > 4)heap_formtuple() etc treats the column as NULL internally
>> 
...etc..
>
>Oh, there is a second drawback to it though ...
>
>DROP COLUMN name
>ADD COLUMN name <of a different type>
>
>Then what? :(

Aren't there two separate issues to be resolved:

1. What happens with meta-data referential integrity - a problem for all
implementations.

2. How is it implemeneted.

For my 0.02c, the meta data integrity issue should be resolved by saying
'drop column' is not allowed if there is any (non-system-generated)
metadata that refers to it. Most people (I think) drop columns because they
have no use for them any longer. If they have indexes, constraints,
triggers etc based on those columns, then the drop should fail, since these
items are a prima-facie case that there is a use for them. Cascading
deletes are a nice thing, but for meta-data they can be quite dangerous.

As far as the implementation is concerned, I like the idea of 'hiding' the
deleted column, but am curious: can it be hidden more effectively so that
the only thing that ever sees it is the part of the code that reads it from
disk?


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: +61-03-5367 7422            |                 _________  \
Fax: +61-03-5367 7430            |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

> Double-yikes.  There goes that idea, or does it?  Attributes are
> numbered.  How does a missing attribute get handled for new rows?
> My guess is that we have to keep this thing around forever.  Can you
> imagine having all those user apps tha query pg_attribute supress that
> column.  Sound like too much work to me.

I don't know the intimate details of the postgres implementation, but I
would have thought every row would need a version number and you would
need to somehow store how the table looked at each version.

Because you could
CREATE TABLE
INSERT
ALTER DROP
INSERT/DELETE
ALTER DROP/ADD
INSERT/DELETE

and you would end up with rows with 5 or 6 different formats.

On Mon, 24 Jan 2000, Bruce Momjian wrote:

> > > The only drawback of this scheme is that the space occupied by the
> > > deleted column wouldn't go away immediately (in any given tuple,
> > > it'd be reclaimed on the next UPDATE of the tuple).  On the other hand,
> > > you could construe that as a feature --- you don't have to wait around
> > > for a DROP COLUMN to finish.  Anyone who did want to reclaim space
> > > immediately could do
> > >     UPDATE table SET someothercolumn = someothercolumn;
> > > followed by a VACUUM.  But I bet a lot of people would be just as
> > > happy to let it happen in background.
> > 
> > Hey Bruce ... Look here ^^^^ :)
> > 
> > Oh, there is a second drawback to it though ...
> > 
> > DROP COLUMN name
> > ADD COLUMN name <of a different type>
> > 
> > Then what? :(
> 
> Double-yikes.  There goes that idea, or does it?  Attributes are

not necessarily, just playing devil's advocate ... :)


> numbered.  How does a missing attribute get handled for new rows?
> My guess is that we have to keep this thing around forever.  Can you
> imagine having all those user apps tha query pg_attribute supress that
> column.  Sound like too much work to me.

I *still* think the best is the "re-write the table in place" method
... we already have most of the logic, I would think, from VACUUM ...

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

> -----Original Message-----
> From: The Hermit Hacker [mailto:scrappy@hub.org]
> 
> > numbered.  How does a missing attribute get handled for new rows?
> > My guess is that we have to keep this thing around forever.  Can you
> > imagine having all those user apps tha query pg_attribute supress that
> > column.  Sound like too much work to me.
> 
> I *still* think the best is the "re-write the table in place" method
> ... we already have most of the logic, I would think, from VACUUM ...
>

AFAIK,there's no such logic in VACUUM.  
Because PostgreSQL has no rollback data separately,we must keep
valid old tuples somewhere(of cource the original place is most natural)
in the table until commit at least.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

On Mon, 24 Jan 2000, The Hermit Hacker wrote:

> On Mon, 24 Jan 2000, Don Baccus wrote:
> 
> > At 03:44 PM 1/24/00 -0400, The Hermit Hacker wrote:
> > 
> > >Oh, there is a second drawback to it though ...
> > >
> > >DROP COLUMN name
> > >ADD COLUMN name <of a different type>
> > >
> > >Then what? :(
> > 
> > I don't understand...the idea is to make the old column name
> > invisible, and therefore "add column" won't see it either.
> 
> so the pg_* file that maintains the 'fields' in a table would have two
> fields of the same name, one enabled, one disabled?

The entries in pg_attribute have oids as well ...

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

The Hermit Hacker wrote:
> 
> > numbered.  How does a missing attribute get handled for new rows?
> > My guess is that we have to keep this thing around forever.  Can you
> > imagine having all those user apps tha query pg_attribute supress that
> > column.  Sound like too much work to me.
> 
> I *still* think the best is the "re-write the table in place" method
> ... we already have most of the logic, I would think, from VACUUM ...

It is much safer with vacuum, as there is no way for vacuum to crash and have 
two different tuple formats left behind (i hope ;-p)

-------------
Hannu

On 2000-01-24, The Hermit Hacker mentioned:

> Except, as Chris Bitmead brought up, OIDs appear to be a key requirement
> in ODBMSs ... so, if we want to go what I *think* is 'next generation',
> OIDs have to be kept ...

Independent of everything else I would like to point out that although
oids do appear in a central role in the theory of object oriented
databases they are still not a user-level feature. The system uses them to
in essence do what some people already do with them now: use them as links
in foreign key settings. This sort of scheme is supposed to eliminate the
need for costly joins, since you already know the location of the data
(assuming that you have a scheme to map the oid to the storage location).

This past summer this sort of idea was discussed around these parts and
most of us came to the conclusion that a) OODBs are a pipe-dream at this
point in time, and b) this is not worth doing in PostgreSQL as it stands.
If we wanna become an OODBs we might as well say that now so we can start
by dropping SQL and the optimizer and the storage manager -- okay, I'm
being sarcastic (about OODBs).

However, once again, users would have no knowledge of these "oids". The
system is free to do whatever it wants in order to do its thing, in
particular it is free to *change* oids when it needs it (because when it
copies the data elsewhere it presumably needs to tag the location
differently).

Our oids are something different (though not sure what), PostgreSQL is
something different. I am by all means against breaking what oids
represent now, but incidentally I am also against them becoming (being) a
user-level feature.

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

Peter,

Are you talking about make OID invisible ?
Please, don't do this. I have a good use of them to move backward and
forward in a set of rows
selected by interactive forms of any table.

Regards,

Ricardo Coelho.

----- Original Message -----
From: Peter Eisentraut <peter_e@gmx.net>
To: The Hermit Hacker <scrappy@hub.org>
Cc: Adriaan Joubert <a.joubert@albourne.com>; <pgsql-hackers@postgreSQL.org>
Sent: Wednesday, January 26, 2000 4:34 PM
Subject: OIDS (Re: [HACKERS] Well, then you keep your darn columns)


> On 2000-01-24, The Hermit Hacker mentioned:
>
> > Except, as Chris Bitmead brought up, OIDs appear to be a key requirement
> > in ODBMSs ... so, if we want to go what I *think* is 'next generation',
> > OIDs have to be kept ...
>
> Independent of everything else I would like to point out that although
> oids do appear in a central role in the theory of object oriented
> databases they are still not a user-level feature. The system uses them to
> in essence do what some people already do with them now: use them as links
> in foreign key settings. This sort of scheme is supposed to eliminate the
> need for costly joins, since you already know the location of the data
> (assuming that you have a scheme to map the oid to the storage location).
>
> This past summer this sort of idea was discussed around these parts and
> most of us came to the conclusion that a) OODBs are a pipe-dream at this
> point in time, and b) this is not worth doing in PostgreSQL as it stands.
> If we wanna become an OODBs we might as well say that now so we can start
> by dropping SQL and the optimizer and the storage manager -- okay, I'm
> being sarcastic (about OODBs).
>
> However, once again, users would have no knowledge of these "oids". The
> system is free to do whatever it wants in order to do its thing, in
> particular it is free to *change* oids when it needs it (because when it
> copies the data elsewhere it presumably needs to tag the location
> differently).
>
> Our oids are something different (though not sure what), PostgreSQL is
> something different. I am by all means against breaking what oids
> represent now, but incidentally I am also against them becoming (being) a
> user-level feature.
>
> --
> Peter Eisentraut                  Sernanders väg 10:115
> peter_e@gmx.net                   75262 Uppsala
> http://yi.org/peter-e/            Sweden
>
>
>
> ************

Peter Eisentraut wrote:
> 
> On 2000-01-24, The Hermit Hacker mentioned:
> 
> > Except, as Chris Bitmead brought up, OIDs appear to be a key requirement
> > in ODBMSs ... so, if we want to go what I *think* is 'next generation',
> > OIDs have to be kept ...
> 
> Independent of everything else I would like to point out that although
> oids do appear in a central role in the theory of object oriented
> databases they are still not a user-level feature. The system uses them to
> in essence do what some people already do with them now: use them as links
> in foreign key settings. This sort of scheme is supposed to eliminate the
> need for costly joins, since you already know the location of the data
> (assuming that you have a scheme to map the oid to the storage location).

AFAIK we currently don't. We have the (volatile) tids for that.

> Our oids are something different (though not sure what), PostgreSQL is
> something different. I am by all means against breaking what oids
> represent now, but incidentally I am also against them becoming (being) a
> user-level feature.

It would go at least half-way if we had a reasonably fast  function that can 
give either the type of objest (the relation) or the tuple itself if given
it's oid.

select tuple_for_oif(myoid);

or

select relation_containing_oid(myoid);

or even 

select * from relation_containing_oid(myoid) where oid = myoid; 
maybe spelled as
select * from * where oid = myoid;  ;)

An we could re-introduce the ability to get full tuples for select * from
base*;

We used to have it but cuurently it is at lest discouraged and probably
unsupported 
in libpq;

---------
Hannu

Peter Eisentraut wrote:
> 
> Independent of everything else I would like to point out that although
> oids do appear in a central role in the theory of object oriented
> databases they are still not a user-level feature.

Yes and no. Any useful ODBMS will allow you to get at the oid. This
allows you to say, pass it over a network as a key that you can use
later to get at the object.

> This past summer this sort of idea was discussed around these parts and
> most of us came to the conclusion that a) OODBs are a pipe-dream at this
> point in time, 

What does that mean?

> and b) this is not worth doing in PostgreSQL as it stands.

Why?

> If we wanna become an OODBs we might as well say that now so we can start
> by dropping SQL and the optimizer and the storage manager -- okay, I'm
> being sarcastic (about OODBs).

The big hope I see for postgresql is to someday be a true combination of
ODBMS and RDBMS. Current commercial ODBMSes suck because their querying
sucks. Current commercial (O)RDBMS suck because their object features
really suck. There is no fundamental reason that this must be so.

> However, once again, users would have no knowledge of these "oids". The
> system is free to do whatever it wants in order to do its thing, in
> particular it is free to *change* oids when it needs it (because when it
> copies the data elsewhere it presumably needs to tag the location
> differently).

Actually no. The whole point of oids is that they don't change. Ok, some
ODBMSes fudge this sometimes, but the whole point is if you have 100
objects pointing to one object, you don't want to have to change the oid
and go patch those other 100 objects. In particular Versant uses an 8
byte oid that NEVER changes. You can even move an object from one
database to another and it STILL doesn't change. This is rather cool.

> Our oids are something different (though not sure what), 

Why?

> PostgreSQL is
> something different. I am by all means against breaking what oids
> represent now, but incidentally I am also against them becoming (being) a
> user-level feature.

OIDs are a pseudo user level feature in an ODBMS. They are something you
don't think about most of the time, but you DO need to be able to get at
them.

On Thu, 27 Jan 2000, Chris Bitmead wrote:

> > This past summer this sort of idea was discussed around these parts and
> > most of us came to the conclusion that a) OODBs are a pipe-dream at this
> > point in time, 
> 
> What does that mean?

Not sure, I missed that conversation ...

> > If we wanna become an OODBs we might as well say that now so we can start
> > by dropping SQL and the optimizer and the storage manager -- okay, I'm
> > being sarcastic (about OODBs).
> 
> The big hope I see for postgresql is to someday be a true combination of
> ODBMS and RDBMS. Current commercial ODBMSes suck because their querying
> sucks. Current commercial (O)RDBMS suck because their object features
> really suck. There is no fundamental reason that this must be so.

I've asked this one before, I believe, some ppl have referred to us as
already being ORDBMS "material", but am not quite sure what that means in
our case ... how "ORDBMS" are we, and what is required to extend that?

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

Hannu Krosing wrote:

> select * from relation_containing_oid(myoid) where oid = myoid;
> maybe spelled as
> select * from * where oid = myoid;  ;)
> 
> An we could re-introduce the ability to get full tuples for select * from
> base*;
> 
> We used to have it but cuurently it is at lest discouraged and probably
> unsupported
> in libpq;

You are saying that you used to be able to get full tuples from postgres
if you selected from base* ??? In other words I select * from base, and
I can get back the fields in an inherited class too?

I have followed postgres from way back before the 'net started hacking
on it, and I've never come across this (but I desperately want it).

BTW, I think the relation_containing_oid logically belongs in the WHERE
clause. Then you need some syntax for specifying to get all the fields
from subclasses too. If you have no idea the type of object then you
have some logical "Object" relation which is the logical parent of all
relations. Suggesting the syntax "**" for meaning all columns including
sub-columns, I have suggested...

SELECT ** FROM object WHERE oid = 12345;

The Hermit Hacker wrote:

> > The big hope I see for postgresql is to someday be a true combination of
> > ODBMS and RDBMS. Current commercial ODBMSes suck because their querying
> > sucks. Current commercial (O)RDBMS suck because their object features
> > really suck. There is no fundamental reason that this must be so.
> 
> I've asked this one before, I believe, some ppl have referred to us as
> already being ORDBMS "material", but am not quite sure what that means in
> our case ... how "ORDBMS" are we, and what is required to extend that?

As I mentioned, I wrote a web page a long time ago where I try to
express the
minimum enhancments required to turn postgres into an ODBMS.
http://www.tech.com.au/postgres

At 5:47 PM -0800 1/26/00, Chris Bitmead wrote:
>Hannu Krosing wrote:
>
>> An we could re-introduce the ability to get full tuples for select * from
>> base*;
>>
>> We used to have it but cuurently it is at lest discouraged and probably
>> unsupported
>> in libpq;
>
>You are saying that you used to be able to get full tuples from postgres
>if you selected from base* ??? In other words I select * from base, and
>I can get back the fields in an inherited class too?
>
>I have followed postgres from way back before the 'net started hacking
>on it, and I've never come across this (but I desperately want it).

I could swear that this was one of the examples given to distinguish
Postgres from an ordinary RDBMS.  I never tried the feature though.

Signature failed Preliminary Design Review.
Feasibility of a new signature is currently being evaluated.
h.b.hotz@jpl.nasa.gov, or hbhotz@oxy.edu

Chris Bitmead wrote:

> Peter Eisentraut wrote:
> >
> > Independent of everything else I would like to point out that although
> > oids do appear in a central role in the theory of object oriented
> > databases they are still not a user-level feature.
>
> Yes and no. Any useful ODBMS will allow you to get at the oid. This
> allows you to say, pass it over a network as a key that you can use
> later to get at the object.

I'm starting to think that an oid is totally the wrong key to use for an
ODBMS. As objects
are only accessed via a client library there is no reason why this could not
add a key field.
You could then have a new system table that maps key fields on physical
locations, specific
types and whatever else you may need.

That would also make it easier to ensure keys being consistent between dumps.
Imagine wanting
to load some tables into an existing database and some of the oids of your
objects have been used already.
If you have overlapping key sets it is much easier to update those with an
increment to make them
unique rather than to try to get all your oids consistent, isn't it?

And a lot of the OO work on postgres would then depend on providing efficient
ways of handling
these keys.

[Please flame when talking rubbish]

Adriaan

On Wed, 26 Jan 2000, The Hermit Hacker wrote:

> I've asked this one before, I believe, some ppl have referred to us as
> already being ORDBMS "material", but am not quite sure what that means in
> our case ... how "ORDBMS" are we, and what is required to extend that?

I think putting some work and thought into inheritance and making it work
right would make a lot of people very happy, and inheritance is one of the
major ideas behind OO in any context. Another thing to expand upon would
be using classes ("tables") as datatypes. I believe this is doesn't work
all that well. But we're surely "ORDBMS material", if you like.

Pure object-oriented databases (which is where the oid thing comes from)
are somewhat separate though, they represent a paradigm shift similar to
moving from, say, hierarchical or network databases to relational ones.
The research in that area is not at all complete and it lacks a
standardized query language and a whole bunch of other stuff. Since a
major goal of this project is moving ever closer to SQL compliance,
becoming an "OODB" is not in the near future.

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

Adriaan Joubert wrote:
> 
> Chris Bitmead wrote:
> 
> > Peter Eisentraut wrote:
> > >
> > > Independent of everything else I would like to point out that although
> > > oids do appear in a central role in the theory of object oriented
> > > databases they are still not a user-level feature.
> >
> > Yes and no. Any useful ODBMS will allow you to get at the oid. This
> > allows you to say, pass it over a network as a key that you can use
> > later to get at the object.
> 
> I'm starting to think that an oid is totally the wrong key to use for an
> ODBMS. As objects
> are only accessed via a client library there is no reason why this could not
> add a key field.
> You could then have a new system table that maps key fields on physical
> locations, specific
> types and whatever else you may need.
> 
> That would also make it easier to ensure keys being consistent between dumps.

This could be fixe by going to Mariposa-style double-length oids, that have 
first 4 bytes as "site-id" and the rest is our conventional oid.

and then just maintain an /etc/postgresql-max-oid file that holds the site-id
which is incremented at each initdb.

If you are gong to do real distributed DBs you have to set up some registry
for 
allocating site-id ranges, so format for /etc/postgresql-max-oid might be

max-used-site-id max-site-id

> And a lot of the OO work on postgres would then depend on providing efficient
> ways of handling these keys.

Yes, but these keys _are_ currently the oids

--------------
Hannu

Chris Bitmead wrote:
> 
> Hannu Krosing wrote:
> 
> > select * from relation_containing_oid(myoid) where oid = myoid;
> > maybe spelled as
> > select * from * where oid = myoid;  ;)
> >
> > An we could re-introduce the ability to get full tuples for select * from
> > base*;
> >
> > We used to have it but cuurently it is at lest discouraged and probably
> > unsupported
> > in libpq;
> 
> You are saying that you used to be able to get full tuples from postgres
> if you selected from base* ??? In other words I select * from base, and
> I can get back the fields in an inherited class too?
> 
> I have followed postgres from way back before the 'net started hacking
> on it, and I've never come across this (but I desperately want it).

Here is how I remember it:

At least the wire protocol supports it and also libpq used to support it, 
until at about time of postgres95/postgreSQL it was removed from libpq as
"unneeded" (by Bruce IIRC).

until that time it should have theoretically been possible to return tuples 
of several types and sizes, either by using "select * from base* " or unions 
or functions in backend.

PostgreSQLs moving to SQL92 has dropped most of OO features as non-compliant
;(

For example inheritance is used my some as a convienient means of creating 
tables with some shared column names/types and adding anything to make it 
more has met vocal resiostance on this net as being incompatible with current 
usage.

> BTW, I think the relation_containing_oid logically belongs in the WHERE
> clause. Then you need some syntax for specifying to get all the fields
> from subclasses too. If you have no idea the type of object then you
> have some logical "Object" relation which is the logical parent of all
> relations. Suggesting the syntax "**" for meaning all columns including
> sub-columns, I have suggested...
> 
> SELECT ** FROM object WHERE oid = 12345;
>
We could define object as a relation with no attributes (columns) that all
other 
inherits from, so 

SELECT ** FROM object* WHERE oid = 12345;

would be valid query for getting all objects with oid=12345;

actually there could be more than one currently as you can input new ones by 
doing a "load from ..." 

My suggestion for using * for all tables would imply a relation called "" 
(i.e. empty string) as the universal base.

Some systems use a keyword ALL .

---------------
Hannu

Adriaan Joubert wrote:

> I'm starting to think that an oid is totally the wrong key to use for an 
> ODBMS. As objects
> are only accessed via a client library there is no reason why this could not
> add a key field.
> You could then have a new system table that maps key fields on physical
> locations, specific
> types and whatever else you may need.

I don't know what that means.

> That would also make it easier to ensure keys being consistent between dumps.
> Imagine wanting
> to load some tables into an existing database and some of the oids of your
> objects have been used already.
> If you have overlapping key sets it is much easier to update those with an
> increment to make them
> unique rather than to try to get all your oids consistent, isn't it?

In general, moving objects between databases depends what you want. One
approach is that the oid contains some bits related to the database it 
was first created in. The other approach is to re-link all the objects
when they are imported. (By incrementing them by a fixed amount given
the current max(oid) is one way).
> And a lot of the OO work on postgres would then depend on providing efficient
> ways of handling
> these keys.

??

Hannu Krosing wrote:

> > You are saying that you used to be able to get full tuples from postgres
> > if you selected from base* ??? In other words I select * from base, and
> > I can get back the fields in an inherited class too?
> >
> > I have followed postgres from way back before the 'net started hacking
> > on it, and I've never come across this (but I desperately want it).
> 
> Here is how I remember it:
> 
> At least the wire protocol supports it and also libpq used to support it,
> until at about time of postgres95/postgreSQL it was removed from libpq as
> "unneeded" (by Bruce IIRC).

Bruce, you scoundrel! :-)

> until that time it should have theoretically been possible to return tuples
> of several types and sizes, either by using "select * from base* " or unions
> or functions in backend.

I wonder how this would have worked. How would the backend know if you
wanted the 
sub-class columns or just the table? Did psql ever print different types
in the one
table? At least the current PQ interface doesn't seem to have this in
mind
since the interface seems to assume every tuple will have the same
number
of columns.

How much of this logic has been destroyed in the back end I wonder?

> For example inheritance is used my some as a convienient means of creating
> tables with some shared column names/types and adding anything to make it
> more has met vocal resiostance on this net as being incompatible with current
> usage.

Hmm. Damned useful though. Every time I design a data model, I feel
myself
yearning for this feature. In fact I can see precious little point in
having
an ORDBMS without this feature.

> > SELECT ** FROM object WHERE oid = 12345;
> >
> We could define object as a relation with no attributes (columns) that all
> other
> inherits from, so
> 
> SELECT ** FROM object* WHERE oid = 12345;
> 
> would be valid query for getting all objects with oid=12345;
> 
> actually there could be more than one currently as you can input new ones by
> doing a "load from ..."
> 
> My suggestion for using * for all tables would imply a relation called ""
> (i.e. empty string) as the universal base.

Interesting thought.

> Some systems use a keyword ALL .
> 
> ---------------
> Hannu

Peter Eisentraut wrote:

> I think putting some work and thought into inheritance and making it work
> right would make a lot of people very happy, and inheritance is one of the
> major ideas behind OO in any context. Another thing to expand upon would
> be using classes ("tables") as datatypes. I believe this is doesn't work
> all that well. But we're surely "ORDBMS material", if you like.

Yes, postgres pretends that classes as datatypes work, but if I remember
right it doesn't work in practice.

> Pure object-oriented databases (which is where the oid thing comes from)
> are somewhat separate though, they represent a paradigm shift similar to
> moving from, say, hierarchical or network databases to relational ones.

Yeh, but this need not be so. There is no necessary conflict between
the requirements of RDBMS and ODBMS. Postgres plus a couple of features
would quite fulfill both paradigms. Why no commercial vendor seems
to have done this very well I don't know.

> The research in that area is not at all complete and it lacks a
> standardized query language and a whole bunch of other stuff. 

Not really true. There IS a standard object query language called OQL,
which
is supported by some ODBMSes. OQL is basicly SQL, except you don't have
to
specify WHERE criteria when it's obvious and a few bits and pieces.
There
is no reason you couldn't support SQL+OQL because they don't really
contradict.

> Since a
> major goal of this project is moving ever closer to SQL compliance,
> becoming an "OODB" is not in the near future.

I would have thought what was in the near future, is whatever people
choose to hack on. I take it no-one is going to reject sensible patches
along this line?

Chris Bitmead <chris@bitmead.com> writes:
> Yes, postgres pretends that classes as datatypes work, but if I remember
> right it doesn't work in practice.

I imagine it did work, at least partly, back in the Berkeley days.
But as someone's already pointed out in this thread, ever since the
code left Berkeley the main development thrust has been on achieving
SQL compliance (not to mention robustness).  POSTQUEL had a lot of
non-SQL features, many of which are now suffering from bit rot...
this is one.

For a lot of this older stuff, there isn't even any documentation
(that I know of) on what it's *supposed* to do, let alone on how
thorough the original implementation was.  For instance, I have
no idea what "classes as datatypes" actually means, in the sense
of what you could do with them in POSTQUEL.  Anyone remember?

> I would have thought what was in the near future, is whatever people
> choose to hack on. I take it no-one is going to reject sensible patches
> along this line?

First problem is to figure out what it should be doing ;-).  If you
can get consensus on that, I doubt anyone will object to making it
work again.
        regards, tom lane

> Hannu Krosing wrote:
> 
> > > You are saying that you used to be able to get full tuples from postgres
> > > if you selected from base* ??? In other words I select * from base, and
> > > I can get back the fields in an inherited class too?
> > >
> > > I have followed postgres from way back before the 'net started hacking
> > > on it, and I've never come across this (but I desperately want it).
> > 
> > Here is how I remember it:
> > 
> > At least the wire protocol supports it and also libpq used to support it,
> > until at about time of postgres95/postgreSQL it was removed from libpq as
> > "unneeded" (by Bruce IIRC).
> 
> Bruce, you scoundrel! :-)
> 
> > until that time it should have theoretically been possible to return tuples
> > of several types and sizes, either by using "select * from base* " or unions
> > or functions in backend.
> 

I have no idea what this was.  I could have removed it, but I don't
remember anything about this.

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

Tom Lane wrote:

> For a lot of this older stuff, there isn't even any documentation
> (that I know of) on what it's *supposed* to do, let alone on how
> thorough the original implementation was.  For instance, I have
> no idea what "classes as datatypes" actually means, in the sense
> of what you could do with them in POSTQUEL.  Anyone remember?

I thought that the original berkeley doco covered this to some extent.
Basicly I think you're supposed to be able to go...

CREATE TABLE address (street TEXT, number TEXT, suburb TEXT, zip TEXT);
CREATE TABLE person (name TEXT, address ADDRESS);

SELECT name, address FROM person WHERE person.address.suburb = 'New
York';

And last time I bothered to look postgresql wouldn't reject these SQL
out of
hand, but I don't think it works properly. You should also be able to
write
constraints I guess for address so that it applies to any class that
has an address.

> First problem is to figure out what it should be doing ;-).  If you
> can get consensus on that, I doubt anyone will object to making it
> work again.

Hmm. My guess is the original researchers didn't do too much wrong,
so getting the old stuff working shouldn't be too much controversy
if it's documented in the old papers. I
suspect they forgot a few things (like the doubt over getting
columns of sub-classes), but for that I don't think there's that much
controversy, either it does it or not.

Bruce Momjian <pgman@candle.pha.pa.us> writes:
>>>> At least the wire protocol supports it and also libpq used to support it,
>>>> until at about time of postgres95/postgreSQL it was removed from libpq as
>>>> "unneeded" (by Bruce IIRC).
>> 
>> Bruce, you scoundrel! :-)
>> 
>>>> until that time it should have theoretically been possible to
>>>> return tuples of several types and sizes, either by using "select *
>>>> from base* " or unions or functions in backend.

> I have no idea what this was.  I could have removed it, but I don't
> remember anything about this.

Actually I might be the guilty party.  I recall having seen that libpq
was willing to accept multiple T (tuple descriptor) messages
interspersed in the data returned by a query.  But the API that libpq
presents to the application cannot support this, and I concluded after
digging around in the backend that the backend couldn't do it either.
I think I broke libpq's support for it during the rewrite for 6.4.

This is something that might have worked once upon a time, long ago
and far away.  But making it work again will take considerably more
than just patching libpq...
        regards, tom lane

Chris Bitmead <chris@bitmead.com> writes:
> Tom Lane wrote:
>> For a lot of this older stuff, there isn't even any documentation
>> (that I know of) on what it's *supposed* to do, let alone on how
>> thorough the original implementation was.

> I thought that the original berkeley doco covered this to some extent.

Where would I find that?

> Basicly I think you're supposed to be able to go...

> CREATE TABLE address (street TEXT, number TEXT, suburb TEXT, zip TEXT);
> CREATE TABLE person (name TEXT, address ADDRESS);

> SELECT name, address FROM person WHERE person.address.suburb = 'New
> York';

Hmm.  This looks like a CREATE TABLE implicitly creates a datatype
that acts more or less like a C 'struct' declaration, ie, it's just a
collection of subfields.  OK, a struct-making declaration is certainly
useful.  What I don't understand yet is whether the contents of table
"address" have any connection to the data stored in table "person".
If not, why must I create a table in order to define a datatype?  Seems
like a separate CREATE DATATYPE command would make more sense...
        regards, tom lane

On Fri, 28 Jan 2000, Chris Bitmead wrote:

> Peter Eisentraut wrote:
> 
> > I think putting some work and thought into inheritance and making it work
> > right would make a lot of people very happy, and inheritance is one of the
> > major ideas behind OO in any context. Another thing to expand upon would
> > be using classes ("tables") as datatypes. I believe this is doesn't work
> > all that well. But we're surely "ORDBMS material", if you like.
> 
> Yes, postgres pretends that classes as datatypes work, but if I remember
> right it doesn't work in practice.
> 
> > Pure object-oriented databases (which is where the oid thing comes from)
> > are somewhat separate though, they represent a paradigm shift similar to
> > moving from, say, hierarchical or network databases to relational ones.
> 
> Yeh, but this need not be so. There is no necessary conflict between
> the requirements of RDBMS and ODBMS. Postgres plus a couple of features
> would quite fulfill both paradigms. Why no commercial vendor seems
> to have done this very well I don't know.
> 
> > The research in that area is not at all complete and it lacks a
> > standardized query language and a whole bunch of other stuff. 
> 
> Not really true. There IS a standard object query language called OQL,
> which
> is supported by some ODBMSes. OQL is basicly SQL, except you don't have
> to
> specify WHERE criteria when it's obvious and a few bits and pieces.
> There
> is no reason you couldn't support SQL+OQL because they don't really
> contradict.
> 
> > Since a
> > major goal of this project is moving ever closer to SQL compliance,
> > becoming an "OODB" is not in the near future.
> 
> I would have thought what was in the near future, is whatever people
> choose to hack on. I take it no-one is going to reject sensible patches
> along this line?

As long as a patch doesnt' break current functionality, definitely not
... if implementing OOL meant removign SQL, forget it ... if it can be
added to augment what w  already have, patch away ...

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

On Fri, 28 Jan 2000, Tom Lane wrote:

> Chris Bitmead <chris@bitmead.com> writes:
> > Tom Lane wrote:
> >> For a lot of this older stuff, there isn't even any documentation
> >> (that I know of) on what it's *supposed* to do, let alone on how
> >> thorough the original implementation was.
> 
> > I thought that the original berkeley doco covered this to some extent.
> 
> Where would I find that?
> 
> > Basicly I think you're supposed to be able to go...
> 
> > CREATE TABLE address (street TEXT, number TEXT, suburb TEXT, zip TEXT);
> > CREATE TABLE person (name TEXT, address ADDRESS);
> 
> > SELECT name, address FROM person WHERE person.address.suburb = 'New
> > York';
> 
> Hmm.  This looks like a CREATE TABLE implicitly creates a datatype
> that acts more or less like a C 'struct' declaration, ie, it's just a
> collection of subfields.  OK, a struct-making declaration is certainly
> useful.  What I don't understand yet is whether the contents of table
> "address" have any connection to the data stored in table "person".
> If not, why must I create a table in order to define a datatype?  Seems
> like a separate CREATE DATATYPE command would make more sense...

Not quite an answer to your question, but my guess is that 'address
ADDRESS' would contain a pointer (OID) to the address table ... so the
person table would be realtively small in comparison to the address table
...

The way I look at the above, its a 'JOIN' at table create time, based on a
unique value, the OID ... 

How 'dep' can you go with this?  ie:

CREATE TABLE address (street TEXT, number TEXT, suburb TEXT, zip TEXT);
CREATE TABLE telephone ( home TEXT, business TEXT, fax TEXT );
CREATE TABLE person (name TEXT, address ADDRESS, telephone TELEPHONE);

Question, if I did an INSERT person VALUES ('myname');

What happens to the address table?  a row gets created with all NULL?  Or?

The reason I ask is the way it was taught to me was that an RDBMS gains
its benefit through normalization and joins ...with the outer join syntax
coming up, if you had a table of 'person' fully populated, but only
address info for 1/2 of them, you could still get all 'people', while your
'address' table has 1/2 the tuples of the person one ... space savings ...

HSorry, rambling thoughts out o fmy head without putting them together
very well :)


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

The Hermit Hacker <scrappy@hub.org> writes:
>> What I don't understand yet is whether the contents of table
>> "address" have any connection to the data stored in table "person".
>> If not, why must I create a table in order to define a datatype?  Seems
>> like a separate CREATE DATATYPE command would make more sense...

> Not quite an answer to your question, but my guess is that 'address
> ADDRESS' would contain a pointer (OID) to the address table ... so the
> person table would be realtively small in comparison to the address table
> ...
> The way I look at the above, its a 'JOIN' at table create time, based on a
> unique value, the OID ... 

Hmm.  OK, that makes sense, because I know I've seen places in the code
that think that any "set type" is represented as an OID.  I never
understood what that was all about, but in this context that would be
what would happen.  Assuming that this facility is the same as what
the code calls a set, that is.

So, if I looked into table address, presumably I'd find rows
corresponding to each value that is (ever has been?) stored in another
table with an ADDRESS column.  How do no-longer-useful values get
cleaned out of the address table, do you suppose?
        regards, tom lane

> Hmm.  This looks like a CREATE TABLE implicitly creates a datatype
> that acts more or less like a C 'struct' declaration, ie, it's just a
> collection of subfields.  OK, a struct-making declaration is certainly
> useful.  What I don't understand yet is whether the contents of table
> "address" have any connection to the data stored in table "person".
> If not, why must I create a table in order to define a datatype?  Seems
> like a separate CREATE DATATYPE command would make more sense...

I'm pretty sure that they were going for symmetry between simple data
types and more complex objects. Another example of this is creating an
empty table to be inherited by other tables, just to get a common
definition of fields.

Chris, one major reason why we run the risk of damaging the OR
features is that we don't have good coverage of these in the
regression tests. And as Tom pointed out, we aren't sure how to get at
the original Postgres papers and docs which might cover this in more
detail; the only thing we had directly was the old Postgres95 User's
Guide, which may not cover all of the possible features (in fact I
know it doesn't, since I made sure that all info in that guide
appeared somewhere in the newer docs).

I know that there are at least a few people with an interest in this
(including me, but I'm suffering from, among other things, a lack of
knowledge of what used to be there and what could be done). A great
first step would be documenting the behaviors one would expect in a
regression test, even if parts of that test currently fails.
                   - Thomas

-- 
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

On Fri, 28 Jan 2000, Tom Lane wrote:

> The Hermit Hacker <scrappy@hub.org> writes:
> >> What I don't understand yet is whether the contents of table
> >> "address" have any connection to the data stored in table "person".
> >> If not, why must I create a table in order to define a datatype?  Seems
> >> like a separate CREATE DATATYPE command would make more sense...
> 
> > Not quite an answer to your question, but my guess is that 'address
> > ADDRESS' would contain a pointer (OID) to the address table ... so the
> > person table would be realtively small in comparison to the address table
> > ...
> > The way I look at the above, its a 'JOIN' at table create time, based on a
> > unique value, the OID ... 
> 
> Hmm.  OK, that makes sense, because I know I've seen places in the code
> that think that any "set type" is represented as an OID.  I never
> understood what that was all about, but in this context that would be
> what would happen.  Assuming that this facility is the same as what
> the code calls a set, that is.
> 
> So, if I looked into table address, presumably I'd find rows
> corresponding to each value that is (ever has been?) stored in another
> table with an ADDRESS column.  How do no-longer-useful values get
> cleaned out of the address table, do you suppose?

An internal trigger?  'ON DELETE FROM person DELETE FROM address where
OID=?' ?


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

On Fri, Jan 28, 2000 at 11:02:32AM -0400, The Hermit Hacker wrote:
> On Fri, 28 Jan 2000, Tom Lane wrote:
> 
> > Chris Bitmead <chris@bitmead.com> writes:
> > > Tom Lane wrote:
> > >> For a lot of this older stuff, there isn't even any documentation
> > >> (that I know of) on what it's *supposed* to do, let alone on how
> > >> thorough the original implementation was.
> > 
> > > I thought that the original berkeley doco covered this to some extent.
> > 
> > Where would I find that?
> > 
> > > Basicly I think you're supposed to be able to go...
> > 
> > > CREATE TABLE address (street TEXT, number TEXT, suburb TEXT, zip TEXT);
> > > CREATE TABLE person (name TEXT, address ADDRESS);
> > 
> > > SELECT name, address FROM person WHERE person.address.suburb = 'New
> > > York';
> > 
> > Hmm.  This looks like a CREATE TABLE implicitly creates a datatype
> > that acts more or less like a C 'struct' declaration, ie, it's just a
> > collection of subfields.  OK, a struct-making declaration is certainly
> > useful.  What I don't understand yet is whether the contents of table
> > "address" have any connection to the data stored in table "person".
> > If not, why must I create a table in order to define a datatype?  Seems
> > like a separate CREATE DATATYPE command would make more sense...
> 
> Not quite an answer to your question, but my guess is that 'address
> ADDRESS' would contain a pointer (OID) to the address table ... so the
> person table would be realtively small in comparison to the address table
> ...
> 
> The way I look at the above, its a 'JOIN' at table create time, based on a
> unique value, the OID ... 
> 

Ah, so attribute inheritance implements "is-a" relationships, and classes
as datatypes implements "has-a"?


> How 'dep' can you go with this?  ie:
> 
> CREATE TABLE address (street TEXT, number TEXT, suburb TEXT, zip TEXT);
> CREATE TABLE telephone ( home TEXT, business TEXT, fax TEXT );
> CREATE TABLE person (name TEXT, address ADDRESS, telephone TELEPHONE);
> 
> Question, if I did an INSERT person VALUES ('myname');
> 
> What happens to the address table?  a row gets created with all NULL?  Or?

Nothing, the address attribute for that person is NULL. At least, That's
what I'd want it to do.

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

Tom Lane wrote:

> > I thought that the original berkeley doco covered this to some extent.
> 
> Where would I find that?

No offence intended guys, but I'm a bit shocked that you're all hacking
on
postgres without having read the design docs. The design docs are
extremely cool and I'd personally like to see the database return
to the glory days of all this cool technology, features and ideas.
(Hey wouldn't even mind if it went back to postquel hey hey :)

Ok, the postgres 4.2 distribution is at 
http://s2k-ftp.cs.berkeley.edu:8000:8000/postgres/postgres-v4r2/
and it has the design docs inside it.

Here is a little excert from one doc...


In addition to the Retrieve-portal command, portals can be defined by an
Execute
command.
For example, suppose the EMP relation had a field of type POSTQUEL
named ``hobbies''

EMP (name, salary, age, hobbies)
that contained commands to retrieve a person's hobbies from the
following
relations:
SOFTBALL (name, position, batting-avg)
COMPUTERS (name, isowner, brand, interest)
An application program can define a portal that will range over the
tuples
describing a person's hobbies as follows:
execute portal H(EMP.hobbies)
where EMP.name = ``Smith''
This command defines a portal, named ``H,'' that is bound to Smith's
hobby records.
Since a person can have several hobbies, represented by more than on
Retrieve
command in the ``hobbies'' field, the records in the
buffer may have different types.
Consequently,
HITCHING POST must provide routines that allow the program
to determine the number of fields, and the type, name,
and value of each field in each record fetched into the buffer.

Tom Lane wrote:

> Hmm.  This looks like a CREATE TABLE implicitly creates a datatype
> that acts more or less like a C 'struct' declaration, ie, it's just a
> collection of subfields.  OK, a struct-making declaration is certainly
> useful.  What I don't understand yet is whether the contents of table
> "address" have any connection to the data stored in table "person".
> If not, why must I create a table in order to define a datatype?  Seems
> like a separate CREATE DATATYPE command would make more sense...

I think the idea is like in C++ if you create a 
class Address {
...
}

and a class
class Person {Address address;
}

Then you can create both standalone Addresses as well as addresses
embedded
inside the Person.

CREATE DATATYPE might be a thought, but it's probably not very
essential.

The Hermit Hacker wrote:

> Not quite an answer to your question, but my guess is that 'address
> ADDRESS' would contain a pointer (OID) to the address table ... so the
> person table would be realtively small in comparison to the address table

I'm 99% sure that this is not the case. Rather the address is embedded
inside
the person object. I think this is basicly what Oracle has done with 8i
too.
I think then if you do SELECT * from person it flattens out all the
fields.
(This might even still work).

Not that the idea of relating to oid as another feature is bad. My last
message I gave the example of how postquel could do this. I think
that design had the advantage that you could construct 1:M relationships
this way too.

Just a trade off a bit like in C++
class Person {Address address;
}

vs

class Person {Address *address;
}

vs 

class Person {List<Address> addresses;
}

pros and cons for each one.

> The way I look at the above, its a 'JOIN' at table create time, based on a
> unique value, the OID ...
> 
> How 'dep' can you go with this?  ie:
> 
> CREATE TABLE address (street TEXT, number TEXT, suburb TEXT, zip TEXT);
> CREATE TABLE telephone ( home TEXT, business TEXT, fax TEXT );
> CREATE TABLE person (name TEXT, address ADDRESS, telephone TELEPHONE);
> 
> Question, if I did an INSERT person VALUES ('myname');
> 
> What happens to the address table?  a row gets created with all NULL?  Or?
> 
> The reason I ask is the way it was taught to me was that an RDBMS gains
> its benefit through normalization and joins ...with the outer join syntax
> coming up, if you had a table of 'person' fully populated, but only
> address info for 1/2 of them, you could still get all 'people', while your
> 'address' table has 1/2 the tuples of the person one ... space savings ...
> 
> HSorry, rambling thoughts out o fmy head without putting them together
> very well :)
> 
> Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
> Systems Administrator @ hub.org
> primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

Thomas Lockhart wrote:

> Chris, one major reason why we run the risk of damaging the OR
> features is that we don't have good coverage of these in the
> regression tests. And as Tom pointed out, we aren't sure how to get at
> the original Postgres papers and docs which might cover this in more
> detail; 

Why not include the original Postgres papers in the postgresql 
distribution? While it's not all applicable now, at least it would
help keep in people's minds what was there.

> the only thing we had directly was the old Postgres95 User's
> Guide, which may not cover all of the possible features (in fact I
> know it doesn't, since I made sure that all info in that guide
> appeared somewhere in the newer docs).
> 
> I know that there are at least a few people with an interest in this
> (including me, but I'm suffering from, among other things, a lack of
> knowledge of what used to be there and what could be done). A great
> first step would be documenting the behaviors one would expect in a
> regression test, even if parts of that test currently fails.
> 
>                     - Thomas
> 
> --
> Thomas Lockhart                         lockhart@alumni.caltech.edu
> South Pasadena, California

> Tom Lane wrote:
> 
> > > I thought that the original berkeley doco covered this to some extent.
> > 
> > Where would I find that?
> 
> No offence intended guys, but I'm a bit shocked that you're all hacking
> on
> postgres without having read the design docs. The design docs are
> extremely cool and I'd personally like to see the database return
> to the glory days of all this cool technology, features and ideas.
> (Hey wouldn't even mind if it went back to postquel hey hey :)

Suppy and demand.  We little demand for fancy features, and huge demand
for SQL standard features.

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

Bruce Momjian wrote:
> 
> > Tom Lane wrote:
> >
> > > > I thought that the original berkeley doco covered this to some extent.
> > >
> > > Where would I find that?
> >
> > No offence intended guys, but I'm a bit shocked that you're all hacking
> > on
> > postgres without having read the design docs. The design docs are
> > extremely cool and I'd personally like to see the database return
> > to the glory days of all this cool technology, features and ideas.
> > (Hey wouldn't even mind if it went back to postquel hey hey :)
> 
> Suppy and demand.  We little demand for fancy features, and huge demand
> for SQL standard features.

And even bigger demand for stability and speed (in this order);)

Once we have both to some high level (which we mostly have now) the demand
for fancyness will no doubt return.

----------
Hannu