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 1vjGwh-00CBZf-2p for pgsql-hackers@arkaria.postgresql.org; Fri, 23 Jan 2026 13:11:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vjGwe-00HXhe-24 for pgsql-hackers@arkaria.postgresql.org; Fri, 23 Jan 2026 13:11:24 +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 1vjGwe-00HXhW-0J for pgsql-hackers@lists.postgresql.org; Fri, 23 Jan 2026 13:11:24 +0000 Received: from mail-yx1-xb133.google.com ([2607:f8b0:4864:20::b133]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vjGwb-001v37-1q for pgsql-hackers@lists.postgresql.org; Fri, 23 Jan 2026 13:11:23 +0000 Received: by mail-yx1-xb133.google.com with SMTP id 956f58d0204a3-64942ebf1a3so2203402d50.3 for ; Fri, 23 Jan 2026 05:11:22 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1769173881; cv=none; d=google.com; s=arc-20240605; b=dkn3jGU0s3bG+WsY7zSNKV5QG7SGHkCWFU5XX9TNL0aFTmPDdUoBYsNYMKnx+OWgf0 SYaSF9YDQFQAweRG3IHu8ZoZsC7HVnWisSu5R2Jyb/mOj4bkeRAdoiEauvHHW9PTyeNM 3UL2wWxkaA0I6941PCfnxN6qdnLffbeUerNes+zsH3gnCqlJ+MNb7QkgJI2ZmBC/DOtp fgdDT4FYQH6dHUTbUObH35Y6d1rYKfodnQq5caZbHL+jaxymqt6Svt8njzRTTfCiIFxp hm4hMG2SKdL0JENktgdv46YFkw3Sz5N1eYH1IvkO5sxgiZOko8eNI62jfmp5O1ccQMXk lR7Q== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=content-transfer-encoding:cc:to:subject:message-id:date:from :in-reply-to:references:mime-version:dkim-signature; bh=DRZfFvr6QTWQ5r42sJFzMUM8w14VJJNhScjURwfah+Q=; fh=Jsj7QQi52bK8ez/hKHJW6DDHoSg2Gv3VzSP4h/Aolbc=; b=RIOyV6pTUHUBJk7ElKPz5IbzKr7xEN3HFrqOOAaGAzBV9flMSKf8hz13wF8XdVGkKV xvdlLJR47vJnVI1ECeHrx4+ueOJpnQakEHxpsODGgMOpS4v3ZetyJ7DSZirShk/pfxe8 eJRnQPE9uzR+agqZyUFZvSHV+UOXrgnSiKMP1bO74oH5qHek8t8Wp24+Rzdk83VJG3aq uF4eYExVA13Wyc5ikcVZIY7PHwwLepkuE2QcPXTkgzPjA1y6UH6AOgC2IF2oc6Yz+j6x IsKC93kGBPd4QlDC4GDHT7zAacxz06M+z1c4rNG3IlLZWkiRHioZi+xzO5rxbbsPWDrP cVWg==; 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=1769173881; x=1769778681; darn=lists.postgresql.org; h=content-transfer-encoding:cc:to:subject:message-id:date:from :in-reply-to:references:mime-version:from:to:cc:subject:date :message-id:reply-to; bh=DRZfFvr6QTWQ5r42sJFzMUM8w14VJJNhScjURwfah+Q=; b=bsVhf1g8NZKDQu/udD36J+2QWujsBzITgYYm4Sez0KP3tfG3yGHTxm4RFPMjLzjA0N Hklowe/x0J3oXhUxEWel5YC5fCslU9yhLiNs3GNdzogHjP6xKrlyeyolY3rOUWqL0Lu2 1Z/PDoHctRF1OJ9ZrTtpgdYSC5gdBh10bf1/D+nUskiJd5NURX9DCVwbUdvEcqa9Qzqr NJUxptgsqpshJqOhs8/ZvHT1JfJK7IOa6oHkrM1K8RJhqO92fKcm2REbgFpfXOAZg8IY 23izTZnKToPgWMPmmfTB1Op+lxZ7DWqW3+i+UyRaZ9cvNKNBmd0UJ21eS5t5TysTOvdT hDRA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1769173881; x=1769778681; h=content-transfer-encoding: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=DRZfFvr6QTWQ5r42sJFzMUM8w14VJJNhScjURwfah+Q=; b=PpjsMG/h1WUm8NGQveH1QReU7kVVBnsNLI1302DpVw4nT9Z2ThEfwR89U9/aa/ju5c HCWfDdVd19hCApZw3rFFjIt9rOwsVMws5+F300T4QYsONTYbMoqZ6SvCN1c677I4fQgk UMjQtp+0YwMiRvD508aBv7joCuFvnGQjfDdGSnsycSJ0f+JzFjm2lyKDymaKuqQxSJi5 6czcF2iuhguQDZDPI5Ut/FduJLxgg6qWPsFhRUYzcqHi83vBZh2omGap4ch6wNhKqfVI f1aY58Gs3VIWdSuZiITynx6YluFyJOasLLL/QsAqJwAVAHr1wJMvqrh4Sa1tJ2IV0drD 8Img== X-Forwarded-Encrypted: i=1; AJvYcCX4ZrryRb0pAByBLlaKORB6cj5gU7nBv7/uDq1wPeVQ47WCNpi5lZqjaIwUjV0mQ9K8AjZ2POzS7N28S0//@lists.postgresql.org X-Gm-Message-State: AOJu0YwqlJcAtPJSg1LzG5HhioQbDMp4qU+o3m4AQdDRzXbeElw6iR/o V8qdrK4ew7HRx9N/VNc6eef/G0wjV+HViniogmgbfar2iOsoTfuSYPMnsGThW1OuzK3mIdsT9y7 qrmMQocMxVQrxBc9Ib4OfFhts+5G096o= X-Gm-Gg: AZuq6aJaxIeEWOYpsKrRmj1Cg7hSj1FaAlX1tbrgFyAPnS91e5vUZOFm2dw58B3b5/3 WNsbUKs7/FERKmxmzGpRmPLQvaRwmHWgWrm/cgyK6LCqj2nVf7ERxnmwqPhPHOYUFlegmXYGy91 4sydiZEv5mOp1bePVplvQ8WIpcxqMI21OrJ0Oz5lGHj1PUFOofiSkNxFOSj5dzusONAddQRh9XM 5RC0XcCfvC2QnTAIY5/ZauLKXaThQFcyY9qNnnjg/0i6SGpYypB6ZfCAU2GRdSYnKJT4KEIupgZ DdxnoyDt X-Received: by 2002:a05:690e:4004:b0:648:f57b:d07d with SMTP id 956f58d0204a3-6495be6be2dmr2178008d50.7.1769173881491; Fri, 23 Jan 2026 05:11:21 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: vignesh C Date: Fri, 23 Jan 2026 18:41:09 +0530 X-Gm-Features: AZwV_QgW4UYPVTb0GP9Y-yO-hDPQgamniXRIZuJJewwsqOX4K1tP1gkoioLcIPY Message-ID: Subject: Re: Skipping schema changes in publication To: Dilip Kumar Cc: shveta malik , Amit Kapila , Shlok Kyal , Peter Smith , "Zhijie Hou (Fujitsu)" , YeXiu <1518981153@qq.com>, Ian Lawrence Barwick , Bharath Rupireddy , PostgreSQL Hackers Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Wed, 21 Jan 2026 at 11:35, Dilip Kumar wrote: > > On Mon, Jan 19, 2026 at 3:08=E2=80=AFPM shveta malik 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=E2=80=99d like to hear others=E2=80=99 o= pinions on > > these approaches. > > > > Consider the following partition hierarchy: > > tab_root > > =E2=94=9C=E2=94=80 tab_part_1 > > =E2=94=82 =E2=94=9C=E2=94=80 tab_part_1_p1 > > =E2=94=82 =E2=94=94=E2=94=80 tab_part_1_p2 > > =E2=94=94=E2=94=80 tab_part_2 > > =E2=94=9C=E2=94=80 tab_part_2_p1 > > =E2=94=94=E2=94=80 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 =3D 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 =E2=94=9C=E2=94=80=E2=94=80 tab_part_1 =E2=94=82 =E2=94=9C=E2=94=80=E2=94=80 tab_part_1_1=E2=80=83=E2=80=83=E2= =80=83=E2=80=83=E2=80=83=E2=80=83=E2=80=83=E2=80=83(except) =E2=94=82 =E2=94=82 =E2=94=9C=E2=94=80=E2=94=80 tab_part_1_1_1 =E2=94=82 =E2=94=82 =E2=94=82 =E2=94=94=E2=94=80=E2=94=80 tab_part_1_= 1_1_1 =E2=94=82 =E2=94=82 =E2=94=94=E2=94=80=E2=94=80 tab_part_1_1_2 =E2=94=82 =E2=94=94=E2=94=80=E2=94=80 tab_part_1_2 =E2=94=82 =E2=94=9C=E2=94=80=E2=94=80 tab_part_1_2_1 =E2=94=82 =E2=94=94=E2=94=80=E2=94=80 tab_part_1_2_2 =E2=94=94=E2=94=80=E2=94=80 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 =3D true flag. The publish_via_partition_root setting remains stored at the publication level, as it is today. With publish_via_partition_root =3D true, the publisher=E2=80=93subscriber mappi= ng is not partition-to-partition. Instead, all eligible data is mapped to the subscriber=E2=80=99s 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 =3D true. Thoughts? [1] - https://www.postgresql.org/message-id/CAJpy0uD81HRrMYr7S-6AV4W2PtbGKM= -nf2D89zsoMHJ9jZssUg%40mail.gmail.com Regards, Vignesh