Re: Declarative partitioning

On 08/19/2015 01:18 PM, Thom Brown wrote:
> On 19 August 2015 at 21:10, Josh Berkus <josh@agliodbs.com
> <mailto:josh@agliodbs.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