public inbox for [email protected]help / color / mirror / Atom feed
Proposal: Automatic partition creation 12+ messages / 6 participants [nested] [flat]
* Proposal: Automatic partition creation @ 2020-07-06 10:45 Anastasia Lubennikova <[email protected]> 0 siblings, 4 replies; 12+ messages in thread From: Anastasia Lubennikova @ 2020-07-06 10:45 UTC (permalink / raw) To: PostgreSQL Hackers <[email protected]> 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. Attached is PoC for static partition creation. The patch core is quite straightforward. It adds one more transform clause to convert given partitioning specification into several CREATE TABLE statements. The patch implements following syntax: CREATE TABLE ... PARTITION BY partition_method (list_of_columns) partition_auto_create_clause where partition_auto_create_clause is CONFIGURATION [IMMEDIATE| DEFERRED] USING partition_bound_spec and partition_bound_spec is: MODULUS integer | VALUES IN (expr [,...]) [, ....] | INTERVAL range_step FROM range_start TO range_end For more examples check auto_partitions.sql in the patch. TODO: - CONFIGURATION is just an existing keyword, that I picked as a stub. Ideas on better wording are welcome. - IMMEDIATE| DEFERRED is optional, DEFERRED is not implemented yet I wonder, is it worth placing a stub for dynamic partitioning, or we can rather add these keywords later. - HASH and LIST static partitioning works as expected. Testing and feedback are welcome. - RANGE partitioning is not really implemented in this patch. Now it only accepts interval data type as 'interval' and respectively date types as range_start and range_end expressions. Only one partition is created. I found it difficult to implement the generation of bounds using internal functions and data types. Both existing solutions (pg_pathman and pg_partman) rely on SQL level routines [2]. I am going to implement this via SPI, which allow to simplify checks and calculations. Do you see any pitfalls in this approach? - Partition naming. Now partition names for all methods look like $tablename_$partnum Do we want more intelligence here? Now we have RunObjectPostCreateHook(), which allows to rename the table. To make it more user-friendly, we can later implement pl/pgsql function that sets the callback, as it is done in pg_pathman set_init_callback() [3]. - Current design doesn't allow to create default partition automatically. Do we need this functionality? - Do you see any restrictions for future extensibility (dynamic partitioning, init_callback, etc.) in the proposed design ? I expect this to be a long discussion, so here is the wiki page [4] to fix important questions and final agreements. [1] https://www.postgresql.org/message-id/flat/alpine.DEB.2.21.1907150711080.22273%40lancre [2] https://github.com/postgrespro/pg_pathman/blob/dbcbd02e411e6acea6d97f572234746007979538/range.sql#L9... [3] https://github.com/postgrespro/pg_pathman#additional-parameters [4] https://wiki.postgresql.org/wiki/Declarative_partitioning_improvements -- Anastasia Lubennikova Postgres Professional: http://www.postgrespro.com The Russian Postgres Company ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Proposal: Automatic partition creation @ 2020-07-06 14:59 Justin Pryzby <[email protected]> parent: Anastasia Lubennikova <[email protected]> 3 siblings, 1 reply; 12+ messages in thread From: Justin Pryzby @ 2020-07-06 14:59 UTC (permalink / raw) To: Anastasia Lubennikova <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> On Mon, Jul 06, 2020 at 01:45:52PM +0300, 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. ... > where partition_auto_create_clause is > > CONFIGURATION [IMMEDIATE| DEFERRED] USING partition_bound_spec > - IMMEDIATE| DEFERRED is optional, DEFERRED is not implemented yet > I wonder, is it worth placing a stub for dynamic partitioning, or we can > rather add these keywords later. I understand by "deferred" you mean that the partition isn't created at the time CREATE TABLE is run but rather deferred until needed by INSERT. For deferred, range partitioned tables, I think maybe what you'd want to specify (and store) is the INTERVAL. If the table is partitioned by day, then we'd date_trunc('day', time) and dynamically create that day. But if it was partitioned by month, we'd create the month. I think you'd want to have an ALTER command for that (we would use that to change tables between daily/monthly based on their current size). That should also support setting the MODULUS of a HASH partitioned table, to allow changing the size of its partitions (currently, the user would have to more or less recreate the table and move all its data into different partitions, but that's not ideal). I don't know if it's important for anyone, but it would be interesting to think about supporting sub-partitioning: partitions which are themselvese partitioned. Like something => something_YYYY => something_YYYY_MM => something_YYYY_MM_DD. You'd need to specify how to partition each layer of the heirarchy. In the most general case, it could be different partition strategy. If you have a callback function for partition renaming, I think you'd want to pass it not just the current name of the partition, but also the "VALUES" used in partition creation. Like (2020-04-05)TO(2020-05-06). Maybe instead, we'd allow setting a "format" to use to construct the partition name. Like "child.foo_bar_%Y_%m_%d". Ideally, the formats would be fixed-length (zero-padded, etc), so failures with length can happen at "parse" time of the statement and not at "run" time of the creation. You'd still have to handle the case that the name already exists but isn't a partition (or is a partition by doesn't handle the incoming tuple for some reason). Also, maybe your "configuration" syntax would allow specifying other values. Maybe including a retention period (as an INTERVAL for RANGE tables). That's useful if you had a command to PRUNE the oldest partitions, like ALTER..PRUNE. -- Justin ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Proposal: Automatic partition creation @ 2020-07-06 15:45 Robert Haas <[email protected]> parent: Anastasia Lubennikova <[email protected]> 3 siblings, 1 reply; 12+ messages in thread From: Robert Haas @ 2020-07-06 15:45 UTC (permalink / raw) To: Anastasia Lubennikova <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> On Mon, Jul 6, 2020 at 6:46 AM Anastasia Lubennikova <[email protected]> wrote: > CREATE TABLE ... PARTITION BY partition_method (list_of_columns) > partition_auto_create_clause > > where partition_auto_create_clause is > > CONFIGURATION [IMMEDIATE| DEFERRED] USING partition_bound_spec > > and partition_bound_spec is: > > MODULUS integer | VALUES IN (expr [,...]) [, ....] | INTERVAL > range_step FROM range_start TO range_end Might be good to compare this to what other databases support. > - IMMEDIATE| DEFERRED is optional, DEFERRED is not implemented yet > I wonder, is it worth placing a stub for dynamic partitioning, or we can > rather add these keywords later. I think we should not add any keywords we don't need immediately - and should seek to minimize the number of new keywords that we need to add, though compatibility with other implementations might be a good reason for accepting some new ones. > - HASH and LIST static partitioning works as expected. > Testing and feedback are welcome. > > - RANGE partitioning is not really implemented in this patch. > Now it only accepts interval data type as 'interval' and respectively > date types as range_start and range_end expressions. > Only one partition is created. I found it difficult to implement the > generation of bounds using internal functions and data types. > Both existing solutions (pg_pathman and pg_partman) rely on SQL level > routines [2]. > I am going to implement this via SPI, which allow to simplify checks and > calculations. Do you see any pitfalls in this approach? I don't really see why we need SPI here. Why can't we just try to evaluate the impression and see if we get a constant of the right type, then use that? I think the big problem here is identifying the operator to use. We have no way of identifying the "plus" or "minus" operator associated with a datatype; indeed, that constant doesn't exist. So either we (a) limit this to a short list of data types and hard-code the operators to be used (which is kind of sad given how extensible our type system is) or we (b) invent some new mechanism for identifying the +/- operators that should be used for a datatype, which was also proposed in the context of some previous discussion of window framing options, but which I don't think ever went anywhere (which is a lot of work) or we (c) just look for operators called '+' and/or '-' by operator name (which will probably make Tom throw up in his mouth a little). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Proposal: Automatic partition creation @ 2020-07-06 16:10 Tom Lane <[email protected]> parent: Robert Haas <[email protected]> 0 siblings, 2 replies; 12+ messages in thread From: Tom Lane @ 2020-07-06 16:10 UTC (permalink / raw) To: Robert Haas <[email protected]>; +Cc: Anastasia Lubennikova <[email protected]>; PostgreSQL Hackers <[email protected]> Robert Haas <[email protected]> writes: > On Mon, Jul 6, 2020 at 6:46 AM Anastasia Lubennikova > <[email protected]> wrote: >> I am going to implement this via SPI, which allow to simplify checks and >> calculations. Do you see any pitfalls in this approach? > I don't really see why we need SPI here. I would vote against any core facility that is implemented via SPI queries. It is just too darn hard to control the semantics completely in the face of fun stuff like varying search_path. Look at what a mess the queries generated by the RI triggers are --- and they only have a very small set of behaviors to worry about. I'm still only about 95% confident they don't have security issues, too. If you're using SPI to try to look up appropriate operators, I think the chances of being vulnerable to security problems are 100%. > I think the big problem here is identifying the operator to use. We > have no way of identifying the "plus" or "minus" operator associated > with a datatype; indeed, that constant doesn't exist. We did indeed solve this in connection with window functions, cf 0a459cec9. I may be misunderstanding what the problem is here, but I think trying to reuse that infrastructure might help. regards, tom lane ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Proposal: Automatic partition creation @ 2020-07-07 14:22 Robert Haas <[email protected]> parent: Tom Lane <[email protected]> 1 sibling, 1 reply; 12+ messages in thread From: Robert Haas @ 2020-07-07 14:22 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Anastasia Lubennikova <[email protected]>; PostgreSQL Hackers <[email protected]> On Mon, Jul 6, 2020 at 12:10 PM Tom Lane <[email protected]> wrote: > We did indeed solve this in connection with window functions, cf > 0a459cec9. I may be misunderstanding what the problem is here, > but I think trying to reuse that infrastructure might help. Ah, nice. I didn't realize that we'd added that. But I'm not sure that it helps here, because I think we need to compute the end of the range, not just test whether something is in a range. Like, if someone wants monthly range partitions starting on 2020-01-01, we need to be able to figure out that the subsequent months start on 2020-02-01, 2020-03-01, 2020-04-01, etc. Is there a way to use in_range to achieve that? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Proposal: Automatic partition creation @ 2020-07-07 15:09 Tom Lane <[email protected]> parent: Robert Haas <[email protected]> 0 siblings, 0 replies; 12+ messages in thread From: Tom Lane @ 2020-07-07 15:09 UTC (permalink / raw) To: Robert Haas <[email protected]>; +Cc: Anastasia Lubennikova <[email protected]>; PostgreSQL Hackers <[email protected]> Robert Haas <[email protected]> writes: > On Mon, Jul 6, 2020 at 12:10 PM Tom Lane <[email protected]> wrote: >> We did indeed solve this in connection with window functions, cf >> 0a459cec9. I may be misunderstanding what the problem is here, >> but I think trying to reuse that infrastructure might help. > Ah, nice. I didn't realize that we'd added that. But I'm not sure that > it helps here, because I think we need to compute the end of the > range, not just test whether something is in a range. Yeah, I was thinking about that later, and I agree that the in_range support function doesn't quite do the job. But we could expand on the principle, and register addition (and subtraction?) functions as btree support functions under the same rules as for in_range functions. The reason in_range isn't just addition is that we wanted it to be able to give correct answers even in cases where addition would overflow. That's still valid for that use-case, but it doesn't apply here. So it'd be something like "btree support function 4, registered under amproclefttype x and amprocrighttype y, must have the signature plus(x, y) returns x and it gives results compatible with the opfamily's ordering of type x". Similarly for subtraction if we think we need that. I'm not sure if we need a formal notion of what "compatible results" means, but it probably would be something like "if x < z according to the opfamily sort ordering, then plus(x, y) < plus(z, y) for any given y". Now this falls to the ground when y is a weird value like Inf or NaN, but we'd want to exclude those as partitioning values anyway. Do we also need some datatype-independent way of identifying such "weird values"? regards, tom lane ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Proposal: Automatic partition creation @ 2020-07-08 04:53 Fabien COELHO <[email protected]> parent: Anastasia Lubennikova <[email protected]> 3 siblings, 1 reply; 12+ messages in thread From: Fabien COELHO @ 2020-07-08 04:53 UTC (permalink / raw) To: Anastasia Lubennikova <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> Hello Anastasia, My 0.02 €: > The patch implements following syntax: > > CREATE TABLE ... PARTITION BY partition_method (list_of_columns) > partition_auto_create_clause > > where partition_auto_create_clause is > > CONFIGURATION [IMMEDIATE| DEFERRED] USING partition_bound_spec > > and partition_bound_spec is: > > MODULUS integer | VALUES IN (expr [,...]) [, ....] | INTERVAL range_step > FROM range_start TO range_end ISTM That we should avoid new specific syntaxes when possible, and prefer free keyword option style, like it is being discussed for some other commands, because it reduces the impact on the parser. That would suggest a more versatile partition_bound_spec which could look like (<keyword> <constant-or-maybe-even-expr>[, …]): For modulus, looks easy: (MODULUS 8) For interval, maybe something like: (STEP ..., FROM/START ..., TO/END ...) The key point is that for dynamic partitioning there would be no need for boundaries, so that it could just set a point and an interval (START/INIT/FROM??? ..., STEP ...) For lists of values, probably it would make little sense to have dynamic partitioning? Or maybe yes, if we could partition on a column value/expression?! eg "MOD(id, 8)"?? What about pg_dump? Should it be able to regenerate the initial create? > [4] https://wiki.postgresql.org/wiki/Declarative_partitioning_improvements Good point, a wiki is better than a thread for that type of things. I'll look at this page. -- Fabien. ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Proposal: Automatic partition creation @ 2020-07-08 05:44 Amul Sul <[email protected]> parent: Fabien COELHO <[email protected]> 0 siblings, 0 replies; 12+ messages in thread From: Amul Sul @ 2020-07-08 05:44 UTC (permalink / raw) To: Fabien COELHO <[email protected]>; +Cc: Anastasia Lubennikova <[email protected]>; PostgreSQL Hackers <[email protected]> On Wed, Jul 8, 2020 at 10:24 AM Fabien COELHO <[email protected]> wrote: > > > Hello Anastasia, > > My 0.02 €: > > > The patch implements following syntax: > > > > CREATE TABLE ... PARTITION BY partition_method (list_of_columns) > > partition_auto_create_clause > > > > where partition_auto_create_clause is > > > > CONFIGURATION [IMMEDIATE| DEFERRED] USING partition_bound_spec > > > > and partition_bound_spec is: > > > > MODULUS integer | VALUES IN (expr [,...]) [, ....] | INTERVAL range_step > > FROM range_start TO range_end > > ISTM That we should avoid new specific syntaxes when possible, and prefer > free keyword option style, like it is being discussed for some other > commands, because it reduces the impact on the parser. > > That would suggest a more versatile partition_bound_spec which could look > like (<keyword> <constant-or-maybe-even-expr>[, …]): > > For modulus, looks easy: > > (MODULUS 8) > > For interval, maybe something like: > > (STEP ..., FROM/START ..., TO/END ...) > > The key point is that for dynamic partitioning there would be no need for > boundaries, so that it could just set a point and an interval > > (START/INIT/FROM??? ..., STEP ...) > > For lists of values, probably it would make little sense to have dynamic > partitioning? Or maybe yes, if we could partition on a column > value/expression?! eg "MOD(id, 8)"?? > > What about pg_dump? Should it be able to regenerate the initial create? > I don't think this is needed for the proposed "Automatic partitioning (static)" which generates a bunch of CREATE TABLE statements, IIUC. Might be needed later for "Automatic partitioning (dynamic)" where dynamic specifications need to be stored. > > [4] https://wiki.postgresql.org/wiki/Declarative_partitioning_improvements > > Good point, a wiki is better than a thread for that type of things. I'll > look at this page. +1 Regards, Amul ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Proposal: Automatic partition creation @ 2020-07-13 18:01 Anastasia Lubennikova <[email protected]> parent: Tom Lane <[email protected]> 1 sibling, 1 reply; 12+ messages in thread From: Anastasia Lubennikova @ 2020-07-13 18:01 UTC (permalink / raw) To: Tom Lane <[email protected]>; Robert Haas <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> On 06.07.2020 19:10, Tom Lane wrote: > Robert Haas <[email protected]> writes: >> On Mon, Jul 6, 2020 at 6:46 AM Anastasia Lubennikova >> <[email protected]> wrote: >>> I am going to implement this via SPI, which allow to simplify checks and >>> calculations. Do you see any pitfalls in this approach? >> I don't really see why we need SPI here. > I would vote against any core facility that is implemented via SPI > queries. It is just too darn hard to control the semantics completely in > the face of fun stuff like varying search_path. Look at what a mess the > queries generated by the RI triggers are --- and they only have a very > small set of behaviors to worry about. I'm still only about 95% confident > they don't have security issues, too. > > If you're using SPI to try to look up appropriate operators, I think > the chances of being vulnerable to security problems are 100%. Good to know, thank you for that. I had doubts about the internal usage of SPI, but didn't know what exactly can go wrong. > >> I think the big problem here is identifying the operator to use. We >> have no way of identifying the "plus" or "minus" operator associated >> with a datatype; indeed, that constant doesn't exist. > We did indeed solve this in connection with window functions, cf > 0a459cec9. I may be misunderstanding what the problem is here, > but I think trying to reuse that infrastructure might help. Do we need to introduce a new support function? Is there a reason why we can not rely on '+' operator? I understand that the addition operator may lack or be overloaded for some complex datatypes, but I haven't found any examples that are useful for range partitioning. Both pg_pathman and pg_partman also use '+' to generate bounds. I explored the code a bit more and came up with this function, which is very similar to generate_series_* functions, but it doesn't use SPI and looks for the function that implements the '+' operator, instead of direct call: // almost pseudocode static Const * generate_next_bound(Const *start, Const *interval) { ObjectWithArgs *sum_oper_object = makeNode(ObjectWithArgs); sum_oper_object->type = OBJECT_OPERATOR; /* hardcode '+' operator for addition */ sum_oper_object->objname = list_make1(makeString("+")); ltype = makeTypeNameFromOid(start->consttype, start->consttypmod); rtype = makeTypeNameFromOid(interval->consttype, interval->consttypmod); sum_oper_object->objargs = list_make2(ltype, rtype); sum_oper_oid = LookupOperWithArgs(sum_oper_object, false); oprcode = get_opcode(sum_oper_oid); fmgr_info(oprcode, &opproc); next_bound->constvalue = FunctionCall2(&opproc, start->constvalue, interval->constvalue); } Thoughts? -- Anastasia Lubennikova Postgres Professional: http://www.postgrespro.com The Russian Postgres Company ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Proposal: Automatic partition creation @ 2020-07-13 19:01 Tom Lane <[email protected]> parent: Anastasia Lubennikova <[email protected]> 0 siblings, 0 replies; 12+ messages in thread From: Tom Lane @ 2020-07-13 19:01 UTC (permalink / raw) To: Anastasia Lubennikova <[email protected]>; +Cc: Robert Haas <[email protected]>; PostgreSQL Hackers <[email protected]> Anastasia Lubennikova <[email protected]> writes: > On 06.07.2020 19:10, Tom Lane wrote: >> Robert Haas <[email protected]> writes: >>> I think the big problem here is identifying the operator to use. We >>> have no way of identifying the "plus" or "minus" operator associated >>> with a datatype; indeed, that constant doesn't exist. >> We did indeed solve this in connection with window functions, cf >> 0a459cec9. I may be misunderstanding what the problem is here, >> but I think trying to reuse that infrastructure might help. > Do we need to introduce a new support function? Is there a reason why we > can not rely on '+' operator? (1) the appropriate operator might not be named '+' (2) even if it is, it might not be in your search_path (3) you're vulnerable to security problems from someone capturing the '+' operator with a better match; since you aren't writing the operator explicitly, you can't fix that by qualifying it (4) if the interval constant is written as an undecorated string literal, the parser may have trouble resolving a match at all > I understand that the addition operator may lack or be overloaded for > some complex datatypes, but I haven't found any examples that are useful > for range partitioning. "It works for all the built-in data types" isn't really a satisfactory answer. But even just in the built-in types, consider "date": # select oid::regoperator from pg_operator where oprname ='+' and oprleft = 'date'::regtype; oid -------------------------------- +(date,interval) +(date,integer) +(date,time without time zone) +(date,time with time zone) (4 rows) It's not that immediately obvious which of these would make sense to use. But the short answer here is that we did not accept relying on '+' being the right thing for window function ranges, and I don't see why it is more acceptable for partitioning ranges. The existing places where our parser relies on implicit operator names are, without exception, problematic [1]. regards, tom lane [1] https://www.postgresql.org/message-id/flat/ffefc172-a487-aa87-a0e7-472bf29735c8%40gmail.com ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Proposal: Automatic partition creation @ 2020-07-13 21:11 Anastasia Lubennikova <[email protected]> parent: Anastasia Lubennikova <[email protected]> 3 siblings, 0 replies; 12+ messages in thread From: Anastasia Lubennikova @ 2020-07-13 21:11 UTC (permalink / raw) To: PostgreSQL Hackers <[email protected]>; +Cc: Fabien COELHO <[email protected]>; Justin Pryzby <[email protected]>; Robert Haas <[email protected]>; Amul Sul <[email protected]> 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. I compared the syntax of other databases. You can find an overview here [1]. It seems that there is no industry standard, so every DBMS has its own implementation. I decided to rely on a Greenplum syntax, as the most similar to the original PostgreSQL syntax. New proposal is: 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' example: CREATE TABLE years(int year) PARTITION BY RANGE (year) USING (START (2006) END (2016) EVERY (1), DEFAULT PARTITION other_years); 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_cha... -- Anastasia Lubennikova Postgres Professional: http://www.postgrespro.com The Russian Postgres Company ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Proposal: Automatic partition creation @ 2020-07-13 21:14 Anastasia Lubennikova <[email protected]> parent: Justin Pryzby <[email protected]> 0 siblings, 0 replies; 12+ messages in thread From: Anastasia Lubennikova @ 2020-07-13 21:14 UTC (permalink / raw) To: Justin Pryzby <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> On 06.07.2020 17:59, Justin Pryzby wrote: > I think you'd want to have an > ALTER command for that (we would use that to change tables between > daily/monthly based on their current size). That should also support setting > the MODULUS of a HASH partitioned table, to allow changing the size of its > partitions (currently, the user would have to more or less recreate the table > and move all its data into different partitions, but that's not ideal). New syntax fits to the ALTER command as well. ALTER TABLE tbl PARTITION BY HASH (number) USING (partition_desc) In simple cases (i.e. range partitioning granularity), it will simply update the rule of bound generation, saved in the catalog. More complex hash partitions will require some rebalancing. Though, the syntax is pretty straightforward for all cases. In the next versions, we can also add a CONCURRENTLY keyword to cover partitioning of an existing non-partitioned table with data. > I don't know if it's important for anyone, but it would be interesting to think > about supporting sub-partitioning: partitions which are themselvese partitioned. > Like something => something_YYYY => something_YYYY_MM => something_YYYY_MM_DD. > You'd need to specify how to partition each layer of the heirarchy. In the > most general case, it could be different partition strategy. I suppose it will be a natural extension of this work. Now we need to ensure that the proposed syntax is extensible. Greenplum syntax, which I choose as an example, provides subpartition syntax as well. > If you have a callback function for partition renaming, I think you'd want to > pass it not just the current name of the partition, but also the "VALUES" used > in partition creation. Like (2020-04-05)TO(2020-05-06). Maybe instead, we'd > allow setting a "format" to use to construct the partition name. Like > "child.foo_bar_%Y_%m_%d". Ideally, the formats would be fixed-length > (zero-padded, etc), so failures with length can happen at "parse" time of the > statement and not at "run" time of the creation. You'd still have to handle > the case that the name already exists but isn't a partition (or is a partition > by doesn't handle the incoming tuple for some reason). In callback design, I want to use the best from pg_pathman's set_init_callback(). The function accepts jsonb argument, which contains all the data about the parent table, bounds, and so on. This information can be used to construct name for the partition and generate RENAME statement. > Also, maybe your "configuration" syntax would allow specifying other values. > Maybe including a retention period (as an INTERVAL for RANGE tables). That's > useful if you had a command to PRUNE the oldest partitions, like ALTER..PRUNE. In this version, I got rid of the 'configuration' keyword. Speaking of retention, I think that it would be hard to cover all use-cases with a declarative syntax. While it is relatively easy to implement deletion within a callback function. See rotation_callback example in pg_pathman [1]. [1] https://github.com/postgrespro/pg_pathman/blob/79e11d94a147095f6e131e980033018c449f8e2e/sql/pathman_... -- Anastasia Lubennikova Postgres Professional: http://www.postgrespro.com The Russian Postgres Company ^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2020-07-13 21:14 UTC | newest] Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-07-06 10:45 Proposal: Automatic partition creation Anastasia Lubennikova <[email protected]> 2020-07-06 14:59 ` Justin Pryzby <[email protected]> 2020-07-13 21:14 ` Anastasia Lubennikova <[email protected]> 2020-07-06 15:45 ` Robert Haas <[email protected]> 2020-07-06 16:10 ` Tom Lane <[email protected]> 2020-07-07 14:22 ` Robert Haas <[email protected]> 2020-07-07 15:09 ` Tom Lane <[email protected]> 2020-07-13 18:01 ` Anastasia Lubennikova <[email protected]> 2020-07-13 19:01 ` Tom Lane <[email protected]> 2020-07-08 04:53 ` Fabien COELHO <[email protected]> 2020-07-08 05:44 ` Amul Sul <[email protected]> 2020-07-13 21:11 ` Anastasia Lubennikova <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox