OO Patch

Chris Bitmead writes:

That also goes for the various ALTER TABLE [ONLY]
syntax additions. If I add a row to A only then B is no longer a subtable
of A.

I agree that the alter table only is crazy, but the functionality was
there before and I didn't want to be the one to take it out. But if
someone does I can't imagine I'd object.

Okay, I think I see what you're getting at. The "ONLY" syntax on DELETE,
UPDATE, and ALTER TABLE would provide an entry point for the current,
broken behaviour, for those who need it (though it's not really backwards
compatibility per se). We might want to flag these with warnings "don't do
that" and reserve the option to remove them at a later date, to save
people from attempting stupid things.

I guess what I might have alluded to with "design document" is that you
would have explained that connection, because I did look at the old
thread(s) and didn't have any clue what was decided upon. What I was also
wondering about were these things such as the "virtual" IDENTITY field
that was proposed, the `SELECT **' syntax (bad idea, IMO), and the notion
that a query could return different types of rows when reading from an
inheritance structure (a worse idea, IMO). I didn't know whether the patch
touched that. (I think now that it doesn't.)

I'll tell you what, I have some time next week, and I'll read up on SQL3.
Perhaps I'll survive it. ;-)

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

Peter Eisentraut <peter_e@gmx.net> writes:

I guess what I might have alluded to with "design document" is that you
would have explained that connection, because I did look at the old
thread(s) and didn't have any clue what was decided upon.

AFAIR, nothing was decided on ;-) ... the list has gone 'round on this
topic a few times without achieving anything you could call consensus.

I think Robert Easter might have his hands on the right idea: there
is more than one concept here, and more than one set of applications
to be addressed. We need to break things down into component concepts
rather than trying for a one-size-fits-all solution.

I'll tell you what, I have some time next week, and I'll read up on SQL3.
Perhaps I'll survive it. ;-)

Daniel enters the lions' den ... good luck ;-)

regards, tom lane

Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

I guess what I might have alluded to with "design document" is that you
would have explained that connection, because I did look at the old
thread(s) and didn't have any clue what was decided upon.

AFAIR, nothing was decided on ;-) ... the list has gone 'round on this
topic a few times without achieving anything you could call consensus.

Oh dear. I thought we had progressed further than that. I hope we're not
back to square one here.

I think Robert Easter might have his hands on the right idea: there
is more than one concept here, and more than one set of applications
to be addressed. We need to break things down into component concepts
rather than trying for a one-size-fits-all solution.

I can't see that anything I've proposed could be construed as
one-size-fits-all.

1) DELETE and UPDATE on inheritance hierarchies. You actually suggested
it Tom, it used to work in postgres (if you look at the V7.0 doco very
carefully, it still says it works!! though it probably hasn't since the
V4.2 days). It's really a rather obvious inclusion.

2) Imaginary classoid field. This is a very stand-alone feature, that I
didn't hear any objections to.

3) Returning of sub-class fields. Any ODBMS *must* do this by
definition. If it doesn't, it isn't an ODBMS. The only question is what
syntax to activate it, and I'm not much fussed about that.

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

3) Returning of sub-class fields. Any ODBMS *must* do this by
definition. If it doesn't, it isn't an ODBMS.

Chris, you have a bad habit of defining away the problem. Not
everyone is convinced upon this point, and your assertions that
there was consensus don't help your cause.

Possibly more to the point: your patch doesn't implement the
above behavior AFAICS. (Certainly libpq is unprepared to support
multiple tuple types returned in one SELECT --- and there are no
frontend changes in your patch.) So it might help if you'd clarify
exactly what the proposed patch does and doesn't do.

regards, tom lane

Tom Lane wrote:

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

3) Returning of sub-class fields. Any ODBMS *must* do this by
definition. If it doesn't, it isn't an ODBMS.

Chris, you have a bad habit of defining away the problem. Not
everyone is convinced upon this point,

You claimed to be convinced in the previous discussions. Who exactly
wasn't?

and your assertions that
there was consensus don't help your cause.

I must admit to frustration here. Will I be issued with a certificate or
something when an arbitrator declares "consensus". I can't fathom how
decisions are made around here, but you seem to be as close to a leader
as I'll find. On the sub-class returning issue you declared that you
understood that it was "good for a certain class of problems" or some
such. My take on the previous discussions were that a great number of
objections were resolved. Am I supposed to just sit on my bum waiting
for people who havn't even used an ODBMS to argue for a few years? I'm
quite willing to talk this all through again but it needs to reach
closure at some point.

Possibly more to the point: your patch doesn't implement the
above behavior AFAICS.

I know, it only implements the first point. But this is useful in
itself.

(Certainly libpq is unprepared to support
multiple tuple types returned in one SELECT --- and there are no
frontend changes in your patch.) So it might help if you'd clarify
exactly what the proposed patch does and doesn't do.

This is the third time I've submitted the patch and you examined it in
detail last two times. This is just a post-7.0 merge and I was expecting
it put in CVS now that 7.0 is done.

To repeat - it implements DELETE and UPDATE on inheritance hierarchies
to correct old bit-rot, and it implements ONLY as relates inheritance
hierarchies to exclude sub-classes. Oh, and the emacs pgsql code style
lisp implementation is done right in the FAQ.

Tom Lane wrote:

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

3) Returning of sub-class fields. Any ODBMS *must* do this by
definition. If it doesn't, it isn't an ODBMS.

Chris, you have a bad habit of defining away the problem. Not
everyone is convinced upon this point.

Or to put things another way, my goal is to implement the ODMG
(http://www.odmg.org/) interface on postgresql. Nobody has said
*anything* like that this is a bad goal to aim for, or that there is a
better way of doing it.

Tom Lane wrote:

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

3) Returning of sub-class fields. Any ODBMS *must* do this by
definition. If it doesn't, it isn't an ODBMS.

Chris, you have a bad habit of defining away the problem. Not
everyone is convinced upon this point, and your assertions that
there was consensus don't help your cause.

I am convinced ;).

There should be no consensus that "there should be no way to
retrieve sub-fields" ;)

I agree that the default may well be to retrieve only fuelds of
base class.

Possibly more to the point: your patch doesn't implement the
above behavior AFAICS. (Certainly libpq is unprepared to support
multiple tuple types returned in one SELECT

IIRC Bruce removed that feature in Pg95 days claiming that it would
not be needed. If backend starts to support it again it would be
relatively easy to put back in.

Good Morning.

From user (somebody like me) point of view it is important that

documentation reflects relaity. I mentioned about the following discrepancy
a couple of months ago. The remark was thorougly ignored.

It is great that it will be corrected in the best possible way, it means
reality will be upgraded to documentation|
Regards,
Andrzej Mazurkiewicz.

On Fri, 19 May 2000, Chris Bitmead wrote:

Tom Lane wrote:

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

3) Returning of sub-class fields. Any ODBMS *must* do this by
definition. If it doesn't, it isn't an ODBMS.

Chris, you have a bad habit of defining away the problem. Not
everyone is convinced upon this point,

You claimed to be convinced in the previous discussions. Who exactly
wasn't?

and your assertions that
there was consensus don't help your cause.

I must admit to frustration here. Will I be issued with a certificate or
something when an arbitrator declares "consensus". I can't fathom how
decisions are made around here, but you seem to be as close to a leader
as I'll find. On the sub-class returning issue you declared that you
understood that it was "good for a certain class of problems" or some
such.

We have a list archive ... just to try and help out here, you
might want to try posting URLs to show quotes ... to back things up ...

My take on the previous discussions were that a great number of
objections were resolved. Am I supposed to just sit on my bum waiting
for people who havn't even used an ODBMS to argue for a few years? I'm
quite willing to talk this all through again but it needs to reach
closure at some point.

Nope, my take on things is that your patch does things that would break
existing functionality, which won't be permitted without one helluva good
explanation ...

This is the third time I've submitted the patch and you examined it in
detail last two times. This is just a post-7.0 merge and I was expecting
it put in CVS now that 7.0 is done.

That won't happen ... v7.1, if you can get agreement, but not in the
current CVS tree ...

The Hermit Hacker wrote:

On Fri, 19 May 2000, Chris Bitmead wrote:

My take on the previous discussions were that a great number of
objections were resolved. Am I supposed to just sit on my bum waiting
for people who havn't even used an ODBMS to argue for a few years? I'm
quite willing to talk this all through again but it needs to reach
closure at some point.

Nope, my take on things is that your patch does things that would break
existing functionality,

IMHO it actually _fixes_ existing broken functionality .

which won't be permitted without one helluva good explanation ...

Yes, that was The Hermit Hacker I fearfully referred to as misusing even
the current "OO" functionality when I warned people not to promote using
any half-baked OO features developers have forgot into PostgreSQL when they
converted a cool ORDBMS into a generlly usable (non-O)RDBMS.

It may be time to fork the tree into OO and beancounting editions ?
Especially so if the main tree will migrate to BDB ;-p

OOPostgreSQL sounds quite nice ;)

This is the third time I've submitted the patch and you examined it in
detail last two times. This is just a post-7.0 merge and I was expecting
it put in CVS now that 7.0 is done.

That won't happen ... v7.1, if you can get agreement, but not in the
current CVS tree ...

From where must he get that agreement ?

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

On Fri, 19 May 2000, Hannu Krosing wrote:

The Hermit Hacker wrote:

On Fri, 19 May 2000, Chris Bitmead wrote:

My take on the previous discussions were that a great number of
objections were resolved. Am I supposed to just sit on my bum waiting
for people who havn't even used an ODBMS to argue for a few years? I'm
quite willing to talk this all through again but it needs to reach
closure at some point.

Nope, my take on things is that your patch does things that would break
existing functionality,

IMHO it actually _fixes_ existing broken functionality .

Oops, sorry, mis-spell ... would should be could ...

which won't be permitted without one helluva good explanation ...

Yes, that was The Hermit Hacker I fearfully referred to as misusing even
the current "OO" functionality when I warned people not to promote using
any half-baked OO features developers have forgot into PostgreSQL when they
converted a cool ORDBMS into a generlly usable (non-O)RDBMS.

It may be time to fork the tree into OO and beancounting editions ?
Especially so if the main tree will migrate to BDB ;-p

OOPostgreSQL sounds quite nice ;)

This is the third time I've submitted the patch and you examined it in
detail last two times. This is just a post-7.0 merge and I was expecting
it put in CVS now that 7.0 is done.

That won't happen ... v7.1, if you can get agreement, but not in the
current CVS tree ...

From where must he get that agreement ?

From more then two ppl? Actually, IMHO, it looks like alot of the problem
is not that we should improve our OO, but how to go about it. It appears
to me that the past thread that Chris started ended in a fashion that bred
misunderstanding ... Chris thought it was resolved, others thought it got
left hanging ...

What *I'd* like to see is that past thread re-picked up again ... I'm
going to take some time tonight to go through the archives and see if I
can pull out "the start of the thread", will post it, and see if we can
get some discussions going ...

v7.0 hasn't been BRANCHED yet, so it can't go into the tree yet, but if we
can take the next bit of time before it is BRANCHED to discuss it out and
reach some sort of consensus here ...

Chris, one quick question ... the last email I read from you stated a
bunch of things that you wanted to accomplish, but your patch only
addressed the first one. Can we focus on that and ignore the others? Do
it through step'ng stones? Or does each step only make sense in view of
the whole picture?

The Hermit Hacker wrote:

On Fri, 19 May 2000, Hannu Krosing wrote:

The Hermit Hacker wrote:

On Fri, 19 May 2000, Chris Bitmead wrote:

My take on the previous discussions were that a great number of
objections were resolved. Am I supposed to just sit on my bum waiting
for people who havn't even used an ODBMS to argue for a few years? I'm
quite willing to talk this all through again but it needs to reach
closure at some point.

Nope, my take on things is that your patch does things that would break
existing functionality,

IMHO it actually _fixes_ existing broken functionality .

Oops, sorry, mis-spell ... would should be could ...

;)

From where must he get that agreement ?

From more then two ppl? Actually, IMHO, it looks like alot of the problem

is not that we should improve our OO, but how to go about it. It appears
to me that the past thread that Chris started ended in a fashion that bred
misunderstanding ... Chris thought it was resolved, others thought it got
left hanging ...

What *I'd* like to see is that past thread re-picked up again ... I'm
going to take some time tonight to go through the archives and see if I
can pull out "the start of the thread", will post it, and see if we can
get some discussions going ...

v7.0 hasn't been BRANCHED yet, so it can't go into the tree yet, but if we
can take the next bit of time before it is BRANCHED to discuss it out and
reach some sort of consensus here ...

Some sort of mission statement - what we want to accomplish and steps
to get there ?

Chris, one quick question ... the last email I read from you stated a
bunch of things that you wanted to accomplish, but your patch only
addressed the first one. Can we focus on that and ignore the others? Do
it through step'ng stones? Or does each step only make sense in view of
the whole picture?

I guess the first step implemented in the patch is a useful fix in
its own right.

Alter table ONLY should be discouraged (maybe even forbidden in future)

Making Alter table to work efficiently on subtables would need some redesign
of tuple storage anyway, but this can probably postponed to when other things
are working. The same redesign would also give us efficient
ALTER TABLE DROP COLUMN.

Future things like having a unique index over all inherited tables require
more technical discussion as there are several vays to implement them, each
efficient for different use pattern.

btw. I'll be away from computer from now to monday, but I'm very much
interested in this topic and will surely followup then - it's a pain to do
all the OO in the frontend.

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

On Sat, May 20, 2000 at 09:42:45AM +1000, Chris wrote:

The Hermit Hacker wrote:

We have a list archive ... just to try and help out here, you
might want to try posting URLs to show quotes ... to back things up ...

I don't have much success with the archive. (Search for "Proposed
Changes" - the name of the thread. It yields zero results). The links
to the result urls are coloured the same whether you have visited them
or not (not a bright idea), and in general I'm skeptical the searching
works properly. I certainly can't lay my hands on quite a few important
postings.

http://www.postgresql.org/mhonarc/pgsql-hackers/2000-02/msg00050.html

Seems to be the start of it. The web server had an unfortunate hard drive
crash, from what I understand, and they've been rebuilding the indices
for the search engine. (I found this by greping my local 'all postgresql
list I subscribe to' archive, to find the date, then going to that page
on postgresql.org. One problem is that the 'by month' links in the mailing
list archives only give you _part_ of the month: you have to hit the
'next page' link at the top)

We're post v7.0 now, so presumably we are in pre-7.1 land right? Surely
any minor patches now can be done in a branch? I can understand
reluctance to branch with heavy development in progress pre-7.0 but once
you've released it's time to move on.

Nope - the standard release process for postgresql is tag at release date,
branch after the inital flurry of bug reports/patches settles down. This
avoids a lot of double patching for the bugs that the beta testers don't
find, but the general user community does.

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

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

I must admit to frustration here. Will I be issued with a certificate or
something when an arbitrator declares "consensus". I can't fathom how
decisions are made around here,

If necessary, hard decisions are made by agreement of the core committee
--- but core prefers not to impose answers on the community.  If
possible we wait until we think we see a consensus on the mailing list.
(I say "we" since I was recently appointed to core, but being the junior
member of core I'm hardly the man in charge ;-).  Perhaps I should also
point out that in sitting here and debating the technical issues with
you, I'm not speaking for core; I'm just speaking as another member of
the community.  My opinion doesn't count any more than yours does,
unless it comes to a point of having to be settled by a core vote ...
which we'd rather avoid.)

On the sub-class returning issue you declared that you understood that
it was "good for a certain class of problems" or some such.

So I did, and I think there wasn't too much debate about that once you'd
exhibited some sample problems. As I recall it, the remaining debate
was mostly about whether we wanted to change the system's default
behavior (ie the results of SQL92-compatible syntax) to cater to that
class of problems. There was also concern about whether we shouldn't
look first at SQL3 and try to follow its lead. If I recall correctly,
you are pursuing some other document than SQL3?

To repeat - it implements DELETE and UPDATE on inheritance hierarchies
to correct old bit-rot, and it implements ONLY as relates inheritance
hierarchies to exclude sub-classes. Oh, and the emacs pgsql code style
lisp implementation is done right in the FAQ.

Fixing DELETE* and UPDATE* is clearly not going to raise any hackles,
since that won't hurt any working applications. Swapping the behavior
of SELECT and SELECT* (which is what you really mean by "ONLY", no?)
*will* break some extant applications, so the threshold for deciding
that that's a good thing to do is a lot higher. That's the point at
which we start wanting to be convinced that there's a community
consensus in favor of the idea, and also that we're not choosing the
wrong standard to follow. If we do break existing apps, we want to
break them once, not several times until we get it right...

regards, tom lane

Hannu Krosing <hannu@tm.ee> writes:

Certainly libpq is unprepared to support
multiple tuple types returned in one SELECT

IIRC Bruce removed that feature in Pg95 days claiming that it would
not be needed. If backend starts to support it again it would be
relatively easy to put back in.

Would it? libpq's internals might not care much, but it seems to me
that a rather significant API change would be needed, thus risking
breaking client applications. I'd want to see how the libpq API
changes before deciding how easy or hard this is ...

regards, tom lane

So is the "community" the hacking community?

It's kinda fuzzy, but in practice I'd say the readers of pgsql-hackers
and maybe pgsql-general.

regards, tom lane

Hannu Krosing <hannu@tm.ee> writes:

Certainly libpq is unprepared to support
multiple tuple types returned in one SELECT

IIRC Bruce removed that feature in Pg95 days claiming that it would
not be needed. If backend starts to support it again it would be
relatively easy to put back in.

Would it? libpq's internals might not care much, but it seems to me
that a rather significant API change would be needed, thus risking
breaking client applications. I'd want to see how the libpq API
changes before deciding how easy or hard this is ...

Since this came up, I don't remember removing any of this. I may have
given the OK to do it, though.

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

The current API would not change. New APIs would be added. One option is
just add PQnfieldsv(result, tuple_number) to find the number of fields
in a particular tuple.

But then we started discussing postgres' lack of streaming result sets
and how we might rectify that at the same time.

And then it was discussed that PQ will be thrown out in favour of Corba
anyway.

And then I couldn't figure out where the project is heading, so I didn't
know what to work on, so I didn't. I want to know up front if PQ is
disappearing in favour of Corba or not.

OK, there are no plans to change PQ anytime soon. What someone may do
is to implement a CORBA network service that interacts with PostgreSQL.

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

Chris <chris@bitmead.com> writes:

And then I couldn't figure out where the project is heading, so I didn't
know what to work on, so I didn't. I want to know up front if PQ is
disappearing in favour of Corba or not.

At this point, I'd say no one knows that (although if Alex's opinion
of Corba is correct, I'd bet we won't be going to Corba after all...)
You can wait and see, or you can make a guess and expend effort on the
basis of a guess.

My guess is that libpq won't be going away for a very long time. Even
if we adopted Corba or some other new protocol, we'd have a lot of
legacy clients that we'd want to support for the foreseeable future.
So it's probably worth improving libpq even if you think we will/should
adopt something else in the long run.

regards, tom lane

The Hermit Hacker wrote:

We have a list archive ... just to try and help out here, you
might want to try posting URLs to show quotes ... to back things up ...

I don't have much success with the archive. (Search for "Proposed
Changes" - the name of the thread. It yields zero results). The links
to the result urls are coloured the same whether you have visited them
or not (not a bright idea), and in general I'm skeptical the searching
works properly. I certainly can't lay my hands on quite a few important
postings.

Nope, my take on things is that your patch does things that would break
existing functionality, which won't be permitted without one helluva
good explanation ...

That is true that the ONLY aspect had controversy up front, but it
seemed to me to peter out as it was discussed and the patch was
submitted. The arguments in favour of ONLY seemed to be (a) It's what
SQL3 says, (b) It's what Informix does (c) Experience in usage suggests
that it significantly reduced programming errors. (d) The other
important point being that the patch includes a SET compatibility mode
so that old code needs only a 1 line change.

This is just a post-7.0 merge and I was expecting

it put in CVS now that 7.0 is done.

That won't happen ... v7.1, if you can get agreement, but not in the
current CVS tree ...

We're post v7.0 now, so presumably we are in pre-7.1 land right? Surely
any minor patches now can be done in a branch? I can understand
reluctance to branch with heavy development in progress pre-7.0 but once
you've released it's time to move on.

Hannu Krosing wrote:

It may be time to fork the tree into OO and beancounting editions ?
Especially so if the main tree will migrate to BDB ;-p

OOPostgreSQL sounds quite nice ;)

I hope we don't have to go there. A better relational engine and a
proper OO engine are completely complementry. That was the whole premise
of the Stonebraker research.

I should also remind people again I guess of my original design proposal
I wrote a few years ago. You can find it here
http://www.tech.com.au/postgres/

These issues have been on my mind ever since Berkeley released R4.2.

The Hermit Hacker wrote:

Chris, one quick question ... the last email I read from you stated a
bunch of things that you wanted to accomplish, but your patch only
addressed the first one. Can we focus on that and ignore the others?
Do it through step'ng stones? Or does each step only make sense in
view of the whole picture?

Each of the 3 is independant and useful in and of itself, although all 3
are needed to achieve the goal - an ODMG interface.

We can discuss one by one. It might be useful to start off with a
meta-discussion. Does everyone understand the significance of ODMG, the
the benefits of supporting it?

Tom Lane wrote:

Hannu Krosing <hannu@tm.ee> writes:

Certainly libpq is unprepared to support
multiple tuple types returned in one SELECT

IIRC Bruce removed that feature in Pg95 days claiming that it would
not be needed. If backend starts to support it again it would be
relatively easy to put back in.

Would it? libpq's internals might not care much, but it seems to me
that a rather significant API change would be needed, thus risking
breaking client applications. I'd want to see how the libpq API
changes before deciding how easy or hard this is ...

The current API would not change. New APIs would be added. One option is
just add PQnfieldsv(result, tuple_number) to find the number of fields
in a particular tuple.

But then we started discussing postgres' lack of streaming result sets
and how we might rectify that at the same time.

And then it was discussed that PQ will be thrown out in favour of Corba
anyway.

And then I couldn't figure out where the project is heading, so I didn't
know what to work on, so I didn't. I want to know up front if PQ is
disappearing in favour of Corba or not.

Tom Lane wrote:

--- but core prefers not to impose answers on the community.  If
possible we wait until we think we see a consensus on the mailing list.

So is the "community" the hacking community?

Ok then, hands up now anyone with concerns about the compatibility
aspect of this patch (taking into account the backwards compatibly SET
mode), and let's talk about it.

Tom Lane wrote:

It's kinda fuzzy, but in practice I'd say the readers of pgsql-hackers
and maybe pgsql-general.

One more time for the <general> mailing list...

Hands up if you have objections to the patch I recently submitted for
postgresql. It fixes the long standing bit-rot / bug that DELETE and
UPDATE don't work on inheritance hierarchies, and it adds the ONLY
syntax as mentioned in SQL3 and as implemented by Informix. The downside
is it breaks compatibility with the old inheritance syntax. But there is
a backward compatibility mode. I.e. "SELECT * FROM foobar*" becomes
"SELECT * FROM foobar", and "SELECT * from foobar" becomes "SELECT *
FROM ONLY foobar".

Benefits:
*) SQL3 says it.
*) Informix does it.
*) If you never used inheritance it doesn't affect you.
*) Performance is unaffected.
*) There is a backwards compatibility mode via SET.
*) My own experience says strongly that this will greatly reduce
programmer bugs because the default is much more common (laziness
usually leads us to discard the "*" to the detriment of future
inheritance data model changes.)
*) It is more OO since by default a <subclass> IS A <baseclass>.

Disadvantage:
*) You need to make a one line change to any programs that use
inheritance to include the back-compatibility SET mode.

Tom Lane wrote:

Chris <chris@bitmead.com> writes:

And then I couldn't figure out where the project is heading, so I didn't
know what to work on, so I didn't. I want to know up front if PQ is
disappearing in favour of Corba or not.

At this point, I'd say no one knows that (although if Alex's opinion
of Corba is correct, I'd bet we won't be going to Corba after all...)

What is Alex's opinion?

Tom Lane wrote:

It's kinda fuzzy, but in practice I'd say the readers of pgsql-hackers
and maybe pgsql-general.

One more time for the <general> mailing list...

Hands up if you have objections to the patch I recently submitted for
postgresql. It fixes the long standing bit-rot / bug that DELETE and
UPDATE don't work on inheritance hierarchies, and it adds the ONLY
syntax as mentioned in SQL3 and as implemented by Informix. The downside
is it breaks compatibility with the old inheritance syntax. But there is
a backward compatibility mode. I.e. "SELECT * FROM foobar*" becomes
"SELECT * FROM foobar", and "SELECT * from foobar" becomes "SELECT *
FROM ONLY foobar".

Benefits:
*) SQL3 says it.
*) Informix does it.
*) If you never used inheritance it doesn't affect you.
*) Performance is unaffected.
*) There is a backwards compatibility mode via SET.
*) My own experience says strongly that this will greatly reduce
programmer bugs because the default is much more common (laziness
usually leads us to discard the "*" to the detriment of future
inheritance data model changes.)
*) It is more OO since by default a <subclass> IS A <baseclass>.

Disadvantage:
*) You need to make a one line change to any programs that use
inheritance to include the back-compatibility SET mode.

Well, it seems many of us forgot the valid arguments for the change.
Matching SQL3 and Informix's behavior is a good thing. Considering how
broken our current inheritance implementation is, backward compatibility
is not a must, and you have a SET option for that too. 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

Chris Bitmead writes:

[ONLY]

For UPDATE and DELETE it is absolutely correct, and useful, not to
mention absolutely essential.

Could you explain how and why, preferably with a concrete example? I am
still at a loss.

the `SELECT **' syntax (bad idea, IMO),

Why is it a bad idea (considering that every ODBMS on the planet does
this)?

First of all, ODBMS and [O]RDBMS are not necessarily infinitely compatible
concepts. An ORDBMS is an RDBMS extended with OO'ish features such as
table inheritance and abstract data types to make data modeling easier for
those who like it. But below it all there's still relational algebra and
friends. An ODBMS is a paradigm shift to get rid of some restrictions in
relational databases, both technical and theoretical, the implication of
which is that it's no longer a relational database. Please correct me if
I'm wrong.

Specifically, a query on a relational database always returns a table, and
a table is a set of rows with the same number and types of columns. This
is a pretty fundamental assumption, and even accounting for the
possibility that it might be broken somehow is going to be a major effort
throughout the entire system.

Now a question in particular. I understand that this syntax might
give me some rows (a, b, c) and others (a, b, c, d, e) and perhaps others
(a, b, c, f, g, h). Now what would be the syntax for getting only (b, c),
(b, c, e) and (b, c, h)?

Finally, it seems that the same effect can be obtained with a UNION query,
padding with NULLs where necessary and perhaps judicious use of
CORRESPONDING. What would be wrong with that?

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

Chris wrote:

Tom Lane wrote:

It's kinda fuzzy, but in practice I'd say the readers of pgsql-hackers
and maybe pgsql-general.

--snip--

So it's not just me, I was using examples from Oracal 8 and was have
trouble. I started thinking, I was just missing something or maybe
just to new to SQL.

Ricahrd

Alex Pilosov wrote:

Corba IS a performance dog compared to everything else in existance.
Almost every ORB in existance is dog-slow. There are some opensource ORBs
which are getting better, but its still a ways off.

Have you tried ORBit? Supposedly those guys found other ORBs slow and
they wrote their own to be fast.

Peter Eisentraut wrote:

Chris Bitmead writes:

[ONLY]

For UPDATE and DELETE it is absolutely correct, and useful, not to
mention absolutely essential.

Could you explain how and why, preferably with a concrete example? I am
still at a loss.

The simple answer is that UPDATE and DELETE should not act different to
SELECT. If SELECT returns a certain set of records with a particular
WHERE clause, then DELETE should delete the same set of records with
identical WHERE clause and UPDATE should UPDATE the same set of records.
Which part of this is tricky?

The complex answer is in your own SQL3 research. Now of course Postgres
is not implemented that way that the SQL3 model seems to imply (a good
thing IMHO). Columns that came from super tables are stored in the most
specific table. But the end result has to conform to the description in
your other posting. I'll comment a little more on your other posting.

the `SELECT **' syntax (bad idea, IMO),

Why is it a bad idea (considering that every ODBMS on the planet does
this)?

First of all, ODBMS and [O]RDBMS are not necessarily infinitely
compatible concepts.

Why?

An ORDBMS is an RDBMS extended with OO'ish features such as
table inheritance and abstract data types to make data modeling easier
for those who like it.

The custom data type aspect of ORDBMS is a good feature. The inheritance
feature of ORDBMS is IMHO half-baked. Take the class
shape/circle/square example...
CREATE TABLE SHAPE( ..);
CREATE TABLE SQUARE(x1, y1, x2, y2) INHERITS(shape);
CREATE TABLE CIRCLE(x, y, radius) INHERITS(shape);

I can't just go SELECT * FROM SHAPE and call some C++ method to display
the shape on the screen. If I maintain an attribute in SHAPE called
"classname" manually, then I can SELECT * FROM SHAPE, and then do a
separate query on the subclass when I know the type - very inefficient.
Or I can do 3 separate queries. But then I'm hosed when I add a TRIANGLE
type.

What I really want is..

Result r = Query<Shape>.select("SELECT ** FROM SHAPE");
foreach(r, item) {
item->display();
}

Which still will work when I add a triangle. I.e. typical polymorphism
code maintenance advantage.

Which is what object databases do or an object relational mapper like
Persistance do. Without that ability I would argue there's very limited
point in having inheritance at all.

But below it all there's still relational algebra and
friends. An ODBMS is a paradigm shift to get rid of some restrictions
in relational databases, both technical and theoretical, the
implication of
which is that it's no longer a relational database. Please correct me
if I'm wrong.

It's no longer a purely relational database true. I think it's always
been a crazy idea that everything should be squeezed into a pure
table/column model.

Specifically, a query on a relational database always returns a table,
and a table is a set of rows with the same number and types of columns.
This is a pretty fundamental assumption, and even accounting for the
possibility that it might be broken somehow is going to be a major
effort throughout the entire system.

It's a pretty fundamentally limiting assumption. If you're saying that
this might be a lot of work to fix I think I agree. If you're saying
that you can't see the relational and object models being merged into a
coherent and useful combination then I disagree. I can see no conflict
at all between them. Both models are like seeing half the world. Both
models without the other are limiting.

Now a question in particular. I understand that this syntax might
give me some rows (a, b, c) and others (a, b, c, d, e) and perhaps others
(a, b, c, f, g, h). Now what would be the syntax for getting only (b, c),
(b, c, e) and (b, c, h)?

I don't think I understand this question.

Finally, it seems that the same effect can be obtained with a UNION
query,
padding with NULLs where necessary and perhaps judicious use of
CORRESPONDING. What would be wrong with that?

Several things. Firstly, what happens when you introduce TRIANGLE? You
have to rewrite every query in your system. Secondly, what if you have
20 classes in your hierarchy each with 20 different fields. Now you have
a UNION with 400 fields, most of which are NULL.

CREATE TABLE SHAPE( ..);
CREATE TABLE SQUARE(x1, y1, x2, y2) INHERITS(shape);
CREATE TABLE CIRCLE(x, y, radius) INHERITS(shape);

I can't just go SELECT * FROM SHAPE and call some C++ method to display
the shape on the screen. If I maintain an attribute in SHAPE called
"classname" manually, then I can SELECT * FROM SHAPE, and then do a
separate query on the subclass when I know the type - very inefficient.
Or I can do 3 separate queries. But then I'm hosed when I add a TRIANGLE
type.

What I really want is..

Result r = Query<Shape>.select("SELECT ** FROM SHAPE");
foreach(r, item) {
item->display();
}

Which still will work when I add a triangle. I.e. typical polymorphism
code maintenance advantage.

Which is what object databases do or an object relational mapper like
Persistance do. Without that ability I would argue there's very limited
point in having inheritance at all.

I can agree with that. As I wrote a relational mapper for Smalltalk/X
based on the libpq API I noticed the same problems, when doing mapping on
tables.

But some questions/comments about that:

a) How are the indices handled ? If I define an index on an attribute
defined in TABLE SHAPE all subclasses are also handled by this index
or do we have an index for the base table and each sub table on
this attribute ?

b) Please do not make the libpq API too compilcated ! It's a charm how
small the API is and how easy the initial connection to a psqgl
database is -- compare it against the ODBC API ....

b.1)
Despite the ODBC API I rather would like to see to enhance the idea
of result
sets supported by the libpq-API. I do not need to query each tuple
what it delivers to me. I would like to open the result, query
the structure and then handle the data. If the database returns
multiple different sets (results from different tables): ok: do it the
same way for each result set.

b.2)
There were some postings about other delivering methods to retrieve
the information from each tuple. Today we get an ASCII-representation
of the result tuple and the client has to convert it.

Some were not very happy about it, but I like it. Some were concerned
about the fact, that they have to copy the result to the information
structure within their software. When you use software systems, which
are based on garbage collection systems, then one ALMOST EVER has
to do it.

c)
I would like to see more ideas about the extension of pgsql to become
an active database. The notification systen is not enough, because
it does not return the most interesting informations.

I myself would like to see something like the VERSANT event system.

d)
Please only add basic, language independent, support for
inheritance - special features can very often better simulated by
software on the client side. The best example is the introduction
of sequences.

Marten

Chris writes:

I.e. "SELECT * FROM foobar*" becomes "SELECT * FROM foobar", and
"SELECT * from foobar" becomes "SELECT * FROM ONLY foobar".

This aspect of the patch I wholeheartedly agree on. The rest I'm not sure
about -- yet. :)

Benefits:
*) SQL3 says it.

That is unfortunately false for the patch in general.

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

Peter Eisentraut wrote:

the `SELECT **' syntax (bad idea, IMO),

Why is it a bad idea (considering that every ODBMS on the planet does
this)?

First of all, ODBMS and [O]RDBMS are not necessarily infinitely compatible
concepts. An ORDBMS is an RDBMS extended with OO'ish features such as
table inheritance and abstract data types to make data modeling easier for
those who like it.

And which may be ignored by those who don't, just like SELECT ** .

But below it all there's still relational algebra and
friends. An ODBMS is a paradigm shift to get rid of some restrictions in
relational databases, both technical and theoretical, the implication of
which is that it's no longer a relational database. Please correct me if
I'm wrong.

Adding DATE and TIME datatypes or functions to SQL may have also seemed
a
paradigm shift but seems quite essential once it is done.

Specifically, a query on a relational database always returns a table, and
a table is a set of rows with the same number and types of columns.

Says who ? ;)

This is a pretty fundamental assumption, and even accounting for the
possibility that it might be broken somehow is going to be a major effort
throughout the entire system.

In first round ** could we disallowed in subselects and other tricky
parts.

Now a question in particular. I understand that this syntax might
give me some rows (a, b, c) and others (a, b, c, d, e) and perhaps others
(a, b, c, f, g, h). Now what would be the syntax for getting only (b, c),
(b, c, e) and (b, c, h)?

What would you need that for ?

If its really needed we could implement something like

SELECT B,C,E?,H? FROM BASECLASS.

but as E can be an INT in one subclass and TIMESTAMP or VARBINARY in
other
it would perhaps be better to do

SELECT B,C,SUB1.E?,SUB3.H? FROM BASECLASS.

which means the attribute E defined in subclass SUB1 (an inherited by
its
descendants)

or perhaps

SELECT B,C,E OF SUB1,H OF SUB3 FROM BASECLASS.

to be style-compatible vith general verbosity and english-likeness of
SQL ;)

Finally, it seems that the same effect can be obtained with a UNION query,
padding with NULLs where necessary and perhaps judicious use of
CORRESPONDING. What would be wrong with that?

It would be overly complex and error-prone and need a rewrite each time
a new
sub-class is added.

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

Marten Feldtmann wrote:

I can agree with that. As I wrote a relational mapper for Smalltalk/X
based on the libpq API I noticed the same problems, when doing mapping on
tables.

But some questions/comments about that:

a) How are the indices handled ? If I define an index on an attribute
defined in TABLE SHAPE all subclasses are also handled by this index
or do we have an index for the base table and each sub table on
this attribute ?

At the moment there is a separate index for each subclass, but this
should probably not be the default.

b) Please do not make the libpq API too compilcated ! It's a charm how
small the API is and how easy the initial connection to a psqgl
database is -- compare it against the ODBC API ....

There will be 2 levels of API. An important part of an object database
is the existance of the client side cache. The lowest level API would be
something like libpq - that is uncached. Next there would be ODMG
interfaces for various languages that incorporate the client side cache.
I would say the only people who would continue to use libpq level would
be people writing higher level interfaces. ODMG is just too convenient.

b.1)
Despite the ODBC API I rather would like to see to enhance the idea
of result
sets supported by the libpq-API. I do not need to query each tuple
what it delivers to me. I would like to open the result, query
the structure and then handle the data. If the database returns
multiple different sets (results from different tables): ok: do it the
same way for each result set.

I'm a bit vague on what you mean here. But if you go the full OO way,
the language polymorphism will do all the handling of different result
sets. The ODMG layer will do the hard work.

b.2)
There were some postings about other delivering methods to retrieve
the information from each tuple. Today we get an ASCII-representation
of the result tuple and the client has to convert it.

Some were not very happy about it, but I like it. Some were concerned
about the fact, that they have to copy the result to the information
structure within their software. When you use software systems, which
are based on garbage collection systems, then one ALMOST EVER has
to do it.

Again, using ODMG takes the hard work out of it. You stop caring about
what format the information is delivered in.

c)
I would like to see more ideas about the extension of pgsql to become
an active database. The notification systen is not enough, because
it does not return the most interesting informations.

I myself would like to see something like the VERSANT event system.

Oh, a Versant fan. Good, I like Versant. The only flaw in the Versant
event system is events can be lost when the receiver is dead, which may
or may not matter depending on the application.

Peter Eisentraut wrote:

Chris writes:

I.e. "SELECT * FROM foobar*" becomes "SELECT * FROM foobar", and
"SELECT * from foobar" becomes "SELECT * FROM ONLY foobar".

This aspect of the patch I wholeheartedly agree on. The rest I'm not sure
about -- yet. :)

Benefits:
*) SQL3 says it.

That is unfortunately false for the patch in general.

Huh?

On Sun, 21 May 2000, Chris Bitmead wrote:

Peter Eisentraut wrote:

Chris writes:

I.e. "SELECT * FROM foobar*" becomes "SELECT * FROM foobar", and
"SELECT * from foobar" becomes "SELECT * FROM ONLY foobar".

This aspect of the patch I wholeheartedly agree on. The rest I'm not sure
about -- yet. :)

Benefits:
*) SQL3 says it.

I also agree about the usage of ONLY, as long as it follows the
official standardized SQL3 spec.

About returning multiple types of rows again: I don't see that in SQL3 so far
(difficult and time consuming to read). If it were allowed, you might have to
specify the level to dig to in the tree. The rows are shared among supertable
and subtables. One row in a leaf table has subrows in all its supertables up
the tree. If you do a "SELECT * FROM supertable*" (for example, if you were to
redefine table* to mean select heterogeneous rows), what row will you get for a
row that exists in a leaf? The same row is in all tables between supertable
and the leaf. I suppose it would be necessary to have the query check each row
and see how far down the tree it goes, or the system keeps track of that and
returns the row-type from the table that inserted it. OR, there could be some
extra specifier like "SELECT * FROM supertable DIGGING TO LEVEL 3". In this
case, it would only look down into the tree to 3 levels below supertable and
you'd never get row-types that are down lower than level 3. Anyhow, I still
don't think returning multple row-types is going to happen, not that I have any
authority one way or the other! :-)

--
Robert B. Easter
reaster@comptechnews.com

Chris Bitmead wrote:

In this
case, it would only look down into the tree to 3 levels below supertable and
you'd never get row-types that are down lower than level 3. Anyhow, I still
don't think returning multple row-types is going to happen,

OTOH, I'm pretty sure that original Postgres did allow for it.

not that I have any authority one way or the other! :-)

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

Hannu Krosing wrote:

Peter Eisentraut wrote:

Now a question in particular. I understand that this syntax might
give me some rows (a, b, c) and others (a, b, c, d, e) and perhaps others
(a, b, c, f, g, h). Now what would be the syntax for getting only (b, c),
(b, c, e) and (b, c, h)?

What would you need that for ?

In OO terms it should be illegal. In terms of any one class, there is a
defined set of columns that can be seen. What Peter is asking for is a union
of selects on different classes. The ordinary union rules should apply.

If its really needed we could implement something like

SELECT B,C,E?,H? FROM BASECLASS.

but as E can be an INT in one subclass and TIMESTAMP or VARBINARY in
other

I don't think that should be allowed. It violates inheritance principles,
since the types are not compatible.

There is quite a lot right with inheritance as it is. We support multiple
inheritance, and columns with the same name are merged in the child. What we
immediately lack are features to make inheritance properly useful:

* shared index - an index that should point to the correct child class for
quick recovery of rows from inheritance hierarchies.

* inheritance of constraints, including Primary/Foreign keys (does this
imply the necessity of turning inheritance off in certain cases?)

* handling of some write operations on a hierarchy: DELETE and UPDATE
(INSERT must require the exact class to be specified)

* automatic use of inheritance hierarchies (use ONLY to avoid it)

* ALTER ... ADD COLUMN inserting columns in the correct positions in
child tables; alternatively, have column numbering independent of
the physical representation, so that columns can be added at the end
but shown in the correct place by SELECT.

There are further complexities in OO which might be desirable, but would
require a lot of design work. One fundamental feature of pure OO is that
classes carry their own methods, whereas SELECT (for example) imposes a
global operation on the various classes of the inheritance tree. This
makes the following problematic:

* renaming columns in multiple inheritance (to avoid column merging, or to
allow a child's column to be of a different type) - what would SELECT do
with them?

* deferred classes - tables that are used only for inheritance rather than
for storing data rows - how could these be specified and implemented?

No doubt further research would bring up many more examples.

I'm not sure it is feasible to make PostgreSQL into a proper OO database,
but getting those first five features would really be useful.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP key from public servers; key ID 32B8FAA1
========================================
"We are troubled on every side, yet not distressed; we
are perplexed, but not in despair; persecuted, but not
forsaken; cast down, but not destroyed; Always bearing
about in the body the dying of the Lord Jesus, that
the life also of Jesus might be made manifest in our
body." II Corinthians 4:8-10

Chris Bitmead wrote:

While SQL3 talks about trees and leaf rows, it's not implemented like
that, so all this worrying about digging down trees and leafs is all a
bit mute.

Moot. ;-)

At a minimum, it seems to me, the backend must support the
concept of multiple tuples with different attributes at the
relation level since concurrency and rollback-ability of ALTER
TABLE ADD COLUMN will cause two concurrent transactions to see a
single relation with different attributes. It doesn't seem a
large leap to support this concept for OO purposes from "leaf" to
"base". For "base" to "leaf" type queries, wouldn't it be
acceptable to return the base attributes only, as long as the
equivalent of run-time type information could be had from the
OID?

Just curious,

Mike Mascari

Mike Mascari wrote:

At a minimum, it seems to me, the backend must support the
concept of multiple tuples with different attributes at the
relation level since concurrency and rollback-ability of ALTER
TABLE ADD COLUMN will cause two concurrent transactions to see a
single relation with different attributes. It doesn't seem a
large leap to support this concept for OO purposes from "leaf" to
"base". For "base" to "leaf" type queries, wouldn't it be
acceptable to return the base attributes only, as long as the
equivalent of run-time type information could be had from the
OID?

How are you going to be able to go shape.display() and have it work for
a triangle, if the triangle's apex's weren't retrieved?

Peter Eisentraut wrote:

On Sun, 21 May 2000, Hannu Krosing wrote:

Now a question in particular. I understand that this syntax might
give me some rows (a, b, c) and others (a, b, c, d, e) and perhaps others
(a, b, c, f, g, h). Now what would be the syntax for getting only (b, c),
(b, c, e) and (b, c, h)?

What would you need that for ?

Gee, lemme think. Why do we have SELECT a, b, c at all? Why doesn't
everyone just use SELECT * and filter the stuff themselves? What if I want
to apply a function on `h' but not on the others? Don't tell me there's no
syntax for that, only for getting all columns. (And the fact that your
proposed syntaxes seem completely ad hoc and home-brewed doesn't make me
feel better.)

Oh, now I understand what you asking. Yes I did suggest that you be
allowed to specify sub-class attributes that don't occur in the
super-class. The syntax would be the obvious - either attrname, or
class.attrname.

As far as syntax is concerned I don't think I'm welded to anything in
particular, so suggestions are welcome.

Chris wrote:

I.e. "SELECT * FROM foobar*" becomes
"SELECT * FROM foobar", and "SELECT * from foobar" becomes "SELECT *
FROM ONLY foobar".

As a user, is this all I need to know?

I'd just ask that the documentation be updated simultaneously. I don't
know SQL3 or any other vendor's implementation. I'm pretty dependant on
the docs to know what I can & can't do, and how to do it. I'm easily
confused.

-Ron-

Hannu Krosing wrote:

but as E can be an INT in one subclass and TIMESTAMP or VARBINARY in
other

I don't think that should be allowed. It violates inheritance principles,
since the types are not compatible.

I see ... here's a person who has always programmed with typed
languages and now thinks, that this is the right definition .... it's
much more out there in the world. Open your mind and think about the
following:

An attribute named "a" of "type" TIMESTAMP of an instance of a class
can be seen as a relation from this class to the class TIMESTAMP and
this relation is named "a".

And if you're on the way to relations you're not far away to see,
that a relation is of course not limited to show to one specific class
... but perhaps to all subclasses also ... and this is not a
violation.

I know, that for many people these are only theoretical questions and
they may even be true with that, but "violation of inheritance
principles" is simply wrong.

But I also know, that we deal with a relational database and I do not
expect, that it will be as good as a pure object-oriented database -
but all those great wrapper software in the market work with
relational databases and they work pretty well - but I also see, that
they only use the basic technology to do their work.

The reason seems to be, that all those nice oo-features within all
those databases do not scale very well ... and they're good for a
single implementation. There're other problems out there:

- caching at the client side

- more powerful db desing evolution features. Change the type, the
length of a typed attribute

Marten

While SQL3 talks about trees and leaf rows, it's not implemented like
that, so all this worrying about digging down trees and leafs is all a
bit mute.

"Robert B. Easter" wrote:

If it were allowed, you might have to
specify the level to dig to in the tree. The rows are shared among supertable
and subtables. One row in a leaf table has subrows in all its supertables up
the tree. If you do a "SELECT * FROM supertable*" (for example, if you were to
redefine table* to mean select heterogeneous rows), what row will you get for a
row that exists in a leaf? The same row is in all tables between supertable
and the leaf. I suppose it would be necessary to have the query check each row
and see how far down the tree it goes, or the system keeps track of that and
returns the row-type from the table that inserted it. OR, there could be some
extra specifier like "SELECT * FROM supertable DIGGING TO LEVEL 3". In this
case, it would only look down into the tree to 3 levels below supertable and
you'd never get row-types that are down lower than level 3. Anyhow, I still
don't think returning multple row-types is going to happen, not that I have any
authority one way or the other! :-)

--
Robert B. Easter
reaster@comptechnews.com

--
Chris Bitmead
mailto:chris@bitmead.com
http://www.techphoto.org - Photography News, Stuff that Matters

Marten Feldtmann wrote:

Hannu Krosing wrote:

but as E can be an INT in one subclass and TIMESTAMP or VARBINARY in
other

I don't think that should be allowed. It violates inheritance principles,
since the types are not compatible.

I see ... here's a person who has always programmed with typed
languages and now thinks, that this is the right definition .... it's
much more out there in the world. Open your mind and think about the
following:

An attribute named "a" of "type" TIMESTAMP of an instance of a class
can be seen as a relation from this class to the class TIMESTAMP and
this relation is named "a".

And if you're on the way to relations you're not far away to see,
that a relation is of course not limited to show to one specific class
... but perhaps to all subclasses also ... and this is not a
violation.

However the example I was referring to talked of INT4, TIMESTAMP or VARBINARY.

These are not subclasses but totally unrelated. Suppose you had

parent (id char(2))
child1 (a int4)
child2 (a timestamp)

and someone asks for

select sum(a) from parent*

since the types are incompatible, the answer would be nonsense.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP key from public servers; key ID 32B8FAA1
========================================
"We are troubled on every side, yet not distressed; we
are perplexed, but not in despair; persecuted, but not
forsaken; cast down, but not destroyed; Always bearing
about in the body the dying of the Lord Jesus, that
the life also of Jesus might be made manifest in our
body." II Corinthians 4:8-10

Oliver Elphick wrote:

Marten Feldtmann wrote:

Hannu Krosing wrote:

but as E can be an INT in one subclass and TIMESTAMP or VARBINARY in
other

I don't think that should be allowed. It violates inheritance principles,
since the types are not compatible.

I see ... here's a person who has always programmed with typed
languages and now thinks, that this is the right definition .... it's
much more out there in the world. Open your mind and think about the
following:

An attribute named "a" of "type" TIMESTAMP of an instance of a class
can be seen as a relation from this class to the class TIMESTAMP and
this relation is named "a".

And if you're on the way to relations you're not far away to see,
that a relation is of course not limited to show to one specific class
... but perhaps to all subclasses also ... and this is not a
violation.

However the example I was referring to talked of INT4, TIMESTAMP or VARBINARY.

These are not subclasses but totally unrelated. Suppose you had

parent (id char(2))
child1 (a int4)
child2 (a timestamp)

and someone asks for

select sum(a) from parent*

since the types are incompatible, the answer would be nonsense.

MS Excel for example SUMs only things summable, by which logic in this
case
the sum would/could/should be sum of int4 colums.

In real world not all things are summable.

OTOH for schema
parent (id char(2))
child1 (a orange)
child2 (a apple)

select sum(a) from parent*

could yield "N apples and M oranges" or possibly "X fruits" if orange
and
apple were subtypes of fruit. Yes, really ;)

Or depending on how the sum() function is defined it could even be "Y
kg" .

---------
Hannu

Oliver Elphick wrote:

These are not subclasses but totally unrelated. Suppose you had

parent (id char(2))
child1 (a int4)
child2 (a timestamp)

and someone asks for

select sum(a) from parent*

since the types are incompatible, the answer would be nonsense.

That query would be disallowed, for the reason you note. Ambigous
coloumns would need to be specified by class.attribute.

On Sat, 20 May 2000, Chris wrote:

And then I couldn't figure out where the project is heading, so I didn't
know what to work on, so I didn't. I want to know up front if PQ is
disappearing in favour of Corba or not.

Eventually ... maybe. But, I agree with Tom on this, it will be awhile
before libpq can/will disappear, as there is too much code out there that
relies on it. Figuring our release cycles being 4-6mos, and figuring that
it would be *at least* 2 full releases after Corba was fully implemented
before we could phase out libpq, figure, oh, 2 years at least before libpq
*could* disappear :)

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

The Hermit Hacker wrote:

And then I couldn't figure out where the project is heading, so I didn't
know what to work on, so I didn't. I want to know up front if PQ is
disappearing in favour of Corba or not.

Eventually ... maybe. But, I agree with Tom on this, it will be awhile
before libpq can/will disappear, as there is too much code out there that
relies on it. Figuring our release cycles being 4-6mos, and figuring that
it would be *at least* 2 full releases after Corba was fully implemented
before we could phase out libpq, figure, oh, 2 years at least before libpq
*could* disappear :)

When you say "libpq", do you mean the API or the protocol? The API can
stay forever if it is implemented in terms of a Corba API.

I've been looking into it. The thing I've come up against now is
postgres' advanced types. Does every postgres type, user-defined or not
now need a Corba IDL definition if we go to Corba? If so, how do people
feel about it? If we go to a binary representation protocol (which I
believe is the right thing BTW), there has to be something which can
marshal etc, and using IDL to achieve it may as well be it.

But when I started to realise this aspect and the amount of work, Corba
started to get pushed down my TODO list in favour of a quick fix to the
current protocol to do my OO stuff.

the tree. If you do a "SELECT * FROM supertable*" (for example, if you were to
redefine table* to mean select heterogeneous rows), what row will you get for a
row that exists in a leaf? The same row is in all tables between supertable
and the leaf. I suppose it would be necessary to have the query check each row
and see how far down the tree it goes, or the system keeps track of that and
returns the row-type from the table that inserted it. OR, there could be some
extra specifier like "SELECT * FROM supertable DIGGING TO LEVEL 3". In this
case, it would only look down into the tree to 3 levels below supertable and
you'd never get row-types that are down lower than level 3. Anyhow, I still
don't think returning multple row-types is going to happen, not that I have any
authority one way or the other! :-)

--
Robert B. Easter
reaster@comptechnews.com

Your example is a very good example, that shows, why multiple result
sets are needed to get a very good object-oriented system !

Everyone here on this lists should think about: "What do we expect
from on object-oriented extension and how can it help me to improve my
system".

As an example: My software background is Smalltalk and relational-
and object-oriented databases. Now I use relational databases and from
this technology I use only a small part to do my mapping.

After reading all the postings here on the lists I looked at my
wrapper and asked myself: how would it benefit from an oo-extension.

And the result was pretty much frustrated:

- the OID (SEQUENCE's) are useless (ok, I say it again and again). Give
PostgreSQL the OID and ask PostgreSQL to return the attributes of this
object. Perhaps even with class informations !

PostgreSQL is not able to do that ! Think about this and you see
the usage of the OID in perhaps a different way :-)

Therefore: for object system you need complete other types of object
identification numbers.

- query over a hierarchy of classes ! See the example above ! Until
you're not able to return multiple sets you get too much garbage or
you need to many queries or you need much more disc-space, depending
of the way you wrap classes to tables. This feature is a CRITICAL
one ! This may push the performance, depending how it is done.

- for associations (m:n) I still need additional help tables, but
that is ok :-)

- no support for tree structures !

- more powerful statements DDL to change the structure of a database !

- no support to inform the client about changes inthe database !

And that's it ! All the other stuff mentioned here are syntactical
sugar for people doing object-oriented database queries over pgsql
or hoping to structure their work - but I do not see, that it's
a real win.

Very frustrating !

Marten Feldtmann

- the OID (SEQUENCE's) are useless (ok, I say it again and again). Give
PostgreSQL the OID and ask PostgreSQL to return the attributes of this
object. Perhaps even with class informations !

PostgreSQL is not able to do that ! Think about this and you see
the usage of the OID in perhaps a different way :-)

Therefore: for object system you need complete other types of object
identification numbers.

I agree, that's why I have suggested an implied super-class "Object" for
all postgresql objects. Then you could do "SELECT ** FROM object WHERE
oid=?". The ability to place an index over sub-class hierarchies (in
this case oid for all objects) would get the good performance.

- query over a hierarchy of classes ! See the example above ! Until
you're not able to return multiple sets you get too much garbage or
you need to many queries or you need much more disc-space, depending
of the way you wrap classes to tables. This feature is a CRITICAL
one ! This may push the performance, depending how it is done.

Yep.

- for associations (m:n) I still need additional help tables, but
that is ok :-)

Actually, postgres can have arrays of oids which is the ODBMS way of
handling associations. Last I looked there are some contrib functions
for doing things like ...

CREATE TABLE foo( bar [] );
CREATE TABLE bar( ... etc);
SELECT bar.** from bar, foo where array_in(bar.oid, foo.bar) and
foo.oid=?". In other words, to retrieve all the objects in a list.
(forget the actual function name).

- no support for tree structures !

AGAIN AGREE! Original postgres had a syntax "SELECT* from foo" to get a
transitive closure on a tree! Why this was removed (argh!) I can only
guess.

- more powerful statements DDL to change the structure of a database !

Yep, important.

- no support to inform the client about changes inthe database !

Havn't even looked at that.

Therefore: for object system you need complete other types of object
identification numbers.

I agree, that's why I have suggested an implied super-class "Object" for
all postgresql objects. Then you could do "SELECT ** FROM object WHERE
oid=?". The ability to place an index over sub-class hierarchies (in
this case oid for all objects) would get the good performance.

I can not believe, that this will result in a good performance. This
column (object identifier) would need an index to cover ALL objects
... and this index will be growing and now image a system with about
1.000.000 objects and now try to insert a new object. Indices on such
large mount of value maybe a problem.

On the other hand: the solution you mentioned can be done without an
implied table - which would be a special solution. The application can
create the "super"-table and should be responsible for it.

Actually, postgres can have arrays of oids which is the ODBMS way of
handling associations. Last I looked there are some contrib functions
for doing things like ...

CREATE TABLE foo( bar [] );
CREATE TABLE bar( ... etc);
SELECT bar.** from bar, foo where array_in(bar.oid, foo.bar) and
foo.oid=?". In other words, to retrieve all the objects in a list.
(forget the actual function name).

Have you ever create a 1:n association with about 800 entries ?
Actually I do not know, how many entries such an array may
have. Unlimited ? How do I remove an entry, how do I delete an
entry. I may have a closer look at that.

- no support to inform the client about changes inthe database !

Havn't even looked at that.

But here again an active system may be build on top of the system we
already have:

- update, insert, deletes are catched via triggers (on commit)
these trigger functions do retrieve the object-id of the objects
changed and write the result into a special table.

- another software has notification on this special table and managed
the ip-commuication to the clients.

Marten

Marten Feldtmann wrote:

Therefore: for object system you need complete other types of object
identification numbers.

I agree, that's why I have suggested an implied super-class "Object" for
all postgresql objects. Then you could do "SELECT ** FROM object WHERE
oid=?". The ability to place an index over sub-class hierarchies (in
this case oid for all objects) would get the good performance.

I can not believe, that this will result in a good performance. This
column (object identifier) would need an index to cover ALL objects
... and this index will be growing and now image a system with about
1.000.000 objects and now try to insert a new object. Indices on such
large mount of value maybe a problem.

On the other hand: the solution you mentioned can be done without an
implied table - which would be a special solution. The application can
create the "super"-table and should be responsible for it.

The implied table doesn't do anything to performance. Having an index on
that table obviously needs to be maintained and the decision to create
such an index would be by the user. So the user can make use of such an
implied super-table or not as they please. But having such a global
index is necessary for an ODBMS, and I can tell you that for the Versant
ODBMS it is lightning fast even with gigabytes of data (I have seen
Versant grown to 100 Gig). Versant does use an indexing mechanism.

Have you ever create a 1:n association with about 800 entries ?

In postgres, no. In other ODBMS, yes easily.

Actually I do not know, how many entries such an array may
have. Unlimited ?

To work properly we do need TOAST so that tuples can grow bigger.

How do I remove an entry, how do I delete an
entry. I may have a closer look at that.

Adding and deleting entries would be done in memory and then the
attribute updated in one go. Of course with an ODBMS you can create more
sophisticated data structures if you need really huge arrays, like roll
your own btree, or whatever thing you can find in Knuth.

Marten Feldtmann wrote:

But here again an active system may be build on top of the system we
already have:

- update, insert, deletes are catched via triggers (on commit)
these trigger functions do retrieve the object-id of the objects
changed and write the result into a special table.

- another software has notification on this special table and managed
the ip-commuication to the clients.

Extending NOTIFY to take at least ONE string argument or OID would go a
long long way. Even better would be for it to take an "Object", in the
one-supertable sense.

So triggers or whatever can just notify interested parties about changes.

This has been on my personal todo for severeal years already ;)

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

One more time for the <general> mailing list...

Hands up if you have objections to the patch I recently submitted for
postgresql. It fixes the long standing bit-rot / bug that DELETE and
UPDATE don't work on inheritance hierarchies, and it adds the ONLY
syntax as mentioned in SQL3 and as implemented by Informix.
The downside
is it breaks compatibility with the old inheritance syntax.
But there is
a backward compatibility mode. I.e. "SELECT * FROM foobar*" becomes
"SELECT * FROM foobar", and "SELECT * from foobar" becomes "SELECT *
FROM ONLY foobar".

Benefits:
*) SQL3 says it.

Imho this alone more than justifies the patch.
We should also change our keyword "inherits" to "under".

Andreas

Chris Bitmead wrote:

- no support for tree structures !

AGAIN AGREE! Original postgres had a syntax "SELECT* from foo" to get a
transitive closure on a tree! Why this was removed (argh!) I can only
guess.

This is what I got sneaked into TODO (or at least I think it must be it ;):

EXOTIC FEATURES

* Add sql3 recursive unions

From my reading of SQL3 draft a few years ago I concluded that this was wat it
described

Now they seem to have RECURSIVE VIEWs that are used as follows:

CREATE RECURSIVE VIEW APPLICABLE_ROLES ( GRANTEE, ROLE_NAME, IS_GRANTABLE ) AS
( ( SELECT GRANTEE, ROLE_NAME, IS_GRANTABLE
FROM DEFINITION_SCHEMA.ROLE_AUTHORIZATION_DESCRIPTORS
WHERE GRANTEE IN ( CURRENT_USER, 'PUBLIC' ) )
UNION
( SELECT RAD.GRANTEE, RAD.ROLE_NAME, RAD.IS_GRANTABLE
FROM DEFINITION_SCHEMA.ROLE_AUTHORIZATION_DESCRIPTORS RAD
JOIN
APPLICABLE_ROLES R
ON
RAD.GRANTEE = R.ROLE_NAME ) );

The definition of the meaning of RECURSIVE is something I should read in the
morning ;~]

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

On Wed, 24 May 2000, Zeugswetter Andreas SB wrote:

One more time for the <general> mailing list...

Hands up if you have objections to the patch I recently submitted for
postgresql. It fixes the long standing bit-rot / bug that DELETE and
UPDATE don't work on inheritance hierarchies, and it adds the ONLY
syntax as mentioned in SQL3 and as implemented by Informix.
The downside
is it breaks compatibility with the old inheritance syntax.
But there is
a backward compatibility mode. I.e. "SELECT * FROM foobar*" becomes
"SELECT * FROM foobar", and "SELECT * from foobar" becomes "SELECT *
FROM ONLY foobar".

Benefits:
*) SQL3 says it.

Imho this alone more than justifies the patch.
We should also change our keyword "inherits" to "under".

I don't agree. UNDER only provides for single inheritance according to spec.
Making it multiple inherit would break UNDER's basic idea of enabling hierarchy
trees that contain subtables under a single maximal supertable. Its like a
body that grows by having organs and cells inside it. INHERIT is like two or
more separate bodies that together, yield an independent offspring. UNDER and
INHERIT can coexist and be used together.

CREATE TABLE bike (
);
CREATE TABLE motorbike UNDER bike (
) INHERITS (pistonengine);
CREATE table harley (
) UNDER motorbike;

CREATE engine (
);
CREATE TABLE pistonengine UNDER engine (
)
CREATE TABLE jetengine UNDER engine (
);

Stuff like that.

CREATE motorbike (
) INHERITS (bike, motor);
is ok too. But the meaning is different than above. It creates an independent
child table that is not contained under either parent so that the parents can
be dropped. You use UNDER when the child/subtabe to share the exact same
physical PRIMARY KEY of the SUPERTABLE. In inherit, the child inherits a
composite key from the parents, but that key is new physically, not the same
physically as any parents.

I just think that since UNDER is limited by the spec, (and there is a
difference anyway), that INHERITS stands on its own and can be used with UNDER
to pull attributes into the tree from another tree/table, linking separate
trees together in an nondependent way.

Andreas

--
Robert B. Easter
reaster@comptechnews.com

"Robert B. Easter" wrote:

Imho this alone more than justifies the patch.
We should also change our keyword "inherits" to "under".

I don't agree. UNDER only provides for single inheritance according to spec.
Making it multiple inherit would break UNDER's basic idea of enabling hierarchy
trees that contain subtables under a single maximal supertable.

I don't see that it's a "basic idea". I see it as crippled subset of
SQL3-94.

is ok too. But the meaning is different than above. It creates an independent
child table that is not contained under either parent so that the parents can
be dropped.

I wouldn't like to define an object model in terms of what happens when
the meta-data is modified.

You use UNDER when the child/subtabe to share the exact same
physical PRIMARY KEY of the SUPERTABLE. In inherit, the child inherits a
composite key from the parents, but that key is new physically, not the same
physically as any parents.

Issues like primary keys are the sort of stuff that probably kept the
committee arguing long enough they were too lazy to come to a decision.
Myself, I'm not too interested in primary keys since they are not a very
OO idea anyway.

Chris Bitmead wrote:

"Robert B. Easter" wrote:

Imho this alone more than justifies the patch.
We should also change our keyword "inherits" to "under".

I don't agree. UNDER only provides for single inheritance according to spec.
Making it multiple inherit would break UNDER's basic idea of enabling hierarchy
trees that contain subtables under a single maximal supertable.

I don't see that it's a "basic idea". I see it as crippled subset of
SQL3-94.

Me too.

OTOH single inheritance has the advantage that it can be implemented
with _all_
subtables stored in single "physical" table, whereas multiple
inheritance can't,
which makes sharing thinkgs like primary keys and other constraints much
easier
to implement as well.

You use UNDER when the child/subtabe to share the exact same
physical PRIMARY KEY of the SUPERTABLE. In inherit, the child inherits a
composite key from the parents,

That composite key must actually still be two unique key (and thus
doube-uniqe ;)
which does not make much sense.

but that key is new physically, not the same physically as any parents.

Maybe what you are trying to accomplice by your definition of INHERITS
is
better done by aggregation ?

create table engine (volume float);
create table wheel(circumference float);
create table car(
car_engine engine,
car_wheels wheel[4]
);

At least this fits better with may feeling that a car is not a "kind of"
engine.

And this should be possible with PostgreSQL now (except that type _wheel
(for array of wheels) is not generated automatically and so only the
following is

create table car(
car_engine engine,
car_wheel1 wheel,
car_wheel2 wheel,
car_wheel3 wheel,
car_wheel4 wheel
);

which probably is a bug ;(

)

Issues like primary keys are the sort of stuff that probably kept the
committee arguing long enough they were too lazy to come to a decision.

It sure is an issue for multiple inheritance, at least when you disallow
multiple primary keys on things that "are" both A and B.

Myself, I'm not too interested in primary keys since they are not a very
OO idea anyway.

Maybe not OO but useful in RDBM anyway. One could argue that table.pk ==
oid.
And when implemented that way it would make finding an "object" in an
RDBM
very easy ;)

------
Hannu

Benefits:
*) SQL3 says it.

Imho this alone more than justifies the patch.
We should also change our keyword "inherits" to "under".

I don't agree. UNDER only provides for single inheritance
according to spec.
Making it multiple inherit would break UNDER's basic idea of
enabling hierarchy
trees that contain subtables under a single maximal
supertable.

I do not see how someone using the current under|inherits scheme
that only uses SQL99 syntax will get a system that does not act like
defined in SQL99 other than not complaining at "create table under"
time that the supertable is not top level. This alone is imho not enough to
validify two different approaches.

Andreas