Hi,

Please find attached the WIP version 1 of the auto partitioning patch. There
was discussion on this a while back on -hackers at:
http://archives.postgresql.org/pgsql-hackers/2007-03/msg00375.php

Please note that this patch tries to automate the activities that currently
are carried out manually. It does nothing fancy beyond that for now. There
were a lot of good suggestions, I have noted them down but for now I have
tried to stick to the initial goal of automating existing steps for
providing partitioning.

Things that this patch does:

i) Handle new syntax to provide partitioning:

CREATE TABLE tabname (
    ...
 ) PARTITION BY
  RANGE(ColId)
| LIST(ColId)
(
PARTITION partition_name CHECK(...),
PARTITION partition_name CHECK(...)
 ...
);

ii) Create master table.
iii) Create children tables based on the number of partitions specified and
make them inherit from the master table.

The following things are TODOs:

iv) Auto generate rules using the checks mentioned for the partitions, to
handle INSERTs/DELETEs/UPDATEs to navigate them to the appropriate child.
Note that checks specified directly on the master table will get inherited
automatically.
v) Based on the PRIMARY, UNIQUE information specified, pass it on to the
children tables.
vi) [stretch goal] Support HASH partitions

Will try to complete the above mentioned TODOs as soon as is possible.

Comments, feedback appreciated.

Thanks and Regards,
Nikhils
--

EnterpriseDB               http://www.enterprisedb.com
Index: src/backend/commands/tablecmds.c
===================================================================
RCS file: /repositories/postgreshome/cvs/pgsql/src/backend/commands/tablecmds.c,v
retrieving revision 1.218
diff -c -r1.218 tablecmds.c
*** src/backend/commands/tablecmds.c	19 Mar 2007 23:38:29 -0000	1.218
--- src/backend/commands/tablecmds.c	30 Mar 2007 06:31:37 -0000
***************
*** 6864,6866 ****
--- 6864,6945 ----
  		}
  	}
  }
+ 
+ /* 
+  * ------------------------------------------------------------------------------
+  *		DefinePartitions
+  *		Create new partitions. They end up inheriting from the parent
+  *		relation.
+  *		Once they have been created, rules need to be assigned to the parent to
+  *		provide the UPDATEs/INSERTs/DELETEs to percolate down to the children
+  * 		Callers expect this function to end with CommandCounterIncrement if it
+  * 		makes any changes.
+  * ------------------------------------------------------------------------------
+  */
+ void
+ DefinePartitions(CreateStmt *stmt)
+ {
+ 	CreateStmt		*childStmt;
+ 	RangeVar 		inr;
+ 	Oid 			childOid;
+ 	PartitionAttrs	*partAttr;
+ 
+ 	if (stmt->partAttr == NULL) 
+ 		return;
+ 
+ 	Assert(IsA(stmt->partAttr, PartitionAttrs));
+ 	partAttr = (PartitionAttrs *)(stmt->partAttr);
+ 
+ 	/*
+ 	 * All the partitions will inherit from the parent, set the parent in the
+ 	 * inhRelations structure
+ 	 */
+ 	inr = *stmt->relation;
+ 
+ 
+ 	/* 
+ 	 * Create the children tables. The parser has already made sure that we
+ 	 * have atleast one partition in the list
+ 	 */
+ 	if (partAttr->partFunc == PART_LIST || partAttr->partFunc == PART_RANGE)
+ 	{
+ 		List *partitionList = partAttr->partitions;
+ 		ListCell *temp_part; 
+ 
+ 		Assert(list_length(partitionList) > 0);
+ 		if (list_length(partitionList) > 0) 
+ 		{
+ 			foreach(temp_part, partitionList)
+ 			{
+ 				Partition *temp_partition = lfirst(temp_part);
+ 				/*
+ 				 * Create a working copy for each child
+ 				 */
+ 				childStmt = (CreateStmt *)copyObject((void *)stmt);
+ 
+ 				/*
+ 				 * Child has to use all columns from the parent, otherwise we will get
+ 				 * unnecessary merging columns notices as part of the
+ 				 * DefineRelation 
+ 				 */
+ 				childStmt->tableElts = NIL;
+ 
+ 				childStmt->inhRelations = lappend(NULL, &inr);
+ 				childStmt->relation->relname = temp_partition->partName->relname;
+ 				childOid = DefineRelation(childStmt, RELKIND_RELATION);
+ 			}
+ 		}
+ 	}
+ 	else
+ 		ereport(ERROR,
+ 				(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ 				 errmsg("Invalid PARTITION type specified")));
+ 
+ 	/*
+ 	 *TODO: Add logic to create rules on the parent table
+ 	 */
+ 	/*
+ 	 * Make the changes carried out so far visible
+ 	 */
+ 	CommandCounterIncrement();
+ }
Index: src/backend/nodes/copyfuncs.c
===================================================================
RCS file: /repositories/postgreshome/cvs/pgsql/src/backend/nodes/copyfuncs.c,v
retrieving revision 1.372
diff -c -r1.372 copyfuncs.c
*** src/backend/nodes/copyfuncs.c	27 Mar 2007 23:21:09 -0000	1.372
--- src/backend/nodes/copyfuncs.c	30 Mar 2007 06:31:37 -0000
***************
*** 2070,2075 ****
--- 2070,2078 ----
  	COPY_NODE_FIELD(options);
  	COPY_SCALAR_FIELD(oncommit);
  	COPY_STRING_FIELD(tablespacename);
+ 	/*
+ 	 * There is no need to copy partAttr as of now
+ 	 */
  
  	return newnode;
  }
Index: src/backend/parser/analyze.c
===================================================================
RCS file: /repositories/postgreshome/cvs/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	30 Mar 2007 06:31:37 -0000
***************
*** 95,100 ****
--- 95,101 ----
  	List	   *alist;			/* "after list" of things to do after creating
  								 * the table */
  	IndexStmt  *pkey;			/* PRIMARY KEY index, if any */
+     PartitionAttrs *partAttr; 	/* partitioning related info */
  } CreateStmtContext;
  
  typedef struct
***************
*** 136,141 ****
--- 137,143 ----
  					   CreateStmtContext *cxt,
  					   bool skipValidation,
  					   bool isAddConstraint);
+ static void transformPartitionData(ParseState *pstate, CreateStmtContext *cxt);
  static void applyColumnNames(List *dst, List *src);
  static void getSetColTypes(ParseState *pstate, Node *node,
  			   List **colTypes, List **colTypmods);
***************
*** 860,865 ****
--- 862,868 ----
  	cxt.alist = NIL;
  	cxt.pkey = NULL;
  	cxt.hasoids = interpretOidsOption(stmt->options);
+ 	cxt.partAttr = (PartitionAttrs *)stmt->partAttr;
  
  	/*
  	 * Run through each primary element in the table creation clause. Separate
***************
*** 919,924 ****
--- 922,932 ----
  	transformFKConstraints(pstate, &cxt, true, false);
  
  	/*
+ 	 * Postprocess partition related information
+ 	 */
+ 	transformPartitionData(pstate, &cxt);
+ 
+ 	/*
  	 * Output results.
  	 */
  	q = makeNode(Query);
***************
*** 1365,1370 ****
--- 1373,1426 ----
  	heap_close(relation, NoLock);
  }
  
+ /*
+  * transformPartitionData:
+  * If partitioning is specified, carry out checks on the supplied create stmt
+  */
+ static void
+ transformPartitionData(ParseState *pstate, CreateStmtContext *cxt)
+ {
+ 	PartitionAttrs 	*partAttr = (PartitionAttrs *)cxt->partAttr;
+ 	ListCell 		*lc;
+ 
+     if (partAttr == NULL)
+ 		return;
+ 
+     /* Foreign key constraints not supported with partitioning */
+     if(cxt->fkconstraints)
+         ereport(ERROR,
+ 			(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ 			 errmsg("FOREIGN KEY constraints not supported with partitioning")));
+     
+ 	/*
+ 	 * Check if the primary key column is the one on which partitioning
+ 	 * is being done
+ 	 */
+ 
+ 	foreach(lc, cxt->ixconstraints)
+ 	{
+ 		Constraint  *constraint = lfirst(lc);
+ 		Assert(IsA(constraint, Constraint));
+ 		if(constraint->contype == CONSTR_PRIMARY ||
+ 		   constraint->contype == CONSTR_UNIQUE) 
+ 		{
+ 			if (constraint->keys->length > 1) 
+ 			{
+ 				ereport(ERROR,
+ 					(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ 					 errmsg("PRIMARY/UNIQUE KEY with partitioning can specify"
+ 							" single attribute only")));
+ 			}
+ 			if(strcmp(partAttr->colName, 
+ 					strVal(lfirst(list_head(constraint->keys)))) != 0)
+ 				ereport(ERROR,
+ 					(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ 					 errmsg("PRIMARY/UNIQUE KEY not the same as"
+ 					 " the specified partitioning key (%s)", partAttr->colName)));
+          }
+      }
+ }
+ 
  static void
  transformIndexConstraints(ParseState *pstate, CreateStmtContext *cxt)
  {
Index: src/backend/parser/gram.y
===================================================================
RCS file: /repositories/postgreshome/cvs/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.584
diff -c -r2.584 gram.y
*** src/backend/parser/gram.y	26 Mar 2007 16:58:39 -0000	2.584
--- src/backend/parser/gram.y	30 Mar 2007 06:31:37 -0000
***************
*** 146,151 ****
--- 146,153 ----
  
  	InsertStmt			*istmt;
  	VariableSetStmt		*vsetstmt;
+ 
+ 	PartitionFunction partFunc;
  }
  
  %type <node>	stmt schema_stmt
***************
*** 355,360 ****
--- 357,365 ----
  %type <ival>	document_or_content
  %type <boolean> xml_whitespace_option
  
+ %type <list>	PartitionList 
+ %type <node> PartitioningData OptPartition Partition_elem
+ %type <partFunc> Partitioning_function
  
  /*
   * If you make any token changes, update the keyword table in
***************
*** 403,409 ****
  	KEY
  
  	LANCOMPILER LANGUAGE LARGE_P LAST_P LEADING LEAST LEFT LEVEL
! 	LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION
  	LOCK_P LOGIN_P
  
  	MATCH MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE
--- 408,414 ----
  	KEY
  
  	LANCOMPILER LANGUAGE LARGE_P LAST_P LEADING LEAST LEFT LEVEL
! 	LIKE LIMIT LIST LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION
  	LOCK_P LOGIN_P
  
  	MATCH MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE
***************
*** 415,427 ****
  	OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OR
  	ORDER OUT_P OUTER_P OVERLAPS OVERLAY OWNED OWNER
  
! 	PARTIAL PASSWORD PLACING POSITION
  	PRECISION PRESERVE PREPARE PREPARED PRIMARY
  	PRIOR PRIVILEGES PROCEDURAL PROCEDURE
  
  	QUOTE
  
! 	READ REAL REASSIGN RECHECK REFERENCES REINDEX RELATIVE_P RELEASE RENAME
  	REPEATABLE REPLACE REPLICA RESET RESTART RESTRICT RETURNING RETURNS REVOKE
  	RIGHT ROLE ROLLBACK ROW ROWS RULE
  
--- 420,432 ----
  	OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OR
  	ORDER OUT_P OUTER_P OVERLAPS OVERLAY OWNED OWNER
  
! 	PARTIAL PARTITION PARTITIONS PASSWORD PLACING POSITION
  	PRECISION PRESERVE PREPARE PREPARED PRIMARY
  	PRIOR PRIVILEGES PROCEDURAL PROCEDURE
  
  	QUOTE
  
! 	RANGE READ REAL REASSIGN RECHECK REFERENCES REINDEX RELATIVE_P RELEASE RENAME
  	REPEATABLE REPLACE REPLICA RESET RESTART RESTRICT RETURNING RETURNS REVOKE
  	RIGHT ROLE ROLLBACK ROW ROWS RULE
  
***************
*** 1826,1843 ****
   *
   *****************************************************************************/
  
! CreateStmt:	CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
! 			OptInherit OptWith OnCommitOption OptTableSpace
  				{
  					CreateStmt *n = makeNode(CreateStmt);
  					$4->istemp = $2;
  					n->relation = $4;
  					n->tableElts = $6;
! 					n->inhRelations = $8;
  					n->constraints = NIL;
! 					n->options = $9;
! 					n->oncommit = $10;
! 					n->tablespacename = $11;
  					$$ = (Node *)n;
  				}
  		| CREATE OptTemp TABLE qualified_name OF qualified_name
--- 1831,1849 ----
   *
   *****************************************************************************/
  
! CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
! 			   OptPartition OptInherit OptWith OnCommitOption OptTableSpace
  				{
  					CreateStmt *n = makeNode(CreateStmt);
  					$4->istemp = $2;
  					n->relation = $4;
  					n->tableElts = $6;
!                     n->partAttr = $8;
! 					n->inhRelations = $9;
  					n->constraints = NIL;
! 					n->options = $10;
! 					n->oncommit = $11;
! 					n->tablespacename = $12;
  					$$ = (Node *)n;
  				}
  		| CREATE OptTemp TABLE qualified_name OF qualified_name
***************
*** 1855,1864 ****
--- 1861,1915 ----
  					n->options = $10;
  					n->oncommit = $11;
  					n->tablespacename = $12;
+                     n->partAttr = NULL;
  					$$ = (Node *)n;
  				}
  		;
  
+ OptPartition: PARTITION BY PartitioningData { $$ = $3; }
+ 		| {$$ = NULL;}
+     	;
+ 
+ PartitioningData: Partitioning_function '(' ColId ')' '(' PartitionList ')'
+ 				{
+ 					PartitionAttrs *partData = makeNode(PartitionAttrs);
+ 					partData->partFunc = $1;
+ 					partData->numberOfPartitions = 0;
+ 					partData->colName = $3;
+ 					partData->partitions = $6;
+ 					$$ = (Node *)partData;
+ 				}
+ 		;
+ 
+ Partitioning_function: LIST {$$ = PART_LIST;}
+     	| RANGE {$$ = PART_RANGE;}
+     	;
+ 
+ PartitionList: Partition_elem						{ $$ = list_make1($1); }
+ 			| PartitionList ',' Partition_elem		{ $$ = lappend($1, $3); }
+ 			
+ Partition_elem:	PARTITION qualified_name CHECK '(' a_expr ')' 
+ 				{
+ 
+ 					Partition *partition = makeNode(Partition);
+ 					Constraint *constraint = NULL;
+ 
+ 					partition->partName = $2;
+ 
+ 					/* Make the constraint node */
+ 					constraint = makeNode(Constraint);
+ 					constraint->contype = CONSTR_CHECK;
+ 					constraint->name = NULL;
+ 					constraint->raw_expr = $5;
+ 					constraint->cooked_expr = NULL;
+ 					constraint->keys = NULL;
+ 					constraint->indexspace = NULL;
+ 
+ 					partition->partitionCheck = constraint;
+ 					$$ = (Node *)partition;
+ 				}
+     	;
+ 
  /*
   * Redundancy here is needed to avoid shift/reduce conflicts,
   * since TEMP is not a reserved word.  See also OptTempTableName.
***************
*** 9005,9014 ****
--- 9056,9067 ----
  			| JOIN
  			| LEFT
  			| LIKE
+ 			| LIST
  			| NATURAL
  			| NOTNULL
  			| OUTER_P
  			| OVERLAPS
+ 			| RANGE
  			| RIGHT
  			| SIMILAR
  			| VERBOSE
***************
*** 9076,9081 ****
--- 9129,9136 ----
  			| ONLY
  			| OR
  			| ORDER
+             | PARTITION
+             | PARTITIONS
  			| PLACING
  			| PRIMARY
  			| REFERENCES
Index: src/backend/parser/keywords.c
===================================================================
RCS file: /repositories/postgreshome/cvs/pgsql/src/backend/parser/keywords.c,v
retrieving revision 1.185
diff -c -r1.185 keywords.c
*** src/backend/parser/keywords.c	19 Mar 2007 23:38:29 -0000	1.185
--- src/backend/parser/keywords.c	30 Mar 2007 06:31:37 -0000
***************
*** 208,213 ****
--- 208,214 ----
  	{"level", LEVEL},
  	{"like", LIKE},
  	{"limit", LIMIT},
+ 	{"list", LIST},
  	{"listen", LISTEN},
  	{"load", LOAD},
  	{"local", LOCAL},
***************
*** 266,271 ****
--- 267,274 ----
  	{"owned", OWNED},
  	{"owner", OWNER},
  	{"partial", PARTIAL},
+ 	{"partition", PARTITION},
+ 	{"partitions", PARTITIONS},
  	{"password", PASSWORD},
  	{"placing", PLACING},
  	{"position", POSITION},
***************
*** 279,284 ****
--- 282,288 ----
  	{"procedural", PROCEDURAL},
  	{"procedure", PROCEDURE},
  	{"quote", QUOTE},
+ 	{"range", RANGE},
  	{"read", READ},
  	{"real", REAL},
  	{"reassign", REASSIGN},
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /repositories/postgreshome/cvs/pgsql/src/backend/tcop/utility.c,v
retrieving revision 1.275
diff -c -r1.275 utility.c
*** src/backend/tcop/utility.c	26 Mar 2007 16:58:39 -0000	1.275
--- src/backend/tcop/utility.c	30 Mar 2007 06:31:38 -0000
***************
*** 537,547 ****
  				relOid = DefineRelation((CreateStmt *) parsetree,
  										RELKIND_RELATION);
  
  				/*
  				 * Let AlterTableCreateToastTable decide if this one needs a
  				 * secondary relation too.
  				 */
- 				CommandCounterIncrement();
  				AlterTableCreateToastTable(relOid);
  			}
  			break;
--- 537,552 ----
  				relOid = DefineRelation((CreateStmt *) parsetree,
  										RELKIND_RELATION);
  
+ 				CommandCounterIncrement();
+ 				/*
+ 				 * Let DefinePartitions decide if partitions need to be created
+ 				 * for this table
+ 				 */
+ 				DefinePartitions((CreateStmt *)parsetree);
  				/*
  				 * Let AlterTableCreateToastTable decide if this one needs a
  				 * secondary relation too.
  				 */
  				AlterTableCreateToastTable(relOid);
  			}
  			break;
Index: src/include/commands/tablecmds.h
===================================================================
RCS file: /repositories/postgreshome/cvs/pgsql/src/include/commands/tablecmds.h,v
retrieving revision 1.32
diff -c -r1.32 tablecmds.h
*** src/include/commands/tablecmds.h	5 Jan 2007 22:19:54 -0000	1.32
--- src/include/commands/tablecmds.h	30 Mar 2007 06:31:38 -0000
***************
*** 58,61 ****
--- 58,62 ----
  							  SubTransactionId mySubid,
  							  SubTransactionId parentSubid);
  
+ extern void	DefinePartitions(CreateStmt *stmt);
  #endif   /* TABLECMDS_H */
Index: src/include/nodes/nodes.h
===================================================================
RCS file: /repositories/postgreshome/cvs/pgsql/src/include/nodes/nodes.h,v
retrieving revision 1.197
diff -c -r1.197 nodes.h
*** src/include/nodes/nodes.h	27 Mar 2007 23:21:12 -0000	1.197
--- src/include/nodes/nodes.h	30 Mar 2007 06:31:38 -0000
***************
*** 341,346 ****
--- 341,348 ----
  	T_LockingClause,
  	T_RowMarkClause,
  	T_XmlSerialize,
+     T_PartitionAttrs,
+     T_Partition,
  
  	/*
  	 * TAGS FOR RANDOM OTHER STUFF
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /repositories/postgreshome/cvs/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.343
diff -c -r1.343 parsenodes.h
*** src/include/nodes/parsenodes.h	19 Mar 2007 23:38:32 -0000	1.343
--- src/include/nodes/parsenodes.h	30 Mar 2007 06:31:38 -0000
***************
*** 1064,1069 ****
--- 1064,1070 ----
  	List	   *options;		/* options from WITH clause */
  	OnCommitAction oncommit;	/* what do we do at COMMIT? */
  	char	   *tablespacename; /* table space to use, or NULL */
+ 	Node	   *partAttr;		/* partitioning related information */
  } CreateStmt;
  
  /* ----------
***************
*** 1115,1120 ****
--- 1116,1145 ----
  								 * constraints; NULL for default */
  } Constraint;
  
+ /*
+  * Enumeration which defines the various possibilites for the partitioning
+  * function.
+  */
+ typedef enum PartitionFunction {
+ 	PART_UNDEFINED,
+ 	PART_LIST,
+ 	PART_RANGE,
+ } PartitionFunction;
+ 
+ typedef struct Partition {
+ 	NodeTag		type;
+ 	RangeVar	*partName;          /* Name of the partition */
+ 	Constraint	*partitionCheck;    /* per partition constraint */
+ } Partition;
+ 
+ typedef struct PartitionAttrs {
+ 	NodeTag				type;
+ 	int     			numberOfPartitions; /* Number of partitions */
+ 	List    			*partitions;  		/* The list of partitions */
+ 	PartitionFunction 	partFunc; 			/* type of partition */
+ 	const char 			*colName;           /* partition column name */
+ } PartitionAttrs;
+ 
  /* ----------
   * Definitions for FOREIGN KEY constraints in CreateStmt
   *
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Reply via email to