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

Uh, shouldn't CREATE TABLE LIKE INCLUDING CONSTRAINTS already be including
any indexes in the parent table?

---------------------------------------------------------------------------

Trevor Hardcastle wrote:
> 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" <nikkhils@gmail.com> 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 — 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
> !       — 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 majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly

--
  Bruce Momjian  <bruce@momjian.us>          http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +