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:
> 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(at)gmail(dot)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 &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 majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

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