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 &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@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

"Bruce Momjian" <bruce@momjian.us> writes:

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

You could argue it should for unique indexes since our unique indexes are how
we implement unique constraints. But I see no particular reason to expect it
to copy random other indexes. At least its name doesn't lead one to expect it
to.

I also thought it was sort of strange to have a command that otherwise is just
copying definitions suddenly start building whole new objects. I think I was
thinking it would be a long slow operation but I suppose creating an empty
index isn't really noticeably slow. It could print a NOTICE similar to what's
printed when you create a primary key or unique constraint.

It does mean that users would be unable to create a partition, load data, then
build indexes. Perhaps it would be nice to have an ALTER TABLE foo LIKE bar
INCLUDING CONSTRAINTS as well.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Gregory Stark wrote:
> "Bruce Momjian" <bruce@momjian.us> writes:
>
> > Uh, shouldn't CREATE TABLE LIKE INCLUDING CONSTRAINTS already be including
> > any indexes in the parent table?
>
> You could argue it should for unique indexes since our unique indexes are how
> we implement unique constraints. But I see no particular reason to expect it
> to copy random other indexes. At least its name doesn't lead one to expect it
> to.
>
> I also thought it was sort of strange to have a command that otherwise is just
> copying definitions suddenly start building whole new objects. I think I was
> thinking it would be a long slow operation but I suppose creating an empty
> index isn't really noticeably slow. It could print a NOTICE similar to what's
> printed when you create a primary key or unique constraint.
>
> It does mean that users would be unable to create a partition, load data, then
> build indexes. Perhaps it would be nice to have an ALTER TABLE foo LIKE bar
> INCLUDING CONSTRAINTS as well.

The patch already _only_ does constraint(unique) indexes:

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

so I am just suggesting it do that always for INCLUDING CONSTRAINTS,
with a notice as you suggest.

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Added to TODO:

o Have WITH CONSTRAINTS also create constraint indexes
http://archives.postgresql.org/pgsql...4/msg00149.php


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

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Hi,

On 4/10/07, Bruce Momjian <bruce@momjian.us> wrote:
>
>
> Added to TODO:
>
> o Have WITH CONSTRAINTS also create constraint indexes
>
> http://archives.postgresql.org/pgsql...4/msg00149.php


Trevor's patch does add unique/primary indexes. This would mean that we have
to remove the syntax support for "INCLUDING INDEXES" and just add code to
the existing WITH CONSTRAINTs code path from his patch.

Is there something else and hence we have the above TODO?

Regards,
Nikhils

--
EnterpriseDB http://www.enterprisedb.com

NikhilS wrote:
> Hi,
>
> On 4/10/07, Bruce Momjian <bruce@momjian.us> wrote:
> >
> >
> > Added to TODO:
> >
> > o Have WITH CONSTRAINTS also create constraint indexes
> >
> > http://archives.postgresql.org/pgsql...4/msg00149.php
>
>
> Trevor's patch does add unique/primary indexes. This would mean that we have
> to remove the syntax support for "INCLUDING INDEXES" and just add code to
> the existing WITH CONSTRAINTs code path from his patch.

That is all that is required.

> Is there something else and hence we have the above TODO?

If someone wants to work on this item and submit it, we can review it
for 8.3, but if not, it waits until 8.4.

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

---------------------------(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 wrote:
> NikhilS wrote:
>
>> Hi,
>>
>> On 4/10/07, Bruce Momjian <bruce@momjian.us> wrote:
>>
>>> Added to TODO:
>>>
>>> o Have WITH CONSTRAINTS also create constraint indexes
>>>
>>> http://archives.postgresql.org/pgsql...4/msg00149.php
>>>
>> Trevor's patch does add unique/primary indexes. This would mean that we have
>> to remove the syntax support for "INCLUDING INDEXES" and just add code to
>> the existing WITH CONSTRAINTs code path from his patch.
>>
>
> That is all that is required.
>
>
>> Is there something else and hence we have the above TODO?
>>
>
> If someone wants to work on this item and submit it, we can review it
> for 8.3, but if not, it waits until 8.4.
>
>
I've updated my patch to merge the INDEXES behavior implemented into the
CONSTRAINTS option, and restore the current error triggered when you try
to use the INDEXES option. Attached is the updated patch.

I didn't remove the INDEXES syntax, just undocumented it again and put
the error it raised back in. It seems like an implementation of copying
all of the indexes could still use that syntax.

Thank you for all the comments,
-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 12 Apr 2007 17:54:49 -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,1431 ----
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)
+ {
+ 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);
+ }
+ }
}

/*
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 12 Apr 2007 17:54:49 -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 12 Apr 2007 17:54:49 -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,642 ----
INSERT INTO inhg VALUES ('foo');
DROP TABLE inhg;
CREATE TABLE inhg (x text, LIKE inhx INCLUDING CONSTRAINTS, y text); /* Copies constraints */
+ 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 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 12 Apr 2007 17:54:50 -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


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings