Re: adding partitioned tables to publications - Mailing list pgsql-hackers
From | Rafia Sabih |
---|---|
Subject | Re: adding partitioned tables to publications |
Date | |
Msg-id | CA+FpmFf2HVQYRQNh1ZvWPwatyr8_5n4pHRzNtmD5zVqs1zoLbQ@mail.gmail.com Whole thread Raw |
In response to | Re: adding partitioned tables to publications (Amit Langote <amitlangote09@gmail.com>) |
Responses |
Re: adding partitioned tables to publications
|
List | pgsql-hackers |
On Thu, 10 Oct 2019 at 08:29, Amit Langote <amitlangote09@gmail.com> wrote:
On Mon, Oct 7, 2019 at 9:55 AM Amit Langote <amitlangote09@gmail.com> 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;
>
> 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;
>
> 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.
Added this to the next CF: https://commitfest.postgresql.org/25/2301/
Hi Amit,
Lately I was exploring logical replication feature of postgresql and I found this addition in the scope of feature for partitioned tables a useful one.
In order to understand the working of your patch a bit more, I performed an experiment wherein I created a partitioned table with several children and a default partition at the publisher side and normal tables of the same name as parent, children, and default partition of the publisher side at the subscriber side. Next I established the logical replication connection and to my surprise the data was successfully replicated from partitioned tables to normal tables and then this error filled the logs,
LOG: logical replication table synchronization worker for subscription "my_subscription", table "parent" has started
ERROR: table "public.parent" not found on publisher
here parent is the name of the partitioned table at the publisher side and it is present as normal table at subscriber side as well. Which is understandable, it is trying to find a normal table of the same name but couldn't find one, maybe it should not worry about that now also if not at replication time.
Please let me know if this is something expected because in my opinion this is not desirable, there should be some check to check the table type for replication. This wasn't important till now maybe because only normal tables were to be replicated, but with the extension of the scope of logical replication to more objects such checks would be helpful.
On a separate note was thinking for partitioned tables, wouldn't it be cleaner to have something like you create only partition table at the subscriber and then when logical replication starts it creates the child tables accordingly. Or would that be too much in future...?
Regards,
Rafia Sabih
Rafia Sabih
pgsql-hackers by date: