Re: adding partitioned tables to publications
От | Amit Langote |
---|---|
Тема | Re: adding partitioned tables to publications |
Дата | |
Msg-id | CA+HiwqFBKtJhE8575DNPeJs=bhcxUgFzJgGT_CgXqbWSkN=wJg@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: adding partitioned tables to publications (Petr Jelinek <petr@2ndquadrant.com>) |
Список | pgsql-hackers |
Hi Petr, Thanks for your comments. On Sun, Oct 13, 2019 at 5:01 AM Petr Jelinek <petr@2ndquadrant.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
В списке pgsql-hackers по дате отправления: