Subject: Re: adding partitioned tables to publications
To: Amit Langote <amitlangote09@gmail.com>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
References: 
 <CA+HiwqH=Y85vRK3mOdjEkqFK+E=ST=eQiHdpj43L=_eJMOOznQ@mail.gmail.com>
From: Petr Jelinek <petr@2ndquadrant.com>
Message-ID: <b078aebe-da4f-661e-0b66-804eda7df55f@2ndquadrant.com>
Date: Sat, 12 Oct 2019 22:01:55 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.9.0
MIME-Version: 1.0
In-Reply-To: 
 <CA+HiwqH=Y85vRK3mOdjEkqFK+E=ST=eQiHdpj43L=_eJMOOznQ@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

Hi,

On 07/10/2019 02:55, Amit Langote wrote:
> One cannot currently add partitioned tables to a publication.
> 
> create table p (a int, b int) partition by hash (a);
> create table p1 partition of p for values with (modulus 3, remainder 0);
> create table p2 partition of p for values with (modulus 3, remainder 1);
> create table p3 partition of p for values with (modulus 3, remainder 2);
> 
> create publication publish_p for table p;
> ERROR:  "p" is a partitioned table
> DETAIL:  Adding partitioned tables to publications is not supported.
> HINT:  You can add the table partitions individually.
> 
> One can do this instead:
> 
> create publication publish_p1 for table p1;
> create publication publish_p2 for table p2;
> create publication publish_p3 for table p3;

Or just create publication publish_p for table p1, p2, p3;

> 
> but maybe that's too much code to maintain for users.
> 
> I propose that we make this command:
> 
> create publication publish_p for table p;
> 

+1

> automatically add all the partitions to the publication.  Also, any
> future partitions should also be automatically added to the
> publication.  So, publishing a partitioned table automatically
> publishes all of its existing and future partitions.  Attached patch
> implements that.
> 
> What doesn't change with this patch is that the partitions on the
> subscription side still have to match one-to-one with the partitions
> on the publication side, because the changes are still replicated as
> being made to the individual partitions, not as the changes to the
> root partitioned table.  It might be useful to implement that
> functionality on the publication side, because it allows users to
> define the replication target any way they need to, but this patch
> doesn't implement that.
> 

Yeah for that to work subscription would need to also need to be able to 
write to partitioned tables, so it needs both sides to add support for 
this. I think if we do both what you did and the transparent handling of 
root only, we'll need new keyword to differentiate the two. It might 
make sense to think about if we want your way to need an extra keyword 
or the transparent one will need it.

One issue that I see reading the patch is following set of commands:

CREATE TABLE foo ...;
CREATE PUBLICATION mypub FOR TABLE foo;

CREATE TABLE bar ...;
ALTER PUBLICATION mypub ADD TABLE bar;

ALTER TABLE foo ATTACH PARTITION bar ...;
ALTER TABLE foo DETACH PARTITION bar ...;

This will end up with bar not being in any publication even though it 
was explicitly added. That might be acceptable caveat but it at least 
should be clearly documented (IMHO with warning).

-- 
Petr Jelinek
2ndQuadrant - PostgreSQL Solutions for the Enterprise
https://www.2ndQuadrant.com/