Skip site navigation (1) Skip section navigation (2)

Re: CREATE TABLE LIKE INCLUDING INDEXES support

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Trevor Hardcastle <chizu(at)spicious(dot)com>
Cc: pgsql-patches(at)postgresql(dot)org
Subject: Re: CREATE TABLE LIKE INCLUDING INDEXES support
Date: 2007-04-05 03:08:15
Message-ID: 200704050308.l3538FF06436@momjian.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-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(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. +

In response to

Responses

pgsql-hackers by date

Next:From: Gregory StarkDate: 2007-04-05 03:25:52
Subject: Re: CREATE TABLE LIKE INCLUDING INDEXES support
Previous:From: Alvaro HerreraDate: 2007-04-05 02:47:45
Subject: Re: autovacuum multiworkers, patch 5

pgsql-patches by date

Next:From: Gregory StarkDate: 2007-04-05 03:25:52
Subject: Re: CREATE TABLE LIKE INCLUDING INDEXES support
Previous:From: Alvaro HerreraDate: 2007-04-05 02:47:45
Subject: Re: autovacuum multiworkers, patch 5

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group