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.
--
Anastasia Lubennikova
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
commit 4a387cfbd43e93b2c2e363307fb9c9ca53c3f56e
Author: anastasia <a.lubennik...@postgrespro.ru>
Date: Tue Oct 6 20:23:22 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);
With tests and documentation draft
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 28f844071b..4501e81bb5 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">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>
[ 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>
+ 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>
+ <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. 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.
+ </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)
COPY_STRING_FIELD(strategy);
COPY_NODE_FIELD(partParams);
+ COPY_NODE_FIELD(autopart);
COPY_LOCATION_FIELD(location);
return newnode;
@@ -4650,6 +4651,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)
{
@@ -5699,6 +5713,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 e2d1b987bf..ccba2471de 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2897,6 +2897,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;
}
@@ -2916,6 +2917,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)
{
@@ -3751,6 +3765,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 f0386480ab..95bf90a099 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -3644,6 +3644,7 @@ _outPartitionSpec(StringInfo str, const PartitionSpec *node)
WRITE_STRING_FIELD(strategy);
WRITE_NODE_FIELD(partParams);
+ WRITE_NODE_FIELD(autopart);
WRITE_LOCATION_FIELD(location);
}
@@ -3662,6 +3663,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)
{
@@ -4335,6 +4348,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 0d101d8171..e655ff858c 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;
}
@@ -601,6 +602,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
@@ -3904,14 +3908,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;
}
;
@@ -3955,6 +3959,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 0dc03dd984..ba5c694cdc 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,8 @@ static Const *transformPartitionBoundValue(ParseState *pstate, Node *con,
const char *colName, Oid colType, int32 colTypmod,
Oid partCollation);
+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;
/* Internal codes for partitioning strategies */
@@ -829,6 +832,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 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;
