Re: Declarative partitioning

On 08/19/2015 01:18 PM, Thom Brown wrote:
> On 19 August 2015 at 21:10, Josh Berkus <josh(at)agliodbs(dot)com
> <mailto:josh(at)agliodbs(dot)com>> wrote:
>
> On 08/19/2015 04:59 AM, Simon Riggs wrote:
> > I like the idea of a regular partitioning step because it is how you
> > design such tables - "lets use monthly partitions".
> >
> > This gives sanely terse syntax, rather than specifying pages and pages
> > of exact values in DDL....
> >
> > PARTITION BY RANGE ON (columns) INCREMENT BY (INTERVAL '1 month' )
> > START WITH value;
>
> Oh, I like that syntax!
>
> How would it work if there were multiple columns? Maybe we don't want
> to allow that for this form?
>
>
> If we went with that, and had:
>
> CREATE TABLE orders (order_id serial, order_date date, item text)
> PARTITION BY RANGE ON (order_date) INCREMENT BY (INTERVAL '1 month')
> START WITH '2015-01-01';
>
> Where would the following go?
>
> INSERT INTO orders (order_date, item) VALUES ('2014-11-12', 'Old item');
>
> Would there automatically be an "others" partition? Or would it produce
> an error and act like a constraint?

The "others" partition was brought up upthread, as an addition to the
original proposal. I really think that an "others" partition needs to
be up to the DBA; I've seen apps where they'd want to capture it, and
apps where they'd want such an insert to error.

I, for one, would be OK with a new partitioning which didn't address the
"others" partition issue until 9.7; I see it as a wholly separable
improvement.

Plus, you can always *manually* add high/low catchall partitions.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com