Proposal for syntax to support creation of partition tables when creating parent table
Hi Hackers,
I want to propose an extension to CREATE TABLE syntax to allow the creation
of partition tables along with its parent table using a single statement.
In this proposal, I am proposing to specify the list of partitioned tables
after the PARTITION BY clause.
CREATE TABLE table_name (..)
PARTITION BY { RANGE | LIST | HASH } (..)
(
list of partitions
) ;
Below are a few examples of the proposed syntax, in a nutshell, I am
leveraging the syntax currently supported by Postgres for creating
partitioned tables. The purpose of this proposal is to combine the creation
of the parent partition table and its partitions in one SQL statement.
CREATE TABLE Sales (salesman_id INT, salesman_name TEXT, sales_region TEXT,
hiring_date DATE, sales_amount INT )
PARTITION BY RANGE (hiring_date)
(
PARTITION part_one FOR VALUES FROM ('2008-02-01') TO ('2008-03-01'),
PARTITION part_two FOR VALUES FROM ('2009-02-01') TO ('2009-03-01'),
PARTITION part_def DEFAULT
);
CREATE TABLE Sales2 (salesman_id INT, salesman_name TEXT, sales_region
TEXT, hiring_date DATE, sales_amount INT )
PARTITION BY HASH (salesman_id)
(
PARTITION par_one FOR VALUES WITH (MODULUS 2, REMAINDER 0),
PARTITION par_two FOR VALUES WITH (MODULUS 2, REMAINDER 1)
);
CREATE TABLE Sales3(salesman_id INT, salesman_name TEXT, sales_region TEXT,
hiring_date DATE, sales_amount INT)
PARTITION BY LIST (sales_region)
(
PARTITION pt_one FOR VALUES IN ('JAPAN','CHINA'),
PARTITION pt_two FOR VALUES IN ('USA','CANADA'),
PARTITION pt_def DEFAULT
);
-- Similarly for specifying subpartitions of partitioned tables
CREATE TABLE All_Sales ( year INT, month INT, day INT, info TEXT)
PARTITION BY RANGE(year)(
PARTITION sale_2019_2020 FOR VALUES FROM (2019) TO (2021)
PARTITION BY LIST(month)
(
PARTITION sale_2019_2020_1 FOR VALUES IN (1,2,3,4)
PARTITION BY RANGE(day)(
PARTITION sale_2019_2020_1_1 FOR VALUES FROM (1) TO (10)
PARTITION BY HASH(info)
(
PARTITION sale_2019_2020_1_1_1 FOR VALUES WITH (MODULUS
2,REMAINDER 0),
PARTITION sale_2019_2020_1_1_2 FOR VALUES WITH (MODULUS
2,REMAINDER 1)
),
PARTITION sale_2019_2020_1_2 FOR VALUES FROM (10) TO (20),
PARTITION sale_2019_2020_1_3 FOR VALUES FROM (20) TO (32)),
PARTITION sale_2019_2020_2 FOR VALUES IN (5,6,7,8),
PARTITION sale_2019_2020_3 FOR VALUES IN (9,10,11,12)
),
PARTITION sale_2021_2022 FOR VALUES FROM (2021) TO (2023),
PARTITION sale_2023_2024 FOR VALUES FROM (2023) TO (2025),
PARTITION sale_default default
);
This new syntax requires minimal changes in the code. I along with my
colleague Movead.li have drafted a rough POC patch attached to this email.
Please note that the patch is just to showcase the new syntax and get a
consensus on the overall design and approach.
As far as I know, there are already few ongoing discussions related to the
partition syntax enhancements, but the proposed syntax will not interfere
with these ongoing proposals. Here is a link to one such discussion:
/messages/by-id/alpine.DEB.2.21.1907150711080.22273@lancre
Please feel free to share your thoughts.
Best Regards
...
Muhammad Usama
Highgo Software Canada
URL : http://www.highgo.ca
ADDR: 10318 WHALLEY BLVD, Surrey, BC
Attachments:
new_partition_syntax_poc.diffapplication/octet-stream; name=new_partition_syntax_poc.diffDownload
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 3432bb921d..ac961df791 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -4563,6 +4563,7 @@ _copyPartitionSpec(const PartitionSpec *from)
COPY_STRING_FIELD(strategy);
COPY_NODE_FIELD(partParams);
+ COPY_NODE_FIELD(partdefs);
COPY_LOCATION_FIELD(location);
return newnode;
@@ -4608,6 +4609,23 @@ _copyPartitionCmd(const PartitionCmd *from)
return newnode;
}
+static SubPartition *
+_copySubPartition(const SubPartition *from)
+{
+ SubPartition *newnode = makeNode(SubPartition);
+ /*
+ * TODO (Next Version)
+ * It supports the necessary options in our current version
+ * We should add something here for options support.
+ */
+ COPY_NODE_FIELD(name);
+ COPY_NODE_FIELD(bound);
+ COPY_STRING_FIELD(tablespacename);
+ COPY_NODE_FIELD(partspec);
+
+ return newnode;
+}
+
static CreatePublicationStmt *
_copyCreatePublicationStmt(const CreatePublicationStmt *from)
{
@@ -5634,6 +5652,9 @@ copyObjectImpl(const void *from)
case T_PartitionCmd:
retval = _copyPartitionCmd(from);
break;
+ case T_SubPartition:
+ retval = _copySubPartition(from);
+ break;
/*
* MISCELLANEOUS NODES
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 18cb014373..cc966d38a4 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2872,6 +2872,7 @@ _equalPartitionSpec(const PartitionSpec *a, const PartitionSpec *b)
{
COMPARE_STRING_FIELD(strategy);
COMPARE_NODE_FIELD(partParams);
+ COMPARE_NODE_FIELD(partdefs);
COMPARE_LOCATION_FIELD(location);
return true;
@@ -2911,6 +2912,21 @@ _equalPartitionCmd(const PartitionCmd *a, const PartitionCmd *b)
return true;
}
+static bool
+_equalSubPartition(const SubPartition *a, const SubPartition *b)
+{
+ /*
+ * TODO
+ * It just supports the necessary options currently for POC
+ */
+ COMPARE_NODE_FIELD(name);
+ COMPARE_NODE_FIELD(bound);
+ COMPARE_STRING_FIELD(tablespacename);
+ COMPARE_NODE_FIELD(partspec);
+
+ return true;
+}
+
/*
* Stuff from pg_list.h
*/
@@ -3730,6 +3746,9 @@ equal(const void *a, const void *b)
case T_PartitionCmd:
retval = _equalPartitionCmd(a, b);
break;
+ case T_SubPartition:
+ retval = _equalSubPartition(a, b);
+ break;
default:
elog(ERROR, "unrecognized node type: %d",
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 3f67aaf30e..9273bb6ea8 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -588,6 +588,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <list> part_params
%type <partboundspec> PartitionBoundSpec
%type <list> hash_partbound
+%type <list> table_partitions_list
+%type <node> table_partition
%type <defelt> hash_partbound_elem
/*
@@ -3870,6 +3872,18 @@ PartitionSpec: PARTITION BY part_strategy '(' part_params ')'
$$ = n;
}
+
+ | PARTITION BY part_strategy '(' part_params ')' '(' table_partitions_list ')'
+ {
+ PartitionSpec *n = makeNode(PartitionSpec);
+
+ n->strategy = $3;
+ n->partParams = $5;
+ n->partdefs = $8;
+ n->location = @1;
+
+ $$ = n;
+ }
;
part_strategy: IDENT { $$ = $1; }
@@ -3915,6 +3929,35 @@ part_elem: ColId opt_collate opt_class
}
;
+/*
+ * table_partitions_list returns the list of PartitionCmd
+ * for now, Latter we may want to use more specific data structure
+ * when we will introduce the shard server specification syntax.
+ */
+table_partitions_list:
+ table_partitions_list ',' table_partition
+ {
+ $$ = lappend($1, $3);
+ }
+
+ | table_partition
+ {
+ $$ = list_make1($1);
+ }
+ ;
+
+table_partition: PARTITION qualified_name PartitionBoundSpec OptTableSpace OptPartitionSpec
+ {
+ SubPartition *n = makeNode(SubPartition);
+
+ n->name = $2;
+ n->bound = $3;
+ n->tablespacename = $4;
+ n->partspec = $5;
+ $$ = n;
+ }
+ ;
+
table_access_method_clause:
USING access_method { $$ = $2; }
| /*EMPTY*/ { $$ = NULL; }
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 6e5768c66c..7fe7d38c7b 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -121,6 +121,10 @@ static void transformTableLikeClause(CreateStmtContext *cxt,
TableLikeClause *table_like_clause);
static void transformOfType(CreateStmtContext *cxt,
TypeName *ofTypename);
+static void transformSubPartitionDefinition(CreateStmtContext *cxt,
+ SubPartition *partition, RangeVar *parentrelation);
+static void transformSubPartitionList(CreateStmtContext *cxt, List *partlist, RangeVar *parentrelation);
+
static CreateStatsStmt *generateClonedExtStatsStmt(RangeVar *heapRel,
Oid heapRelid, Oid source_statsid);
static List *get_collation(Oid collation, Oid actual_datatype);
@@ -261,6 +265,14 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
ereport(ERROR,
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
errmsg("cannot create partitioned table as inheritance child")));
+ /*
+ * For handle the new syntax for create subpartition
+ * during create parent partition table.
+ */
+ if(stmt->partspec->partdefs)
+ {
+ transformSubPartitionList(&cxt, stmt->partspec->partdefs, cxt.relation);
+ }
}
/*
@@ -492,6 +504,66 @@ generateSerialExtraStmts(CreateStmtContext *cxt, ColumnDef *column,
*sname_p = sname;
}
+/*
+ * transformSubPartitionList -
+ * Handle the subpartition list, and the function body is in transformCreateStmt
+ * function at first, and it comes to a independent function because of to
+ * implement recursion subpartition.
+ */
+static void
+transformSubPartitionList(CreateStmtContext *cxt, List *partlist, RangeVar *parentrelation)
+{
+ ListCell *partielems = NULL;
+
+ //partlist = stmt->partspec->partdefs;
+ foreach(partielems, partlist)
+ {
+ Node *element = lfirst(partielems);
+ /*
+ * Set the subpartition create, into cxt.alist.
+ */
+ transformSubPartitionDefinition(cxt, (SubPartition *) element, parentrelation);
+ }
+}
+
+/*
+ * transformSubPartitionDefinition -
+ * transform a partition create within CREATE TABLE
+ */
+static void
+transformSubPartitionDefinition(CreateStmtContext *cxt, SubPartition *partition, RangeVar *relation)
+{
+ CreateStmt *stmt = NULL;
+
+ stmt = makeNode(CreateStmt);
+ stmt->relation = partition->name;
+ stmt->relation->relpersistence = RELPERSISTENCE_PERMANENT;
+ stmt->tableElts = NULL;
+ stmt->inhRelations = list_make1(relation);
+ stmt->partbound = partition->bound;
+ stmt->partspec = partition->partspec;
+ stmt->ofTypename = NULL;
+ stmt->constraints = NIL;
+ /*
+ * TODO
+ * For table_access_method_clause
+ */
+ stmt->accessMethod = NULL;
+ /*
+ * TODO
+ * For OptWith
+ */
+ stmt->options = NULL;
+ stmt->oncommit = ONCOMMIT_NOOP;
+ stmt->tablespacename = partition->tablespacename;
+ cxt->alist = lappend(cxt->alist, stmt);
+
+ if(partition->partspec)
+ {
+ transformSubPartitionList(cxt, partition->partspec->partdefs, partition->name);
+ }
+}
+
/*
* transformColumnDefinition -
* transform a single ColumnDef within CREATE TABLE
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index bce2d59b0d..582b6d0ae0 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -476,6 +476,7 @@ typedef enum NodeTag
T_PartitionBoundSpec,
T_PartitionRangeDatum,
T_PartitionCmd,
+ T_SubPartition,
T_VacuumRelation,
/*
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index d93a79a554..455224a4a9 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -791,6 +791,9 @@ typedef struct PartitionSpec
char *strategy; /* partitioning strategy ('hash', 'list' or
* 'range') */
List *partParams; /* List of PartitionElems */
+ List *partdefs; /* List of partition definations
+ * provided in the CREATE TABLE
+ */
int location; /* token location, or -1 if unknown */
} PartitionSpec;
@@ -859,6 +862,23 @@ typedef struct PartitionCmd
PartitionBoundSpec *bound; /* FOR VALUES, if attaching */
} PartitionCmd;
+/*
+ * Partition - info for subpartition which created during create parent table.
+ */
+typedef struct SubPartition
+{
+ /*
+ * TODO
+ * It supports the necessary options in our current version
+ * We should add something here for options support.
+ */
+ NodeTag type;
+ RangeVar *name; /* name of partition to attach/detach/create */
+ char *tablespacename; /* table space to use, or NULL */
+ PartitionBoundSpec *bound; /* FOR VALUES of creating subpartition*/
+ PartitionSpec *partspec; /* PARTITION BY clause */
+}SubPartition;
+
/****************************************************************************
* Nodes for a Query tree
****************************************************************************/
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index fb65265175..ceb65528fb 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1698,6 +1698,7 @@ PartitionBoundInfo
PartitionBoundInfoData
PartitionBoundSpec
PartitionCmd
+SubPartition
PartitionDesc
PartitionDescData
PartitionDirectory
Hello Muhammad,
I think that it may be better to have a partition spec which describes not
the list of partitions, but what is wanted, letting postgres to do some
more work.
See this thread:
/messages/by-id/alpine.DEB.2.21.1907150711080.22273@lancre
I want to propose an extension to CREATE TABLE syntax to allow the creation
of partition tables along with its parent table using a single statement.In this proposal, I am proposing to specify the list of partitioned tables
after the PARTITION BY clause.CREATE TABLE table_name (..)
PARTITION BY { RANGE | LIST | HASH } (..)
(
list of partitions
) ;
Below are a few examples of the proposed syntax, in a nutshell, I am
leveraging the syntax currently supported by Postgres for creating
partitioned tables. The purpose of this proposal is to combine the creation
of the parent partition table and its partitions in one SQL statement.CREATE TABLE Sales (salesman_id INT, salesman_name TEXT, sales_region TEXT,
hiring_date DATE, sales_amount INT )
PARTITION BY RANGE (hiring_date)
(
PARTITION part_one FOR VALUES FROM ('2008-02-01') TO ('2008-03-01'),
PARTITION part_two FOR VALUES FROM ('2009-02-01') TO ('2009-03-01'),
PARTITION part_def DEFAULT
);CREATE TABLE Sales2 (salesman_id INT, salesman_name TEXT, sales_region
TEXT, hiring_date DATE, sales_amount INT )
PARTITION BY HASH (salesman_id)
(
PARTITION par_one FOR VALUES WITH (MODULUS 2, REMAINDER 0),
PARTITION par_two FOR VALUES WITH (MODULUS 2, REMAINDER 1)
);CREATE TABLE Sales3(salesman_id INT, salesman_name TEXT, sales_region TEXT,
hiring_date DATE, sales_amount INT)
PARTITION BY LIST (sales_region)
(
PARTITION pt_one FOR VALUES IN ('JAPAN','CHINA'),
PARTITION pt_two FOR VALUES IN ('USA','CANADA'),
PARTITION pt_def DEFAULT
);-- Similarly for specifying subpartitions of partitioned tables
CREATE TABLE All_Sales ( year INT, month INT, day INT, info TEXT)
PARTITION BY RANGE(year)(
PARTITION sale_2019_2020 FOR VALUES FROM (2019) TO (2021)
PARTITION BY LIST(month)
(
PARTITION sale_2019_2020_1 FOR VALUES IN (1,2,3,4)
PARTITION BY RANGE(day)(
PARTITION sale_2019_2020_1_1 FOR VALUES FROM (1) TO (10)
PARTITION BY HASH(info)
(
PARTITION sale_2019_2020_1_1_1 FOR VALUES WITH (MODULUS
2,REMAINDER 0),
PARTITION sale_2019_2020_1_1_2 FOR VALUES WITH (MODULUS
2,REMAINDER 1)
),
PARTITION sale_2019_2020_1_2 FOR VALUES FROM (10) TO (20),
PARTITION sale_2019_2020_1_3 FOR VALUES FROM (20) TO (32)),
PARTITION sale_2019_2020_2 FOR VALUES IN (5,6,7,8),
PARTITION sale_2019_2020_3 FOR VALUES IN (9,10,11,12)
),
PARTITION sale_2021_2022 FOR VALUES FROM (2021) TO (2023),
PARTITION sale_2023_2024 FOR VALUES FROM (2023) TO (2025),
PARTITION sale_default default
);This new syntax requires minimal changes in the code. I along with my
colleague Movead.li have drafted a rough POC patch attached to this email.Please note that the patch is just to showcase the new syntax and get a
consensus on the overall design and approach.As far as I know, there are already few ongoing discussions related to the
partition syntax enhancements, but the proposed syntax will not interfere
with these ongoing proposals. Here is a link to one such discussion:
/messages/by-id/alpine.DEB.2.21.1907150711080.22273@lancrePlease feel free to share your thoughts.
Best Regards
...
Muhammad Usama
Highgo Software Canada
URL : http://www.highgo.ca
ADDR: 10318 WHALLEY BLVD, Surrey, BC
--
Fabien Coelho - CRI, MINES ParisTech
Muhammad Usama <m.usama@gmail.com> writes:
I want to propose an extension to CREATE TABLE syntax to allow the creation
of partition tables along with its parent table using a single statement.
TBH, I think this isn't a particularly good idea. It seems very
reminiscent of the variant of CREATE SCHEMA that lets you create
a bunch of contained objects along with the schema. That variant
is a mess to support and AFAIK it's practically unused in the
real world. (If it were used, we'd get requests to support more
than the small number of object types that the CREATE SCHEMA
grammar currently allows.)
As Fabien noted, there's been some related discussion about this
area, but nobody was advocating a solution of this particular shape.
regards, tom lane
On Wed, Sep 25, 2019 at 8:53 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Muhammad Usama <m.usama@gmail.com> writes:
I want to propose an extension to CREATE TABLE syntax to allow the
creation
of partition tables along with its parent table using a single statement.
TBH, I think this isn't a particularly good idea. It seems very
reminiscent of the variant of CREATE SCHEMA that lets you create
a bunch of contained objects along with the schema. That variant
is a mess to support and AFAIK it's practically unused in the
real world. (If it were used, we'd get requests to support more
than the small number of object types that the CREATE SCHEMA
grammar currently allows.)
IMO creating auto-partitions shouldn't be viewed as creating bunch of
schema objects with CREATE SCHEMA command. Most of the other RDBMS
solutions support the table partition syntax where parent partition table
is specified with partitions and sub-partitions in same SQL statement. As I
understand the proposal is not changing the syntax of creating partitions,
it is providing the ease of creating parent partition table along with its
partitions in same statement. I think it does make it easier when you are
creating a big partition table with lots of partitions and sub-partitions.
The would also benefit users migrating to postgres from Oracle or mysql etc
where similar syntax is supported.
And if not more I think it is a tick in the box with minimal code change.
As Fabien noted, there's been some related discussion about this
area, but nobody was advocating a solution of this particular shape.
The thread that Usama mentioned in his email is creating auto-partitions
just for HASH partitions, this is trying to do similar for all types of
partitions.
regards, tom lane
--
Highgo Software (Canada/China/Pakistan)
URL : http://www.highgo.ca
ADDR: 10318 WHALLEY BLVD, Surrey, BC
EMAIL: mailto: ahsan.hadi@highgo.ca
Hi Ahsan, Usama
Thanks for starting work on this.
On Thu, Sep 26, 2019 at 3:46 AM Ahsan Hadi <ahsan.hadi@gmail.com> wrote:
On Wed, Sep 25, 2019 at 8:53 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
As Fabien noted, there's been some related discussion about this
area, but nobody was advocating a solution of this particular shape.The thread that Usama mentioned in his email is creating auto-partitions just for HASH partitions, this is trying to do similar for all types of partitions.
I agree that this proposal makes life easier for developers familiar
with the partitioning syntax and features of other databases.
However, it adds little functionality over what users can already do,
even though today it takes multiple commands rather than just one.
The problem is that the syntax proposed here is still verbose because
users still have to spell out all the partition bounds by themselves.
The focus of the other thread, as I understand it, is to implement the
functionality to get the same thing done (create many partitions in
one command) in much less verbose manner. Fabien started the
discussion for hash partitioning because the interface for it seems
straightforward -- just specify the number of partitions and that many
partitions would get created without having to actually specify
modulus/remainder for each. Since the underlying functionality
wouldn't be too different for other partitioning methods, we would
only have to come up with a suitable interface.
Thanks,
Amit