Re: [POC] hash partitioning

On Thu, 13 Apr 2017 16:40:29 -0400
Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Fri, Mar 17, 2017 at 7:57 AM, Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp> wrote:
> > I also understanded that my design has a problem during pg_dump and
> > pg_upgrade, and that some information to identify the partition
> > is required not depending the command order. However, I feel that
> > Amul's design is a bit complicated with the rule to specify modulus.
> >
> > I think we can use simpler syntax, for example, as below.
> >
> > CREATE TABLE h1 PARTITION OF h FOR (0);
> > CREATE TABLE h2 PARTITION OF h FOR (1);
> > CREATE TABLE h3 PARTITION OF h FOR (2);
>
> I don't see how that can possibly work. Until you see all the table
> partitions, you don't know what the partitioning constraint for any
> given partition should be, which seems to me to be a fatal problem.

If a partition has an id, the partitioning constraint can be written as

hash_func(hash_key) % N = id

wehre N is the number of paritions. Doesn't it work?

> I agree that Amul's syntax - really, I proposed it to him - is not the
> simplest, but I think all the details needed to reconstruct the
> partitioning constraint need to be explicit. Otherwise, I'm pretty
> sure things we're going to have lots of problems that we can't really
> solve cleanly. We can later invent convenience syntax that makes
> common configurations easier to set up, but we should invent the
> syntax that spells out all the details first.

I have a question about Amul's syntax. After we create partitions
as followings,

create table foo (a integer, b text) partition by hash (a);
create table foo1 partition of foo with (modulus 2, remainder 0);
create table foo2 partition of foo with (modulus 2, remainder 1);

we cannot create any additional partitions for the partition.

Then, after inserting records into foo1 and foo2, how we can
increase the number of partitions?

>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company

--
Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>