public inbox for [email protected]
help / color / mirror / Atom feedFrom: shveta malik <[email protected]>
To: vignesh C <[email protected]>
Cc: Dilip Kumar <[email protected]>
Cc: Amit Kapila <[email protected]>
Cc: Shlok Kyal <[email protected]>
Cc: Peter Smith <[email protected]>
Cc: Zhijie Hou (Fujitsu) <[email protected]>
Cc: YeXiu <[email protected]>
Cc: Ian Lawrence Barwick <[email protected]>
Cc: Bharath Rupireddy <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: shveta malik <[email protected]>
Subject: Re: Skipping schema changes in publication
Date: Wed, 28 Jan 2026 10:46:07 +0530
Message-ID: <CAJpy0uBfEuzYX+qjAPM+GV5duOwMNqO6fkDtsN1OzONVNR9WGQ@mail.gmail.com> (raw)
In-Reply-To: <CALDaNm3kX=16L-72m13CqXL9uAiHURNZ+BLo-HfTEYHDFejj-A@mail.gmail.com>
References: <CALDaNm3=JrucjhiiwsYQw5-PGtBHFONa6F7hhWCXMsGvh=tamA@mail.gmail.com>
<CALj2ACVOzhs+BD+abFV2x4oKJdsDNd6SgsE7r8UjnZDCKGEckA@mail.gmail.com>
<CAA4eK1K6Kr88d2S0zFdHRMyuoaZeNh+ktU+oigmCuD09_x_-+g@mail.gmail.com>
<CAHut+PsvC-NezO3MJkdyEz=G1QRje2LntjwhQiEeVbmhOQuBMA@mail.gmail.com>
<CALDaNm18VH2j8cTqfELHQ=0ZNognbGBhbHPteJenWQC6C2dueQ@mail.gmail.com>
<CALDaNm0k_0Ccj47wzJzzPFwgQB7w=R5+Q2_nSqYrmMmjhmcRUw@mail.gmail.com>
<CAHut+Pv_0DwyWoGQNMF+G2AGqMuJTzWQKRtmxaC+=zLTPL-Zkw@mail.gmail.com>
<CALDaNm2-GJt2HsYTkLqQ=ecm=R-vOBw1=aM_d2EiYbz39x_cTQ@mail.gmail.com>
<TYCPR01MB8373C3120C2B3112001ED6F1EDCF9@TYCPR01MB8373.jpnprd01.prod.outlook.com>
<CALDaNm0iZZDB300Dez_97S8G6_RW5QpQ8ef6X3wq8tyK-8wnXQ@mail.gmail.com>
<CAHut+PtiomM+iyAZHvb2dzfsPvRru266KuBe49hKy2n2h+m_zA@mail.gmail.com>
<CALDaNm30KDnwX4Czi29fqLb8JBkuwqjbpj9ixwNXXox574NZqQ@mail.gmail.com>
<CALDaNm1PfKRJsEzbKpyt=v4p3bw+_SzE+LFPsMhR5X+qs+0pPw@mail.gmail.com>
<TYCPR01MB83730A2F1D6A5303E9C1416AEDD99@TYCPR01MB8373.jpnprd01.prod.outlook.com>
<CALDaNm0sAU4s1KTLOEWv=rYo5dQK6uFTJn_0FKj3XG1Nv4D-qw@mail.gmail.com>
<CALDaNm3CLRa95tpas6tEj8x58MUNDShxBNoYS+P8Uq5cryoAOw@mail.gmail.com>
<CALDaNm0EKC3o=v+F7GneGibuCULGKkBWXmNaVB4GR9HoqD066A@mail.gmail.com>
<CALDaNm1Z1Rmqj9s6P9ZzmrVA9F_vZ_DwwhYAJmsjqmY6dS3-hA@mail.gmail.com>
<CAB8KJ=jJGuW=ozKmXZzKDUHZ_-J2ZYGOtJo=i2cnNbSu6=KuYg@mail.gmail.com>
<CALDaNm1mbFP8fxHU_H1Ex4cT2Aq3n8FE79tq0TO5ThvFnDUYMA@mail.gmail.com>
<CAB8KJ=jq4RwTs8K7pokmXQwQppP2ChVJLMSAdXaxAX+c1r+mdg@mail.gmail.com>
<CALDaNm1mJvLni8GODebKBmyegXuZ18bLoG-Pz6H1MCX=vphCYA@mail.gmail.com>
<CALDaNm3dWZCYDih55qTNAYsjCvYXMFv=46UsDWmfCnXMt3kPCg@mail.gmail.com>
<CALDaNm1AQZYgT0tALRrkvpP1Q+8+e7vkGCUjQ-jim1C0q3e=zA@mail.gmail.com>
<CAA4eK1KRdAPC=5=7tQ1GW0cRwD=zaDMi+T4u_k4GxPhPY6e8BQ@mail.gmail.com>
<OS3PR01MB5718C8BE84B862E7E0CEC29B94BD2@OS3PR01MB5718.jpnprd01.prod.outlook.com>
<CAA4eK1KYQz7cf46_D=6VkZ4J6Y8vJ88MMi=6zm2TJXDP+V1mLg@mail.gmail.com>
<CANhcyEXZq4mP5dNgg7u=sMPwvxA4_ZN9U92uZEuzs=0xTu+8Yg@mail.gmail.com>
<CANhcyEXspT3v5-Tdop9uqQV2HWBvZoN5P0BxXQ6Md6Mr7GXK9A@mail.gmail.com>
<CAHut+PuiaLOCkiAx9nPnjk6wTbPFvnm9T5svTuKbgwJwTdea8w@mail.gmail.com>
<CANhcyEV_MePxgftHY65et1WdOAk70M0C7PZ1STPUO8PXHVB1YA@mail.gmail.com>
<CAHut+Ps0hSNqrjv_jT1AuXxO-CrZue3ixE0jKsxVhtArMrkujQ@mail.gmail.com>
<CANhcyEXX3viVpYcGHD_fzhf_f6CDQWr2+VBywrJf5zm_XiB4tg@mail.gmail.com>
<CAHut+PsXP_61ZXuVOx5u9FZGK3oH4taaA59oOzgqyygZx8ezWw@mail.gmail.com>
<CANhcyEU+aPu6iAH2cTA0cDtn3pd6c_njBONCt3FubYZoEEnm8Q@mail.gmail.com>
<CAHut+Pv2P6dJ7hZj_fmzN+=xzjvpOpgkAJvDZg3TD2xpvmY1NQ@mail.gmail.com>
<CANhcyEW2LK4diNeCG862DE40yQoV3VAgf59kXUq2TuR8fnw5vQ@mail.gmail.com>
<CAHut+PuSHScrODVGCM7P53Mv1HE2N6ThzkH4+gQ1eFXVeD-OCA@mail.gmail.com>
<CANhcyEUtYV-9ujtxLasnxN_peT+3LuZjcRx1xUECh1CCmANB8w@mail.gmail.com>
<CAHut+PuviFA6C7qps=+kDYfe3P99as8NCjbR=SYxoi0o96ipoA@mail.gmail.com>
<CANhcyEXkeg3sjkS3DS9yU1ckz4ozUBNZ+RmrWaRNSSVCR8RquA@mail.gmail.com>
<CAHut+PsHavMy_KJ0MwR9J6q0BTTty54TxS-KBZc7X6tb4u7rfA@mail.gmail.com>
<CANhcyEU=k4+0BqOu25N76g738XKUwfLGGdf8e+ssGiRKHC4RwQ@mail.gmail.com>
<CAHut+Ptdi8txJbMc+5Sp2uB4QLGOp-rZdkBbXyhAkxim0iAhBQ@mail.gmail.com>
<CANhcyEW+uJB_bvQLEaZCgoRTc1=i+QnrPPHxZ2=0SBSCyj9pkg@mail.gmail.com>
<CAHut+Ptq0-tMYUOvG3yR34AvuEzR9vUH=muqV_=uEO3zCuA6rA@mail.gmail.com>
<CANhcyEUEMWSkTfGc7Q3B+UiOzSiOmOGLgK-+C5DXwtCGOnDBhg@mail.gmail.com>
<CAHut+PuAgSDr3kbSxPMYEyCeGiJ5hgaT1JUvuiYPRT=Q+--O-Q@mail.gmail.com>
<CANhcyEU_uuiKMRrd_E_DeYsyCvwY_u995E-Do3d66J7tQnzdzw@mail.gmail.com>
<CANhcyEWK9sj9UY4uaV36Q8qxUv=8Joch0o98RCN5U3xLjUxAag@mail.gmail.com>
<CALDaNm32XQDR4qsOhPQeophVbZ8r+ShJSSssoVfdPcwG6joPHQ@mail.gmail.com>
<CANhcyEVt2CBnG7MOktaPPV4rYapHR-VHe5=qoziTZh1L9SVc6w@mail.gmail.com>
<CAHut+PtGu2j72yV_as_TVKfWr3ctd18svReGEx3xa=q5BtKyKA@mail.gmail.com>
<CANhcyEUh9ki36VTXyYf8UqUrfLX9ZhfP_f2LjpvvycgqWLQqqQ@mail.gmail.com>
<CAHut+Pv96u0VciuSx2B99jDHEvn7svVJynCmw-qYb=z4Kc2knA@mail.gmail.com>
<CANhcyEWGiWwGt1-v6d9bCAae9Np7cNPt+iRV9PXBZ0z=75XEVw@mail.gmail.com>
<CANhcyEWAQHtUfgNPA2m-+okEh7pXaK5irBm+yzyNVJXL2LUTXw@mail.gmail.com>
<CAHut+PuzcKXheZwgNvDJkwK5txd1kzNRxCmcJcbr=_9mGHjKtA@mail.gmail.com>
<CANhcyEXCKPCAdoqBLAhxt64Nwf+7T52dd8daE3qvhBNTvro13Q@mail.gmail.com>
<CAHut+Pu50yWjMR5Mswhi6uVKQmn3hO9o0ocRAgXyUUf4cnVTwg@mail.gmail.com>
<CANhcyEV7ewT+nfLM2owquxW-_6m8Ju+P93y=acoS=JCBHoT-MQ@mail.gmail.com>
<CAJpy0uDdQH7b=LRn_HevbmFACpvq9=c32Vb3tRvjSOnDsQd74w@mail.gmail.com>
<CAA4eK1KZ1Sb0soHp3HH2htwJ3=qka-eQjW35vOW3+4VeWw4VoQ@mail.gmail.com>
<CANhcyEXwLrQsec6g+1dqWTKyJQMQMh=getj28C+zLL14BjuumA@mail.gmail.com>
<CAJpy0uD35+YmDahVNUm+NZsjisV_k2hUDBkiFOJDwAJ1o1CT9A@mail.gmail.com>
<CANhcyEV_EVi5cgJ6WPvmeVAqjCS7Of+VAWuRHZtsVf8PQb_z7g@mail.gmail.com>
<CAHut+Pu5Ukuvd_5oK_mTEjSOEAYupbEXzRqXwSPoLe_sDkA_fg@mail.gmail.com>
<CANhcyEWg2WbEW_fFwk0D3J2KBrUF7th6VrE+gvESgkUKP9VpZg@mail.gmail.com>
<CAJpy0uBegaytfG=AS5VUb-6jAEDzC374-1icn-hP5AnRoMJ+Lg@mail.gmail.com>
<CAJpy0uD+orAxUWKq9Ogf5FEWtXcwkQXb_YZOvXqDc8b15nJe9A@mail.gmail.com>
<CANhcyEVDTPVRWDm-BdJe0yaWFuz1ozTUFNHKvzX2YuiUHh=ArA@mail.gmail.com>
<CAHut+Pu6ameXD4YwbhMXf8kHBhPJXGpOmc21R5o7Lo18hkSKMQ@mail.gmail.com>
<CANhcyEUiwF9w2U6CzkuOsXkLYMOjPs=6O8QyjU_5fDfYWyMswg@mail.gmail.com>
<CAJpy0uCnK7A8HE_mjWaVHDn9Q=CNJQM_mB=QTtseh=GQ4aGumQ@mail.gmail.com>
<CANhcyEWewbuqrwLTuwZUSGRGmLSmRadiNKyDB1yt0Sm+JTMHZA@mail.gmail.com>
<CAA4eK1KFdzZD3JvjfajhbHYYU9DErNcDVQZxfvGKv9npNfO5fQ@mail.gmail.com>
<CAJpy0uD81HRrMYr7S-6AV4W2PtbGKM-nf2D89zsoMHJ9jZssUg@mail.gmail.com>
<CAFiTN-sbpXXbU=u0U7SWjjouScAddZq+xb2FP8zfyVZZZt8z7w@mail.gmail.com>
<CALDaNm2x3fR+AEji0ZruTdss-4WDatraXKs1QA44eVnsBmbUiA@mail.gmail.com>
<CALDaNm3kX=16L-72m13CqXL9uAiHURNZ+BLo-HfTEYHDFejj-A@mail.gmail.com>
On Tue, Jan 27, 2026 at 8:25 PM vignesh C <[email protected]> wrote:
>
> On Fri, 23 Jan 2026 at 18:41, vignesh C <[email protected]> wrote:
> >
> > On Wed, 21 Jan 2026 at 11:35, Dilip Kumar <[email protected]> wrote:
> > >
> > > On Mon, Jan 19, 2026 at 3:08 PM shveta malik <[email protected]> wrote:
> > > >
> > > > Approaches for Supporting EXCEPT in Partitioned Tables
> > > > ------------------------------------------------------------------------
> > > >
> > > > In an offline discussion with Peter Smith, Amit, and Shlok, we
> > > > identified several approaches for supporting EXCEPT with partitioned
> > > > tables and their partitions. I’d like to hear others’ opinions on
> > > > these approaches.
> > > >
> > > > Consider the following partition hierarchy:
> > > > tab_root
> > > > ├─ tab_part_1
> > > > │ ├─ tab_part_1_p1
> > > > │ └─ tab_part_1_p2
> > > > └─ tab_part_2
> > > > ├─ tab_part_2_p1
> > > > └─ tab_part_2_p2
> > > >
> > > >
> > > > Approach 1:
> > > > ---------------------------------
> > > > If we exclude a table, then the data in that table and all of its
> > > > partitions (i.e., the entire subtree under that table) should not be
> > > > replicated.
> > > >
> > > > For example EXCEPT (tab_part_1) skips replication of tab_part_1 and
> > > > all of its partitions.
> > > >
> > > > This behaviour remains the same with or without
> > > > publish_via_partition_root. The publish_via_partition_root flag only
> > > > affects publish_via_relid, i.e., the relation through which data is
> > > > published.
> > > >
> > > > This approach involves certain implementation challenges. For brevity,
> > > > these are documented in the attached 'Approach1_challenges' document.
> > > >
> > > > Approach 2:
> > > > ---------------------------------------------------
> > > > Assign meaning to ONLY and '*' for partition tables in the EXCEPT
> > > > list. In HEAD, ONLY and '*' do not have any meaning for partitioned
> > > > tables or partitions, and these keywords are currently ignored.
> > > >
> > > > Examples:
> > > > 1. EXCEPT (ONLY tab_part_1) skips replication of only the table
> > > > tab_part_1. Changes for tab_root, tab_part_1_p1, and tab_part_1_p2 are
> > > > still replicated.
> > > >
> > > > ii. EXCEPT (tab_part_1*) skips replication of tables tab_part_1,
> > > > tab_part_1_p1, and tab_part_1_p2
> > > >
> > > > The challenges described in Approach 1, particularly around tablesync
> > > > handling and COPY behaviour, would still need to be addressed under
> > > > this approach as well. ONLY or '*' with partitioned tables is not
> > > > supported in HEAD, supporting it specifically for ALL TABLES EXCEPT
> > > > may introduce additional confusion for users.
> > > >
> > > > Approach 3:
> > > > ----------------
> > > > Do not allow partitions to be specified in the EXCEPT clause.
> > > >
> > > > Only EXCEPT (tab_root) is supported, which excludes tab_root and all
> > > > of its partitions. Specifying EXCEPT (tab_part_1) or EXCEPT
> > > > (tab_part_1_p1) will result in an error.
> > > >
> > > > ~~
> > > >
> > > > While Approach 1 and Approach 2 offer more flexibility to the user
> > > > compared to Approach 3, they also introduce additional design
> > > > complexity which does not seem simpler to address.
> > >
> > > Thanks for explaining this, overall I like the Approach 1, and I also
> > > see the problem when publish via root is given in that case COPY FROM
> > > is executed on the root and it would be hard to exclude specific
> > > partitions.
> >
> > Regarding the above issue which is also mentioned in
> > Approach1_challenges at [1]:
> > When a publication is created with publish_via_partition_root = true
> > and a specific partition(tab_part_1_1) is excluded, the expected
> > behavior is that changes from non-excluded partitions (for example,
> > tab_part_2 and tab_part_1_2 and their descendants) are replicated,
> > while changes from the excluded partition (tab_part_1_1 and its
> > subtree) are not.
> > tab_root
> > ├── tab_part_1
> > │ ├── tab_part_1_1 (except)
> > │ │ ├── tab_part_1_1_1
> > │ │ │ └── tab_part_1_1_1_1
> > │ │ └── tab_part_1_1_2
> > │ └── tab_part_1_2
> > │ ├── tab_part_1_2_1
> > │ └── tab_part_1_2_2
> > └── tab_part_2
> >
> > In this situation, replication cannot be performed purely via the
> > partition root (tab_root), because doing so would implicitly include
> > data from the excluded child partitions.
> >
> > To address this, the publication creation should explicitly record the
> > excluded partition(tab_part_1_1) in pg_publication_rel with an
> > excluded = true flag. The publish_via_partition_root setting remains
> > stored at the publication level, as it is today. With
> > publish_via_partition_root = true, the publisher–subscriber mapping is
> > not partition-to-partition. Instead, all eligible data is mapped to
> > the subscriber’s partition root. Therefore,
> > pg_get_publication_tables() should return only the top-level root
> > table (tab_root) to the subscriber for table synchronization. During
> > initial table sync, when the tablesync worker prepares the COPY
> > command, it can query the publisher to determine the effective set of
> > tables that belong to the publication after applying the exclusion
> > rules. Based on this resolved table list, the tablesync worker can
> > construct a COPY query that unions data only from the non-excluded
> > partitions, for example:
> > COPY (
> > SELECT * FROM tab_part_1_2_1
> > UNION ALL
> > SELECT * FROM tab_part_1_2_2
> > UNION ALL
> > SELECT * FROM tab_part_2
> > )
> >
> > This ensures that only non-excluded data is copied and applied to
> > tab_root on the subscriber, while preserving the semantics of
> > publish_via_partition_root = true.
I agree with the suggested changes in tablesync. It will be good if we
can add these details in the commit-msg section of the patch. Also
please mention how increment replication is impacted (or supposed to
work) with Approach1.
> Here is a patch which has the changes to handle the same.
>
Thank You for the patch.
1)
There are certain parts of Approach 3 still present in Approach 1, as
an example:
1a)
+ For partitioned tables, only the root partitioned table may be specified
+ in <literal>EXCEPT TABLE</literal>.
1b)
+ /*
+ * Only the topmost ancestor of a partitioned table can be specified
+ * in EXCEPT TABLES clause of a FOR ALL TABLES publication. So fetch
+ * the publications excluding the topmost ancestor only.
+ */
+ GetRelationPublications(llast_oid(ancestors), NULL, &exceptpuboids);
+
1c)
+ /* Check if the partiton is part of EXCEPT list of any publication */
+ GetRelationPublications(RelationGetRelid(attachrel), NULL, &except_pubids);
+ if (except_pubids != NIL)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("cannot attach relation \"%s\" as partition because it is
part of EXCEPT list in publication",
+ RelationGetRelationName(attachrel))));
+
Overall, please take a diff of v35 and v37 to find such parts and
please correct these and others (if any).
2)
Also I don't think if below is correct statement for Approach 1:
+ * 2. For a partition, if the topmost ancestor is part of
+ * the EXCEPT TABLE list, we don't publish it.
Even if any ancestor is part of EXECPT list (not only top most) we
should not publish that partition, isn't it?
3)
I tried a scenario and found that incremental replication is not
working correctly. Attached the failing test as Approach1_v37_fail.txt
Once these basic things are corrected, I can review further.
thanks
Shveta
tab_root (RANGE range_col)
├── tab_part_1 (1–1000) PARTITION BY RANGE (i)
│ ├── tab_part_1_p1 (i 0–500)
│ └── tab_part_1_p2 (i 500–1000)
└── tab_part_2 (1000–2000) PARTITION BY RANGE (i)
├── tab_part_2_p1 (i 0–500)
└── tab_part_2_p2 (i 500–1000)
Sceanrio 1:
CREATE TABLE tab_root (range_col int, i int, j int) PARTITION BY RANGE (range_col);
CREATE TABLE tab_part_1 PARTITION OF tab_root FOR VALUES FROM (1) TO (1000) PARTITION BY RANGE (i);
CREATE TABLE tab_part_2 PARTITION OF tab_root FOR VALUES FROM (1000) TO (2000) PARTITION BY RANGE (i);
-- Leaf partitions under tab_part_1
CREATE TABLE tab_part_1_p1 PARTITION OF tab_part_1 FOR VALUES FROM (0) TO (500);
CREATE TABLE tab_part_1_p2 PARTITION OF tab_part_1 FOR VALUES FROM (500) TO (1000);
-- Leaf partitions under tab_part_2
CREATE TABLE tab_part_2_p1 PARTITION OF tab_part_2 FOR VALUES FROM (0) TO (500);
CREATE TABLE tab_part_2_p2 PARTITION OF tab_part_2 FOR VALUES FROM (500) TO (1000);
-- Leaf partition: tab_part_1_p1 (range_col 1–1000, i 0–500)
INSERT INTO tab_root (range_col, i, j) VALUES (200, 250, 2);
-- Leaf partition: tab_part_1_p2 (range_col 1–1000, i 500–1000)
INSERT INTO tab_root (range_col, i, j) VALUES (250, 750, 11);
-- Leaf partition: tab_part_2_p1 (range_col 1000–2000, i 0–500)
INSERT INTO tab_root (range_col, i, j) VALUES (1300, 250, 21);
-- Leaf partition: tab_part_2_p2 (range_col 1000–2000, i 500–1000)
INSERT INTO tab_root (range_col, i, j) VALUES (1700, 750, 31);
CREATE PUBLICATION pub1 for all tables EXCEPT(tab_part_1,tab_part_2_p2) WITH (PUBLISH_VIA_PARTITION_ROOT=true);
Sub:
CREATE TABLE tab_root (range_col int, i int, j int);
CREATE TABLE tab_part_1 (range_col int, i int, j int);
CREATE TABLE tab_part_2 (range_col int, i int, j int);
CREATE TABLE tab_part_1_p1 (range_col int, i int, j int);
CREATE TABLE tab_part_1_p2 (range_col int, i int, j int);
CREATE TABLE tab_part_2_p1 (range_col int, i int, j int);
CREATE TABLE tab_part_2_p2 (range_col int, i int, j int);
CREATE SUBSCRIPTION sub1 connection 'dbname=postgres host=localhost user=shveta port=5433' publication pub1;
--Initial data belonging to tab_part_2_p1 alone should show up in table_root
select * from tab_root;
Pub:
--Now test incremental replication:
-- Leaf partition: tab_part_1_p1 (range_col 1–1000, i 0–500)
INSERT INTO tab_root (range_col, i, j) VALUES (200, 250, 2);
-- Leaf partition: tab_part_1_p2 (range_col 1–1000, i 500–1000)
INSERT INTO tab_root (range_col, i, j) VALUES (250, 750, 11);
-- Leaf partition: tab_part_2_p1 (range_col 1000–2000, i 0–500)
INSERT INTO tab_root (range_col, i, j) VALUES (1300, 250, 21);
-- Leaf partition: tab_part_2_p2 (range_col 1000–2000, i 500–1000)
INSERT INTO tab_root (range_col, i, j) VALUES (1700, 750, 31);
Sub:
-- Expectation: data belonging to tab_part_2_p1 alone should show up in tab_root.
-- Actual: tab_part_2_p2's data also show up in tab_root
select * from tab_root;
postgres=# select * from tab_root;
range_col | i | j
-----------+-----+----
1300 | 250 | 21
1300 | 250 | 21
1700 | 750 | 31 --> tab_part_2_p2 data
(3 rows)
~~
Sceanrio 2:
CREATE PUBLICATION pub2 for all tables EXCEPT(tab_root) WITH (PUBLISH_VIA_PARTITION_ROOT=true);
--This gives error on Pub, expectation is no error:
CREATE TABLE tab_top_root (range_col int, i int, j int) PARTITION BY RANGE (range_col);
ALTER TABLE tab_top_root ATTACH PARTITION tab_root FOR VALUES FROM (0) TO (2000);
ERROR: cannot attach relation "tab_root" as partition because it is part of EXCEPT list in publication
Attachments:
[text/plain] Approach1_v37_fail.txt (3.6K, 2-Approach1_v37_fail.txt)
download | inline:
tab_root (RANGE range_col)
├── tab_part_1 (1–1000) PARTITION BY RANGE (i)
│ ├── tab_part_1_p1 (i 0–500)
│ └── tab_part_1_p2 (i 500–1000)
└── tab_part_2 (1000–2000) PARTITION BY RANGE (i)
├── tab_part_2_p1 (i 0–500)
└── tab_part_2_p2 (i 500–1000)
Sceanrio 1:
CREATE TABLE tab_root (range_col int, i int, j int) PARTITION BY RANGE (range_col);
CREATE TABLE tab_part_1 PARTITION OF tab_root FOR VALUES FROM (1) TO (1000) PARTITION BY RANGE (i);
CREATE TABLE tab_part_2 PARTITION OF tab_root FOR VALUES FROM (1000) TO (2000) PARTITION BY RANGE (i);
-- Leaf partitions under tab_part_1
CREATE TABLE tab_part_1_p1 PARTITION OF tab_part_1 FOR VALUES FROM (0) TO (500);
CREATE TABLE tab_part_1_p2 PARTITION OF tab_part_1 FOR VALUES FROM (500) TO (1000);
-- Leaf partitions under tab_part_2
CREATE TABLE tab_part_2_p1 PARTITION OF tab_part_2 FOR VALUES FROM (0) TO (500);
CREATE TABLE tab_part_2_p2 PARTITION OF tab_part_2 FOR VALUES FROM (500) TO (1000);
-- Leaf partition: tab_part_1_p1 (range_col 1–1000, i 0–500)
INSERT INTO tab_root (range_col, i, j) VALUES (200, 250, 2);
-- Leaf partition: tab_part_1_p2 (range_col 1–1000, i 500–1000)
INSERT INTO tab_root (range_col, i, j) VALUES (250, 750, 11);
-- Leaf partition: tab_part_2_p1 (range_col 1000–2000, i 0–500)
INSERT INTO tab_root (range_col, i, j) VALUES (1300, 250, 21);
-- Leaf partition: tab_part_2_p2 (range_col 1000–2000, i 500–1000)
INSERT INTO tab_root (range_col, i, j) VALUES (1700, 750, 31);
CREATE PUBLICATION pub1 for all tables EXCEPT(tab_part_1,tab_part_2_p2) WITH (PUBLISH_VIA_PARTITION_ROOT=true);
Sub:
CREATE TABLE tab_root (range_col int, i int, j int);
CREATE TABLE tab_part_1 (range_col int, i int, j int);
CREATE TABLE tab_part_2 (range_col int, i int, j int);
CREATE TABLE tab_part_1_p1 (range_col int, i int, j int);
CREATE TABLE tab_part_1_p2 (range_col int, i int, j int);
CREATE TABLE tab_part_2_p1 (range_col int, i int, j int);
CREATE TABLE tab_part_2_p2 (range_col int, i int, j int);
CREATE SUBSCRIPTION sub1 connection 'dbname=postgres host=localhost user=shveta port=5433' publication pub1;
--Initial data belonging to tab_part_2_p1 alone should show up in table_root
select * from tab_root;
Pub:
--Now test incremental replication:
-- Leaf partition: tab_part_1_p1 (range_col 1–1000, i 0–500)
INSERT INTO tab_root (range_col, i, j) VALUES (200, 250, 2);
-- Leaf partition: tab_part_1_p2 (range_col 1–1000, i 500–1000)
INSERT INTO tab_root (range_col, i, j) VALUES (250, 750, 11);
-- Leaf partition: tab_part_2_p1 (range_col 1000–2000, i 0–500)
INSERT INTO tab_root (range_col, i, j) VALUES (1300, 250, 21);
-- Leaf partition: tab_part_2_p2 (range_col 1000–2000, i 500–1000)
INSERT INTO tab_root (range_col, i, j) VALUES (1700, 750, 31);
Sub:
-- Expectation: data belonging to tab_part_2_p1 alone should show up in tab_root.
-- Actual: tab_part_2_p2's data also show up in tab_root
select * from tab_root;
postgres=# select * from tab_root;
range_col | i | j
-----------+-----+----
1300 | 250 | 21
1300 | 250 | 21
1700 | 750 | 31 --> tab_part_2_p2 data
(3 rows)
~~
Sceanrio 2:
CREATE PUBLICATION pub2 for all tables EXCEPT(tab_root) WITH (PUBLISH_VIA_PARTITION_ROOT=true);
--This gives error on Pub, expectation is no error:
CREATE TABLE tab_top_root (range_col int, i int, j int) PARTITION BY RANGE (range_col);
ALTER TABLE tab_top_root ATTACH PARTITION tab_root FOR VALUES FROM (0) TO (2000);
ERROR: cannot attach relation "tab_root" as partition because it is part of EXCEPT list in publication
view thread (368+ messages) latest in thread
reply
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Skipping schema changes in publication
In-Reply-To: <CAJpy0uBfEuzYX+qjAPM+GV5duOwMNqO6fkDtsN1OzONVNR9WGQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox