Re: CREATE TABLE LIKE INCLUDING INDEXES support

Bruce Momjian wrote:
> NikhilS wrote:
>
>> Hi,
>>
>> On 4/10/07, Bruce Momjian <bruce@momjian.us> wrote:
>>
>>> Added to TODO:
>>>
>>>         o Have WITH CONSTRAINTS also create constraint indexes
>>>
>>> http://archives.postgresql.org/pgsql-patches/2007-04/msg00149.php
>>>
>> Trevor's patch does add unique/primary indexes. This would mean that we have
>> to remove the syntax support for "INCLUDING INDEXES" and just add code to
>> the existing WITH CONSTRAINTs code path from his patch.
>>
>
> That is all that is required.
>
>
>> Is there something else and hence we have the above TODO?
>>
>
> If someone wants to work on this item and submit it, we can review it
> for 8.3, but if not, it waits until 8.4.
>
>
I've updated my patch to merge the INDEXES behavior implemented into the
CONSTRAINTS option, and restore the current error triggered when you try
to use the INDEXES option. Attached is the updated patch.

I didn't remove the INDEXES syntax, just undocumented it again and put
the error it raised back in. It seems like an implementation of copying
all of the indexes could still use that syntax.

Thank you for all the comments,
-Trevor Hardcastle
Index: src/backend/parser/analyze.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/parser/analyze.c,v
retrieving revision 1.362
diff -c -r1.362 analyze.c
*** src/backend/parser/analyze.c    13 Mar 2007 00:33:41 -0000    1.362
--- src/backend/parser/analyze.c    12 Apr 2007 17:54:49 -0000
***************
*** 28,33 ****
--- 28,34 ----
  #include "postgres.h"

  #include "access/heapam.h"
+ #include "access/genam.h"
  #include "catalog/heap.h"
  #include "catalog/index.h"
  #include "catalog/namespace.h"
***************
*** 54,59 ****
--- 55,61 ----
  #include "utils/acl.h"
  #include "utils/builtins.h"
  #include "utils/lsyscache.h"
+ #include "utils/relcache.h"
  #include "utils/syscache.h"


***************
*** 1331,1338 ****
      }

      /*
!      * Copy CHECK constraints if requested, being careful to adjust
!      * attribute numbers
       */
      if (including_constraints && tupleDesc->constr)
      {
--- 1333,1340 ----
      }

      /*
!      * Copy CHECK based constraints if requested, being careful to adjust
!      * attribute numbers. Also duplicate unique index constraints.
       */
      if (including_constraints && tupleDesc->constr)
      {
***************
*** 1355,1360 ****
--- 1357,1431 ----
              n->indexspace = NULL;
              cxt->ckconstraints = lappend(cxt->ckconstraints, (Node *) n);
          }
+
+         /*
+          * Clone constraint indexes if any exist.
+          */
+         if (relation->rd_rel->relhasindex)
+         {
+             List       *parent_index_list = RelationGetIndexList(relation);
+             ListCell   *parent_index_scan;
+
+             foreach(parent_index_scan, parent_index_list)
+             {
+                 Oid        parent_index_oid = lfirst_oid(parent_index_scan);
+                 Relation   parent_index;
+
+                 parent_index = index_open(parent_index_oid, AccessShareLock);
+
+                 /*
+                  * Create new unique or primary key indexes on the child.
+                  */
+                 if (parent_index->rd_index->indisunique || parent_index->rd_index->indisprimary)
+                 {
+                     IndexInfo  *parent_index_info;
+                     Constraint *n = makeNode(Constraint);
+                     AttrNumber  parent_attno;
+
+                     parent_index_info = BuildIndexInfo(parent_index);
+
+                     if (parent_index->rd_index->indisprimary)
+                     {
+                         n->contype = CONSTR_PRIMARY;
+                     }
+                     else
+                     {
+                         n->contype = CONSTR_UNIQUE;
+                     }
+                     /* Let DefineIndex name it */
+                     n->name = NULL;
+                     n->raw_expr = NULL;
+                     n->cooked_expr = NULL;
+
+                     /*
+                      * Search through the possible index keys, and append
+                      * the names of simple columns to the new index key list.
+                      */
+                     for (parent_attno = 1; parent_attno <= parent_index->rd_att->natts;
+                          parent_attno++)
+                     {
+                         Form_pg_attribute attribute = parent_index->rd_att->attrs[parent_attno - 1];
+                         char       *attributeName = NameStr(attribute->attname);
+
+                         /*
+                          * Ignore dropped columns in the parent.
+                          */
+                         if (!attribute->attisdropped)
+                             n->keys = lappend(n->keys,
+                                               makeString(attributeName));
+                     }
+
+                     /* Add the new index constraint to the create context */
+                     cxt->ixconstraints = lappend(cxt->ixconstraints, n);
+
+                     ereport(NOTICE,
+                             (errmsg("Index \"%s\" cloned.",
+                                     RelationGetRelationName(parent_index))));
+                 }
+
+                 relation_close(parent_index, AccessShareLock);
+             }
+         }
      }

      /*
Index: src/test/regress/sql/inherit.sql
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/sql/inherit.sql,v
retrieving revision 1.10
diff -c -r1.10 inherit.sql
*** src/test/regress/sql/inherit.sql    27 Jun 2006 03:43:20 -0000    1.10
--- src/test/regress/sql/inherit.sql    12 Apr 2007 17:54:49 -0000
***************
*** 151,160 ****
  DROP TABLE inhg;
  CREATE TABLE inhg (x text, LIKE inhx INCLUDING CONSTRAINTS, y text); /* Copies constraints */
  INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Succeeds */
! INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Succeeds -- Unique constraints not copied */
  INSERT INTO inhg VALUES ('x', 'foo',  'y');  /* fails due to constraint */
  SELECT * FROM inhg; /* Two records with three columns in order x=x, xx=text, y=y */
  DROP TABLE inhg;


  -- Test changing the type of inherited columns
--- 151,161 ----
  DROP TABLE inhg;
  CREATE TABLE inhg (x text, LIKE inhx INCLUDING CONSTRAINTS, y text); /* Copies constraints */
  INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Succeeds */
! INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Fails -- Unique constraints copied */
  INSERT INTO inhg VALUES ('x', 'foo',  'y');  /* fails due to constraint */
  SELECT * FROM inhg; /* Two records with three columns in order x=x, xx=text, y=y */
  DROP TABLE inhg;
+ CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* Unimplemented */


  -- Test changing the type of inherited columns
Index: src/test/regress/expected/inherit.out
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/inherit.out,v
retrieving revision 1.20
diff -c -r1.20 inherit.out
*** src/test/regress/expected/inherit.out    27 Jun 2006 03:43:20 -0000    1.20
--- src/test/regress/expected/inherit.out    12 Apr 2007 17:54:49 -0000
***************
*** 621,638 ****
  INSERT INTO inhg VALUES ('foo');
  DROP TABLE inhg;
  CREATE TABLE inhg (x text, LIKE inhx INCLUDING CONSTRAINTS, y text); /* Copies constraints */
  INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Succeeds */
! INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Succeeds -- Unique constraints not copied */
  INSERT INTO inhg VALUES ('x', 'foo',  'y');  /* fails due to constraint */
  ERROR:  new row for relation "inhg" violates check constraint "foo"
  SELECT * FROM inhg; /* Two records with three columns in order x=x, xx=text, y=y */
   x |  xx  | y
  ---+------+---
   x | text | y
!  x | text | y
! (2 rows)

  DROP TABLE inhg;
  -- Test changing the type of inherited columns
  insert into d values('test','one','two','three');
  alter table a alter column aa type integer using bit_length(aa);
--- 621,642 ----
  INSERT INTO inhg VALUES ('foo');
  DROP TABLE inhg;
  CREATE TABLE inhg (x text, LIKE inhx INCLUDING CONSTRAINTS, y text); /* Copies constraints */
+ NOTICE:  Index "inhx_pkey" cloned.
+ NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "inhg_pkey" for table "inhg"
  INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Succeeds */
! INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Fails -- Unique constraints copied */
! ERROR:  duplicate key violates unique constraint "inhg_pkey"
  INSERT INTO inhg VALUES ('x', 'foo',  'y');  /* fails due to constraint */
  ERROR:  new row for relation "inhg" violates check constraint "foo"
  SELECT * FROM inhg; /* Two records with three columns in order x=x, xx=text, y=y */
   x |  xx  | y
  ---+------+---
   x | text | y
! (1 row)

  DROP TABLE inhg;
+ CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* Unimplemented */
+ ERROR:  LIKE INCLUDING INDEXES is not implemented
  -- Test changing the type of inherited columns
  insert into d values('test','one','two','three');
  alter table a alter column aa type integer using bit_length(aa);
Index: doc/src/sgml/ref/create_table.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/create_table.sgml,v
retrieving revision 1.107
diff -c -r1.107 create_table.sgml
*** doc/src/sgml/ref/create_table.sgml    1 Feb 2007 00:28:18 -0000    1.107
--- doc/src/sgml/ref/create_table.sgml    12 Apr 2007 17:54:50 -0000
***************
*** 259,269 ****
       </para>
       <para>
        Not-null constraints are always copied to the new table.
!       <literal>CHECK</literal> constraints will only be copied if
!       <literal>INCLUDING CONSTRAINTS</literal> is specified; other types of
!       constraints will never be copied. Also, no distinction is made between
!       column constraints and table constraints — when constraints are
!       requested, all check constraints are copied.
       </para>
       <para>
        Note also that unlike <literal>INHERITS</literal>, copied columns and
--- 259,268 ----
       </para>
       <para>
        Not-null constraints are always copied to the new table.
!       <literal>CHECK, UNIQUE, and PRIMARY KEY</literal> constraints will only
!       be copied if <literal>INCLUDING CONSTRAINTS</literal> is specified. Also,
!       no distinction is made between column constraints and table constraints
!       — when constraints are requested, all check constraints are copied.
       </para>
       <para>
        Note also that unlike <literal>INHERITS</literal>, copied columns and