public inbox for [email protected]
help / color / mirror / Atom feedFrom: vignesh C <[email protected]>
To: shveta malik <[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]>
Subject: Re: Skipping schema changes in publication
Date: Thu, 29 Jan 2026 20:41:26 +0530
Message-ID: <CALDaNm2MZU8-JbFruQAxF8OZfcH4ZsBrCsWDg3VMbO-P+xKmBg@mail.gmail.com> (raw)
In-Reply-To: <CAJpy0uBfEuzYX+qjAPM+GV5duOwMNqO6fkDtsN1OzONVNR9WGQ@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>
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.
Regards,
Vignesh
Attachments:
[text/x-patch] v38-0002-Restrict-EXCEPT-TABLE-to-root-partitioned-tables-apporach-3.patch (20.6K, 2-v38-0002-Restrict-EXCEPT-TABLE-to-root-partitioned-tables-apporach-3.patch)
download | inline diff:
From d81ed92c169a86753f426a1049f68198823c34c9 Mon Sep 17 00:00:00 2001
From: Vignesh C <[email protected]>
Date: Wed, 28 Jan 2026 10:58:56 +0530
Subject: [PATCH v38] Restrict EXCEPT TABLE to root partitioned tables
Only root partitioned tables can be specified in the EXCEPT TABLE clause.
Specifying a root partitioned table excludes all partitions belonging to
that partition hierarchy from publication, irrespective of the option
publish_via_partition_root.
This is based on approach 3 discussed at:
https://www.postgresql.org/message-id/CAJpy0uD81HRrMYr7S-6AV4W2PtbGKM-nf2D89zsoMHJ9jZssUg@mail.gmail.com
This patch is a topup patch on top of 0001 patch.
---
doc/src/sgml/ref/create_publication.sgml | 15 +-
src/backend/catalog/pg_publication.c | 26 +--
src/backend/commands/tablecmds.c | 9 +
src/backend/replication/pgoutput/pgoutput.c | 39 ++--
src/backend/utils/cache/relcache.c | 13 +-
.../t/037_rep_changes_except_table.pl | 177 ++++++++++--------
6 files changed, 140 insertions(+), 139 deletions(-)
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index 1e091bb3c6d..61974f41fd9 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -205,16 +205,11 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
tables are excluded.
</para>
<para>
- For partitioned tables, when <literal>publish_via_partition_root</literal>
- is set to <literal>true</literal>, specifying a root partitioned table in
- <literal>EXCEPT TABLE</literal> excludes it and all its partitions from
- replication. Specifying a leaf partition has no effect, as its changes are
- still replicated via the root partitioned table. When
- <literal>publish_via_partition_root</literal> is set to
- <literal>false</literal>, specifying a root partitioned table has no
- effect, as changes are replicated via the leaf partitions. Specifying a
- leaf partition excludes only that partition from replication. The optional
- <literal>*</literal> has no meaning for partitioned tables.
+ For partitioned tables, only the root partitioned table may be specified
+ in <literal>EXCEPT TABLE</literal>. Doing so excludes the root table and
+ all of its partitions from replication, regardless of the value of
+ <literal>publish_via_partition_root</literal>. The optional
+ <literal>*</literal> has no effect for partitioned tables.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index 09c69005122..0fdddd96704 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -469,24 +469,12 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri,
}
/*
- * Handle the case where a partition is excluded by EXCEPT TABLE while
- * publish_via_partition_root = true.
+ * Handle the case where a partition is excluded by EXCEPT TABLE
*/
- if (pub->alltables && pub->pubviaroot && pri->except &&
- targetrel->rd_rel->relispartition)
- ereport(WARNING,
- (errmsg("partition \"%s\" might be replicated as publish_via_partition_root is \"%s\"",
- RelationGetRelationName(targetrel), "true")));
-
- /*
- * Handle the case where a partitioned table is excluded by EXCEPT TABLE
- * while publish_via_partition_root = false.
- */
- if (pub->alltables && !pub->pubviaroot && pri->except &&
- targetrel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
- ereport(WARNING,
- (errmsg("partitioned table \"%s\" might be replicated as publish_via_partition_root is \"%s\"",
- RelationGetRelationName(targetrel), "false")));
+ if (pub->alltables && pri->except && targetrel->rd_rel->relispartition)
+ ereport(ERROR,
+ (errmsg("partition \"%s\" cannot be excluded using EXCEPT TABLE",
+ RelationGetRelationName(targetrel))));
check_publication_add_relation(targetrel);
@@ -960,8 +948,8 @@ GetAllPublicationRelations(Publication *pub, char relkind)
if (relkind == RELKIND_RELATION)
exceptlist = GetAllPublicationExcludedTables(pubid, pubviaroot ?
- PUBLICATION_PART_ALL :
- PUBLICATION_PART_ROOT);
+ PUBLICATION_PART_ROOT :
+ PUBLICATION_PART_LEAF);
classRel = table_open(RelationRelationId, AccessShareLock);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index a5351fc59c6..395b8c0c2a1 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -20325,6 +20325,7 @@ ATExecAttachPartition(List **wqueue, Relation rel, PartitionCmd *cmd,
const char *trigger_name;
Oid defaultPartOid;
List *partBoundConstraint;
+ List *except_pubids = NIL;
ParseState *pstate = make_parsestate(NULL);
pstate->p_sourcetext = context->queryString;
@@ -20449,6 +20450,14 @@ ATExecAttachPartition(List **wqueue, Relation rel, PartitionCmd *cmd,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("cannot attach temporary relation of another session as partition")));
+ /* 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))));
+
/*
* Check if attachrel has any identity columns or any columns that aren't
* in the parent.
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 05802482c10..48dcf0fedc4 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -2206,16 +2206,11 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
* root and set the ancestor level accordingly.
*
* If this is a FOR ALL TABLES publication and it has an EXCEPT
- * TABLE list:
- *
- * 1. If pubviaroot is set and the relation is a partition, check
- * whether the partition root is included in the EXCEPT TABLE
- * list. If so, do not publish the change.
- *
- * 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.
- *
+ * TABLE list
+ * 1. For a normal table or a partitioned table, if it is part of
+ * the EXCEPT TABLE list, we don't publish it.
+ * 2. For a partition, if the topmost ancestor is part of
+ * the EXCEPT TABLE list, we don't publish it.
* This is achieved by keeping the variable "publish" set to
* false. And eventually, entry->pubactions will remain all false
* for this publication.
@@ -2223,27 +2218,23 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
if (pub->alltables)
{
List *exceptpubids = NIL;
+ List *ancestors = get_partition_ancestors(relid);
+ Oid root_relid = relid;
- if (pub->pubviaroot && am_partition)
+ if (am_partition)
{
- List *ancestors = get_partition_ancestors(relid);
+ root_relid = llast_oid(ancestors);
+ GetRelationPublications(root_relid, NULL, &exceptpubids);
- pub_relid = llast_oid(ancestors);
- ancestor_level = list_length(ancestors);
+ if (pub->pubviaroot)
+ {
+ pub_relid = root_relid;
+ ancestor_level = list_length(ancestors);
+ }
}
- GetRelationPublications(pub_relid, NULL, &exceptpubids);
-
if (!list_member_oid(exceptpubids, pub->oid))
publish = true;
- else
- {
- /* Sanity check */
- Assert(entry->pubactions.pubinsert == false &&
- entry->pubactions.pubupdate == false &&
- entry->pubactions.pubdelete == false &&
- entry->pubactions.pubtruncate == false);
- }
list_free(exceptpubids);
}
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index dc021dbb6cd..ec7f82fbcdc 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -5837,21 +5837,24 @@ RelationBuildPublicationDesc(Relation relation, PublicationDesc *pubdesc)
/* Add publications that the ancestors are in too. */
ancestors = get_partition_ancestors(relid);
+ /*
+ * 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);
+
foreach(lc, ancestors)
{
Oid ancestor = lfirst_oid(lc);
List *ancestor_puboids = NIL;
- List *ancestor_exceptpuboids = NIL;
- GetRelationPublications(ancestor, &ancestor_puboids,
- &ancestor_exceptpuboids);
+ GetRelationPublications(ancestor, &ancestor_puboids, NULL);
puboids = list_concat_unique_oid(puboids, ancestor_puboids);
schemaid = get_rel_namespace(ancestor);
puboids = list_concat_unique_oid(puboids,
GetSchemaPublications(schemaid));
- exceptpuboids = list_concat_unique_oid(exceptpuboids,
- ancestor_exceptpuboids);
}
}
diff --git a/src/test/subscription/t/037_rep_changes_except_table.pl b/src/test/subscription/t/037_rep_changes_except_table.pl
index 95904ddd005..4c3c81462a5 100644
--- a/src/test/subscription/t/037_rep_changes_except_table.pl
+++ b/src/test/subscription/t/037_rep_changes_except_table.pl
@@ -88,8 +88,11 @@ $node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_schema");
$node_publisher->safe_psql(
'postgres', qq(
CREATE TABLE sch1.t1(a int) PARTITION BY RANGE(a);
- CREATE TABLE sch1.part1 PARTITION OF sch1.t1 FOR VALUES FROM (0) TO (5);
- CREATE TABLE sch1.part2 PARTITION OF sch1.t1 FOR VALUES FROM (6) TO (10);
+ CREATE TABLE sch1.part1 PARTITION OF sch1.t1 FOR VALUES FROM (0) TO (100);
+ CREATE TABLE sch1.part2(a int) PARTITION BY RANGE(a);
+ CREATE TABLE sch1.part2_1 PARTITION OF sch1.part2 FOR VALUES FROM (101) TO (150);
+ CREATE TABLE sch1.part2_2 PARTITION OF sch1.part2 FOR VALUES FROM (151) TO (200);
+ ALTER TABLE sch1.t1 ATTACH PARTITION sch1.part2 FOR VALUES FROM (101) TO (200);
));
$node_subscriber->safe_psql(
@@ -97,140 +100,152 @@ $node_subscriber->safe_psql(
CREATE TABLE sch1.t1(a int);
CREATE TABLE sch1.part1(a int);
CREATE TABLE sch1.part2(a int);
+ CREATE TABLE sch1.part2_1(a int);
+ CREATE TABLE sch1.part2_2(a int);
));
-# EXCEPT TABLE (sch1.part1) with publish_via_partition_root = false
-# Excluding a partition while publish_via_partition_root = false prevents
-# replication of rows inserted into the partitioned table for that particular
-# partition.
-$node_publisher->safe_psql(
+# Partititions cannot be excluded using EXCEPT TABLE
+my ($stdout, $stderr);
+($result, $stdout, $stderr) = $node_publisher->psql(
'postgres', qq(
- CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT TABLE (sch1.part1) WITH (publish_via_partition_root = false);
- INSERT INTO sch1.t1 VALUES (1), (6);
+ CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT TABLE (sch1.part2) WITH (publish_via_partition_root = false);
));
-$node_subscriber->safe_psql('postgres',
- "CREATE SUBSCRIPTION tap_sub_part CONNECTION '$publisher_connstr' PUBLICATION tap_pub_part"
-);
-$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub_part');
-$node_publisher->safe_psql('postgres',
- "INSERT INTO sch1.t1 VALUES (2), (7);");
-$node_publisher->wait_for_catchup('tap_sub_part');
-
-$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.t1");
-is($result, qq(), 'check rows on partitioned table');
+like(
+ $stderr,
+ qr/partition "part2" cannot be excluded using EXCEPT TABLE/,
+ 'partition "part2" cannot be excluded using EXCEPT TABLE');
-$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part1");
-is($result, qq(), 'check rows on excluded partition');
+($result, $stdout, $stderr) = $node_publisher->psql(
+ 'postgres', qq(
+ CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT TABLE (sch1.part2) WITH (publish_via_partition_root = true);
+));
+like(
+ $stderr,
+ qr/partition "part2" cannot be excluded using EXCEPT TABLE/,
+ 'partition "part2" cannot be excluded using EXCEPT TABLE');
-$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part2");
-is( $result, qq(6
-7), 'check rows on other partition');
+($result, $stdout, $stderr) = $node_publisher->psql(
+ 'postgres', qq(
+ CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT TABLE (sch1.part2_1) WITH (publish_via_partition_root = false);
+));
+like(
+ $stderr,
+ qr/partition "part2_1" cannot be excluded using EXCEPT TABLE/,
+ 'partition "part2_1" cannot be excluded using EXCEPT TABLE');
-$node_publisher->safe_psql('postgres', "TRUNCATE sch1.t1");
-$node_publisher->wait_for_catchup('tap_sub_part');
-$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_part");
-$node_publisher->safe_psql('postgres', "DROP PUBLICATION tap_pub_part;");
+($result, $stdout, $stderr) = $node_publisher->psql(
+ 'postgres', qq(
+ CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT TABLE (sch1.part2_1) WITH (publish_via_partition_root = true);
+));
+like(
+ $stderr,
+ qr/partition "part2_1" cannot be excluded using EXCEPT TABLE/,
+ 'partition "part2_1" cannot be excluded using EXCEPT TABLE');
-# EXCEPT TABLE (sch1.t1) with publish_via_partition_root = false
-# Excluding the partitioned table still allows rows inserted into the
-# partitioned table to be replicated via its partitions.
+# Excluding the root partitioned table excludes all its partitions as well when
+# publish_via_partition_root = false.
$node_publisher->safe_psql(
'postgres', qq(
CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT TABLE (sch1.t1) WITH (publish_via_partition_root = false);
- INSERT INTO sch1.t1 VALUES (1), (6);
+ INSERT INTO sch1.t1 VALUES (1), (101), (151);
));
$node_subscriber->safe_psql('postgres',
"CREATE SUBSCRIPTION tap_sub_part CONNECTION '$publisher_connstr' PUBLICATION tap_pub_part"
);
$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub_part');
$node_publisher->safe_psql('postgres',
- "INSERT INTO sch1.t1 VALUES (2), (7);");
+ "SELECT slot_name FROM pg_replication_slot_advance('test_slot', pg_current_wal_lsn());"
+);
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO sch1.t1 VALUES (2), (102), (152)");
+
+# Verify that data inserted to the partitioned table is not published when it is
+# excluded with publish_via_partition_root = true.
+$result = $node_publisher->safe_psql('postgres',
+ "SELECT count(*) = 0 FROM pg_logical_slot_get_binary_changes('test_slot', NULL, NULL, 'proto_version', '1', 'publication_names', 'tap_pub_part')"
+);
$node_publisher->wait_for_catchup('tap_sub_part');
+# Check that no rows are replicated to subscriber
$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.t1");
-is($result, qq(), 'check rows on partitioned table');
+is($result, qq(), 'check rows on root table');
$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part1");
-is( $result, qq(1
-2), 'check rows on first partition');
+is($result, qq(), 'check rows on table sch1.part1');
$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part2");
-is( $result, qq(6
-7), 'check rows on second partition');
+is($result, qq(), 'check rows on table sch1.part2');
+
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part2_1");
+is($result, qq(), 'check rows on table sch1.part2_1');
+
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part2_2");
+is($result, qq(), 'check rows on table sch1.part2_2');
-$node_publisher->safe_psql('postgres', "TRUNCATE sch1.t1");
-$node_publisher->wait_for_catchup('tap_sub_part');
$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_part");
$node_publisher->safe_psql('postgres', "DROP PUBLICATION tap_pub_part;");
-$node_publisher->safe_psql('postgres',
- "SELECT slot_name FROM pg_replication_slot_advance('test_slot', pg_current_wal_lsn());"
-);
-# EXCEPT TABLE (sch1.t1) with publish_via_partition_root = true
-# When the partitioned table is excluded and publish_via_partition_root is true,
-# no rows from the table or its partitions are replicated.
+# Excluding the root partitioned table excludes all its partitions as well when
+# publish_via_partition_root = true.
$node_publisher->safe_psql(
'postgres', qq(
- CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT TABLE (sch1.t1) WITH (publish_via_partition_root);
- INSERT INTO sch1.t1 VALUES (1), (6);
+ CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT TABLE (sch1.t1) WITH (publish_via_partition_root = true);
));
$node_subscriber->safe_psql('postgres',
"CREATE SUBSCRIPTION tap_sub_part CONNECTION '$publisher_connstr' PUBLICATION tap_pub_part"
);
$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub_part');
$node_publisher->safe_psql('postgres',
- "INSERT INTO sch1.t1 VALUES (2), (7);");
-$node_publisher->wait_for_catchup('tap_sub_part');
+ "SELECT slot_name FROM pg_replication_slot_advance('test_slot', pg_current_wal_lsn());"
+);
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO sch1.t1 VALUES (3), (103), (153);");
# Verify that data inserted to the partitioned table is not published when it is
# excluded with publish_via_partition_root = true.
$result = $node_publisher->safe_psql('postgres',
"SELECT count(*) = 0 FROM pg_logical_slot_get_binary_changes('test_slot', NULL, NULL, 'proto_version', '1', 'publication_names', 'tap_pub_part')"
);
-is($result, qq(t), 'check no changes for excluded table in replication slot');
+$node_publisher->wait_for_catchup('tap_sub_part');
+# Check that no rows are replicated to subscriber
$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.t1");
-is($result, qq(), 'check rows on partitioned table');
+is($result, qq(), 'check rows on root table');
$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part1");
-is($result, qq(), 'check rows on first partition');
+is($result, qq(), 'check rows on table sch1.part1');
$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part2");
-is($result, qq(), 'check rows on second partition');
+is($result, qq(), 'check rows on table sch1.part2');
+
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part2_1");
+is($result, qq(), 'check rows on table sch1.part2_1');
+
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part2_2");
+is($result, qq(), 'check rows on table sch1.part2_2');
-$node_publisher->safe_psql('postgres', "TRUNCATE sch1.t1");
-$node_publisher->wait_for_catchup('tap_sub_part');
$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_part");
$node_publisher->safe_psql('postgres', "DROP PUBLICATION tap_pub_part;");
-# EXCEPT TABLE (sch1.part1) with publish_via_partition_root = true
-# When a partition is excluded but publish_via_partition_root is true,
-# rows published through the partitioned table can still be replicated.
-$node_publisher->safe_psql(
+# Cannot attach partition that is part of EXCEPT list in publication
+$node_publisher->safe_psql('postgres',
+ "ALTER TABLE sch1.t1 DETACH PARTITION sch1.part2");
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT TABLE (sch1.part2) WITH (publish_via_partition_root = true)"
+);
+($result, $stdout, $stderr) = $node_publisher->psql(
'postgres', qq(
- CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT TABLE (sch1.part1) WITH (publish_via_partition_root);
- INSERT INTO sch1.t1 VALUES (1), (6)
+ ALTER TABLE sch1.t1 ATTACH PARTITION sch1.part2 FOR VALUES FROM (101) TO (200);
));
-$node_subscriber->safe_psql('postgres',
- "CREATE SUBSCRIPTION tap_sub_part CONNECTION '$publisher_connstr' PUBLICATION tap_pub_part"
+like(
+ $stderr,
+ qr/cannot attach relation "part2" as partition because it is part of EXCEPT list in publication/,
+ 'cannot attach relation "part2" as partition because it is part of EXCEPT list in publication'
);
-$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub_part');
-$node_publisher->safe_psql('postgres',
- "INSERT INTO sch1.t1 VALUES (2), (7);");
-$node_publisher->wait_for_catchup('tap_sub_part');
-
-$result =
- $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.t1 ORDER BY a");
-is( $result, qq(1
-2
-6
-7), 'check rows on partitioned table');
-
-$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part1");
-is($result, qq(), 'check rows on excluded partition');
-
-$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part2");
-is($result, qq(), 'check rows on other partition');
$node_subscriber->stop('fast');
$node_publisher->stop('fast');
--
2.43.0
[text/x-patch] v38-0002-handle-EXCEPT-TABLE-correctly-with-partitioned-approach-1.patch (26.8K, 3-v38-0002-handle-EXCEPT-TABLE-correctly-with-partitioned-approach-1.patch)
download | inline diff:
From 9d009b5a884b3f9dad7e9b7ba4797c65ad8b9d94 Mon Sep 17 00:00:00 2001
From: Vignesh C <[email protected]>
Date: Thu, 29 Jan 2026 18:56:13 +0530
Subject: [PATCH v38] handle EXCEPT TABLE correctly with partitioned tables
When a publication is created with EXCEPT TABLE, adjust logical replication
so that data synchronization and change replication correctly respect
exclusions for partitioned tables.
On the subscriber side, extend fetch_remote_table_info() to compute the
effective set of relations used for the initial COPY. When exclusions are
present, the root partitioned table can no longer be used directly; instead,
derive the list of non-excluded leaf partitions and combine them with
UNION ALL. When no exclusions exist, retain the existing behavior and copy
from the root relation as before.
This is based on approach 1 discussed at:
https://www.postgresql.org/message-id/CAJpy0uD81HRrMYr7S-6AV4W2PtbGKM-nf2D89zsoMHJ9jZssUg@mail.gmail.com
This patch is a topup patch on top of 0001 patch.
---
doc/src/sgml/ref/create_publication.sgml | 17 +-
src/backend/catalog/pg_publication.c | 4 +-
src/backend/commands/subscriptioncmds.c | 2 +
src/backend/replication/logical/tablesync.c | 185 +++++++++++++++++-
src/backend/replication/pgoutput/pgoutput.c | 9 +-
src/include/replication/worker_internal.h | 6 +
.../t/037_rep_changes_except_table.pl | 156 ++++++++-------
src/tools/pgindent/typedefs.list | 1 +
8 files changed, 285 insertions(+), 95 deletions(-)
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index 1e091bb3c6d..730b9c4bced 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -205,16 +205,15 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
tables are excluded.
</para>
<para>
- For partitioned tables, when <literal>publish_via_partition_root</literal>
- is set to <literal>true</literal>, specifying a root partitioned table in
- <literal>EXCEPT TABLE</literal> excludes it and all its partitions from
- replication. Specifying a leaf partition has no effect, as its changes are
- still replicated via the root partitioned table. When
+ For partitioned tables, when a table is specified in EXCEPT TABLE, then
+ changes to that table and all of its partitions (that is, the entire
+ partition subtree rooted at that table) are not replicated. This behavior
+ is the same regardless of whether
<literal>publish_via_partition_root</literal> is set to
- <literal>false</literal>, specifying a root partitioned table has no
- effect, as changes are replicated via the leaf partitions. Specifying a
- leaf partition excludes only that partition from replication. The optional
- <literal>*</literal> has no meaning for partitioned tables.
+ <literal>true</literal> or <literal>false</literal>. The
+ <literal>publish_via_partition_root</literal> setting only determines
+ which relation is used as the publishing relation for replicated changes,
+ and does not affect exclusion semantics.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index 09c69005122..e72e49bd97b 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -960,8 +960,8 @@ GetAllPublicationRelations(Publication *pub, char relkind)
if (relkind == RELKIND_RELATION)
exceptlist = GetAllPublicationExcludedTables(pubid, pubviaroot ?
- PUBLICATION_PART_ALL :
- PUBLICATION_PART_ROOT);
+ PUBLICATION_PART_ROOT :
+ PUBLICATION_PART_LEAF);
classRel = table_open(RelationRelationId, AccessShareLock);
diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c
index 0b3c8499b49..804ae2f349e 100644
--- a/src/backend/commands/subscriptioncmds.c
+++ b/src/backend/commands/subscriptioncmds.c
@@ -2940,6 +2940,8 @@ fetch_relation_list(WalReceiverConn *wrconn, List *publications)
pub_names.data);
}
+ elog(LOG, "fetch_relation_list: executing query to fetch effectiverelations: \n%s",
+ cmd.data);
pfree(pub_names.data);
res = walrcv_exec(wrconn, cmd.data, column_count, tableRow);
diff --git a/src/backend/replication/logical/tablesync.c b/src/backend/replication/logical/tablesync.c
index 19a3c21a863..2e8466b3ab7 100644
--- a/src/backend/replication/logical/tablesync.c
+++ b/src/backend/replication/logical/tablesync.c
@@ -716,21 +716,28 @@ copy_read_data(void *outbuf, int minread, int maxread)
* message provides during replication.
*
* This function also returns (a) the relation qualifications to be used in
- * the COPY command, and (b) whether the remote relation has published any
- * generated column.
+ * the COPY command, (b) whether the remote relation has published any
+ * generated column, and (c) computes the effective set of relations to be used
+ * as COPY sources when exclusions are present. When no exclusions exist, the
+ * list remains empty and the root relation is used as-is. When exclusions
+ * exist, the list contains leaf relations that are not excluded and must be
+ * combined using UNION ALL.
*/
static void
fetch_remote_table_info(char *nspname, char *relname, LogicalRepRelation *lrel,
- List **qual, bool *gencol_published)
+ List **qual, bool *gencol_published,
+ List **effective_relations)
{
WalRcvExecResult *res;
StringInfoData cmd;
TupleTableSlot *slot;
- Oid tableRow[] = {OIDOID, CHAROID, CHAROID};
+ Oid tableRow[] = {OIDOID, CHAROID, CHAROID, BOOLOID};
Oid attrRow[] = {INT2OID, TEXTOID, OIDOID, BOOLOID, BOOLOID};
Oid qualRow[] = {TEXTOID};
+ Oid filtertableRow[] = {TEXTOID, TEXTOID};
bool isnull;
int natt;
+ bool is_partition;
StringInfo pub_names = NULL;
Bitmapset *included_cols = NULL;
int server_version = walrcv_server_version(LogRepWorkerWalRcvConn);
@@ -740,7 +747,7 @@ fetch_remote_table_info(char *nspname, char *relname, LogicalRepRelation *lrel,
/* First fetch Oid and replica identity. */
initStringInfo(&cmd);
- appendStringInfo(&cmd, "SELECT c.oid, c.relreplident, c.relkind"
+ appendStringInfo(&cmd, "SELECT c.oid, c.relreplident, c.relkind, c.relispartition"
" FROM pg_catalog.pg_class c"
" INNER JOIN pg_catalog.pg_namespace n"
" ON (c.relnamespace = n.oid)"
@@ -770,6 +777,8 @@ fetch_remote_table_info(char *nspname, char *relname, LogicalRepRelation *lrel,
Assert(!isnull);
lrel->relkind = DatumGetChar(slot_getattr(slot, 3, &isnull));
Assert(!isnull);
+ is_partition = DatumGetBool(slot_getattr(slot, 4, &isnull));
+ Assert(!isnull);
ExecDropSingleTupleTableSlot(slot);
walrcv_clear_result(res);
@@ -954,6 +963,110 @@ fetch_remote_table_info(char *nspname, char *relname, LogicalRepRelation *lrel,
walrcv_clear_result(res);
+ 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.
+ */
+ appendStringInfo(&cmd,
+ "WITH RECURSIVE branch_search AS (\n"
+ " SELECT %u::oid AS oid, false AS is_excluded\n"
+ " UNION ALL\n"
+ " SELECT i.inhrelid AS oid,\n"
+ " parent.is_excluded\n"
+ " OR EXISTS (\n"
+ " SELECT 1\n"
+ " FROM pg_publication_rel pr\n"
+ " JOIN pg_publication p ON p.oid = pr.prpubid\n"
+ " WHERE pr.prrelid = i.inhrelid\n"
+ " AND pr.prexcept = true\n"
+ " AND p.pubname IN ( %s )\n"
+ " ) AS is_excluded\n"
+ " FROM pg_inherits i\n"
+ " JOIN branch_search parent\n"
+ " ON i.inhparent = parent.oid\n"
+ ")\n"
+ " SELECT n.nspname AS schemaname, c.relname AS relname\n"
+ " FROM (\n"
+ " SELECT bs.*, bool_or(bs.is_excluded) OVER () AS has_exclusion\n"
+ " FROM branch_search bs\n"
+ " ) bs\n"
+ " JOIN pg_class c ON c.oid = bs.oid\n"
+ " JOIN pg_namespace n ON n.oid = c.relnamespace\n"
+ " WHERE bs.is_excluded = false\n"
+ " AND (\n"
+ " (bs.has_exclusion = false AND bs.oid = %u::oid)\n"
+ " OR (bs.has_exclusion = true AND NOT EXISTS (\n"
+ " SELECT 1\n"
+ " FROM pg_inherits\n"
+ " WHERE inhparent = bs.oid\n"
+ " ))\n"
+ " );",
+ lrel->remoteid,
+ pub_names->data,
+ lrel->remoteid
+ );
+
+ elog(LOG, "Executing query to get the tables:\n%s", cmd.data);
+ res = walrcv_exec(LogRepWorkerWalRcvConn, cmd.data,
+ lengthof(filtertableRow), filtertableRow);
+
+ if (res->status != WALRCV_OK_TUPLES)
+ ereport(ERROR,
+ errcode(ERRCODE_CONNECTION_FAILURE),
+ errmsg("could not get non excluded table list for table \"%s.%s\" from publisher: %s",
+ nspname, relname, res->err));
+
+ /*
+ * Store the tables as a list of schemaname and tablename.
+ */
+ natt = 0;
+ slot = MakeSingleTupleTableSlot(res->tupledesc, &TTSOpsMinimalTuple);
+ while (tuplestore_gettupleslot(res->tuplestore, true, false, slot))
+ {
+ QualifiedRelationName *relinfo = palloc_object(QualifiedRelationName);
+
+ relinfo->schemaname = TextDatumGetCString(slot_getattr(slot, 1, &isnull));
+ Assert(!isnull);
+ relinfo->relname = TextDatumGetCString(slot_getattr(slot, 2, &isnull));
+ Assert(!isnull);
+
+ *effective_relations = lappend(*effective_relations, relinfo);
+
+ ExecClearTuple(slot);
+ }
+
+ ExecDropSingleTupleTableSlot(slot);
+
+ /*
+ * If there is exactly one item in the exclusion list and it equals
+ * the table being processed, that means no actual exclusion occurred.
+ */
+ if (list_length(*effective_relations) == 1)
+ {
+ QualifiedRelationName *relinfo;
+
+ relinfo = linitial(*effective_relations);
+ if (strcmp(nspname, relinfo->schemaname) == 0 &&
+ strcmp(relname, relinfo->relname) == 0)
+ {
+ pfree(relinfo->schemaname);
+ pfree(relinfo->relname);
+ list_free_deep(*effective_relations);
+ *effective_relations = NIL;
+ }
+ }
+ }
+
/*
* Get relation's row filter expressions. DISTINCT avoids the same
* expression of a table in multiple publications from being included
@@ -1043,6 +1156,7 @@ copy_table(Relation rel)
LogicalRepRelMapEntry *relmapentry;
LogicalRepRelation lrel;
List *qual = NIL;
+ List *effective_relations = NIL;
WalRcvExecResult *res;
StringInfoData cmd;
CopyFromState cstate;
@@ -1054,7 +1168,7 @@ copy_table(Relation rel)
/* Get the publisher relation info. */
fetch_remote_table_info(get_namespace_name(RelationGetNamespace(rel)),
RelationGetRelationName(rel), &lrel, &qual,
- &gencol_published);
+ &gencol_published, &effective_relations);
/* Put the relation into relmap. */
logicalrep_relmap_update(&lrel);
@@ -1066,12 +1180,64 @@ copy_table(Relation rel)
/* Start copy on the publisher. */
initStringInfo(&cmd);
+
+ if (effective_relations && list_length(effective_relations))
+ {
+ bool first = true;
+
+ /*
+ * Build a single COPY command to synchronize all resolved relations
+ * into the root table.
+ *
+ * The array 'effective_relations' contains the leaf tables of
+ * partition hierarchies, with excluded subtrees removed according to
+ * the EXCEPT clauses. This applies only when
+ * 'publish_via_partition_root' is enabled, since the initial sync must
+ * route all changes to the root table.
+ *
+ * We construct a UNION ALL query that combines data from multiple leaf
+ * relations into one sub-COPY statement, ensuring all rows are copied
+ * consistently into the root table.
+ */
+ appendStringInfoString(&cmd, "COPY (\n");
+ foreach_ptr(QualifiedRelationName, relinfo, effective_relations)
+ {
+ if (!first)
+ appendStringInfoString(&cmd, "UNION ALL\n");
+
+ first = false;
+
+ appendStringInfoString(&cmd, "SELECT ");
+
+ /* If the table has columns, then specify the columns */
+ if (lrel.natts)
+ {
+ for (int i = 0; i < lrel.natts; i++)
+ {
+ if (i > 0)
+ appendStringInfoString(&cmd, ", ");
+
+ appendStringInfoString(&cmd, quote_identifier(lrel.attnames[i]));
+ }
+ }
+ else
+ appendStringInfoString(&cmd, " * ");
+
+ appendStringInfo(&cmd, " FROM %s\n",
+ quote_qualified_identifier(relinfo->schemaname,
+ relinfo->relname));
+ }
+
+ appendStringInfoString(&cmd, ")\n");
+ appendStringInfoString(&cmd, "TO STDOUT");
+ }
/* Regular or partitioned table with no row filter or generated columns */
- if ((lrel.relkind == RELKIND_RELATION || lrel.relkind == RELKIND_PARTITIONED_TABLE)
- && qual == NIL && !gencol_published)
+ else if ((lrel.relkind == RELKIND_RELATION ||
+ lrel.relkind == RELKIND_PARTITIONED_TABLE) &&
+ qual == NIL && !gencol_published)
{
appendStringInfo(&cmd, "COPY %s",
- quote_qualified_identifier(lrel.nspname, lrel.relname));
+ quote_qualified_identifier(lrel.nspname, lrel.relname));
/* If the table has columns, then specify the columns */
if (lrel.natts)
@@ -1157,6 +1323,7 @@ copy_table(Relation rel)
}
res = walrcv_exec(LogRepWorkerWalRcvConn, cmd.data, 0, NULL);
+ elog(LOG, "Tablesync worker: Executing query to get the initial sync data:\n%s", cmd.data);
pfree(cmd.data);
if (res->status != WALRCV_OK_COPY_OUT)
ereport(ERROR,
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 05802482c10..407d9304101 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -2209,8 +2209,8 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
* TABLE list:
*
* 1. If pubviaroot is set and the relation is a partition, check
- * whether the partition root is included in the EXCEPT TABLE
- * list. If so, do not publish the change.
+ * whether the current relation or any of the ancestors is included
+ * in the EXCEPT TABLE list. If so, do not publish the change.
*
* 2. If pubviaroot is not set, check whether the relation itself
* is included in the EXCEPT TABLE list. If so, do not publish the
@@ -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);
}
diff --git a/src/include/replication/worker_internal.h b/src/include/replication/worker_internal.h
index c1285fdd1bc..cab97531276 100644
--- a/src/include/replication/worker_internal.h
+++ b/src/include/replication/worker_internal.h
@@ -234,6 +234,12 @@ typedef struct ParallelApplyWorkerInfo
ParallelApplyWorkerShared *shared;
} ParallelApplyWorkerInfo;
+typedef struct QualifiedRelationName
+{
+ char *schemaname;
+ char *relname;
+} QualifiedRelationName;
+
/* Main memory context for apply worker. Permanent during worker lifetime. */
extern PGDLLIMPORT MemoryContext ApplyContext;
diff --git a/src/test/subscription/t/037_rep_changes_except_table.pl b/src/test/subscription/t/037_rep_changes_except_table.pl
index 95904ddd005..80b6a70fca8 100644
--- a/src/test/subscription/t/037_rep_changes_except_table.pl
+++ b/src/test/subscription/t/037_rep_changes_except_table.pl
@@ -88,8 +88,11 @@ $node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_schema");
$node_publisher->safe_psql(
'postgres', qq(
CREATE TABLE sch1.t1(a int) PARTITION BY RANGE(a);
- CREATE TABLE sch1.part1 PARTITION OF sch1.t1 FOR VALUES FROM (0) TO (5);
- CREATE TABLE sch1.part2 PARTITION OF sch1.t1 FOR VALUES FROM (6) TO (10);
+ CREATE TABLE sch1.part1 PARTITION OF sch1.t1 FOR VALUES FROM (0) TO (100);
+ CREATE TABLE sch1.part2(a int) PARTITION BY RANGE(a);
+ CREATE TABLE sch1.part2_1 PARTITION OF sch1.part2 FOR VALUES FROM (101) TO (150);
+ CREATE TABLE sch1.part2_2 PARTITION OF sch1.part2 FOR VALUES FROM (151) TO (200);
+ ALTER TABLE sch1.t1 ATTACH PARTITION sch1.part2 FOR VALUES FROM (101) TO (200);
));
$node_subscriber->safe_psql(
@@ -97,142 +100,149 @@ $node_subscriber->safe_psql(
CREATE TABLE sch1.t1(a int);
CREATE TABLE sch1.part1(a int);
CREATE TABLE sch1.part2(a int);
+ CREATE TABLE sch1.part2_1(a int);
+ CREATE TABLE sch1.part2_2(a int);
));
-# EXCEPT TABLE (sch1.part1) with publish_via_partition_root = false
-# Excluding a partition while publish_via_partition_root = false prevents
-# replication of rows inserted into the partitioned table for that particular
-# partition.
+# Excluding the root partitioned table excludes all its partitions as well when
+# publish_via_partition_root = false.
$node_publisher->safe_psql(
'postgres', qq(
- CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT TABLE (sch1.part1) WITH (publish_via_partition_root = false);
- INSERT INTO sch1.t1 VALUES (1), (6);
+ CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT TABLE (sch1.t1) WITH (publish_via_partition_root = false);
+ INSERT INTO sch1.t1 VALUES (1), (101), (151);
));
$node_subscriber->safe_psql('postgres',
"CREATE SUBSCRIPTION tap_sub_part CONNECTION '$publisher_connstr' PUBLICATION tap_pub_part"
);
$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub_part');
$node_publisher->safe_psql('postgres',
- "INSERT INTO sch1.t1 VALUES (2), (7);");
+ "SELECT slot_name FROM pg_replication_slot_advance('test_slot', pg_current_wal_lsn());"
+);
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO sch1.t1 VALUES (2), (102), (152)");
+
+# Verify that data inserted to the partitioned table is not published when it is
+# excluded with publish_via_partition_root = true.
+$result = $node_publisher->safe_psql('postgres',
+ "SELECT count(*) = 0 FROM pg_logical_slot_get_binary_changes('test_slot', NULL, NULL, 'proto_version', '1', 'publication_names', 'tap_pub_part')"
+);
$node_publisher->wait_for_catchup('tap_sub_part');
+# Check that no rows are replicated to subscriber
$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.t1");
-is($result, qq(), 'check rows on partitioned table');
+is($result, qq(), 'check rows on root table');
$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part1");
-is($result, qq(), 'check rows on excluded partition');
+is($result, qq(), 'check rows on table sch1.part1');
$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part2");
-is( $result, qq(6
-7), 'check rows on other partition');
+is($result, qq(), 'check rows on table sch1.part2');
+
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part2_1");
+is($result, qq(), 'check rows on table sch1.part2_1');
+
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part2_2");
+is($result, qq(), 'check rows on table sch1.part2_2');
-$node_publisher->safe_psql('postgres', "TRUNCATE sch1.t1");
-$node_publisher->wait_for_catchup('tap_sub_part');
$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_part");
$node_publisher->safe_psql('postgres', "DROP PUBLICATION tap_pub_part;");
-# EXCEPT TABLE (sch1.t1) with publish_via_partition_root = false
-# Excluding the partitioned table still allows rows inserted into the
-# partitioned table to be replicated via its partitions.
+# Excluding the root partitioned table excludes all its partitions as well when
+# publish_via_partition_root = true.
$node_publisher->safe_psql(
'postgres', qq(
- CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT TABLE (sch1.t1) WITH (publish_via_partition_root = false);
- INSERT INTO sch1.t1 VALUES (1), (6);
+ CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT TABLE (sch1.t1) WITH (publish_via_partition_root = true);
));
$node_subscriber->safe_psql('postgres',
"CREATE SUBSCRIPTION tap_sub_part CONNECTION '$publisher_connstr' PUBLICATION tap_pub_part"
);
$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub_part');
$node_publisher->safe_psql('postgres',
- "INSERT INTO sch1.t1 VALUES (2), (7);");
+ "SELECT slot_name FROM pg_replication_slot_advance('test_slot', pg_current_wal_lsn());"
+);
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO sch1.t1 VALUES (3), (103), (153);");
+
+# Verify that data inserted to the partitioned table is not published when it is
+# excluded with publish_via_partition_root = true.
+$result = $node_publisher->safe_psql('postgres',
+ "SELECT count(*) = 0 FROM pg_logical_slot_get_binary_changes('test_slot', NULL, NULL, 'proto_version', '1', 'publication_names', 'tap_pub_part')"
+);
$node_publisher->wait_for_catchup('tap_sub_part');
+# Check that no rows are replicated to subscriber
$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.t1");
-is($result, qq(), 'check rows on partitioned table');
+is($result, qq(), 'check rows on root table');
$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part1");
-is( $result, qq(1
-2), 'check rows on first partition');
+is($result, qq(), 'check rows on table sch1.part1');
$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part2");
-is( $result, qq(6
-7), 'check rows on second partition');
+is($result, qq(), 'check rows on table sch1.part2');
+
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part2_1");
+is($result, qq(), 'check rows on table sch1.part2_1');
+
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part2_2");
+is($result, qq(), 'check rows on table sch1.part2_2');
-$node_publisher->safe_psql('postgres', "TRUNCATE sch1.t1");
-$node_publisher->wait_for_catchup('tap_sub_part');
$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_part");
$node_publisher->safe_psql('postgres', "DROP PUBLICATION tap_pub_part;");
$node_publisher->safe_psql('postgres',
"SELECT slot_name FROM pg_replication_slot_advance('test_slot', pg_current_wal_lsn());"
);
-# EXCEPT TABLE (sch1.t1) with publish_via_partition_root = true
-# When the partitioned table is excluded and publish_via_partition_root is true,
-# no rows from the table or its partitions are replicated.
+# Excluding one of the child partition table with
+# publish_via_partition_root = true should replicate the other partitions.
$node_publisher->safe_psql(
'postgres', qq(
- CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT TABLE (sch1.t1) WITH (publish_via_partition_root);
- INSERT INTO sch1.t1 VALUES (1), (6);
+ TRUNCATE sch1.t1;
+ INSERT INTO sch1.t1 VALUES (3), (103), (153);
+ CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT TABLE (sch1.part2) WITH (publish_via_partition_root = true);
+));
+$node_subscriber->safe_psql(
+ 'postgres', qq(
+ TRUNCATE sch1.t1;
+ CREATE SUBSCRIPTION tap_sub_part CONNECTION '$publisher_connstr' PUBLICATION tap_pub_part;
));
-$node_subscriber->safe_psql('postgres',
- "CREATE SUBSCRIPTION tap_sub_part CONNECTION '$publisher_connstr' PUBLICATION tap_pub_part"
-);
$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub_part');
$node_publisher->safe_psql('postgres',
- "INSERT INTO sch1.t1 VALUES (2), (7);");
-$node_publisher->wait_for_catchup('tap_sub_part');
+ "SELECT slot_name FROM pg_replication_slot_advance('test_slot', pg_current_wal_lsn());"
+);
# Verify that data inserted to the partitioned table is not published when it is
# excluded with publish_via_partition_root = true.
$result = $node_publisher->safe_psql('postgres',
"SELECT count(*) = 0 FROM pg_logical_slot_get_binary_changes('test_slot', NULL, NULL, 'proto_version', '1', 'publication_names', 'tap_pub_part')"
);
-is($result, qq(t), 'check no changes for excluded table in replication slot');
+$node_publisher->wait_for_catchup('tap_sub_part');
+# Check that table data 103 and 153 which is present in sch1.sch1.part2 should
+# not be replicated.
$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.t1");
-is($result, qq(), 'check rows on partitioned table');
+is($result, qq(3), 'check rows on root table');
$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part1");
-is($result, qq(), 'check rows on first partition');
+is($result, qq(), 'check rows on table sch1.part1');
$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part2");
-is($result, qq(), 'check rows on second partition');
-
-$node_publisher->safe_psql('postgres', "TRUNCATE sch1.t1");
-$node_publisher->wait_for_catchup('tap_sub_part');
-$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_part");
-$node_publisher->safe_psql('postgres', "DROP PUBLICATION tap_pub_part;");
-
-# EXCEPT TABLE (sch1.part1) with publish_via_partition_root = true
-# When a partition is excluded but publish_via_partition_root is true,
-# rows published through the partitioned table can still be replicated.
-$node_publisher->safe_psql(
- 'postgres', qq(
- CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT TABLE (sch1.part1) WITH (publish_via_partition_root);
- INSERT INTO sch1.t1 VALUES (1), (6)
-));
-$node_subscriber->safe_psql('postgres',
- "CREATE SUBSCRIPTION tap_sub_part CONNECTION '$publisher_connstr' PUBLICATION tap_pub_part"
-);
-$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub_part');
-$node_publisher->safe_psql('postgres',
- "INSERT INTO sch1.t1 VALUES (2), (7);");
-$node_publisher->wait_for_catchup('tap_sub_part');
+is($result, qq(), 'check rows on table sch1.part2');
$result =
- $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.t1 ORDER BY a");
-is( $result, qq(1
-2
-6
-7), 'check rows on partitioned table');
+ $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part2_1");
+is($result, qq(), 'check rows on table sch1.part2_1');
-$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part1");
-is($result, qq(), 'check rows on excluded partition');
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part2_2");
+is($result, qq(), 'check rows on table sch1.part2_2');
-$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part2");
-is($result, qq(), 'check rows on other partition');
+$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_part");
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION tap_pub_part;");
-$node_subscriber->stop('fast');
$node_publisher->stop('fast');
done_testing();
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 34374df0d67..bfe2964cba1 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2418,6 +2418,7 @@ QTNode
QUERYTYPE
QualCost
QualItem
+QualifiedRelationName
Query
QueryCompletion
QueryDesc
--
2.43.0
[text/x-patch] v38-0001-Skip-publishing-the-tables-specified-in-EXCEPT-T.patch (68.0K, 4-v38-0001-Skip-publishing-the-tables-specified-in-EXCEPT-T.patch)
download | inline diff:
From 64b30c96a0cc8657934333116ef97867e81cdc9f Mon Sep 17 00:00:00 2001
From: Vignesh C <[email protected]>
Date: Wed, 28 Jan 2026 10:47:02 +0530
Subject: [PATCH v38] Skip publishing the tables specified in EXCEPT TABLE.
A new "EXCEPT TABLE" clause for CREATE PUBLICATION allows one or more
tables to be excluded. The publisher will not send the data of excluded
tables to the subscriber.
The new syntax allows specifying excluded relations when creating a
publication. For example:
CREATE PUBLICATION pub1 FOR ALL TABLES EXCEPT TABLE (t1,t2);
A new column "prexcept" is added to table "pg_publication_rel", to flag
the relations that the user wants to exclude from the publications.
pg_dump is updated to identify and dump the excluded tables of the publications.
The psql \d family of commands can now display excluded tables.
Bump catalog version.
---
doc/src/sgml/catalogs.sgml | 10 +
doc/src/sgml/logical-replication.sgml | 6 +-
doc/src/sgml/ref/create_publication.sgml | 56 ++++-
doc/src/sgml/ref/psql-ref.sgml | 5 +-
src/backend/catalog/pg_publication.c | 143 +++++++++--
src/backend/commands/publicationcmds.c | 109 ++++----
src/backend/commands/tablecmds.c | 4 +-
src/backend/parser/gram.y | 42 +++-
src/backend/replication/pgoutput/pgoutput.c | 43 +++-
src/backend/utils/cache/relcache.c | 21 +-
src/bin/pg_dump/pg_dump.c | 71 ++++++
src/bin/pg_dump/pg_dump.h | 1 +
src/bin/pg_dump/t/002_pg_dump.pl | 30 +++
src/bin/psql/describe.c | 87 ++++++-
src/bin/psql/tab-complete.in.c | 12 +-
src/include/catalog/pg_publication.h | 13 +-
src/include/catalog/pg_publication_rel.h | 1 +
src/include/nodes/parsenodes.h | 3 +
src/test/regress/expected/publication.out | 93 ++++++-
src/test/regress/sql/publication.sql | 37 ++-
src/test/subscription/meson.build | 1 +
.../t/037_rep_changes_except_table.pl | 238 ++++++++++++++++++
22 files changed, 897 insertions(+), 129 deletions(-)
create mode 100644 src/test/subscription/t/037_rep_changes_except_table.pl
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 332193565e2..d40e5cc1090 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -6581,6 +6581,16 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
if there is no publication qualifying condition.</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>prexcept</structfield> <type>bool</type>
+ </para>
+ <para>
+ True if the relation is excluded from the publication. See
+ <link linkend="sql-createpublication-params-for-except-table"><literal>EXCEPT TABLE</literal></link>.
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>prattrs</structfield> <type>int2vector</type>
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index 5028fe9af09..f01281fd4f9 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -116,7 +116,11 @@
<literal>FOR TABLES IN SCHEMA</literal>, <literal>FOR ALL TABLES</literal>,
or <literal>FOR ALL SEQUENCES</literal>. Unlike tables, sequences can be
synchronized at any time. For more information, see
- <xref linkend="logical-replication-sequences"/>.
+ <xref linkend="logical-replication-sequences"/>. When a publication is
+ created with <literal>FOR ALL TABLES</literal>, tables can be explicitly
+ excluded from publication using the
+ <link linkend="sql-createpublication-params-for-except-table"><literal>EXCEPT TABLE</literal></link>
+ clause.
</para>
<para>
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index 6efbb915cec..1e091bb3c6d 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -32,12 +32,16 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
<phrase>and <replaceable class="parameter">publication_all_object</replaceable> is one of:</phrase>
- ALL TABLES
+ ALL TABLES [ EXCEPT [ TABLE ] ( <replaceable class="parameter">except_table_object</replaceable> [, ... ] ) ]
ALL SEQUENCES
<phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
[ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] [ WHERE ( <replaceable class="parameter">expression</replaceable> ) ]
+
+<phrase>and <replaceable class="parameter">except_table_object</replaceable> is:</phrase>
+
+ [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
</synopsis>
</refsynopsisdiv>
@@ -164,7 +168,8 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
<listitem>
<para>
Marks the publication as one that replicates changes for all tables in
- the database, including tables created in the future.
+ the database, including tables created in the future. Tables listed in
+ EXCEPT TABLE are excluded from the publication.
</para>
</listitem>
</varlistentry>
@@ -184,6 +189,36 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
</listitem>
</varlistentry>
+ <varlistentry id="sql-createpublication-params-for-except-table">
+ <term><literal>EXCEPT TABLE</literal></term>
+ <listitem>
+ <para>
+ This clause specifies a list of tables to be excluded from the
+ publication.
+ </para>
+ <para>
+ For inherited tables, if <literal>ONLY</literal> is specified before the
+ table name, only that table is excluded from the publication. If
+ <literal>ONLY</literal> is not specified, the table and all its descendant
+ tables (if any) are excluded. Optionally, <literal>*</literal> can be
+ specified after the table name to explicitly indicate that descendant
+ tables are excluded.
+ </para>
+ <para>
+ For partitioned tables, when <literal>publish_via_partition_root</literal>
+ is set to <literal>true</literal>, specifying a root partitioned table in
+ <literal>EXCEPT TABLE</literal> excludes it and all its partitions from
+ replication. Specifying a leaf partition has no effect, as its changes are
+ still replicated via the root partitioned table. When
+ <literal>publish_via_partition_root</literal> is set to
+ <literal>false</literal>, specifying a root partitioned table has no
+ effect, as changes are replicated via the leaf partitions. Specifying a
+ leaf partition excludes only that partition from replication. The optional
+ <literal>*</literal> has no meaning for partitioned tables.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="sql-createpublication-params-with">
<term><literal>WITH ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
<listitem>
@@ -489,6 +524,23 @@ CREATE PUBLICATION all_sequences FOR ALL SEQUENCES;
all sequences for synchronization:
<programlisting>
CREATE PUBLICATION all_tables_sequences FOR ALL TABLES, ALL SEQUENCES;
+</programlisting>
+ </para>
+
+ <para>
+ Create a publication that publishes all changes in all tables except
+ <structname>users</structname> and <structname>departments</structname>:
+<programlisting>
+CREATE PUBLICATION all_tables_except FOR ALL TABLES EXCEPT (users, departments);
+</programlisting>
+ </para>
+
+ <para>
+ Create a publication that publishes all sequences for synchronization, and
+ all changes in all tables except <structname>users</structname> and
+ <structname>departments</structname>:
+<programlisting>
+CREATE PUBLICATION all_sequences_tables_except FOR ALL SEQUENCES, ALL TABLES EXCEPT (users, departments);
</programlisting>
</para>
</refsect1>
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index e464e3b13de..f4c0183b93a 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -2103,8 +2103,9 @@ SELECT $1 \parse stmt1
listed.
If <literal>x</literal> is appended to the command name, the results
are displayed in expanded mode.
- If <literal>+</literal> is appended to the command name, the tables and
- schemas associated with each publication are shown as well.
+ If <literal>+</literal> is appended to the command name, the tables,
+ excluded tables, and schemas associated with each publication are shown
+ as well.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index 9a4791c573e..09c69005122 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -366,9 +366,11 @@ GetTopMostAncestorInPublication(Oid puboid, List *ancestors, int *ancestor_level
foreach(lc, ancestors)
{
Oid ancestor = lfirst_oid(lc);
- List *apubids = GetRelationPublications(ancestor);
+ List *apubids = NIL;
List *aschemaPubids = NIL;
+ GetRelationPublications(ancestor, &apubids, NULL);
+
level++;
if (list_member_oid(apubids, puboid))
@@ -466,6 +468,26 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri,
RelationGetRelationName(targetrel), pub->name)));
}
+ /*
+ * Handle the case where a partition is excluded by EXCEPT TABLE while
+ * publish_via_partition_root = true.
+ */
+ if (pub->alltables && pub->pubviaroot && pri->except &&
+ targetrel->rd_rel->relispartition)
+ ereport(WARNING,
+ (errmsg("partition \"%s\" might be replicated as publish_via_partition_root is \"%s\"",
+ RelationGetRelationName(targetrel), "true")));
+
+ /*
+ * Handle the case where a partitioned table is excluded by EXCEPT TABLE
+ * while publish_via_partition_root = false.
+ */
+ if (pub->alltables && !pub->pubviaroot && pri->except &&
+ targetrel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ ereport(WARNING,
+ (errmsg("partitioned table \"%s\" might be replicated as publish_via_partition_root is \"%s\"",
+ RelationGetRelationName(targetrel), "false")));
+
check_publication_add_relation(targetrel);
/* Validate and translate column names into a Bitmapset of attnums. */
@@ -482,6 +504,8 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri,
ObjectIdGetDatum(pubid);
values[Anum_pg_publication_rel_prrelid - 1] =
ObjectIdGetDatum(relid);
+ values[Anum_pg_publication_rel_prexcept - 1] =
+ BoolGetDatum(pri->except);
/* Add qualifications, if available */
if (pri->whereClause != NULL)
@@ -749,38 +773,58 @@ publication_add_schema(Oid pubid, Oid schemaid, bool if_not_exists)
return myself;
}
-/* Gets list of publication oids for a relation */
-List *
-GetRelationPublications(Oid relid)
+/*
+ * Get the list of publication oids associated with a specified relation.
+ *
+ * Parameter 'pubids' returns the Oids of the publications the relation is part
+ * of. Parameter 'except_pubids' returns the Oids of publications the relation
+ * is excluded from.
+ *
+ * This function returns true if the relation is part of any publication.
+ */
+bool
+GetRelationPublications(Oid relid, List **pubids, List **except_pubids)
{
- List *result = NIL;
CatCList *pubrellist;
- int i;
+ bool found = false;
/* Find all publications associated with the relation. */
pubrellist = SearchSysCacheList1(PUBLICATIONRELMAP,
ObjectIdGetDatum(relid));
- for (i = 0; i < pubrellist->n_members; i++)
+ for (int i = 0; i < pubrellist->n_members; i++)
{
HeapTuple tup = &pubrellist->members[i]->tuple;
- Oid pubid = ((Form_pg_publication_rel) GETSTRUCT(tup))->prpubid;
+ Form_pg_publication_rel pubrel = (Form_pg_publication_rel) GETSTRUCT(tup);
+ Oid pubid = pubrel->prpubid;
- result = lappend_oid(result, pubid);
+ if (pubrel->prexcept)
+ {
+ if (except_pubids)
+ *except_pubids = lappend_oid(*except_pubids, pubid);
+ }
+ else
+ {
+ if (pubids)
+ *pubids = lappend_oid(*pubids, pubid);
+ found = true;
+ }
}
ReleaseSysCacheList(pubrellist);
- return result;
+ return found;
}
/*
- * Gets list of relation oids for a publication.
+ * Internal function to get the list of relation Oids for a publication.
*
- * This should only be used FOR TABLE publications, the FOR ALL TABLES/SEQUENCES
- * should use GetAllPublicationRelations().
+ * If except_flag is true, returns the list of relations excluded from the
+ * publication; otherwise, returns the list of relations included in the
+ * publication.
*/
-List *
-GetPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt)
+static List *
+get_publication_relations(Oid pubid, PublicationPartOpt pub_partopt,
+ bool except_flag)
{
List *result;
Relation pubrelsrel;
@@ -805,8 +849,10 @@ GetPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt)
Form_pg_publication_rel pubrel;
pubrel = (Form_pg_publication_rel) GETSTRUCT(tup);
- result = GetPubPartitionOptionRelations(result, pub_partopt,
- pubrel->prrelid);
+
+ if (except_flag == pubrel->prexcept)
+ result = GetPubPartitionOptionRelations(result, pub_partopt,
+ pubrel->prrelid);
}
systable_endscan(scan);
@@ -819,6 +865,36 @@ GetPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt)
return result;
}
+/*
+ * Return the list of relation Oids for a publication.
+ *
+ * For a FOR TABLE publication, this returns the list of relations explicitly
+ * included in the publication.
+ *
+ * Publications declared with FOR ALL TABLES should use
+ * GetAllPublicationRelations() to obtain the complete set of tables covered by
+ * the publication.
+ */
+List *
+GetPublicationIncludedRelations(Oid pubid, PublicationPartOpt pub_partopt)
+{
+ Assert(!GetPublication(pubid)->alltables);
+
+ return get_publication_relations(pubid, pub_partopt, false);
+}
+
+/*
+ * Return the list of tables Oids excluded from a publication.
+ * This is only applicable for FOR ALL TABLES publications.
+ */
+List *
+GetAllPublicationExcludedTables(Oid pubid, PublicationPartOpt pub_partopt)
+{
+ Assert(GetPublication(pubid)->alltables);
+
+ return get_publication_relations(pubid, pub_partopt, true);
+}
+
/*
* Gets list of publication oids for publications marked as FOR ALL TABLES.
*/
@@ -864,18 +940,29 @@ GetAllTablesPublications(void)
* partitioned tables, we must exclude partitions in favor of including the
* root partitioned tables. This is not applicable to FOR ALL SEQUENCES
* publication.
+ *
+ * For a FOR ALL TABLES publication, the returned list excludes tables mentioned
+ * in EXCEPT TABLE clause.
*/
List *
-GetAllPublicationRelations(char relkind, bool pubviaroot)
+GetAllPublicationRelations(Publication *pub, char relkind)
{
Relation classRel;
ScanKeyData key[1];
TableScanDesc scan;
HeapTuple tuple;
List *result = NIL;
+ List *exceptlist = NIL;
+ bool pubviaroot = pub->pubviaroot;
+ Oid pubid = pub->oid;
Assert(!(relkind == RELKIND_SEQUENCE && pubviaroot));
+ if (relkind == RELKIND_RELATION)
+ exceptlist = GetAllPublicationExcludedTables(pubid, pubviaroot ?
+ PUBLICATION_PART_ALL :
+ PUBLICATION_PART_ROOT);
+
classRel = table_open(RelationRelationId, AccessShareLock);
ScanKeyInit(&key[0],
@@ -891,7 +978,8 @@ GetAllPublicationRelations(char relkind, bool pubviaroot)
Oid relid = relForm->oid;
if (is_publishable_class(relid, relForm) &&
- !(relForm->relispartition && pubviaroot))
+ !(relForm->relispartition && pubviaroot) &&
+ !list_member_oid(exceptlist, relid))
result = lappend_oid(result, relid);
}
@@ -912,7 +1000,8 @@ GetAllPublicationRelations(char relkind, bool pubviaroot)
Oid relid = relForm->oid;
if (is_publishable_class(relid, relForm) &&
- !relForm->relispartition)
+ !relForm->relispartition &&
+ !list_member_oid(exceptlist, relid))
result = lappend_oid(result, relid);
}
@@ -1168,17 +1257,17 @@ pg_get_publication_tables(PG_FUNCTION_ARGS)
* those. Otherwise, get the partitioned table itself.
*/
if (pub_elem->alltables)
- pub_elem_tables = GetAllPublicationRelations(RELKIND_RELATION,
- pub_elem->pubviaroot);
+ pub_elem_tables = GetAllPublicationRelations(pub_elem,
+ RELKIND_RELATION);
else
{
List *relids,
*schemarelids;
- relids = GetPublicationRelations(pub_elem->oid,
- pub_elem->pubviaroot ?
- PUBLICATION_PART_ROOT :
- PUBLICATION_PART_LEAF);
+ relids = GetPublicationIncludedRelations(pub_elem->oid,
+ pub_elem->pubviaroot ?
+ PUBLICATION_PART_ROOT :
+ PUBLICATION_PART_LEAF);
schemarelids = GetAllSchemaPublicationRelations(pub_elem->oid,
pub_elem->pubviaroot ?
PUBLICATION_PART_ROOT :
@@ -1367,7 +1456,7 @@ pg_get_publication_sequences(PG_FUNCTION_ARGS)
publication = GetPublicationByName(pubname, false);
if (publication->allsequences)
- sequences = GetAllPublicationRelations(RELKIND_SEQUENCE, false);
+ sequences = GetAllPublicationRelations(publication, RELKIND_SEQUENCE);
funcctx->user_fctx = sequences;
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index fc3a4c19e65..6bb816b219c 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -198,7 +198,12 @@ ObjectsInPublicationToOids(List *pubobjspec_list, ParseState *pstate,
switch (pubobj->pubobjtype)
{
+ case PUBLICATIONOBJ_EXCEPT_TABLE:
+ pubobj->pubtable->except = true;
+ *rels = lappend(*rels, pubobj->pubtable);
+ break;
case PUBLICATIONOBJ_TABLE:
+ pubobj->pubtable->except = false;
*rels = lappend(*rels, pubobj->pubtable);
break;
case PUBLICATIONOBJ_TABLES_IN_SCHEMA:
@@ -519,8 +524,8 @@ InvalidatePubRelSyncCache(Oid pubid, bool puballtables)
* a target. However, WAL records for TRUNCATE specify both a root and
* its leaves.
*/
- relids = GetPublicationRelations(pubid,
- PUBLICATION_PART_ALL);
+ relids = GetPublicationIncludedRelations(pubid,
+ PUBLICATION_PART_ALL);
schemarelids = GetAllSchemaPublicationRelations(pubid,
PUBLICATION_PART_ALL);
@@ -929,55 +934,61 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
CommandCounterIncrement();
/* Associate objects with the publication. */
- if (stmt->for_all_tables)
+ ObjectsInPublicationToOids(stmt->pubobjects, pstate, &relations,
+ &schemaidlist);
+
+ /* FOR TABLES IN SCHEMA requires superuser */
+ if (schemaidlist != NIL && !superuser())
+ ereport(ERROR,
+ errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to create FOR TABLES IN SCHEMA publication"));
+
+ /* Add relations (tables) to the publication. */
+ if (relations != NIL)
{
/*
- * Invalidate relcache so that publication info is rebuilt. Sequences
- * publication doesn't require invalidation, as replica identity
- * checks don't apply to them.
+ * The 'relations' list can be non-empty in only two cases:
+ *
+ * 1. CREATE PUBLICATION ... FOR TABLE In this case, 'relations'
+ * contains the list of specified tables.
+ *
+ * 2. CREATE PUBLICATION ... FOR ALL TABLES In this case, 'relations'
+ * contains the list of tables specified in the EXCEPT TABLE clause.
+ * During parsing, the 'except' flag is set for the associated
+ * PublicationRelInfo objects.
*/
- CacheInvalidateRelcacheAll();
- }
- else if (!stmt->for_all_sequences)
- {
- ObjectsInPublicationToOids(stmt->pubobjects, pstate, &relations,
- &schemaidlist);
+ List *rels;
- /* FOR TABLES IN SCHEMA requires superuser */
- if (schemaidlist != NIL && !superuser())
- ereport(ERROR,
- errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("must be superuser to create FOR TABLES IN SCHEMA publication"));
-
- if (relations != NIL)
- {
- List *rels;
+ rels = OpenTableList(relations);
+ TransformPubWhereClauses(rels, pstate->p_sourcetext,
+ publish_via_partition_root);
- rels = OpenTableList(relations);
- TransformPubWhereClauses(rels, pstate->p_sourcetext,
- publish_via_partition_root);
+ CheckPubRelationColumnList(stmt->pubname, rels,
+ schemaidlist != NIL,
+ publish_via_partition_root);
- CheckPubRelationColumnList(stmt->pubname, rels,
- schemaidlist != NIL,
- publish_via_partition_root);
-
- PublicationAddTables(puboid, rels, true, NULL);
- CloseTableList(rels);
- }
+ PublicationAddTables(puboid, rels, true, NULL);
+ CloseTableList(rels);
+ }
- if (schemaidlist != NIL)
- {
- /*
- * Schema lock is held until the publication is created to prevent
- * concurrent schema deletion.
- */
- LockSchemaList(schemaidlist);
- PublicationAddSchemas(puboid, schemaidlist, true, NULL);
- }
+ if (schemaidlist != NIL)
+ {
+ /*
+ * Schema lock is held until the publication is created to prevent
+ * concurrent schema deletion.
+ */
+ LockSchemaList(schemaidlist);
+ PublicationAddSchemas(puboid, schemaidlist, true, NULL);
}
table_close(rel, RowExclusiveLock);
+ if (stmt->for_all_tables)
+ {
+ /* Invalidate relcache so that publication info is rebuilt. */
+ CacheInvalidateRelcacheAll();
+ }
+
InvokeObjectPostCreateHook(PublicationRelationId, puboid, 0);
/*
@@ -1050,8 +1061,8 @@ AlterPublicationOptions(ParseState *pstate, AlterPublicationStmt *stmt,
LockDatabaseObject(PublicationRelationId, pubform->oid, 0,
AccessShareLock);
- root_relids = GetPublicationRelations(pubform->oid,
- PUBLICATION_PART_ROOT);
+ root_relids = GetPublicationIncludedRelations(pubform->oid,
+ PUBLICATION_PART_ROOT);
foreach(lc, root_relids)
{
@@ -1170,8 +1181,8 @@ AlterPublicationOptions(ParseState *pstate, AlterPublicationStmt *stmt,
* trees, not just those explicitly mentioned in the publication.
*/
if (root_relids == NIL)
- relids = GetPublicationRelations(pubform->oid,
- PUBLICATION_PART_ALL);
+ relids = GetPublicationIncludedRelations(pubform->oid,
+ PUBLICATION_PART_ALL);
else
{
/*
@@ -1256,8 +1267,8 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
PublicationDropTables(pubid, rels, false);
else /* AP_SetObjects */
{
- List *oldrelids = GetPublicationRelations(pubid,
- PUBLICATION_PART_ROOT);
+ List *oldrelids = GetPublicationIncludedRelations(pubid,
+ PUBLICATION_PART_ROOT);
List *delrels = NIL;
ListCell *oldlc;
@@ -1358,6 +1369,7 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
oldrel = palloc_object(PublicationRelInfo);
oldrel->whereClause = NULL;
oldrel->columns = NIL;
+ oldrel->except = false;
oldrel->relation = table_open(oldrelid,
ShareUpdateExclusiveLock);
delrels = lappend(delrels, oldrel);
@@ -1408,7 +1420,8 @@ AlterPublicationSchemas(AlterPublicationStmt *stmt,
ListCell *lc;
List *reloids;
- reloids = GetPublicationRelations(pubform->oid, PUBLICATION_PART_ROOT);
+ reloids = GetPublicationIncludedRelations(pubform->oid,
+ PUBLICATION_PART_ROOT);
foreach(lc, reloids)
{
@@ -1771,6 +1784,7 @@ OpenTableList(List *tables)
pub_rel->relation = rel;
pub_rel->whereClause = t->whereClause;
pub_rel->columns = t->columns;
+ pub_rel->except = t->except;
rels = lappend(rels, pub_rel);
relids = lappend_oid(relids, myrelid);
@@ -1843,6 +1857,7 @@ OpenTableList(List *tables)
/* child inherits column list from parent */
pub_rel->columns = t->columns;
+ pub_rel->except = t->except;
rels = lappend(rels, pub_rel);
relids = lappend_oid(relids, childrelid);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index f976c0e5c7e..a5351fc59c6 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -8688,7 +8688,7 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName,
* expressions.
*/
if (attgenerated == ATTRIBUTE_GENERATED_VIRTUAL &&
- GetRelationPublications(RelationGetRelid(rel)) != NIL)
+ GetRelationPublications(RelationGetRelid(rel), NULL, NULL))
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("ALTER TABLE / SET EXPRESSION is not supported for virtual generated columns in tables that are part of a publication"),
@@ -18884,7 +18884,7 @@ ATPrepChangePersistence(AlteredTableInfo *tab, Relation rel, bool toLogged)
* UNLOGGED, as UNLOGGED tables can't be published.
*/
if (!toLogged &&
- GetRelationPublications(RelationGetRelid(rel)) != NIL)
+ GetRelationPublications(RelationGetRelid(rel), NULL, NULL))
ereport(ERROR,
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("cannot change table \"%s\" to unlogged because it is part of a publication",
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 713ee5c10a2..d3c3e9f59c6 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -203,6 +203,7 @@ static void processCASbits(int cas_bits, int location, const char *constrType,
static PartitionStrategy parsePartitionStrategy(char *strategy, int location,
core_yyscan_t yyscanner);
static void preprocess_pub_all_objtype_list(List *all_objects_list,
+ List **pubobjects,
bool *all_tables,
bool *all_sequences,
core_yyscan_t yyscanner);
@@ -455,6 +456,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
TriggerTransitions TriggerReferencing
vacuum_relation_list opt_vacuum_relation_list
drop_option_list pub_obj_list pub_all_obj_type_list
+ pub_except_obj_list opt_pub_except_clause
%type <retclause> returning_clause
%type <node> returning_option
@@ -592,6 +594,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <node> var_value zone_value
%type <rolespec> auth_ident RoleSpec opt_granted_by
%type <publicationobjectspec> PublicationObjSpec
+%type <publicationobjectspec> PublicationExceptObjSpec
%type <publicationallobjectspec> PublicationAllObjSpec
%type <keyword> unreserved_keyword type_func_name_keyword
@@ -10792,7 +10795,7 @@ AlterOwnerStmt: ALTER AGGREGATE aggregate_with_argtypes OWNER TO RoleSpec
*
* pub_all_obj_type is one of:
*
- * TABLES
+ * TABLES [EXCEPT [TABLE] ( table [, ...] )]
* SEQUENCES
*
* CREATE PUBLICATION FOR pub_obj [, ...] [WITH options]
@@ -10818,7 +10821,8 @@ CreatePublicationStmt:
CreatePublicationStmt *n = makeNode(CreatePublicationStmt);
n->pubname = $3;
- preprocess_pub_all_objtype_list($5, &n->for_all_tables,
+ preprocess_pub_all_objtype_list($5, &n->pubobjects,
+ &n->for_all_tables,
&n->for_all_sequences,
yyscanner);
n->options = $6;
@@ -10858,6 +10862,7 @@ PublicationObjSpec:
$$->pubtable->relation = $2;
$$->pubtable->columns = $3;
$$->pubtable->whereClause = $4;
+ $$->location = @1;
}
| TABLES IN_P SCHEMA ColId
{
@@ -10933,11 +10938,19 @@ pub_obj_list: PublicationObjSpec
{ $$ = lappend($1, $3); }
;
+opt_pub_except_clause:
+ EXCEPT opt_table '(' pub_except_obj_list ')' { $$ = $4; }
+ | /*EMPTY*/ { $$ = NIL; }
+ ;
+
PublicationAllObjSpec:
- ALL TABLES
+ ALL TABLES opt_pub_except_clause
{
$$ = makeNode(PublicationAllObjSpec);
$$->pubobjtype = PUBLICATION_ALL_TABLES;
+ $$->except_tables = $3;
+ if($$->except_tables != NULL)
+ preprocess_pubobj_list($$->except_tables, yyscanner);
$$->location = @1;
}
| ALL SEQUENCES
@@ -10954,6 +10967,23 @@ pub_all_obj_type_list: PublicationAllObjSpec
{ $$ = lappend($1, $3); }
;
+PublicationExceptObjSpec:
+ relation_expr
+ {
+ $$ = makeNode(PublicationObjSpec);
+ $$->pubobjtype = PUBLICATIONOBJ_EXCEPT_TABLE;
+ $$->pubtable = makeNode(PublicationTable);
+ $$->pubtable->except = true;
+ $$->pubtable->relation = $1;
+ $$->location = @1;
+ }
+ ;
+
+pub_except_obj_list: PublicationExceptObjSpec
+ { $$ = list_make1($1); }
+ | pub_except_obj_list ',' PublicationExceptObjSpec
+ { $$ = lappend($1, $3); }
+ ;
/*****************************************************************************
*
@@ -19794,8 +19824,9 @@ parsePartitionStrategy(char *strategy, int location, core_yyscan_t yyscanner)
* Also, checks if the pub_object_type has been specified more than once.
*/
static void
-preprocess_pub_all_objtype_list(List *all_objects_list, bool *all_tables,
- bool *all_sequences, core_yyscan_t yyscanner)
+preprocess_pub_all_objtype_list(List *all_objects_list, List **pubobjects,
+ bool *all_tables, bool *all_sequences,
+ core_yyscan_t yyscanner)
{
if (!all_objects_list)
return;
@@ -19815,6 +19846,7 @@ preprocess_pub_all_objtype_list(List *all_objects_list, bool *all_tables,
parser_errposition(obj->location));
*all_tables = true;
+ *pubobjects = list_concat(*pubobjects, obj->except_tables);
}
else if (obj->pubobjtype == PUBLICATION_ALL_SEQUENCES)
{
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index e016f64e0b3..05802482c10 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -2088,7 +2088,7 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
if (!entry->replicate_valid)
{
Oid schemaId = get_rel_namespace(relid);
- List *pubids = GetRelationPublications(relid);
+ List *pubids = NIL;
/*
* We don't acquire a lock on the namespace system table as we build
@@ -2103,6 +2103,8 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
char relkind = get_rel_relkind(relid);
List *rel_publications = NIL;
+ GetRelationPublications(relid, &pubids, NULL);
+
/* Reload publications if needed before use. */
if (!publications_valid)
{
@@ -2202,10 +2204,26 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
/*
* If this is a FOR ALL TABLES publication, pick the partition
* root and set the ancestor level accordingly.
+ *
+ * If this is a FOR ALL TABLES publication and it has an EXCEPT
+ * TABLE list:
+ *
+ * 1. If pubviaroot is set and the relation is a partition, check
+ * whether the partition root is included in the EXCEPT TABLE
+ * list. If so, do not publish the change.
+ *
+ * 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 is achieved by keeping the variable "publish" set to
+ * false. And eventually, entry->pubactions will remain all false
+ * for this publication.
*/
if (pub->alltables)
{
- publish = true;
+ List *exceptpubids = NIL;
+
if (pub->pubviaroot && am_partition)
{
List *ancestors = get_partition_ancestors(relid);
@@ -2213,9 +2231,23 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
pub_relid = llast_oid(ancestors);
ancestor_level = list_length(ancestors);
}
- }
- if (!publish)
+ GetRelationPublications(pub_relid, NULL, &exceptpubids);
+
+ if (!list_member_oid(exceptpubids, pub->oid))
+ publish = true;
+ else
+ {
+ /* Sanity check */
+ Assert(entry->pubactions.pubinsert == false &&
+ entry->pubactions.pubupdate == false &&
+ entry->pubactions.pubdelete == false &&
+ entry->pubactions.pubtruncate == false);
+ }
+
+ list_free(exceptpubids);
+ }
+ else if (!publish)
{
bool ancestor_published = false;
@@ -2259,6 +2291,9 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
* Don't publish changes for partitioned tables, because
* publishing those of its partitions suffices, unless partition
* changes won't be published due to pubviaroot being set.
+ *
+ * If the relation is part of EXCEPT TABLE list of a publication,
+ * the 'publish' variable is already set to false.
*/
if (publish &&
(relkind != RELKIND_PARTITIONED_TABLE || pub->pubviaroot))
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 6b634c9fff1..dc021dbb6cd 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -5788,7 +5788,9 @@ RelationGetExclusionInfo(Relation indexRelation,
void
RelationBuildPublicationDesc(Relation relation, PublicationDesc *pubdesc)
{
- List *puboids;
+ List *puboids = NIL;
+ List *exceptpuboids = NIL;
+ List *alltablespuboids;
ListCell *lc;
MemoryContext oldcxt;
Oid schemaid;
@@ -5826,7 +5828,7 @@ RelationBuildPublicationDesc(Relation relation, PublicationDesc *pubdesc)
pubdesc->gencols_valid_for_delete = true;
/* Fetch the publication membership info. */
- puboids = GetRelationPublications(relid);
+ GetRelationPublications(relid, &puboids, &exceptpuboids);
schemaid = RelationGetNamespace(relation);
puboids = list_concat_unique_oid(puboids, GetSchemaPublications(schemaid));
@@ -5838,16 +5840,25 @@ RelationBuildPublicationDesc(Relation relation, PublicationDesc *pubdesc)
foreach(lc, ancestors)
{
Oid ancestor = lfirst_oid(lc);
+ List *ancestor_puboids = NIL;
+ List *ancestor_exceptpuboids = NIL;
- puboids = list_concat_unique_oid(puboids,
- GetRelationPublications(ancestor));
+ GetRelationPublications(ancestor, &ancestor_puboids,
+ &ancestor_exceptpuboids);
+
+ puboids = list_concat_unique_oid(puboids, ancestor_puboids);
schemaid = get_rel_namespace(ancestor);
puboids = list_concat_unique_oid(puboids,
GetSchemaPublications(schemaid));
+ exceptpuboids = list_concat_unique_oid(exceptpuboids,
+ ancestor_exceptpuboids);
}
}
- puboids = list_concat_unique_oid(puboids, GetAllTablesPublications());
+ alltablespuboids = GetAllTablesPublications();
+ puboids = list_concat_unique_oid(puboids,
+ list_difference_oid(alltablespuboids,
+ exceptpuboids));
foreach(lc, puboids)
{
Oid pubid = lfirst_oid(lc);
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 2bebefd0ba2..851a97352f7 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -4648,9 +4648,58 @@ getPublications(Archive *fout)
(strcmp(PQgetvalue(res, i, i_pubviaroot), "t") == 0);
pubinfo[i].pubgencols_type =
*(PQgetvalue(res, i, i_pubgencols));
+ pubinfo[i].except_tables = (SimplePtrList)
+ {
+ NULL, NULL
+ };
/* Decide whether we want to dump it */
selectDumpableObject(&(pubinfo[i].dobj), fout);
+
+ /*
+ * Get the list of tables for publications specified with the EXCEPT
+ * TABLE clause. This is introduced in PostgreSQL 19.
+ *
+ * EXCEPT TABLES is processed here and output directly by
+ * dumpPublication(). This differs from the approach used in
+ * dumpPublicationTable() and dumpPublicationNamespace(), since that
+ * approach would require EXCEPT TABLE support for ALTER PUBLICATION,
+ * which is not currently supported.
+ */
+ if (fout->remoteVersion >= 190000)
+ {
+ int ntbls;
+ PGresult *res_tbls;
+
+ resetPQExpBuffer(query);
+ appendPQExpBuffer(query,
+ "SELECT prrelid\n"
+ "FROM pg_catalog.pg_publication_rel\n"
+ "WHERE prpubid = %u and prexcept",
+ pubinfo[i].dobj.catId.oid);
+
+ res_tbls = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
+
+ ntbls = PQntuples(res_tbls);
+ if (ntbls == 0)
+ continue;
+
+ for (int j = 0; j < ntbls; j++)
+ {
+ Oid prrelid;
+ TableInfo *tbinfo;
+
+ prrelid = atooid(PQgetvalue(res_tbls, j, 0));
+
+ tbinfo = findTableByOid(prrelid);
+ if (tbinfo == NULL)
+ continue;
+
+ simple_ptr_list_append(&pubinfo[i].except_tables, tbinfo);
+ }
+
+ PQclear(res_tbls);
+ }
}
cleanup:
@@ -4690,7 +4739,25 @@ dumpPublication(Archive *fout, const PublicationInfo *pubinfo)
if (pubinfo->puballtables && pubinfo->puballsequences)
appendPQExpBufferStr(query, " FOR ALL TABLES, ALL SEQUENCES");
else if (pubinfo->puballtables)
+ {
+ int n_excluded = 0;
+
appendPQExpBufferStr(query, " FOR ALL TABLES");
+
+ /* Include EXCEPT TABLE clause if there are except_tables. */
+ for (SimplePtrListCell *cell = pubinfo->except_tables.head; cell; cell = cell->next)
+ {
+ TableInfo *tbinfo = (TableInfo *) cell->ptr;
+
+ if (++n_excluded == 1)
+ appendPQExpBufferStr(query, " EXCEPT TABLE (");
+ else
+ appendPQExpBufferStr(query, ", ");
+ appendPQExpBuffer(query, "ONLY %s", fmtQualifiedDumpable(tbinfo));
+ }
+ if (n_excluded > 0)
+ appendPQExpBufferStr(query, ")");
+ }
else if (pubinfo->puballsequences)
appendPQExpBufferStr(query, " FOR ALL SEQUENCES");
@@ -4870,6 +4937,7 @@ getPublicationTables(Archive *fout, TableInfo tblinfo[], int numTables)
/* Collect all publication membership info. */
if (fout->remoteVersion >= 150000)
+ {
appendPQExpBufferStr(query,
"SELECT tableoid, oid, prpubid, prrelid, "
"pg_catalog.pg_get_expr(prqual, prrelid) AS prrelqual, "
@@ -4882,6 +4950,9 @@ getPublicationTables(Archive *fout, TableInfo tblinfo[], int numTables)
" WHERE attrelid = pr.prrelid AND attnum = prattrs[s])\n"
" ELSE NULL END) prattrs "
"FROM pg_catalog.pg_publication_rel pr");
+ if (fout->remoteVersion >= 190000)
+ appendPQExpBufferStr(query, " WHERE NOT pr.prexcept");
+ }
else
appendPQExpBufferStr(query,
"SELECT tableoid, oid, prpubid, prrelid, "
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 4c4b14e5fc7..d141eb66d17 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -676,6 +676,7 @@ typedef struct _PublicationInfo
bool pubtruncate;
bool pubviaroot;
PublishGencolsType pubgencols_type;
+ SimplePtrList except_tables;
} PublicationInfo;
/*
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index a8dcc2b5c75..54eae577c91 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -3170,6 +3170,36 @@ my %tests = (
like => { %full_runs, section_post_data => 1, },
},
+ 'CREATE PUBLICATION pub8' => {
+ create_order => 50,
+ create_sql =>
+ 'CREATE PUBLICATION pub8 FOR ALL TABLES EXCEPT (dump_test.test_table);',
+ regexp => qr/^
+ \QCREATE PUBLICATION pub8 FOR ALL TABLES EXCEPT TABLE (ONLY dump_test.test_table) WITH (publish = 'insert, update, delete, truncate');\E
+ /xm,
+ like => { %full_runs, section_post_data => 1, },
+ },
+
+ 'CREATE PUBLICATION pub9' => {
+ create_order => 50,
+ create_sql =>
+ 'CREATE PUBLICATION pub9 FOR ALL TABLES EXCEPT TABLE (dump_test.test_table, dump_test.test_second_table);',
+ regexp => qr/^
+ \QCREATE PUBLICATION pub9 FOR ALL TABLES EXCEPT TABLE (ONLY dump_test.test_table, ONLY dump_test.test_second_table) WITH (publish = 'insert, update, delete, truncate');\E
+ /xm,
+ like => { %full_runs, section_post_data => 1, },
+ },
+
+ 'CREATE PUBLICATION pub10' => {
+ create_order => 92,
+ create_sql =>
+ 'CREATE PUBLICATION pub10 FOR ALL TABLES EXCEPT TABLE (dump_test.test_inheritance_parent);',
+ regexp => qr/^
+ \QCREATE PUBLICATION pub10 FOR ALL TABLES EXCEPT TABLE (ONLY dump_test.test_inheritance_parent, ONLY dump_test.test_inheritance_child) WITH (publish = 'insert, update, delete, truncate');\E
+ /xm,
+ like => { %full_runs, section_post_data => 1, },
+ },
+
'CREATE SUBSCRIPTION sub1' => {
create_order => 50,
create_sql => 'CREATE SUBSCRIPTION sub1
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 3584c4e1428..1bfec0bd3ef 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3073,17 +3073,34 @@ describeOneTableDetails(const char *schemaname,
" WHERE attrelid = pr.prrelid AND attnum = prattrs[s])\n"
" ELSE NULL END) "
"FROM pg_catalog.pg_publication p\n"
- " JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid\n"
- " JOIN pg_catalog.pg_class c ON c.oid = pr.prrelid\n"
- "WHERE pr.prrelid = '%s'\n"
+ " JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid\n"
+ " JOIN pg_catalog.pg_class c ON c.oid = pr.prrelid\n"
+ "WHERE pr.prrelid = '%s'\n",
+ oid, oid, oid);
+
+ if (pset.sversion >= 190000)
+ appendPQExpBufferStr(&buf, " AND NOT pr.prexcept\n");
+
+ appendPQExpBuffer(&buf,
"UNION\n"
"SELECT pubname\n"
- " , NULL\n"
- " , NULL\n"
+ " , NULL\n"
+ " , NULL\n"
"FROM pg_catalog.pg_publication p\n"
- "WHERE p.puballtables AND pg_catalog.pg_relation_is_publishable('%s')\n"
- "ORDER BY 1;",
- oid, oid, oid, oid);
+ "WHERE p.puballtables AND pg_catalog.pg_relation_is_publishable('%s')\n",
+ oid);
+
+ if (pset.sversion >= 190000)
+ appendPQExpBuffer(&buf,
+ " AND NOT EXISTS (\n"
+ " SELECT 1\n"
+ " FROM pg_catalog.pg_publication_rel pr\n"
+ " JOIN pg_catalog.pg_class pc\n"
+ " ON pr.prrelid = pc.oid\n"
+ " WHERE pr.prrelid = '%s' AND pr.prpubid = p.oid)\n",
+ oid);
+
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
}
else
{
@@ -3134,6 +3151,35 @@ describeOneTableDetails(const char *schemaname,
PQclear(result);
}
+ /* Print publications that the table is explicitly excluded from */
+ if (pset.sversion >= 190000)
+ {
+ printfPQExpBuffer(&buf,
+ "SELECT pubname\n"
+ "FROM pg_catalog.pg_publication p\n"
+ "JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid\n"
+ "WHERE pr.prrelid = '%s'\n AND pr.prexcept\n"
+ "ORDER BY 1;", oid);
+
+ result = PSQLexec(buf.data);
+ if (!result)
+ goto error_return;
+ else
+ tuples = PQntuples(result);
+
+ if (tuples > 0)
+ printTableAddFooter(&cont, _("Except Publications:"));
+
+ /* Might be an empty set - that's ok */
+ for (i = 0; i < tuples; i++)
+ {
+ printfPQExpBuffer(&buf, " \"%s\"", PQgetvalue(result, i, 0));
+
+ printTableAddFooter(&cont, buf.data);
+ }
+ PQclear(result);
+ }
+
/*
* If verbose, print NOT NULL constraints.
*/
@@ -6753,8 +6799,12 @@ describePublications(const char *pattern)
" pg_catalog.pg_publication_rel pr\n"
"WHERE c.relnamespace = n.oid\n"
" AND c.oid = pr.prrelid\n"
- " AND pr.prpubid = '%s'\n"
- "ORDER BY 1,2", pubid);
+ " AND pr.prpubid = '%s'\n", pubid);
+
+ if (pset.sversion >= 190000)
+ appendPQExpBuffer(&buf, " AND NOT pr.prexcept\n");
+
+ appendPQExpBuffer(&buf, "ORDER BY 1,2");
if (!addFooterToPublicationDesc(&buf, _("Tables:"), false, &cont))
goto error_return;
@@ -6772,6 +6822,23 @@ describePublications(const char *pattern)
goto error_return;
}
}
+ else
+ {
+ if (pset.sversion >= 190000)
+ {
+ /* Get the excluded tables for the specified publication */
+ printfPQExpBuffer(&buf,
+ "SELECT concat(c.relnamespace::regnamespace, '.', c.relname)\n"
+ "FROM pg_catalog.pg_class c\n"
+ " JOIN pg_catalog.pg_publication_rel pr ON c.oid = pr.prrelid\n"
+ "WHERE pr.prpubid = '%s'\n"
+ " AND pr.prexcept\n"
+ "ORDER BY 1", pubid);
+ if (!addFooterToPublicationDesc(&buf, _("Except tables:"),
+ true, &cont))
+ goto error_return;
+ }
+ }
printTable(&cont, pset.queryFout, false, pset.logfile);
printTableCleanup(&cont);
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 8b91bc00062..39404ea0f69 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -3669,7 +3669,17 @@ match_previous_words(int pattern_id,
else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL"))
COMPLETE_WITH("TABLES", "SEQUENCES");
else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "TABLES"))
- COMPLETE_WITH("WITH (");
+ COMPLETE_WITH("EXCEPT TABLE (", "WITH (");
+ else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "TABLES", "EXCEPT"))
+ COMPLETE_WITH("TABLE (");
+ else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "TABLES", "EXCEPT", "TABLE"))
+ COMPLETE_WITH("(");
+ else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "TABLES", "EXCEPT", "TABLE", "("))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
+ else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "TABLES", "EXCEPT", "TABLE", "(", MatchAnyN) && ends_with(prev_wd, ','))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
+ else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "TABLES", "EXCEPT", "TABLE", "(", MatchAnyN) && !ends_with(prev_wd, ','))
+ COMPLETE_WITH(")");
else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "TABLES"))
COMPLETE_WITH("IN SCHEMA");
else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "TABLE", MatchAny) && !ends_with(prev_wd, ','))
diff --git a/src/include/catalog/pg_publication.h b/src/include/catalog/pg_publication.h
index 368becca899..5126c996ff7 100644
--- a/src/include/catalog/pg_publication.h
+++ b/src/include/catalog/pg_publication.h
@@ -146,14 +146,16 @@ typedef struct PublicationRelInfo
Relation relation;
Node *whereClause;
List *columns;
+ bool except;
} PublicationRelInfo;
extern Publication *GetPublication(Oid pubid);
extern Publication *GetPublicationByName(const char *pubname, bool missing_ok);
-extern List *GetRelationPublications(Oid relid);
+extern bool GetRelationPublications(Oid relid, List **pubids, List **except_pubids);
/*---------
- * Expected values for pub_partopt parameter of GetPublicationRelations(),
+ * Expected values for pub_partopt parameter of
+ * GetPublicationIncludedRelations(), and GetAllPublicationExcludedTables(),
* which allows callers to specify which partitions of partitioned tables
* mentioned in the publication they expect to see.
*
@@ -168,9 +170,12 @@ typedef enum PublicationPartOpt
PUBLICATION_PART_ALL,
} PublicationPartOpt;
-extern List *GetPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt);
+extern List *GetPublicationIncludedRelations(Oid pubid,
+ PublicationPartOpt pub_partopt);
+extern List *GetAllPublicationExcludedTables(Oid pubid,
+ PublicationPartOpt pub_partopt);
extern List *GetAllTablesPublications(void);
-extern List *GetAllPublicationRelations(char relkind, bool pubviaroot);
+extern List *GetAllPublicationRelations(Publication *pub, char relkind);
extern List *GetPublicationSchemas(Oid pubid);
extern List *GetSchemaPublications(Oid schemaid);
extern List *GetSchemaPublicationRelations(Oid schemaid,
diff --git a/src/include/catalog/pg_publication_rel.h b/src/include/catalog/pg_publication_rel.h
index 3a8790e8482..e3ccba5ec79 100644
--- a/src/include/catalog/pg_publication_rel.h
+++ b/src/include/catalog/pg_publication_rel.h
@@ -31,6 +31,7 @@ CATALOG(pg_publication_rel,6106,PublicationRelRelationId)
Oid oid; /* oid */
Oid prpubid BKI_LOOKUP(pg_publication); /* Oid of the publication */
Oid prrelid BKI_LOOKUP(pg_class); /* Oid of the relation */
+ bool prexcept BKI_DEFAULT(f); /* exclude the relation */
#ifdef CATALOG_VARLEN /* variable-length fields start here */
pg_node_tree prqual; /* qualifications */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 646d6ced763..5282425b4dd 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -4300,6 +4300,7 @@ typedef struct PublicationTable
RangeVar *relation; /* relation to be published */
Node *whereClause; /* qualifications */
List *columns; /* List of columns in a publication table */
+ bool except; /* exclude the relation */
} PublicationTable;
/*
@@ -4308,6 +4309,7 @@ typedef struct PublicationTable
typedef enum PublicationObjSpecType
{
PUBLICATIONOBJ_TABLE, /* A table */
+ PUBLICATIONOBJ_EXCEPT_TABLE, /* A table to be excluded */
PUBLICATIONOBJ_TABLES_IN_SCHEMA, /* All tables in schema */
PUBLICATIONOBJ_TABLES_IN_CUR_SCHEMA, /* All tables in first element of
* search_path */
@@ -4336,6 +4338,7 @@ typedef struct PublicationAllObjSpec
{
NodeTag type;
PublicationAllObjType pubobjtype; /* type of this publication object */
+ List *except_tables; /* List of tables to be excluded */
ParseLoc location; /* token location, or -1 if unknown */
} PublicationAllObjSpec;
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 7fb49aaf29b..b2115edb3c3 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -213,33 +213,104 @@ Not-null constraints:
regress_publication_user | t | f | t | t | f | f | none | f
(1 row)
-DROP TABLE testpub_tbl2;
-DROP PUBLICATION testpub_foralltables, testpub_fortable, testpub_forschema, testpub_for_tbl_schema;
-CREATE TABLE testpub_tbl3 (a int);
-CREATE TABLE testpub_tbl3a (b text) INHERITS (testpub_tbl3);
SET client_min_messages = 'ERROR';
-CREATE PUBLICATION testpub3 FOR TABLE testpub_tbl3;
-CREATE PUBLICATION testpub4 FOR TABLE ONLY testpub_tbl3;
+-- Exclude tables using FOR ALL TABLES EXCEPT TABLE (tablelist)
+CREATE PUBLICATION testpub_foralltables_excepttable FOR ALL TABLES EXCEPT TABLE (testpub_tbl1, testpub_tbl2);
+\dRp+ testpub_foralltables_excepttable
+ Publication testpub_foralltables_excepttable
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | t | f | t | t | t | t | none | f
+Except tables:
+ "public.testpub_tbl1"
+ "public.testpub_tbl2"
+
+-- Exclude tables using FOR ALL TABLES EXCEPT (tablelist)
+CREATE PUBLICATION testpub_foralltables_excepttable1 FOR ALL TABLES EXCEPT (testpub_tbl1);
+\dRp+ testpub_foralltables_excepttable1
+ Publication testpub_foralltables_excepttable1
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | t | f | t | t | t | t | none | f
+Except tables:
+ "public.testpub_tbl1"
+
+-- Check that the table description shows the publication list the table is
+-- excluded from
+\d testpub_tbl1
+ Table "public.testpub_tbl1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+------------------------------------------
+ id | integer | | not null | nextval('testpub_tbl1_id_seq'::regclass)
+ data | text | | |
+Indexes:
+ "testpub_tbl1_pkey" PRIMARY KEY, btree (id)
+Publications:
+ "testpub_foralltables"
+Except Publications:
+ "testpub_foralltables_excepttable"
+ "testpub_foralltables_excepttable1"
+
RESET client_min_messages;
+DROP TABLE testpub_tbl2;
+DROP PUBLICATION testpub_foralltables, testpub_fortable, testpub_forschema, testpub_for_tbl_schema, testpub_foralltables_excepttable, testpub_foralltables_excepttable1;
+CREATE TABLE testpub_tbl_parent (a int);
+CREATE TABLE testpub_tbl_child (b text) INHERITS (testpub_tbl_parent);
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION testpub3 FOR TABLE testpub_tbl_parent;
\dRp+ testpub3
Publication testpub3
Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
regress_publication_user | f | f | t | t | t | t | none | f
Tables:
- "public.testpub_tbl3"
- "public.testpub_tbl3a"
+ "public.testpub_tbl_child"
+ "public.testpub_tbl_parent"
+CREATE PUBLICATION testpub4 FOR TABLE ONLY testpub_tbl_parent;
\dRp+ testpub4
Publication testpub4
Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
regress_publication_user | f | f | t | t | t | t | none | f
Tables:
- "public.testpub_tbl3"
+ "public.testpub_tbl_parent"
-DROP TABLE testpub_tbl3, testpub_tbl3a;
-DROP PUBLICATION testpub3, testpub4;
+-- Exclude parent table, omitting both of 'ONLY' and '*'
+CREATE PUBLICATION testpub5 FOR ALL TABLES EXCEPT TABLE (testpub_tbl_parent);
+\dRp+ testpub5
+ Publication testpub5
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | t | f | t | t | t | t | none | f
+Except tables:
+ "public.testpub_tbl_child"
+ "public.testpub_tbl_parent"
+
+-- EXCEPT with '*': exclude table and all descendants
+CREATE PUBLICATION testpub6 FOR ALL TABLES EXCEPT TABLE (testpub_tbl_parent *);
+\dRp+ testpub6
+ Publication testpub6
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | t | f | t | t | t | t | none | f
+Except tables:
+ "public.testpub_tbl_child"
+ "public.testpub_tbl_parent"
+
+-- EXCEPT with ONLY: exclude table but not descendants
+CREATE PUBLICATION testpub7 FOR ALL TABLES EXCEPT TABLE (ONLY testpub_tbl_parent);
+\dRp+ testpub7
+ Publication testpub7
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | t | f | t | t | t | t | none | f
+Except tables:
+ "public.testpub_tbl_parent"
+
+RESET client_min_messages;
+DROP TABLE testpub_tbl_parent, testpub_tbl_child;
+DROP PUBLICATION testpub3, testpub4, testpub5, testpub6, testpub7;
--- Tests for publications with SEQUENCES
CREATE SEQUENCE regress_pub_seq0;
CREATE SEQUENCE pub_test.regress_pub_seq1;
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 85b00bd67c8..5224da93d77 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -105,20 +105,41 @@ SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_forall
\d+ testpub_tbl2
\dRp+ testpub_foralltables
+SET client_min_messages = 'ERROR';
+-- Exclude tables using FOR ALL TABLES EXCEPT TABLE (tablelist)
+CREATE PUBLICATION testpub_foralltables_excepttable FOR ALL TABLES EXCEPT TABLE (testpub_tbl1, testpub_tbl2);
+\dRp+ testpub_foralltables_excepttable
+-- Exclude tables using FOR ALL TABLES EXCEPT (tablelist)
+CREATE PUBLICATION testpub_foralltables_excepttable1 FOR ALL TABLES EXCEPT (testpub_tbl1);
+\dRp+ testpub_foralltables_excepttable1
+-- Check that the table description shows the publication list the table is
+-- excluded from
+\d testpub_tbl1
+
+RESET client_min_messages;
DROP TABLE testpub_tbl2;
-DROP PUBLICATION testpub_foralltables, testpub_fortable, testpub_forschema, testpub_for_tbl_schema;
+DROP PUBLICATION testpub_foralltables, testpub_fortable, testpub_forschema, testpub_for_tbl_schema, testpub_foralltables_excepttable, testpub_foralltables_excepttable1;
-CREATE TABLE testpub_tbl3 (a int);
-CREATE TABLE testpub_tbl3a (b text) INHERITS (testpub_tbl3);
+CREATE TABLE testpub_tbl_parent (a int);
+CREATE TABLE testpub_tbl_child (b text) INHERITS (testpub_tbl_parent);
SET client_min_messages = 'ERROR';
-CREATE PUBLICATION testpub3 FOR TABLE testpub_tbl3;
-CREATE PUBLICATION testpub4 FOR TABLE ONLY testpub_tbl3;
-RESET client_min_messages;
+CREATE PUBLICATION testpub3 FOR TABLE testpub_tbl_parent;
\dRp+ testpub3
+CREATE PUBLICATION testpub4 FOR TABLE ONLY testpub_tbl_parent;
\dRp+ testpub4
+-- Exclude parent table, omitting both of 'ONLY' and '*'
+CREATE PUBLICATION testpub5 FOR ALL TABLES EXCEPT TABLE (testpub_tbl_parent);
+\dRp+ testpub5
+-- EXCEPT with '*': exclude table and all descendants
+CREATE PUBLICATION testpub6 FOR ALL TABLES EXCEPT TABLE (testpub_tbl_parent *);
+\dRp+ testpub6
+-- EXCEPT with ONLY: exclude table but not descendants
+CREATE PUBLICATION testpub7 FOR ALL TABLES EXCEPT TABLE (ONLY testpub_tbl_parent);
+\dRp+ testpub7
-DROP TABLE testpub_tbl3, testpub_tbl3a;
-DROP PUBLICATION testpub3, testpub4;
+RESET client_min_messages;
+DROP TABLE testpub_tbl_parent, testpub_tbl_child;
+DROP PUBLICATION testpub3, testpub4, testpub5, testpub6, testpub7;
--- Tests for publications with SEQUENCES
CREATE SEQUENCE regress_pub_seq0;
diff --git a/src/test/subscription/meson.build b/src/test/subscription/meson.build
index a4c7dbaff59..07282aa3c18 100644
--- a/src/test/subscription/meson.build
+++ b/src/test/subscription/meson.build
@@ -46,6 +46,7 @@ tests += {
't/034_temporal.pl',
't/035_conflicts.pl',
't/036_sequences.pl',
+ 't/037_rep_changes_except_table.pl',
't/100_bugs.pl',
],
},
diff --git a/src/test/subscription/t/037_rep_changes_except_table.pl b/src/test/subscription/t/037_rep_changes_except_table.pl
new file mode 100644
index 00000000000..95904ddd005
--- /dev/null
+++ b/src/test/subscription/t/037_rep_changes_except_table.pl
@@ -0,0 +1,238 @@
+
+# Copyright (c) 2021-2025, PostgreSQL Global Development Group
+
+# Logical replication tests for EXCEPT TABLE publications
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Initialize publisher node
+my $node_publisher = PostgreSQL::Test::Cluster->new('publisher');
+$node_publisher->init(allows_streaming => 'logical');
+$node_publisher->start;
+
+# Initialize subscriber node
+my $node_subscriber = PostgreSQL::Test::Cluster->new('subscriber');
+$node_subscriber->init(allows_streaming => 'logical');
+$node_subscriber->start;
+
+# ============================================
+# EXCEPT TABLE test cases for normal tables
+# ============================================
+# Create schemas and tables on publisher
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ CREATE SCHEMA sch1;
+ CREATE TABLE sch1.tab1 AS SELECT generate_series(1,10) AS a;
+));
+
+# Create schemas and tables on subscriber
+$node_subscriber->safe_psql(
+ 'postgres', qq(
+ CREATE SCHEMA sch1;
+ CREATE TABLE sch1.tab1 (a int);
+));
+
+# Setup logical replication
+my $publisher_connstr = $node_publisher->connstr . ' dbname=postgres';
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION tap_pub_schema FOR ALL TABLES EXCEPT TABLE (sch1.tab1)"
+);
+# Create a logical replication slot
+$node_publisher->safe_psql('postgres',
+ "SELECT pg_create_logical_replication_slot('test_slot', 'pgoutput')");
+
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION tap_sub_schema CONNECTION '$publisher_connstr' PUBLICATION tap_pub_schema"
+);
+
+# Wait for initial table sync to finish
+$node_subscriber->wait_for_subscription_sync($node_publisher,
+ 'tap_sub_schema');
+
+# Check the table data does not sync for excluded table
+my $result = $node_subscriber->safe_psql('postgres',
+ "SELECT count(*), min(a), max(a) FROM sch1.tab1");
+is($result, qq(0||),
+ 'check there is no initial data copied for the excluded table');
+
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO sch1.tab1 VALUES(generate_series(11,20))");
+
+# Verify that data inserted to excluded table is not published.
+$result = $node_publisher->safe_psql('postgres',
+ "SELECT count(*) = 0 FROM pg_logical_slot_get_binary_changes('test_slot', NULL, NULL, 'proto_version', '1', 'publication_names', 'tap_sub_schema')"
+);
+is($result, qq(t), 'check no changes for excluded table in replication slot');
+
+$node_publisher->wait_for_catchup('tap_sub_schema');
+
+# Verify that data inserted to the excluded table is not replicated.
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT count(*), min(a), max(a) FROM sch1.tab1");
+is($result, qq(0||), 'check replicated inserts on subscriber');
+
+# cleanup
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION tap_pub_schema");
+$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_schema");
+
+# ============================================
+# EXCEPT TABLE test cases for partitioned tables
+# Check behavior of EXCEPT TABLE with publish_via_partition_root on a
+# partitioned table and its partitions.
+# ============================================
+# Setup partitioned table and partitions on the publisher that map to normal
+# tables on the subscriber
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ CREATE TABLE sch1.t1(a int) PARTITION BY RANGE(a);
+ CREATE TABLE sch1.part1 PARTITION OF sch1.t1 FOR VALUES FROM (0) TO (5);
+ CREATE TABLE sch1.part2 PARTITION OF sch1.t1 FOR VALUES FROM (6) TO (10);
+));
+
+$node_subscriber->safe_psql(
+ 'postgres', qq(
+ CREATE TABLE sch1.t1(a int);
+ CREATE TABLE sch1.part1(a int);
+ CREATE TABLE sch1.part2(a int);
+));
+
+# EXCEPT TABLE (sch1.part1) with publish_via_partition_root = false
+# Excluding a partition while publish_via_partition_root = false prevents
+# replication of rows inserted into the partitioned table for that particular
+# partition.
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT TABLE (sch1.part1) WITH (publish_via_partition_root = false);
+ INSERT INTO sch1.t1 VALUES (1), (6);
+));
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION tap_sub_part CONNECTION '$publisher_connstr' PUBLICATION tap_pub_part"
+);
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub_part');
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO sch1.t1 VALUES (2), (7);");
+$node_publisher->wait_for_catchup('tap_sub_part');
+
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.t1");
+is($result, qq(), 'check rows on partitioned table');
+
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part1");
+is($result, qq(), 'check rows on excluded partition');
+
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part2");
+is( $result, qq(6
+7), 'check rows on other partition');
+
+$node_publisher->safe_psql('postgres', "TRUNCATE sch1.t1");
+$node_publisher->wait_for_catchup('tap_sub_part');
+$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_part");
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION tap_pub_part;");
+
+# EXCEPT TABLE (sch1.t1) with publish_via_partition_root = false
+# Excluding the partitioned table still allows rows inserted into the
+# partitioned table to be replicated via its partitions.
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT TABLE (sch1.t1) WITH (publish_via_partition_root = false);
+ INSERT INTO sch1.t1 VALUES (1), (6);
+));
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION tap_sub_part CONNECTION '$publisher_connstr' PUBLICATION tap_pub_part"
+);
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub_part');
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO sch1.t1 VALUES (2), (7);");
+$node_publisher->wait_for_catchup('tap_sub_part');
+
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.t1");
+is($result, qq(), 'check rows on partitioned table');
+
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part1");
+is( $result, qq(1
+2), 'check rows on first partition');
+
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part2");
+is( $result, qq(6
+7), 'check rows on second partition');
+
+$node_publisher->safe_psql('postgres', "TRUNCATE sch1.t1");
+$node_publisher->wait_for_catchup('tap_sub_part');
+$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_part");
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION tap_pub_part;");
+$node_publisher->safe_psql('postgres',
+ "SELECT slot_name FROM pg_replication_slot_advance('test_slot', pg_current_wal_lsn());"
+);
+
+# EXCEPT TABLE (sch1.t1) with publish_via_partition_root = true
+# When the partitioned table is excluded and publish_via_partition_root is true,
+# no rows from the table or its partitions are replicated.
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT TABLE (sch1.t1) WITH (publish_via_partition_root);
+ INSERT INTO sch1.t1 VALUES (1), (6);
+));
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION tap_sub_part CONNECTION '$publisher_connstr' PUBLICATION tap_pub_part"
+);
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub_part');
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO sch1.t1 VALUES (2), (7);");
+$node_publisher->wait_for_catchup('tap_sub_part');
+
+# Verify that data inserted to the partitioned table is not published when it is
+# excluded with publish_via_partition_root = true.
+$result = $node_publisher->safe_psql('postgres',
+ "SELECT count(*) = 0 FROM pg_logical_slot_get_binary_changes('test_slot', NULL, NULL, 'proto_version', '1', 'publication_names', 'tap_pub_part')"
+);
+is($result, qq(t), 'check no changes for excluded table in replication slot');
+
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.t1");
+is($result, qq(), 'check rows on partitioned table');
+
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part1");
+is($result, qq(), 'check rows on first partition');
+
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part2");
+is($result, qq(), 'check rows on second partition');
+
+$node_publisher->safe_psql('postgres', "TRUNCATE sch1.t1");
+$node_publisher->wait_for_catchup('tap_sub_part');
+$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_part");
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION tap_pub_part;");
+
+# EXCEPT TABLE (sch1.part1) with publish_via_partition_root = true
+# When a partition is excluded but publish_via_partition_root is true,
+# rows published through the partitioned table can still be replicated.
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT TABLE (sch1.part1) WITH (publish_via_partition_root);
+ INSERT INTO sch1.t1 VALUES (1), (6)
+));
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION tap_sub_part CONNECTION '$publisher_connstr' PUBLICATION tap_pub_part"
+);
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub_part');
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO sch1.t1 VALUES (2), (7);");
+$node_publisher->wait_for_catchup('tap_sub_part');
+
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.t1 ORDER BY a");
+is( $result, qq(1
+2
+6
+7), 'check rows on partitioned table');
+
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part1");
+is($result, qq(), 'check rows on excluded partition');
+
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part2");
+is($result, qq(), 'check rows on other partition');
+
+$node_subscriber->stop('fast');
+$node_publisher->stop('fast');
+
+done_testing();
--
2.43.0
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: <CALDaNm2MZU8-JbFruQAxF8OZfcH4ZsBrCsWDg3VMbO-P+xKmBg@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