public inbox for [email protected]
help / color / mirror / Atom feedFrom: Shlok Kyal <[email protected]>
To: vignesh C <[email protected]>
Cc: shveta malik <[email protected]>
Cc: Dilip Kumar <[email protected]>
Cc: Amit Kapila <[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]>
Subject: Re: Skipping schema changes in publication
Date: Mon, 2 Feb 2026 17:18:45 +0530
Message-ID: <CANhcyEXP5BS+0Hq2o=Jmx6J5Nf+H0TW9yayO21NnVwtFqauDGw@mail.gmail.com> (raw)
In-Reply-To: <CALDaNm2MZU8-JbFruQAxF8OZfcH4ZsBrCsWDg3VMbO-P+xKmBg@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>
<CAJpy0uBfEuzYX+qjAPM+GV5duOwMNqO6fkDtsN1OzONVNR9WGQ@mail.gmail.com>
<CALDaNm2MZU8-JbFruQAxF8OZfcH4ZsBrCsWDg3VMbO-P+xKmBg@mail.gmail.com>
On Thu, 29 Jan 2026 at 20:41, vignesh C <[email protected]> wrote:
>
> On Wed, 28 Jan 2026 at 10:46, shveta malik <[email protected]> wrote:
> >
> > 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.
>
> These comments are addressed in the v38 version patch attached.
> Currently the approach-3 changes is present separately in
> v38-0002-Restrict-EXCEPT-TABLE-to-root-partitioned-tables-apporach-3.patch
> which can be applied on top of
> v38-0001-Skip-publishing-the-tables-specified-in-EXCEPT-T.patch.
> Similarly the approach-1 changes is present separately in
> v38-0002-handle-EXCEPT-TABLE-correctly-with-partitioned-approach-1.patch
> which can be applied on top of
> v38-0001-Skip-publishing-the-tables-specified-in-EXCEPT-T.patch.
> Currently few of the query are logged as LOG messages, I will reduce
> the log level for these queries once few rounds of review are
> completed on the queries.
Hi Vignesh,
I reviewed v38-0002-handle-EXCEPT-TABLE-correctly-with-partitioned-approach-1.patch
patch. Here are my comments:
1. in pgoutput.c:
@@ -2228,6 +2228,11 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
{
List *ancestors = get_partition_ancestors(relid);
+ GetRelationPublications(relid, NULL, &exceptpubids);
+
+ foreach_oid(ancestor, ancestors)
+ GetRelationPublications(ancestor, NULL, &exceptpubids);
+
pub_relid = llast_oid(ancestors);
ancestor_level = list_length(ancestors);
}
when we create a publication on a partitioned table and
publish_via_partition_root is false,
the changes of the partitions should not be published as discussed.
But, while testing I found that the changes are being published.
Test:
CREATE SCHEMA sc1;
CREATE TABLE sc1.t1(id int) PARTITION BY RANGE(id);
CREATE TABLE sc1.child1(id int) PARTITION BY RANGE(id);
CREATE TABLE sc1.child2 PARTITION OF sc1.t1 FOR VALUES FROM (101) TO (200);
CREATE TABLE sc1.child1_1 PARTITION OF sc1.child1 FOR VALUES FROM (0) TO (50);
CREATE TABLE sc1.child1_2 PARTITION OF sc1.child1 FOR VALUES FROM (51) TO (100);
ALTER TABLE sc1.t1 ATTACH PARTITION sc1.child1 FOR VALUES FROM (0) TO (100);
CREATE PUBLICATION pub1 FOR ALL TABLES EXCEPT TABLE (sc1.t1);
insert into sc1. t1 values (1), (60), (150);
I get following output:
postgres=# SELECT * FROM pg_logical_slot_get_binary_changes(
's1',
NULL,
NULL,
'proto_version', '1',
'publication_names', 'pub1'
);
lsn | xid | data
------------+-----+----------------------------------------------------------------------
0/0175A1A0 | 777 | \x42000000000175a3f80002eccfc016338000000309
0/0175A1A0 | 777 |
\x520000400a736331006368696c64315f31006400010069640000000017ffffffff
0/0175A1A0 | 777 | \x490000400a4e0001740000000131
0/0175A268 | 777 |
\x520000400d736331006368696c64315f32006400010069640000000017ffffffff
0/0175A268 | 777 | \x490000400d4e000174000000023630
0/0175A330 | 777 |
\x5200004007736331006368696c6432006400010069640000000017ffffffff
0/0175A330 | 777 | \x49000040074e00017400000003313530
0/0175A428 | 777 | \x4300000000000175a3f8000000000175a4280002eccfc0163380
(8 rows)
I think this check should be done irrespective of the value of
publish_via_partition_root.
+ foreach_oid(ancestor, ancestors)
+ GetRelationPublications(ancestor, NULL, &exceptpubids);
2. In get_rel_sync_entry we have a comment:
*
* 2. If pubviaroot is not set, check whether the relation itself
* is included in the EXCEPT TABLE list. If so, do not publish the
* change.
*
This comment does not seem correct. Even if publish_via_partition_root
is false, we should not publish changes if any of its ancestors is
excluded.
This is my understanding of approach 1 [1]. Correct me if I am wrong.
3. In tablesync.c:
+ /*
+ * Store the tables as a list of schemaname and tablename.
+ */
+ natt = 0;
this assignment 'natt = 0', is not required
4. While testing, I noticed that the new query introduced in tablesync
can be invoked for "FOR TABLE". SHould we only call it for "ALL
TABLES" publications?
+ if (server_version >= 190000 && !is_partition &&
+ lrel->relkind == RELKIND_PARTITIONED_TABLE)
+ {
+ resetStringInfo(&cmd);
+
+ /*
+ * This query recursively traverses the inheritance (partition) tree
+ * starting from the given table OID and determines which leaf
+ * relations should be included for replication. Exclusion propagates
+ * from parent to child, and a relation is also treated as excluded if
+ * it is explicitly marked with prexcept = true in pg_publication_rel
+ * for the specified publications. The final result returns only
+ * non excluded leaf relations.
+ */
Test:
Create publication for table sc1.t1 using (publish_via_partition_root
= true) and create subscription on it. In subscriber logs we can see
the logs for the new query.
sc1.t1 has the same structure as in comment 1.
5. I was testing some scenarios and found a difference in behaviour of
tablesync and incremental changes.
Consider the same table structure as comment1. But we have two publications as:
CREATE PUBLICATION pub1 FOR ALL TABLES EXCEPT TABLE (sc1.child1) WITH
(publish_via_partition_root = true);
CREATE PUBLICATION pub2 FOR ALL TABLES WITH (publish_via_partition_root = true);
And subscription is created
CREATE SUBSCRIPTION sub1 CONNECTION 'host=localhost port=5432
dbname=postgres' PUBLICATION pub1, pub2;
Before creating the subscription I did a insert on publisher node:
INSERT INTO sc1.t1 VALUES(1), (51), (101);
Output after tablesync:
postgres=# select * from sc1.t1;
id
-----
101
(1 row)
Now, I again did a insert on publisher node:
INSERT INTO sc1.t1 VALUES(1), (51), (101);
Output after incremental sync:
postgres=# select * from sc1.t1;
id
-----
101
1
51
101
(4 rows)
What should be the behaviour if a partitioned table is published by a
publication and excluded by another publication?
I checked the behaviour of publication with row_filter for similar conditions:
CREATE PUBLICATION pub1 FOR TABLE sc1.t1 WHERE (id > 100) WITH
(publish_via_partition_root = true);
CREATE PUBLICATION pub2 FOR TABLE sc1.t1 WITH
(publish_via_partition_root = true);
Here is the behaviour:
Before creating the publication I did a insert on publisher node:
INSERT INTO sc1.t1 VALUES(1), (51), (101);
Output after tablesync:
postgres=# select * from sc1.t1;
id
-----
101
1
51
(3 rows)
Now, I again did a insert on publisher node:
INSERT INTO sc1.t1 VALUES(1), (51), (101);
Output after incremental sync:
postgres=# select * from sc1.t1;
id
-----
101
1
51
1
51
101
(6 rows)
So, I think in the EXCEPT table case we should follow the same
behaviour with tablesync i.e. publish the change if any of the
publications publishes it . Thoughts?
[1]: https://www.postgresql.org/message-id/[email protected]...
Thanks,
Shlok Kyal
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: <CANhcyEXP5BS+0Hq2o=Jmx6J5Nf+H0TW9yayO21NnVwtFqauDGw@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