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

Your patch has been added to the PostgreSQL unapplied patches list at:

    http://momjian.postgresql.org/cgi-bin/pgpatches

It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.

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


Trevor Hardcastle wrote:
> 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

--
  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. +