Message-ID: <55D50034.1050700@agliodbs.com>
Date: Wed, 19 Aug 2015 15:16:20 -0700
From: Josh Berkus <josh@agliodbs.com>
Organization: PostgreSQL Experts Inc.
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.8.0
MIME-Version: 1.0
To: Thom Brown <thom@linux.com>
CC: Simon Riggs <simon@2ndquadrant.com>,
 Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>,
 Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Declarative partitioning
References: <55D3093C.5010800@lab.ntt.co.jp>
 <WM!d7710f5363855cdb19d569b89c9c5e583742def1a8442d29ccee7f2591bfe40e9b26d6d9500448c2078cac09dedcf224!@asav-3.01.com>
 <55D36BFA.7080004@agliodbs.com>
 <CANP8+jLDBxfndrXpwuoqsB8iQN1tJu14shd68+v-KnBnnpGLXw@mail.gmail.com>
 <WM!952c85ac5038044c064918d1f182b019f01a36b62a9fd1111248789b2e3dc7b2dc3d53e4f18a4b23efd54ae06d6c1eb5!@asav-3.01.com>
 <55D4E2D0.3010906@agliodbs.com>
 <CAA-aLv7QbL77NLPeKP1omGYHBGP8jCnk9nW_opQqkddvjmp1-g@mail.gmail.com>
 <WM!34ca3cdaf2bc4b8368c335b863f38fae17639c3de8f05d670859c320c8601a084b93b7b8122ba0823968cbe7bbdabf32!@asav-3.01.com>
In-Reply-To: 
 <WM!34ca3cdaf2bc4b8368c335b863f38fae17639c3de8f05d670859c320c8601a084b93b7b8122ba0823968cbe7bbdabf32!@asav-3.01.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers