Re: adding partitioned tables to publications

Hi Petr,

Thanks for your comments.

On Sun, Oct 13, 2019 at 5:01 AM Petr Jelinek <petr(at)2ndquadrant(dot)com> wrote:
> 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;

Yep, facepalm! :)

So, one doesn't really need as many publication objects as there are
partitions as my version suggests, which is good. Although, as you
can tell, a user would still manually need to keep the set of
published partitions up to date, for example when new partitions are
added.

> > 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.

Ah, I didn't know that the subscription code doesn't out-of-the-box
support tuple routing. Indeed, we will need to fix that.

> 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.

I didn't think about that but maybe you are right.

> 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.

I tested and bar continues to be in the publication with above steps:

create table foo (a int) partition by list (a);
create publication mypub for table foo;
create table bar (a int);
alter publication mypub add table bar;
\d bar
Table "public.bar"
Column │ Type │ Collation │ Nullable │ Default
────────┼─────────┼───────────┼──────────┼─────────
a │ integer │ │ │
Publications:
"mypub"

alter table foo attach partition bar for values in (1);
\d bar
Table "public.bar"
Column │ Type │ Collation │ Nullable │ Default
────────┼─────────┼───────────┼──────────┼─────────
a │ integer │ │ │
Partition of: foo FOR VALUES IN (1)
Publications:
"mypub"

-- can't now drop bar from mypub (its membership is no longer standalone)
alter publication mypub drop table bar;
ERROR: cannot drop partition "bar" from an inherited publication
HINT: Drop the parent from publication instead.

alter table foo detach partition bar;

-- bar is still in mypub (now a standalone member)
\d bar
Table "public.bar"
Column │ Type │ Collation │ Nullable │ Default
────────┼─────────┼───────────┼──────────┼─────────
a │ integer │ │ │
Publications:
"mypub"

-- ok to drop now from mypub
alter publication mypub drop table bar;

Thanks,
Amit