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: NikhilS <nikkhils(at)gmail(dot)com>, pgsql-patches(at)postgresql(dot)org
Subject: Re: CREATE TABLE LIKE INCLUDING INDEXES support
Date: 2007-04-27 01:12:08
Message-ID: 200704270112.l3R1C9522574@momjian.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-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 &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. +

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2007-04-27 01:14:17
Subject: Re: [PATCHES] Reviewers Guide to Deferred Transactions/TransactionGuarantee
Previous:From: Bruce MomjianDate: 2007-04-27 01:05:51
Subject: Re: [HACKERS] CIC and deadlocks

pgsql-patches by date

Next:From: Bruce MomjianDate: 2007-04-27 01:14:17
Subject: Re: [PATCHES] Reviewers Guide to Deferred Transactions/TransactionGuarantee
Previous:From: Bruce MomjianDate: 2007-04-27 01:06:28
Subject: Re: UPDATE using sub selects

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