Re: Proposal: Automatic partition creation

On Mon, Jul 6, 2020 at 6:46 AM Anastasia Lubennikova
<a(dot)lubennikova(at)postgrespro(dot)ru> 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