Re: adding partitioned tables to publications

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.

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.

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

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services