Message-ID: <548C6C17.40602@adv-solutions.net>
Date: Sat, 13 Dec 2014 17:40:55 +0100
From: =?UTF-8?B?Sm9zw6kgTHVpcyBUYWxsw7Nu?= <jltallon@adv-solutions.net>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Icedove/31.0
MIME-Version: 1.0
To: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>,
 'Robert Haas' <robertmhaas@gmail.com>
CC: 'Amit Kapila' <amit.kapila16@gmail.com>,
 'Andres Freund' <andres@2ndquadrant.com>,
 'Alvaro Herrera' <alvherre@2ndquadrant.com>,
 'Bruce Momjian' <bruce@momjian.us>,
 'Pg Hackers' <pgsql-hackers@postgresql.org>
Subject: Re: On partitioning
References: <20140829155607.GF7705@eldon.alvh.no-ip.org>
	<066501cffd52$3d919390$b8b4bab0$@lab.ntt.co.jp>
	<CA+TgmoZqndQQQY1U0OBxUOJy1RE_MAawJErghNB7RM8MPgCYDg@mail.gmail.com>
	<032601d00471$ea6f4280$bf4dc780$@lab.ntt.co.jp>
	<CA+TgmoZ-HJOTPfZcnPOh27881xn4N1QGtY7J4nG+v6tV9cJjNw@mail.gmail.com>
	<04da01d00917$2dde1010$899a3030$@lab.ntt.co.jp>
	<CA+TgmoaU+0EN-XsAT1O+64xrENNJiKPgem+Q1hWzz7a=1MYrDA@mail.gmail.com>
	<015b01d00eab$56d37700$047a6500$@lab.ntt.co.jp>	<C!
 AA4eK1KoJyoV0qOd0a8rEj+	iTiAb1ki0pVz5S8Zoi98pCKrDhg@mail.gmail.com>
	<CA+TgmoYk9aU44Z5YRWoV2V9raj=HMbJBfORYr9GzF4vShsGvDA@mail.gmail.com>
	<002d01d01359$290cb4a0$7b261de0$@lab.ntt.co.jp>
	<CAA4eK1L53knKrrhXDTi-no53F2mF+X2a2gKXDpQ1DNi1+NMuVQ@mail.gmail.com>
	<CA+Tgmob5EvcVH73p8pCLsjkwvVe1dHEnkjk2K-fG1GJjvhcTvw@mail.gmail.com>
	<CAA4eK1LcDLmOfw3Y4L9FeAn+pMcLg6OLSTK45G9SBQUvO4tZYQ@mail.gmail.com>
 <CA+TgmoYqwOmL=ykspWmwfFivwA2T3gzyEo_94YZd3L3WpLau4g@mail.gmail.com>
 <007b01d015c6$373c21e0$a5b465a0$@lab.ntt.co.jp>
In-Reply-To: <007b01d015c6$373c21e0$a5b465a0$@lab.ntt.co.jp>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On 12/12/2014 05:43 AM, Amit Langote wrote:
> [snip]
> In case of what we would have called a 'LIST' partition, this could look like
>
> ... FOR VALUES (val1, val2, val3, ...)
>
> Assuming we only support partition key to contain only one column in such a case.

Hmmm….

[...] PARTITION BY LIST(col1 [, col2, ...])

     just like we do for indexes would do.


and CREATE PARTITION child_name OF parent_name
     FOR [VALUES] (val1a,val2a), (val1b,val2b), (val1c,val2c)
[IN tblspc_name]

     just like we do for multi-valued inserts.

> In case of what we would have called a 'RANGE' partition, this could look like
>
> ... FOR VALUES (val1min, val2min, ...) TO (val1max, val2max, ...)
>
> How about BETWEEN ... AND ... ?

Unless I'm missing something obvious, we already have range types for 
this, don't we?

...   PARTITION BY RANGE (col)

CREATE PARTITION child_name OF parent_name
     FOR [VALUES] '[val1min,val1max)', '[val2min,val2max)', 
'[val3min,val3max)'
     [IN tblspc_name]

and I guess this should simplify a fully flexible implementation (if you 
can construct a RangeType for it, you can use that for partitioning).
This would substitute the ugly (IMHO) "VALUES LESS THAN" syntax with a 
more flexible one
     (even though it might end up being converted into "less than" 
boundaries internally for implementation/optimization purposes)

In both cases we would need to allow for overflows / default partition 
different from the parent table.


Plus some ALTER PARTITION part_name TABLESPACE=tblspc_name


The main problem being that we are assuming named partitions here, which 
might not be that practical at all.

> [snip]
>> I would include the noise keyword VALUES just for readability if 
>> anything. 

+1


FWIW, deviating from already "standard" syntax (Oracle-like --as 
implemented by PPAS for example-- or DB2-like) is quite 
counter-productive unless we have very good reasons for it... which 
doesn't mean that we have to do it exactly like they do (specially if we 
would like to go the incremental implementation route).

Amit: mind if I add the DB2 syntax for partitioning to the wiki, too?

     This might as well help with deciding the final form of 
partitioning (and define the first implementation boundaries, too)


Thanks,

     / J.L.


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