From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
To: Amit Langote <amitlangote09@gmail.com>
Cc: Rafia Sabih <rafia.pghackers@gmail.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: adding partitioned tables to publications
Date: Fri, 22 Nov 2019 11:46:14 +0100
Message-ID: <7a23e60b-ffe8-bce1-5c44-db48d3bc163e@2ndquadrant.com> (raw)
In-Reply-To: <CA+HiwqFwZV4F5sznWvgqf4eR6SebWLTq--gLVytNdtiCDMn=vg@mail.gmail.com>
References: <CA+HiwqH=Y85vRK3mOdjEkqFK+E=ST=eQiHdpj43L=_eJMOOznQ@mail.gmail.com>
<CA+HiwqEF4K+SxBZkU-=q6krGD74ysZZZv5z10_2C=+UcE2vTYw@mail.gmail.com>
<CA+FpmFf2HVQYRQNh1ZvWPwatyr8_5n4pHRzNtmD5zVqs1zoLbQ@mail.gmail.com>
<CA+HiwqEgn5da9KvWP4y0rBjcMtHdRao01Lr6TgTw78+UGceJwQ@mail.gmail.com>
<037d3133-1689-108c-360d-b782e3209cfb@2ndquadrant.com>
<CA+HiwqHf8_bcyHt=6R4O_Ju4P_Wz6wu1MkOAe14URdpdafS_KQ@mail.gmail.com>
<CA+HiwqEQb5-26-=-K=vrWKQhvn7kqOfS-Vgj+H2YMw5XtdUUQw@mail.gmail.com>
<eed6fb07-94d6-a2a5-33e1-84679dca9363@2ndquadrant.com>
<CA+HiwqF7JD0CLkN12KNuGsf5GfaLuVb-Nc6+nTyL_iOhrSuA0g@mail.gmail.com>
<CA+HiwqFWK3raKUU5Fp789jBsju-pYVpAiw0OsuO5sMX1CkYwFQ@mail.gmail.com>
<b1e7d3c5-d4f8-2bc6-80a1-18b18c5db347@2ndquadrant.com>
<CA+HiwqFwZV4F5sznWvgqf4eR6SebWLTq--gLVytNdtiCDMn=vg@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: peter.eisentraut@2ndquadrant.com, amitlangote09@gmail.com, rafia.pghackers@gmail.com
Subject: Re: adding partitioned tables to publications
In-Reply-To: <7a23e60b-ffe8-bce1-5c44-db48d3bc163e@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox