Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vmsQQ-00HI6j-2d for pgsql-hackers@arkaria.postgresql.org; Mon, 02 Feb 2026 11:49:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vmsQO-00DdWv-2E for pgsql-hackers@arkaria.postgresql.org; Mon, 02 Feb 2026 11:49:01 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vmsQO-00DdWl-0M for pgsql-hackers@lists.postgresql.org; Mon, 02 Feb 2026 11:49:01 +0000 Received: from mail-oa1-x2a.google.com ([2001:4860:4864:20::2a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vmsQM-00000000BdI-3Eel for pgsql-hackers@lists.postgresql.org; Mon, 02 Feb 2026 11:48:59 +0000 Received: by mail-oa1-x2a.google.com with SMTP id 586e51a60fabf-409440b98b5so2542359fac.2 for ; Mon, 02 Feb 2026 03:48:58 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1770032938; cv=none; d=google.com; s=arc-20240605; b=AlzP7pxoVXldDWQ/bBDVLVZzczJcRrDE3gkMtck1Nr9A5gU3M+KnAxM2H61+v+4zOV c2cyNP9rmNuMxs3JCtcUvAtGO4yYs+3PAkDNobhvujaIZAiaYd4YI4hhxsY/l/5KA1/W QxBWiUZeof2fo/YWNMTQhvCitqZCZoY1Az1ij+TDyySbG4P4yaePUzuJOPzP7fPbqXcO HHgfnMkh297f0F4VYNlaDr9Kby5byz6ISjuEx8lzLT36p6vv3/sL0HTD5HO+U4XfJqcZ fTmSVFN/1LtP5rNirR0CyGRt6Ugu3w/J80HpjqZ79UJA0pGnN7O6O3FbXBygcgt5ZG6J lXhg== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=GCXvUJc2GDmsnYGfTgPph532qB7dlW+CW/SbD3dG3gc=; fh=FMIhK+5jJzLj1/OBvdZSpzcQF9IYNApDz8Mcrc2yYbY=; b=ZxSrrtzG09JEE5gSU0iDUzsFmNr/bdi1SFx13oAahQ0K+//49RWVrZoTy8EV8tIuQd yYvFYKG1Y57V6Ty6E+QsmQMYgHH7wVwiV/8PokHkogWRdBakHYaQDsGDV8pZo62tPuOq 712VAx+1E7rOGKCLYglzQz0rsUHQ8pvAuWEVkmKgQkHE3/wbOKVKsMAIRj6OVYe3sge5 hkYQ9yuOu3YBVJRUB3zwlNqYnx0vNeCc1B3KKSxIjaFLzXAsI5HcweYHL7AvmxlddIeC sviMiHOSoKZEtb6B6PRgfVWKp5dFT/L+XKxZ46/lHxUlZsxAlVTunq58VUpqdG5TypzJ 8YyQ==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1770032938; x=1770637738; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=GCXvUJc2GDmsnYGfTgPph532qB7dlW+CW/SbD3dG3gc=; b=iaQ1C81h9YmbLwLTFe9W9qYvFSpUjSw/ZmM2AkAnjMbSRyBfgYPgMlCCbKVqJPARHm tZ3bNnA0JbtypLKYLs48aaZdZsqrIMfG1KWbwQY8/ZLUU2LdWLFziEuq1TOOYpI1FZHr z9Qd3GQPwVVrfL2fnVndYTJR1jfg77iAh1R4LVZSvTsZ6HGj7zZCLImJac6IDRRO63gQ 59lGQYxx7si/OSLPv9hPh3+7EOd2FiDBGeWZnqVe6N/99Om16Dwqnz+QPBhSNEu4bxW0 MOnFaze88IXB+YsWuF1qxV1WemKueE8qnLrvSlaoh1QazNT4H87LBBmCYMU0W3/SdddR 9ruA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1770032938; x=1770637738; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=GCXvUJc2GDmsnYGfTgPph532qB7dlW+CW/SbD3dG3gc=; b=P+iMon0Eg1KZU0z8QBNflgS8bhlfEHyWAxSkflus/Jn2S46cTzJd83c7w+EWAbsahz HyNIFko5TZmh4taBvOfiNHzp/W6bHtIqhT6sgEuqeHnaTw/TLFTezjRhn13XsAb1ulIX 91xz0LfAX4vJTylkzcpGxpVJrLX6CLFQFN6BUhYNCRl8VBpfdOw0KOfOCpVLtCAOIz9B yok6dkhiGVL65jreXwqRVj97J9nuwdD8+AylBKZqn7ORbGjte3W2pmYmxbWv/2VQnpok SSnctjs/kj6OKiGLsfYa1WYGsCUbPrZM4N8hdVt897J0am6HRXjWsZlVrctjolk5AHon fnUQ== X-Forwarded-Encrypted: i=1; AJvYcCXZHWkFrdbcAbGF5XRFiNIAqpqCMWYm22L272VtYr67injRQ86kkqLAhKCn8bLCHNQffJC4ZzA/fuasEHcI@lists.postgresql.org X-Gm-Message-State: AOJu0YynHvnGTxUR6fl8zSd3Htryz6rLKXzIWFkwTA9XaBACC9WSA4to E2RfIMtwVKA/GPEl/BZGP0tg75zTeZz0Gw7AlBOl41PfBCjGvfEnrwPPL5lVNCEJjWpJrorsoqM ZYVPkogdMSrlQbuXFinbqMfzm8930Aak= X-Gm-Gg: AZuq6aKW2CvsmVtNDwhcKDsCoqEuqHePWfl4OPf8D2QwISi6kg51FksRbOh8yF9MvW1 QTOcPcqZ19FTFPErm1lKFuHvxlZ5TjalRW+KrzVNlBqmO8CATxITdg1v4t/WfMuoPMC45cSWeN8 n0uW+uA+KRpljbf0U33LYsnCAznnjllCDWusmv9WGd+1JxJajC6uhMt/hSCOwdB8vGFL+k3nPv4 tph5RP6/8InOtFMfN5sXrRM/vlD8YLg8y8BakWOraZyn4tZbRpbRo5x5IpVg/CgmuV1tAB6olhW 1n6keA== X-Received: by 2002:a05:6820:2217:b0:662:f0cb:84c5 with SMTP id 006d021491bc7-6630f04f336mr5751270eaf.34.1770032937984; Mon, 02 Feb 2026 03:48:57 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Shlok Kyal Date: Mon, 2 Feb 2026 17:18:45 +0530 X-Gm-Features: AZwV_Qg2IPwSQI0JrtIFRvcqs12pD0GK0V7DVLAfqB8wMb88XBtXx7Fp5OR3_4A Message-ID: Subject: Re: Skipping schema changes in publication To: vignesh C Cc: shveta malik , Dilip Kumar , Amit Kapila , Peter Smith , "Zhijie Hou (Fujitsu)" , YeXiu <1518981153@qq.com>, Ian Lawrence Barwick , Bharath Rupireddy , PostgreSQL Hackers Content-Type: text/plain; charset="UTF-8" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Thu, 29 Jan 2026 at 20:41, vignesh C wrote: > > On Wed, 28 Jan 2026 at 10:46, shveta malik 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 EXCEPT TABLE. > > > > 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/CAJpy0uD81HRrMYr7S-6AV4W2PtbGKM-nf2D89zsoMHJ9jZssUg@mail.gmail.com Thanks, Shlok Kyal