Hi,
I found that thread (and the patch), but it seems to be pretty dead.
Patch didn't apply, due to gen_node_support.pl
Can I hope for a rebirth ?
I've made a rebased patch,in case of no response...
It's just the patch from
https://www.postgresql.org/message-id/calt9zeg9okz9-dv9yyzaeexnpzp0+telfsz7qst28acmerv...@mail.gmail.com
rebased on 17dev
Perhaps it's too early for a commit ; automatic range partitioning is still
missing and, according to
https://wiki.postgresql.org/wiki/Declarative_partitioning_improvements,
syntax is arguable.
If 'USING' it out of option (already a keyword for CREATE TABLE) and
'CONFIGURATION()' is not what we want, we should reach for a final decision
first.
I suggest OVER that is a keyword but unused in CREATE TABLE (nor ALTER
TABLE). Whatever...
For RANGE partitioning I think of four syntaxes (inspired by pg_partman)
PARTITION BY RANGE(stamp) CONFIGURATION (SPAN interval CENTER datetime BACK
integer AHEAD integer [DEFAULT [PARTITION] [defname]])
PARTITION BY RANGE(stamp) CONFIGURATION (SPAN interval
START firstfrombound END lasttobound [DEFAULT [PARTITION] [defname]])
PARTITION BY RANGE(region_id) CONFIGURATION (STEP integer START integer END
integer [DEFAULT [PARTITION] [defname]])
PARTITION BY RANGE(name) CONFIGURATION (BOUNDS (boundlist) [START
firstfrombound] [END lasttobound] [DEFAULT [PARTITION] [defname]])
Last one should solve the addition operator problem with non numeric non
timedate range.
Plus, it allows non uniform range (thinking about an "encyclopedia volume"
partitioning, you know 'A', 'B-CL', 'CL-D'...)
CREATE table (LIKE other INCLUDING PARTITIONS) should create 'table'
partitioned the same as 'other'
and
CREATE table (LIKE other INCLUDING PARTITIONS) PARTITION BY partspec
CONFIGURATION(), should create 'table' partitioned by partspec and sub
partitioned as 'other'.
Then CREATE could accept multiple PARTITION BY CONFIGURATION().
For ALTER TABLE (and automatic maintenance) to be usable, we will need
SPLIT and MERGE CONCURRENTLY (pg_pathman ?) enhanced by CREATE TABLE LIKE
to handle subpartitioning. But that's another story.
Stéphane.
Le jeu. 2 déc. 2021 à 12:20, Daniel Gustafsson <dan...@yesql.se> a écrit :
> 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/
>
>
>
>
--
"Où se posaient les hirondelles avant l'invention du téléphone ?"
-- Grégoire Lacroix
From ff875672556a2da09cfcc6c976178abd91c74622 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?St=C3=A9phane=20Tachoires?= <sttachoi...@airfrance.fr>
Date: Mon, 17 Jul 2023 13:25:29 +0200
Subject: [PATCH v7] This is a simple rebase on most recent 17dev. Due to lack
of answer from author Pavel Borisov <pashkin.e...@gmail.com>
Previous meaningfull commit from author was:
From ef0fcba607641aef94a9ce9b0393e607ba476ba4 Mon Sep 17 00:00:00 2001
From: Pavel Borisov <pashkin.e...@gmail.com>
Date: Fri, 9 Jul 2021 00:34:21 +0400
Subject: [PATCH v5] Automatically generate partitions by LIST and HASH
A patch adds CREATE TABLE statement syntax and functions to create both
a parent partitioned table and child partitions at once based on a
partitioning rules. The created partitions set can be manipulated using
existing expressions like DETACH, CREATE TABLE.. PARTITION OF etc.
It is the first step towards more complicated:
(1) partitioning BY RANGE,
(2) automatic deferred child partition creation on a first try to
insert a row with which fullfills a condition for the partition etc.
---
doc/src/sgml/ref/create_table.sgml | 49 +++
src/backend/parser/gram.y | 81 +++-
src/backend/parser/parse_utilcmd.c | 142 +++++++
src/include/nodes/parsenodes.h | 23 +
src/include/partitioning/partdefs.h | 2 +
src/test/regress/expected/create_table.out | 464 +++++++++++++++++++++
src/test/regress/sql/create_table.sql | 265 ++++++++++++
7 files changed, 1024 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 10ef699fab..5a87fa1a7e 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> [, ... ] ) ]
@@ -447,6 +455,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.
@@ -455,6 +468,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/parser/gram.y b/src/backend/parser/gram.y
index edb6c00ece..af650d6235 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -270,6 +270,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
PartitionElem *partelem;
PartitionSpec *partspec;
PartitionBoundSpec *partboundspec;
+ PartitionBoundAutoSpec *partboundautospec;
RoleSpec *rolespec;
PublicationObjSpec *publicationobjectspec;
struct SelectLimit *selectlimit;
@@ -659,6 +660,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
json_object_constructor_null_clause_opt
json_array_constructor_null_clause_opt
+%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.
@@ -4377,14 +4380,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 = parsePartitionStrategy($3);
n->partParams = $5;
n->location = @1;
-
+ n->autopart = (Node *) $7;
$$ = n;
}
;
@@ -4428,6 +4431,80 @@ part_elem: ColId opt_collate opt_qualified_name
}
;
+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 e48e9e99d3..39c9383caa 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -72,6 +72,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 */
@@ -141,6 +142,8 @@ static Const *transformPartitionBoundValue(ParseState *pstate, Node *val,
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 -
@@ -234,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;
@@ -341,6 +345,10 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
*/
transformExtendedStatistics(&cxt);
+ /* Process partition definitions */
+ if (stmt->partspec && stmt->partspec->autopart)
+ transformPartitionAutoCreate(&cxt, stmt->partspec);
+
/*
* Output results.
*/
@@ -4358,3 +4366,137 @@ 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 (partspec->strategy == PARTITION_STRATEGY_HASH)
+ {
+ 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++)
+ {
+ /*
+ * Generate partition name in the format:
+ * $relname_$partnum
+ * All checks of name validity will be made afterwards in DefineRelation()
+ */
+ part = initAutoPartitionCreateStmt(cxt, psprintf("%s_%d", cxt->relation->relname, i));
+
+ /* 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 (partspec->strategy == PARTITION_STRATEGY_LIST)
+ {
+
+ 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++)
+ {
+ List *listdatums = (List *)
+ list_nth(bound->listdatumsList, i);
+
+ part = initAutoPartitionCreateStmt(cxt, psprintf("%s_%d", cxt->relation->relname, i));
+
+ /* 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)
+ {
+ part = initAutoPartitionCreateStmt(cxt, bound->default_partition_rv->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/parsenodes.h b/src/include/nodes/parsenodes.h
index efb5c3e098..a486a3c4d9 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -879,6 +879,9 @@ typedef struct PartitionSpec
PartitionStrategy strategy;
List *partParams; /* List of PartitionElems */
int location; /* token location, or -1 if unknown */
+
+ Node *autopart; /* PartitionBoundAutoSpec -
+ * spec to generate bounds automatically */
} PartitionSpec;
/*
@@ -908,6 +911,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 55bb49c816..cfbbcab9e0 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 2a0902ece2..0d1a1d3b0e 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -1110,3 +1110,467 @@ Indexes:
"part_column_drop_1_10_expr_idx1" btree ((d = 2))
drop table part_column_drop;
+-- Auto generated partitions
+-- partitioning type not specified
+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);
+ ^
+-- must fail because of wrong configuration
+CREATE TABLE fail_part (a int) PARTITION BY HASH (a) CONFIGURATION
+(values in (1, 2), (3, 4) DEFAULT PARTITION part_default);
+ERROR: invalid bound specification for a hash partition
+LINE 1: CREATE TABLE fail_part (a int) PARTITION BY HASH (a) CONFIGU...
+ ^
+-- forbidden expressions for partition bound with list partitioned table
+CREATE TABLE fail_parted (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 fail_parted (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 fail_parted (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 fail_parted (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 fail_parted (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 fail_parted (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 fail_parted (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 fail_parted (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 fail_parted (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 fail_parted (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 fail_parted (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 fail_parted (a int) PARTITION BY LIST (a) CONFIGURATION
+(MODULUS 10);
+ERROR: invalid bound specification for a list partition
+LINE 1: CREATE TABLE fail_parted (a int) PARTITION BY LIST (a) CONFI...
+ ^
+-- specified literal can't be cast to the partition column data type
+CREATE TABLE fail_parted (a bool) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (1));
+ERROR: specified value cannot be cast to type boolean for column "a"
+LINE 2: (VALUES IN (1));
+ ^
+-- check for partition bound overlap and other invalid specifications
+CREATE TABLE fail_parted (a varchar) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (null, 'z'),('a', 'b'),(null) DEFAULT PARTITION part_default);
+ERROR: partition "fail_parted_2" would overlap partition "fail_parted_0"
+LINE 2: (VALUES IN (null, 'z'),('a', 'b'),(null) DEFAULT PARTITION p...
+ ^
+CREATE TABLE fail_parted (a varchar) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (null, 'z'),('a', 'b'),('b', 'c') DEFAULT PARTITION part_default);
+ERROR: partition "fail_parted_2" would overlap partition "fail_parted_1"
+LINE 2: (VALUES IN (null, 'z'),('a', 'b'),('b', 'c') DEFAULT PARTITI...
+ ^
+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));
+ ^
+-- trying to create default partition for the hash partitioned table
+REATE TABLE fail_parted (a int) PARTITION BY HASH (a) CONFIGURATION
+(MODULUS 10 DEFAULT PARTITION hash_default);
+ERROR: syntax error at or near "REATE"
+LINE 1: REATE TABLE fail_parted (a int) PARTITION BY HASH (a) CONFIG...
+ ^
+CREATE TABLE list_parted (a int) PARTITION BY LIST (a) CONFIGURATION
+(values in (1, 2), (3, 4) 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, 2),
+ list_parted_1 FOR VALUES IN (3, 4),
+ part_default DEFAULT
+
+DROP TABLE list_parted;
+CREATE TABLE hash_parted (a int) PARTITION BY HASH (a) CONFIGURATION
+(modulus 3);
+\d+ hash_parted
+ Partitioned table "public.hash_parted"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | | | plain | |
+Partition key: HASH (a)
+Partitions: hash_parted_0 FOR VALUES WITH (modulus 3, remainder 0),
+ hash_parted_1 FOR VALUES WITH (modulus 3, remainder 1),
+ hash_parted_2 FOR VALUES WITH (modulus 3, remainder 2)
+
+DROP TABLE hash_parted;
+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
+
+DROP TABLE list_parted;
+-- 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;
+-- 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);
+-- Because \d+ will issue a pg_temp_nn that .ou will never match
+SELECT * FROM (
+ WITH RECURSIVE ancestor(name) AS (SELECT c.relname AS "name" FROM pg_catalog.pg_class c WHERE c.relkind = 'p')
+ SELECT a.relname AS "parent",
+ 'PARENT' AS "type",
+ ancest.name AS "name",
+ pg_get_partkeydef(p.oid) AS "range"
+ FROM ancestor ancest
+ JOIN pg_catalog.pg_class p ON p.relname = ancest.name
+ LEFT JOIN pg_catalog.pg_inherits i ON i.inhrelid = p.oid
+ LEFT JOIN pg_catalog.pg_class a ON a.oid = i.inhparent
+ JOIN pg_catalog.pg_namespace n ON n.oid = p.relnamespace
+ WHERE p.relkind IN ('p', 'r')
+ UNION ALL
+ SELECT parent.relname AS "parent",
+ 'CHILD' AS "type",
+ child.relname AS "name",
+ pg_get_expr(child.relpartbound, child.oid) AS "range"
+ FROM ancestor ancest
+ JOIN pg_catalog.pg_class parent ON parent.relname = ancest.name
+ JOIN pg_catalog.pg_inherits inh ON inh.inhparent = parent.oid
+ JOIN pg_catalog.pg_class child ON child.oid = inh.inhrelid
+ JOIN pg_catalog.pg_namespace namespace ON namespace.oid = child.relnamespace
+ WHERE parent.relkind IN ('p', 'r')
+ ) AS dp
+WHERE
+ "parent" = 'temp_parted'
+ORDER BY "parent" NULLS FIRST, "type" DESC, "name";
+ parent | type | name | range
+-------------+-------+---------------------+---------------------
+ temp_parted | CHILD | temp_parted_0 | FOR VALUES IN ('a')
+ temp_parted | CHILD | temp_parted_default | DEFAULT
+(2 rows)
+
+DROP TABLE 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
+ 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
+
+DROP TABLE unlogged_parted;
+-- testing PARTITION OF on automatically generated partitioned table
+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 add default partition to hash partitioned table
+CREATE TABLE fail_part PARTITION OF hash_parted DEFAULT;
+ERROR: a hash-partitioned table may not have a default partition
+\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.)
+
+DROP TABLE hash_parted;
+-- 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;
+-- check default partition overlap
+CREATE TABLE list_parted (a varchar) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (null, 'z'),('a', 'b') DEFAULT partition tbl_default);
+INSERT INTO list_parted VALUES('X');
+CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES IN ('W', 'X', 'Y');
+ERROR: updated partition constraint for default partition "tbl_default" would be violated by some row
+-- trying to create already existing default partition
+CREATE TABLE fail_part PARTITION OF list_parted DEFAULT;
+ERROR: partition "fail_part" conflicts with existing default partition "tbl_default"
+LINE 1: CREATE TABLE fail_part PARTITION OF list_parted DEFAULT;
+ ^
+DROP TABLE list_parted;
+-- 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: ...RTITION 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.)
+
+DROP TABLE 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 82ada47661..48d7686c90 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -730,3 +730,268 @@ 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
+-- partitioning type not specified
+CREATE TABLE fail_part (a int) CONFIGURATION (MODULUS 10);
+
+-- must fail because of wrong configuration
+CREATE TABLE fail_part (a int) PARTITION BY HASH (a) CONFIGURATION
+(values in (1, 2), (3, 4) DEFAULT PARTITION part_default);
+
+-- forbidden expressions for partition bound with list partitioned table
+CREATE TABLE fail_parted (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (somename));
+CREATE TABLE fail_parted (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (somename.somename));
+CREATE TABLE fail_parted (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (a));
+CREATE TABLE fail_parted (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (sum(a)));
+CREATE TABLE fail_parted (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (sum(somename)));
+CREATE TABLE fail_parted (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (sum(1)));
+CREATE TABLE fail_parted (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN ((select 1)));
+CREATE TABLE fail_parted (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (generate_series(4, 6)));
+CREATE TABLE fail_parted (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 fail_parted (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN ());
+-- trying to specify range for list partitioned table
+CREATE TABLE fail_parted (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES FROM (1) TO (2));
+-- trying to specify modulus and remainder for list partitioned table
+CREATE TABLE fail_parted (a int) PARTITION BY LIST (a) CONFIGURATION
+(MODULUS 10);
+-- specified literal can't be cast to the partition column data type
+CREATE TABLE fail_parted (a bool) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (1));
+
+-- check for partition bound overlap and other invalid specifications
+CREATE TABLE fail_parted (a varchar) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (null, 'z'),('a', 'b'),(null) DEFAULT PARTITION part_default);
+
+CREATE TABLE fail_parted (a varchar) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (null, 'z'),('a', 'b'),('b', 'c') DEFAULT PARTITION part_default);
+
+CREATE TABLE fail_parted (a int) PARTITION BY LIST (a) CONFIGURATION
+(values in (1, 2), (1, 3));
+
+-- trying to create default partition for the hash partitioned table
+REATE TABLE fail_parted (a int) PARTITION BY HASH (a) CONFIGURATION
+(MODULUS 10 DEFAULT PARTITION hash_default);
+
+CREATE TABLE list_parted (a int) PARTITION BY LIST (a) CONFIGURATION
+(values in (1, 2), (3, 4) DEFAULT PARTITION part_default);
+\d+ list_parted
+DROP TABLE list_parted;
+
+CREATE TABLE hash_parted (a int) PARTITION BY HASH (a) CONFIGURATION
+(modulus 3);
+\d+ hash_parted
+DROP TABLE hash_parted;
+
+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
+DROP TABLE list_parted;
+
+-- 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;
+
+-- 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);
+
+-- Because \d+ will issue a pg_temp_nn that .ou will never match
+SELECT * FROM (
+ WITH RECURSIVE ancestor(name) AS (SELECT c.relname AS "name" FROM pg_catalog.pg_class c WHERE c.relkind = 'p')
+ SELECT a.relname AS "parent",
+ 'PARENT' AS "type",
+ ancest.name AS "name",
+ pg_get_partkeydef(p.oid) AS "range"
+ FROM ancestor ancest
+ JOIN pg_catalog.pg_class p ON p.relname = ancest.name
+ LEFT JOIN pg_catalog.pg_inherits i ON i.inhrelid = p.oid
+ LEFT JOIN pg_catalog.pg_class a ON a.oid = i.inhparent
+ JOIN pg_catalog.pg_namespace n ON n.oid = p.relnamespace
+ WHERE p.relkind IN ('p', 'r')
+ UNION ALL
+ SELECT parent.relname AS "parent",
+ 'CHILD' AS "type",
+ child.relname AS "name",
+ pg_get_expr(child.relpartbound, child.oid) AS "range"
+ FROM ancestor ancest
+ JOIN pg_catalog.pg_class parent ON parent.relname = ancest.name
+ JOIN pg_catalog.pg_inherits inh ON inh.inhparent = parent.oid
+ JOIN pg_catalog.pg_class child ON child.oid = inh.inhrelid
+ JOIN pg_catalog.pg_namespace namespace ON namespace.oid = child.relnamespace
+ WHERE parent.relkind IN ('p', 'r')
+ ) AS dp
+WHERE
+ "parent" = 'temp_parted'
+ORDER BY "parent" NULLS FIRST, "type" DESC, "name";
+
+
+
+DROP TABLE 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
+DROP TABLE unlogged_parted;
+
+-- testing PARTITION OF on automatically generated partitioned table
+
+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 add default partition to hash partitioned table
+CREATE TABLE fail_part PARTITION OF hash_parted DEFAULT;
+\d hash_parted
+DROP TABLE hash_parted;
+
+-- 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;
+
+-- check default partition overlap
+CREATE TABLE list_parted (a varchar) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (null, 'z'),('a', 'b') DEFAULT partition tbl_default);
+INSERT INTO list_parted VALUES('X');
+CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES IN ('W', 'X', 'Y');
+-- trying to create already existing default partition
+CREATE TABLE fail_part PARTITION OF list_parted DEFAULT;
+DROP TABLE list_parted;
+
+-- 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
+DROP TABLE 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;
--
2.34.1
