Re: 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(at)gmx(dot)net 75262 Uppsala
http://yi.org/peter-e/ Sweden

Peter Eisentraut <peter_e(at)gmx(dot)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(at)gmx(dot)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(at)nimrod(dot)itg(dot)telstra(dot)com(dot)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(at)nimrod(dot)itg(dot)telstra(dot)com(dot)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(at)nimrod(dot)itg(dot)telstra(dot)com(dot)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(at)nimrod(dot)itg(dot)telstra(dot)com(dot)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.

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

On Fri, 19 May 2000, Chris Bitmead wrote:

> Tom Lane wrote:
> >
> > Chris Bitmead <chrisb(at)nimrod(dot)itg(dot)telstra(dot)com(dot)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(at)rice(dot)edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005

Chris Bitmead <chrisb(at)nimrod(dot)itg(dot)telstra(dot)com(dot)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(at)tm(dot)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(at)tm(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)bitmead(dot)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(at)tm(dot)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(at)bitmead(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

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

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(at)gmx(dot)net 75262 Uppsala
http://yi.org/peter-e/ Sweden

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(at)comptechnews(dot)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

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?

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-

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(at)comptechnews(dot)com

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

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(at)hub(dot)org secondary: scrappy(at){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(at)comptechnews(dot)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

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