Re: adding partitioned tables to publications

Hi Peter,

On Wed, Nov 20, 2019 at 4:55 PM Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:
> On 2019-11-18 09:53, Amit Langote wrote:
> > I have spent some time hacking on this.  With the attached updated
> > patch, adding a partitioned table to publication results in publishing
> > the inserts, updates, deletes of the table's leaf partitions as
> > inserts, updates, deletes of the table itself (it all happens inside
> > pgoutput).  So, the replication target table doesn't necessarily have
> > to be a partitioned table and even if it is partitioned its partitions
> > don't have to match one-to-one.
> >
> > One restriction remains though: partitioned tables on a subscriber
> > can't accept updates and deletes, because we'd need to map those to
> > updates and deletes of their partitions, including handling a tuple
> > possibly moving from one partition to another during an update.
>
> Right.  Without that second part, the first part isn't really that
> useful yet, is it?

I would say yes.

> I'm not sure what your intent with this patch is now.  I thought the
> previous behavior -- add a partitioned table to a publication and its
> leaf tables appear in the replication output -- was pretty welcome.  Do
> we not want that anymore?

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 should probably be an option to pick the behavior, like we do in
> pg_dump.

I don't understand which existing behavior.  Can you clarify?

Regarding allowing users to choose between publishing partitioned
table changes using leaf tables' schema vs as using own schema, I tend
to agree that there would be value in that.  Users who choose the
former will have to ensure that target leaf partitions match exactly.
Users who want flexibility in how the target table is defined can use
the latter.

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

Thanks,
Amit