Re: Proposal: Automatic partition creation

On Wed, Jul 8, 2020 at 10:24 AM Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> 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