Greetings all,

I wrote this patch about a week ago to introduce myself to coding on PostgreSQL. I wasn't entirely sure what the 'INCLUDING INDEXES' option was meant to do, so I held off submitting it until I could get around to asking about that and tweaking the documentation to reflect the patch. By useful coincidence the thread "Auto creation of Partitions" had this post in it, which made the intent of the option clear enough for me to go ahead and see what people think of this.

Gregory Stark wrote:
"NikhilS" <[EMAIL PROTECTED]> writes:

the intention is to use this information from the parent and make it a
property of the child table. This will avoid the step for the user having to
manually specify CREATE INDEX and the likes on all the children tables
one-by-one.

Missed the start of this thread. A while back I had intended to add WITH
INDEXES to CREATE TABLE LIKE. That would let you create a tale LIKE parent
WITH CONSTRAINTS WITH INDEXES and get pretty much a perfect table ready for
adding to the inheritance structure.


So, that's what this patch does. When a table is created with 'CREATE TABLE ... LIKE parent INCLUDING INDEXES' this iterates over the parent table indexes looking for constraint indexes, and alters the CreateStmtContext to include equivalent indexes on the child table.

This is probably a somewhat naive implementation, being a first attempt. I wasn't sure what sort of lock to place on the parent indexes or what tablespace the new indexes should be created in. Any help improving it would be appreciated.

Thank you,
-Trevor Hardcastle

Index: src/backend/parser/analyze.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/parser/analyze.c,v
retrieving revision 1.361
diff -c -r1.361 analyze.c
*** src/backend/parser/analyze.c        20 Feb 2007 17:32:16 -0000      1.361
--- src/backend/parser/analyze.c        7 Mar 2007 01:43:12 -0000
***************
*** 14,19 ****
--- 14,20 ----
  #include "postgres.h"
  
  #include "access/heapam.h"
+ #include "access/genam.h"
  #include "catalog/heap.h"
  #include "catalog/index.h"
  #include "catalog/namespace.h"
***************
*** 40,45 ****
--- 41,47 ----
  #include "utils/acl.h"
  #include "utils/builtins.h"
  #include "utils/lsyscache.h"
+ #include "utils/relcache.h"
  #include "utils/syscache.h"
  
  
***************
*** 1345,1355 ****
                }
        }
  
-       if (including_indexes)
-               ereport(ERROR,
-                               (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-                                errmsg("LIKE INCLUDING INDEXES is not 
implemented")));
- 
        /*
         * Insert the copied attributes into the cxt for the new table
         * definition.
--- 1347,1352 ----
***************
*** 1448,1453 ****
--- 1445,1519 ----
        }
  
        /*
+        * Clone constraint indexes if requested.
+        */
+       if (including_indexes && 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);
+               }
+       }
+ 
+       /*
         * Close the parent rel, but keep our AccessShareLock on it until xact
         * commit.      That will prevent someone else from deleting or 
ALTERing the
         * parent before the child is committed.
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  7 Mar 2007 01:43:13 -0000
***************
*** 23,29 ****
  CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable 
class="PARAMETER">table_name</replaceable> ( [
    { <replaceable class="PARAMETER">column_name</replaceable> <replaceable 
class="PARAMETER">data_type</replaceable> [ DEFAULT 
<replaceable>default_expr</> ] [ <replaceable 
class="PARAMETER">column_constraint</replaceable> [ ... ] ]
      | <replaceable>table_constraint</replaceable>
!     | LIKE <replaceable>parent_table</replaceable> [ { INCLUDING | EXCLUDING 
} { DEFAULTS | CONSTRAINTS } ] ... }
      [, ... ]
  ] )
  [ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ]
--- 23,29 ----
  CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable 
class="PARAMETER">table_name</replaceable> ( [
    { <replaceable class="PARAMETER">column_name</replaceable> <replaceable 
class="PARAMETER">data_type</replaceable> [ DEFAULT 
<replaceable>default_expr</> ] [ <replaceable 
class="PARAMETER">column_constraint</replaceable> [ ... ] ]
      | <replaceable>table_constraint</replaceable>
!     | LIKE <replaceable>parent_table</replaceable> [ { INCLUDING | EXCLUDING 
} { DEFAULTS | CONSTRAINTS | INDEXES } ] ... }
      [, ... ]
  ] )
  [ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ]
***************
*** 237,243 ****
     </varlistentry>
  
     <varlistentry>
!     <term><literal>LIKE <replaceable>parent_table</replaceable> [ { INCLUDING 
| EXCLUDING } { DEFAULTS | CONSTRAINTS } ]</literal></term>
      <listitem>
       <para>
        The <literal>LIKE</literal> clause specifies a table from which
--- 237,243 ----
     </varlistentry>
  
     <varlistentry>
!     <term><literal>LIKE <replaceable>parent_table</replaceable> [ { INCLUDING 
| EXCLUDING | INDEXES } { DEFAULTS | CONSTRAINTS } ]</literal></term>
      <listitem>
       <para>
        The <literal>LIKE</literal> clause specifies a table from which
***************
*** 260,269 ****
       <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 &mdash; when constraints are
!       requested, all check constraints are copied.
       </para>
       <para>
        Note also that unlike <literal>INHERITS</literal>, copied columns and
--- 260,271 ----
       <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. UNIQUE and 
!         PRIMARY KEY constraints will only be copied if 
!         <literal>INCLUDING INDEXES</literal> is specified. Also, no 
!         distinction is made between column constraints and table constraints
!         &mdash; when constraints are requested, all check constraints are 
!         copied.
       </para>
       <para>
        Note also that unlike <literal>INHERITS</literal>, copied columns and
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    7 Mar 2007 01:43:13 -0000
***************
*** 155,160 ****
--- 155,164 ----
  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); /* Copies 
indexes */
+ INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Succeeds */
+ INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Fails -- Unique constraints 
are copied */
+ DROP TABLE inhg;
  
  
  -- 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       7 Mar 2007 01:43:14 -0000
***************
*** 633,638 ****
--- 633,645 ----
  (2 rows)
  
  DROP TABLE inhg;
+ CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* Copies 
indexes */
+ 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 
are copied */
+ ERROR:  duplicate key violates unique constraint "inhg_pkey"
+ 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);
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [EMAIL PROTECTED] so that your
       message can get through to the mailing list cleanly

Reply via email to