Subject: [PATCH] Automatic HASH and LIST partition creation

From: Anastasia Lubennikova <[email-masked]>
Date: Tue, 25 Aug 2020 13:14:29 +0300

On 14.07.2020 00:11, Anastasia Lubennikova wrote:
> On 06.07.2020 13:45, Anastasia Lubennikova wrote:
>> The previous discussion of automatic partition creation [1] has 
>> addressed static and dynamic creation of partitions and ended up with 
>> several syntax proposals.
>> In this thread, I want to continue this work.
>>
>> ...
>> [1] 
>> https://www.postgresql.org/message-id/flat/alpine.DEB.2.21.1907150711080.22273%40lancre
>
> Syntax proposal v2, that takes into account received feedback.
>
> CREATE TABLE numbers(int number)
> PARTITION BY partition_method (list_of_columns)
> USING (partition_desc)
>
> where partition_desc is:
>
> MODULUS n
> | VALUES IN (value_list), [DEFAULT PARTITION part_name]
> | START ([datatype] 'start_value')
>    END ([datatype] 'end_value')
>    EVERY (partition_step), [DEFAULT PARTITION part_name]
>
> where partition_step is:
> [datatype] [number | INTERVAL] 'interval_value'
>
> It is less wordy than the previous version. It uses a free keyword option
> style. It covers static partitioning for all methods, default 
> partition for
> list and range methods, and can be extended to implement dynamic 
> partitioning
> for range partitions.
>
> [1] 
> https://wiki.postgresql.org/wiki/Declarative_partitioning_improvements#Other_DBMS
> [2] 
> https://wiki.postgresql.org/wiki/Declarative_partitioning_improvements#Proposal_.28is_subject_to_change.29
>
Here is the patch for automated HASH and LIST partitioning, that 
implements proposed syntax.

Range partitioning is more complicated. It will require new support 
function to calculate bounds, new catalog attribute to store them and so 
on. So I want to start small and implement automated range partitioning 
in a separate patch later.

1) Syntax

New syntax is heavily based on Greenplum syntax for automated 
partitioning with one change. Keyword "USING", that was suggested above, 
causes shift/reduce conflict with "USING method" syntax of a table 
access method. It seems that Greenplum folks will face this problem later.

I stick to CONFIGURATION as an existing keyword that makes sense in this 
context.
Any better ideas are welcome.

Thus, current version is:

CREATE TABLE table_name (attrs)
PARTITION BY partition_method (list_of_columns)
CONFIGURATION (partition_desc)

where partition_desc is:

MODULUS n
| VALUES IN (value_list) [DEFAULT PARTITION part_name]

This syntax can be easily extended for range partitioning as well.

2) Implementation

PartitionBoundAutoSpec is a new part of PartitionSpec, that contains 
information needed to generate partition bounds.

For HASH and LIST automatic partition creation, transformation happens 
during parse analysis of CREATE TABLE statement.
transformPartitionAutoCreate() calculates bounds and generates 
statements to create partition tables.

Partitions are named in a format: $tablename_$partnum. One can use post 
create hook to rename relations.

For LIST partition one can also define a default partition.

3) TODO

The patch lacks documentation, because I expect some details may change 
during discussion. Other than that, the feature is ready for review.

Regards

-- 
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

commit ede0c9d8f13509915ee1db724f7bcabc0365ecd5
Author: anastasia <[email-masked]>
Date:   Tue Aug 25 03:34:15 2020 +0300

    Auto generated HASH and LIST partitions.
    
    New syntax:
    
    CREATE TABLE tbl_hash (i int) PARTITION BY HASH (i)
    CONFIGURATION (modulus 3);
    
    CREATE TABLE tbl_list (i int) PARTITION BY LIST (i)
    CONFIGURATION (values in (1, 2), (3, 4) DEFAULT PARTITION tbl_default);
---
 src/backend/nodes/copyfuncs.c              |  17 ++++
 src/backend/nodes/equalfuncs.c             |  17 ++++
 src/backend/nodes/outfuncs.c               |  16 ++++
 src/backend/nodes/readfuncs.c              |  15 ++++
 src/backend/parser/gram.y                  |  82 ++++++++++++++++-
 src/backend/parser/parse_utilcmd.c         | 138 +++++++++++++++++++++++++++++
 src/include/nodes/nodes.h                  |   1 +
 src/include/nodes/parsenodes.h             |  23 +++++
 src/include/partitioning/partdefs.h        |   2 +
 src/test/regress/expected/create_table.out |  37 ++++++++
 src/test/regress/sql/create_table.sql      |  23 +++++
 11 files changed, 369 insertions(+), 2 deletions(-)

diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 89c409de66..cb537bce3a 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -4629,6 +4629,7 @@ _copyPartitionSpec(const PartitionSpec *from)
 
 	COPY_STRING_FIELD(strategy);
 	COPY_NODE_FIELD(partParams);
+	COPY_NODE_FIELD(autopart);
 	COPY_LOCATION_FIELD(location);
 
 	return newnode;
@@ -4651,6 +4652,19 @@ _copyPartitionBoundSpec(const PartitionBoundSpec *from)
 	return newnode;
 }
 
+static PartitionBoundAutoSpec *
+_copyPartitionBoundAutoSpec(const PartitionBoundAutoSpec *from)
+{
+	PartitionBoundAutoSpec *newnode = makeNode(PartitionBoundAutoSpec);
+
+	COPY_SCALAR_FIELD(strategy);
+	COPY_SCALAR_FIELD(modulus);
+	COPY_NODE_FIELD(listdatumsList);
+	COPY_NODE_FIELD(default_partition_rv);
+
+	return newnode;
+}
+
 static PartitionRangeDatum *
 _copyPartitionRangeDatum(const PartitionRangeDatum *from)
 {
@@ -5700,6 +5714,9 @@ copyObjectImpl(const void *from)
 		case T_PartitionBoundSpec:
 			retval = _copyPartitionBoundSpec(from);
 			break;
+		case T_PartitionBoundAutoSpec:
+			retval = _copyPartitionBoundAutoSpec(from);
+			break;
 		case T_PartitionRangeDatum:
 			retval = _copyPartitionRangeDatum(from);
 			break;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index e3f33c40be..4e9d388534 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2898,6 +2898,7 @@ _equalPartitionSpec(const PartitionSpec *a, const PartitionSpec *b)
 	COMPARE_STRING_FIELD(strategy);
 	COMPARE_NODE_FIELD(partParams);
 	COMPARE_LOCATION_FIELD(location);
+	COMPARE_NODE_FIELD(autopart);
 
 	return true;
 }
@@ -2917,6 +2918,19 @@ _equalPartitionBoundSpec(const PartitionBoundSpec *a, const PartitionBoundSpec *
 	return true;
 }
 
+static bool
+_equalPartitionBoundAutoSpec(const PartitionBoundAutoSpec *a,
+							 const PartitionBoundAutoSpec *b)
+{
+	COMPARE_SCALAR_FIELD(strategy);
+	COMPARE_SCALAR_FIELD(modulus);
+	COMPARE_NODE_FIELD(listdatumsList);
+	COMPARE_NODE_FIELD(default_partition_rv);
+
+	return true;
+}
+
+
 static bool
 _equalPartitionRangeDatum(const PartitionRangeDatum *a, const PartitionRangeDatum *b)
 {
@@ -3752,6 +3766,9 @@ equal(const void *a, const void *b)
 		case T_PartitionBoundSpec:
 			retval = _equalPartitionBoundSpec(a, b);
 			break;
+		case T_PartitionBoundAutoSpec:
+			retval = _equalPartitionBoundAutoSpec(a, b);
+			break;
 		case T_PartitionRangeDatum:
 			retval = _equalPartitionRangeDatum(a, b);
 			break;
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index e2f177515d..4fd12523d8 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -3643,6 +3643,7 @@ _outPartitionSpec(StringInfo str, const PartitionSpec *node)
 
 	WRITE_STRING_FIELD(strategy);
 	WRITE_NODE_FIELD(partParams);
+	WRITE_NODE_FIELD(autopart);
 	WRITE_LOCATION_FIELD(location);
 }
 
@@ -3661,6 +3662,18 @@ _outPartitionBoundSpec(StringInfo str, const PartitionBoundSpec *node)
 	WRITE_LOCATION_FIELD(location);
 }
 
+static void
+_outPartitionBoundAutoSpec(StringInfo str, const PartitionBoundAutoSpec *node)
+{
+	WRITE_NODE_TYPE("PARTITIONBOUNDAUTOSPEC");
+
+	WRITE_CHAR_FIELD(strategy);
+	WRITE_INT_FIELD(modulus);
+	WRITE_NODE_FIELD(listdatumsList);
+	WRITE_NODE_FIELD(default_partition_rv);
+
+}
+
 static void
 _outPartitionRangeDatum(StringInfo str, const PartitionRangeDatum *node)
 {
@@ -4334,6 +4347,9 @@ outNode(StringInfo str, const void *obj)
 			case T_PartitionBoundSpec:
 				_outPartitionBoundSpec(str, obj);
 				break;
+			case T_PartitionBoundAutoSpec:
+				_outPartitionBoundAutoSpec(str, obj);
+				break;
 			case T_PartitionRangeDatum:
 				_outPartitionRangeDatum(str, obj);
 				break;
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 42050ab719..703b413f93 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -2602,6 +2602,19 @@ _readPartitionBoundSpec(void)
 	READ_DONE();
 }
 
+static PartitionBoundAutoSpec *
+_readPartitionBoundAutoSpec(void)
+{
+	READ_LOCALS(PartitionBoundAutoSpec);
+
+	READ_CHAR_FIELD(strategy);
+	READ_INT_FIELD(modulus);
+	READ_NODE_FIELD(listdatumsList);
+	READ_NODE_FIELD(default_partition_rv);
+
+	READ_DONE();
+}
+
 /*
  * _readPartitionRangeDatum
  */
@@ -2880,6 +2893,8 @@ parseNodeString(void)
 		return_value = _readPartitionBoundSpec();
 	else if (MATCH("PARTITIONRANGEDATUM", 19))
 		return_value = _readPartitionRangeDatum();
+	else if (MATCH("PARTITIONBOUNDAUTOSPEC", 22))
+		return_value = _readPartitionBoundAutoSpec();
 	else
 	{
 		elog(ERROR, "badly formatted node string \"%.32s\"...", token);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index dbb47d4982..ba96fafa94 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -249,6 +249,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	PartitionElem		*partelem;
 	PartitionSpec		*partspec;
 	PartitionBoundSpec	*partboundspec;
+	PartitionBoundAutoSpec	*partboundautospec;
 	RoleSpec			*rolespec;
 	struct SelectLimit	*selectlimit;
 }
@@ -599,6 +600,9 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <list>		hash_partbound
 %type <defelt>		hash_partbound_elem
 
+%type <partboundautospec> OptPartitionBoundAutoSpec values_in_clause p_desc
+%type <range>		opt_default_partition_clause
+
 /*
  * Non-keyword token types.  These are hard-wired into the "flex" lexer.
  * They must be listed first so that their numeric codes do not depend on
@@ -3907,14 +3911,14 @@ OptPartitionSpec: PartitionSpec	{ $$ = $1; }
 			| /*EMPTY*/			{ $$ = NULL; }
 		;
 
-PartitionSpec: PARTITION BY ColId '(' part_params ')'
+PartitionSpec: PARTITION BY ColId '(' part_params ')' OptPartitionBoundAutoSpec
 				{
 					PartitionSpec *n = makeNode(PartitionSpec);
 
 					n->strategy = $3;
 					n->partParams = $5;
 					n->location = @1;
-
+					n->autopart = (Node *) $7;
 					$$ = n;
 				}
 		;
@@ -3958,6 +3962,80 @@ part_elem: ColId opt_collate opt_class
 				}
 		;
 
+OptPartitionBoundAutoSpec:
+	CONFIGURATION '(' p_desc ')'
+	{
+		$$ = $3;
+	}
+	| /*EMPTY*/			{ $$ = NULL; }
+	;
+
+p_desc:
+	hash_partbound
+	{
+		ListCell   *lc;
+		PartitionBoundAutoSpec *n = makeNode(PartitionBoundAutoSpec);
+
+		n->modulus = -1;
+
+		foreach (lc, $1)
+		{
+			DefElem    *opt = lfirst_node(DefElem, lc);
+
+			if (strcmp(opt->defname, "modulus") == 0)
+			{
+				n->strategy = PARTITION_STRATEGY_HASH;
+				if (n->modulus != -1)
+					ereport(ERROR,
+							(errcode(ERRCODE_DUPLICATE_OBJECT),
+								errmsg("modulus for hash partition provided more than once"),
+								parser_errposition(opt->location)));
+				n->modulus = defGetInt32(opt);
+			}
+			else
+				ereport(ERROR,
+						(errcode(ERRCODE_SYNTAX_ERROR),
+							errmsg("unrecognized auto partition bound specification \"%s\"",
+								opt->defname),
+							parser_errposition(opt->location)));
+		}
+
+		$$ = (PartitionBoundAutoSpec *) n;
+	}
+	| values_in_clause opt_default_partition_clause
+	{
+		PartitionBoundAutoSpec *n = $1;
+		n->default_partition_rv = $2;
+		$$ = (PartitionBoundAutoSpec *) n;
+	}
+	;
+
+values_in_clause:
+	VALUES IN_P '(' expr_list ')'
+	{
+		PartitionBoundAutoSpec *n = makeNode(PartitionBoundAutoSpec);
+		n->strategy = PARTITION_STRATEGY_LIST;
+		n->listdatumsList = list_make1($4);
+		$$ = (PartitionBoundAutoSpec *) n;
+	}
+	| values_in_clause ',' '(' expr_list ')'
+	{
+			PartitionBoundAutoSpec *n = (PartitionBoundAutoSpec *) $1;
+			n->strategy = PARTITION_STRATEGY_LIST;
+			n->listdatumsList = lappend(n->listdatumsList, $4);
+			$$ = (PartitionBoundAutoSpec *) n;
+	}
+	;
+
+opt_default_partition_clause:
+	DEFAULT PARTITION qualified_name
+	{
+		$$ = $3;
+	}
+	| /* EMPTY */
+		{ $$ = NULL; }
+	;
+
 table_access_method_clause:
 			USING name							{ $$ = $2; }
 			| /*EMPTY*/							{ $$ = NULL; }
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 6c49554def..686aabf630 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -75,6 +75,7 @@
 /* State shared by transformCreateStmt and its subroutines */
 typedef struct
 {
+	CreateStmt *stmt;			/* initial statement */
 	ParseState *pstate;			/* overall parser state */
 	const char *stmtType;		/* "CREATE [FOREIGN] TABLE" or "ALTER TABLE" */
 	RangeVar   *relation;		/* relation to create */
@@ -145,6 +146,7 @@ static Const *transformPartitionBoundValue(ParseState *pstate, Node *con,
 										   const char *colName, Oid colType, int32 colTypmod,
 										   Oid partCollation);
 
+static void transformPartitionAutoCreate(CreateStmtContext *cxt, PartitionSpec* partspec);
 
 /*
  * transformCreateStmt -
@@ -235,6 +237,7 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
 		cxt.stmtType = "CREATE TABLE";
 		cxt.isforeign = false;
 	}
+	cxt.stmt = stmt;
 	cxt.relation = stmt->relation;
 	cxt.rel = NULL;
 	cxt.inhRelations = stmt->inhRelations;
@@ -324,6 +327,10 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
 	 */
 	transformExtendedStatistics(&cxt);
 
+	/* Process partition definitions */
+	if (stmt->partspec && stmt->partspec->autopart)
+		transformPartitionAutoCreate(&cxt, stmt->partspec);
+
 	/*
 	 * Output results.
 	 */
@@ -4259,3 +4266,134 @@ transformPartitionBoundValue(ParseState *pstate, Node *val,
 
 	return (Const *) value;
 }
+
+
+/*
+ * Transform configuration into a set of partition bounds.
+ * Generate extra statements to create partition tables.
+ */
+static void
+transformPartitionAutoCreate(CreateStmtContext *cxt, PartitionSpec* partspec)
+{
+	CreateStmt  *part;
+	List	    *partlist = NIL;
+	ListCell    *lc;
+	int			i = 0;
+	PartitionBoundAutoSpec *bound = (PartitionBoundAutoSpec *) partspec->autopart;
+
+	elog(DEBUG1, "transformPartitionAutoCreate \n %s \n ",  nodeToString(bound));
+
+	/*
+	 * Generate regular partbounds based on autopart rule.
+	 * and form create table statements from these partbounds
+	 */
+	if (pg_strcasecmp(partspec->strategy, "hash") == 0)
+	{
+		if (bound->strategy != PARTITION_STRATEGY_HASH)
+			ereport(ERROR,
+			(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+				errmsg("invalid bound specification for a hash partition"),
+				parser_errposition(cxt->pstate, exprLocation((Node *) partspec))));
+
+		for (i = 0; i < bound->modulus; i++)
+		{
+			char *part_relname;
+
+			/*
+			 * Generate partition name in the format:
+			 * $relname_$partnum
+			 *
+			 * TODO: Add checks on relname length.
+			 */
+			part_relname = psprintf("%s_%d", cxt->relation->relname, i);
+
+			part = copyObject(cxt->stmt);
+
+			part->relation = makeRangeVar(cxt->relation->schemaname,
+								part_relname, cxt->relation->location);
+			/* set table as a parent */
+			part->inhRelations = lappend(part->inhRelations, cxt->relation);
+
+			/* child table is not partitioned */
+			part->partspec = NULL;
+
+			/* Actual partbound generation is here */
+			part->partbound = makeNode(PartitionBoundSpec);
+			part->partbound->strategy = PARTITION_STRATEGY_HASH;
+			part->partbound->modulus = bound->modulus;
+			part->partbound->remainder = i;
+			part->partbound->is_default = false;
+
+			elog(DEBUG1,"stransformPartitionAutoCreate HASH i %d MODULUS %d \n %s\n",
+						 i, bound->modulus, nodeToString(part));
+
+			partlist = lappend(partlist, part);
+		}
+	}
+	else if (pg_strcasecmp(partspec->strategy, "list") == 0)
+	{
+
+		int n_list_parts = list_length(bound->listdatumsList);
+
+		if (bound->strategy != PARTITION_STRATEGY_LIST)
+			ereport(ERROR,
+			(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+				errmsg("invalid bound specification for a list partition"),
+				parser_errposition(cxt->pstate, exprLocation((Node *) partspec))));
+
+		for (i = 0; i < n_list_parts; i++)
+		{
+			char *part_relname;
+			List *listdatums = (List *)
+					list_nth(bound->listdatumsList, i);
+
+			part_relname = psprintf("%s_%d", cxt->relation->relname, i);
+
+			part = copyObject(cxt->stmt);
+
+			part->relation = makeRangeVar(cxt->relation->schemaname,
+								part_relname, cxt->relation->location);
+			/* set table as a parent */
+			part->inhRelations = lappend(part->inhRelations, cxt->relation);
+
+			/* child table is not partitioned */
+			part->partspec = NULL;
+
+			/* Actual partbound generation is here */
+			part->partbound = makeNode(PartitionBoundSpec);
+			part->partbound->strategy = PARTITION_STRATEGY_LIST;
+			part->partbound->listdatums = list_copy(listdatums);
+			part->partbound->is_default = false;
+
+			elog(DEBUG1,"Debug transformPartitionAutoCreate LIST i %d \n %s\n",
+						 i, nodeToString(part));
+
+			partlist = lappend(partlist, part);
+		}
+
+		if (bound->default_partition_rv)
+		{
+			part = copyObject(cxt->stmt);
+
+			part->relation = bound->default_partition_rv;
+			/* set table as a parent */
+			part->inhRelations = lappend(part->inhRelations, cxt->relation);
+
+			/* child table is not partitioned */
+			part->partspec = NULL;
+
+			part->partbound = makeNode(PartitionBoundSpec);
+			part->partbound->strategy = PARTITION_STRATEGY_LIST;
+			part->partbound->listdatums = NULL;
+			part->partbound->is_default = true;
+
+			elog(DEBUG1,"Debug transformPartitionAutoCreate LIST default partition \n %s\n",
+						 nodeToString(part));
+
+			partlist = lappend(partlist, part);
+		}
+	}
+
+	/* Add statemets to create each partition after we create parent table */
+	cxt->alist = list_concat(cxt->alist, partlist);
+}
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 381d84b4e4..ac6fcc029e 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -478,6 +478,7 @@ typedef enum NodeTag
 	T_PartitionElem,
 	T_PartitionSpec,
 	T_PartitionBoundSpec,
+	T_PartitionBoundAutoSpec,
 	T_PartitionRangeDatum,
 	T_PartitionCmd,
 	T_VacuumRelation,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 47d4c07306..d44dc9983a 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -794,6 +794,9 @@ typedef struct PartitionSpec
 								 * 'range') */
 	List	   *partParams;		/* List of PartitionElems */
 	int			location;		/* token location, or -1 if unknown */
+
+	Node		*autopart;	/* PartitionBoundAutoSpec -
+							 * spec to generate bounds automatically */
 } PartitionSpec;
 
 /* Internal codes for partitioning strategies */
@@ -828,6 +831,26 @@ struct PartitionBoundSpec
 	int			location;		/* token location, or -1 if unknown */
 };
 
+/*
+ * PartitionBoundAutoSpec - a partition bound specification
+ * 							for auto generated partitions.
+ *
+ * This represents the rule of generating partition bounds
+ */
+struct PartitionBoundAutoSpec
+{
+	NodeTag		type;
+
+	char		strategy;		/* see PARTITION_STRATEGY codes above */
+
+	/* Partitioning info for HASH strategy: */
+	int			modulus;
+
+	/* Partitioning info for LIST strategy: */
+	List	   *listdatumsList;		/* List of lists of Consts (or A_Consts in raw tree) */
+	RangeVar   *default_partition_rv; /* Name of default list partition */
+};
+
 /*
  * PartitionRangeDatum - one of the values in a range partition bound
  *
diff --git a/src/include/partitioning/partdefs.h b/src/include/partitioning/partdefs.h
index 6414e2c116..25ecfbd1de 100644
--- a/src/include/partitioning/partdefs.h
+++ b/src/include/partitioning/partdefs.h
@@ -19,6 +19,8 @@ typedef struct PartitionKeyData *PartitionKey;
 
 typedef struct PartitionBoundSpec PartitionBoundSpec;
 
+typedef struct PartitionBoundAutoSpec PartitionBoundAutoSpec;
+
 typedef struct PartitionDescData *PartitionDesc;
 
 typedef struct PartitionDirectoryData *PartitionDirectory;
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 1c72f23bc9..3047ed9007 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -1283,3 +1283,40 @@ Indexes:
     "part_column_drop_1_10_expr_idx1" btree ((d = 2))
 
 drop table part_column_drop;
+-- Auto generated partitions
+-- must fail because of wrong configuration
+CREATE TABLE tbl_hash_fail (i int) PARTITION BY HASH (i)
+CONFIGURATION (values in (1, 2), (3, 4) default partition tbl_default);
+ERROR:  invalid bound specification for a hash partition
+LINE 1: CREATE TABLE tbl_hash_fail (i int) PARTITION BY HASH (i)
+                                           ^
+-- must fail because of wrong configuration
+CREATE TABLE tbl_list_fail (i int) PARTITION BY LIST (i)
+CONFIGURATION (values in (1, 2), (1, 3));
+ERROR:  partition "tbl_list_fail_1" would overlap partition "tbl_list_fail_0"
+CREATE TABLE tbl_list (i int) PARTITION BY LIST (i)
+CONFIGURATION (values in (1, 2), (3, 4) default partition tbl_default);
+\d+ tbl_list
+                           Partitioned table "public.tbl_list"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ i      | integer |           |          |         | plain   |              | 
+Partition key: LIST (i)
+Partitions: tbl_list_0 FOR VALUES IN (1, 2),
+            tbl_list_1 FOR VALUES IN (3, 4),
+            tbl_default DEFAULT
+
+CREATE TABLE tbl_hash (i int) PARTITION BY HASH (i)
+CONFIGURATION (modulus 3);
+\d+ tbl_hash
+                           Partitioned table "public.tbl_hash"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ i      | integer |           |          |         | plain   |              | 
+Partition key: HASH (i)
+Partitions: tbl_hash_0 FOR VALUES WITH (modulus 3, remainder 0),
+            tbl_hash_1 FOR VALUES WITH (modulus 3, remainder 1),
+            tbl_hash_2 FOR VALUES WITH (modulus 3, remainder 2)
+
+DROP TABLE tbl_list;
+DROP TABLE tbl_hash;
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index 9b1adcb8ad..c82fca0a9a 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -971,3 +971,26 @@ create table part_column_drop_1_10 partition of
 \d part_column_drop
 \d part_column_drop_1_10
 drop table part_column_drop;
+
+-- Auto generated partitions
+
+-- must fail because of wrong configuration
+CREATE TABLE tbl_hash_fail (i int) PARTITION BY HASH (i)
+CONFIGURATION (values in (1, 2), (3, 4) default partition tbl_default);
+
+-- must fail because of wrong configuration
+CREATE TABLE tbl_list_fail (i int) PARTITION BY LIST (i)
+CONFIGURATION (values in (1, 2), (1, 3));
+
+CREATE TABLE tbl_list (i int) PARTITION BY LIST (i)
+CONFIGURATION (values in (1, 2), (3, 4) default partition tbl_default);
+
+\d+ tbl_list
+
+CREATE TABLE tbl_hash (i int) PARTITION BY HASH (i)
+CONFIGURATION (modulus 3);
+
+\d+ tbl_hash
+
+DROP TABLE tbl_list;
+DROP TABLE tbl_hash;

---

From: Pavel Borisov <[email-masked]>
Date: Tue, 8 Sep 2020 18:03:39 +0400

> during discussion. Other than that, the feature is ready for review.
Hi, hackers!

---

From: Anastasia Lubennikova <[email-masked]>
Date: Mon, 14 Sep 2020 14:38:56 +0300

On 08.09.2020 17:03, Pavel Borisov wrote:
>
>     The patch lacks documentation, because I expect some details may
>     change during discussion. Other than that, the feature is ready
>     for review.
>
> Hi, hackers!
>
> From what I've read I see there is much interest in automatic 
> partitions creation. (Overall discussion on the topic is partitioned 
> into two threads: (1) 
> https://www.postgresql.org/message-id/alpine.DEB.2.21.1907150711080.22273%40lancre and 
> (2) 
> https://www.postgresql.org/message-id/flat/[email-masked] 
> (current thread) )
>
> There were many syntax proposals and finally, there is a patch 
> realizing one of them. So I'd like to review it.
>
> The syntax proposed in the patch seems good enough for me and is in 
> accordance with one of the proposals in the discussion. Maybe I'd 
> prefer using the word AUTOMATICALLY/AUTO instead of CONFIGURATION with 
> explicit meaning that using this syntax we'd get already 
> (automatically) created partitions and don't need to create them 
> manually, as in the existing state of postgresql declarative 
> partitioning.
>
> CREATE TABLE tbl (iint) PARTITION BY HASH (i) AUTOMATICALLY (MODULUS 3); (partitions are created automatically)
> vs
> CREATE TABLE tbl (iint) PARTITION BY HASH (i); (partitions should be created manually by use of PARTITION OF)
> CREATE TABLE tbl (i char) PARTITION BY LIST (i) AUTOMATICALLY (VALUES 
> IN ('a', 'b'), ('c', 'd'), ('e','f') DEFAULTPARTITION tbl_default);
> vs
> CREATE TABLE tbl (ichar) PARTITION BY LIST (i); (partitions should be created manually by use of PARTITION OF)
>
> I think this syntax can also be extended later with adding automatic 
> creation of RANGE partitions, with IMMEDIATE/DEFERRED for 
> dynamic/on-demand automatic partition creation, and with SUBPARTITION 
> possibility.
>
> But I don't have a strong preference for the word AUTOMATICALLY, 
> moreover I saw opposition to using AUTO at the top of the discussion. 
> I suppose we can go with the existing CONFIGURATION word.

I agree that 'AUTOMATICALLY' keyword is more specific and probably less 
confusing for users. I've picked 'CONFIGURATION' simply because it is an 
already existing keyword. It would like to hear other opinions on that.

> If compare with existing declarative partitions, I think automatic 
> creation simplifies the process for the end-user and  I'd vote for its 
> committing into Postgres. The patch is short and clean in code style. 
> It has enough comments Tests covering the new functionality are 
> included. Yet it doesn't have documentation and I'd suppose it's worth 
> adding it. Even if there will be syntax changes, I hope they will not 
> be more than the replacement of several words. Current syntax is 
> described in the text of a patch.
>

Fair enough. New patch contains a documentation draft. While writing it, 
I also noticed, that the syntax, introduced in the patch differs from 
greenpulm one. For now, list partitioning clause doesn't support 
'PARTITION name' part, that is supported in greenplum. I don't think 
that we aim for 100% compatibility here. Still, the ability to provide 
table names is probably a good optional feature, especially for list 
partitions.

What do you think?

> The patch applies cleanly and installcheck-world is passed.
>
> Some minor things:
>
> I've got a compiler warning:
> parse_utilcmd.c:4280:15: warning: unused variable 'lc' [-Wunused-variable]

Fixed. This was also caught by cfbot. This version should pass it clean.

>
> When the number of partitions is over the maximum value of int32 the 
> output shows a generic syntax error. I don't think it is very 
> important as it is not the case someone will make deliberately, but 
> maybe it's better to output something like "Partitions number is more 
> than the maximum supported value"
> create table test (i int, t text) partition by hash (i) configuration 
> (modulus 888888888888);
> ERROR:  syntax error at or near "888888888888"

This value is not a valid int32 number, thus parser throws the error 
before we have a chance to handle it more gracefully.

>
> One more piece of nitpicking. Probably we can go just with a mention 
> in documentation.
> create table test (i int, t text) partition by hash (i) configuration 
> (modulus 8888);
> ERROR:  out of shared memory
> HINT:  You might need to increase max_locks_per_transaction.
>
Well, it looks like a legit error, when we try to lock a lot of objects 
in one transaction. I will double check if we don't release a lock 
somewhere.

Do we need to restrict the number of partitions, that can be created by 
this statement? With what number?  As far as I see, there is no such 
restriction for now, just a recommendation about performance issues. 
With automatic creation it becomes easier to mess with it.

Probably, it's enough to mention it in documentation and rely on users 
common sense.

> Typo:
Fixed.

> Overall I see the patch almost ready for commit and I'd like to meet 
> this functionality in v14.
I also hope that this patch will make it to v14, but for now, I don't 
see a consensus on the syntax and some details, so I wouldn't rush.

Besides, it definitely needs more testing. I haven't thoroughly tested 
following cases yet:
- how triggers and constraints are propagated to partitions;
- how does it handle some tricky clauses in list partitioning expr_list;
and so on.

Also, there is an open question about partition naming. Currently, the 
patch implements dummy %tbl_%partnum name generation, which is far from 
user-friendly. I think we must provide some hook or trigger function to 
rename partitions after they were created.

commit 08c5f8b35c4ffcaf09b8189cd8d0dc27ce76d715
Author: anastasia <[email-masked]>
Date:   Mon Sep 14 11:34:42 2020 +0300

        New syntax:

    
        With documentation draft.

diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 087cad184c..ff9a7eda09 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -29,6 +29,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
 ] )
 [ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ]
 [ PARTITION BY { RANGE | LIST | HASH } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ... ] ) ]
+[ CONFIGURATION ( <replaceable class="parameter">partition_bound_auto_spec</replaceable> ) ]
 [ USING <replaceable class="parameter">method</replaceable> ]
 [ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) | WITHOUT OIDS ]
 [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
@@ -41,6 +42,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
     [, ... ]
 ) ]
 [ PARTITION BY { RANGE | LIST | HASH } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ... ] ) ]
+[ CONFIGURATION ( <replaceable class="parameter">partition_bound_auto_spec</replaceable> ) ]
 [ USING <replaceable class="parameter">method</replaceable> ]
 [ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) | WITHOUT OIDS ]
 [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
@@ -53,6 +55,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
     [, ... ]
 ) ] { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT }
 [ PARTITION BY { RANGE | LIST | HASH } ( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ... ] ) ]
+[ CONFIGURATION ( <replaceable class="parameter">partition_bound_auto_spec</replaceable> ) ]
 [ USING <replaceable class="parameter">method</replaceable> ]
 [ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) | WITHOUT OIDS ]
 [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
@@ -96,6 +99,11 @@ FROM ( { <replaceable class="parameter">partition_bound_expr</replaceable> | MIN
   TO ( { <replaceable class="parameter">partition_bound_expr</replaceable> | MINVALUE | MAXVALUE } [, ...] ) |
 WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REMAINDER <replaceable class="parameter">numeric_literal</replaceable> )
 
+<phrase>and <replaceable class="parameter">partition_bound_auto_spec</replaceable> is:</phrase>
+
+VALUES IN ( <replaceable class="parameter">partition_bound_expr</replaceable> [, ...] ), [( <replaceable class="parameter">partition_bound_expr</replaceable> [, ...] )] [, ...] [DEFAULT PARTITION <replaceable class="parameter">defailt_part_name</replaceable>]
+MODULUS <replaceable class="parameter">numeric_literal</replaceable>
+
 <phrase><replaceable class="parameter">index_parameters</replaceable> in <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and <literal>EXCLUDE</literal> constraints are:</phrase>
 
 [ INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ]
@@ -383,6 +391,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
       however, you can define these constraints on individual partitions.
      </para>
 
+     <para>
+      Range and list partitioning also support automatic creation of partitions
+      with an optional <literal>CONFIGURATION</literal> clause.
+    </para>
+
      <para>
       See <xref linkend="ddl-partitioning"/> for more discussion on table
       partitioning.
@@ -391,6 +404,38 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>CONFIGURATION ( <replaceable class="parameter">partition_bound_auto_spec</replaceable> ) ] </literal></term>
+    <listitem>
+     <para>
+      The optional <literal>CONFIGURATION</literal> clause used together
+      with <literal>PARTITION BY</literal> specifies a rule of generating bounds
+      for partitions of the partitioned table. All partitions are created automatically
+      along with the parent table.
+  
+      Any indexes, constraints and user-defined row-level triggers that exist
+      in the parent table are cloned on the new partitions.
+     </para>
+    
+     <para>
+      The <replaceable class="parameter">partition_bound_auto_spec</replaceable>
+      must correspond to the partitioning method and partition key of the
+      parent table, and must not overlap with any existing partition of that
+      parent.  The form with <literal>VALUES IN</literal> is used for list partitioning 
+      and the form with <literal>MODULUS</literal> is used for hash partitioning.
+      List partitioning can also provide a default partition using 
+      <literal>DEFAULT PARTITION</literal>.
+     </para>
+
+     <para>
+     Automatic range partitioning is not supported yet.
+     </para>
+
+
+
+    </listitem>
+   </varlistentry>
+
    <varlistentry id="sql-createtable-partition">
     <term><literal>PARTITION OF <replaceable class="parameter">parent_table</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT }</literal></term>
     <listitem>
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 0409a40b82..6893fa5495 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -4628,6 +4628,7 @@ _copyPartitionSpec(const PartitionSpec *from)

 	return newnode;
@@ -4650,6 +4651,19 @@ _copyPartitionBoundSpec(const PartitionBoundSpec *from)
 	return newnode;
 }
 
@@ -5699,6 +5713,9 @@ copyObjectImpl(const void *from)
index e2d1b987bf..ccba2471de 100644
@@ -2897,6 +2897,7 @@ _equalPartitionSpec(const PartitionSpec *a, const PartitionSpec *b)
 
 	return true;
 }
@@ -2916,6 +2917,19 @@ _equalPartitionBoundSpec(const PartitionBoundSpec *a, const PartitionBoundSpec *
 	return true;
 }
 
+static bool
@@ -3751,6 +3765,9 @@ equal(const void *a, const void *b)

 
+static void
 
index 9f47745ee2..a1f9db30d2 100644

 
 					n->strategy = $3;
 					n->partParams = $5;
 					n->location = @1;
-
 
index ec944371dd..a22bbb8ebc 100644

 /*
  * transformCreateStmt -
 
+	/* Process partition definitions */
+	if (stmt->partspec && stmt->partspec->autopart)
+		transformPartitionAutoCreate(&cxt, stmt->partspec);
+
 	/*
 	 * Output results.
 	 */
@@ -4258,3 +4265,133 @@ transformPartitionBoundValue(ParseState *pstate, Node *val,
 
 	return (Const *) value;
 }
+
+
+/*
+	/* Add statements to create each partition after we create parent table */
index e83329fd6d..271632f0fe 100644

+/*

---

From: Michael Paquier <[email-masked]>
Date: Thu, 24 Sep 2020 12:27:38 +0900

> Fixed. This was also caught by cfbot. This version should pass it clean.

Please note that regression tests are failing, because of 6b2c4e59.
--
Michael

---

From: Anastasia Lubennikova <[email-masked]>
Date: Thu, 24 Sep 2020 23:40:46 +0300

> Please note that regression tests are failing, because of 6b2c4e59.

Thank you. Updated patch is attached.

Open issues for review:
- new syntax;
- generation of partition names;
- overall patch review and testing, especially with complex partitioning 
clauses.

commit faa5805b839effd9d8220eff787fb0995276c370
Author: anastasia <[email-masked]>
Date:   Mon Sep 14 11:34:42 2020 +0300

        New syntax:

    

 
 
+     <para>
 
+   <varlistentry>

 	return newnode;

 	return true;
 }
 
+static bool

 
+static void
 
index 17653ef3a7..aca93b8c96 100644
@@ -601,6 +602,9 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 
@@ -3904,14 +3908,14 @@ OptPartitionSpec: PartitionSpec	{ $$ = $1; }

 					n->strategy = $3;
 					n->partParams = $5;
 					n->location = @1;
-
+					n->autopart = (Node *) $7;
 					$$ = n;
 				}
 		;
@@ -3955,6 +3959,80 @@ part_elem: ColId opt_collate opt_class
 				}
 		;
 
index 164312d60e..b9ba712e36 100644

 /*
  * transformCreateStmt -
 
+	/* Process partition definitions */
+	if (stmt->partspec && stmt->partspec->autopart)
+		transformPartitionAutoCreate(&cxt, stmt->partspec);
+
 	/*
 	 * Output results.
 	 */
@@ -4279,3 +4286,133 @@ transformPartitionBoundValue(ParseState *pstate, Node *val,
 
 	return (Const *) value;
 }
+
+
+/*
index 60c2f45466..ff7903a0dd 100644
@@ -795,6 +795,9 @@ typedef struct PartitionSpec
 
 /* Internal codes for partitioning strategies */
@@ -829,6 +832,26 @@ struct PartitionBoundSpec
 	int			location;		/* token location, or -1 if unknown */
 };
 
+/*

 
 typedef struct PartitionDirectoryData *PartitionDirectory;
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 41dce69cc4..1776721b7b 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -1313,3 +1313,42 @@ Indexes:
     "part_column_drop_1_10_expr_idx1" btree ((d = 2))
 
+LINE 2: CONFIGURATION (values in (1, 2), (1, 3));

---

From: Rahila Syed <[email-masked]>
Date: Thu, 1 Oct 2020 01:28:28 +0530

Hi Anastasia,

I tested the syntax with some basic commands and it works fine, regression
tests also pass.

Couple of comments:
1. The syntax used omits the { IMMEDIATE | DEFERRED} keywords suggested in
the earlier discussions. I think it is intuitive to include IMMEDIATE with
the current implementation
so that the syntax can be extended with a  DEFERRED clause in future for
dynamic partitions.

>   CREATE TABLE tbl_lst (i int) PARTITION BY LIST (i)

2. One suggestion for generation of partition names is to append a unique
id to
avoid conflicts.

3. Probably, here you mean to write list and hash instead of range and list
as
per the current state.

     <para>
>      Range and list partitioning also support automatic creation of
> partitions
>       with an optional <literal>CONFIGURATION</literal> clause.
>     </para>

4. Typo in default_part_name

+VALUES IN ( <replaceable
> class="parameter">partition_bound_expr</replaceable> [, ...] ), [(
> <replaceable class="parameter">partition_bound_expr</replaceable> [, ...]
> )] [, ...] [DEFAULT PARTITION <replaceable
> class="parameter">defailt_part_name</replaceable>]

Thank you,
Rahila Syed

---

From: Anastasia Lubennikova <[email-masked]>
Date: Thu, 1 Oct 2020 19:02:04 +0300

On 30.09.2020 22:58, Rahila Syed wrote:
> Hi Anastasia,
>
> I tested the syntax with some basic commands and it works fine, 
> regression tests also pass.
>
Thank you for your review.
> the earlier discussions. I think it is intuitive to include IMMEDIATE 
> with the current implementation
> so that the syntax can be extended with a  DEFERRED clause in future 
> for dynamic partitions.
>
After some consideration, I decided that we don't actually need to 
introduce IMMEDIATE | DEFERRED keyword. For hash and list partitions it 
will always be immediate, as the number of partitions cannot change 
after we initially set it. For range partitions, on the contrary, it 
doesn't make much sense to make partitions immediately, because in many 
use-cases one bound will be open.

> 2. One suggestion for generation of partition names is to append a 
> unique id to
> avoid conflicts.

Can you please give an example of such a conflict? I agree that current 
naming scheme is far from perfect, but I think that 'tablename'_partnum 
provides unique name for each partition.

>
> 3. Probably, here you mean to write list and hash instead of range and 
> list as
> per the current state.
>
>          <para>
>          Range and list partitioning also support automatic creation
>     of partitions
>     <replaceable class="parameter">partition_bound_expr</replaceable>
>     [, ...] )] [, ...] [DEFAULT PARTITION <replaceable
Yes, you're right. I will fix these typos in next version of the patch.

---

From: Rahila Syed <[email-masked]>
Date: Mon, 5 Oct 2020 12:06:49 +0530

Hi,

Couple of comments:
> will always be immediate, as the number of partitions cannot change after
> we initially set it. For range partitions, on the contrary, it doesn't make
> much sense to make partitions immediately, because in many use-cases one
> bound will be open.
>
>
As per discussions on this thread:
https://www.postgresql.org/message-id/flat/alpine.DEB.2.21.1907150711080.22273%40lancre
DEFERRED clause refers to creating partitions on the fly, while the data is
being inserted.
The number of partitions and partition bounds can be the same as specified
initially
during partitioned table creation, but the actual creation of
partitions can be deferred.
This seems like a potential extension to statically created partitions even
in the case of
hash and list partitions, as it won't involve moving any existing data.

avoid conflicts.
> Sorry for not being clear earlier, I mean the partition name
'tablename_partnum' can conflict with any existing table name.
As per current impemetation, if I do the following it results in the table
name conflict.

postgres=# create table tbl_test_5_1(i int);
CREATE TABLE
postgres=# CREATE TABLE tbl_test_5 (i int) PARTITION BY LIST((tbl_test_5))

                                CONFIGURATION (values in
('(1)'::tbl_test_5), ('(3)'::tbl_test_5) default partition tbl_default_5);
ERROR:  relation "tbl_test_5_1" already exists

Thank you,
Rahila Syed

---

From: Pavel Borisov <[email-masked]>
Date: Mon, 5 Oct 2020 11:53:27 +0400

> ERROR:  relation "tbl_test_5_1" already exists

Basically, it's the same thing when you try to create two tables with the
same name. It is not specific to partition creation and common for every
case that using any defaults, they can conflict with something existing.
And in this case this conflict is explicitly processes as I see from output
message.

In fact in PG there are other places when names are done in default way
e.g. in aggregates regression test it is not surprise to find in PG13:

explain (costs off)
  select min(f1), max(f1) from minmaxtest;
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Result
   InitPlan 1 (returns $0)
     ->  Limit
           ->  Merge Append
                 Sort Key: minmaxtest.f1
                 ->  Index Only Scan using minmaxtesti on minmaxtest
minmaxtest_1
                       Index Cond: (f1 IS NOT NULL)
                 ->  Index Only Scan using minmaxtest1i on minmaxtest1
minmaxtest_2
                       Index Cond: (f1 IS NOT NULL)
                 ->  Index Only Scan Backward using minmaxtest2i on
minmaxtest2 minmaxtest_3
                       Index Cond: (f1 IS NOT NULL)
                 ->  Index Only Scan using minmaxtest3i on minmaxtest3
minmaxtest_4
   InitPlan 2 (returns $1)
     ->  Limit
           ->  Merge Append
                 Sort Key: minmaxtest_5.f1 DESC
                 ->  Index Only Scan Backward using minmaxtesti on
minmaxtest minmaxtest_6
                       Index Cond: (f1 IS NOT NULL)
                 ->  Index Only Scan Backward using minmaxtest1i on
minmaxtest1 minmaxtest_7
                       Index Cond: (f1 IS NOT NULL)
                 ->  Index Only Scan using minmaxtest2i on minmaxtest2
minmaxtest_8
                       Index Cond: (f1 IS NOT NULL)
                 ->  Index Only Scan Backward using minmaxtest3i on
minmaxtest3 minmaxtest_9

where minmaxtest_<number> are the temporary relations
and minmaxtest<number> are real partition names (last naming is unrelated
to first)

Overall I don't see much trouble in any form of automatic naming. But there
may be a convenience to provide fixed user-specified prefix to partition
names.

Thank you,
-- 
Best regards,
Pavel Borisov

Postgres Professional: http://postgrespro.com <http://www.postgrespro.com>

---

From: Pavel Borisov <[email-masked]>
Date: Tue, 6 Oct 2020 01:21:01 +0400

Hi, hackers!
I added some extra tests for different cases of use of automatic partition
creation.
v3-0002 can be applied on top of the original v2 patch for correct work
with some corner cases with constraints included in this test.

As for immediate/deferred I think that now only available now is immediate,
so using word IMMEDIATE seems a little bit redundant to me. We may
introduce this word together with adding DEFERRED option. However, my
opinion is not in strong opposition to both options. Оther opinions are
very much welcome!

-- 
Best regards,
Pavel Borisov

---

From: Anastasia Lubennikova <[email-masked]>
Date: Tue, 6 Oct 2020 20:25:48 +0300

On 06.10.2020 00:21, Pavel Borisov wrote:
> Hi, hackers!
> I added some extra tests for different cases of use of automatic 
> partition creation.
> v3-0002 can be applied on top of the original v2 patch for correct 
> work with some corner cases with constraints included in this test.
>
Thank you for the tests. I've added them and the fix into the patch.

I also noticed, that some table parameters, such as persistence were not 
promoted to auto generated partitions. This is fixed now. The test cases 
for temp and unlogged auto partitioned tables are updated respectively.
Besides, I slightly refactored the code and fixed documentation typos, 
that were reported by Rahila.

With my recent changes, one test statement, that you've added as 
failing, works.

CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
(VALUES IN ('1' collate "POSIX"));

It simply ignores collate POSIX part and creates a table with following 
structure:

                        Partitioned table "public.list_parted_fail"
  Column |  Type   | Collation | Nullable | Default | Storage | Stats 
target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
  a      | integer |           |          |         | plain 
|              |
Partition key: LIST (a)
Partitions: list_parted_fail_0 FOR VALUES IN (1)

Do you think that it is a bug? For now, I removed this statement from 
tests just to calm down the CI.

commit 4a387cfbd43e93b2c2e363307fb9c9ca53c3f56e
Author: anastasia <[email-masked]>
Date:   Tue Oct 6 20:23:22 2020 +0300

            New syntax:

    
            With tests and documentation draft

index 28f844071b..4501e81bb5 100644
 
+<phrase>and <replaceable class="parameter">partition_bound_auto_spec</replaceable> is:</phrase>
+
+VALUES IN ( <replaceable class="parameter">partition_bound_expr</replaceable> [, ...] ), [( <replaceable class="parameter">partition_bound_expr</replaceable> [, ...] )] [, ...] [DEFAULT PARTITION <replaceable class="parameter">default_part_name</replaceable>]
+MODULUS <replaceable class="parameter">numeric_literal</replaceable>
+
 <phrase><replaceable class="parameter">index_parameters</replaceable> in <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and <literal>EXCLUDE</literal> constraints are:</phrase>

+     <para>
+      Hash and list partitioning also support automatic creation of partitions
+      with an optional <literal>CONFIGURATION</literal> clause.
+    </para>
+
      <para>
       See <xref linkend="ddl-partitioning"/> for more discussion on table
       partitioning.
@@ -391,6 +404,42 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+      in the parent table are cloned on the new partitions. When using this clause,
+      <literal> CREATE TABLE </literal> statement can only contain clauses, that are
+      applicable to both relation kinds: partitioned table and regular partition tables.
+      All tables created by the statement will use same parameters, such as
+      relation persistence.

 	return newnode;

 	return true;
 }
 
+static bool
index f0386480ab..95bf90a099 100644
@@ -3644,6 +3644,7 @@ _outPartitionSpec(StringInfo str, const PartitionSpec *node)

@@ -3662,6 +3663,18 @@ _outPartitionBoundSpec(StringInfo str, const PartitionBoundSpec *node)
 	WRITE_LOCATION_FIELD(location);
 }
 
+static void
@@ -4335,6 +4348,9 @@ outNode(StringInfo str, const void *obj)
 
index 0d101d8171..e655ff858c 100644

 
 					n->strategy = $3;
 					n->partParams = $5;
 					n->location = @1;
-
 
index 0dc03dd984..ba5c694cdc 100644
@@ -145,6 +146,8 @@ static Const *transformPartitionBoundValue(ParseState *pstate, Node *con,
 
+static CreateStmt* initAutoPartitionCreateStmt(CreateStmtContext *cxt, char *part_relname);
+static void transformPartitionAutoCreate(CreateStmtContext *cxt, PartitionSpec* partspec);
 
 /*
  * transformCreateStmt -
@@ -235,6 +238,7 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
 		cxt.stmtType = "CREATE TABLE";
 		cxt.isforeign = false;
 	}
+	cxt.stmt = stmt;
 	cxt.relation = stmt->relation;
 	cxt.rel = NULL;
 	cxt.inhRelations = stmt->inhRelations;
@@ -324,6 +328,10 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
 	 */
 	transformExtendedStatistics(&cxt);
 
+	/* Process partition definitions */
+	if (stmt->partspec && stmt->partspec->autopart)
+		transformPartitionAutoCreate(&cxt, stmt->partspec);
+
 	/*
 	 * Output results.
 	 */
@@ -4236,3 +4244,144 @@ transformPartitionBoundValue(ParseState *pstate, Node *val,
 
 	return (Const *) value;
 }
+
+
+/* init basic fields of auto generated partition */
+static CreateStmt*
+initAutoPartitionCreateStmt(CreateStmtContext *cxt, char *part_relname)
+{
+	CreateStmt  *part;
+
+	part = copyObject(cxt->stmt);
+
+	part->relation = makeRangeVar(cxt->relation->schemaname,
+						part_relname, cxt->relation->location);
+
+	/* inherit persistence from parent relation */
+	part->relation->relpersistence = cxt->relation->relpersistence;
+	/* set parent table as a parent */
+	part->inhRelations = lappend(part->inhRelations, cxt->relation);
+
+	/*
+	 * child table is not partitioned itself, at least now
+	 * while we do not support multilevel auto partitioning
+	 */
+	part->partspec = NULL;
+
+	/*
+	 * Partition doesn't need a list of column definitions and constraints.
+	 * They will be inherited from parent.
+	 */
+	part->tableElts = NIL;
+	part->constraints = NIL;
+
+	return part;
+}
+
+/*
+ * Transform configuration into a set of partition bounds.
+ * Generate extra statements to create partition tables.
+ */
+static void
+transformPartitionAutoCreate(CreateStmtContext *cxt, PartitionSpec* partspec)
+{
+	CreateStmt  *part;
+	List	    *partlist = NIL;
+	int			i = 0;
+	PartitionBoundAutoSpec *bound = (PartitionBoundAutoSpec *) partspec->autopart;
+
+	elog(DEBUG1, "transformPartitionAutoCreate \n %s \n ",  nodeToString(bound));
+
+	/*
+	 * Generate regular partbounds based on autopart rule.
+	 * and form create table statements from these partbounds
+	 */
+	if (pg_strcasecmp(partspec->strategy, "hash") == 0)
+	{
+		if (bound->strategy != PARTITION_STRATEGY_HASH)
+			ereport(ERROR,
+			(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+				errmsg("invalid bound specification for a hash partition"),
+				parser_errposition(cxt->pstate, exprLocation((Node *) partspec))));
+
+		for (i = 0; i < bound->modulus; i++)
+		{
+			char *part_relname;
+
+			/*
+			 * Generate partition name in the format:
+			 * $relname_$partnum
+			 * All checks of name validity will be made afterwards in DefineRelation()
+			 */
+			part_relname = psprintf("%s_%d", cxt->relation->relname, i);
+			part = initAutoPartitionCreateStmt(cxt, part_relname);
+
+			/* Actual partbound generation happens here */
+			part->partbound = makeNode(PartitionBoundSpec);
+			part->partbound->strategy = PARTITION_STRATEGY_HASH;
+			part->partbound->modulus = bound->modulus;
+			part->partbound->remainder = i;
+			part->partbound->is_default = false;
+
+			elog(DEBUG1,"stransformPartitionAutoCreate HASH i %d MODULUS %d \n %s\n",
+						 i, bound->modulus, nodeToString(part));
+
+			partlist = lappend(partlist, part);
+		}
+	}
+	else if (pg_strcasecmp(partspec->strategy, "list") == 0)
+	{
+
+		int n_list_parts = list_length(bound->listdatumsList);
+
+		if (bound->strategy != PARTITION_STRATEGY_LIST)
+			ereport(ERROR,
+			(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+				errmsg("invalid bound specification for a list partition"),
+				parser_errposition(cxt->pstate, exprLocation((Node *) partspec))));
+
+		for (i = 0; i < n_list_parts; i++)
+		{
+			char *part_relname;
+			List *listdatums = (List *)
+					list_nth(bound->listdatumsList, i);
+
+			part_relname = psprintf("%s_%d", cxt->relation->relname, i);
+			part = initAutoPartitionCreateStmt(cxt, part_relname);
+
+			/* Actual partbound generation happens here */
+			part->partbound = makeNode(PartitionBoundSpec);
+			part->partbound->strategy = PARTITION_STRATEGY_LIST;
+			part->partbound->listdatums = list_copy(listdatums);
+			part->partbound->is_default = false;
+
+			elog(DEBUG1,"Debug transformPartitionAutoCreate LIST i %d \n %s\n",
+						 i, nodeToString(part));
+
+			partlist = lappend(partlist, part);
+		}
+
+		if (bound->default_partition_rv)
+		{
+
+			char *part_relname = bound->default_partition_rv->relname;
+			part = initAutoPartitionCreateStmt(cxt, part_relname);
+
+			/* TODO: Should we use fields from default_partition_rv, other than relname? */
+
+			/* Actual partbound generation happens here */
+			part->partbound = makeNode(PartitionBoundSpec);
+			part->partbound->strategy = PARTITION_STRATEGY_LIST;
+			part->partbound->listdatums = NULL;
+			part->partbound->is_default = true;
+
+			elog(DEBUG1,"Debug transformPartitionAutoCreate LIST default partition \n %s\n",
+						 nodeToString(part));
+
+			partlist = lappend(partlist, part);
+		}
+	}
+
+	/* Add statements to create each partition after we create parent table */
+	cxt->alist = list_concat(cxt->alist, partlist);
+}
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 7ddd8c011b..4c6bd005ea 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -477,6 +477,7 @@ typedef enum NodeTag
 	T_PartitionElem,
 	T_PartitionSpec,
 	T_PartitionBoundSpec,
+	T_PartitionBoundAutoSpec,
 	T_PartitionRangeDatum,
 	T_PartitionCmd,
 	T_VacuumRelation,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 60c2f45466..ff7903a0dd 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -795,6 +795,9 @@ typedef struct PartitionSpec
 								 * 'range') */
 	List	   *partParams;		/* List of PartitionElems */
 	int			location;		/* token location, or -1 if unknown */
+
+	Node		*autopart;	/* PartitionBoundAutoSpec -
+							 * spec to generate bounds automatically */
 } PartitionSpec;

+/*

 
 typedef struct PartitionDirectoryData *PartitionDirectory;
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 1fc266dd65..2bca260195 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -1307,3 +1307,437 @@ Indexes:
     "part_column_drop_1_10_expr_idx1" btree ((d = 2))
 
 drop table part_column_drop;
+-- Auto generated partitions
+-- must fail because of wrong configuration
+CREATE TABLE tbl_hash_fail (i int) PARTITION BY HASH (i)
+CONFIGURATION (values in (1, 2), (3, 4) default partition tbl_default);
+ERROR:  invalid bound specification for a hash partition
+LINE 1: CREATE TABLE tbl_hash_fail (i int) PARTITION BY HASH (i)
+                                           ^
+-- must fail because of wrong configuration
+CREATE TABLE tbl_list_fail (i int) PARTITION BY LIST (i)
+CONFIGURATION (values in (1, 2), (1, 3));
+ERROR:  partition "tbl_list_fail_1" would overlap partition "tbl_list_fail_0"
+LINE 2: CONFIGURATION (values in (1, 2), (1, 3));
+                                          ^
+CREATE TABLE tbl_list (i int) PARTITION BY LIST (i)
+CONFIGURATION (values in (1, 2), (3, 4) default partition tbl_default);
+\d+ tbl_list
+                           Partitioned table "public.tbl_list"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ i      | integer |           |          |         | plain   |              | 
+Partition key: LIST (i)
+Partitions: tbl_list_0 FOR VALUES IN (1, 2),
+            tbl_list_1 FOR VALUES IN (3, 4),
+            tbl_default DEFAULT
+
+CREATE TABLE tbl_hash (i int) PARTITION BY HASH (i)
+CONFIGURATION (modulus 3);
+\d+ tbl_hash
+                           Partitioned table "public.tbl_hash"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ i      | integer |           |          |         | plain   |              | 
+Partition key: HASH (i)
+Partitions: tbl_hash_0 FOR VALUES WITH (modulus 3, remainder 0),
+            tbl_hash_1 FOR VALUES WITH (modulus 3, remainder 1),
+            tbl_hash_2 FOR VALUES WITH (modulus 3, remainder 2)
+
+DROP TABLE tbl_list;
+DROP TABLE tbl_hash;
+-- forbidden expressions for partition bound with list partitioned table
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (somename));
+ERROR:  cannot use column reference in partition bound expression
+LINE 2: (VALUES IN (somename));
+                    ^
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (somename.somename));
+ERROR:  cannot use column reference in partition bound expression
+LINE 2: (VALUES IN (somename.somename));
+                    ^
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (a));
+ERROR:  cannot use column reference in partition bound expression
+LINE 2: (VALUES IN (a));
+                    ^
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (sum(a)));
+ERROR:  cannot use column reference in partition bound expression
+LINE 2: (VALUES IN (sum(a)));
+                        ^
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (sum(somename)));
+ERROR:  cannot use column reference in partition bound expression
+LINE 2: (VALUES IN (sum(somename)));
+                        ^
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (sum(1)));
+ERROR:  aggregate functions are not allowed in partition bound
+LINE 2: (VALUES IN (sum(1)));
+                    ^
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN ((select 1)));
+ERROR:  cannot use subquery in partition bound
+LINE 2: (VALUES IN ((select 1)));
+                    ^
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (generate_series(4, 6)));
+ERROR:  set-returning functions are not allowed in partition bound
+LINE 2: (VALUES IN (generate_series(4, 6)));
+                    ^
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN ((1+1) collate "POSIX"));
+ERROR:  collations are not supported by type integer
+LINE 2: (VALUES IN ((1+1) collate "POSIX"));
+                          ^
+-- syntax does not allow empty list of values for list partitions
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN ());
+ERROR:  syntax error at or near ")"
+LINE 2: (VALUES IN ());
+                    ^
+-- trying to specify range for list partitioned table
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES FROM (1) TO (2));
+ERROR:  syntax error at or near "FROM"
+LINE 2: (VALUES FROM (1) TO (2));
+                ^
+-- trying to specify modulus and remainder for list partitioned table
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(MODULUS 10);
+ERROR:  invalid bound specification for a list partition
+LINE 1: CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) ...
+                                              ^
+-- must succeed
+CREATE TABLE list_parted (a int)
+PARTITION BY LIST (a)
+CONFIGURATION (values in ('1'), (2), (2+1), (null) DEFAULT PARTITION part_default);
+\d+ list_parted
+                          Partitioned table "public.list_parted"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a      | integer |           |          |         | plain   |              | 
+Partition key: LIST (a)
+Partitions: list_parted_0 FOR VALUES IN (1),
+            list_parted_1 FOR VALUES IN (2),
+            list_parted_2 FOR VALUES IN (3),
+            list_parted_3 FOR VALUES IN (NULL),
+            part_default DEFAULT
+
+-- check default partition cannot be created more than once
+CREATE TABLE fail_default_part PARTITION OF list_parted DEFAULT;
+ERROR:  partition "fail_default_part" conflicts with existing default partition "part_default"
+LINE 1: ...TE TABLE fail_default_part PARTITION OF list_parted DEFAULT;
+                                                               ^
+-- specified literal can't be cast to the partition column data type
+CREATE TABLE bools (a bool) PARTITION BY LIST (a) CONFIGURATION (VALUES IN (1));
+ERROR:  specified value cannot be cast to type boolean for column "a"
+LINE 1: ...a bool) PARTITION BY LIST (a) CONFIGURATION (VALUES IN (1));
+                                                                   ^
+-- specified literal can be cast, and the cast might not be immutable
+CREATE TABLE moneyp (a money) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (10), ('11'), (to_char(12, '99')::int));
+DROP TABLE moneyp;
+-- cast is immutable
+CREATE TABLE bigintp (a bigint) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (10));
+-- fails due to overlap:
+CREATE TABLE bigintp_overlap PARTITION OF bigintp FOR VALUES IN ('10');
+ERROR:  partition "bigintp_overlap" would overlap partition "bigintp_0"
+LINE 1: ...E bigintp_overlap PARTITION OF bigintp FOR VALUES IN ('10');
+                                                                 ^
+DROP TABLE bigintp;
+CREATE TABLE hash_parted (a int) PARTITION BY HASH (a) CONFIGURATION (MODULUS 10);
+-- all remainder values are already belong to partitions
+CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES WITH (MODULUS 30, REMAINDER 3);
+ERROR:  partition "fail_part" would overlap partition "hash_parted_3"
+LINE 1: ...BLE fail_part PARTITION OF hash_parted FOR VALUES WITH (MODU...
+                                                             ^
+-- trying to specify range for the hash partitioned table
+CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES FROM ('a', 1) TO ('z');
+ERROR:  invalid bound specification for a hash partition
+LINE 1: ...BLE fail_part PARTITION OF hash_parted FOR VALUES FROM ('a',...
+                                                             ^
+-- trying to specify list value for the hash partitioned table
+CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES IN (1000);
+ERROR:  invalid bound specification for a hash partition
+LINE 1: ...BLE fail_part PARTITION OF hash_parted FOR VALUES IN (1000);
+                                                             ^
+-- trying to create default partition for the hash partitioned table
+CREATE TABLE fail_part (a int) PARTITION BY HASH (a) CONFIGURATION (MODULUS 10
+	DEFAULT hash_default);
+ERROR:  syntax error at or near "DEFAULT"
+LINE 2:  DEFAULT hash_default);
+         ^
+-- cannot create auto partition of a non-partitioned table
+CREATE TABLE fail_part (a int) CONFIGURATION (MODULUS 10);
+ERROR:  syntax error at or near "CONFIGURATION"
+LINE 1: CREATE TABLE fail_part (a int) CONFIGURATION (MODULUS 10);
+                                       ^
+-- partition table inherits relation persistence setting from parent
+CREATE TEMP TABLE temp_parted (a char) PARTITION BY LIST (a)
+CONFIGURATION (VALUES IN ('a') DEFAULT PARTITION temp_parted_default);
+\d+ temp_parted
+                           Partitioned table "pg_temp_3.temp_parted"
+ Column |     Type     | Collation | Nullable | Default | Storage  | Stats target | Description 
+--------+--------------+-----------+----------+---------+----------+--------------+-------------
+ a      | character(1) |           |          |         | extended |              | 
+Partition key: LIST (a)
+Partitions: temp_parted_0 FOR VALUES IN ('a'),
+            temp_parted_default DEFAULT
+
+-- partition table inherits relation persistence setting from parent
+CREATE UNLOGGED TABLE unlogged_parted (a char) PARTITION BY LIST (a)
+CONFIGURATION (VALUES IN ('a') DEFAULT PARTITION unlogged_parted_default);
+\d+ unlogged_parted
+                      Unlogged partitioned table "public.unlogged_parted"
+ Column |     Type     | Collation | Nullable | Default | Storage  | Stats target | Description 
+--------+--------------+-----------+----------+---------+----------+--------------+-------------
+ a      | character(1) |           |          |         | extended |              | 
+Partition key: LIST (a)
+Partitions: unlogged_parted_0 FOR VALUES IN ('a'),
+            unlogged_parted_default DEFAULT
+
+-- check for partition bound overlap and other invalid specifications
+CREATE TABLE fail_parted2 (a varchar) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (null, 'z'),('a', 'b'),(null) DEFAULT partition tbl_default);
+ERROR:  partition "fail_parted2_2" would overlap partition "fail_parted2_0"
+LINE 2: (VALUES IN (null, 'z'),('a', 'b'),(null) DEFAULT partition t...
+                                           ^
+CREATE TABLE fail_parted2 (a varchar) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (null, 'z'),('a', 'b'),('b', 'c') DEFAULT partition tbl_default);
+ERROR:  partition "fail_parted2_2" would overlap partition "fail_parted2_1"
+LINE 2: (VALUES IN (null, 'z'),('a', 'b'),('b', 'c') DEFAULT partiti...
+                                           ^
+-- check default partition overlap
+CREATE TABLE list_parted2 (a varchar) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (null, 'z'),('a', 'b') DEFAULT partition tbl_default);
+INSERT INTO list_parted2 VALUES('X');
+CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN ('W', 'X', 'Y');
+ERROR:  updated partition constraint for default partition "tbl_default" would be violated by some row
+-- check schema propagation from parent
+CREATE TABLE parted (a text, b int NOT NULL DEFAULT 0,
+	CONSTRAINT check_a CHECK (length(a) > 0))
+PARTITION BY LIST (a) CONFIGURATION ( VALUES IN ('a','b'),('d') );
+-- only inherited attributes (never local ones)
+SELECT attname, attislocal, attinhcount FROM pg_attribute
+  WHERE attrelid = 'parted_1'::regclass and attnum > 0
+  ORDER BY attnum;
+ attname | attislocal | attinhcount 
+---------+------------+-------------
+ a       | f          |           1
+ b       | f          |           1
+(2 rows)
+
+-- able to specify column default, column constraint, and table constraint
+-- first check the "column specified more than once" error
+CREATE TABLE part_e_fail PARTITION OF parted (
+	b NOT NULL,
+	b DEFAULT 1,
+	b CHECK (b >= 0),
+	CONSTRAINT check_a CHECK (length(a) > 0)
+) FOR VALUES IN ('e');
+ERROR:  column "b" specified more than once
+CREATE TABLE part_e PARTITION OF parted (
+	b NOT NULL DEFAULT 1,
+	CONSTRAINT check_a CHECK (length(a) > 0),
+	CONSTRAINT check_b CHECK (b >= 0)
+) FOR VALUES IN ('e');
+NOTICE:  merging constraint "check_a" with inherited definition
+-- conislocal should be false for any merged constraints, true otherwise
+SELECT conname, conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_e'::regclass ORDER BY conislocal, coninhcount;
+ conname | conislocal | coninhcount 
+---------+------------+-------------
+ check_a | f          |           1
+ check_b | t          |           0
+(2 rows)
+
+-- check_a can not be dropped as it is inherited
+ALTER TABLE part_e DROP CONSTRAINT check_a;
+ERROR:  cannot drop inherited constraint "check_a" of relation "part_e"
+-- check_b can be dropped as it is local
+ALTER TABLE part_e DROP CONSTRAINT check_b;
+-- Once check_b is added to the parent, it should be made non-local for part_b
+ALTER TABLE part_e ADD CONSTRAINT check_b CHECK (b >= 0);
+ALTER TABLE parted ADD CONSTRAINT check_b CHECK (b >= 0);
+NOTICE:  merging constraint "check_b" with inherited definition
+SELECT conname, conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_e'::regclass;
+ conname | conislocal | coninhcount 
+---------+------------+-------------
+ check_a | f          |           1
+ check_b | f          |           1
+(2 rows)
+
+-- Neither check_a nor check_b are droppable from part_b
+ALTER TABLE part_e DROP CONSTRAINT check_a;
+ERROR:  cannot drop inherited constraint "check_a" of relation "part_e"
+ALTER TABLE part_e DROP CONSTRAINT check_b;
+ERROR:  cannot drop inherited constraint "check_b" of relation "part_e"
+-- And dropping it from parted should leave no trace of them on part_e, unlike
+-- traditional inheritance where they will be left behind, because they would
+-- be local constraints.
+ALTER TABLE parted DROP CONSTRAINT check_a, DROP CONSTRAINT check_b;
+SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_e'::regclass;
+ conislocal | coninhcount 
+------------+-------------
+(0 rows)
+
+-- specify PARTITION BY for a partition
+CREATE TABLE fail_part_col_not_found PARTITION OF parted FOR VALUES IN ('c') PARTITION BY HASH(c);
+ERROR:  column "c" named in partition key does not exist
+LINE 1: ...ARTITION OF parted FOR VALUES IN ('c') PARTITION BY HASH(c);
+                                                                    ^
+CREATE TABLE part_c PARTITION OF parted (b WITH OPTIONS NOT NULL DEFAULT 0) FOR VALUES IN ('c') PARTITION BY RANGE ((b));
+-- create a level-2 partition
+CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
+-- check that NOT NULL and default value are inherited correctly
+create table parted_notnull_inh_test (a int default 1, b int not null default 0) partition by list (a) CONFIGURATION (VALUES IN (1));
+insert into parted_notnull_inh_test (b) values (null);
+ERROR:  null value in column "b" of relation "parted_notnull_inh_test_0" violates not-null constraint
+DETAIL:  Failing row contains (1, null).
+-- note that a's default is preserved
+\d parted_notnull_inh_test1
+drop table parted_notnull_inh_test;
+-- Partition bound in describe output
+\d+ part_e
+                                   Table "public.part_e"
+ Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a      | text    |           |          |         | extended |              | 
+ b      | integer |           | not null | 1       | plain    |              | 
+Partition of: parted FOR VALUES IN ('e')
+Partition constraint: ((a IS NOT NULL) AND (a = 'e'::text))
+
+-- Both partition bound and partition key in describe output
+\d+ part_c
+                             Partitioned table "public.part_c"
+ Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a      | text    |           |          |         | extended |              | 
+ b      | integer |           | not null | 0       | plain    |              | 
+Partition of: parted FOR VALUES IN ('c')
+Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text))
+Partition key: RANGE (b)
+Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10)
+
+-- a level-2 partition's constraint will include the parent's expressions
+\d+ part_c_1_10
+                                Table "public.part_c_1_10"
+ Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a      | text    |           |          |         | extended |              | 
+ b      | integer |           | not null | 0       | plain    |              | 
+Partition of: part_c FOR VALUES FROM (1) TO (10)
+Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text) AND (b IS NOT NULL) AND (b >= 1) AND (b < 10))
+
+-- Show partition count in the parent's describe output
+-- Tempted to include \d+ output listing partitions with bound info but
+-- output could vary depending on the order in which partition oids are
+-- returned.
+\d parted
+         Partitioned table "public.parted"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | text    |           |          | 
+ b      | integer |           | not null | 0
+Partition key: LIST (a)
+Number of partitions: 4 (Use \d+ to list them.)
+
+\d hash_parted
+      Partitioned table "public.hash_parted"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           |          | 
+Partition key: HASH (a)
+Number of partitions: 10 (Use \d+ to list them.)
+
+-- cleanup
+DROP TABLE parted;
+DROP TABLE list_parted;
+DROP TABLE list_parted2;
+DROP TABLE hash_parted;
+DROP TABLE temp_parted;
+DROP TABLE unlogged_parted;
+-- list partitioning on array type column
+CREATE TABLE arrlp (a int[]) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN ('{1}', '{2}'));
+\d+ arrlp_1
+DROP TABLE arrlp;
+-- partition on boolean column
+create table boolspart (a bool) partition by list (a) CONFIGURATION
+(values in (true), (false));
+\d+ boolspart
+                           Partitioned table "public.boolspart"
+ Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a      | boolean |           |          |         | plain   |              | 
+Partition key: LIST (a)
+Partitions: boolspart_0 FOR VALUES IN (true),
+            boolspart_1 FOR VALUES IN (false)
+
+drop table boolspart;
+-- test using a volatile expression as partition bound
+create table volatile_partbound_test (partkey timestamp) partition by list (partkey) CONFIGURATION (values in ('1970-01-01 00:00:00+00'::timestamp, current_timestamp),('1982-01-25 00:00:00+00'::timestamp));
+drop table volatile_partbound_test;
+-- tests of column drop with partition tables and indexes using
+-- predicates and expressions.
+create table part_column_drop (useless_1 int, id int, useless_2 int, d int,
+  b int, useless_3 int) partition by hash (id) CONFIGURATION (modulus 3);
+alter table part_column_drop drop column useless_1;
+alter table part_column_drop drop column useless_2;
+alter table part_column_drop drop column useless_3;
+create index part_column_drop_b_pred on part_column_drop(b) where b = 1;
+create index part_column_drop_b_expr on part_column_drop((b = 1));
+create index part_column_drop_d_pred on part_column_drop(d) where d = 2;
+create index part_column_drop_d_expr on part_column_drop((d = 2));
+create index part_column_drop_d_1_pred on part_column_drop_1(d) where d = 2;
+create index part_column_drop_d_1_expr on part_column_drop_1((d = 2));
+\d part_column_drop
+    Partitioned table "public.part_column_drop"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ id     | integer |           |          | 
+ d      | integer |           |          | 
+ b      | integer |           |          | 
+Partition key: HASH (id)
+Indexes:
+    "part_column_drop_b_expr" btree ((b = 1))
+    "part_column_drop_b_pred" btree (b) WHERE b = 1
+    "part_column_drop_d_expr" btree ((d = 2))
+    "part_column_drop_d_pred" btree (d) WHERE d = 2
+Number of partitions: 3 (Use \d+ to list them.)
+
+\d part_column_drop_1
+         Table "public.part_column_drop_1"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ id     | integer |           |          | 
+ d      | integer |           |          | 
+ b      | integer |           |          | 
+Partition of: part_column_drop FOR VALUES WITH (modulus 3, remainder 1)
+Indexes:
+    "part_column_drop_1_b_idx" btree (b) WHERE b = 1
+    "part_column_drop_1_d_idx" btree (d) WHERE d = 2
+    "part_column_drop_1_expr_idx" btree ((b = 1))
+    "part_column_drop_1_expr_idx1" btree ((d = 2))
+    "part_column_drop_d_1_expr" btree ((d = 2))
+    "part_column_drop_d_1_pred" btree (d) WHERE d = 2
+
+\d part_column_drop_2
+         Table "public.part_column_drop_2"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ id     | integer |           |          | 
+ d      | integer |           |          | 
+ b      | integer |           |          | 
+Partition of: part_column_drop FOR VALUES WITH (modulus 3, remainder 2)
+Indexes:
+    "part_column_drop_2_b_idx" btree (b) WHERE b = 1
+    "part_column_drop_2_d_idx" btree (d) WHERE d = 2
+    "part_column_drop_2_expr_idx" btree ((b = 1))
+    "part_column_drop_2_expr_idx1" btree ((d = 2))
+
+\d part_column_drop_3
+drop table part_column_drop;
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index cee822aa8b..53b69c221e 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -971,3 +971,241 @@ create table part_column_drop_1_10 partition of
 \d part_column_drop
 \d part_column_drop_1_10
 drop table part_column_drop;
+
+-- Auto generated partitions
+
+-- must fail because of wrong configuration
+CREATE TABLE tbl_hash_fail (i int) PARTITION BY HASH (i)
+CONFIGURATION (values in (1, 2), (3, 4) default partition tbl_default);
+
+-- must fail because of wrong configuration
+CREATE TABLE tbl_list_fail (i int) PARTITION BY LIST (i)
+CONFIGURATION (values in (1, 2), (1, 3));
+
+CREATE TABLE tbl_list (i int) PARTITION BY LIST (i)
+CONFIGURATION (values in (1, 2), (3, 4) default partition tbl_default);
+
+\d+ tbl_list
+
+CREATE TABLE tbl_hash (i int) PARTITION BY HASH (i)
+CONFIGURATION (modulus 3);
+
+\d+ tbl_hash
+
+DROP TABLE tbl_list;
+DROP TABLE tbl_hash;
+
+-- forbidden expressions for partition bound with list partitioned table
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (somename));
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (somename.somename));
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (a));
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (sum(a)));
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (sum(somename)));
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (sum(1)));
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN ((select 1)));
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (generate_series(4, 6)));
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN ((1+1) collate "POSIX"));
+
+-- syntax does not allow empty list of values for list partitions
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN ());
+-- trying to specify range for list partitioned table
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES FROM (1) TO (2));
+-- trying to specify modulus and remainder for list partitioned table
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(MODULUS 10);
+
+-- must succeed
+CREATE TABLE list_parted (a int)
+PARTITION BY LIST (a)
+CONFIGURATION (values in ('1'), (2), (2+1), (null) DEFAULT PARTITION part_default);
+\d+ list_parted
+
+-- check default partition cannot be created more than once
+CREATE TABLE fail_default_part PARTITION OF list_parted DEFAULT;
+
+-- specified literal can't be cast to the partition column data type
+CREATE TABLE bools (a bool) PARTITION BY LIST (a) CONFIGURATION (VALUES IN (1));
+
+-- specified literal can be cast, and the cast might not be immutable
+CREATE TABLE moneyp (a money) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (10), ('11'), (to_char(12, '99')::int));
+DROP TABLE moneyp;
+
+-- cast is immutable
+CREATE TABLE bigintp (a bigint) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (10));
+-- fails due to overlap:
+CREATE TABLE bigintp_overlap PARTITION OF bigintp FOR VALUES IN ('10');
+DROP TABLE bigintp;
+
+CREATE TABLE hash_parted (a int) PARTITION BY HASH (a) CONFIGURATION (MODULUS 10);
+-- all remainder values are already belong to partitions
+CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES WITH (MODULUS 30, REMAINDER 3);
+-- trying to specify range for the hash partitioned table
+CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES FROM ('a', 1) TO ('z');
+-- trying to specify list value for the hash partitioned table
+CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES IN (1000);
+-- trying to create default partition for the hash partitioned table
+CREATE TABLE fail_part (a int) PARTITION BY HASH (a) CONFIGURATION (MODULUS 10
+	DEFAULT hash_default);
+
+-- cannot create auto partition of a non-partitioned table
+CREATE TABLE fail_part (a int) CONFIGURATION (MODULUS 10);
+
+-- partition table inherits relation persistence setting from parent
+CREATE TEMP TABLE temp_parted (a char) PARTITION BY LIST (a)
+CONFIGURATION (VALUES IN ('a') DEFAULT PARTITION temp_parted_default);
+\d+ temp_parted
+
+-- partition table inherits relation persistence setting from parent
+CREATE UNLOGGED TABLE unlogged_parted (a char) PARTITION BY LIST (a)
+CONFIGURATION (VALUES IN ('a') DEFAULT PARTITION unlogged_parted_default);
+\d+ unlogged_parted
+
+-- check for partition bound overlap and other invalid specifications
+CREATE TABLE fail_parted2 (a varchar) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (null, 'z'),('a', 'b'),(null) DEFAULT partition tbl_default);
+
+CREATE TABLE fail_parted2 (a varchar) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (null, 'z'),('a', 'b'),('b', 'c') DEFAULT partition tbl_default);
+
+-- check default partition overlap
+CREATE TABLE list_parted2 (a varchar) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (null, 'z'),('a', 'b') DEFAULT partition tbl_default);
+INSERT INTO list_parted2 VALUES('X');
+CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN ('W', 'X', 'Y');
+
+-- check schema propagation from parent
+
+CREATE TABLE parted (a text, b int NOT NULL DEFAULT 0,
+	CONSTRAINT check_a CHECK (length(a) > 0))
+PARTITION BY LIST (a) CONFIGURATION ( VALUES IN ('a','b'),('d') );
+
+-- only inherited attributes (never local ones)
+SELECT attname, attislocal, attinhcount FROM pg_attribute
+  WHERE attrelid = 'parted_1'::regclass and attnum > 0
+  ORDER BY attnum;
+
+-- able to specify column default, column constraint, and table constraint
+
+-- first check the "column specified more than once" error
+CREATE TABLE part_e_fail PARTITION OF parted (
+	b NOT NULL,
+	b DEFAULT 1,
+	b CHECK (b >= 0),
+	CONSTRAINT check_a CHECK (length(a) > 0)
+) FOR VALUES IN ('e');
+
+CREATE TABLE part_e PARTITION OF parted (
+	b NOT NULL DEFAULT 1,
+	CONSTRAINT check_a CHECK (length(a) > 0),
+	CONSTRAINT check_b CHECK (b >= 0)
+) FOR VALUES IN ('e');
+-- conislocal should be false for any merged constraints, true otherwise
+SELECT conname, conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_e'::regclass ORDER BY conislocal, coninhcount;
+
+-- check_a can not be dropped as it is inherited
+ALTER TABLE part_e DROP CONSTRAINT check_a;
+-- check_b can be dropped as it is local
+ALTER TABLE part_e DROP CONSTRAINT check_b;
+
+-- Once check_b is added to the parent, it should be made non-local for part_b
+ALTER TABLE part_e ADD CONSTRAINT check_b CHECK (b >= 0);
+ALTER TABLE parted ADD CONSTRAINT check_b CHECK (b >= 0);
+SELECT conname, conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_e'::regclass;
+
+-- Neither check_a nor check_b are droppable from part_b
+ALTER TABLE part_e DROP CONSTRAINT check_a;
+ALTER TABLE part_e DROP CONSTRAINT check_b;
+
+-- And dropping it from parted should leave no trace of them on part_e, unlike
+-- traditional inheritance where they will be left behind, because they would
+-- be local constraints.
+ALTER TABLE parted DROP CONSTRAINT check_a, DROP CONSTRAINT check_b;
+SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_e'::regclass;
+
+-- specify PARTITION BY for a partition
+CREATE TABLE fail_part_col_not_found PARTITION OF parted FOR VALUES IN ('c') PARTITION BY HASH(c);
+CREATE TABLE part_c PARTITION OF parted (b WITH OPTIONS NOT NULL DEFAULT 0) FOR VALUES IN ('c') PARTITION BY RANGE ((b));
+
+-- create a level-2 partition
+CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
+
+-- check that NOT NULL and default value are inherited correctly
+create table parted_notnull_inh_test (a int default 1, b int not null default 0) partition by list (a) CONFIGURATION (VALUES IN (1));
+insert into parted_notnull_inh_test (b) values (null);
+-- note that a's default is preserved
+\d parted_notnull_inh_test1
+drop table parted_notnull_inh_test;
+
+-- Partition bound in describe output
+\d+ part_e
+
+-- Both partition bound and partition key in describe output
+\d+ part_c
+
+-- a level-2 partition's constraint will include the parent's expressions
+\d+ part_c_1_10
+
+-- Show partition count in the parent's describe output
+-- Tempted to include \d+ output listing partitions with bound info but
+-- output could vary depending on the order in which partition oids are
+-- returned.
+\d parted
+\d hash_parted
+
+-- cleanup
+DROP TABLE parted;
+DROP TABLE list_parted;
+DROP TABLE list_parted2;
+DROP TABLE hash_parted;
+DROP TABLE temp_parted;
+DROP TABLE unlogged_parted;
+
+
+-- list partitioning on array type column
+CREATE TABLE arrlp (a int[]) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN ('{1}', '{2}'));
+\d+ arrlp_1
+DROP TABLE arrlp;
+
+-- partition on boolean column
+create table boolspart (a bool) partition by list (a) CONFIGURATION
+(values in (true), (false));
+\d+ boolspart
+drop table boolspart;
+
+-- test using a volatile expression as partition bound
+create table volatile_partbound_test (partkey timestamp) partition by list (partkey) CONFIGURATION (values in ('1970-01-01 00:00:00+00'::timestamp, current_timestamp),('1982-01-25 00:00:00+00'::timestamp));
+drop table volatile_partbound_test;
+
+-- tests of column drop with partition tables and indexes using
+-- predicates and expressions.
+create table part_column_drop (useless_1 int, id int, useless_2 int, d int,
+  b int, useless_3 int) partition by hash (id) CONFIGURATION (modulus 3);
+alter table part_column_drop drop column useless_1;
+alter table part_column_drop drop column useless_2;
+alter table part_column_drop drop column useless_3;
+create index part_column_drop_b_pred on part_column_drop(b) where b = 1;
+create index part_column_drop_b_expr on part_column_drop((b = 1));
+create index part_column_drop_d_pred on part_column_drop(d) where d = 2;
+create index part_column_drop_d_expr on part_column_drop((d = 2));
+create index part_column_drop_d_1_pred on part_column_drop_1(d) where d = 2;
+create index part_column_drop_d_1_expr on part_column_drop_1((d = 2));
+
+\d part_column_drop
+\d part_column_drop_1
+\d part_column_drop_2
+\d part_column_drop_3
+drop table part_column_drop;

---

From: Pavel Borisov <[email-masked]>
Date: Wed, 7 Oct 2020 11:30:01 +0400

> tests just to calm down the CI.

It is in accordance with changes in tests for vanilla
decralarive partitioning as per

commit 2dfa3fea88bc951d0812a18649d801f07964c9b9
Author: Tom Lane <[email-masked]>
Date:   Mon Sep 28 13:44:01 2020 -0400
    Remove complaints about COLLATE clauses in partition bound values.

which my test does for automatic way in the same style. So I consider your
removal completely correct.

Thank you!

---

From: Anastasia Lubennikova <[email-masked]>
Date: Wed, 7 Oct 2020 16:05:08 +0300

On 05.10.2020 09:36, Rahila Syed wrote:
>>     the earlier discussions. I think it is intuitive to
>>     include IMMEDIATE with the current implementation
>>     so that the syntax can be extended with a DEFERRED clause in
>>     future for dynamic partitions.
>>
>     introduce IMMEDIATE | DEFERRED keyword. For hash and list
>     partitions it will always be immediate, as the number of
>     partitions cannot change after we initially set it. For range
>     partitions, on the contrary, it doesn't make much sense to make
>     partitions immediately, because in many use-cases one bound will
>     be open.
>
>
> DEFERRED clause refers to creating partitions on the fly, while the 
> data is being inserted.
> The number of partitions and partition bounds can be the same as 
> specified initially
> This seems like a potential extension to statically created partitions 
> even in the case of

Oh, now I see what you mean. The case with already existing tables will 
require changes to ALTER TABLE syntax. And that's where we may want to 
choose between immediate (i.e. locking) and deferred (i.e. concurrent) 
creation of partitions. I think we should try to implement it with 
existing keywords, maybe use 'CONCURRENTLY' keyword and it will look like:

ALTER TABLE tbl PARTITION BY ... CONFIGURATION (....) [CONCURRENTLY];

Anyway, the task of handling existing data is much more complicated, 
especially the 'concurrent' case and to be honest, I haven't put much 
thought into it yet.

The current patch only implements the simplest case of creating a new 
partitioned table. And I don't see if CREATE TABLE needs this 
immediate|deferred clause or if it will need it in the future.

Thoughts?

>
>          2. One suggestion for generation of partition names is to
>     append a unique id to
>
>>     avoid conflicts.
>
>     Can you please give an example of such a conflict? I agree that
>     current naming scheme is far from perfect, but I think that
>     'tablename'_partnum provides unique name for each partition.
>
>>
> As per current impemetation, if I do the following it results in the 
> table name conflict.
>
> postgres=# CREATE TABLE tbl_test_5 (i int) PARTITION BY 
> LIST((tbl_test_5)) CONFIGURATION (values in ('(1)'::tbl_test_5), 
> ('(3)'::tbl_test_5) default partition tbl_default_5);
> ERROR:relation "tbl_test_5_1" already exists

I don't mind adding some specific suffix for generated partitions, 
although it still may conflict with existing table names. The main 
disadvantage of this idea, is that it reduces number of symbols 
available for table name, which can lead to something like this:

CREATE TABLE 
parteddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd (a 
text, b int NOT NULL DEFAULT 0,  CONSTRAINT check_aa CHECK (length(a) > 0))
PARTITION BY LIST (a) CONFIGURATION (VALUES IN ('a','b'),('c','d') 
DEFAULT PARTITION parted_def) ;;
NOTICE:  identifier 
"parteddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd" 
will be truncated to 
"partedddddddddddddddddddddddddddddddddddddddddddddddddddddddddd"
ERROR:  relation 
"partedddddddddddddddddddddddddddddddddddddddddddddddddddddddddd" 
already exists

The error message here is a bit confusing, as relation 
'partedddddddddddddddddddddddddddddddddddddddddddddddddddddddddd' 
haven't existed before and this is a conflict between partitioned and 
generated partition table name. For now, I don't know if we can handle 
it more gracefully. Probably, we could truncate tablename to a shorter 
size, but it doesn't provide a complete solution, because partition 
number can contain several digits.

See also pg_partman documentation on the same issue: 
https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#naming-length-limits

---

From: Pavel Borisov <[email-masked]>
Date: Wed, 7 Oct 2020 20:30:32 +0400

> disadvantage of this idea, is that it reduces number of symbols available
> for table name, which can lead to something like this:
>
> CREATE TABLE
> PARTITION BY LIST (a) CONFIGURATION (VALUES IN ('a','b'),('c','d') DEFAULT
> PARTITION parted_def) ;;
> NOTICE:  identifier
> "parteddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd" will
> be truncated to
> "partedddddddddddddddddddddddddddddddddddddddddddddddddddddddddd" already
> exists
> doc/pg_partman.md#naming-length-limits
> <https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#naming-length-limits>
>
It seems to me that a working idea is to add a prefix to partitions is to
give the possibility to specify it for users. So the user will be able to
choose appropriate and not very long suffix to avoid conflicts.
Maybe like this:
CREATE TABLE city (a text) PARTITION BY LIST (a) CONFIGURATION (VALUES IN
('a','b'),('c','d') DEFAULT PARTITION city_other PREFIX _prt) ;

Result:
---
city_prt1
city_prt2
...
city_other

-- 
Best regards,
Pavel Borisov

---

From: Pavel Borisov <[email-masked]>
Date: Tue, 3 Nov 2020 12:09:41 +0400

Again I've checked v3 patch. In the discussion, there are several other
ideas on its further development, so I consider the patch as the first step
to later progress. Though now the patch is fully self-sufficient in
functionality and has enough tests etc. I suppose it is ready to be
committed.

-- 
Best regards,
Pavel Borisov

---

From: Pavel Borisov <[email-masked]>
Date: Fri, 18 Dec 2020 22:54:54 +0400

I've realized one strange effect in current grammar parsing: if I do

CREATE TABLE foo (a int) PARTITION BY LIST (a) CONFIGURATION (a 1);
ERROR:  unrecognized auto partition bound specification "a"

I consulted the patch code and realized that in fact, the patch considers
it the (invalid) HASH bounds (doesn't find a word 'modulus') unless it is
specified to be (still invalid) LIST. This is due to the fact that the
grammar parser is not context-aware and in the patch, we tried to avoid the
new parser keyword MODULUS. The effect is that inside a CONFIGURATION
parentheses in case of HASH bounds we don't have a single keyword for the
parser to determine it is really a HASH case.

It doesn't make the patch work wrongly, besides it checks the validity of
all types of bounds in the HASH case even when the partitioning is not
HASH. I find this slightly bogus. This is because the parser can not
determine the type of partitioning inside the configuration clause and this
makes adding new syntax (e.g. adding RANGE partitioning configuration
inside CONFIGURATION parentheses) complicated.

So I have one more syntax proposal: to have separate keywords
inside CONFIGURATION parentheses for each partitioning type.
E.g:
CREATE TABLE foo(a int) PARTITION BY LIST(a) CONFIGURATION (FOR VALUES IN
(1,2),(3,4) DEFAULT PARTITION foo_def);
CREATE TABLE foo(a int) PARTITION BY HASH(a) CONFIGURATION (FOR VALUES WITH
MODULUS 3);
CREATE TABLE foo(a int) PARTITION BY RAGE(a) CONFIGURATION (FOR VALUES FROM
1 TO 1000 INTERVAL 10 DEFAULT PARTITION foo_def);

This proposal is in accordance with the current syntax of declarative
partitioning: CREATE TABLE foo_1 PARTITION OF foo FOR VALUES ...

Some more facultative proposals incremental to the abovementioned:
1. Omit CONFIGURATION with/without parentheses. This makes syntax closer
to (non-automatic) declarative partitioning syntax but the clause
seems less legible (in my opinion).
2. Omit just FOR VALUES. This makes the clause short, but adds a difference
to (non-automatic) declarative partitioning syntax.

 I'm planning also to add RANGE partitioning syntax to this in the future
and I will be happy if all three types of the syntax could come along
easily.
 I very much appreciate your views on this especially regarding that
changes can be still made easily because the patch is not committed yet.

-- 
Best regards,
Pavel Borisov

---

From: Maxim Orlov <[email-masked]>
Date: Mon, 21 Dec 2020 13:49:25 +0300

On 2020-12-18 21:54, Pavel Borisov wrote:
> I consulted the patch code and realized that in fact, the patch
> considers it the (invalid) HASH bounds (doesn't find a word 'modulus')
> unless it is specified to be (still invalid) LIST. This is due to the
> fact that the grammar parser is not context-aware and in the patch, we
> tried to avoid the new parser keyword MODULUS. The effect is that
> inside a CONFIGURATION parentheses in case of HASH bounds we don't
> have a single keyword for the parser to determine it is really a HASH
> case.
> 
> It doesn't make the patch work wrongly, besides it checks the validity
> of all types of bounds in the HASH case even when the partitioning is
> not HASH. I find this slightly bogus. This is because the parser can
> not determine the type of partitioning inside the configuration clause
> and this makes adding new syntax (e.g. adding RANGE partitioning
> configuration inside CONFIGURATION parentheses) complicated.
> 
> So I have one more syntax proposal: to have separate keywords inside
> CONFIGURATION parentheses for each partitioning type.
> E.g:
> CREATE TABLE foo(a int) PARTITION BY LIST(a) CONFIGURATION (FOR VALUES
> IN (1,2),(3,4) DEFAULT PARTITION foo_def);
> CREATE TABLE foo(a int) PARTITION BY HASH(a) CONFIGURATION (FOR VALUES
> WITH MODULUS 3);
> CREATE TABLE foo(a int) PARTITION BY RAGE(a) CONFIGURATION (FOR VALUES
> FROM 1 TO 1000 INTERVAL 10 DEFAULT PARTITION foo_def);
> 
> 1. Omit CONFIGURATION with/without parentheses. This makes syntax
> closer to (non-automatic) declarative partitioning syntax but the
> clause seems less legible (in my opinion).
> 2. Omit just FOR VALUES. This makes the clause short, but adds a
> difference to (non-automatic) declarative partitioning syntax.
> 
>  I'm planning also to add RANGE partitioning syntax to this in the
> future and I will be happy if all three types of the syntax could come
> along easily.
> 
> Postgres Professional: http://postgrespro.com [1]
> 
> 
> Links:
> ------
> [1] http://www.postgrespro.com

In my view, next expressions are the golden ground here. On one hand, 
not far from the original
non-automatic declarative partitioning syntax syntax, on the other hand, 
omit CONFIGURATION
key-word (which is redundant here in terms of gram parsing) makes this 
expressions less apprehensible for the human.

CREATE TABLE foo(a int) PARTITION BY LIST(a) CONFIGURATION (FOR VALUES 
IN (1,2),(3,4) DEFAULT PARTITION foo_def);
CREATE TABLE foo(a int) PARTITION BY HASH(a) CONFIGURATION (FOR VALUES 
WITH MODULUS 3);
CREATE TABLE foo(a int) PARTITION BY RAGE(a) CONFIGURATION (FOR VALUES 
FROM 1 TO 1000 INTERVAL 10 DEFAULT PARTITION foo_def);

In addition to that, adding RANGE PARTITION would be much simpler since 
we would have specific "branches" in gram instead of using 
context-sensitive grammar and dealing with it in c-code.
---
Best regards,
Maxim Orlov.

---

From: Fabien COELHO <[email-masked]>
Date: Tue, 22 Dec 2020 10:29:56 -0400 (AST)

> (1,2),(3,4) DEFAULT PARTITION foo_def);

I would like to disagree with this syntactic approach because it would 
very specific to each partition method. IMHO the syntax should be as 
generic as possible. I'd suggest (probably again) a keyword/value list 
which would allow to be quite adaptable without inducing any pressure on 
the parser.

-- 
Fabien.

---

From: Pavel Borisov <[email-masked]>
Date: Tue, 22 Dec 2020 19:03:05 +0400

> which would allow to be quite adaptable without inducing any pressure on
If I remember your proposal correctly it is something like
CREATE TABLE foo(...) PARTITION BY HASH AUTOMATIC (MODULUS 10);

It is still possible but there are some caveats:
1. We'll need to add keyword MODULUS (and probably AUTOMATIC) to the
parser's list. I don't against this but as far as I've heard there is some
opposition among PG community against new keywords. Maybe I am wrong.
2. The existing syntax for declarative partitioning is different to your
proposal. It is still not a big problem and your proposal makes query
shorter for several words. I'd just like to see some consensus on the
syntax. Now I must admit there are too many contradictions in opinions
which make progress slow. Also I think it is important to have a really
convenient syntaх.
2a Maybe we all who participated in the thread can vote for some variant?
2b Maybe the existing syntax for declarative partitioniong should be given
some priority as it is already committed into CREATE TABLE ... PARTITION OF
... FOR VALUES IN.. etc.

I'd be happy if everyone will join some version of the proposed syntaх in
this thread and in the previous discussion [1]. If we have a variant with
more than one supporter, sure we can develop patch based on it.
Thank you very much
and Merry Christmas!

[1]

-- 
Best regards,
Pavel Borisov

---

From: Fabien COELHO <[email-masked]>
Date: Tue, 22 Dec 2020 11:38:30 -0400 (AST)

HEllo.

> CREATE TABLE foo(...) PARTITION BY HASH AUTOMATIC (MODULUS 10);

Yep, that would be the spirit.

> It is still possible but there are some caveats: 1. We'll need to add 
> keyword MODULUS (and probably AUTOMATIC) to the parser's list.

Why? We could accept anything in the list? i.e.:

    (ident =? value[, ident =? value]*)

> I don't against this but as far as I've heard there is some

the ident is a keyword that can be interpreted later on, not a "reserved 
keyword" from a parser perspective, which is the only real issue?

The parser does not need to know about it, only the command interpreter 
which will have to interpret it. AUTOMATIC is a nice parser cue to 
introduce such a ident-value list.

> proposal.

Yep. I think that it was not so good a design choice from a 
language/extensibility perspective.

> It is still not a big problem and your proposal makes query

-- 
Fabien.

---

From: Pavel Borisov <[email-masked]>
Date: Tue, 22 Dec 2020 19:58:13 +0400

> language/extensibility perspective.
Thank you very much, Fabien. It is clear enough.
BTW could you tell me a couple of words about pros and cons of c-code
syntax parsing comparing to parsing using gram.y trees? I think both are
possible but my predisposition was that we'd better use the later if
possible.

Best regards,
Pavel Borisov

---

From: Fabien COELHO <[email-masked]>
Date: Tue, 22 Dec 2020 16:50:36 -0400 (AST)

> syntax parsing comparing to parsing using gram.y trees?

I'd rather use an automatic tool (lexer/parser) if possible instead of 
doing it by hand if I can. If you want a really nice syntax with clever 
tricks, then you may need to switch to manual though, but pg/sql is not in 
that class.

> I think both are possible but my predisposition was that we'd better use 
> the later if possible.

I agree.

-- 
Fabien.

---

From: Pavel Borisov <[email-masked]>
Date: Wed, 23 Dec 2020 14:59:51 +0400

Thank you!

Fabien, do you consider it possible to change the syntax of declarative
partitioning too? It is problematic as it is already committed but also is
very tempting to have the same type of syntax both in automatic
partitioning and in manual (PARTITION OF...)

-- 
Best regards,
Pavel Borisov

---

From: Fabien COELHO <[email-masked]>
Date: Wed, 23 Dec 2020 09:49:15 -0400 (AST)

> partitioning too?

My 0.02 €: What I think does not matter much, what committers think is the 
way to pass something. However, I do not think that such an idea would 
pass a committer:-)

> It is problematic as it is already committed but also is very tempting 
> to have the same type of syntax both in automatic partitioning and in 
> manual (PARTITION OF...)

I think that if a "common" syntax, for a given meaning of common, can be 
thought of, and without breaking backward compatibility, then there may be 
an argument to provide such a syntax, but I would not put too much energy 
into that if I were you.

I see 3 cases:

  - partition declaration but no actual table generated, the current
    version.

  - partition declaration with actual sub-tables generated, eg for hash
    where it is pretty straightforward to know what would be needed, or for
    a bounded range.

  - partition declaration without generated table, but they are generated
    on demand, when needed, for a range one may want weekly or monthly
    without creating tables in advance, esp. if it is unbounded.

ISTM that the syntax should be clear and if possible homogeneous for all 
three use cases, even if they are not implemented yet. It should also 
allow easy extensibility, hence something without a strong syntax, 
key/value pairs to be interpreted later.

-- 
Fabien.

---

From: Pavel Borisov <[email-masked]>
Date: Wed, 23 Dec 2020 18:03:28 +0400

> way to pass something. However, I do not think that such an idea would

The same idea was the reason for my proposal to make automatic partitioning
clauses to be in accordance with existing declarative syntax (even if it
seems little bit long to write words "configuration (for values" )

FROM 1 TO 1000 INTERVAL 10 DEFAULT PARTITION foo_def)

If we want generic (ident = value,...) then we need to introduce different
to what is already committed for manual partitioning which I considered
worse than my proposal above. Still other opinions are highly valued.
-- 
Best regards,
Pavel Borisov

---

From: Thomas Munro <[email-masked]>
Date: Mon, 11 Jan 2021 10:22:48 +1300

> tests just to calm down the CI.

I don't think we can use \d+ on a temporary table here, because the
backend ID appears in the namespace, which is causing a failure on one
of the CI OSes due to nondeterminism:

CREATE TEMP TABLE temp_parted (a char) PARTITION BY LIST (a)
CONFIGURATION (VALUES IN ('a') DEFAULT PARTITION temp_parted_default);
\d+ temp_parted
- Partitioned table "pg_temp_3.temp_parted"
+ Partitioned table "pg_temp_4.temp_parted"

---

From: Pavel Borisov <[email-masked]>
Date: Mon, 25 Jan 2021 16:32:31 +0400

> + Partitioned table "pg_temp_4.temp_parted"

I've updated the tests accordingly. PFA version 4.
As none of the recent proposals to modify the syntax were seconded by
anyone, I return the previous Ready-for-committer CF status.

-- 
Best regards,
Pavel Borisov

---

From: Justin Pryzby <[email-masked]>
Date: Tue, 2 Mar 2021 14:26:17 -0600

https://commitfest.postgresql.org/32/2694/

I don't know what committers will say, but I think that "ALTER TABLE" might be
the essential thing for this patch to support, not "CREATE".  (This is similar
to ALTER..SET STATISTICS, which is not allowed in CREATE.)

The reason is that ALTER is what's important for RANGE partitions, which need
to be created dynamically (for example, to support time-series data
continuously inserting data around 'now').  I assume it's sometimes also
important for LIST.  I think this patch should handle those cases better before
being commited, or else we risk implementing grammar and other user-facing interface
that fails to handle what's needed into the future (or that's non-essential).
Even if dynamic creation isn't implemented yet, it seems important to at least
implement the foundation for setting the configuration to *allow* that in the
future, in a manner that's consistent with the initial implementation for
"static" partitions.

ALTER also supports other ideas I mentioned here:
https://www.postgresql.org/message-id/20200706145947.GX4107%40telsasoft.com

  - ALTER .. SET interval (for dynamic/deferred RANGE partitioning)
  - ALTER .. SET modulus, for HASH partitioning, in the initial implementation,
    this would allow CREATING paritions, but wouldn't attempt to handle moving
    data if overlapping table already exists:
  - Could also set the table-name, maybe by format string;
  - Could set "retention interval" for range partitioning;
  - Could set if the partitions are themselves partitioned(??)

I think once you allow setting configuration parameters like this, then you
might have an ALTER command to "effect" them, which would create any static
tables required by the configuration.  maybe that'd be automatic, but if
there's an "ALTER .. APPLY PARTITIONS" command (or whatever), maybe in the
future, the command could also be used to "repartition" existing table data
into partitions with more fine/course granularity (modulus, or daily vs monthly
range, etc).

-- 
Justin

---

From: Nitin Jadhav <[email-masked]>
Date: Sun, 25 Apr 2021 19:49:47 +0530

I have reviewed the v4 patch. The patch does not get applied on the latest
source. Kindly rebase.
However I have found few comments.

1.
> +CONFIGURATION (values in (1, 2), (3, 4) default partition tbl_default);

Here some of the keywords are mentioned in UPPER CASE (Ex: CREATE TABLE,
CONFIGURATION, etc) and some are mentioned in lower case (Ex: values in,
default partition, etc). Kindly make it common. I feel making it to UPPER
CASE is better. Please take care of this in all the cases.

2. It is better to separate the failure cases and success cases in
/src/test/regress/sql/create_table.sql for better readability. All the
failure cases can be listed first and then the success cases.

3.
> +           part_relname = psprintf("%s_%d", cxt->relation->relname, i);

The assignment can be done directly while declaring the variable.

Thanks & Regards,
Nitin Jadhav

---

From: Pavel Borisov <[email-masked]>
Date: Fri, 9 Jul 2021 14:29:49 +0400

> The assignment can be done directly while declaring the variable.
Thank you for your review!
I've rebased the patch and made the changes mentioned.
PFA v5.

-- 
Best regards,
Pavel Borisov

---

From: John Naylor <[email-masked]>
Date: Fri, 9 Jul 2021 07:05:13 -0400

> I've rebased the patch and made the changes mentioned.

I've set this back to "needs review" in CF.

--
John Naylor
EDB: http://www.enterprisedb.com

---

From: Pavel Borisov <[email-masked]>
Date: Fri, 9 Jul 2021 15:19:02 +0400

> I've set this back to "needs review" in CF.
Thanks for the attention! I did the review of this patch, and the changes
I've introduced in v5 are purely cosmetic. So I'd suppose the
ready-for-committer status should not better have been changed.
So I'd like return it to ready-for-committer. If you mind against this,
please mention. The opinion of Nitin, a second reviewer, is also very much
appreciated.

-- 
Best regards,
Pavel Borisov

---

From: Robert Haas <[email-masked]>
Date: Fri, 9 Jul 2021 09:31:05 -0400

> future, in a manner that's consistent with the initial implementation for

I don't think it's a hard requirement, but it's an interesting point.
My initial reactions to the patch are:

- I don't think it's a very good idea to support LIST and HASH but not
RANGE. We need a design that can work for all three partitioning
strategies, even if we don't have support for all of them in the
initial patch. If they CAN all be in the same patch, so much the
better.

- I am not very impressed with the syntax. CONFIGURATION is an odd
word that seems too generic for what we're talking about here. It
would be tempting to use a connecting word like WITH or USING except
that both would be ambiguous here, so we can't. MySQL and Oracle use
the keyword PARTITIONS -- which I realize isn't a keyword at all in
PostgreSQL right now -- to introduce the partition specification. DB2
uses no keyword at all; it seems you just say PARTITION BY
(mypartitioncol) (...partition specifications go here...). I think
either approach could work for us. Avoiding the extra keyword is a
plus, especially since I doubt we're likely to support the exact
syntax that Oracle and MySQL offer anyway - though if we do, then I'd
be in favor of inserting the PARTITIONS keyword so that people's SQL
can work without modification.

- We need to think a little bit about exactly what we're trying to do.
The simplest imaginable thing here would be to just give people a
place to put a bunch of partition specifications. So you can imagine
letting someone say PARTITION BY HASH (FOR VALUES WITH (MODULUS 2,
REMAINDER 0), FOR VALUES WITH (MODULUS 2, REMAINDER 1)). However, the
patch quite rightly rejects that approach in favor of the theory that,
at CREATE TABLE time, you're just going to want to give a modulus and
have the system create one partition for every possible remainder. But
that could be expressed even more compactly than what the patch does.
Instead of saying PARTITION BY HASH CONFIGURATION (MODULUS 4) we could
just let people say PARTITION BY HASH (4) or probably even PARTITION
BY HASH 4.

- For list partitioning, the patch falls back to just letting you put
a bunch of VALUES IN clauses in the CREATE TABLE statement. I don't
find something like PARTITION BY LIST CONFIGURATION (VALUES IN (1, 2),
(1, 3)) to be particularly readable. What are all the extra keywords
adding? We could just say PARTITION BY LIST ((1, 2), (1, 3)). I think
I would find that easier to remember; not sure what other people
think. As an alternative, PARTITION BY LIST VALUES IN (1, 2), (1, 3)
looks workable, too.

- What about range partitioning? This is an interesting case because
while in theory you could leave gaps between range partitions, in
practice people probably don't want to do that very often, and it
might be better to have a simpler syntax that caters to the common
case, since people can always create partitions individually if they
happen to want gaps. So you can imagine making something like
PARTITION BY RANGE ((MINVALUE), (42), (163)) mean create two
partitions, one from (MINVALUE) to (42) and the other from (42) to
(163). I think that would be pretty useful.

- Another possible separating keyword here would be INITIALLY, which
is already a parser keyword. So then you could have stuff like
PARTITION BY HASH INITIALLY 4, PARTITION BY LIST INITIALLY ((1, 2),
(1, 3)), PARTITION BY RANGE INITIALLY ((MINVALUE), (42), (163)).

- The patch doesn't document the naming convention for the
automatically created partitions, and it is worth thinking a bit about
how that is going to work. Do people want to be able to specify the
name of the partitioned table when they are using this syntax, or are
they happy with automatically generated names? If the latter, are they
happy with THESE automatically generated names? I guess for HASH
appending _%d where %d is the modulus is fine, but it is not necessary
so great for LIST. If I said CREATE TABLE foo ... PARTITION BY LIST
(('en'), ('ru'), ('jp')) I think I'd be hoping to end up with
partitions named foo_en, foo_ru, and foo_jp rather than foo_0, foo_1,
foo_2. Or maybe I'd rather say PARTITION BY LIST (foo_en ('en'),
foo_ru ('ru'), foo_jp ('jp')) or something like that to be explicit
about it. Not sure. But it's worth some thought. I think this comes
into focus even more clearly for range partitions, where you probably
want the partitions to follow a convention like basetablename_yyyy_mm.

- The documentation for the CONFIGURATION option doesn't match the
grammar. The documentation makes it an independent clause, so
CONFIGURATION could be specified even if PARTITION BY is not. But the
implementation makes the better choice to treat CONFIGURATION as a
further specification of PARTITION BY.

- I don't think this patch is really all that close to being ready for
committer. Beyond the design issues which seem to need more thought,
there's stuff in the patch like:

Now, on the one hand, debugging elogs like this have little business
in a final patch. And, on the other hand, if we were going to include
them in the final patch, we'd probably want to at least spell the
function name correctly. Similarly, it's evident that this test case
has not been carefully reviewed by anyone, including the author:

+REATE TABLE fail_parted (a int) PARTITION BY HASH (a) CONFIGURATION
+(MODULUS 10 DEFAULT PARTITION hash_default);

Not too surprisingly, the system isn't familiar with the REATE command.

- There's some questionable error-reporting behavior in here, too, particularly:

+CREATE TABLE fail_parted (a int) PARTITION BY LIST (a) CONFIGURATION
+(values in (1, 2), (1, 3));
+ERROR:  partition "fail_parted_1" would overlap partition "fail_parted_0"
+LINE 2: (values in (1, 2), (1, 3));

Since the user hasn't provided the names fail_parted_0 or
fail_parted_1, it kind of stinks to use them in an error report. The
error cursor is good, but I wonder if we need to do better. One option
would be to go to a syntax where the user specifies the partition
names explicitly, which then justifies using that name in an error
report. Another possibility would be to give a different message in
this case, like:

ERROR: partition for values in (1, 2) would overlap partition for
values in (1, 3)

Now, that would require a pretty substantial redesign of the patch,
and I'm not sure it's worth the effort. But I'm also not sure that it
isn't.

-- 
Robert Haas

---

From: Pavel Borisov <[email-masked]>
Date: Wed, 14 Jul 2021 15:28:05 +0400

> (1, 3)), PARTITION BY RANGE INITIALLY ((MINVALUE), (42), (163)).

Robert, I've read your considerations and I have a proposal to change the
syntax to make it like:

CREATE TABLE foo (bar text) PARTITION BY LIST (bar) PARTITIONS (('US'),
('UK', 'RU'));
CREATE TABLE foo (bar text) PARTITION BY LIST (bar) PARTITIONS
(foo_us('US'), foo_uk_ru('UK', 'RU'), { DEFAULT foo_dflt | AUTOMATIC });

CREATE TABLE foo (bar int) PARTITION BY HASH (bar) PARTITIONS (5);

CREATE TABLE foo (bar int) PARTITION BY RANGE (bar) PARTITIONS (FROM 1 TO
10 INTERVAL 2, { DEFAULT foo_dflt | AUTOMATIC });

- I think using partitions syntax without any keyword at all, is quite
different from the existing pseudo-english PostgreSQL syntax. Also, it will
need two consecutive brackets divided by nothing (<partitioning
key>)(<partitions configuration>). So I think it's better to use the
keyword PARTITIONS

- from the current patch it seems like a 'syntactic sugar' only but I don't
think it is being so. From a new syntaх proposal it's seen that it can
enable three options
(1) create a fixed set of partitions with everything else comes to the
default partition
(2) create a fixed set of partitions with everything else invokes error on
insert
(3) create a set of partitions with everything else invokes a new partition
creation based on a partition key (AUTOMATIC word). Like someone will be
able to do:
CREATE TABLE foo (a varchar) PARTITION BY LIST (SUBSTRING (a, 1, 1))
PARTITIONS (('a'),('b'),('c'));
INSERT INTO foo VALUES ("doctor"); // will automatically create partition
for 'd'
INSERT INTO foo VALUES ("dam"); // will come into partition 'd'

Option (3) is not yet implemented and sure it needs much care from DBA to
not end up with the each-row-separate-partition.

- Also with option (3) and AUTOMATIC word someone will be able to do:
CREATE TABLE foo (a timestamp, t text) PARTITION BY LIST(EXTRACT (YEAR FROM
a)) PARTITIONS (('1982'),('1983'),('1984'));
INSERT INTO foo VALUES (TIMESTAMP '1986-01-01 13:30:03', 'Orwell'); //
creates '1986' partition and inserts into it
I think this option will be very useful as partitioning based on regular
intervals of time I think is quite natural and often used. And to do it we
don't need to implement arbitrary intervals (partition by range). But I
think it's also worth implementing (proposed syntax for RANGE see above);

- As for the naming of partitions I've seen what is done in Oracle:
partition names can be provided when you create an initial set, and when a
partition is created automatically on insert it will get some illegible
name chosen by the system (it even doesn't include parent table prefix).
I'd propose to implement:
(1) If partition name is not specified it has format
<parent_table_name>_<value_of_partition_key>
where <value_of_partition_key> is a remainder in HASH, the first element of
the list of values for the partition in LIST case, left range-bound in
RANGE case
(2) If it is specified (not possible at partition creation at insert
command) it is <parent_table_name>_<specified_name>
Though we'll probably need to have some rules for the abbreviation for
partition name should not exceed the relation name length limit. I think
partitions naming with plain _numbers in the existing patch is for the
purpose of increasing relation name length as little as possible for not
implementing abbreviation.

What do you think will the described approach lead to a useful patch?
Should it be done as a whole or it's possible to commit it in smaller
steps? (E.g. first part without AUTOMATIC capability, then add AUTOMATIC
capability. Or with some other order of features implementation)

My own view is that if some implementation of syntax is solidly decided, it
will promote work on more complicated logic of the patch and implement all
parts one-by-one for the feature finally become really usable (not just
helping to squash several SQL commands into one as this patch does). I see
the existing patch as the starting point of the whole work and given some
decisions on syntax I can try to rework and extend it accordingly.

Overall I consider this useful for PostgreSQL.

What do you think about it?

-- 
Best regards,
Pavel Borisov

---

From: Robert Haas <[email-masked]>
Date: Tue, 20 Jul 2021 14:42:16 -0400

On Wed, Jul 14, 2021 at 7:28 AM Pavel Borisov <[email-masked]> wrote:
> What do you think will the described approach lead to a useful patch? Should it be done as a whole or it's possible to commit it in smaller steps? (E.g. first part without AUTOMATIC capability, then add AUTOMATIC capability. Or with some other order of features implementation)

I would suggest that you consider on-the-fly partition creation to be
a completely separate feature from initial partition creation as part
of CREATE TABLE. I think you can have either without the other, and I
think the latter is a lot easier than the former. I doubt that
on-the-fly partition creation makes any sense at all for hash
partitions; there seems to be no reason not to pre-create all the
partitions. It's pretty straightforward to see how it should work for
LIST, but RANGE needs an interval or something to be stored in the
system catalogs so you can figure out where to put the boundaries, and
somehow you've got to identify a + operator for the relevant data
type. Tom Lane probably won't be thrilled if you suggest looking it up
based on the operator NAME. The bigger issue IMHO with on-the-fly
partition creation is avoiding deadlocks in the presence of current
inserters; I submit that without at least some kind of attempt to
avoid deadlocks and spurious errors there, it's not really a usable
scheme, and that seems hard.

On the other hand, modulo syntax details, creating partitions at
CREATE TABLE time seems relatively simple and, especially in the case
of hash partitioning, useful.

---

From: Justin Pryzby <[email-masked]>
Date: Tue, 20 Jul 2021 14:13:34 -0500

On Tue, Jul 20, 2021 at 02:42:16PM -0400, Robert Haas wrote:
> The bigger issue IMHO with on-the-fly

I was thinking that for dynamic creation, there would be a DDL command to
create the necessary partitions:

-- Creates 2021-01-02, unless the month already exists:
ALTER TABLE bydate SET GRANULARITY='1day';
ALTER TABLE bydate CREATE PARTITION FOR VALUE ('2021-01-02');

I'd want it to support changing the granularity of the range partitions:

-- Creates 2021-01 unless the month already exists.
-- Errors if a day partition already exists which would overlap?
ALTER TABLE bydate SET granularity='1month';
ALTER TABLE bydate CREATE PARTITION FOR VALUE ('2021-01-03');

It could support creating ranges, which might create multiple partitions,
depending on the granularity:

ALTER TABLE bydate CREATE PARTITION FOR VALUES ('2021-01-01') TO ('2021-02-01')

Or the catalog could include not only granularity, but also endpoints:

ALTER TABLE bydate SET ENDPOINTS ('2012-01-01') ('2022-01-01')
ALTER TABLE bydate CREATE PARTITIONS; --create anything needed to fill from a->b
ALTER TABLE bydate PRUNE PARTITIONS; --drop anything outside of [a,b]

I would use this to set "fine" granularity for large tables, and "course"
granularity for tables that were previously set to "fine" granularity, but its
partitions are no longer large enough to justify it.  This logic currently
exists in our application - we create partitions dynamically immediately before
inserting.  But it'd be nicer if it were created asynchronously.  It may create
tables which were never inserted into, which is fine - they'd be course
granularity tables (one per month).

I think this might elegantly allow both 1) subpartitioning; 2) repartitioning
to a different granularity (for which I currently have my own tool).

-- 
Justin

---

From: Robert Haas <[email-masked]>
Date: Tue, 20 Jul 2021 15:34:22 -0400

> ALTER TABLE bydate CREATE PARTITION FOR VALUE ('2021-01-02');

Well, that dodges the deadlock issue with doing it implicitly, but it
also doesn't seem to offer a lot of value over just creating the
partitions in a fully manual way. I mean you could just say:

CREATE TABLE bydate_2021_02_02 PARTITION OF bydate FOR VALUES FROM
('2021-01-02') TO ('2021-02-03');

It's longer, but it's not really that bad.

---

From: Daniel Gustafsson <[email-masked]>
Date: Thu, 2 Dec 2021 12:20:16 +0100

This thread has stalled since July with review comments unanswered, I'm marking
the patch Returned with Feedback.  Please feel free to resubmit when/if a new
patch is available.

--
Daniel Gustafsson		https://vmware.com/