Re: CREATE TABLE LIKE INCLUDING INDEXES support

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 &mdash; 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
> ! &mdash; when constraints are requested, all check constraints are copied.
> </para>
> <para>
> Note also that unlike <literal>INHERITS</literal>, copied columns and

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

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