Attached is a patch for the ONLY inheritance
functionality...

*) It includes a SET compatibility mode.
*) The overhead for non-inheritance has
been cut down to 30 microseconds (on a pc).
*) It needs an initdb.

Comments welcome.

-- 
Chris Bitmead
mailto:chris@bitmead.com
Sat, 05 Feb 2000 14:36:43 +1100
Chris <chris@bitmead.com>
Patch attached...
At 02:36 PM 2/5/00 +1100, Chris wrote:

>*) The overhead for non-inheritance has
>been cut down to 30 microseconds (on a pc).

What kind of PC?  I'm getting 4,000 microseconds doing
simple selects on a classic P200 (no L2 cache) through
AOLserver and Tcl scripts, which probably means more like
2,000 microseconds for PG alone.  But without knowing your
PC, I have no way to scale.  For instance, my P500e that
I just built gets between 3-6x performance over my P200.

What's an acceptable level for overhead?  I have no personal
desire to eat any overhead, in all honesty.  2000/30 < 1%
but without knowledge of the actual PC platform (which certainly
you must know vary widely in performance) I have no way to
scale.  If your PC platform is closer to my P500e than my
(classic) P200 (not pro, no L2 cache) then the overhead is
more like 2-3%.  That's measurable.

And if SQL92 compliance is the goal, why must ANY degradation
of performance be acceptable unless there are very, very strong
reasons to do so (reasons that impact the target audience).



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

Fri, 04 Feb 2000 20:38:23 -0800
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] Patch attached...
> >*) The overhead for non-inheritance has
> >been cut down to 30 microseconds (on a pc).

We actually have to see how you have implemented it.  I am not so
interested in timings as in your method.  It can be done fast, or it can
be done sloppy.   I will check the patch.

> And if SQL92 compliance is the goal, why must ANY degradation
> of performance be acceptable unless there are very, very strong
> reasons to do so (reasons that impact the target audience).

Agreed.

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

Sat, 5 Feb 2000 00:00:32 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Patch attached...
> 
> Attached is a patch for the ONLY inheritance
> functionality...
> 
> *) It includes a SET compatibility mode.
> *) The overhead for non-inheritance has
> been cut down to 30 microseconds (on a pc).
> *) It needs an initdb.
> 
> Comments welcome.
> 

One problem is that you use SearchSysCacheTupleCopy while
SearchSysCacheTuple is more appropriate.  You need Copy only when you
are going to be using the cache tuple for an extended period.  Looks
like the rest of that particular function is OK.

However, I have received an objection from someone on another issue
related to the patch.  There are no documentation changes for that
patch.  That includes the SET manual page, and any other place
Inheritance is mentioned.  You also need to update
include/catalog/catversion.h because initdb is required.  

I will wait for a new patch that has these changes, and any others
mentioned by people.  Thanks.

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

Sat, 5 Feb 2000 00:05:49 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Patch attached...
FYI the SearchSysCacheTupleCopy call that I was objecting to was in
function has_inheritors.  Looks like you clearly are on the right track
with the patch.  There is some tricky code in there, and it looks pretty
good.


> 
> Attached is a patch for the ONLY inheritance
> functionality...
> 
> *) It includes a SET compatibility mode.
> *) The overhead for non-inheritance has
> been cut down to 30 microseconds (on a pc).
> *) It needs an initdb.
> 
> Comments welcome.
> 

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

Sat, 5 Feb 2000 00:07:41 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Patch attached...
Don Baccus wrote:
> 
> At 02:36 PM 2/5/00 +1100, Chris wrote:
> 
> >*) The overhead for non-inheritance has
> >been cut down to 30 microseconds (on a pc).
> 
> What kind of PC? 

Cerelon 400, 64MB, IDE disk. 

-- 
Chris Bitmead
mailto:chris@bitmead.com

Sat, 05 Feb 2000 16:59:46 +1100
Chris <chris@bitmead.com>
Re: [HACKERS] Patch attached...
Thanks Bruce! That suggestion with SearchSysCacheTuple makes a big
difference! I am no longer able to measure ANY performance difference
between inherited and non-inherited while doing one million queries.

Attached is a patch that incorporates your suggestions.
Sat, 05 Feb 2000 19:32:56 +1100
Chris <chris@bitmead.com>
New Improved Patch
Chris wrote:
> 
> Don Baccus wrote:
> >
> > At 02:36 PM 2/5/00 +1100, Chris wrote:
> >
> > >*) The overhead for non-inheritance has
> > >been cut down to 30 microseconds (on a pc).
> >
> > What kind of PC?
> 
> Cerelon 400, 64MB, IDE disk.

Btw, how did you measure that 30us  overhead ?

Does it involve disk accesses or is it just in-memory code that 
speed-concious folks could move to assembly like current 
spinlocking code for some architectures?

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

Sat, 05 Feb 2000 13:47:50 +0200
Hannu Krosing <hannu@tm.ee>
Re: [HACKERS] Patch attached...
Hannu Krosing wrote:

> Btw, how did you measure that 30us  overhead ?

I measured it with the test program below. With the latest patch it is
no longer 30us, but as far as I can measure 0us.
 
> Does it involve disk accesses or is it just 
> in-memory code that
> speed-concious folks could move to assembly like current
> spinlocking code for some architectures?

For this patch it is an in-memory issue.

-- 
Chris Bitmead
mailto:chris@bitmead.com

#include <stdio.h>
#include <time.h>
#include "libpq-fe.h"

#define rep 1000000


main() {
int c;
PGconn *conn;
PGresult *res;
time_t t, t2;

conn = PQsetdb(NULL,NULL,NULL,NULL,"foo");
time(&t);
for (c = 0; c < rep; c++) {
        res = PQexec(conn, "select * from a*");
        PQclear(res);
}
time(&t2);
printf("inh %d\n", t2 - t);
time(&t);
for (c = 0; c < rep; c++) {
        res = PQexec(conn, "select * from only a");
        PQclear(res);
}
time(&t2);
printf("no inh %d\n", t2 - t);

PQfinish(conn);

}

Sat, 05 Feb 2000 22:55:12 +1100
Chris <chris@bitmead.com>
Re: [HACKERS] Patch attached...
Hi

I looked at the patch and for me the name of the variable 
to set to get the old behaviour (SET EXAMINE_SUBCLASS TO 'on';)
seems confusing.

At first I thought it was a typo to set it to 'ON' for old behaviour,
my internal logic would set it to 'OFF' to not select subclass by default.

I think something like DONT_SELECT_INHERITED or OLD_INHERITED_SELECT_SYNTAX
would be much clearer in meaning.

Actually the name is not very important, as most people won't use it anyway ;)

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

Sat, 05 Feb 2000 14:36:12 +0200
Hannu Krosing <hannu@tm.ee>
Re: [HACKERS] Patch attached...
Hannu Krosing wrote:
> 
> Hi
> 
> I looked at the patch and for me the name of the variable
> to set to get the old behaviour (SET EXAMINE_SUBCLASS TO 'on';)
> seems confusing.
> 
> At first I thought it was a typo to set it to 'ON' for old behaviour,
> my internal logic would set it to 'OFF' to not select subclass by default.

Umm, but that IS how it works...

$ psql
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit
pghack=# select * from a;
 aa  
-----
 aaa
 bbb
(2 rows)

pghack=# set examine_subclass to 'off';
SET VARIABLE
pghack=# select * from a;
 aa  
-----
 aaa
(1 row)

> I think something like DONT_SELECT_INHERITED or OLD_INHERITED_SELECT_SYNTAX
> would be much clearer in meaning.

I'm happy to hear alternative names, but I don't really want "SELECT" in
the name, because this might  apply to UPDATE eventually too.


> 
> Actually the name is not very important, as most people won't use it anyway ;)
> 
> ----------------
> Hannu

-- 
Chris Bitmead
mailto:chris@bitmead.com

Sun, 06 Feb 2000 00:12:53 +1100
Chris <chris@bitmead.com>
Re: [HACKERS] Patch attached...
> pghack=# set examine_subclass to 'off';
> > I think something like DONT_SELECT_INHERITED or 
> > OLD_INHERITED_SELECT_SYNTAX
> > would be much clearer in meaning.
> I'm happy to hear alternative names, but I don't really want "SELECT" in
> the name, because this might  apply to UPDATE eventually too.

Hmm. This uncovers our clunky SET syntax. imho this should be a clean
and natural option, not something with a bunch of underscores in the
keyword and a quoted string for the option.

But this is awfully close to beta to be even considering making this
change in default behavior and syntax for 7.0. We've got a lot of
other features to babysit through beta, and this one wasn't even on
the radar until a few days ago...

                   - Thomas

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

Sat, 05 Feb 2000 14:46:36 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] Patch attached...
Chris,
  This is to let you know that the core list has discussed this patch,
and we feel that it is not appropriate to apply it at this late stage
in the 7.0 development cycle.  There are several reasons for this:

* It appears that making such a definitional change is still
controversial.  (One thing that still needs to be looked at is whether
SQL 3 defines any comparable features, and if so whether we ought
to be following their syntax and behavior.)

* The implications of changing this behavior still need to be followed
through in the rest of the system.  For example, it doesn't make much
sense to me to change SELECT to have recursive behavior by default when
UPDATE and DELETE can't yet do it at all.  A user would naturally
expect "UPDATE table" to scan the same tuples that "SELECT FROM table"
does.

* It's awfully late in the 7.0 development cycle to be making such a
significant change.  We have only ten days left to scheduled beta,
which is not enough time to find and work out any unexpected problems
that may be lurking.

We encourage you to continue to work on this line of development,
but with an eye to merging your code into CVS early in the 7.1 cycle,
rather than trying to squeeze it into 7.0 at the last minute.

			regards, tom lane

Sat, 05 Feb 2000 12:10:35 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Status of inheritance-changing patch
Chris wrote:
> 
> Hannu Krosing wrote:
> >
> > Hi
> >
> > I looked at the patch and for me the name of the variable
> > to set to get the old behaviour (SET EXAMINE_SUBCLASS TO 'on';)
> > seems confusing.
> >
> > At first I thought it was a typo to set it to 'ON' for old behaviour,
> > my internal logic would set it to 'OFF' to not select subclass by default.
> 
> Umm, but that IS how it works...

I don't contest that ;)

> > I think something like DONT_SELECT_INHERITED or OLD_INHERITED_SELECT_SYNTAX
> > would be much clearer in meaning.
> 
> I'm happy to hear alternative names, but I don't really want "SELECT" in
> the name, because this might  apply to UPDATE eventually too.

Oops, I didnt think of that.

Of course it should actually apply to all four (SELECT, UPDATE, SELETE,
INSERT)
as wellas DDL statements (ALTER TABLE ADD/DROP xxx, CREATE
CONSTRAINT/INDEX/RULE/TRIGGER)

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

Sat, 05 Feb 2000 21:30:15 +0200
Hannu Krosing <hannu@tm.ee>
Re: [HACKERS] Patch attached...
Tom Lane wrote:
> 
> Chris,
>   This is to let you know that the core list has discussed this patch,
> and we feel that it is not appropriate to apply it at this late stage
> in the 7.0 development cycle.

Here you see Chris what happens when you try to force the default 
behaviour be the "wrong" way :-p

But seriously, we could still warn people about current (mis)use of 
inheritance and that it may be soon be changed/deprecated or "made 
compatible with Informix" whichever seems most PC.

>  There are several reasons for this:
> 
> * It appears that making such a definitional change is still
> controversial.  (One thing that still needs to be looked at is whether
> SQL 3 defines any comparable features, 

It does define "comparable" features, but moves away from out nice clean 
SQL92 worldview quite radically.

> and if so whether we ought
> to be following their syntax and behavior.)

I agree that some discussion about OQL vs. SQL3 would be in place.

> * The implications of changing this behavior still need to be followed
> through in the rest of the system.  For example, it doesn't make much
> sense to me to change SELECT to have recursive behavior by default when
> UPDATE and DELETE can't yet do it at all.  A user would naturally
> expect "UPDATE table" to scan the same tuples that "SELECT FROM table"
> does.

That's true. I would like to see INSERT,UPDATE,DELETE and SELECT be 
updated together.

Fixing ALTER TABLE behaviour is not so important as we are just getting 
most of it done for plain SQL92 by 7.0. 

> * It's awfully late in the 7.0 development cycle to be making such a
> significant change.  We have only ten days left to scheduled beta,
> which is not enough time to find and work out any unexpected problems
> that may be lurking.

Also - fixing object DB behaviours would give us reason to move to 8.x 
faster ;)

> We encourage you to continue to work on this line of development,
> but with an eye to merging your code into CVS early in the 7.1 cycle,
> rather than trying to squeeze it into 7.0 at the last minute.

But could we then disable the current half-hearted OO for the time being 
to avoid more compatibility problems from people who might err to use it.

If there is serious attempt to put the O back in ORDBMS we should not let 
compatibility with non-SQL postgres extensions to be a decisive fact.

But then again that kind of change is best done at a major number change.

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

Sat, 05 Feb 2000 21:55:05 +0200
Hannu Krosing <hannu@tm.ee>
Re: [HACKERS] Status of inheritance-changing patch
Tom Lane wrote:
>(One thing that still needs to be looked at is 
> whether SQL 3 defines any comparable features, and 
> if so whether we ought to be following their syntax 
> and behavior.)

I just downloaded the SQL3 document from dec. I can't seem to make head
or  tail of it. Can anybody understand what it's saying?

-- 
Chris Bitmead
mailto:chris@bitmead.com

Sun, 06 Feb 2000 11:21:52 +1100
Chris <chris@bitmead.com>
Re: Status of inheritance-changing patch
At 11:21 AM 2/6/00 +1100, Chris wrote:
>Tom Lane wrote:
>>(One thing that still needs to be looked at is 
>> whether SQL 3 defines any comparable features, and 
>> if so whether we ought to be following their syntax 
>> and behavior.)

>I just downloaded the SQL3 document from dec. I can't seem to make head
>or  tail of it. Can anybody understand what it's saying?

No ... a full summary of the private discussion earlier today between
Jan and I regarding referential integrity would indicate that NOBODY can
understand what it's saying!  Be glad it was in private, it was bad
enough that the two of us had to see each other so confused.

Date cheated, his co-author's a ringer who was part of the standards
committee and knows what they meant, rather than what they wrote :)

The appendix on SQL3 in Date's book talks very briefly about it.
There's a CREATE TABLE foo LIKE bar that causes foo to inherit
from bar.  He doesn't go into details, though.  Talks briefly about
sub and super tables and how the consequences aren't fully 
understood.  Then he punts.

It would still be a good place to start if you have it.




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

Sat, 05 Feb 2000 16:39:00 -0800
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] Re: Status of inheritance-changing patch
On Sun, Feb 06, 2000 at 11:21:52AM +1100, Chris wrote:
> Tom Lane wrote:
> >(One thing that still needs to be looked at is 
> > whether SQL 3 defines any comparable features, and 
> > if so whether we ought to be following their syntax 
> > and behavior.)
> 
> I just downloaded the SQL3 document from dec. I can't seem to make head
> or  tail of it. Can anybody understand what it's saying?

I can occasionall twist my brian in the specific way necessary to read
(and partially understand) standards. Got a URI for what you downloaded?

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

Sun, 6 Feb 2000 19:13:39 -0600
"Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu>
Re: [HACKERS] Re: Status of inheritance-changing patch