Re: Partitioning WIP patch (was: Partitioning: issues/ideas)

On 25-02-2015 AM 01:13, Corey Huinker wrote:
> I think it's confusing to use BETWEEN to mean [low,high) when it already
> means [low,high] in WHERE clauses.
>

Yeah, I'm not really attached to that syntax.

> Why not leverage range notation instead?
>
> CREATE TABLE parent_monthly_xxxxx_201401 PARTITION OF
> parent_monthly_xxxxx FOR VALUES IN RANGE '[2014-04-01,2014-05-01)'
>
> "IN RANGE" could easily be "WITHIN RANGE" or "WITHIN" or something else.
>
> Clearly, this example above assumes that the partitioning is on a single
> column.
>
> For partitioning on a set of columns you're essentially creating a custom
> composite type with major-minor collation, could that custom type be
> created at table creation time? Could an existing composite type be
> declared as the partition key?
>

The answer to the latter is yes as long as there is an operator class
that supports a strategy compatible with the chosen partitioning
strategy. For example, record/composite type has built-in support for
various btree strategies via record_ops.

As for the former, I tend to think creating new user-space
types/operators transparently might not be a very good idea. One concern
would be pg_dump related. Though I also agree there is some duplication
of major-minor collation logic in case of multi-column keys.

> CREATE TYPE year_month( year int, month int );
>
> (CREATE OPERATOR... for < = > )
>
> CREATE TABLE parent_monthly(year int, month int, day int) PARTITION BY
> RANGE ON year_month(year, month);
>

Regards,
Amit