Thread: New improved patch

New improved patch

From
Chris
Date:
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


Re: [HACKERS] New improved patch^N

From
Bruce Momjian
Date:
> 
> 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,
Pennsylvania19026
 


Re: [HACKERS] New improved patch^N

From
Chris
Date:
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


Re: [HACKERS] New improved patch^N

From
Bruce Momjian
Date:
>
> 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 > 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 > 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 > 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 > 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("");