
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.

The patch is too big to email, so it's up for ftp here...

ftp://www.tech.com.au/pub/patch.only

-- 
Chris Bitmead
mailto:chris@bitmead.com

Sat, 05 Feb 2000 19:45:55 +1100
Chris <chris@bitmead.com>
New improved patch
> 
> 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.
> 
> The patch is too big to email, so it's up for ftp here...
> 
> ftp://www.tech.com.au/pub/patch.only
> 

I am getting a failure trying to retrieve this.  It says server not
found.  Also, are the documentation changes in there?


-- 
  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 04:16:10 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] New improved patch^N
Bruce Momjian wrote:

> I am getting a failure trying to retrieve this.  It 
> says server not found. 

That's strange. I'll email you the patch in a separate email.

> Also, are the documentation 
> changes in there?

Yes.

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

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

Sat, 05 Feb 2000 20:21:47 +1100
Chris <chris@bitmead.com>
Re: [HACKERS] New improved patch^N
> 
> 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.
> 
> The patch is too big to email, so it's up for ftp here...
> 
> ftp://www.tech.com.au/pub/patch.only
> 

Never mind.  Got it.  I am attaching it here for people to review.
Let's see what people say now.  I see documentation changes in there
too.  Great.

Gee, I didn't know catalog.sgml existed.  I wonder if it is up-to-date?
No, pg_database doesn't show "encoding".  Man, this is really old.  I
see pg_platter, which we have never had.  I deals with jukebox platter
inventory.  pg_class shows things like relpreserved, which deals with
time travel.  I suggest we remove this file and tell people to look in
include/catalog/*.h and use \dS.  Comments?

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

? pgsql/src/config.log
? pgsql/src/config.cache
? pgsql/src/config.status
? pgsql/src/GNUmakefile
? pgsql/src/Makefile.global
? pgsql/src/backend/fmgr.h
? pgsql/src/backend/parse.h
? pgsql/src/backend/postgres
? pgsql/src/backend/global1.bki.source
? pgsql/src/backend/local1_template1.bki.source
? pgsql/src/backend/global1.description
? pgsql/src/backend/local1_template1.description
? pgsql/src/backend/1
? pgsql/src/backend/catalog/genbki.sh
? pgsql/src/backend/catalog/global1.bki.source
? pgsql/src/backend/catalog/global1.description
? pgsql/src/backend/catalog/local1_template1.bki.source
? pgsql/src/backend/catalog/local1_template1.description
? pgsql/src/backend/port/Makefile
? pgsql/src/backend/utils/Gen_fmgrtab.sh
? pgsql/src/backend/utils/fmgr.h
? pgsql/src/backend/utils/fmgrtab.c
? pgsql/src/bin/initdb/initdb
? pgsql/src/bin/initlocation/initlocation
? pgsql/src/bin/ipcclean/ipcclean
? pgsql/src/bin/pg_ctl/pg_ctl
? pgsql/src/bin/pg_dump/Makefile
? pgsql/src/bin/pg_dump/pg_dump
? pgsql/src/bin/pg_id/pg_id
? pgsql/src/bin/pg_passwd/pg_passwd
? pgsql/src/bin/pg_version/Makefile
? pgsql/src/bin/pg_version/pg_version
? pgsql/src/bin/pgtclsh/mkMakefile.tcldefs.sh
? pgsql/src/bin/pgtclsh/mkMakefile.tkdefs.sh
? pgsql/src/bin/psql/Makefile
? pgsql/src/bin/psql/psql
? pgsql/src/bin/scripts/createlang
? pgsql/src/include/version.h
? pgsql/src/include/config.h
? pgsql/src/interfaces/ecpg/lib/Makefile
? pgsql/src/interfaces/ecpg/lib/libecpg.so.3.0.10
? pgsql/src/interfaces/ecpg/preproc/ecpg
? pgsql/src/interfaces/libpgeasy/Makefile
? pgsql/src/interfaces/libpgeasy/libpgeasy.so.2.1
? pgsql/src/interfaces/libpgtcl/Makefile
? pgsql/src/interfaces/libpq/Makefile
? pgsql/src/interfaces/libpq/libpq.so.2.1
? pgsql/src/interfaces/libpq++/Makefile
? pgsql/src/interfaces/libpq++/libpq++.so.3.1
? pgsql/src/interfaces/odbc/GNUmakefile
? pgsql/src/interfaces/odbc/Makefile.global
? pgsql/src/pl/plpgsql/src/Makefile
? pgsql/src/pl/plpgsql/src/mklang.sql
? pgsql/src/pl/plpgsql/src/libplpgsql.so.1.0
? pgsql/src/pl/tcl/mkMakefile.tcldefs.sh
? pgsql/src/test/regress/GNUmakefile
Index: pgsql/doc/src/sgml/advanced.sgml
===================================================================
RCS file: /usr/local/cvsroot/pgsql/doc/src/sgml/advanced.sgml,v
retrieving revision 1.7
diff -c -r1.7 advanced.sgml
*** pgsql/doc/src/sgml/advanced.sgml	1999/10/04 15:18:53	1.7
--- pgsql/doc/src/sgml/advanced.sgml	2000/02/05 08:24:35
***************
*** 56,93 ****
       </para>
      </note>
  
!     For example, the  following  query  finds
!     all  the cities that are situated at an attitude of 500ft or higher:
!      
!     <programlisting>
! SELECT name, altitude
!     FROM cities
!     WHERE altitude &gt; 500;
  
  +----------+----------+
  |name      | altitude |
  +----------+----------+
  |Las Vegas | 2174     |
  +----------+----------+
  |Mariposa  | 1953     |
  +----------+----------+
!     </programlisting>         
!    </para>
  
!    <para>
!     On the other hand, to find the  names  of  all  cities,
!     including  state capitals, that are located at an altitude 
!     over 500ft, the query is:
! 
!     <programlisting>
! SELECT c.name, c.altitude
!     FROM cities* c
!     WHERE c.altitude > 500;
!     </programlisting>
  
-     which returns:
-      
-     <programlisting>
  +----------+----------+
  |name      | altitude |
  +----------+----------+
--- 56,97 ----
       </para>
      </note>
  
!   <para>
!    For example, the  following  query finds the  names  of  all  cities,
!    including  state capitals, that are located at an altitude 
!    over 500ft, the query is:
! 
!    <programlisting>
!     SELECT c.name, c.altitude
!     FROM cities c
!     WHERE c.altitude > 500;
!    </programlisting>
  
+    which returns:
+ 
+    <programlisting>
  +----------+----------+
  |name      | altitude |
  +----------+----------+
  |Las Vegas | 2174     |
  +----------+----------+
  |Mariposa  | 1953     |
+ +----------+----------+
+ |Madison   | 845      |
  +----------+----------+
!    </programlisting>
!   </para>
  
!   <para>
!    On the other hand, the  following  query  finds
!    all  the cities, but not capital cities 
!    that are situated at an attitude of 500ft or higher:
! 
!    <programlisting>
!     SELECT name, altitude
!     FROM ONLY cities
!     WHERE altitude &gt; 500;
  
  +----------+----------+
  |name      | altitude |
  +----------+----------+
***************
*** 95,112 ****
  +----------+----------+
  |Mariposa  | 1953     |
  +----------+----------+
! |Madison   | 845      |
! +----------+----------+
!     </programlisting>
  
!     Here the <quote>*</quote> after cities indicates that the query should
!     be  run over cities and all classes below cities in the
!     inheritance hierarchy.  Many of the  commands  that  we
!     have  already discussed (<command>select</command>,
!     <command>and>up</command>and> and <command>delete</command>)
!     support this <quote>*</quote> notation, as do others, like
!     <command>alter</command>.
!    </para>
    </sect1>
  
    <sect1>
--- 99,129 ----
  +----------+----------+
  |Mariposa  | 1953     |
  +----------+----------+
!    </programlisting>         
!   </para>
! 
  
!    Here the <quote>ONLY</quote> before cities indicates that the query should
!    be  run over only cities and not classes below cities in the
!    inheritance hierarchy.  Many of the  commands  that  we
!    have  already discussed -- <command>SELECT</command>,
!    <command>UPDATE</command> and <command>DELETE</command> --
!    support this <quote>ONLY</quote> notation, as do others, like
!    <command>ALTER TABLE</command>.
!   </para>
!   <para>
!   Deprecated: In previous versions of postgres, the default was not to
!   get access to child classes. By experience this was found to be error
!   prone. Under the old syntax, to get the sub-classes you append "*"
!   to the table name. For example
!    <programlisting>
!    SELECT * from cities*;
!    </programlisting>     
!    This old behaviour is still available by using a SET command...    
!    <programlisting>
!    SET EXAMINE_SUBCLASS TO 'on';
!    </programlisting>     
!   </para>
    </sect1>
  
    <sect1>
Index: pgsql/doc/src/sgml/catalogs.sgml
===================================================================
RCS file: /usr/local/cvsroot/pgsql/doc/src/sgml/catalogs.sgml,v
retrieving revision 2.3
diff -c -r2.3 catalogs.sgml
*** pgsql/doc/src/sgml/catalogs.sgml	2000/01/22 23:50:08	2.3
--- pgsql/doc/src/sgml/catalogs.sgml	2000/02/05 08:24:37
***************
*** 192,197 ****
--- 192,199 ----
  			   2=main memory */
       int2vector relkey		/* - unused */
       oidvector  relkeyop	/* - unused */
+      bool       relhassubclass	/* does the class have a subclass?
+ 				    */
       aclitem    relacl[1]	/* access control lists */
  .fi
  .nf M
Index: pgsql/doc/src/sgml/inherit.sgml
===================================================================
RCS file: /usr/local/cvsroot/pgsql/doc/src/sgml/inherit.sgml,v
retrieving revision 1.4
diff -c -r1.4 inherit.sgml
*** pgsql/doc/src/sgml/inherit.sgml	1999/08/08 04:21:33	1.4
--- pgsql/doc/src/sgml/inherit.sgml	2000/02/05 08:24:37
***************
*** 37,50 ****
      </para>
     </note>
  
!    For example, the  following  query  finds
!    all  the cities that are situated at an attitude of 500ft or higher:
  
     <programlisting>
! SELECT name, altitude
!     FROM cities
!     WHERE altitude &gt; 500;
  
  +----------+----------+
  |name      | altitude |
  +----------+----------+
--- 37,56 ----
      </para>
     </note>
  
!   <para>
!    For example, the  following  query finds the  names  of  all  cities,
!    including  state capitals, that are located at an altitude 
!    over 500ft, the query is:
  
     <programlisting>
!     SELECT c.name, c.altitude
!     FROM cities c
!     WHERE c.altitude > 500;
!    </programlisting>
! 
!    which returns:
  
+    <programlisting>
  +----------+----------+
  |name      | altitude |
  +----------+----------+
***************
*** 52,92 ****
  +----------+----------+
  |Mariposa  | 1953     |
  +----------+----------+
!    </programlisting>         
    </para>
  
    <para>
!    On the other hand, to find the  names  of  all  cities,
!    including  state capitals, that are located at an altitude 
!    over 500ft, the query is:
  
     <programlisting>
! SELECT c.name, c.altitude
!     FROM cities* c
!     WHERE c.altitude > 500;
!    </programlisting>
! 
!    which returns:
  
-    <programlisting>
  +----------+----------+
  |name      | altitude |
  +----------+----------+
  |Las Vegas | 2174     |
  +----------+----------+
  |Mariposa  | 1953     |
- +----------+----------+
- |Madison   | 845      |
  +----------+----------+
!    </programlisting>
  
!    Here the <quote>*</quote> after cities indicates that the query should
!    be  run over cities and all classes below cities in the
     inheritance hierarchy.  Many of the  commands  that  we
     have  already discussed -- <command>SELECT</command>,
     <command>UPDATE</command> and <command>DELETE</command> --
!    support this <quote>*</quote> notation, as do others, like
     <command>ALTER TABLE</command>.
    </para>
   </chapter>
  
--- 58,109 ----
  +----------+----------+
  |Mariposa  | 1953     |
  +----------+----------+
! |Madison   | 845      |
! +----------+----------+
!    </programlisting>
    </para>
  
    <para>
!    On the other hand, the  following  query  finds
!    all  the cities, but not capital cities 
!    that are situated at an attitude of 500ft or higher:
  
     <programlisting>
!     SELECT name, altitude
!     FROM ONLY cities
!     WHERE altitude &gt; 500;
  
  +----------+----------+
  |name      | altitude |
  +----------+----------+
  |Las Vegas | 2174     |
  +----------+----------+
  |Mariposa  | 1953     |
  +----------+----------+
!    </programlisting>         
!   </para>
  
! 
!    Here the <quote>ONLY</quote> before cities indicates that the query should
!    be  run over only cities and not classes below cities in the
     inheritance hierarchy.  Many of the  commands  that  we
     have  already discussed -- <command>SELECT</command>,
     <command>UPDATE</command> and <command>DELETE</command> --
!    support this <quote>ONLY</quote> notation, as do others, like
     <command>ALTER TABLE</command>.
+   </para>
+   <para>
+   Deprecated: In previous versions of postgres, the default was not to
+   get access to child classes. By experience this was found to be error
+   prone. Under the old syntax, to get the sub-classes you append "*"
+   to the table name. For example
+    <programlisting>
+    SELECT * from cities*;
+    </programlisting>     
+    This old behaviour is still available by using a SET command...    
+    <programlisting>
+    SET EXAMINE_SUBCLASS TO 'on';
+    </programlisting>     
    </para>
   </chapter>
  
Index: pgsql/doc/src/sgml/ref/alter_table.sgml
===================================================================
RCS file: /usr/local/cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v
retrieving revision 1.10
diff -c -r1.10 alter_table.sgml
*** pgsql/doc/src/sgml/ref/alter_table.sgml	2000/01/29 16:58:27	1.10
--- pgsql/doc/src/sgml/ref/alter_table.sgml	2000/02/05 08:24:38
***************
*** 23,35 ****
     <date>1999-07-20</date>
    </refsynopsisdivinfo>
    <synopsis>
! ALTER TABLE <replaceable class="PARAMETER">table</replaceable> [ * ]
      ADD [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> <replaceable
      class="PARAMETER">type</replaceable>
! ALTER TABLE <replaceable class="PARAMETER">table</replaceable> [ * ]
      ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { SET DEFAULT <replaceable
      class="PARAMETER">value</replaceable> | DROP DEFAULT }
! ALTER TABLE <replaceable class="PARAMETER">table</replaceable> [ * ]
      RENAME [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> TO <replaceable
      class="PARAMETER">newcolumn</replaceable>
  ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
--- 23,35 ----
     <date>1999-07-20</date>
    </refsynopsisdivinfo>
    <synopsis>
! ALTER TABLE [ ONLY ]<replaceable class="PARAMETER">table</replaceable> [ * ]
      ADD [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> <replaceable
      class="PARAMETER">type</replaceable>
! ALTER TABLE [ ONLY ]<replaceable class="PARAMETER">table</replaceable> [ * ]
      ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { SET DEFAULT <replaceable
      class="PARAMETER">value</replaceable> | DROP DEFAULT }
! ALTER TABLE [ ONLY ]<replaceable class="PARAMETER">table</replaceable> [ * ]
      RENAME [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> TO <replaceable
      class="PARAMETER">newcolumn</replaceable>
  ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
***************
*** 162,178 ****
     </para>
  
     <para>
!     <quote>*</quote> following a name of a table indicates that the statement
!     should be run over that table and all tables below it in the
      inheritance hierarchy;
!     by default, the attribute will not be added to or renamed in any of the subclasses.
  
!     This should always be done when adding or modifying an attribute in a
!     superclass. If it is not, queries on  the  inheritance  hierarchy
      such as
  
      <programlisting>
! SELECT <replaceable>NewColumn</replaceable> FROM <replaceable>SuperClass</replaceable>*
      </programlisting>
  
      will not work because the subclasses will be missing an attribute
--- 162,178 ----
     </para>
  
     <para>
!     <quote>ONLY</quote> preceeding the name of a table indicates that the statement
!     should be run over only that table and not tables below it in the
      inheritance hierarchy;
!     by default, the attribute will be added to or renamed in any of the subclasses.
  
!     It is recommended to never use the ONLY feature however.
!     If it is, queries on  the  inheritance  hierarchy
      such as
  
      <programlisting>
! SELECT <replaceable>NewColumn</replaceable> FROM <replaceable>SuperClass</replaceable>
      </programlisting>
  
      will not work because the subclasses will be missing an attribute
Index: pgsql/doc/src/sgml/ref/select.sgml
===================================================================
RCS file: /usr/local/cvsroot/pgsql/doc/src/sgml/ref/select.sgml,v
retrieving revision 1.24
diff -c -r1.24 select.sgml
*** pgsql/doc/src/sgml/ref/select.sgml	2000/01/27 18:11:25	1.24
--- pgsql/doc/src/sgml/ref/select.sgml	2000/02/05 08:24:40
***************
*** 25,31 ****
  SELECT [ ALL | DISTINCT [ ON ( <replaceable class="PARAMETER">expression</replaceable> [, ...] ) ] ]
      <replaceable class="PARAMETER">expression</replaceable> [ AS <replaceable class="PARAMETER">name</replaceable> ] [, ...]
      [ INTO [ TEMPORARY | TEMP ] [ TABLE ] <replaceable class="PARAMETER">new_table</replaceable> ]
!     [ FROM <replaceable class="PARAMETER">table</replaceable> [ <replaceable class="PARAMETER">alias</replaceable> ] [, ...] ]
      [ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
      [ GROUP BY <replaceable class="PARAMETER">column</replaceable> [, ...] ]
      [ HAVING <replaceable class="PARAMETER">condition</replaceable> [, ...] ]
--- 25,31 ----
  SELECT [ ALL | DISTINCT [ ON ( <replaceable class="PARAMETER">expression</replaceable> [, ...] ) ] ]
      <replaceable class="PARAMETER">expression</replaceable> [ AS <replaceable class="PARAMETER">name</replaceable> ] [, ...]
      [ INTO [ TEMPORARY | TEMP ] [ TABLE ] <replaceable class="PARAMETER">new_table</replaceable> ]
!     [ FROM [ ONLY ]<replaceable class="PARAMETER">table</replaceable> [ <replaceable class="PARAMETER">alias</replaceable> ] [, ...] ]
      [ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
      [ GROUP BY <replaceable class="PARAMETER">column</replaceable> [, ...] ]
      [ HAVING <replaceable class="PARAMETER">condition</replaceable> [, ...] ]
***************
*** 198,203 ****
--- 198,210 ----
     Candidates for selection are rows which satisfy the WHERE condition;
     if WHERE is omitted, all rows are candidates.
     (See <xref linkend="sql-where" endterm="sql-where-title">.)
+   </para>
+   <para>
+   <command>ONLY</command> will eliminate rows from subclasses of the table.
+   This was previously the default result, and getting subclasses was
+   obtained by appending <command>*</command> to the table name.
+   The old behaviour is available via the command 
+   <command>SET EXAMINE_SUBCLASS TO 'on';</command>
    </para>
  
    <para>
Index: pgsql/doc/src/sgml/ref/set.sgml
===================================================================
RCS file: /usr/local/cvsroot/pgsql/doc/src/sgml/ref/set.sgml,v
retrieving revision 1.28
diff -c -r1.28 set.sgml
*** pgsql/doc/src/sgml/ref/set.sgml	1999/07/22 15:09:15	1.28
--- pgsql/doc/src/sgml/ref/set.sgml	2000/02/05 08:24:41
***************
*** 443,448 ****
--- 443,482 ----
  	  </listitem>
  	 </varlistentry>
  
+      <varlistentry>
+       <term>EXAMINE_SUBCLASS</term>
+       <listitem>
+        <para>
+ 	Sets the inheritance query syntax to the traditional postgres style.
+ 	
+ 	<variablelist>
+ 	 <varlistentry>
+ 	  <term><replaceable class="parameter">OFF</replaceable></term>
+ 	  <listitem>
+ 	   <para>
+        Changes the behaviour of SELECT so that it no longer automatically
+        examines sub-classes. (See SELECT). By default a SELECT on a table
+        will also return subclass tuples unless specifying ONLY tablename.
+        Setting this returns postgres to the traditional behaviour of
+        only returning subclasses when appending "*" to the tablename.
+ 	   </para>
+ 	  </listitem>
+ 	 </varlistentry>
+ 	 
+ 	 <varlistentry>
+ 	  <term>ON</term>
+ 	  <listitem>
+ 	   <para>
+        Returns SELECT to the behaviour of automatically returning
+        results from sub-classes.
+ 	   </para>
+ 	  </listitem>
+ 	 </varlistentry>
+ 	</variablelist>
+        </para>
+       </listitem>
+      </varlistentry>
+ 
  	 <varlistentry>
  	  <term>OFF</term>
  	  <listitem>
Index: pgsql/src/backend/commands/creatinh.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/commands/creatinh.c,v
retrieving revision 1.56
diff -c -r1.56 creatinh.c
*** pgsql/src/backend/commands/creatinh.c	2000/01/29 16:58:34	1.56
--- pgsql/src/backend/commands/creatinh.c	2000/02/05 08:24:44
***************
*** 35,40 ****
--- 35,43 ----
                              const char *attributeType, List *schema);
  static List *MergeAttributes(List *schema, List *supers, List **supconstr);
  static void StoreCatalogInheritance(Oid relationId, List *supers);
+ static void
+ setRelhassubclassInRelation(Oid relationId, bool relhassubclass);
+ 
  
  /* ----------------------------------------------------------------
   *		DefineRelation
***************
*** 323,328 ****
--- 326,332 ----
  		TupleConstr *constr;
  
  		relation = heap_openr(name, AccessShareLock);
+ 		setRelhassubclassInRelation(relation->rd_id, true);
  		tupleDesc = RelationGetDescr(relation);
  		constr = tupleDesc->constr;
  
***************
*** 655,657 ****
--- 659,698 ----
  	}
  	return false;
  }
+ 
+ 
+ static void
+ setRelhassubclassInRelation(Oid relationId, bool relhassubclass)
+ {
+         Relation        relationRelation;
+         HeapTuple       tuple;
+         Relation        idescs[Num_pg_class_indices];
+ 
+         /*
+          * Lock a relation given its Oid. Go to the RelationRelation (i.e.
+          * pg_relation), find the appropriate tuple, and add the specified
+          * lock to it.
+          */
+         relationRelation = heap_openr(RelationRelationName, RowExclusiveLock);
+         tuple = SearchSysCacheTuple(RELOID,
+                                     ObjectIdGetDatum(relationId),
+                                     0, 0, 0)
+ ;
+         Assert(HeapTupleIsValid(tuple));
+ 
+         ((Form_pg_class) GETSTRUCT(tuple))->relhassubclass = relhassubclass;
+         heap_update(relationRelation, &tuple->t_self, tuple, NULL);
+ 
+         /* keep the catalog indices up to date */
+         CatalogOpenIndices(Num_pg_class_indices, Name_pg_class_indices, idescs);
+         CatalogIndexInsert(idescs, Num_pg_class_indices, relationRelation, tuple
+ );
+         CatalogCloseIndices(Num_pg_class_indices, idescs);
+ 
+         /*        heap_freetuple(tuple); */
+         heap_close(relationRelation, RowExclusiveLock);
+ }
+ 
+ 
+ 
+ 
Index: pgsql/src/backend/commands/variable.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/commands/variable.c,v
retrieving revision 1.28
diff -c -r1.28 variable.c
*** pgsql/src/backend/commands/variable.c	2000/01/22 23:50:10	1.28
--- pgsql/src/backend/commands/variable.c	2000/02/05 08:24:45
***************
*** 48,53 ****
--- 48,56 ----
  
  extern bool _use_keyset_query_optimizer;
  
+ #define examine_subclass_default true
+ bool examine_subclass = examine_subclass_default;
+ 
  /*
   *
   * Get_Token
***************
*** 228,233 ****
--- 231,274 ----
  	geqo_rels = GEQO_RELS;
  	return TRUE;
  }
+ /*
+  *
+  * EXAMINE_SUBCLASS
+  *
+  */
+ #define EXAMINE_SUBCLASS "EXAMINE_SUBCLASS"
+ 
+ static bool
+ parse_examine_subclass(const char *value)
+ {
+     if (strcasecmp(value, "on") == 0)
+         examine_subclass = true;
+     else if (strcasecmp(value, "off") == 0)
+         examine_subclass = false;
+     else if (strcasecmp(value, "default") == 0) 
+         examine_subclass = examine_subclass_default;
+ 	else
+ 		elog(ERROR, "Bad value for %s (%s)", EXAMINE_SUBCLASS, value);
+ 	return TRUE;
+ }
+ 
+ static bool
+ show_examine_subclass()
+ {
+     
+ 	if (examine_subclass)
+ 		elog(NOTICE, "%s is ON", EXAMINE_SUBCLASS);
+ 	else
+ 		elog(NOTICE, "%s is OFF", EXAMINE_SUBCLASS);
+ 	return TRUE;
+ }
+ 
+ static bool
+ reset_examine_subclass(void)
+ {
+     examine_subclass = examine_subclass_default;
+ 	return TRUE;
+ }
  
  /*
   *
***************
*** 600,605 ****
--- 641,649 ----
  	{
  		"pg_options", parse_pg_options, show_pg_options, reset_pg_options
  	},
+     {
+ 		EXAMINE_SUBCLASS, parse_examine_subclass, show_examine_subclass, reset_examine_subclass
+     },
  	{
  		NULL, NULL, NULL, NULL
  	}
Index: pgsql/src/backend/optimizer/plan/planner.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/optimizer/plan/planner.c,v
retrieving revision 1.74
diff -c -r1.74 planner.c
*** pgsql/src/backend/optimizer/plan/planner.c	2000/01/27 18:11:31	1.74
--- pgsql/src/backend/optimizer/plan/planner.c	2000/02/05 08:24:48
***************
*** 35,40 ****
--- 35,41 ----
  #include "utils/builtins.h"
  #include "utils/lsyscache.h"
  #include "utils/syscache.h"
+ #include "parser/parsetree.h"
  
  static List *make_subplanTargetList(Query *parse, List *tlist,
  									AttrNumber **groupColIdx);
***************
*** 140,146 ****
  		 * to change interface to plan_union_queries to pass that info back!
  		 */
  	}
! 	else if ((rt_index = first_inherit_rt_entry(rangetable)) != -1)
  	{
  		List	   *sub_tlist;
  
--- 141,147 ----
  		 * to change interface to plan_union_queries to pass that info back!
  		 */
  	}
! 	else if ((rt_index = first_inherit_rt_entry(rangetable)) != -1 && has_inheritors(rt_fetch(rt_index, parse->rtable)->relid))
  	{
  		List	   *sub_tlist;
  
Index: pgsql/src/backend/optimizer/prep/prepunion.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/optimizer/prep/prepunion.c,v
retrieving revision 1.43
diff -c -r1.43 prepunion.c
*** pgsql/src/backend/optimizer/prep/prepunion.c	2000/02/03 06:12:19	1.43
--- pgsql/src/backend/optimizer/prep/prepunion.c	2000/02/05 08:24:49
***************
*** 25,30 ****
--- 25,33 ----
  #include "parser/parse_clause.h"
  #include "parser/parsetree.h"
  #include "utils/lsyscache.h"
+ #include "access/heapam.h"
+ #include "catalog/catname.h"
+ #include "utils/syscache.h"
  
  typedef struct {
  	Index		rt_index;
***************
*** 45,50 ****
--- 48,54 ----
  static Append *make_append(List *appendplans, List *unionrtables,
  						   Index rt_index,
  						   List *inheritrtable, List *tlist);
+ bool has_inheritors(Oid relationId);
  
  
  /*
***************
*** 352,357 ****
--- 356,386 ----
  
  	*union_rtentriesPtr = union_rtentries;
  	return union_plans;
+ }
+ 
+ bool has_inheritors(Oid relationId)
+ {
+         bool rtn;
+         Relation        relationRelation;
+         HeapTuple       tuple;
+ 
+         /*
+          * Lock a relation given its Oid. Go to the RelationRelation (i.e.
+          * pg_relation), find the appropriate tuple, and add the specified
+          * lock to it.
+          */
+         relationRelation = heap_openr(RelationRelationName, NoLock);
+         tuple = SearchSysCacheTuple(RELOID,
+                                  ObjectIdGetDatum(relationId),
+                                  0, 0, 0)
+ ;
+ /*        Assert(HeapTupleIsValid(tuple)); */
+ 
+         rtn = ((Form_pg_class) GETSTRUCT(tuple))->relhassubclass;
+ 
+         /*         heap_freetuple(tuple); */
+         heap_close(relationRelation, NoLock);
+         return rtn;
  }
  
  /*
Index: pgsql/src/backend/parser/gram.y
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.139
diff -c -r2.139 gram.y
*** pgsql/src/backend/parser/gram.y	2000/02/04 18:49:33	2.139
--- pgsql/src/backend/parser/gram.y	2000/02/05 08:25:00
***************
*** 811,868 ****
  
  AlterTableStmt:
  /* ALTER TABLE <name> ADD [COLUMN] <coldef> */
          ALTER TABLE relation_name opt_inh_star ADD opt_column columnDef
  	{
  		AlterTableStmt *n = makeNode(AlterTableStmt);
                  n->subtype = 'A';
  		n->relname = $3;
! 		n->inh = $4;
  		n->def = $7;
  		$$ = (Node *)n;
  	}
  /* ALTER TABLE <name> ALTER [COLUMN] <colname> {SET DEFAULT <expr>|DROP DEFAULT} */
        | ALTER TABLE relation_name opt_inh_star ALTER opt_column ColId alter_column_action
          {
                  AlterTableStmt *n = makeNode(AlterTableStmt);
                  n->subtype = 'T';
                  n->relname = $3;
!                 n->inh = $4;
                  n->name = $7;
                  n->def = $8;
                  $$ = (Node *)n;
          }
  /* ALTER TABLE <name> DROP [COLUMN] <name> {RESTRICT|CASCADE} */
        | ALTER TABLE relation_name opt_inh_star DROP opt_column ColId drop_behavior
          {
                  AlterTableStmt *n = makeNode(AlterTableStmt);
                  n->subtype = 'D';
                  n->relname = $3;
!                 n->inh = $4;
                  n->name = $7;
                  n->behavior = $8;
                  $$ = (Node *)n;
          }
  /* ALTER TABLE <name> ADD CONSTRAINT ... */
        | ALTER TABLE relation_name opt_inh_star ADD TableConstraint
          {
                  AlterTableStmt *n = makeNode(AlterTableStmt);
                  n->subtype = 'C';
                  n->relname = $3;
!                 n->inh = $4;
                  n->def = $6;
                  $$ = (Node *)n;
          }
  /* ALTER TABLE <name> DROP CONSTRAINT <name> {RESTRICT|CASCADE} */
        | ALTER TABLE relation_name opt_inh_star DROP CONSTRAINT name drop_behavior
          {
                  AlterTableStmt *n = makeNode(AlterTableStmt);
                  n->subtype = 'X';
                  n->relname = $3;
!                 n->inh = $4;
                  n->name = $7;
                  n->behavior = $8;
                  $$ = (Node *)n;
          }
          ;
  
  alter_column_action:
--- 811,926 ----
  
  AlterTableStmt:
  /* ALTER TABLE <name> ADD [COLUMN] <coldef> */
+         /* "*" deprecated */
          ALTER TABLE relation_name opt_inh_star ADD opt_column columnDef
  	{
+         extern bool examine_subclass;
  		AlterTableStmt *n = makeNode(AlterTableStmt);
                  n->subtype = 'A';
  		n->relname = $3;
! 		n->inh = $4 || examine_subclass;
  		n->def = $7;
  		$$ = (Node *)n;
  	}
+     |    ALTER TABLE ONLY relation_name ADD opt_column columnDef
+ 	{
+ 		AlterTableStmt *n = makeNode(AlterTableStmt);
+                 n->subtype = 'A';
+ 		n->relname = $4;
+ 		n->inh = FALSE;
+ 		n->def = $7;
+ 		$$ = (Node *)n;
+ 	}
  /* ALTER TABLE <name> ALTER [COLUMN] <colname> {SET DEFAULT <expr>|DROP DEFAULT} */
+         /* "*" deprecated */
        | ALTER TABLE relation_name opt_inh_star ALTER opt_column ColId alter_column_action
          {
+         extern bool examine_subclass;
                  AlterTableStmt *n = makeNode(AlterTableStmt);
                  n->subtype = 'T';
                  n->relname = $3;
!                 n->inh = $4 || examine_subclass;
                  n->name = $7;
                  n->def = $8;
                  $$ = (Node *)n;
          }
+       | ALTER TABLE ONLY relation_name ALTER opt_column ColId alter_column_action
+         {
+                 AlterTableStmt *n = makeNode(AlterTableStmt);
+                 n->subtype = 'T';
+                 n->relname = $4;
+                 n->inh = FALSE;
+                 n->name = $7;
+                 n->def = $8;
+                 $$ = (Node *)n;
+         }
  /* ALTER TABLE <name> DROP [COLUMN] <name> {RESTRICT|CASCADE} */
+         /* "*" deprecated */
        | ALTER TABLE relation_name opt_inh_star DROP opt_column ColId drop_behavior
          {
+         extern bool examine_subclass;
                  AlterTableStmt *n = makeNode(AlterTableStmt);
                  n->subtype = 'D';
                  n->relname = $3;
!                 n->inh = $4 || examine_subclass;
                  n->name = $7;
                  n->behavior = $8;
                  $$ = (Node *)n;
          }
+       | ALTER TABLE ONLY relation_name DROP opt_column ColId drop_behavior
+         {
+                 AlterTableStmt *n = makeNode(AlterTableStmt);
+                 n->subtype = 'D';
+                 n->relname = $4;
+                 n->inh = FALSE;
+                 n->name = $7;
+                 n->behavior = $8;
+                 $$ = (Node *)n;
+         }
  /* ALTER TABLE <name> ADD CONSTRAINT ... */
+         /* "*" deprecated */
        | ALTER TABLE relation_name opt_inh_star ADD TableConstraint
          {
+         extern bool examine_subclass;
                  AlterTableStmt *n = makeNode(AlterTableStmt);
                  n->subtype = 'C';
                  n->relname = $3;
!                 n->inh = $4 || examine_subclass;
                  n->def = $6;
                  $$ = (Node *)n;
          }
+       | ALTER TABLE ONLY relation_name ADD TableConstraint
+         {
+                 AlterTableStmt *n = makeNode(AlterTableStmt);
+                 n->subtype = 'C';
+                 n->relname = $4;
+                 n->inh = FALSE;
+                 n->def = $6;
+                 $$ = (Node *)n;
+         }
  /* ALTER TABLE <name> DROP CONSTRAINT <name> {RESTRICT|CASCADE} */
+         /* "*" deprecated */
        | ALTER TABLE relation_name opt_inh_star DROP CONSTRAINT name drop_behavior
          {
+         extern bool examine_subclass;
                  AlterTableStmt *n = makeNode(AlterTableStmt);
                  n->subtype = 'X';
                  n->relname = $3;
!                 n->inh = $4 || examine_subclass;
                  n->name = $7;
                  n->behavior = $8;
                  $$ = (Node *)n;
          }
+       | ALTER TABLE ONLY relation_name DROP CONSTRAINT name drop_behavior
+         {
+                 AlterTableStmt *n = makeNode(AlterTableStmt);
+                 n->subtype = 'X';
+                 n->relname = $4;
+                 n->inh = FALSE;
+                 n->name = $7;
+                 n->behavior = $8;
+                 $$ = (Node *)n;
+         }
          ;
  
  alter_column_action:
***************
*** 2380,2390 ****
   *****************************************************************************/
  
  RenameStmt:  ALTER TABLE relation_name opt_inh_star
  				  RENAME opt_column opt_name TO name
  				{
  					RenameStmt *n = makeNode(RenameStmt);
  					n->relname = $3;
! 					n->inh = $4;
  					n->column = $7;
  					n->newname = $9;
  					$$ = (Node *)n;
--- 2438,2460 ----
   *****************************************************************************/
  
  RenameStmt:  ALTER TABLE relation_name opt_inh_star
+         /* "*" deprecated */
  				  RENAME opt_column opt_name TO name
  				{
+                     extern bool examine_subclass;
  					RenameStmt *n = makeNode(RenameStmt);
  					n->relname = $3;
! 					n->inh = $4 || examine_subclass;
! 					n->column = $7;
! 					n->newname = $9;
! 					$$ = (Node *)n;
! 				}
!       | ALTER TABLE ONLY relation_name
! 				  RENAME opt_column opt_name TO name
! 				{
! 					RenameStmt *n = makeNode(RenameStmt);
! 					n->relname = $4;
! 					n->inh = FALSE;
  					n->column = $7;
  					n->newname = $9;
  					$$ = (Node *)n;
***************
*** 3553,3562 ****
  
  relation_expr:	relation_name
  				{
! 					/* normal relations */
  					$$ = makeNode(RelExpr);
  					$$->relname = $1;
! 					$$->inh = FALSE;
  				}
  		| relation_name '*'				  %prec '='
  				{
--- 3623,3633 ----
  
  relation_expr:	relation_name
  				{
!     				/* default inheritance */
!                     extern bool examine_subclass;
  					$$ = makeNode(RelExpr);
  					$$->relname = $1;
! 					$$->inh = examine_subclass;
  				}
  		| relation_name '*'				  %prec '='
  				{
***************
*** 3565,3570 ****
--- 3636,3648 ----
  					$$->relname = $1;
  					$$->inh = TRUE;
  				}
+         | ONLY relation_name
+                 {
+ 					/* no inheritance */
+ 					$$ = makeNode(RelExpr);
+ 					$$->relname = $2;
+ 					$$->inh = FALSE;
+                 }
  
  opt_array_bounds:	'[' ']' opt_array_bounds
  				{  $$ = lcons(makeInteger(-1), $3); }
Index: pgsql/src/include/catalog/catversion.h
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/include/catalog/catversion.h,v
retrieving revision 1.13
diff -c -r1.13 catversion.h
*** pgsql/src/include/catalog/catversion.h	2000/01/27 18:11:40	1.13
--- pgsql/src/include/catalog/catversion.h	2000/02/05 08:25:05
***************
*** 53,58 ****
   */
  
  /*                          yyyymmddN */
! #define CATALOG_VERSION_NO  200001271
  
  #endif
--- 53,58 ----
   */
  
  /*                          yyyymmddN */
! #define CATALOG_VERSION_NO  200002050
  
  #endif
Index: pgsql/src/include/catalog/pg_attribute.h
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/include/catalog/pg_attribute.h,v
retrieving revision 1.53
diff -c -r1.53 pg_attribute.h
*** pgsql/src/include/catalog/pg_attribute.h	2000/01/26 05:57:57	1.53
--- pgsql/src/include/catalog/pg_attribute.h	2000/02/05 08:25:09
***************
*** 402,408 ****
  { 1259, {"relrefs"},	   21, 0,	2, 16, 0, -1, -1, '\001', 'p', '\0', 's', '\0', '\0' }, \
  { 1259, {"relhaspkey"},    16, 0,	1, 17, 0, -1, -1, '\001', 'p', '\0', 'c', '\0', '\0' }, \
  { 1259, {"relhasrules"},   16, 0,	1, 18, 0, -1, -1, '\001', 'p', '\0', 'c', '\0', '\0' }, \
! { 1259, {"relacl"},		 1034, 0,  -1, 19, 0, -1, -1,	'\0', 'p', '\0', 'i', '\0', '\0' }
  
  DATA(insert OID = 0 ( 1259 relname			19 0 NAMEDATALEN   1 0 -1 -1 f p f i f f));
  DATA(insert OID = 0 ( 1259 reltype			26 0  4   2 0 -1 -1 t p f i f f));
--- 402,409 ----
  { 1259, {"relrefs"},	   21, 0,	2, 16, 0, -1, -1, '\001', 'p', '\0', 's', '\0', '\0' }, \
  { 1259, {"relhaspkey"},    16, 0,	1, 17, 0, -1, -1, '\001', 'p', '\0', 'c', '\0', '\0' }, \
  { 1259, {"relhasrules"},   16, 0,	1, 18, 0, -1, -1, '\001', 'p', '\0', 'c', '\0', '\0' }, \
! { 1259, {"relhassubclass"},16, 0,	1, 19, 0, -1, -1, '\001', 'p', '\0', 'c', '\0', '\0' }, \
! { 1259, {"relacl"},		 1034, 0,  -1, 20, 0, -1, -1,	'\0', 'p', '\0', 'i', '\0', '\0' }
  
  DATA(insert OID = 0 ( 1259 relname			19 0 NAMEDATALEN   1 0 -1 -1 f p f i f f));
  DATA(insert OID = 0 ( 1259 reltype			26 0  4   2 0 -1 -1 t p f i f f));
***************
*** 422,428 ****
  DATA(insert OID = 0 ( 1259 relrefs			21 0  2  16 0 -1 -1 t p f s f f));
  DATA(insert OID = 0 ( 1259 relhaspkey		16 0  1  17 0 -1 -1 t p f c f f));
  DATA(insert OID = 0 ( 1259 relhasrules		16 0  1  18 0 -1 -1 t p f c f f));
! DATA(insert OID = 0 ( 1259 relacl		  1034 0 -1  19 0 -1 -1 f p f i f f));
  DATA(insert OID = 0 ( 1259 ctid				27 0  6  -1 0 -1 -1 f p f i f f));
  DATA(insert OID = 0 ( 1259 oid				26 0  4  -2 0 -1 -1 t p f i f f));
  DATA(insert OID = 0 ( 1259 xmin				28 0  4  -3 0 -1 -1 t p f i f f));
--- 423,430 ----
  DATA(insert OID = 0 ( 1259 relrefs			21 0  2  16 0 -1 -1 t p f s f f));
  DATA(insert OID = 0 ( 1259 relhaspkey		16 0  1  17 0 -1 -1 t p f c f f));
  DATA(insert OID = 0 ( 1259 relhasrules		16 0  1  18 0 -1 -1 t p f c f f));
! DATA(insert OID = 0 ( 1259 relhassubclass	16 0  1   19 0 -1 -1 t p f c f f));
! DATA(insert OID = 0 ( 1259 relacl		  1034 0 -1  20 0 -1 -1 f p f i f f));
  DATA(insert OID = 0 ( 1259 ctid				27 0  6  -1 0 -1 -1 f p f i f f));
  DATA(insert OID = 0 ( 1259 oid				26 0  4  -2 0 -1 -1 t p f i f f));
  DATA(insert OID = 0 ( 1259 xmin				28 0  4  -3 0 -1 -1 t p f i f f));
Index: pgsql/src/include/catalog/pg_class.h
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/include/catalog/pg_class.h,v
retrieving revision 1.33
diff -c -r1.33 pg_class.h
*** pgsql/src/include/catalog/pg_class.h	2000/01/26 05:57:57	1.33
--- pgsql/src/include/catalog/pg_class.h	2000/02/05 08:25:09
***************
*** 78,88 ****
  	int2		relrefs;		/* # of references to this relation */
  	bool		relhaspkey;		/* has PRIMARY KEY */
  	bool		relhasrules;
  	aclitem		relacl[1];		/* this is here for the catalog */
  } FormData_pg_class;
  
  #define CLASS_TUPLE_SIZE \
! 	 (offsetof(FormData_pg_class,relhasrules) + sizeof(bool))
  
  /* ----------------
   *		Form_pg_class corresponds to a pointer to a tuple with
--- 78,89 ----
  	int2		relrefs;		/* # of references to this relation */
  	bool		relhaspkey;		/* has PRIMARY KEY */
  	bool		relhasrules;
+ 	bool		relhassubclass;
  	aclitem		relacl[1];		/* this is here for the catalog */
  } FormData_pg_class;
  
  #define CLASS_TUPLE_SIZE \
! 	 (offsetof(FormData_pg_class,relhassubclass) + sizeof(bool))
  
  /* ----------------
   *		Form_pg_class corresponds to a pointer to a tuple with
***************
*** 102,109 ****
   *		relacl field.
   * ----------------
   */
! #define Natts_pg_class_fixed			18
! #define Natts_pg_class					19
  #define Anum_pg_class_relname			1
  #define Anum_pg_class_reltype			2
  #define Anum_pg_class_relowner			3
--- 103,110 ----
   *		relacl field.
   * ----------------
   */
! #define Natts_pg_class_fixed			19
! #define Natts_pg_class					20
  #define Anum_pg_class_relname			1
  #define Anum_pg_class_reltype			2
  #define Anum_pg_class_relowner			3
***************
*** 122,128 ****
  #define Anum_pg_class_relrefs			16
  #define Anum_pg_class_relhaspkey		17
  #define Anum_pg_class_relhasrules		18
! #define Anum_pg_class_relacl			19
  
  /* ----------------
   *		initial contents of pg_class
--- 123,130 ----
  #define Anum_pg_class_relrefs			16
  #define Anum_pg_class_relhaspkey		17
  #define Anum_pg_class_relhasrules		18
! #define Anum_pg_class_relhassubclass		19
! #define Anum_pg_class_relacl			20
  
  /* ----------------
   *		initial contents of pg_class
***************
*** 135,141 ****
  DESCR("");
  DATA(insert OID = 1255 (  pg_proc 81		  PGUID 0 0 0 0 f f r 16 0 0 0 0 0 f f _null_ ));
  DESCR("");
! DATA(insert OID = 1259 (  pg_class 83		  PGUID 0 0 0 0 f f r 19 0 0 0 0 0 f f _null_ ));
  DESCR("");
  DATA(insert OID = 1260 (  pg_shadow 86		  PGUID 0 0 0 0 f t r 8  0 0 0 0 0 f f _null_ ));
  DESCR("");
--- 137,143 ----
  DESCR("");
  DATA(insert OID = 1255 (  pg_proc 81		  PGUID 0 0 0 0 f f r 16 0 0 0 0 0 f f _null_ ));
  DESCR("");
! DATA(insert OID = 1259 (  pg_class 83		  PGUID 0 0 0 0 f f r 20 0 0 0 0 0 f f _null_ ));
  DESCR("");
  DATA(insert OID = 1260 (  pg_shadow 86		  PGUID 0 0 0 0 f t r 8  0 0 0 0 0 f f _null_ ));
  DESCR("");
Sat, 5 Feb 2000 04:31:02 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] New improved patch^N