Re: CREATE TABLE LIKE INCLUDING INDEXES support - Mailing list pgsql-patches

NikhilS wrote:
> Hi Trevor,
>
>
>     +
>     +                                       parent_index_info =
>     BuildIndexInfo(parent_index);
>
>
> The above is not used anywhere else in the code and seems redundant.
Yep, pulled that out.
>
>     +
>     +                                       ereport(NOTICE,
>     +
>     (errmsg("Index \"%s\" cloned.",
>     +
>     RelationGetRelationName(parent_index))));
>
>
> DefineIndex will give out a message anyways for unique/primary keys.
> The above seems additional to it.
The original reason for this was the support for copying all indexes,
but it doesn't make much sense now. I've pulled it too.

Thanks for pointing those out. An updated patch is attached.

-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    13 Apr 2007 16:41:46 -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,1424 ----
              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)
+                 {
+                     Constraint *n = makeNode(Constraint);
+                     AttrNumber  parent_attno;
+
+                     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);
+                 }
+
+                 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    13 Apr 2007 16:41:46 -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    13 Apr 2007 16:41:46 -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,641 ----
  INSERT INTO inhg VALUES ('foo');
  DROP TABLE inhg;
  CREATE TABLE inhg (x text, LIKE inhx INCLUDING CONSTRAINTS, y text); /* Copies constraints */
+ 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    13 Apr 2007 16:41:46 -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