Subject: Re: Documentation improvements for partitioning
To: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>,
 Robert Haas <robertmhaas@gmail.com>, Simon Riggs <simon@2ndquadrant.com>
References: 
 <CADkLM=esKK-DB=8fs6QNZZAa3N4-G9OuPQatAMSFPzmJv2HmPQ@mail.gmail.com>
 <CANP8+jJBpWocfKrbJcaf3iBt9E3U=WPE_NC8YE6rye+YJ1sYnQ@mail.gmail.com>
 <4ee94808-968a-57d7-c12a-a18f08138a2f@lab.ntt.co.jp>
 <CANP8+jJaE4fcbbtA9vKr83_cCgBfgrWhk92Q1ts-97JAzdmVSw@mail.gmail.com>
 <CA+TgmoZUwj=QYnaK+F7xEf4w_e2g3XxdMnSNZMZjuinHRcOB8A@mail.gmail.com>
 <CANP8+jJOrCZ5KYGGRyp0yuzFZMTck+tWAtkiFdg=Bc6TUedBaw@mail.gmail.com>
 <CA+TgmoZ7QJd_kOYyFUtgHCP2Qyzuv0gQJutqEV9mBDn1jONDKg@mail.gmail.com>
 <20170220203320.GB2672@momjian.us>
 <CA+TgmobhXyo8oe9BJVi5zBge-UQ+EbmZ_zOOC9y6ZgJjC480CQ@mail.gmail.com>
 <CANP8+j+q_nDg6KsBxJvMW2+cXay+tpktoKmN7Gf3wgTgX_5_Ow@mail.gmail.com>
 <CA+TgmobQ4+H9-wBFLz7=31OwTbbLo0Pm_RCjT-UY9mE31wWmRw@mail.gmail.com>
 <CANP8+jKHUJAWkgj=TYoyq8+UC4xz=dNt1mWYzxmTgLhQYniZcQ@mail.gmail.com>
 <CA+TgmoZ9ysS96tjYNKKUpb_kALYAZ_s0nxRQqwKg6XB99cGrNQ@mail.gmail.com>
 <f6317f9a-7368-b196-207f-2e425e57eff3@2ndquadrant.com>
 <70d3b45d-f76a-904b-7bd9-8355604d2643@lab.ntt.co.jp>
Cc: Bruce Momjian <bruce@momjian.us>, Corey Huinker
 <corey.huinker@gmail.com>, Pg Hackers <pgsql-hackers@postgresql.org>
From: Petr Jelinek <petr.jelinek@2ndquadrant.com>
Message-ID: <c3c15847-27ea-0a1c-2a9a-ec5adf2d7b41@2ndquadrant.com>
Date: Mon, 27 Feb 2017 22:24:24 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.7.0
MIME-Version: 1.0
In-Reply-To: <70d3b45d-f76a-904b-7bd9-8355604d2643@lab.ntt.co.jp>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On 27/02/17 07:59, Amit Langote wrote:
> On 2017/02/27 3:18, Petr Jelinek wrote:
>> On 24/02/17 07:15, Robert Haas wrote:
>>> On Fri, Feb 24, 2017 at 9:53 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
>>>> The good news is that logical replication DOES work with partitioning,
>>>> but only for a Publication with PUBLISH INSERT, pushing from a normal
>>>> table to a partitioned one. Which is useful enough for first release.
>>>>
>>>> The work on having UPDATE work between partitions can be used to make
>>>> updates and deletes work in logical replication. That might yet be
>>>> made to work in this release, and I think we should look into it, but
>>>> I think it can be left til next release if we try.
>>>
>>> Are you speaking of the case where you want to replicate from an
>>> unpartitioned table to a partitioned table?  I would imagine that if
>>> you are replicating between two identical partitioning hierarchies, it
>>> would just work.  (If not, that's probably a bug, though I don't know
>>> whose bug.)
>>>
>>
>> Yes same hierarchies will work but mainly because one has to add
>> partitions themselves to publication currently. I guess that's the
>> limitation we might have to live with in 10 as adding the whole
>> partitioned table should probably work for different hierarchies when we
>> enable it and I am not quite sure that's doable before start of the CF
>> at this point.
> 
> If and when we add support to add partitioned tables to publications, I
> think it will work by recursing to include the partitions to the same
> publication (I see that OpenTableList() in publicationcmds.c calls
> find_all_inheritors if recursion is requested by not specifying ONLY).
> When a subscription replicates from this publication, it's going to match
> changes for individual leaf partitions, not the root parent table.  IOW,
> none of the changes applied to a partitioned table are replicated as
> changes to the table itself.  So, it seems that adding a partitioned table
> to a publication or subscription would simply be a *shorthand* for adding
> all the (leaf) partitions that will actually emit and receive changes.

This was my first thought as well. However we need to also take into
account the use-case with different partitioning topology on publisher
and subscriber (at least in a sense that the initial design will not
paint us in the corner).

Now I see two ways of achieving this.
a) Either going with more or less what you wrote above and in the future
have some smarts where we can specify that it should replicate with
different name. We'll eventually want to be able to replicate table to
differently named table anyway so it's not stretch by any means to do that.
b) Or just replicate changes to leaf partitions as changes to
partitioned table. That's also not that hard to do, we just need fast
lookup of partitioned table from leaf table.

I guess a) looks simpler given that we eventually need the rename
anyway, but I'd like opinions of other people as well.

> I'm not sure but should adding/removing partitions after the fact cause
> their addition/removal from the publication (& subscription)? Maybe we'll
> discuss these issues later.

That's something I've been also wondering as there is many corner cases
here. For example if table is in some publication and then is added as
partition to partitioned table what should happen? What should happen
when the partitioned table is then removed from same publication to
which partition was added explicitly? Should we allow different
publication configuration for different partitions within same
partitioned table, etc?

This somewhat brings bigger question about where we want to go in
general with partitioning. And that is, should partition be a separate
entity that is on the same level of table, or should it be part of the
partitioned table without it's own "identity".

-- 
  Petr Jelinek                  http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services


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