public inbox for [email protected]
help / color / mirror / Atom feedFrom: vignesh C <[email protected]>
To: Amit Kapila <[email protected]>
Cc: Shlok Kyal <[email protected]>
Cc: shveta malik <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: Dilip Kumar <[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: Fri, 20 Feb 2026 14:08:21 +0530
Message-ID: <CALDaNm3X24fJznRUFh6NVhY1SDzgY9Aie1Ks=b6YqmAx-Z4H7Q@mail.gmail.com> (raw)
In-Reply-To: <CAA4eK1JcrqPi7Pa9eQqj-=NFPAdzMhfVD=1jbwRWXT6tGtRyEw@mail.gmail.com>
References: <CALDaNm3=JrucjhiiwsYQw5-PGtBHFONa6F7hhWCXMsGvh=tamA@mail.gmail.com>
<CALj2ACVOzhs+BD+abFV2x4oKJdsDNd6SgsE7r8UjnZDCKGEckA@mail.gmail.com>
<CAA4eK1K6Kr88d2S0zFdHRMyuoaZeNh+ktU+oigmCuD09_x_-+g@mail.gmail.com>
<CAHut+PsvC-NezO3MJkdyEz=G1QRje2LntjwhQiEeVbmhOQuBMA@mail.gmail.com>
<CALDaNm18VH2j8cTqfELHQ=0ZNognbGBhbHPteJenWQC6C2dueQ@mail.gmail.com>
<CALDaNm0k_0Ccj47wzJzzPFwgQB7w=R5+Q2_nSqYrmMmjhmcRUw@mail.gmail.com>
<CAHut+Pv_0DwyWoGQNMF+G2AGqMuJTzWQKRtmxaC+=zLTPL-Zkw@mail.gmail.com>
<CALDaNm2-GJt2HsYTkLqQ=ecm=R-vOBw1=aM_d2EiYbz39x_cTQ@mail.gmail.com>
<TYCPR01MB8373C3120C2B3112001ED6F1EDCF9@TYCPR01MB8373.jpnprd01.prod.outlook.com>
<CALDaNm0iZZDB300Dez_97S8G6_RW5QpQ8ef6X3wq8tyK-8wnXQ@mail.gmail.com>
<CAHut+PtiomM+iyAZHvb2dzfsPvRru266KuBe49hKy2n2h+m_zA@mail.gmail.com>
<CALDaNm30KDnwX4Czi29fqLb8JBkuwqjbpj9ixwNXXox574NZqQ@mail.gmail.com>
<CALDaNm1PfKRJsEzbKpyt=v4p3bw+_SzE+LFPsMhR5X+qs+0pPw@mail.gmail.com>
<TYCPR01MB83730A2F1D6A5303E9C1416AEDD99@TYCPR01MB8373.jpnprd01.prod.outlook.com>
<CALDaNm0sAU4s1KTLOEWv=rYo5dQK6uFTJn_0FKj3XG1Nv4D-qw@mail.gmail.com>
<CALDaNm3CLRa95tpas6tEj8x58MUNDShxBNoYS+P8Uq5cryoAOw@mail.gmail.com>
<CALDaNm0EKC3o=v+F7GneGibuCULGKkBWXmNaVB4GR9HoqD066A@mail.gmail.com>
<CALDaNm1Z1Rmqj9s6P9ZzmrVA9F_vZ_DwwhYAJmsjqmY6dS3-hA@mail.gmail.com>
<CAB8KJ=jJGuW=ozKmXZzKDUHZ_-J2ZYGOtJo=i2cnNbSu6=KuYg@mail.gmail.com>
<CALDaNm1mbFP8fxHU_H1Ex4cT2Aq3n8FE79tq0TO5ThvFnDUYMA@mail.gmail.com>
<CAB8KJ=jq4RwTs8K7pokmXQwQppP2ChVJLMSAdXaxAX+c1r+mdg@mail.gmail.com>
<CALDaNm1mJvLni8GODebKBmyegXuZ18bLoG-Pz6H1MCX=vphCYA@mail.gmail.com>
<CALDaNm3dWZCYDih55qTNAYsjCvYXMFv=46UsDWmfCnXMt3kPCg@mail.gmail.com>
<CALDaNm1AQZYgT0tALRrkvpP1Q+8+e7vkGCUjQ-jim1C0q3e=zA@mail.gmail.com>
<CAA4eK1KRdAPC=5=7tQ1GW0cRwD=zaDMi+T4u_k4GxPhPY6e8BQ@mail.gmail.com>
<OS3PR01MB5718C8BE84B862E7E0CEC29B94BD2@OS3PR01MB5718.jpnprd01.prod.outlook.com>
<CAA4eK1KYQz7cf46_D=6VkZ4J6Y8vJ88MMi=6zm2TJXDP+V1mLg@mail.gmail.com>
<CANhcyEXZq4mP5dNgg7u=sMPwvxA4_ZN9U92uZEuzs=0xTu+8Yg@mail.gmail.com>
<CANhcyEXspT3v5-Tdop9uqQV2HWBvZoN5P0BxXQ6Md6Mr7GXK9A@mail.gmail.com>
<CAHut+PuiaLOCkiAx9nPnjk6wTbPFvnm9T5svTuKbgwJwTdea8w@mail.gmail.com>
<CANhcyEV_MePxgftHY65et1WdOAk70M0C7PZ1STPUO8PXHVB1YA@mail.gmail.com>
<CAHut+Ps0hSNqrjv_jT1AuXxO-CrZue3ixE0jKsxVhtArMrkujQ@mail.gmail.com>
<CANhcyEXX3viVpYcGHD_fzhf_f6CDQWr2+VBywrJf5zm_XiB4tg@mail.gmail.com>
<CAHut+PsXP_61ZXuVOx5u9FZGK3oH4taaA59oOzgqyygZx8ezWw@mail.gmail.com>
<CANhcyEU+aPu6iAH2cTA0cDtn3pd6c_njBONCt3FubYZoEEnm8Q@mail.gmail.com>
<CAHut+Pv2P6dJ7hZj_fmzN+=xzjvpOpgkAJvDZg3TD2xpvmY1NQ@mail.gmail.com>
<CANhcyEW2LK4diNeCG862DE40yQoV3VAgf59kXUq2TuR8fnw5vQ@mail.gmail.com>
<CAHut+PuSHScrODVGCM7P53Mv1HE2N6ThzkH4+gQ1eFXVeD-OCA@mail.gmail.com>
<CANhcyEUtYV-9ujtxLasnxN_peT+3LuZjcRx1xUECh1CCmANB8w@mail.gmail.com>
<CAHut+PuviFA6C7qps=+kDYfe3P99as8NCjbR=SYxoi0o96ipoA@mail.gmail.com>
<CANhcyEXkeg3sjkS3DS9yU1ckz4ozUBNZ+RmrWaRNSSVCR8RquA@mail.gmail.com>
<CAHut+PsHavMy_KJ0MwR9J6q0BTTty54TxS-KBZc7X6tb4u7rfA@mail.gmail.com>
<CANhcyEU=k4+0BqOu25N76g738XKUwfLGGdf8e+ssGiRKHC4RwQ@mail.gmail.com>
<CAHut+Ptdi8txJbMc+5Sp2uB4QLGOp-rZdkBbXyhAkxim0iAhBQ@mail.gmail.com>
<CANhcyEW+uJB_bvQLEaZCgoRTc1=i+QnrPPHxZ2=0SBSCyj9pkg@mail.gmail.com>
<CAHut+Ptq0-tMYUOvG3yR34AvuEzR9vUH=muqV_=uEO3zCuA6rA@mail.gmail.com>
<CANhcyEUEMWSkTfGc7Q3B+UiOzSiOmOGLgK-+C5DXwtCGOnDBhg@mail.gmail.com>
<CAHut+PuAgSDr3kbSxPMYEyCeGiJ5hgaT1JUvuiYPRT=Q+--O-Q@mail.gmail.com>
<CANhcyEU_uuiKMRrd_E_DeYsyCvwY_u995E-Do3d66J7tQnzdzw@mail.gmail.com>
<CANhcyEWK9sj9UY4uaV36Q8qxUv=8Joch0o98RCN5U3xLjUxAag@mail.gmail.com>
<CALDaNm32XQDR4qsOhPQeophVbZ8r+ShJSSssoVfdPcwG6joPHQ@mail.gmail.com>
<CANhcyEVt2CBnG7MOktaPPV4rYapHR-VHe5=qoziTZh1L9SVc6w@mail.gmail.com>
<CAHut+PtGu2j72yV_as_TVKfWr3ctd18svReGEx3xa=q5BtKyKA@mail.gmail.com>
<CANhcyEUh9ki36VTXyYf8UqUrfLX9ZhfP_f2LjpvvycgqWLQqqQ@mail.gmail.com>
<CAHut+Pv96u0VciuSx2B99jDHEvn7svVJynCmw-qYb=z4Kc2knA@mail.gmail.com>
<CANhcyEWGiWwGt1-v6d9bCAae9Np7cNPt+iRV9PXBZ0z=75XEVw@mail.gmail.com>
<CANhcyEWAQHtUfgNPA2m-+okEh7pXaK5irBm+yzyNVJXL2LUTXw@mail.gmail.com>
<CAHut+PuzcKXheZwgNvDJkwK5txd1kzNRxCmcJcbr=_9mGHjKtA@mail.gmail.com>
<CANhcyEXCKPCAdoqBLAhxt64Nwf+7T52dd8daE3qvhBNTvro13Q@mail.gmail.com>
<CAHut+Pu50yWjMR5Mswhi6uVKQmn3hO9o0ocRAgXyUUf4cnVTwg@mail.gmail.com>
<CANhcyEV7ewT+nfLM2owquxW-_6m8Ju+P93y=acoS=JCBHoT-MQ@mail.gmail.com>
<CAJpy0uDdQH7b=LRn_HevbmFACpvq9=c32Vb3tRvjSOnDsQd74w@mail.gmail.com>
<CAA4eK1KZ1Sb0soHp3HH2htwJ3=qka-eQjW35vOW3+4VeWw4VoQ@mail.gmail.com>
<CANhcyEXwLrQsec6g+1dqWTKyJQMQMh=getj28C+zLL14BjuumA@mail.gmail.com>
<CAJpy0uD35+YmDahVNUm+NZsjisV_k2hUDBkiFOJDwAJ1o1CT9A@mail.gmail.com>
<CANhcyEV_EVi5cgJ6WPvmeVAqjCS7Of+VAWuRHZtsVf8PQb_z7g@mail.gmail.com>
<CAHut+Pu5Ukuvd_5oK_mTEjSOEAYupbEXzRqXwSPoLe_sDkA_fg@mail.gmail.com>
<CANhcyEWg2WbEW_fFwk0D3J2KBrUF7th6VrE+gvESgkUKP9VpZg@mail.gmail.com>
<CAJpy0uBegaytfG=AS5VUb-6jAEDzC374-1icn-hP5AnRoMJ+Lg@mail.gmail.com>
<CAJpy0uD+orAxUWKq9Ogf5FEWtXcwkQXb_YZOvXqDc8b15nJe9A@mail.gmail.com>
<CANhcyEVDTPVRWDm-BdJe0yaWFuz1ozTUFNHKvzX2YuiUHh=ArA@mail.gmail.com>
<CAHut+Pu6ameXD4YwbhMXf8kHBhPJXGpOmc21R5o7Lo18hkSKMQ@mail.gmail.com>
<CANhcyEUiwF9w2U6CzkuOsXkLYMOjPs=6O8QyjU_5fDfYWyMswg@mail.gmail.com>
<CAJpy0uCnK7A8HE_mjWaVHDn9Q=CNJQM_mB=QTtseh=GQ4aGumQ@mail.gmail.com>
<CANhcyEWewbuqrwLTuwZUSGRGmLSmRadiNKyDB1yt0Sm+JTMHZA@mail.gmail.com>
<CAA4eK1KFdzZD3JvjfajhbHYYU9DErNcDVQZxfvGKv9npNfO5fQ@mail.gmail.com>
<CAJpy0uD81HRrMYr7S-6AV4W2PtbGKM-nf2D89zsoMHJ9jZssUg@mail.gmail.com>
<CAFiTN-sbpXXbU=u0U7SWjjouScAddZq+xb2FP8zfyVZZZt8z7w@mail.gmail.com>
<CALDaNm2x3fR+AEji0ZruTdss-4WDatraXKs1QA44eVnsBmbUiA@mail.gmail.com>
<CALDaNm3kX=16L-72m13CqXL9uAiHURNZ+BLo-HfTEYHDFejj-A@mail.gmail.com>
<CAJpy0uBfEuzYX+qjAPM+GV5duOwMNqO6fkDtsN1OzONVNR9WGQ@mail.gmail.com>
<CALDaNm2MZU8-JbFruQAxF8OZfcH4ZsBrCsWDg3VMbO-P+xKmBg@mail.gmail.com>
<CANhcyEXP5BS+0Hq2o=Jmx6J5Nf+H0TW9yayO21NnVwtFqauDGw@mail.gmail.com>
<CALDaNm1Y5C_-gOA95a+07P5z4DY=PuXbnvdqMT6g7t7OEKD2YA@mail.gmail.com>
<CANhcyEWda-0kWVCn8zQ4z9snFK4sCo1-JEewFGWs-9PMrJAmrg@mail.gmail.com>
<CAJpy0uB=gaJgDaP8MiVeZmpxALrmDPbx=fqoidAbzhwEO3cv-g@mail.gmail.com>
<CAA4eK1J9=vfNYU=K=0pfEf=b8iZFB0FGocCOpJL1tdTY-g94uw@mail.gmail.com>
<CAHut+PsxDe-mVq_6YyhaUCyPUuohZE5dRtf80syP3y_n+Z6Tog@mail.gmail.com>
<CAA4eK1JXdUArUqJ_uPjwXcYR8qj2hJyVORdRNhmx9=Kp0EXumg@mail.gmail.com>
<CAKFQuwauV8v46mM3kZ9ducnN9Oq6E2etLbvf5KxVj4K0CEZKHg@mail.gmail.com>
<CAJpy0uC_0uvhmXyWegKGRozhpyoLGHwHrUAK=Wk+bSmzqLMoSw@mail.gmail.com>
<CALDaNm2EU7qdEnGmirzVn9a1O-6y3q5c7nzsBZCuyVjaPS37mg@mail.gmail.com>
<CAJpy0uD5nVQ9vasP+UP=1ySdG_M64iBXVuzu1CT7b7b-KdbdPA@mail.gmail.com>
<CAKFQuwaEizi5bUD1kM-gn_0Lp-q=O3Axn267jekyehSJtkc=FA@mail.gmail.com>
<CANhcyEVB6XkVQ4YYN3x+P5NeG=tOWg-OWebGiqMp02XXMVUa6A@mail.gmail.com>
<CAKFQuwbx5QErvadzs5Nmj7A0LUyS63pnHxrop5FAmd58wcfn+w@mail.gmail.com>
<CALDaNm0MpQPoU+C-Jt6BDOfFSFoU=DsFaV-Ohs2nkGnGfLiCcQ@mail.gmail.com>
<CAFiTN-sYKNNW=8Z_qdqzyr9sA+-G-PpTSs5R1mVbPT6aKyEAqw@mail.gmail.com>
<CAKFQuwYJKiDV6QgLLD+FHZ2zCi7DWLo_xoqYutJQbtPX+DJxdQ@mail.gmail.com>
<CAJpy0uBDgwki1k5zPhTX5b6shG0TuHdnsceCG75+WVMN_CtsKA@mail.gmail.com>
<CALDaNm11LKbC2epEyHOvD6H_ONqLqhDQk7sXWwcneyhrTbFaTw@mail.gmail.com>
<CAJpy0uC5SPBD1hh0-rAuFVvVcyTk8xWt-y=qE6b8EEkjz=k=ng@mail.gmail.com>
<CAJpy0uAHr8=YUKSyEBs8G4P0zGGb6mcUr-YJdB56tdYMF9yoKA@mail.gmail.com>
<CANhcyEXBw0NeCmrbzSEQ3bBHzzEwvyLo-rOx0migTtfm-H4sNw@mail.gmail.com>
<CAA4eK1JcrqPi7Pa9eQqj-=NFPAdzMhfVD=1jbwRWXT6tGtRyEw@mail.gmail.com>
On Thu, 19 Feb 2026 at 16:06, Amit Kapila <[email protected]> wrote:
>
> On Thu, Feb 19, 2026 at 10:13 AM Shlok Kyal <[email protected]> wrote:
> >
> > Thanks for reviewing the patch. I have addressed the remaining
> > comments in the v46 patch..
> >
>
> Can we think of some ideas to split this patch? One possibility is
> that in the first patch we give an ERROR, if a non-root partitioned
> table is present in EXCEPT Clause. I see that a lot of code is written
> to handle partitions in EXCEPT clause. I feel such a split will make
> code easier to review and validate.
Split it accordingly.
> Few other comments:
> =================
> 1.
> if (stmt->for_all_tables)
> {
> + /* Process EXCEPT table list */
> + if (relations != NIL)
> + {
> + Assert(rels != NIL);
> + PublicationAddTables(puboid, rels, true, NULL);
> + }
> +
> /*
> * Invalidate relcache so that publication info is rebuilt. Sequences
> * publication doesn't require invalidation, as replica identity
> CacheInvalidateRelcacheAll();
>
> Here, the relations listed in the except table list will be
> invalidated twice, once inside
> PublicationAddTables->publication_add_relation, and second time via
> CacheInvalidateRelcacheAll. Can we avoid that by adding a parameter to
> PublicationAddTables?
Fixed this
> 2.
> - root_relids = GetPublicationRelations(pubform->oid,
> - PUBLICATION_PART_ROOT);
> + root_relids = GetIncludedRelationsByPub(pubform->oid,
> + PUBLICATION_PART_ROOT);
>
> To follow the previous function naming pattern, can we rename
> GetIncludedRelationsByPub to GetIncludedPublicationRelations? If we
> agree to this then rename the excluded version of the function as
> well.
Modified
> 3.
> +/*
> + * 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/SEQUENCES should use
> + * GetAllPublicationRelations() to obtain the complete set of tables/sequences
> + * covered by the publication.
> + */
> +List *
> +GetIncludedRelationsByPub(Oid pubid, PublicationPartOpt pub_partopt)
>
> This is equivalent to the existing function GetPublicationRelations()
> which has more precise comments atop. We can use the same comments
> unless there is any functionality difference.
Updated it
> 4.
> --- a/src/backend/catalog/pg_publication.c
> +++ b/src/backend/catalog/pg_publication.c
> @@ -29,6 +29,7 @@
> #include "catalog/pg_publication.h"
> #include "catalog/pg_publication_namespace.h"
> #include "catalog/pg_publication_rel.h"
> +#include "catalog/pg_subscription.h"
>
> It appears odd to include pg_subscription.h in the publication code.
> Is there a reason for the same? If not then avoid it.
This was required because we now started using GetPublicationsStr. I
have moved GetPublicationsStr to logical.c from pg_subscription which
is common to both pub and sub.
> Apart from above, a few cosmetic changes are attached.
Merged them.
The attached v47 version patch has the changes for the same.
Regards,
Vignesh
Attachments:
[application/octet-stream] v47-0001-Skip-publishing-the-tables-specified-in-EXCEPT-T.patch (81.7K, 2-v47-0001-Skip-publishing-the-tables-specified-in-EXCEPT-T.patch)
download | inline diff:
From d21932a5766f3c7f8e53312d27e5a29fa7f5514c Mon Sep 17 00:00:00 2001
From: Vignesh C <[email protected]>
Date: Thu, 19 Feb 2026 20:16:39 +0530
Subject: [PATCH v47 1/2] 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.
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.
---
doc/src/sgml/catalogs.sgml | 10 +
doc/src/sgml/logical-replication.sgml | 6 +-
doc/src/sgml/ref/create_publication.sgml | 61 +++-
doc/src/sgml/ref/psql-ref.sgml | 5 +-
src/backend/catalog/pg_publication.c | 197 ++++++++++---
src/backend/catalog/pg_subscription.c | 31 --
src/backend/commands/publicationcmds.c | 53 +++-
src/backend/commands/subscriptioncmds.c | 111 +++++++
src/backend/commands/tablecmds.c | 4 +-
src/backend/parser/gram.y | 41 ++-
src/backend/replication/logical/logical.c | 31 ++
src/backend/replication/logical/tablesync.c | 1 +
src/backend/replication/pgoutput/pgoutput.c | 47 ++-
src/backend/utils/cache/relcache.c | 21 +-
src/bin/pg_dump/pg_dump.c | 68 +++++
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 | 18 +-
src/include/catalog/pg_publication_rel.h | 1 +
src/include/catalog/pg_subscription.h | 3 -
src/include/nodes/parsenodes.h | 5 +-
src/include/replication/logical.h | 2 +
src/test/regress/expected/publication.out | 121 +++++++-
src/test/regress/sql/publication.sql | 59 +++-
src/test/subscription/meson.build | 1 +
.../t/037_rep_changes_except_table.pl | 276 ++++++++++++++++++
28 files changed, 1159 insertions(+), 144 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..600fed9f4c0 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -6572,6 +6572,16 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>prexcept</structfield> <type>bool</type>
+ </para>
+ <para>
+ True if the table 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>prqual</structfield> <type>pg_node_tree</type>
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index 5028fe9af09..bcb473c078b 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>, a table or set of 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..f7cf7237061 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,41 @@ 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, 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>
+ <para>
+ There can be a case where a subscription includes multiple publications.
+ In such a case, a table or partition that is included in one publication
+ and listed in the <literal>EXCEPT TABLE</literal> clause of another is
+ considered included for replication.
+ </para>
+ <para>
+ Subscribing to multiple publications with different
+ <literal>EXCEPT TABLE</literal> lists is currently not supported.
+ </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 +529,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 8b1d948ba05..1045bc6a02c 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..e3a68ffb6bb 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -32,6 +32,7 @@
#include "catalog/pg_type.h"
#include "commands/publicationcmds.h"
#include "funcapi.h"
+#include "replication/logical.h"
#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/catcache.h"
@@ -366,9 +367,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 +469,11 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri,
RelationGetRelationName(targetrel), pub->name)));
}
+ 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);
/* Validate and translate column names into a Bitmapset of attnums. */
@@ -482,6 +490,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)
@@ -530,17 +540,25 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri,
table_close(rel, RowExclusiveLock);
/*
- * Invalidate relcache so that publication info is rebuilt.
- *
- * For the partitioned tables, we must invalidate all partitions contained
- * in the respective partition hierarchies, not just the one explicitly
- * mentioned in the publication. This is required because we implicitly
- * publish the child tables when the parent table is published.
+ * No need to invalidate the tables listed in the EXCEPT clause, because
+ * in that case all tables are invalidated anyway.
*/
- relids = GetPubPartitionOptionRelations(relids, PUBLICATION_PART_ALL,
- relid);
+ if (!pri->except)
+ {
+ /*
+ * Invalidate relcache so that publication info is rebuilt.
+ *
+ * For the partitioned tables, we must invalidate all partitions
+ * contained in the respective partition hierarchies, not just the one
+ * explicitly mentioned in the publication. This is required because
+ * we implicitly publish the child tables when the parent table is
+ * published.
+ */
+ relids = GetPubPartitionOptionRelations(relids, PUBLICATION_PART_ALL,
+ relid);
- InvalidatePublicationRels(relids);
+ InvalidatePublicationRels(relids);
+ }
return myself;
}
@@ -749,38 +767,54 @@ 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.
+ *
+ * 'pubids' returns the Oids of the publications the relation is part of.
+ *
+ * '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;
+ List **target_list = pubrel->prexcept ? except_pubids : pubids;
- result = lappend_oid(result, pubid);
+ if (target_list)
+ *target_list = lappend_oid(*target_list, pubid);
+
+ if (!pubrel->prexcept)
+ 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 specified in the
+ * EXCEPT clause of 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 +839,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 +855,35 @@ GetPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt)
return result;
}
+/*
+ * Gets list of relation oids that are associated with a publication.
+ *
+ * This should only be used FOR TABLE publications, the FOR ALL TABLES/SEQUENCES
+ * should use GetAllPublicationRelations().
+ */
+List *
+GetIncludedPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt)
+{
+ Assert(!GetPublication(pubid)->alltables);
+
+ return get_publication_relations(pubid, pub_partopt, false);
+}
+
+/*
+ * Gets list of table oids that were specified in the except clause for a
+ * publication.
+ *
+ * This should only be used FOR TABLE publications, the FOR ALL TABLES/SEQUENCES
+ * should use GetAllPublicationRelations().
+ */
+List *
+GetExcludedPublicationTables(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.
*/
@@ -858,24 +923,35 @@ GetAllTablesPublications(void)
/*
* Gets list of all relations published by FOR ALL TABLES/SEQUENCES
- * publication(s).
+ * publication.
*
* If the publication publishes partition changes via their respective root
* 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 = GetExcludedPublicationTables(pubid, pubviaroot ?
+ PUBLICATION_PART_ROOT :
+ PUBLICATION_PART_LEAF);
+
classRel = table_open(RelationRelationId, AccessShareLock);
ScanKeyInit(&key[0],
@@ -891,7 +967,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 +989,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);
}
@@ -1116,6 +1194,51 @@ GetPublicationByName(const char *pubname, bool missing_ok)
return OidIsValid(oid) ? GetPublication(oid) : NULL;
}
+/*
+ * publication_has_any_except_table
+ *
+ * Returns true if the given publication OID has at least one entry in
+ * pg_publication_rel marked as an exception (prexcept = true).
+ */
+bool
+publication_has_any_except_table(Oid pubid)
+{
+ Relation pubrelsrel;
+ ScanKeyData scankey;
+ SysScanDesc scan;
+ HeapTuple tup;
+ bool found = false;
+
+ pubrelsrel = table_open(PublicationRelRelationId, AccessShareLock);
+
+ ScanKeyInit(&scankey,
+ Anum_pg_publication_rel_prpubid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(pubid));
+
+ scan = systable_beginscan(pubrelsrel,
+ PublicationRelPrpubidIndexId,
+ true, NULL, 1, &scankey);
+
+ /* We only need to find any occurrence of prexcept = true */
+ while (HeapTupleIsValid(tup = systable_getnext(scan)))
+ {
+ Form_pg_publication_rel pubrel;
+
+ pubrel = (Form_pg_publication_rel) GETSTRUCT(tup);
+ if (pubrel->prexcept)
+ {
+ found = true;
+ break;
+ }
+ }
+
+ systable_endscan(scan);
+ table_close(pubrelsrel, AccessShareLock);
+
+ return found;
+}
+
/*
* Get information of the tables in the given publication array.
*
@@ -1168,17 +1291,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 = GetIncludedPublicationRelations(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 +1490,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/catalog/pg_subscription.c b/src/backend/catalog/pg_subscription.c
index 2b103245290..fe468d61fc6 100644
--- a/src/backend/catalog/pg_subscription.c
+++ b/src/backend/catalog/pg_subscription.c
@@ -34,37 +34,6 @@
static List *textarray_to_stringlist(ArrayType *textarray);
-/*
- * Add a comma-separated list of publication names to the 'dest' string.
- */
-void
-GetPublicationsStr(List *publications, StringInfo dest, bool quote_literal)
-{
- ListCell *lc;
- bool first = true;
-
- Assert(publications != NIL);
-
- foreach(lc, publications)
- {
- char *pubname = strVal(lfirst(lc));
-
- if (first)
- first = false;
- else
- appendStringInfoString(dest, ", ");
-
- if (quote_literal)
- appendStringInfoString(dest, quote_literal_cstr(pubname));
- else
- {
- appendStringInfoChar(dest, '"');
- appendStringInfoString(dest, pubname);
- appendStringInfoChar(dest, '"');
- }
- }
-}
-
/*
* Fetch the subscription from the syscache.
*/
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index fc3a4c19e65..40772c67d75 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 = GetIncludedPublicationRelations(pubid,
+ PUBLICATION_PART_ALL);
schemarelids = GetAllSchemaPublicationRelations(pubid,
PUBLICATION_PART_ALL);
@@ -845,6 +850,7 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
AclResult aclresult;
List *relations = NIL;
List *schemaidlist = NIL;
+ List *rels = NIL;
/* must have CREATE privilege on database */
aclresult = object_aclcheck(DatabaseRelationId, MyDatabaseId, GetUserId(), ACL_CREATE);
@@ -929,8 +935,23 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
CommandCounterIncrement();
/* Associate objects with the publication. */
+ ObjectsInPublicationToOids(stmt->pubobjects, pstate, &relations,
+ &schemaidlist);
+ if (relations != NIL)
+ {
+ Assert(!stmt->for_all_sequences);
+ rels = OpenTableList(relations);
+ }
+
if (stmt->for_all_tables)
{
+ /* Process EXCEPT table list */
+ if (relations != NIL)
+ {
+ Assert(rels != NIL);
+ PublicationAddTables(puboid, rels, true, NULL);
+ }
+
/*
* Invalidate relcache so that publication info is rebuilt. Sequences
* publication doesn't require invalidation, as replica identity
@@ -940,9 +961,6 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
}
else if (!stmt->for_all_sequences)
{
- ObjectsInPublicationToOids(stmt->pubobjects, pstate, &relations,
- &schemaidlist);
-
/* FOR TABLES IN SCHEMA requires superuser */
if (schemaidlist != NIL && !superuser())
ereport(ERROR,
@@ -951,9 +969,6 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
if (relations != NIL)
{
- List *rels;
-
- rels = OpenTableList(relations);
TransformPubWhereClauses(rels, pstate->p_sourcetext,
publish_via_partition_root);
@@ -962,7 +977,6 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
publish_via_partition_root);
PublicationAddTables(puboid, rels, true, NULL);
- CloseTableList(rels);
}
if (schemaidlist != NIL)
@@ -976,6 +990,9 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
}
}
+ if (rels != NIL)
+ CloseTableList(rels);
+
table_close(rel, RowExclusiveLock);
InvokeObjectPostCreateHook(PublicationRelationId, puboid, 0);
@@ -1050,8 +1067,8 @@ AlterPublicationOptions(ParseState *pstate, AlterPublicationStmt *stmt,
LockDatabaseObject(PublicationRelationId, pubform->oid, 0,
AccessShareLock);
- root_relids = GetPublicationRelations(pubform->oid,
- PUBLICATION_PART_ROOT);
+ root_relids = GetIncludedPublicationRelations(pubform->oid,
+ PUBLICATION_PART_ROOT);
foreach(lc, root_relids)
{
@@ -1170,8 +1187,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 = GetIncludedPublicationRelations(pubform->oid,
+ PUBLICATION_PART_ALL);
else
{
/*
@@ -1256,8 +1273,8 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
PublicationDropTables(pubid, rels, false);
else /* AP_SetObjects */
{
- List *oldrelids = GetPublicationRelations(pubid,
- PUBLICATION_PART_ROOT);
+ List *oldrelids = GetIncludedPublicationRelations(pubid,
+ PUBLICATION_PART_ROOT);
List *delrels = NIL;
ListCell *oldlc;
@@ -1358,6 +1375,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 +1426,8 @@ AlterPublicationSchemas(AlterPublicationStmt *stmt,
ListCell *lc;
List *reloids;
- reloids = GetPublicationRelations(pubform->oid, PUBLICATION_PART_ROOT);
+ reloids = GetIncludedPublicationRelations(pubform->oid,
+ PUBLICATION_PART_ROOT);
foreach(lc, reloids)
{
@@ -1771,6 +1790,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 +1863,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/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c
index 0b3c8499b49..b0a53c17dd0 100644
--- a/src/backend/commands/subscriptioncmds.c
+++ b/src/backend/commands/subscriptioncmds.c
@@ -37,6 +37,7 @@
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "pgstat.h"
+#include "replication/logical.h"
#include "replication/logicallauncher.h"
#include "replication/logicalworker.h"
#include "replication/origin.h"
@@ -549,6 +550,113 @@ check_publications(WalReceiverConn *wrconn, List *publications)
}
}
+/*
+ * ERROR if multiple publications with EXCEPT clauses are present.
+ *
+ * Logical replication does not support subscribing to more than one
+ * publication that defines an exclusion list. Allowing this would
+ * create ambiguous and unpredictable replication behavior.
+ *
+ * The ambiguity arises when publications differ in their exclusions
+ * and in the setting of 'publish_via_partition_root'. In such cases,
+ * the effective publication set cannot be determined consistently:
+ *
+ * - Tables excluded in one publication may be implicitly included
+ * in another.
+ * - Conflicting 'publish_via_partition_root' settings alter how
+ * partitions are represented and deduplicated.
+ *
+ * To avoid these conflicts, a subscription may include at most one
+ * 'FOR ALL TABLES' publication that specifies an EXCEPT clause.
+ *
+ * Example:
+ *
+ * Partitioned table: tab_root
+ * Partitions: part1, part2, part3
+ *
+ * pub1:
+ * FOR ALL TABLES EXCEPT (part1, part2)
+ * WITH (publish_via_partition_root = true)
+ *
+ * pub2:
+ * FOR ALL TABLES EXCEPT (part3)
+ * WITH (publish_via_partition_root = false)
+ *
+ * Subscribing to both pub1 and pub2 is invalid because:
+ * - pub1 excludes part1 and part2, publishing remaining partitions
+ * via the root.
+ * - pub2 excludes part3, publishing other partitions individually.
+ *
+ * The resulting publication set is ambiguous and provides no clear
+ * benefit. Unless all publications exclude the same tables, combining
+ * them introduces complex and conflicting partition resolution rules.
+ */
+static void
+check_publications_except_list(WalReceiverConn *wrconn, List *publications)
+{
+ List *except_publications = NIL;
+ WalRcvExecResult *res;
+ StringInfoData cmd;
+ StringInfoData pubnames;
+ TupleTableSlot *slot;
+ Oid tableRow[1] = {TEXTOID};
+
+ if (list_length(publications) <= 1)
+ return;
+
+ initStringInfo(&cmd);
+ appendStringInfoString(&cmd,
+ "SELECT p.pubname\n"
+ " FROM pg_catalog.pg_publication p\n"
+ " WHERE p.pubname IN (");
+
+ GetPublicationsStr(publications, &cmd, true);
+
+ appendStringInfoString(&cmd,
+ ")\n"
+ " AND EXISTS (SELECT 1\n"
+ " FROM pg_catalog.pg_publication_rel pr\n"
+ " WHERE pr.prpubid = p.oid\n"
+ " AND pr.prexcept IS TRUE)");
+
+ res = walrcv_exec(wrconn, cmd.data, 1, tableRow);
+ pfree(cmd.data);
+
+ if (res->status != WALRCV_OK_TUPLES)
+ ereport(ERROR,
+ errmsg("could not receive list of publications from the publisher: %s",
+ res->err));
+
+ slot = MakeSingleTupleTableSlot(res->tupledesc, &TTSOpsMinimalTuple);
+ while (tuplestore_gettupleslot(res->tuplestore, true, false, slot))
+ {
+ char *pubname;
+ bool isnull;
+
+ pubname = TextDatumGetCString(slot_getattr(slot, 1, &isnull));
+ Assert(!isnull);
+
+ except_publications = lappend(except_publications, makeString(pubname));
+ ExecClearTuple(slot);
+ }
+
+ ExecDropSingleTupleTableSlot(slot);
+
+ walrcv_clear_result(res);
+
+ if (list_length(except_publications) <= 1)
+ return;
+
+ initStringInfo(&pubnames);
+ GetPublicationsStr(except_publications, &pubnames, false);
+
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot combine publications that define EXCEPT TABLE clauses"),
+ errdetail("The following publications define EXCEPT TABLE clauses: %s.",
+ pubnames.data));
+}
+
/*
* Auxiliary function to build a text array out of a list of String nodes.
*/
@@ -795,6 +903,7 @@ CreateSubscription(ParseState *pstate, CreateSubscriptionStmt *stmt,
char relation_state;
check_publications(wrconn, publications);
+ check_publications_except_list(wrconn, publications);
check_publications_origin_tables(wrconn, publications,
opts.copy_data,
opts.retaindeadtuples, opts.origin,
@@ -959,6 +1068,8 @@ AlterSubscription_refresh(Subscription *sub, bool copy_data,
if (validate_publications)
check_publications(wrconn, validate_publications);
+ check_publications_except_list(wrconn, sub->publications);
+
/* Get the relation list from publisher. */
pubrels = fetch_relation_list(wrconn, sub->publications);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 2f5b7007ff9..581da6b7b20 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 c567252acc4..de4374e74d5 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;
@@ -10933,11 +10937,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 +10966,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); }
+ ;
/*****************************************************************************
*
@@ -19812,8 +19841,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;
@@ -19833,6 +19863,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/logical/logical.c b/src/backend/replication/logical/logical.c
index 603a2b94d05..a8b16310fca 100644
--- a/src/backend/replication/logical/logical.c
+++ b/src/backend/replication/logical/logical.c
@@ -2210,3 +2210,34 @@ LogicalSlotAdvanceAndCheckSnapState(XLogRecPtr moveto,
return retlsn;
}
+
+/*
+ * Add a comma-separated list of publication names to the 'dest' string.
+ */
+void
+GetPublicationsStr(List *publications, StringInfo dest, bool quote_literal)
+{
+ ListCell *lc;
+ bool first = true;
+
+ Assert(publications != NIL);
+
+ foreach(lc, publications)
+ {
+ char *pubname = strVal(lfirst(lc));
+
+ if (first)
+ first = false;
+ else
+ appendStringInfoString(dest, ", ");
+
+ if (quote_literal)
+ appendStringInfoString(dest, quote_literal_cstr(pubname));
+ else
+ {
+ appendStringInfoChar(dest, '"');
+ appendStringInfoString(dest, pubname);
+ appendStringInfoChar(dest, '"');
+ }
+ }
+}
diff --git a/src/backend/replication/logical/tablesync.c b/src/backend/replication/logical/tablesync.c
index 19a3c21a863..eee909d912b 100644
--- a/src/backend/replication/logical/tablesync.c
+++ b/src/backend/replication/logical/tablesync.c
@@ -104,6 +104,7 @@
#include "nodes/makefuncs.h"
#include "parser/parse_relation.h"
#include "pgstat.h"
+#include "replication/logical.h"
#include "replication/logicallauncher.h"
#include "replication/logicalrelation.h"
#include "replication/logicalworker.h"
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 7a49185d29d..dd38eef9303 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -1797,12 +1797,18 @@ pgoutput_shutdown(LogicalDecodingContext *ctx)
* to silently continue the replication in the absence of a missing publication.
* This is required because we allow the users to create publications after they
* have specified the required publications at the time of replication start.
+ *
+ * We also enforce that no more than one publication in the list may contain
+ * an EXCEPT TABLE clause. Using multiple publications with EXCEPT TABLE clause
+ * is currently unsupported to prevent non-deterministic filtering behavior
+ * across overlapping publication sets.
*/
static List *
LoadPublications(List *pubnames)
{
List *result = NIL;
ListCell *lc;
+ List *pubnames_with_except = NIL;
foreach(lc, pubnames)
{
@@ -1810,7 +1816,13 @@ LoadPublications(List *pubnames)
Publication *pub = GetPublicationByName(pubname, true);
if (pub)
+ {
result = lappend(result, pub);
+
+ /* Check if this publication has an EXCEPT TABLE list. */
+ if (publication_has_any_except_table(pub->oid))
+ pubnames_with_except = lappend(pubnames_with_except, pstrdup(pubname));
+ }
else
ereport(WARNING,
errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
@@ -1819,6 +1831,25 @@ LoadPublications(List *pubnames)
errhint("Create the publication if it does not exist."));
}
+ /*
+ * If more than one publication has an EXCEPT list, throw an error listing
+ * all the problematic publications.
+ */
+ if (list_length(pubnames_with_except) > 1)
+ {
+ StringInfoData pub_names_str;
+
+ initStringInfo(&pub_names_str);
+ GetPublicationsStr(pubnames_with_except, &pub_names_str, true);
+
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot combine publications that define EXCEPT TABLE clauses"),
+ errdetail("The following publications define EXCEPT TABLE clauses: %s.",
+ pub_names_str.data));
+ }
+
+ list_free_deep(pubnames_with_except);
return result;
}
@@ -2089,7 +2120,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
@@ -2104,6 +2135,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)
{
@@ -2206,14 +2239,22 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
*/
if (pub->alltables)
{
- publish = true;
- if (pub->pubviaroot && am_partition)
+ List *exceptpubids = NIL;
+
+ if (am_partition && pub->pubviaroot)
{
List *ancestors = get_partition_ancestors(relid);
pub_relid = llast_oid(ancestors);
ancestor_level = list_length(ancestors);
}
+
+ GetRelationPublications(pub_relid, NULL, &exceptpubids);
+
+ if (!list_member_oid(exceptpubids, pub->oid))
+ publish = true;
+
+ list_free(exceptpubids);
}
if (!publish)
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 49598304335..1112c4e124d 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -4623,9 +4623,55 @@ 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);
+
+ for (int j = 0; j < ntbls; j++)
+ {
+ Oid prrelid;
+ TableInfo *tbinfo;
+
+ prrelid = atooid(PQgetvalue(res_tbls, j, 0));
+
+ tbinfo = findTableByOid(prrelid);
+
+ if (tbinfo != NULL)
+ simple_ptr_list_append(&pubinfo[i].except_tables, tbinfo);
+ }
+
+ PQclear(res_tbls);
+ }
}
cleanup:
@@ -4665,7 +4711,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_except = 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_except == 1)
+ appendPQExpBufferStr(query, " EXCEPT TABLE (");
+ else
+ appendPQExpBufferStr(query, ", ");
+ appendPQExpBuffer(query, "ONLY %s", fmtQualifiedDumpable(tbinfo));
+ }
+ if (n_except > 0)
+ appendPQExpBufferStr(query, ")");
+ }
else if (pubinfo->puballsequences)
appendPQExpBufferStr(query, " FOR ALL SEQUENCES");
@@ -4845,6 +4909,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, "
@@ -4857,6 +4922,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..64b1b6efebe 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 where the table is in the EXCEPT clause */
+ 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 tables in the EXCEPT clause for this 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..8d695276837 100644
--- a/src/include/catalog/pg_publication.h
+++ b/src/include/catalog/pg_publication.h
@@ -146,16 +146,18 @@ 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(),
- * which allows callers to specify which partitions of partitioned tables
- * mentioned in the publication they expect to see.
+ * Expected values for pub_partopt parameter of
+ * GetIncludedPublicationRelations(), which allows callers to specify which
+ * partitions of partitioned tables mentioned in the publication they expect to
+ * see.
*
* ROOT: only the table explicitly mentioned in the publication
* LEAF: only leaf partitions in given tree
@@ -168,9 +170,12 @@ typedef enum PublicationPartOpt
PUBLICATION_PART_ALL,
} PublicationPartOpt;
-extern List *GetPublicationRelations(Oid pubid, PublicationPartOpt pub_partopt);
+extern List *GetIncludedPublicationRelations(Oid pubid,
+ PublicationPartOpt pub_partopt);
+extern List *GetExcludedPublicationTables(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,
@@ -185,6 +190,7 @@ extern Oid GetTopMostAncestorInPublication(Oid puboid, List *ancestors,
extern bool is_publishable_relation(Relation rel);
extern bool is_schema_publication(Oid pubid);
+extern bool publication_has_any_except_table(Oid pubid);
extern bool check_and_fetch_column_list(Publication *pub, Oid relid,
MemoryContext mcxt, Bitmapset **cols);
extern ObjectAddress publication_add_relation(Oid pubid, PublicationRelInfo *pri,
diff --git a/src/include/catalog/pg_publication_rel.h b/src/include/catalog/pg_publication_rel.h
index 3a8790e8482..802a8f576c0 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); /* relation is not published */
#ifdef CATALOG_VARLEN /* variable-length fields start here */
pg_node_tree prqual; /* qualifications */
diff --git a/src/include/catalog/pg_subscription.h b/src/include/catalog/pg_subscription.h
index f3571d2bfcf..928e5af8769 100644
--- a/src/include/catalog/pg_subscription.h
+++ b/src/include/catalog/pg_subscription.h
@@ -205,7 +205,4 @@ extern void DisableSubscription(Oid subid);
extern int CountDBSubscriptions(Oid dbid);
-extern void GetPublicationsStr(List *publications, StringInfo dest,
- bool quote_literal);
-
#endif /* PG_SUBSCRIPTION_H */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 0aec49bdd22..b1de35dc3e1 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -4299,9 +4299,10 @@ typedef struct AlterTSConfigurationStmt
typedef struct PublicationTable
{
NodeTag type;
- RangeVar *relation; /* relation to be published */
+ RangeVar *relation; /* publication relation */
Node *whereClause; /* qualifications */
List *columns; /* List of columns in a publication table */
+ bool except; /* True if listed in the EXCEPT clause */
} PublicationTable;
/*
@@ -4310,6 +4311,7 @@ typedef struct PublicationTable
typedef enum PublicationObjSpecType
{
PUBLICATIONOBJ_TABLE, /* A table */
+ PUBLICATIONOBJ_EXCEPT_TABLE, /* A table in the EXCEPT clause */
PUBLICATIONOBJ_TABLES_IN_SCHEMA, /* All tables in schema */
PUBLICATIONOBJ_TABLES_IN_CUR_SCHEMA, /* All tables in first element of
* search_path */
@@ -4338,6 +4340,7 @@ typedef struct PublicationAllObjSpec
{
NodeTag type;
PublicationAllObjType pubobjtype; /* type of this publication object */
+ List *except_tables; /* tables specified in the EXCEPT clause */
ParseLoc location; /* token location, or -1 if unknown */
} PublicationAllObjSpec;
diff --git a/src/include/replication/logical.h b/src/include/replication/logical.h
index bc9d4ece672..1fece74398d 100644
--- a/src/include/replication/logical.h
+++ b/src/include/replication/logical.h
@@ -152,5 +152,7 @@ extern XLogRecPtr LogicalReplicationSlotCheckPendingWal(XLogRecPtr end_of_wal,
XLogRecPtr scan_cutoff_lsn);
extern XLogRecPtr LogicalSlotAdvanceAndCheckSnapState(XLogRecPtr moveto,
bool *found_consistent_snapshot);
+extern void GetPublicationsStr(List *publications, StringInfo dest,
+ bool quote_literal);
#endif
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 7fb49aaf29b..1e5819c6de2 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -213,33 +213,132 @@ 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);
+---------------------------------------------
+-- EXCEPT TABLE tests for normal tables
+---------------------------------------------
SET client_min_messages = 'ERROR';
-CREATE PUBLICATION testpub3 FOR TABLE testpub_tbl3;
-CREATE PUBLICATION testpub4 FOR TABLE ONLY testpub_tbl3;
+-- Specify tablelist in the EXCEPT clause of a FOR ALL TABLES publication
+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"
+
+-- Specify table in the EXCEPT clause of a FOR ALL TABLES publication
+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 publications where it is listed
+-- in the EXCEPT clause
+\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;
+---------------------------------------------
+-- Tests for inherited tables, and
+-- EXCEPT TABLE tests for inherited tables
+---------------------------------------------
+SET client_min_messages = 'ERROR';
+CREATE TABLE testpub_tbl_parent (a int);
+CREATE TABLE testpub_tbl_child (b text) INHERITS (testpub_tbl_parent);
+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;
+-- List the parent table in the EXCEPT clause (without ONLY or '*')
+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 '*': list the table and all its descendants in the EXCEPT clause
+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: list the table in the EXCEPT clause, but not its 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;
+---------------------------------------------
+-- EXCEPT TABLE tests for partitioned tables
+---------------------------------------------
+SET client_min_messages = 'ERROR';
+CREATE TABLE testpub_root(a int) PARTITION BY RANGE(a);
+CREATE TABLE testpub_part1 PARTITION OF testpub_root FOR VALUES FROM (0) TO (100);
+CREATE TABLE testpub_part2 PARTITION OF testpub_root FOR VALUES FROM (100) TO (200);
+CREATE PUBLICATION testpub8 FOR ALL TABLES EXCEPT TABLE (testpub_root);
+\dRp+ testpub8;
+ Publication testpub8
+ 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_root"
+
+CREATE PUBLICATION testpub9 FOR ALL TABLES EXCEPT TABLE (testpub_part1);
+ERROR: partition "testpub_part1" cannot be excluded using EXCEPT TABLE
+RESET client_min_messages;
+DROP TABLE testpub_root, testpub_part1, testpub_part2;
+DROP PUBLICATION testpub8;
--- 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..4de0176bc25 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -105,20 +105,63 @@ SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_forall
\d+ testpub_tbl2
\dRp+ testpub_foralltables
+---------------------------------------------
+-- EXCEPT TABLE tests for normal tables
+---------------------------------------------
+SET client_min_messages = 'ERROR';
+-- Specify tablelist in the EXCEPT clause of a FOR ALL TABLES publication
+CREATE PUBLICATION testpub_foralltables_excepttable FOR ALL TABLES EXCEPT TABLE (testpub_tbl1, testpub_tbl2);
+\dRp+ testpub_foralltables_excepttable
+-- Specify table in the EXCEPT clause of a FOR ALL TABLES publication
+CREATE PUBLICATION testpub_foralltables_excepttable1 FOR ALL TABLES EXCEPT (testpub_tbl1);
+\dRp+ testpub_foralltables_excepttable1
+-- Check that the table description shows the publications where it is listed
+-- in the EXCEPT clause
+\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);
+---------------------------------------------
+-- Tests for inherited tables, and
+-- EXCEPT TABLE tests for inherited tables
+---------------------------------------------
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 TABLE testpub_tbl_parent (a int);
+CREATE TABLE testpub_tbl_child (b text) INHERITS (testpub_tbl_parent);
+CREATE PUBLICATION testpub3 FOR TABLE testpub_tbl_parent;
\dRp+ testpub3
+CREATE PUBLICATION testpub4 FOR TABLE ONLY testpub_tbl_parent;
\dRp+ testpub4
+-- List the parent table in the EXCEPT clause (without ONLY or '*')
+CREATE PUBLICATION testpub5 FOR ALL TABLES EXCEPT TABLE (testpub_tbl_parent);
+\dRp+ testpub5
+-- EXCEPT with '*': list the table and all its descendants in the EXCEPT clause
+CREATE PUBLICATION testpub6 FOR ALL TABLES EXCEPT TABLE (testpub_tbl_parent *);
+\dRp+ testpub6
+-- EXCEPT with ONLY: list the table in the EXCEPT clause, but not its 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;
+
+---------------------------------------------
+-- EXCEPT TABLE tests for partitioned tables
+---------------------------------------------
+SET client_min_messages = 'ERROR';
+CREATE TABLE testpub_root(a int) PARTITION BY RANGE(a);
+CREATE TABLE testpub_part1 PARTITION OF testpub_root FOR VALUES FROM (0) TO (100);
+CREATE TABLE testpub_part2 PARTITION OF testpub_root FOR VALUES FROM (100) TO (200);
+CREATE PUBLICATION testpub8 FOR ALL TABLES EXCEPT TABLE (testpub_root);
+\dRp+ testpub8;
+CREATE PUBLICATION testpub9 FOR ALL TABLES EXCEPT TABLE (testpub_part1);
+
+RESET client_min_messages;
+DROP TABLE testpub_root, testpub_part1, testpub_part2;
+DROP PUBLICATION testpub8;
--- 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..361ce1ac996
--- /dev/null
+++ b/src/test/subscription/t/037_rep_changes_except_table.pl
@@ -0,0 +1,276 @@
+
+# Copyright (c) 2021-2026, 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;
+
+my $publisher_connstr = $node_publisher->connstr . ' dbname=postgres';
+
+# Initialize subscriber node
+my $node_subscriber = PostgreSQL::Test::Cluster->new('subscriber');
+$node_subscriber->init(allows_streaming => 'logical');
+$node_subscriber->start;
+
+my $result;
+
+sub test_except_root_partition
+{
+ my $pubviaroot = @_;
+
+ # When the root partitioned table is listed in the EXCEPT clause,
+ # all its partitions are not published.
+ $node_publisher->safe_psql(
+ 'postgres', qq(
+ CREATE PUBLICATION tap_pub_part FOR ALL TABLES EXCEPT TABLE (sch1.t1) WITH (publish_via_partition_root = $pubviaroot);
+ 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',
+ "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 into the partitioned table is not published when
+ # it is in the EXCEPT clause.
+ $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 root table');
+
+ $result =
+ $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part1");
+ 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 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_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_part");
+ $node_publisher->safe_psql('postgres', "DROP PUBLICATION tap_pub_part;");
+}
+
+# ============================================
+# 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, and create a logical replication slot to help with
+# later tests.
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION tap_pub_schema FOR ALL TABLES EXCEPT TABLE (sch1.tab1)"
+);
+
+$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 the tables specified in EXCEPT clause
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM sch1.tab1");
+is($result, qq(0),
+ 'check there is no initial data copied for the tables specified in the except clause'
+);
+
+# Insert some data into the table listed in the EXCEPT clause
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO sch1.tab1 VALUES(generate_series(11,20))");
+
+# Verify that data inserted into a table listed in the EXCEPT clause 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),
+ 'verify no changes for table listed in the EXCEPT clause are present in the replication slot'
+);
+
+# Verify that data inserted into a table listed in the EXCEPT clause is not
+# replicated.
+$node_publisher->wait_for_catchup('tap_sub_schema');
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM sch1.tab1");
+is($result, qq(0), 'check replicated inserts on subscriber');
+
+# cleanup
+$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_schema");
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ DROP PUBLICATION tap_pub_schema;
+ TRUNCATE TABLE sch1.tab1;
+));
+$node_subscriber->safe_psql('postgres', "TRUNCATE TABLE sch1.tab1");
+
+# ============================================
+# 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 (100);
+ CREATE TABLE sch1.part2 PARTITION OF sch1.t1 FOR VALUES FROM (100) TO (200) PARTITION BY RANGE(a);;
+ CREATE TABLE sch1.part2_1 PARTITION OF sch1.part2 FOR VALUES FROM (100) TO (150);
+ CREATE TABLE sch1.part2_2 PARTITION OF sch1.part2 FOR VALUES FROM (150) TO (200);
+));
+
+$node_subscriber->safe_psql(
+ 'postgres', qq(
+ 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);
+));
+
+test_except_root_partition('false');
+test_except_root_partition('true');
+
+$node_publisher->safe_psql('postgres',
+ "SELECT slot_name FROM pg_replication_slot_advance('test_slot', pg_current_wal_lsn());"
+);
+
+# ============================================
+# Test when a subscription is subscribing to multiple publications
+# ============================================
+# ERROR if subscribing to multiple publications having EXCEPT TABLE.
+my ($stdout, $stderr);
+
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ CREATE PUBLICATION tap_pub1 FOR ALL TABLES EXCEPT (sch1.tab1);
+ CREATE PUBLICATION tap_pub2 FOR ALL TABLES EXCEPT (sch1.t1);
+));
+
+($result, $stdout, $stderr) = $node_subscriber->psql('postgres',
+ "CREATE SUBSCRIPTION tap_sub CONNECTION '$publisher_connstr' PUBLICATION tap_pub1, tap_pub2"
+);
+like(
+ $stderr,
+ qr/ERROR: cannot combine publications that define EXCEPT TABLE clauses.*
+.*DETAIL:.*The following publications define EXCEPT TABLE clauses: "tap_pub1", "tap_pub2"./,
+ 'subscription with multiple EXCEPT TABLE publication');
+
+$node_publisher->safe_psql('postgres', 'DROP PUBLICATION tap_pub2');
+
+# OK when a table is excluded by pub1 EXCEPT TABLE, but it is included by pub2
+# FOR TABLE
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ CREATE PUBLICATION tap_pub2 FOR TABLE sch1.tab1;
+ INSERT INTO sch1.tab1 VALUES(1);
+));
+$node_subscriber->psql('postgres',
+ "CREATE SUBSCRIPTION tap_sub CONNECTION '$publisher_connstr' PUBLICATION tap_pub1, tap_pub2"
+);
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub');
+
+$node_publisher->safe_psql('postgres', qq(INSERT INTO sch1.tab1 VALUES(2)));
+$node_publisher->wait_for_catchup('tap_sub');
+
+$result = $node_publisher->safe_psql('postgres',
+ "SELECT * FROM sch1.tab1 ORDER BY a");
+is( $result, qq(1
+2),
+ "check replication of a table in the EXCEPT clause of one publication but included by another"
+);
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ DROP PUBLICATION tap_pub2;
+ TRUNCATE sch1.tab1;
+));
+$node_subscriber->safe_psql('postgres', qq(TRUNCATE sch1.tab1));
+
+# OK when a table is excluded by pub1 EXCEPT TABLE, but it is included by pub2
+# FOR ALL TABLES
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ CREATE PUBLICATION tap_pub2 FOR ALL TABLES;
+ INSERT INTO sch1.tab1 VALUES(1);
+));
+$node_subscriber->psql('postgres',
+ "CREATE SUBSCRIPTION tap_sub CONNECTION '$publisher_connstr' PUBLICATION tap_pub1, tap_pub2"
+);
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub');
+
+$node_publisher->safe_psql('postgres', qq(INSERT INTO sch1.tab1 VALUES(2)));
+$node_publisher->wait_for_catchup('tap_sub');
+
+$result = $node_publisher->safe_psql('postgres',
+ "SELECT * FROM sch1.tab1 ORDER BY a");
+is( $result, qq(1
+2),
+ "check replication of a table in the EXCEPT clause of one publication but included by another"
+);
+
+# ERROR if ALTER SUBSCRIPTION ... REFRESH PUBLICATION causes the
+# subscription to end up with multiple publications having EXCEPT TABLE.
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ DROP PUBLICATION tap_pub2;
+ CREATE PUBLICATION tap_pub2 FOR ALL TABLES EXCEPT (sch1.t1);
+));
+
+($result, $stdout, $stderr) = $node_subscriber->psql('postgres',
+ "ALTER SUBSCRIPTION tap_sub REFRESH PUBLICATION");
+like(
+ $stderr,
+ qr/ERROR: cannot combine publications that define EXCEPT TABLE clauses.*
+.*DETAIL:.*The following publications define EXCEPT TABLE clauses: "tap_pub1", "tap_pub2"./,
+ 'subscription with multiple EXCEPT TABLE publication');
+
+$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION tap_sub');
+$node_publisher->safe_psql('postgres', 'DROP PUBLICATION tap_pub1');
+$node_publisher->safe_psql('postgres', 'DROP PUBLICATION tap_pub2');
+
+$node_publisher->stop('fast');
+
+done_testing();
--
2.43.0
[application/octet-stream] v47-0002-Support-specifying-partition-tables-in-EXCEPT-cl.patch (41.2K, 3-v47-0002-Support-specifying-partition-tables-in-EXCEPT-cl.patch)
download | inline diff:
From 6f81b16a09a604d7293edea5977884a04eb26d50 Mon Sep 17 00:00:00 2001
From: Vignesh C <[email protected]>
Date: Fri, 20 Feb 2026 13:34:17 +0530
Subject: [PATCH v47 2/2] Support specifying partition tables in EXCEPT clause
Allow partitioned tables and individual partitions to be explicitly
specified in the EXCEPT clause of a publication.
Adjust logical replication so that both initial data synchronization and
ongoing change replication correctly honor exclusions involving
partitioned tables.
On the subscriber side, extend fetch_remote_table_info() to compute the
effective set of relations for the initial COPY. If exclusions affect a
partitioned table, the root relation cannot always be used directly.
Instead, derive the list of non-excluded leaf partitions and construct the
COPY query using UNION ALL over those partitions. If no relevant
exclusions are present, retain the existing behavior and copy from the
root relation as before.
---
doc/src/sgml/ref/create_publication.sgml | 8 +-
src/backend/catalog/pg_publication.c | 447 +++++++++++++++++-
src/backend/commands/subscriptioncmds.c | 2 +
src/backend/replication/logical/tablesync.c | 148 +++++-
src/backend/replication/pgoutput/pgoutput.c | 156 +++++-
src/include/catalog/pg_proc.dat | 7 +
src/include/catalog/pg_publication.h | 1 +
src/include/replication/worker_internal.h | 6 +
src/test/regress/expected/publication.out | 11 +-
src/test/regress/sql/publication.sql | 3 +-
.../t/037_rep_changes_except_table.pl | 90 +++-
src/tools/pgindent/typedefs.list | 1 +
12 files changed, 847 insertions(+), 33 deletions(-)
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index f7cf7237061..436e7d694a3 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -205,11 +205,9 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
tables are excluded.
</para>
<para>
- 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.
+ For partitioned tables, if a table is specified in EXCEPT TABLE, that
+ table and all of its partitions (that is, the entire partition subtree
+ rooted at that table) are excluded from the publication.
</para>
<para>
There can be a case where a subscription includes multiple publications.
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index e3a68ffb6bb..1fdafafb558 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -469,11 +469,6 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri,
RelationGetRelationName(targetrel), pub->name)));
}
- 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);
/* Validate and translate column names into a Bitmapset of attnums. */
@@ -921,6 +916,45 @@ GetAllTablesPublications(void)
return result;
}
+/*
+ * Returns true if the given partitioned table is effectively excluded
+ * by the EXCEPT list.
+ *
+ * A relation is considered excluded if:
+ * 1) It is explicitly present in exceptlist, or
+ * 2) All of its leaf partitions are present in exceptlist.
+ */
+static bool
+relation_is_effectively_excluded(Oid relid, List *exceptlist)
+{
+ List *leaftables = NIL;
+
+ if (exceptlist == NIL)
+ return false;
+
+ /* Explicitly excluded */
+ if (list_member_oid(exceptlist, relid))
+ return true;
+
+ /* Get all leaf partitions of relid */
+ leaftables = GetPubPartitionOptionRelations(leaftables,
+ PUBLICATION_PART_LEAF,
+ relid);
+
+ /*
+ * If any leaf is NOT present in exceptlist, then the relation is not
+ * fully excluded.
+ */
+ foreach_oid(leafrelid, leaftables)
+ {
+ if (!list_member_oid(exceptlist, leafrelid))
+ return false;
+ }
+
+ /* All leaves are excluded */
+ return true;
+}
+
/*
* Gets list of all relations published by FOR ALL TABLES/SEQUENCES
* publication.
@@ -949,7 +983,7 @@ GetAllPublicationRelations(Publication *pub, char relkind)
if (relkind == RELKIND_RELATION)
exceptlist = GetExcludedPublicationTables(pubid, pubviaroot ?
- PUBLICATION_PART_ROOT :
+ PUBLICATION_PART_ALL :
PUBLICATION_PART_LEAF);
classRel = table_open(RelationRelationId, AccessShareLock);
@@ -990,7 +1024,7 @@ GetAllPublicationRelations(Publication *pub, char relkind)
if (is_publishable_class(relid, relForm) &&
!relForm->relispartition &&
- !list_member_oid(exceptlist, relid))
+ !relation_is_effectively_excluded(relid, exceptlist))
result = lappend_oid(result, relid);
}
@@ -1239,6 +1273,405 @@ publication_has_any_except_table(Oid pubid)
return found;
}
+/* is_relid_excepted
+ *
+ * Check if the relation 'relid' is explicitly specified in the EXCEPT clause
+ * of the given publication.
+ */
+bool
+is_relid_excepted(Oid relid, Oid pubid)
+{
+ HeapTuple tup;
+ bool result = false;
+
+ tup = SearchSysCache2(PUBLICATIONRELMAP, ObjectIdGetDatum(relid),
+ ObjectIdGetDatum(pubid));
+ if (HeapTupleIsValid(tup))
+ {
+ Form_pg_publication_rel prform;
+
+ prform = (Form_pg_publication_rel) GETSTRUCT(tup);
+ result = prform->prexcept;
+
+ ReleaseSysCache(tup);
+ }
+ return result;
+}
+
+/*
+ * is_relid_or_ancestor_excepted
+ *
+ * Check if the relation 'relid' or any of its partition ancestors are
+ * explicitly specified in the EXCEPT clause of the given publication.
+ */
+static bool
+is_relid_or_ancestor_excepted(Oid relid, Oid pubid)
+{
+ List *ancestors;
+ ListCell *lc;
+ bool in_except = false;
+
+ /* Check the relation itself first */
+ if (is_relid_excepted(relid, pubid))
+ return true;
+
+ /* Check the inheritance chain */
+ ancestors = get_partition_ancestors(relid);
+
+ foreach(lc, ancestors)
+ {
+ Oid ancestor = lfirst_oid(lc);
+
+ if (is_relid_excepted(ancestor, pubid))
+ {
+ in_except = true;
+ break;
+ }
+ }
+
+ list_free(ancestors);
+
+ return in_except;
+}
+
+/*
+ * is_relid_published
+ *
+ * Check whether a given table or its schema is included in the specified
+ * publication.
+ */
+static bool
+is_relid_published(Oid relid, Oid pubid)
+{
+ HeapTuple tup;
+
+ tup = SearchSysCache2(PUBLICATIONRELMAP, ObjectIdGetDatum(relid),
+ ObjectIdGetDatum(pubid));
+ if (HeapTupleIsValid(tup))
+ {
+ bool published = false;
+ Form_pg_publication_rel prform;
+
+ prform = (Form_pg_publication_rel) GETSTRUCT(tup);
+ published = !prform->prexcept;
+
+ ReleaseSysCache(tup);
+
+ if (published)
+ return true;
+ }
+
+ return SearchSysCacheExists2(PUBLICATIONNAMESPACEMAP,
+ ObjectIdGetDatum(get_rel_namespace(relid)),
+ ObjectIdGetDatum(pubid));
+}
+
+/*
+ * is_relid_or_ancestor_published
+ *
+ * Check whether a given table or its schema or any of its partition ancestors,
+ * or its schema included in the specified publication
+ */
+static bool
+is_relid_or_ancestor_published(Oid relid, Oid pubid)
+{
+ if (is_relid_published(relid, pubid))
+ return true;
+ else
+ {
+ List *ancestors = get_partition_ancestors(relid);
+
+ foreach_oid(anc_oid, ancestors)
+ {
+ if (is_relid_published(anc_oid, pubid))
+ return true;
+ }
+ }
+
+ return false;
+}
+
+/*
+ * pg_get_publication_effective_tables
+ *
+ * Given a root partitioned table and a list of publications, calculate the set
+ * of relations that are effectively published. This is necessary for
+ * "FOR ALL TABLES" publications that use "EXCEPT TABLE" filters.
+ *
+ * The function returns a minimal set of relations that collectively
+ * include all non-excluded leaf partitions in the partition hierarchy.
+ */
+Datum
+pg_get_publication_effective_tables(PG_FUNCTION_ARGS)
+{
+ FuncCallContext *funcctx;
+ List *results;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ Oid root_relid = PG_GETARG_OID(0);
+ ArrayType *pub_names_array = PG_GETARG_ARRAYTYPE_P(1);
+ MemoryContext oldcontext;
+ List *pub_oids = NIL;
+ Datum *pub_datums;
+ bool *pub_nulls;
+ int pub_count;
+ TupleDesc tupdesc;
+ List *final_output = NIL;
+ bool has_clean_all_tables_pub = false;
+ List *except_pub_names = NIL;
+ Oid except_pub_id = InvalidOid;
+
+ Assert(get_rel_relkind(root_relid) == RELKIND_PARTITIONED_TABLE);
+
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ deconstruct_array(pub_names_array, TEXTOID, -1, false, 'i',
+ &pub_datums, &pub_nulls, &pub_count);
+
+ /* Build the list of pub_oids */
+ for (int i = 0; i < pub_count; i++)
+ {
+ if (!pub_nulls[i])
+ {
+ char *pubname = TextDatumGetCString(pub_datums[i]);
+
+ pub_oids = lappend_oid(pub_oids, get_publication_oid(pubname, false));
+ }
+ }
+
+ /*
+ * Determine whether the expensive expansion step can be skipped. If
+ * any publication is a FOR ALL TABLES publication without an EXCEPT
+ * clause, the root relation alone is sufficient as the result.
+ */
+ foreach_oid(puboid, pub_oids)
+ {
+ HeapTuple pubTup;
+ Form_pg_publication pubform;
+
+ pubTup = SearchSysCache1(PUBLICATIONOID, ObjectIdGetDatum(puboid));
+ if (!HeapTupleIsValid(pubTup))
+ continue;
+
+ pubform = (Form_pg_publication) GETSTRUCT(pubTup);
+ if (pubform->puballtables)
+ {
+ /* Check whether this publication defines any EXCEPT entries */
+ if (publication_has_any_except_table(puboid))
+ {
+ except_pub_names = lappend(except_pub_names,
+ makeString(pubform->pubname.data));
+ except_pub_id = pubform->oid;
+ }
+ else
+ {
+ /* This publication includes all tables without except */
+ has_clean_all_tables_pub = true;
+ }
+ }
+
+ ReleaseSysCache(pubTup);
+ }
+
+ if (list_length(except_pub_names) > 1)
+ {
+ StringInfo pub_names = makeStringInfo();
+
+ GetPublicationsStr(except_pub_names, pub_names, true);
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot combine publications that define EXCEPT TABLE clauses"),
+ errdetail("The following publications define EXCEPT TABLE clauses: %s.",
+ pub_names->data));
+ }
+
+ /* Return root immediately if no filtering logic is needed */
+ if (has_clean_all_tables_pub || !OidIsValid(except_pub_id))
+ {
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+ final_output = list_make1_oid(root_relid);
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ /*
+ * Build the effective publication relation list for a partitioned
+ * hierarchy in the presence of an EXCEPT publication.
+ *
+ * 1. Collect all leaf partitions under the given root relation.
+ *
+ * 2. Classify each leaf as either: - excepted_leaves: explicitly
+ * excluded via the EXCEPT clause (either directly or through any of
+ * its ancestors), or - allowed_leaves: not excluded and therefore
+ * part of the effective publication set.
+ *
+ * 3. Re-evaluate excluded leaves against other publications. A leaf
+ * excluded in one publication may still be effectively included if it
+ * (or any of its ancestors, or its schema) is published through
+ * another publication. Such leaves are added back to allowed_leaves.
+ *
+ * 4. Bottom-up collapse of partition branches. For each partitioned
+ * table in the hierarchy: - If *all* of its leaf partitions are
+ * present in allowed_leaves, the parent can represent the entire
+ * branch. - Such parents are added as candidates, allowing
+ * higher-level representation instead of listing every leaf
+ * individually.
+ *
+ * 5. Deduplicate and normalize the result. Remove any relation whose
+ * ancestor is already selected as a candidate. This ensures the final
+ * output contains only the highest-level representative for each
+ * fully-allowed branch and avoids redundant entries.
+ *
+ * The final_output therefore contains a minimal, non-redundant set of
+ * relations that accurately represents the effective publication set
+ * after considering EXCEPT rules and multiple publications.
+ */
+ else
+ {
+ List *all_tables;
+ List *all_leaves = NIL;
+ List *excepted_leaves = NIL;
+ List *allowed_leaves = NIL;
+ List *candidate_list = NIL;
+
+ /* Get all the leaf relations */
+ all_leaves = GetPubPartitionOptionRelations(all_leaves,
+ PUBLICATION_PART_LEAF,
+ root_relid);
+ foreach_oid(curr_relid, all_leaves)
+ {
+ /*
+ * A leaf table is considered excluded if it, or any of its
+ * ancestors, is listed in the EXCEPT clause of the
+ * publication. Otherwise, it remains part of the effective
+ * publication set.
+ */
+ if (is_relid_or_ancestor_excepted(curr_relid, except_pub_id))
+ excepted_leaves = lappend_oid(excepted_leaves, curr_relid);
+ else
+ allowed_leaves = lappend_oid(allowed_leaves, curr_relid);
+ }
+
+ /*
+ * A table excluded by the EXCEPT clause of one publication may
+ * still be included if it is explicitly published, or published
+ * via its schema or any of its ancestors, in another publication.
+ */
+ foreach_oid(curr_relid, excepted_leaves)
+ {
+ foreach_oid(pubid, pub_oids)
+ {
+ /* Skip the publication that excluded this relation. */
+ if (pubid == except_pub_id)
+ continue;
+
+ if (is_relid_or_ancestor_published(curr_relid, pubid))
+ allowed_leaves = lappend_oid(allowed_leaves,
+ curr_relid);
+ }
+ }
+
+ /* Bottom-Up Collapse. Check if parents can represent children */
+ all_tables = find_all_inheritors(root_relid, AccessShareLock, NULL);
+ candidate_list = list_copy(allowed_leaves);
+ foreach_oid(curr_relid, all_tables)
+ {
+ List *branch_leaves = NIL;
+ bool all_allowed = true;
+
+ /* Only consider partitioned tables as collapse candidates */
+ if (get_rel_relkind(curr_relid) != RELKIND_PARTITIONED_TABLE)
+ continue;
+
+ branch_leaves = GetPubPartitionOptionRelations(branch_leaves,
+ PUBLICATION_PART_LEAF,
+ curr_relid);
+ if (branch_leaves == NIL)
+ continue;
+
+ foreach_oid(lcb_oid, branch_leaves)
+ {
+ if (!list_member_oid(allowed_leaves, lcb_oid))
+ {
+ all_allowed = false;
+ break;
+ }
+ }
+
+ if (all_allowed)
+ candidate_list = list_append_unique_oid(candidate_list,
+ curr_relid);
+ }
+
+ /*
+ * Deduplicate: Filter out any relation whose ancestor is already
+ * present in the candidate list. This ensures we only return the
+ * "highest" representative for each branch.
+ */
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ foreach_oid(curr_relid, candidate_list)
+ {
+ List *ancestors = get_partition_ancestors(curr_relid);
+ bool ancestor_already_included = false;
+
+ /*
+ * Check if any ancestor of the current relation exists in the
+ * candidate list. If so, this relation is redundant.
+ */
+ foreach_oid(ancestor_relid, ancestors)
+ {
+ if (list_member_oid(candidate_list, ancestor_relid))
+ {
+ ancestor_already_included = true;
+ break;
+ }
+ }
+
+ if (!ancestor_already_included)
+ final_output = lappend_oid(final_output, curr_relid);
+
+ list_free(ancestors);
+ }
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+ /* Construct a tuple descriptor for the result rows. */
+ tupdesc = CreateTemplateTupleDesc(2);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 1, "nspname",
+ TEXTOID, -1, 0);
+ TupleDescInitEntry(tupdesc, (AttrNumber) 2, "relname",
+ TEXTOID, -1, 0);
+
+ funcctx->tuple_desc = BlessTupleDesc(tupdesc);
+ funcctx->user_fctx = final_output;
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ /* SRF Per-call Resume */
+ funcctx = SRF_PERCALL_SETUP();
+ results = (List *) funcctx->user_fctx;
+
+ if (funcctx->call_cntr < list_length(results))
+ {
+ Oid current_relid = list_nth_oid(results, (int) funcctx->call_cntr);
+ HeapTuple rettuple;
+ Datum values[2];
+ bool nulls[2] = {false, false};
+
+ values[0] = CStringGetTextDatum(get_namespace_name(get_rel_namespace(current_relid)));
+ values[1] = CStringGetTextDatum(get_rel_name(current_relid));
+
+ rettuple = heap_form_tuple(funcctx->tuple_desc, values, nulls);
+ SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum(rettuple));
+ }
+
+ SRF_RETURN_DONE(funcctx);
+}
+
/*
* Get information of the tables in the given publication array.
*
diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c
index b0a53c17dd0..1dee31a86c5 100644
--- a/src/backend/commands/subscriptioncmds.c
+++ b/src/backend/commands/subscriptioncmds.c
@@ -3051,6 +3051,8 @@ fetch_relation_list(WalReceiverConn *wrconn, List *publications)
pub_names.data);
}
+ elog(LOG, "fetch_relation_list: executing query to fetch effective relations: \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 eee909d912b..a6b6437da58 100644
--- a/src/backend/replication/logical/tablesync.c
+++ b/src/backend/replication/logical/tablesync.c
@@ -717,21 +717,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);
@@ -741,7 +748,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)"
@@ -771,6 +778,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);
@@ -955,6 +964,75 @@ 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, "SELECT nspname, relname FROM pg_get_publication_effective_tables(%u, ARRAY[%s]);",
+ lrel->remoteid,
+ pub_names->data);
+
+ elog(LOG, "Executing query to get the partition tables to be copied:\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 effective included table list for table \"%s.%s\" from publisher: %s",
+ nspname, relname, res->err));
+
+ /*
+ * Store the tables as a list of schemaname and tablename.
+ */
+ slot = MakeSingleTupleTableSlot(res->tupledesc, &TTSOpsMinimalTuple);
+ while (tuplestore_gettupleslot(res->tuplestore, true, false, slot))
+ {
+ QualifiedRelationName *relinfo = palloc_object(QualifiedRelationName);
+
+ relinfo->nspname = 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 effective_relations 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->nspname) == 0 &&
+ strcmp(relname, relinfo->relname) == 0)
+ {
+ pfree(relinfo->nspname);
+ 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
@@ -1044,6 +1122,7 @@ copy_table(Relation rel)
LogicalRepRelMapEntry *relmapentry;
LogicalRepRelation lrel;
List *qual = NIL;
+ List *effective_relations = NIL;
WalRcvExecResult *res;
StringInfoData cmd;
CopyFromState cstate;
@@ -1055,7 +1134,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);
@@ -1067,9 +1146,61 @@ 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->nspname,
+ 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));
@@ -1158,6 +1289,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 dd38eef9303..cbb1a0e7fe7 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -2073,6 +2073,73 @@ set_schema_sent_in_streamed_txn(RelationSyncEntry *entry, TransactionId xid)
MemoryContextSwitchTo(oldctx);
}
+/*
+ * publication_has_publishable_partitions
+ *
+ * Checks if the publication has any effective data in a partition tree.
+ * Returns true if at least one leaf is NOT excluded.
+ */
+static bool
+publication_has_publishable_partitions(Oid root_relid, Oid pubid)
+{
+ List *all_leaves = NIL;
+ List *except_tables_in_pub = NIL;
+
+ Assert(get_rel_relkind(root_relid) == RELKIND_PARTITIONED_TABLE);
+
+ except_tables_in_pub = GetExcludedPublicationTables(pubid,
+ PUBLICATION_PART_LEAF);
+
+ if (except_tables_in_pub == NIL)
+ return false;
+
+ /* Get all leaf relations in the hierarchy */
+ all_leaves = GetPubPartitionOptionRelations(NIL,
+ PUBLICATION_PART_LEAF,
+ root_relid);
+
+ /*
+ * Traverse leaves. If we find even ONE leaf that is not excluded by the
+ * publication's EXCEPT list, the tree is effective.
+ */
+ foreach_oid(curr_relid, all_leaves)
+ {
+ bool relation_in_except = false;
+ List *ancestors;
+
+ /* Check if the leaf itself is an exception */
+ if (list_member_oid(except_tables_in_pub, curr_relid))
+ continue;
+
+ /* Check if any ancestor is an exception */
+ ancestors = get_partition_ancestors(curr_relid);
+
+ foreach_oid(ancestor, ancestors)
+ {
+ if (list_member_oid(except_tables_in_pub, ancestor))
+ {
+ relation_in_except = true;
+ break;
+ }
+ }
+
+ list_free(ancestors);
+
+ /*
+ * If this leaf is not listed in the EXCEPT clause, the publication
+ * effectively includes data from this partition tree.
+ */
+ if (!relation_in_except)
+ {
+ list_free(all_leaves);
+ return true;
+ }
+ }
+
+ list_free(all_leaves);
+ return false;
+}
+
/*
* Find or create entry in the relation schema cache.
*
@@ -2134,6 +2201,9 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
bool am_partition = get_rel_relispartition(relid);
char relkind = get_rel_relkind(relid);
List *rel_publications = NIL;
+ bool root_published_via_alltables = false;
+ Oid root_ancestor = InvalidOid;
+ List *ancestors = NIL;
GetRelationPublications(relid, &pubids, NULL);
@@ -2214,6 +2284,33 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
entry->estate = NULL;
memset(entry->exprstate, 0, sizeof(entry->exprstate));
+ /*
+ * For partitions, pre-determine if the top-most ancestor is covered
+ * by any 'FOR ALL TABLES' publication that uses
+ * 'publish_via_partition_root' and an 'EXCEPT' clause.
+ *
+ * This pre-calculation is vital for resolving overlap conflicts: if a
+ * partition is excluded globally via an EXCEPT clause but included
+ * explicitly elsewhere (table/schema level), it must still be routed
+ * via the root identity if that root is published.
+ */
+ if (am_partition)
+ {
+ ancestors = get_partition_ancestors(relid);
+ root_ancestor = llast_oid(ancestors);
+
+ foreach_ptr(Publication, pubinfo, data->publications)
+ {
+ if (pubinfo->alltables && pubinfo->pubviaroot &&
+ publication_has_publishable_partitions(root_ancestor,
+ pubinfo->oid))
+ {
+ root_published_via_alltables = true;
+ break;
+ }
+ }
+ }
+
/*
* Build publication cache. We can't use one provided by relcache as
* relcache considers all publications that the given relation is in,
@@ -2236,28 +2333,42 @@ 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:
+ *
+ * If the relation is a partition, check whether the current
+ * relation or any of the ancestors is included in the EXCEPT
+ * TABLE list. If so, do not publish the change.
+ *
+ * "Do not publish the change" is achieved by keeping the variable
+ * "publish" set to false. And eventually, entry->pubactions will
+ * remain all false for this publication.
*/
if (pub->alltables)
{
List *exceptpubids = NIL;
- if (am_partition && pub->pubviaroot)
+ if (am_partition)
{
- List *ancestors = get_partition_ancestors(relid);
+ foreach_oid(ancestor, ancestors)
+ GetRelationPublications(ancestor, NULL, &exceptpubids);
- pub_relid = llast_oid(ancestors);
- ancestor_level = list_length(ancestors);
+ if (pub->pubviaroot)
+ {
+ pub_relid = root_ancestor;
+ ancestor_level = list_length(ancestors);
+ }
}
- GetRelationPublications(pub_relid, NULL, &exceptpubids);
+ GetRelationPublications(relid, NULL, &exceptpubids);
if (!list_member_oid(exceptpubids, pub->oid))
publish = true;
list_free(exceptpubids);
}
-
- if (!publish)
+ else if (!publish)
{
bool ancestor_published = false;
@@ -2271,12 +2382,10 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
{
Oid ancestor;
int level;
- List *ancestors = get_partition_ancestors(relid);
ancestor = GetTopMostAncestorInPublication(pub->oid,
ancestors,
&level);
-
if (ancestor != InvalidOid)
{
ancestor_published = true;
@@ -2291,7 +2400,25 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
if (list_member_oid(pubids, pub->oid) ||
list_member_oid(schemaPubids, pub->oid) ||
ancestor_published)
+ {
+ /*
+ * If the root ancestor is effectively published by an ALL
+ * TABLES publication with publish_via_partition_root,
+ * then changes for its partitions must be published using
+ * the root identity.
+ *
+ * This applies even if other publications do not specify
+ * publish_via_partition_root, provided the root is not
+ * excluded from that ALL TABLES publication.
+ */
+ if (root_published_via_alltables)
+ {
+ pub_relid = root_ancestor;
+ ancestor_level = list_length(ancestors);
+ }
+
publish = true;
+ }
}
/*
@@ -2339,6 +2466,17 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
Assert(publish_as_relid == pub_relid);
}
+ /*
+ * Partitions whose top-most ancestor is being published via
+ * an 'ALL TABLES' publication need not be individually
+ * published via any other publication. Repeated occurrences
+ * of a partition take the least restricted definition, which
+ * the 'ALL TABLES' publication always provides. I.e., all
+ * columns and all rows.
+ */
+ if (root_published_via_alltables)
+ continue;
+
/* Track publications for this ancestor. */
rel_publications = lappend(rel_publications, pub);
}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index dac40992cbc..0b951a3d6ea 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12398,6 +12398,13 @@
proname => 'pg_relation_is_publishable', provolatile => 's',
prorettype => 'bool', proargtypes => 'regclass',
prosrc => 'pg_relation_is_publishable' },
+{ oid => '9002', descr => 'return partition tables eligible for tablesync when publication uses EXCEPT and publish_via_partition_root = true',
+ proname => 'pg_get_publication_effective_tables', prorows => '1000',
+ proretset => 't', provolatile => 's',
+ prorettype => 'record', proargtypes => 'oid _text',
+ proallargtypes => '{oid,_text,text,text}', proargmodes => '{i,i,o,o}',
+ proargnames => '{root_relid,pub_names,nspname,relname}',
+ prosrc => 'pg_get_publication_effective_tables' },
# rls
{ oid => '3298',
diff --git a/src/include/catalog/pg_publication.h b/src/include/catalog/pg_publication.h
index 8d695276837..24308ad4950 100644
--- a/src/include/catalog/pg_publication.h
+++ b/src/include/catalog/pg_publication.h
@@ -190,6 +190,7 @@ extern Oid GetTopMostAncestorInPublication(Oid puboid, List *ancestors,
extern bool is_publishable_relation(Relation rel);
extern bool is_schema_publication(Oid pubid);
+extern bool is_relid_excepted(Oid relid, Oid pubid);
extern bool publication_has_any_except_table(Oid pubid);
extern bool check_and_fetch_column_list(Publication *pub, Oid relid,
MemoryContext mcxt, Bitmapset **cols);
diff --git a/src/include/replication/worker_internal.h b/src/include/replication/worker_internal.h
index 33fb7f552b4..1f9ece056b8 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 *nspname;
+ char *relname;
+} QualifiedRelationName;
+
/* Main memory context for apply worker. Permanent during worker lifetime. */
extern PGDLLIMPORT MemoryContext ApplyContext;
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 1e5819c6de2..55dfbe9d5e9 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -335,10 +335,17 @@ Except tables:
"public.testpub_root"
CREATE PUBLICATION testpub9 FOR ALL TABLES EXCEPT TABLE (testpub_part1);
-ERROR: partition "testpub_part1" cannot be excluded using EXCEPT TABLE
+\dRp+ testpub9;
+ Publication testpub9
+ 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_part1"
+
RESET client_min_messages;
DROP TABLE testpub_root, testpub_part1, testpub_part2;
-DROP PUBLICATION testpub8;
+DROP PUBLICATION testpub8, testpub9;
--- 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 4de0176bc25..80913d562be 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -158,10 +158,11 @@ CREATE TABLE testpub_part2 PARTITION OF testpub_root FOR VALUES FROM (100) TO (2
CREATE PUBLICATION testpub8 FOR ALL TABLES EXCEPT TABLE (testpub_root);
\dRp+ testpub8;
CREATE PUBLICATION testpub9 FOR ALL TABLES EXCEPT TABLE (testpub_part1);
+\dRp+ testpub9;
RESET client_min_messages;
DROP TABLE testpub_root, testpub_part1, testpub_part2;
-DROP PUBLICATION testpub8;
+DROP PUBLICATION testpub8, testpub9;
--- Tests for publications with SEQUENCES
CREATE SEQUENCE regress_pub_seq0;
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 361ce1ac996..28f9e59de9d 100644
--- a/src/test/subscription/t/037_rep_changes_except_table.pl
+++ b/src/test/subscription/t/037_rep_changes_except_table.pl
@@ -178,6 +178,54 @@ $node_publisher->safe_psql('postgres',
"SELECT slot_name FROM pg_replication_slot_advance('test_slot', pg_current_wal_lsn());"
);
+# Excluding one of the partitions (part2) should not affect replication of the
+# other partitions that don't intersect it. So, in this case, all of part1
+# should still be replicated.
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ 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->wait_for_subscription_sync($node_publisher, 'tap_sub_part');
+$node_publisher->safe_psql('postgres',
+ "SELECT slot_name FROM pg_replication_slot_advance('test_slot', pg_current_wal_lsn());"
+);
+
+# Verify that data inserted to the partition part2 is not published when it
+# is in the EXCEPT clause
+$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 table data 103 and 153 which is present in sch1.part2 should
+# not be replicated.
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.t1");
+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 table sch1.part1');
+
+$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.part2");
+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_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_part");
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION tap_pub_part;");
+
# ============================================
# Test when a subscription is subscribing to multiple publications
# ============================================
@@ -265,12 +313,52 @@ like(
$stderr,
qr/ERROR: cannot combine publications that define EXCEPT TABLE clauses.*
.*DETAIL:.*The following publications define EXCEPT TABLE clauses: "tap_pub1", "tap_pub2"./,
- 'subscription with multiple EXCEPT TABLE publication');
+ 'subscription with multiple EXCEPT TABLE publication');
$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION tap_sub');
$node_publisher->safe_psql('postgres', 'DROP PUBLICATION tap_pub1');
$node_publisher->safe_psql('postgres', 'DROP PUBLICATION tap_pub2');
+# ============================================
+# Test multi-publication subscription where a table listed in the
+# EXCEPT clause of one publication is included by another publication
+# ============================================
+$node_publisher->safe_psql(
+ 'postgres', qq(
+ TRUNCATE TABLE sch1.t1;
+ CREATE PUBLICATION tap_pub_part1 FOR ALL TABLES EXCEPT TABLE (sch1.part2_1) WITH (publish_via_partition_root = true);
+ CREATE PUBLICATION tap_pub_part2 FOR TABLE sch1.part2_1 WHERE (a > 110) WITH (publish_via_partition_root = true);
+ INSERT INTO sch1.t1 VALUES (11), (111), (161);
+));
+$node_subscriber->safe_psql(
+ 'postgres', qq(
+ TRUNCATE TABLE sch1.t1, sch1.part1, sch1.part2, sch1.part2_1, sch1.part2_2;
+ CREATE SUBSCRIPTION tap_sub_part CONNECTION '$publisher_connstr' PUBLICATION tap_pub_part1, tap_pub_part2;
+));
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub_part');
+
+# Verify that rows for sch1.part2_1 are replicated because the table, although
+# listed in the EXCEPT clause of tap_pub_part1, is included by tap_pub_part2.
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.t1 ORDER BY 1");
+is( $result, qq(11
+111
+161), 'initial rows replicated to root table');
+
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO sch1.t1 VALUES (12), (112), (162);");
+
+$node_publisher->wait_for_catchup('tap_sub_part');
+
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT * FROM sch1.t1 ORDER BY 1");
+is( $result, qq(11
+12
+111
+112
+161
+162), 'subsequent rows replicated to root table');
+
$node_publisher->stop('fast');
done_testing();
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 241945734ec..9c576e3c267 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2421,6 +2421,7 @@ QTNode
QUERYTYPE
QualCost
QualItem
+QualifiedRelationName
Query
QueryCompletion
QueryDesc
--
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], [email protected]
Subject: Re: Skipping schema changes in publication
In-Reply-To: <CALDaNm3X24fJznRUFh6NVhY1SDzgY9Aie1Ks=b6YqmAx-Z4H7Q@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