Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kAVyO-0008Qh-Kk for pgsql-hackers@arkaria.postgresql.org; Tue, 25 Aug 2020 10:14:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kAVyN-0004ON-Hj for pgsql-hackers@arkaria.postgresql.org; Tue, 25 Aug 2020 10:14:35 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kAVyN-0004OG-4N for pgsql-hackers@lists.postgresql.org; Tue, 25 Aug 2020 10:14:35 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kAVyJ-0006OH-SO for pgsql-hackers@lists.postgresql.org; Tue, 25 Aug 2020 10:14:34 +0000 Received: from [192.168.1.64] (109-252-84-101.nat.spd-mgts.ru [109.252.84.101]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id B583B21C2FFB; Tue, 25 Aug 2020 13:14:29 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1598350470; bh=+bHzLN8po/EsJQAnXJfYCusnZP+lSAvHTjHhTEcS4fw=; h=Subject:From:To:Cc:References:Date:In-Reply-To; b=pcwRh0mdwk/qLMLuRheJMdcxv4JxSDeGlb43yx/0aJKF0d+uP7dEuIOhLeSiTxP+n cwk1K2Rkk/4QaT3hJPno8KMAraQbESincGZtwiwaqKw/CSnYEKfYleqocYUMu++ocf oN1dmghaJZUxNlJ5BFJw7Iwv/vZ8GNPYQsOmrtLc= Subject: [PATCH] Automatic HASH and LIST partition creation From: Anastasia Lubennikova To: PostgreSQL Hackers Cc: Fabien COELHO , Justin Pryzby , Robert Haas , Amul Sul References: <7fec3abb-c663-c0d2-8452-a46141be6d4a@postgrespro.ru> <7c4013db-a628-c8bd-ce54-064389109e0f@postgrespro.ru> Message-ID: <726c63e2-ef34-8113-f3cf-90e1646e4c2b@postgrespro.ru> Date: Tue, 25 Aug 2020 13:14:29 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.10.0 MIME-Version: 1.0 In-Reply-To: <7c4013db-a628-c8bd-ce54-064389109e0f@postgrespro.ru> Content-Type: multipart/mixed; boundary="------------C8FE200CC741A5A05B90D34E" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------C8FE200CC741A5A05B90D34E Content-Type: multipart/alternative; boundary="------------17D478057566BCB011ABEA89" --------------17D478057566BCB011ABEA89 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit On 14.07.2020 00:11, Anastasia Lubennikova wrote: > On 06.07.2020 13:45, Anastasia Lubennikova wrote: >> The previous discussion of automatic partition creation [1] has >> addressed static and dynamic creation of partitions and ended up with >> several syntax proposals. >> In this thread, I want to continue this work. >> >> ... >> [1] >> https://www.postgresql.org/message-id/flat/alpine.DEB.2.21.1907150711080.22273%40lancre > > Syntax proposal v2, that takes into account received feedback. > > CREATE TABLE numbers(int number) > PARTITION BY partition_method (list_of_columns) > USING (partition_desc) > > where partition_desc is: > > MODULUS n > | VALUES IN (value_list), [DEFAULT PARTITION part_name] > | START ([datatype] 'start_value') > END ([datatype] 'end_value') > EVERY (partition_step), [DEFAULT PARTITION part_name] > > where partition_step is: > [datatype] [number | INTERVAL] 'interval_value' > > It is less wordy than the previous version. It uses a free keyword option > style. It covers static partitioning for all methods, default > partition for > list and range methods, and can be extended to implement dynamic > partitioning > for range partitions. > > [1] > https://wiki.postgresql.org/wiki/Declarative_partitioning_improvements#Other_DBMS > [2] > https://wiki.postgresql.org/wiki/Declarative_partitioning_improvements#Proposal_.28is_subject_to_change.29 > Here is the patch for automated HASH and LIST partitioning, that implements proposed syntax. Range partitioning is more complicated. It will require new support function to calculate bounds, new catalog attribute to store them and so on. So I want to start small and implement automated range partitioning in a separate patch later. 1) Syntax New syntax is heavily based on Greenplum syntax for automated partitioning with one change. Keyword "USING", that was suggested above, causes shift/reduce conflict with "USING method" syntax of a table access method. It seems that Greenplum folks will face this problem later. I stick to CONFIGURATION as an existing keyword that makes sense in this context. Any better ideas are welcome. Thus, current version is: CREATE TABLE table_name (attrs) PARTITION BY partition_method (list_of_columns) CONFIGURATION (partition_desc) where partition_desc is: MODULUS n | VALUES IN (value_list) [DEFAULT PARTITION part_name] This syntax can be easily extended for range partitioning as well. 2) Implementation PartitionBoundAutoSpec is a new part of PartitionSpec, that contains information needed to generate partition bounds. For HASH and LIST automatic partition creation, transformation happens during parse analysis of CREATE TABLE statement. transformPartitionAutoCreate() calculates bounds and generates statements to create partition tables. Partitions are named in a format: $tablename_$partnum. One can use post create hook to rename relations. For LIST partition one can also define a default partition. 3) TODO The patch lacks documentation, because I expect some details may change during discussion. Other than that, the feature is ready for review. Regards -- Anastasia Lubennikova Postgres Professional: http://www.postgrespro.com The Russian Postgres Company --------------17D478057566BCB011ABEA89 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit
On 14.07.2020 00:11, Anastasia Lubennikova wrote:
On 06.07.2020 13:45, Anastasia Lubennikova wrote:
The previous discussion of automatic partition creation [1] has addressed static and dynamic creation of partitions and ended up with several syntax proposals.
In this thread, I want to continue this work.

...
[1] https://www.postgresql.org/message-id/flat/alpine.DEB.2.21.1907150711080.22273%40lancre

Syntax proposal v2, that takes into account received feedback.

CREATE TABLE numbers(int number)
PARTITION BY partition_method (list_of_columns)
USING (partition_desc)

where partition_desc is:

MODULUS n
| VALUES IN (value_list), [DEFAULT PARTITION part_name]
| START ([datatype] 'start_value')
  END ([datatype] 'end_value')
  EVERY (partition_step), [DEFAULT PARTITION part_name]

where partition_step is:
[datatype] [number | INTERVAL] 'interval_value'

It is less wordy than the previous version. It uses a free keyword option
style. It covers static partitioning for all methods, default partition for
list and range methods, and can be extended to implement dynamic partitioning
for range partitions.

[1] https://wiki.postgresql.org/wiki/Declarative_partitioning_improvements#Other_DBMS
[2] https://wiki.postgresql.org/wiki/Declarative_partitioning_improvements#Proposal_.28is_subject_to_change.29

Here is the patch for automated HASH and LIST partitioning, that implements proposed syntax.

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

1) Syntax

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

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

Thus, current version is:

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

where partition_desc is:

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

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

2) Implementation

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

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

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

For LIST partition one can also define a default partition.

3) TODO

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


Regards

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