Re: adding partitioned tables to publications - Mailing list pgsql-hackers

On Fri, Nov 22, 2019 at 7:46 PM Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:
> On 2019-11-22 07:28, Amit Langote wrote:
> > Hmm, I thought it would be more desirable to not expose a published
> > partitioned table's leaf partitions to a subscriber, because it allows
> > the target table to be defined more flexibly.
>
> There are multiple different variants that we probably eventually want
> to support.  But I think there is value in exposing the partition
> structure to the subscriber.  Most notably, it allows the subscriber to
> run the initial table sync per partition rather than in one big chunk --
> which ultimately reflects one of the reasons partitioning exists.

I agree that replicating leaf-to-leaf has the least overhead.

> The other way, exposing only the partitioned table, is also useful,
> especially if you want to partition differently on the subscriber.  But
> without the ability to target a partitioned table on the subscriber,
> this would right now only allow you to replicate a partitioned table
> into a non-partitioned table.  Which is valid but probably not often useful.

Handling non-partitioned target tables was the main reason for me to
make publishing using the root parent's schema the default behavior.
But given that replicating from partitioned tables into
non-partitioned ones would be rare, I agree to replicating using the
leaf schema by default.

> >> What happens when you add a leaf table directly to a publication?  Is it
> >> replicated under its own identity or under its ancestor partitioned
> >> table?  (What if both the leaf table and a partitioned table are
> >> publication members?)
> >
> > If both a leaf partition and an ancestor belong to the same
> > publication, then leaf partition changes are replicated using the
> > ancestor's schema.  For a leaf partition to be replicated using its
> > own schema it must be published via a separate publication that
> > doesn't contain the ancestor.  At least that's what the current patch
> > does.
>
> Hmm, that seems confusing.  This would mean that if you add a
> partitioned table to a publication that already contains leaf tables,
> the publication behavior of the leaf tables would change.  So again, I
> think this alternative behavior of publishing partitions under the name
> of their root table should be an explicit option on a publication, and
> then it should be ensured somehow that individual partitions are not
> added to the publication in confusing ways.
>
> So, it's up to you which aspect of this you want to tackle, but I
> thought your original goal of being able to add partitioned tables to
> publications and have that implicitly expand to all member partitions on
> the publication side seemed quite useful, self-contained, and
> uncontroversial.

OK, let's make whether to publish with root or leaf schema an option,
with the latter being the default.  I will see about updating the
patch that way.

Thanks,
Amit