ALTER TABLE ... ADD/DROPS INHERIT (actually INHERIT / NO INHERIT)

Open items:

There were a few tangentially related issues that have come up that I think
are TODOs. I'm likely to tackle one or two of these next so I'm interested in
hearing feedback on them as well.

. Constraints currently do not know anything about inheritance. Tom suggested
  adding a coninhcount and conislocal like attributes have to track their
  inheritance status.

. Foreign key constraints currently do not get copied to new children (and
  therefore my code doesn't verify them). I don't think it would be hard to
  add them and treat them like CHECK constraints.

. No constraints at all are copied to tables defined with LIKE. That makes it
  hard to use LIKE to define new partitions. The standard defines LIKE and
  specifically says it does not copy constraints. But the standard already has
  an option called INCLUDING DEFAULTS; we could always define a non-standard
  extension LIKE table INCLUDING CONSTRAINTS that gives the user the option to
  request a copy including constraints.

. Personally, I think the whole attislocal thing is bunk. The decision about
  whether to drop a column from children tables or not is something that
  should be up to the user and trying to DWIM based on whether there was ever
  a local definition or the column was acquired purely through inheritance is
  hardly ever going to match up with user expectations.

. And of course there's the whole unique and primary key constraint issue. I
  think to get any traction at all on this you have a prerequisite of a real
  partitioned table implementation where the system knows what the partition
  key is so it can recognize when it's a leading part of an index key.

Greg Stark

2 files changed, 211 insertions, 62 deletions

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index a6fb4b3691f..a404a9c6141 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.57 2006/04/30 21:15:32 tgl Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.58 2006/07/02 01:58:36 momjian Exp $ -->
 
 <chapter id="ddl">
  <title>Data Definition</title>
@@ -2061,27 +2061,47 @@ VALUES ('New York', NULL, NULL, 'NY');
   </para>
 
   <para>
-   Table inheritance can currently only be defined using the <xref
-   linkend="sql-createtable" endterm="sql-createtable-title">
-   statement.  The related statement <command>CREATE TABLE AS</command> does
-   not allow inheritance to be specified. There
-   is no way to add an inheritance link to make an existing table into
-   a child table. Similarly, there is no way to remove an inheritance
-   link from a child table once it has been defined, other than by dropping
-   the table completely.  A parent table cannot be dropped
-   while any of its children remain. If you wish to remove a table and
-   all of its descendants, one easy way is to drop the parent table with
-   the <literal>CASCADE</literal> option.
+   Table inheritance can be defined using the <xref linkend="sql-createtable"
+   endterm="sql-createtable-title"> statement using the
+   <command>INHERITS</command> keyword. However the related statement
+   <command>CREATE TABLE AS</command> does not allow inheritance to be
+   specified. 
+  </para>
+
+  <para>
+   Alternatively a table which is already defined in a compatible way can have
+   a new parent added with <xref linkend="sql-altertable"
+   endterm="sql-altertable-title"> using the <command>INHERIT</command>
+   subform. To do this the new child table must already include columns with
+   the same name and type as the columns of the parent. It must also include
+   check constraints with the same name and check expression as those of the
+   parent. Similarly an inheritance link can be removed from a child using the
+   <command>ALTER TABLE</command> using the <command>NO INHERIT</command>
+   subform.
+
+  <para>
+   One convenient way to create a compatible table to be a new child is using
+   the <command>LIKE</command> option of <command>CREATE TABLE</command>. This
+   creates a table with the same columns with the same type (however note the
+   caveat below regarding constraints). Alternatively a compatible table can
+   be created by first creating a new child using <command>CREATE
+   TABLE</command> then removing the inheritance link with <command>ALTER
+   TABLE</command>. </para>
+
+  <para>
+   A parent table cannot be dropped while any
+   of its children remain. If you wish to remove a table and all of its
+   descendants, one easy way is to drop the parent table with the
+   <literal>CASCADE</literal> option. Neither can columns of child tables be
+   dropped or altered if they are inherited from any parent tables.
   </para>
 
   <para>
    <xref linkend="sql-altertable" endterm="sql-altertable-title"> will
-   propagate any changes in column data definitions and check
-   constraints down the inheritance hierarchy.  Again, dropping
-   columns or constraints on parent tables is only possible when using
-   the <literal>CASCADE</literal> option. <command>ALTER
-   TABLE</command> follows the same rules for duplicate column merging
-   and rejection that apply during <command>CREATE TABLE</command>.
+   propagate any changes in column data definitions and check constraints down
+   the inheritance hierarchy. <command>ALTER TABLE</command> follows the same
+   rules for duplicate column merging and rejection that apply during
+   <command>CREATE TABLE</command>. 
   </para>
 
  <sect2 id="ddl-inherit-caveats">
@@ -2136,6 +2156,29 @@ VALUES ('New York', NULL, NULL, 'NY');
       not capital names.  There is no good workaround for this case.
      </para>
     </listitem>
+
+    <listitem>
+     <para>
+      There is no convenient way to define a table compatible with a specific
+      parent including columns and constraints. The <command>LIKE</command>
+      option for <command>CREATE TABLE</command> does not copy constraints
+      which makes the tables it creates ineligible for being added using
+      <command>ALTER TABLE</command>. Matching check constraints must be added 
+      manually or the table must be created as a child immediately, then if
+      needed removed from the inheritance structure temporarily to be added
+      again later.
+     </para>
+    </listitem>
+
+    <listitem>
+     <para>
+      If a table is ever removed from the inheritance structure using
+      <command>ALTER TABLE</command> then all its columns will be marked as
+      being locally defined. This means <command>DROP COLUMN</command> on the
+      parent table will never cascade to drop those columns on the child
+      table. They must be dropped manually.
+     </para>
+    </listitem>
    </itemizedlist>
 
    These deficiencies will probably be fixed in some future release,
@@ -2186,27 +2229,37 @@ VALUES ('New York', NULL, NULL, 'NY');
    <itemizedlist>
     <listitem>
      <para>
-      Query performance can be improved dramatically for certain kinds
-      of queries.
+      Query performance can be improved when partition constraints can be
+      combined with local indexes to reduce the number of records that need to
+      be accessed for a query. Whereas the alternative, adding those columns
+      to every index, increases space usage which can erase any
+      performance gain.
+     <para>
+
+     <para>
+      When most of the heavily accessed area of the table is in a single
+      partition or a small number of partitions. That partition and its
+      indexes are more likely to fit within memory than the index of the
+      entire table.
      </para>
     </listitem>
 
     <listitem>
      <para>
-      Update performance can be improved too, since each piece of the table
-      has indexes smaller than an index on the entire data set would be.
-      When an index no longer fits easily
-      in memory, both read and write operations on the index take
-      progressively more disk accesses.
+      When queries or updates access a large percentage of a a single
+      partition performance can be improved dramatically by taking advantage
+      of sequential disk access of a single partition instead of using an
+      index and random access reads across the whole table.
      </para>
     </listitem>
 
     <listitem>
      <para>
-      Bulk deletes may be accomplished by simply removing one of the
-      partitions, if that requirement is planned into the partitioning design.
-      <command>DROP TABLE</> is far faster than a bulk <command>DELETE</>,
-      to say nothing of the ensuing <command>VACUUM</> overhead.
+      Bulk loads and deletes may be accomplished by simply removing or adding
+      one of the partitions. <command>ALTER TABLE</> is far faster than a bulk
+      and takes the same amount of time regardless of the amount of data being
+      added or removed. It also entirely avoids the <command>VACUUM</command>
+      overhead caused by a bulk <command>delete</>.
      </para>
     </listitem>
 
@@ -2404,12 +2457,12 @@ CREATE TABLE measurement (
         Next we create one partition for each active month:
 
 <programlisting>
-CREATE TABLE measurement_yy04mm02 ( ) INHERITS (measurement);
-CREATE TABLE measurement_yy04mm03 ( ) INHERITS (measurement);
+CREATE TABLE measurement_y2004m02 ( ) INHERITS (measurement);
+CREATE TABLE measurement_y2004m03 ( ) INHERITS (measurement);
 ...
-CREATE TABLE measurement_yy05mm11 ( ) INHERITS (measurement);
-CREATE TABLE measurement_yy05mm12 ( ) INHERITS (measurement);
-CREATE TABLE measurement_yy06mm01 ( ) INHERITS (measurement);
+CREATE TABLE measurement_y2005m11 ( ) INHERITS (measurement);
+CREATE TABLE measurement_y2005m12 ( ) INHERITS (measurement);
+CREATE TABLE measurement_y2006m01 ( ) INHERITS (measurement);
 </programlisting>
 
         Each of the partitions are complete tables in their own right,
@@ -2431,20 +2484,20 @@ CREATE TABLE measurement_yy06mm01 ( ) INHERITS (measurement);
         table creation script becomes:
 
  <programlisting>
-CREATE TABLE measurement_yy04mm02 (
+CREATE TABLE measurement_y2004m02 (
     CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
 ) INHERITS (measurement);
-CREATE TABLE measurement_yy04mm03 (
+CREATE TABLE measurement_y2004m03 (
     CHECK ( logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01' )
 ) INHERITS (measurement);
 ...
-CREATE TABLE measurement_yy05mm11 (
+CREATE TABLE measurement_y2005m11 (
     CHECK ( logdate >= DATE '2005-11-01' AND logdate < DATE '2005-12-01' )
 ) INHERITS (measurement);
-CREATE TABLE measurement_yy05mm12 (
+CREATE TABLE measurement_y2005m12 (
     CHECK ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' )
 ) INHERITS (measurement);
-CREATE TABLE measurement_yy06mm01 (
+CREATE TABLE measurement_y2006m01 (
     CHECK ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' )
 ) INHERITS (measurement);
 </programlisting>
@@ -2456,12 +2509,12 @@ CREATE TABLE measurement_yy06mm01 (
         We probably need indexes on the key columns too:
 
  <programlisting>
-CREATE INDEX measurement_yy04mm02_logdate ON measurement_yy04mm02 (logdate);
-CREATE INDEX measurement_yy04mm03_logdate ON measurement_yy04mm03 (logdate);
+CREATE INDEX measurement_y2004m02_logdate ON measurement_y2004m02 (logdate);
+CREATE INDEX measurement_y2004m03_logdate ON measurement_y2004m03 (logdate);
 ...
-CREATE INDEX measurement_yy05mm11_logdate ON measurement_yy05mm11 (logdate);
-CREATE INDEX measurement_yy05mm12_logdate ON measurement_yy05mm12 (logdate);
-CREATE INDEX measurement_yy06mm01_logdate ON measurement_yy06mm01 (logdate);
+CREATE INDEX measurement_y2005m11_logdate ON measurement_y2005m11 (logdate);
+CREATE INDEX measurement_y2005m12_logdate ON measurement_y2005m12 (logdate);
+CREATE INDEX measurement_y2006m01_logdate ON measurement_y2006m01 (logdate);
 </programlisting>
 
         We choose not to add further indexes at this time.
@@ -2479,7 +2532,7 @@ CREATE INDEX measurement_yy06mm01_logdate ON measurement_yy06mm01 (logdate);
 CREATE OR REPLACE RULE measurement_current_partition AS
 ON INSERT TO measurement
 DO INSTEAD
-    INSERT INTO measurement_yy06mm01 VALUES ( NEW.city_id,
+    INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id,
                                               NEW.logdate,
                                               NEW.peaktemp,
                                               NEW.unitsales );
@@ -2490,28 +2543,28 @@ DO INSTEAD
         could do this with a more complex set of rules as shown below.
 
 <programlisting>
-CREATE RULE measurement_insert_yy04mm02 AS
+CREATE RULE measurement_insert_y2004m02 AS
 ON INSERT TO measurement WHERE
     ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
 DO INSTEAD
-    INSERT INTO measurement_yy04mm02 VALUES ( NEW.city_id,
+    INSERT INTO measurement_y2004m02 VALUES ( NEW.city_id,
                                               NEW.logdate,
                                               NEW.peaktemp,
                                               NEW.unitsales );
 ...
-CREATE RULE measurement_insert_yy05mm12 AS
+CREATE RULE measurement_insert_y2005m12 AS
 ON INSERT TO measurement WHERE
     ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' )
 DO INSTEAD
-    INSERT INTO measurement_yy05mm12 VALUES ( NEW.city_id,
+    INSERT INTO measurement_y2005m12 VALUES ( NEW.city_id,
                                               NEW.logdate,
                                               NEW.peaktemp,
                                               NEW.unitsales );
-CREATE RULE measurement_insert_yy06mm01 AS
+CREATE RULE measurement_insert_y2006m01 AS
 ON INSERT TO measurement WHERE
     ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' )
 DO INSTEAD
-    INSERT INTO measurement_yy06mm01 VALUES ( NEW.city_id,
+    INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id,
                                               NEW.logdate,
                                               NEW.peaktemp,
                                               NEW.unitsales );
@@ -2522,6 +2575,44 @@ DO INSTEAD
         constraint for its partition.
        </para>
       </listitem>
+
+      <listitem>
+       <para>
+        When the time comes to archive and remove the old data we first remove
+        it from the production table using:
+
+<programlisting>
+ALTER TABLE measurement_y2003mm02 NO INHERIT measurement
+</programlisting>
+
+        Then we can perform any sort of data modification necessary prior to
+        archiving without impacting the data viewed by the production system.
+        This could include, for example, deleting or compressing out redundant
+        data.
+       </para>
+      </listitem>
+      <listitem>
+      <para>
+
+       Similarly we can a new partition to handle new data. We can either
+       create an empty partition as the original partitions were created
+       above, or for some applications it's necessary to bulk load and clean
+       data for the new partition. If that operation involves multiple steps
+       by different processes it can be helpful to work with it in a fresh
+       table outside of the master partitioned table until it's ready to be
+       loaded:
+
+<programlisting>
+CREATE TABLE measurement_y2006m02 (LIKE measurement WITH DEFAULTS);
+\COPY measurement_y2006m02 FROM 'measurement_y2006m02'
+UPDATE ...
+ALTER TABLE measurement_y2006m02 ADD CONSTRAINT y2006m02 CHECK ( logdate &gt;= DATE '2006-02-01' AND logdate &lt DATE '2006-03-01' );
+ALTER TABLE measurement_y2006m02 INHERIT MEASUREMENT;
+</programlisting>
+
+      </para>
+      </listitem>
+
      </orderedlist>
     </para>
 
@@ -2555,6 +2646,16 @@ DO INSTEAD
       using a set of rules as suggested above.)
      </para>
     </listitem>
+
+    <listitem>
+     <para>
+      When using the <literal>LIKE</> option above to create new partitions
+      check constraints are not copied from the parent. If there are any check
+      constraints defined for the parent they must be manually created in new
+      partitions before <command>ALTER TABLE</command> will allow them to be
+      added.
+     </para>
+    </listitem>
    </itemizedlist>
    </para>
 
@@ -2564,12 +2665,12 @@ DO INSTEAD
 
 <programlisting>
 CREATE VIEW measurement AS
-          SELECT * FROM measurement_yy04mm02
-UNION ALL SELECT * FROM measurement_yy04mm03
+          SELECT * FROM measurement_y2004m02
+UNION ALL SELECT * FROM measurement_y2004m03
 ...
-UNION ALL SELECT * FROM measurement_yy05mm11
-UNION ALL SELECT * FROM measurement_yy05mm12
-UNION ALL SELECT * FROM measurement_yy06mm01;
+UNION ALL SELECT * FROM measurement_y2005m11
+UNION ALL SELECT * FROM measurement_y2005m12
+UNION ALL SELECT * FROM measurement_y2006m01;
 </programlisting>
 
     However, the need to
@@ -2619,14 +2720,14 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';
    ->  Append  (cost=0.00..151.88 rows=2715 width=0)
          ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)
                Filter: (logdate >= '2006-01-01'::date)
-         ->  Seq Scan on measurement_yy04mm02 measurement  (cost=0.00..30.38 rows=543 width=0)
+         ->  Seq Scan on measurement_y2004m02 measurement  (cost=0.00..30.38 rows=543 width=0)
                Filter: (logdate >= '2006-01-01'::date)
-         ->  Seq Scan on measurement_yy04mm03 measurement  (cost=0.00..30.38 rows=543 width=0)
+         ->  Seq Scan on measurement_y2004m03 measurement  (cost=0.00..30.38 rows=543 width=0)
                Filter: (logdate >= '2006-01-01'::date)
 ...
-         ->  Seq Scan on measurement_yy05mm12 measurement  (cost=0.00..30.38 rows=543 width=0)
+         ->  Seq Scan on measurement_y2005m12 measurement  (cost=0.00..30.38 rows=543 width=0)
                Filter: (logdate >= '2006-01-01'::date)
-         ->  Seq Scan on measurement_yy06mm01 measurement  (cost=0.00..30.38 rows=543 width=0)
+         ->  Seq Scan on measurement_y2006m01 measurement  (cost=0.00..30.38 rows=543 width=0)
                Filter: (logdate >= '2006-01-01'::date)
 </programlisting>
 
@@ -2645,7 +2746,7 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';
    ->  Append  (cost=0.00..60.75 rows=1086 width=0)
          ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)
                Filter: (logdate >= '2006-01-01'::date)
-         ->  Seq Scan on measurement_yy06mm01 measurement  (cost=0.00..30.38 rows=543 width=0)
+         ->  Seq Scan on measurement_y2006m01 measurement  (cost=0.00..30.38 rows=543 width=0)
                Filter: (logdate >= '2006-01-01'::date)
 </programlisting>
    </para>
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 16f6ce8117a..ee501a1a372 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.84 2006/02/12 19:11:00 momjian Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.85 2006/07/02 01:58:36 momjian Exp $
 PostgreSQL documentation
 -->
 
@@ -46,6 +46,8 @@ where <replaceable class="PARAMETER">action</replaceable> is one of:
     CLUSTER ON <replaceable class="PARAMETER">index_name</replaceable>
     SET WITHOUT CLUSTER
     SET WITHOUT OIDS
+    INHERIT <replaceable class="PARAMETER">parent_table</replaceable>
+    NO INHERIT <replaceable class="PARAMETER">parent_table</replaceable>
     OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
     SET TABLESPACE <replaceable class="PARAMETER">new_tablespace</replaceable>
 </synopsis>
@@ -250,6 +252,52 @@ where <replaceable class="PARAMETER">action</replaceable> is one of:
    </varlistentry>
 
    <varlistentry>
+    <term><literal>INHERIT <replaceable class="PARAMETER">parent_table</replaceable></literal></term>
+    <listitem>
+     <para>
+
+      This form adds a new parent table to the table. This won't add new
+      columns to the child table, instead all columns of the parent table must
+      already exist in the child table. They must have matching data types,
+      and if they have <literal>NOT NULL</literal> constraints in the parent
+      then they must also have <literal>NOT NULL</literal> constraints in the
+      child.
+
+	  </para>
+	  <para>
+
+      There must also be matching table constraints for all
+      <literal>CHECK</literal> table constraints of the parent. Currently
+      <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and
+      <literal>FOREIGN KEY</literal> constraints are ignored however this may
+      change in the future.
+
+	  </para>
+	  <para>
+
+      The easiest way to create a suitable table is to create a table using
+      <literal>INHERITS</literal> and then remove it via <literal>NO
+      INHERIT</literal>. Alternatively create a table using
+      <literal>LIKE</literal> however note that <literal>LIKE</literal> does
+      not create the necessary constraints.
+
+     </para>
+
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><literal>NO INHERIT <replaceable class="PARAMETER">parent_table</replaceable></literal></term>
+    <listitem>
+     <para>
+	 This form removes a parent table from the list of parents of the table.
+	 Queries against the parent table will no longer include records drawn
+	 from the target table.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
     <term><literal>OWNER</literal></term>
     <listitem>
      <para>