public inbox for [email protected]
help / color / mirror / Atom feedFrom: vignesh C <[email protected]>
To: Nisha Moond <[email protected]>
Cc: shveta malik <[email protected]>
Cc: Shlok Kyal <[email protected]>
Cc: Amit Kapila <[email protected]>
Cc: Ashutosh Sharma <[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: Sun, 1 Mar 2026 08:41:30 +0530
Message-ID: <CALDaNm32g7c323M4mgZ5Wn8sbYp_=uQ6G_u0f9qfBCzuHP8jgQ@mail.gmail.com> (raw)
In-Reply-To: <CABdArM6Wc2QtubTm2BAnmqtZqQSA16GsEMRZgbpa66kyLDy_AQ@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>
<CALDaNm3X24fJznRUFh6NVhY1SDzgY9Aie1Ks=b6YqmAx-Z4H7Q@mail.gmail.com>
<CAE9k0PkuG-gr8aBRs5jOUSqLkr_pdNmn0enBWjfp=RC8+De3PQ@mail.gmail.com>
<CANhcyEUK_L+2Y+QX44Gkf+TCyz8YBCCT4zp1mVqizqYKkx4RVw@mail.gmail.com>
<CAA4eK1+GuzCJZ80pAKZbqtMtr3RTPABtUQdvNvzn8RsCw+rQ6Q@mail.gmail.com>
<CAJpy0uAoKeDCc8PLc+f5Kjnd4Jt1KkCXK2bgKMWxCXSFu-jy7Q@mail.gmail.com>
<CANhcyEUur4fH9cB0NgiBHcV7Q0aoL+cC8qB2DCAJVcsdOJ+nqg@mail.gmail.com>
<CAJpy0uD4Hhx3aOgjR_AEk++gY0vknsnNd6+qdLPPS3QoS7y2uw@mail.gmail.com>
<CALDaNm02BrFzWK+uhR2RA8LLQsYgD99-qDzyshOFpKg220VP_Q@mail.gmail.com>
<CABdArM6Wc2QtubTm2BAnmqtZqQSA16GsEMRZgbpa66kyLDy_AQ@mail.gmail.com>
On Fri, 27 Feb 2026 at 17:01, Nisha Moond <[email protected]> wrote:
>
> On Fri, Feb 27, 2026 at 12:10 PM vignesh C <[email protected]> wrote:
> >
> > Rest of the comments are addressed in the attached v51 version.
>
> Thanks for the patches, I am still testing the patches, but sharing a
> few initial review comments.
>
> 1) v51-0001: Bug in testcase - /t/037_rep_changes_except_table.pl
> The test_except_root_partition() is invoked twice with values true and
> false. However, from the logs it appears that both executions use
> publish_via_partition_root = 1.
> LOG output:
> 138 2026-02-27 12:26:51.167 IST client backend[72317]
> 037_rep_changes_except_table.pl LOG: statement: CREATE PUBLICATION
> tap_pub_part FOR ALL TABLES EXCEPT TABLE (sch1.t1) WITH
> (publish_via_partition_root = 1);
> ...
> ...
> 238 2026-02-27 12:26:51.600 IST client backend[72369]
> 037_rep_changes_except_table.pl LOG: statement: CREATE PUBLICATION
> tap_pub_part FOR ALL TABLES EXCEPT TABLE (sch1.t1) WITH
> (publish_via_partition_root = 1);
>
> It seems $pubviaroot is assigned using the argument count instead of
> the argument value, which causes both runs to behave the same.
> Fix is to replace the assignment as:
> 27 - my $pubviaroot = @_;
> 27 + my ($pubviaroot) = @_;
Fixed
> 2) v51-0001: File: src/backend/commands/tablecmds.c
>
> + ereport(ERROR,
> + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
> + errmsg("cannot attach table \"%s\" as partition because it is
> referenced in publication \"%s\" EXCEPT clause",
> + RelationGetRelationName(attachrel), pubnames.data),
> + errdetail("The publication EXCEPT clause cannot contain tables that
> are partitions.");
> + errhint("Remove the table from the publication EXCEPT clause before
> attaching it."));
> + }
>
> In the ereport() call, there appears to be a small typo. A comma
> should follow errdetail() instead of a semicolon.
Fixed
> 3) v51-0002: File: src/backend/commands/publicationcmds.c
> +
> + /* Check that user is allowed to manipulate the publication tables. */
> + if (excepttables && !pubform->puballtables)
> + ereport(ERROR,
> + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
> + errmsg("publication \"%s\" is defined as NON FOR ALL TABLES",
> + NameStr(pubform->pubname)),
> + errdetail("EXCEPT Tables cannot be added to or dropped from non FOR
> ALL TABLES publications."));
> }
>
> It seems the check is for both SET and DROP cases, but this patch is
> only for SET. Also, I find it a bit confusing, could be made clearer.
> Suggestion:
> - errmsg("publication \"%s\" is defined as NON FOR ALL TABLES",
> + errmsg("publication \"%s\" is not defined as FOR ALL TABLES",
> NameStr(pubform->pubname)),
> - errdetail("EXCEPT Tables cannot be added to or dropped from non FOR
> ALL TABLES publications."));
> + errdetail("EXCEPT TABLE cannot be used with publications that are
> not defined as FOR ALL TABLES."));
Modified with slight changes to keep it inline with other error messages there.
> 4) v51-0002 and v51-003:
> File: doc/src/sgml/ref/alter_publication.sgml
> ...
> - remove one or more tables/schemas from the publication. Note that adding
> - tables/schemas to a publication that is already subscribed to will
> require an
> + except tables/tables/schemas in the publication with the specified list; the
> + existing except tables/ tables/schemas that were present in the publication
> ...
> 4a) After adding “except tables” as a third option, the slash based
> formatting may be slightly confusing. It may be clearer to write as:
> ... the existing except tables, tables, or schemas that were present in ....
> ... The <literal>DROP</literal> clauses will remove one or more except
> tables, tables, or schemas from the publication....
>
> If the existing slash format is retained, there is a typo in "except
> tables/ tables/schemas", extra space after tables/.
Existing format was used, the typo issue was fixed.
> 4b) Should we add examples for SET/DROP EXCEPT TABLE as well?
Added them
> 5) Inconsistency in EXCEPT TABLE syntax
> In CREATE PUBLICATION, the syntax requires parentheses, for example
> EXCEPT TABLE (t1, t2), whereas in SET and DROP it is accepted only
> without parentheses, like - EXCEPT TABLE t1, t2;
>
> Should we keep the syntax consistent across all commands?
I’ll look into this separately and share my analysis soon.
The attached v53 version patch has the changes for the same.
Also the comments from [1] have been addressed.
[1] - https://www.postgresql.org/message-id/CAA4eK1L2LDps5AdtRYySQhmqSbKeUG2GinOKJCn7bvLEdKOoPg%40mail.gma...
Regards,
Vignesh
Attachments:
[application/octet-stream] v53-0001-Skip-publishing-the-tables-specified-in-EXCEPT-T.patch (77.0K, 2-v53-0001-Skip-publishing-the-tables-specified-in-EXCEPT-T.patch)
download | inline diff:
From 0b947b64467c2e5591e5cdabf4e8d84d44d360b7 Mon Sep 17 00:00:00 2001
From: Vignesh C <[email protected]>
Date: Thu, 19 Feb 2026 20:16:39 +0530
Subject: [PATCH v53 1/3] 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 | 57 +++-
doc/src/sgml/ref/psql-ref.sgml | 5 +-
src/backend/catalog/pg_publication.c | 225 ++++++++++++---
src/backend/commands/publicationcmds.c | 52 +++-
src/backend/commands/tablecmds.c | 42 ++-
src/backend/parser/gram.y | 39 ++-
src/backend/replication/pgoutput/pgoutput.c | 46 ++-
src/backend/utils/cache/relcache.c | 19 +-
src/bin/pg_dump/pg_dump.c | 75 ++++-
src/bin/pg_dump/pg_dump.h | 1 +
src/bin/pg_dump/t/002_pg_dump.pl | 30 ++
src/bin/psql/describe.c | 95 ++++++-
src/bin/psql/tab-complete.in.c | 12 +-
src/include/catalog/pg_publication.h | 19 +-
src/include/catalog/pg_publication_rel.h | 1 +
src/include/nodes/parsenodes.h | 5 +-
src/test/regress/expected/publication.out | 129 ++++++++-
src/test/regress/sql/publication.sql | 65 ++++-
src/test/subscription/meson.build | 1 +
.../t/037_rep_changes_except_table.pl | 267 ++++++++++++++++++
22 files changed, 1080 insertions(+), 121 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 e7067c84ece..69588937719 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..77066ef680b 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,37 @@ 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. The optional
+ <literal>ONLY</literal> and <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>
+ </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 +525,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 TABLE (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 TABLE (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..e1b1532c464 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -53,37 +53,49 @@ typedef struct
* error if not.
*/
static void
-check_publication_add_relation(Relation targetrel)
+check_publication_add_relation(PublicationRelInfo *pri)
{
+ Relation targetrel = pri->relation;
+ const char *errormsg;
+
+ if (pri->except)
+ errormsg = gettext_noop("cannot use publication EXCEPT clause for relation \"%s\"");
+ else
+ errormsg = gettext_noop("cannot add relation \"%s\" to publication");
+
+
+ /* If in EXCEPT clause, must be root partitioned table */
+ if (pri->except && targetrel->rd_rel->relispartition)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg(errormsg, RelationGetRelationName(targetrel)),
+ errdetail("This operation is not supported for individual partitions.")));
+
/* Must be a regular or partitioned table */
if (RelationGetForm(targetrel)->relkind != RELKIND_RELATION &&
RelationGetForm(targetrel)->relkind != RELKIND_PARTITIONED_TABLE)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("cannot add relation \"%s\" to publication",
- RelationGetRelationName(targetrel)),
+ errmsg(errormsg, RelationGetRelationName(targetrel)),
errdetail_relkind_not_supported(RelationGetForm(targetrel)->relkind)));
/* Can't be system table */
if (IsCatalogRelation(targetrel))
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("cannot add relation \"%s\" to publication",
- RelationGetRelationName(targetrel)),
+ errmsg(errormsg, RelationGetRelationName(targetrel)),
errdetail("This operation is not supported for system tables.")));
/* UNLOGGED and TEMP relations cannot be part of publication. */
if (targetrel->rd_rel->relpersistence == RELPERSISTENCE_TEMP)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("cannot add relation \"%s\" to publication",
- RelationGetRelationName(targetrel)),
+ errmsg(errormsg, RelationGetRelationName(targetrel)),
errdetail("This operation is not supported for temporary tables.")));
else if (targetrel->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("cannot add relation \"%s\" to publication",
- RelationGetRelationName(targetrel)),
+ errmsg(errormsg, RelationGetRelationName(targetrel)),
errdetail("This operation is not supported for unlogged tables.")));
}
@@ -366,7 +378,7 @@ GetTopMostAncestorInPublication(Oid puboid, List *ancestors, int *ancestor_level
foreach(lc, ancestors)
{
Oid ancestor = lfirst_oid(lc);
- List *apubids = GetRelationPublications(ancestor);
+ List *apubids = GetRelationIncludedPublications(ancestor);
List *aschemaPubids = NIL;
level++;
@@ -466,7 +478,7 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri,
RelationGetRelationName(targetrel), pub->name)));
}
- check_publication_add_relation(targetrel);
+ check_publication_add_relation(pri);
/* Validate and translate column names into a Bitmapset of attnums. */
attnums = pub_collist_validate(pri->relation, pri->columns);
@@ -482,6 +494,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 +544,26 @@ 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.
+ * Relations excluded via the EXCEPT clause do not need explicit
+ * invalidation as CreatePublication() function invalidates all relations
+ * as part of defining a FOR ALL TABLES publication.
*/
- 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,23 +772,30 @@ 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)
+/*
+ * Internal function to get the list of publication Oids for a relation.
+ *
+ * If except_flag is true, returns the list of publication which specified the
+ * relation with EXCEPT clause; otherwise, returns the list of publications
+ * in which relation is included.
+ */
+static List *
+GetRelationPublications(Oid relid, bool except_flag)
{
List *result = NIL;
CatCList *pubrellist;
- int i;
/* Find all publications associated with the relation. */
pubrellist = SearchSysCacheList1(PUBLICATIONRELMAP,
ObjectIdGetDatum(relid));
- for (i = 0; i < pubrellist->n_members; i++)
+ for (int i = 0; i < pubrellist->n_members; i++)
{
HeapTuple tup = &pubrellist->members[i]->tuple;
- Oid pubid = ((Form_pg_publication_rel) GETSTRUCT(tup))->prpubid;
+ Form_pg_publication_rel pubrel = (Form_pg_publication_rel) GETSTRUCT(tup);
+ Oid pubid = pubrel->prpubid;
- result = lappend_oid(result, pubid);
+ if (pubrel->prexcept == except_flag)
+ result = lappend_oid(result, pubid);
}
ReleaseSysCacheList(pubrellist);
@@ -773,14 +803,30 @@ GetRelationPublications(Oid relid)
return result;
}
+/* Gets list of publication oids for a relation */
+List *
+GetRelationIncludedPublications(Oid relid)
+{
+ return GetRelationPublications(relid, false);
+}
+
+/* Gets list of publication oids which has relation in EXCEPT clause */
+List *
+GetRelationExcludedPublications(Oid relid)
+{
+ return GetRelationPublications(relid, true);
+}
+
/*
- * 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 +851,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 +867,34 @@ 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 ALL TABLES publications.
+ */
+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 +934,36 @@ 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));
+ /* EXCEPT filtering applies only to relations, not sequences */
+ if (relkind == RELKIND_RELATION)
+ exceptlist = GetExcludedPublicationTables(pubid, pubviaroot ?
+ PUBLICATION_PART_ROOT :
+ PUBLICATION_PART_LEAF);
+
classRel = table_open(RelationRelationId, AccessShareLock);
ScanKeyInit(&key[0],
@@ -891,7 +979,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 +1001,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 +1206,51 @@ GetPublicationByName(const char *pubname, bool missing_ok)
return OidIsValid(oid) ? GetPublication(oid) : NULL;
}
+/*
+ * publication_has_any_except_table
+ *
+ * Return true if the given publication has any relation entry
+ * in pg_publication_rel with prexcept set.
+ */
+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 +1303,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 +1502,7 @@ pg_get_publication_sequences(PG_FUNCTION_ARGS)
publication = GetPublicationByName(pubname, false);
if (publication->allsequences)
- sequences = GetAllPublicationRelations(RELKIND_SEQUENCE, false);
+ sequences = GetAllPublicationRelations(publication, RELKIND_SEQUENCE);
funcctx->user_fctx = sequences;
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index fc3a4c19e65..6a3ca4751fa 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -181,7 +181,7 @@ parse_publication_options(ParseState *pstate,
*/
static void
ObjectsInPublicationToOids(List *pubobjspec_list, ParseState *pstate,
- List **rels, List **schemas)
+ List **rels, List **exceptrels, List **schemas)
{
ListCell *cell;
PublicationObjSpec *pubobj;
@@ -198,7 +198,12 @@ ObjectsInPublicationToOids(List *pubobjspec_list, ParseState *pstate,
switch (pubobj->pubobjtype)
{
+ case PUBLICATIONOBJ_EXCEPT_TABLE:
+ pubobj->pubtable->except = true;
+ *exceptrels = lappend(*exceptrels, 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);
@@ -844,6 +849,7 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
char publish_generated_columns;
AclResult aclresult;
List *relations = NIL;
+ List *exceptrelations = NIL;
List *schemaidlist = NIL;
/* must have CREATE privilege on database */
@@ -929,8 +935,21 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
CommandCounterIncrement();
/* Associate objects with the publication. */
+ ObjectsInPublicationToOids(stmt->pubobjects, pstate, &relations,
+ &exceptrelations, &schemaidlist);
+
if (stmt->for_all_tables)
{
+ /* Process EXCEPT table list */
+ if (exceptrelations != NIL)
+ {
+ List *rels;
+
+ rels = OpenTableList(exceptrelations);
+ PublicationAddTables(puboid, rels, true, NULL);
+ CloseTableList(rels);
+ }
+
/*
* Invalidate relcache so that publication info is rebuilt. Sequences
* publication doesn't require invalidation, as replica identity
@@ -940,9 +959,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,
@@ -1050,8 +1066,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 +1186,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 +1272,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 +1374,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 +1425,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)
{
@@ -1566,11 +1584,15 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt)
else
{
List *relations = NIL;
+ List *exceptrelations = NIL;
List *schemaidlist = NIL;
Oid pubid = pubform->oid;
ObjectsInPublicationToOids(stmt->pubobjects, pstate, &relations,
- &schemaidlist);
+ &exceptrelations, &schemaidlist);
+
+ /* EXCEPT clause is not supported with ALTER PUBLICATION */
+ Assert(exceptrelations == NIL);
CheckAlterPublication(stmt, tup, relations, schemaidlist);
@@ -1771,6 +1793,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 +1866,7 @@ OpenTableList(List *tables)
/* child inherits column list from parent */
pub_rel->columns = t->columns;
+ pub_rel->except = t->except;
rels = lappend(rels, pub_rel);
relids = lappend_oid(relids, childrelid);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index b04b0dbd2a0..c0104f084fb 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -8686,7 +8686,7 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName,
* expressions.
*/
if (attgenerated == ATTRIBUTE_GENERATED_VIRTUAL &&
- GetRelationPublications(RelationGetRelid(rel)) != NIL)
+ GetRelationIncludedPublications(RelationGetRelid(rel)) != NIL)
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"),
@@ -18881,7 +18881,7 @@ ATPrepChangePersistence(AlteredTableInfo *tab, Relation rel, bool toLogged)
* UNLOGGED, as UNLOGGED tables can't be published.
*/
if (!toLogged &&
- GetRelationPublications(RelationGetRelid(rel)) != NIL)
+ GetRelationIncludedPublications(RelationGetRelid(rel)) != NIL)
ereport(ERROR,
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("cannot change table \"%s\" to unlogged because it is part of a publication",
@@ -20322,6 +20322,7 @@ ATExecAttachPartition(List **wqueue, Relation rel, PartitionCmd *cmd,
const char *trigger_name;
Oid defaultPartOid;
List *partBoundConstraint;
+ List *exceptpuboids = NIL;
ParseState *pstate = make_parsestate(NULL);
pstate->p_sourcetext = context->queryString;
@@ -20361,6 +20362,43 @@ ATExecAttachPartition(List **wqueue, Relation rel, PartitionCmd *cmd,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("cannot attach a typed table as partition")));
+ /*
+ * Disallow attaching a partition if the table is referenced in a
+ * publication EXCEPT clause. Changing the partition hierarchy could alter
+ * the effective publication membership.
+ */
+ exceptpuboids = GetRelationExcludedPublications(RelationGetRelid(attachrel));
+ if (exceptpuboids != NIL)
+ {
+ bool first = true;
+ StringInfoData pubnames;
+
+ initStringInfo(&pubnames);
+
+ foreach_oid(pubid, exceptpuboids)
+ {
+ char *pubname = get_publication_name(pubid, false);
+
+ if (!first)
+ appendStringInfoString(&pubnames, ", ");
+
+ first = false;
+
+ appendStringInfoString(&pubnames, quote_literal_cstr(pubname));
+ }
+
+ ereport(ERROR,
+ errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg_plural("cannot attach table \"%s\" as partition because it is referenced in publication \"%s\" EXCEPT clause",
+ "cannot attach table \"%s\" as partition because it is referenced in publications \"%s\" EXCEPT clause",
+ list_length(exceptpuboids),
+ RelationGetRelationName(attachrel),
+ pubnames.data),
+ errdetail("The publication EXCEPT clause cannot contain tables that are partitions."));
+ }
+
+ list_free(exceptpuboids);
+
/*
* Table being attached should not already be part of inheritance; either
* as a child table...
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c567252acc4..f4d49b6018d 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,17 @@ pub_obj_list: PublicationObjSpec
{ $$ = lappend($1, $3); }
;
+opt_pub_except_clause:
+ EXCEPT 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;
$$->location = @1;
}
| ALL SEQUENCES
@@ -10954,6 +10964,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 +19839,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 +19861,7 @@ preprocess_pub_all_objtype_list(List *all_objects_list, bool *all_tables,
parser_errposition(obj->location));
*all_tables = true;
+ *pubobjects = list_concat(*pubobjects, obj->except_tables);
}
else if (obj->pubobjtype == PUBLICATION_ALL_SEQUENCES)
{
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 7a49185d29d..6cabcce3e3f 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -2089,7 +2089,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 = GetRelationIncludedPublications(relid);
/*
* We don't acquire a lock on the namespace system table as we build
@@ -2206,14 +2206,50 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
*/
if (pub->alltables)
{
- publish = true;
- if (pub->pubviaroot && am_partition)
+ List *exceptpubids = NIL;
+ List *ancestorexceptpubids = NIL;
+
+ if (am_partition)
{
List *ancestors = get_partition_ancestors(relid);
- pub_relid = llast_oid(ancestors);
- ancestor_level = list_length(ancestors);
+ /*
+ * Only the top-most ancestor may appear in the EXCEPT
+ * clause. Therefore, exclusion must be evaluated at the
+ * top-most ancestor.
+ */
+ if (pub->pubviaroot)
+ {
+ pub_relid = llast_oid(ancestors);
+ ancestor_level = list_length(ancestors);
+ }
+ else
+ {
+ /*
+ * When publishing partitions individually, we must
+ * still check whether the top-most ancestor is
+ * excluded. If the root is excluded, none of its
+ * partitions should be published.
+ */
+ ancestorexceptpubids = GetRelationExcludedPublications(llast_oid(ancestors));
+ }
}
+
+ /*
+ * Check if the resolved relation (the table itself or its
+ * partition root) is explicitly excluded from this
+ * publication.
+ */
+ exceptpubids = GetRelationExcludedPublications(pub_relid);
+
+ if (!list_member_oid(exceptpubids, pub->oid) &&
+ !list_member_oid(ancestorexceptpubids, pub->oid))
+ publish = true;
+
+ list_free(exceptpubids);
+
+ if (!publish)
+ continue;
}
if (!publish)
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 6b634c9fff1..19a24f44948 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,8 @@ RelationBuildPublicationDesc(Relation relation, PublicationDesc *pubdesc)
pubdesc->gencols_valid_for_delete = true;
/* Fetch the publication membership info. */
- puboids = GetRelationPublications(relid);
+ puboids = GetRelationIncludedPublications(relid);
+ exceptpuboids = GetRelationExcludedPublications(relid);
schemaid = RelationGetNamespace(relation);
puboids = list_concat_unique_oid(puboids, GetSchemaPublications(schemaid));
@@ -5838,16 +5841,22 @@ RelationBuildPublicationDesc(Relation relation, PublicationDesc *pubdesc)
foreach(lc, ancestors)
{
Oid ancestor = lfirst_oid(lc);
+ List *ancestor_puboids = GetRelationIncludedPublications(ancestor);
+ List *ancestor_exceptpuboids = GetRelationExcludedPublications(ancestor);;
- puboids = list_concat_unique_oid(puboids,
- GetRelationPublications(ancestor));
+ 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 dd8adef0a3e..95e1a57141c 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:
@@ -4662,10 +4708,29 @@ dumpPublication(Archive *fout, const PublicationInfo *pubinfo)
appendPQExpBuffer(query, "CREATE PUBLICATION %s",
qpubname);
- if (pubinfo->puballtables && pubinfo->puballsequences)
- appendPQExpBufferStr(query, " FOR ALL TABLES, ALL SEQUENCES");
- else if (pubinfo->puballtables)
+ 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, ")");
+
+ if (pubinfo->puballsequences)
+ appendPQExpBufferStr(query, ", ALL SEQUENCES");
+ }
else if (pubinfo->puballsequences)
appendPQExpBufferStr(query, " FOR ALL SEQUENCES");
@@ -4845,6 +4910,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 +4923,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 6deceef23f3..e138ef1276c 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 f15bd06adcc..588891a62bd 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -3202,6 +3202,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 TABLE (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 4352991e541..509e50bbfb6 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3073,17 +3073,42 @@ 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);
+
+ /*
+ * Skip entries where this relation appears in the
+ * publication's EXCEPT TABLE list.
+ */
+ 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);
+
+ /*
+ * For FOR ALL TABLES publications, skip publications that
+ * explicitly exclude this relation via EXCEPT TABLE.
+ */
+ 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 +3159,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' 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 +6807,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 +6830,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 n.nspname || '.' || c.relname\n"
+ "FROM pg_catalog.pg_class c\n"
+ " JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
+ " JOIN pg_catalog.pg_publication_rel pr ON c.oid = pr.prrelid\n"
+ "WHERE pr.prpubid = '%s' 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 b2dba6d10ab..8927f7e6f22 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 6e5f73caa9e..a9f89e89006 100644
--- a/src/include/catalog/pg_publication.h
+++ b/src/include/catalog/pg_publication.h
@@ -150,16 +150,19 @@ 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 List *GetRelationIncludedPublications(Oid relid);
+extern List *GetRelationExcludedPublications(Oid relid);
/*---------
- * 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
@@ -172,9 +175,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,
@@ -189,6 +195,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 63eb7c75f53..cefc38c9ed8 100644
--- a/src/include/catalog/pg_publication_rel.h
+++ b/src/include/catalog/pg_publication_rel.h
@@ -33,6 +33,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/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index f37131835be..ff41943a6db 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/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 7fb49aaf29b..85da81a1f27 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -213,33 +213,140 @@ 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 table list in the EXCEPT clause of a FOR ALL TABLES publication and
+-- the optional TABLE keyword.
+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 TABLE (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"
+
+-- 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"
-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;
+ 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: cannot use publication EXCEPT clause for relation "testpub_part1"
+DETAIL: This operation is not supported for individual partitions.
+CREATE TABLE tab_main (a int) PARTITION BY RANGE(a);
+-- Attaching a partition is not allowed if the partitioned table appears in a
+-- publication's EXCEPT clause.
+ALTER TABLE tab_main ATTACH PARTITION testpub_root FOR VALUES FROM (0) TO (200);
+ERROR: cannot attach table "testpub_root" as partition because it is referenced in publication "'testpub8'" EXCEPT clause
+DETAIL: The publication EXCEPT clause cannot contain tables that are partitions.
+RESET client_min_messages;
+DROP TABLE testpub_root, testpub_part1, testpub_part2, tab_main;
+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..507028bed32 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -105,20 +105,69 @@ 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 table list in the EXCEPT clause of a FOR ALL TABLES publication and
+-- the optional TABLE keyword.
+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 TABLE (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);
+
+CREATE TABLE tab_main (a int) PARTITION BY RANGE(a);
+-- Attaching a partition is not allowed if the partitioned table appears in a
+-- publication's EXCEPT clause.
+ALTER TABLE tab_main ATTACH PARTITION testpub_root FOR VALUES FROM (0) TO (200);
+
+RESET client_min_messages;
+DROP TABLE testpub_root, testpub_part1, testpub_part2, tab_main;
+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..786799ed260
--- /dev/null
+++ b/src/test/subscription/t/037_rep_changes_except_table.pl
@@ -0,0 +1,267 @@
+
+# Copyright (c) 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) = @_;
+
+ # If the root partitioned table is in the EXCEPT clause, all its partitions
+ # are excluded from publication, regardless of the
+ # publish_via_partition_root setting.
+ $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');
+
+ # Advance the replication slot to ignore changes generated before this point.
+ $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 TABLE sch1.parent (a int);
+ CREATE TABLE sch1.child (b int) INHERITS (sch1.parent);
+ CREATE TABLE sch1.parent1 (a int);
+ CREATE TABLE sch1.child1 (b int) INHERITS (sch1.parent1);
+));
+
+# Create schemas and tables on subscriber
+$node_subscriber->safe_psql(
+ 'postgres', qq(
+ CREATE SCHEMA sch1;
+ CREATE TABLE sch1.tab1 (a int);
+ CREATE TABLE sch1.parent (a int);
+ CREATE TABLE sch1.child (b int) INHERITS (sch1.parent);
+ CREATE TABLE sch1.parent1 (a int);
+ CREATE TABLE sch1.child1 (b int) INHERITS (sch1.parent1);
+));
+
+# Exclude sch1.tab1 and the parent tables sch1.parent and ONLY sch1.parent1 to
+# verify exclusion behavior for inherited tables and the effect of ONLY in the
+# EXCEPT list.
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION tap_pub_schema FOR ALL TABLES EXCEPT TABLE (sch1.tab1, sch1.parent, only sch1.parent1)"
+);
+
+# Create a logical replication slot to help with later tests.
+$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', qq(
+ INSERT INTO sch1.tab1 VALUES(generate_series(11,20));
+ INSERT INTO sch1.child VALUES(generate_series(11,20), 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'
+);
+
+# This should be published because ONLY sch1.parent1 was specified in the
+# EXCEPT clause, so the exclusion applies only to the parent table and not to
+# its child.
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO sch1.child1 VALUES(generate_series(11,20), generate_series(11,20))"
+);
+
+# 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');
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM sch1.child");
+is($result, qq(0), 'check replicated inserts on subscriber');
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM sch1.child1");
+is($result, qq(10), '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');
+
+# ============================================
+# Test when a subscription is subscribing to multiple publications
+# ============================================
+
+# 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_pub1 FOR ALL TABLES EXCEPT TABLE (sch1.tab1);
+ 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"
+);
+
+$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] v53-0003-Support-DROP-EXCEPT-TABLE-in-ALTER-PUBLICATION.patch (14.0K, 3-v53-0003-Support-DROP-EXCEPT-TABLE-in-ALTER-PUBLICATION.patch)
download | inline diff:
From 4cebf4059a7462b94e0ae8d0aeeb657e396d0363 Mon Sep 17 00:00:00 2001
From: Vignesh C <[email protected]>
Date: Sat, 28 Feb 2026 23:05:09 +0530
Subject: [PATCH v53 3/3] Support DROP EXCEPT TABLE in ALTER PUBLICATION
Extend ALTER PUBLICATION to support DROP EXCEPT TABLE for
publications defined with FOR ALL TABLES.
---
doc/src/sgml/ref/alter_publication.sgml | 17 +++++++----
src/backend/commands/publicationcmds.c | 2 +-
src/backend/commands/tablecmds.c | 2 +-
src/backend/parser/gram.y | 11 ++-----
src/bin/psql/tab-complete.in.c | 6 ++--
src/test/regress/expected/publication.out | 28 +++++++++++-------
src/test/regress/sql/publication.sql | 3 +-
.../t/037_rep_changes_except_table.pl | 29 +++++++++++++++++++
8 files changed, 69 insertions(+), 29 deletions(-)
diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index 7c28391033e..da046be474b 100644
--- a/doc/src/sgml/ref/alter_publication.sgml
+++ b/doc/src/sgml/ref/alter_publication.sgml
@@ -41,7 +41,7 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
<phrase>and <replaceable class="parameter">publication_drop_object</replaceable> is one of:</phrase>
- TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ... ]
+ [ EXCEPT ] TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ... ]
TABLES IN SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ... ]
<phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
@@ -63,10 +63,11 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
publication. The <literal>SET</literal> clause will replace the list of
except tables/tables/schemas in the publication with the specified list; the
existing except tables/tables/schemas that were present in the publication
- will be removed. The <literal>ADD</literal> and <literal>DROP</literal>
- clauses will add and remove one or more tables/schemas from the publication.
- Note that adding tables/schemas to a publication that is already subscribed
- to will require an
+ will be removed. The <literal>ADD</literal> clauses will add one or more
+ tables/schemas to the publication. The <literal>DROP</literal> clauses
+ will remove one or more except tables/tables/schemas from the publication.
+ Note that adding tables/schemas or dropping except tables to a publication
+ that is already subscribed to will require an
<link linkend="sql-altersubscription-params-refresh-publication">
<literal>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</literal></link> action on the
subscribing side in order to become effective. Note also that
@@ -235,6 +236,12 @@ ALTER PUBLICATION mypublication SET TABLE users (user_id, firstname, lastname),
Replace the publication's EXCEPT table list:
<programlisting>
ALTER PUBLICATION mypublication SET EXCEPT TABLE users, departments;
+</programlisting></para>
+
+ <para>
+ Remove tables from the publication's EXCEPT table list:
+<programlisting>
+ALTER PUBLICATION mypublication DROP EXCEPT TABLE users, departments;
</programlisting></para>
<para>
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index 166fe3b0c3d..f43edd33098 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -1562,7 +1562,7 @@ CheckAlterPublication(AlterPublicationStmt *stmt, HeapTuple tup,
errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("publication \"%s\" is not defined as FOR ALL TABLES",
NameStr(pubform->pubname)),
- errdetail("EXCEPT Tables cannot be added to publications that are not defined as FOR ALL TABLES."));
+ errdetail("EXCEPT Tables cannot be added to or dropped from publications that are not defined as FOR ALL TABLES."));
}
/*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index cedbdd38570..12e0d1a2574 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -20395,7 +20395,7 @@ ATExecAttachPartition(List **wqueue, Relation rel, PartitionCmd *cmd,
RelationGetRelationName(attachrel),
pubnames.data),
errdetail("The publication EXCEPT clause cannot contain tables that are partitions."),
- errhint("Modify the publication's EXCEPT clause using ALTER PUBLICATION ... SET EXCEPT TABLE before attaching the table."));
+ errhint("Modify the publication's EXCEPT clause using ALTER PUBLICATION ... SET EXCEPT TABLE or DROP EXCEPT TABLE before attaching the table."));
}
list_free(exceptpuboids);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0544908fcb9..37dece5ed98 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11032,7 +11032,7 @@ AlterPublicationStmt:
if (has_except_table)
ereport(ERROR,
errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("EXCEPT TABLE clause allowed only for SET clause"));
+ errmsg("EXCEPT TABLE clause allowed only for SET/DROP clause"));
n->action = AP_AddObjects;
$$ = (Node *) n;
@@ -11049,18 +11049,11 @@ AlterPublicationStmt:
}
| ALTER PUBLICATION name DROP pub_obj_list
{
- bool has_except_table = false;
AlterPublicationStmt *n = makeNode(AlterPublicationStmt);
n->pubname = $3;
n->pubobjects = $5;
- has_except_table = preprocess_pubobj_list(n->pubobjects,
- yyscanner);
- if (has_except_table)
- ereport(ERROR,
- errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("EXCEPT TABLE clause allowed only for SET clause"));
-
+ preprocess_pubobj_list(n->pubobjects, yyscanner);
n->action = AP_DropObjects;
$$ = (Node *) n;
}
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index f45f3b76523..9ab11971c1d 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2317,8 +2317,10 @@ match_previous_words(int pattern_id,
COMPLETE_WITH(",");
/* ALTER PUBLICATION <name> DROP */
else if (Matches("ALTER", "PUBLICATION", MatchAny, "DROP"))
- COMPLETE_WITH("TABLES IN SCHEMA", "TABLE");
- /* ALTER PUBLICATION <name> SET */
+ COMPLETE_WITH("EXCEPT", "TABLES IN SCHEMA", "TABLE");
+ else if (Matches("ALTER", "PUBLICATION", MatchAny, "DROP", "EXCEPT"))
+ COMPLETE_WITH("TABLE");
+ /* ALTER PUBLICATION <name> SET */
else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET"))
COMPLETE_WITH("(", "EXCEPT", "TABLES IN SCHEMA", "TABLE");
else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "EXCEPT"))
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 3c70c51ddec..1b23116db28 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -128,14 +128,15 @@ Tables from schemas:
-- fail - can't add an EXCEPT TABLE to 'FOR TABLE' publication
ALTER PUBLICATION testpub_fortable ADD EXCEPT TABLE testpub_tbl1;
-ERROR: EXCEPT TABLE clause allowed only for SET clause
+ERROR: EXCEPT TABLE clause allowed only for SET/DROP clause
-- fail - can't drop an EXCEPT TABLE from 'FOR TABLE' publication
ALTER PUBLICATION testpub_fortable DROP EXCEPT TABLE testpub_tbl1;
-ERROR: EXCEPT TABLE clause allowed only for SET clause
+ERROR: publication "testpub_fortable" is not defined as FOR ALL TABLES
+DETAIL: EXCEPT Tables cannot be added to or dropped from publications that are not defined as FOR ALL TABLES.
-- fail - can't set an EXCEPT TABLE to 'FOR TABLE' publication
ALTER PUBLICATION testpub_fortable SET EXCEPT TABLE testpub_tbl1;
ERROR: publication "testpub_fortable" is not defined as FOR ALL TABLES
-DETAIL: EXCEPT Tables cannot be added to publications that are not defined as FOR ALL TABLES.
+DETAIL: EXCEPT Tables cannot be added to or dropped from publications that are not defined as FOR ALL TABLES.
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub_forschema FOR TABLES IN SCHEMA pub_test;
-- should be able to create publication with schema and table of the same
@@ -225,14 +226,15 @@ Not-null constraints:
-- fail - can't add an EXCEPT TABLE to schema publication
ALTER PUBLICATION testpub_forschema ADD EXCEPT TABLE pub_test.testpub_nopk;
-ERROR: EXCEPT TABLE clause allowed only for SET clause
+ERROR: EXCEPT TABLE clause allowed only for SET/DROP clause
-- fail - can't drop an EXCEPT TABLE from schema publication
ALTER PUBLICATION testpub_forschema DROP EXCEPT TABLE pub_test.testpub_nopk;
-ERROR: EXCEPT TABLE clause allowed only for SET clause
+ERROR: publication "testpub_forschema" is not defined as FOR ALL TABLES
+DETAIL: EXCEPT Tables cannot be added to or dropped from publications that are not defined as FOR ALL TABLES.
-- fail - can't set an EXCEPT TABLE to schema publication
ALTER PUBLICATION testpub_forschema SET EXCEPT TABLE pub_test.testpub_nopk;
ERROR: publication "testpub_forschema" is not defined as FOR ALL TABLES
-DETAIL: EXCEPT Tables cannot be added to publications that are not defined as FOR ALL TABLES.
+DETAIL: EXCEPT Tables cannot be added to or dropped from publications that are not defined as FOR ALL TABLES.
---------------------------------------------
-- EXCEPT TABLE tests for normal tables
---------------------------------------------
@@ -285,12 +287,18 @@ ALTER PUBLICATION testpub_foralltables_excepttable SET EXCEPT TABLE testpub_tbl1
Except tables:
"public.testpub_tbl1"
--- fail - Dropping EXCEPT table is not supported.
+-- Drop table from the EXCEPT list of a FOR ALL TABLES publication.
ALTER PUBLICATION testpub_foralltables_excepttable DROP EXCEPT TABLE testpub_tbl1;
-ERROR: EXCEPT TABLE clause allowed only for SET clause
+\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
+(1 row)
+
-- fail - Adding EXCEPT table is not supported.
ALTER PUBLICATION testpub_foralltables_excepttable ADD EXCEPT TABLE testpub_tbl1;
-ERROR: EXCEPT TABLE clause allowed only for SET clause
+ERROR: EXCEPT TABLE clause allowed only for SET/DROP clause
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;
@@ -380,7 +388,7 @@ CREATE TABLE tab_main (a int) PARTITION BY RANGE(a);
ALTER TABLE tab_main ATTACH PARTITION testpub_root FOR VALUES FROM (0) TO (200);
ERROR: cannot attach table "testpub_root" as partition because it is referenced in publication "'testpub8'" EXCEPT clause
DETAIL: The publication EXCEPT clause cannot contain tables that are partitions.
-HINT: Modify the publication's EXCEPT clause using ALTER PUBLICATION ... SET EXCEPT TABLE before attaching the table.
+HINT: Modify the publication's EXCEPT clause using ALTER PUBLICATION ... SET EXCEPT TABLE or DROP EXCEPT TABLE before attaching the table.
RESET client_min_messages;
DROP TABLE testpub_root, testpub_part1, testpub_part2, tab_main;
DROP PUBLICATION testpub8;
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 957b2d183f9..a62fa0ac84d 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -138,8 +138,9 @@ CREATE PUBLICATION testpub_foralltables_excepttable1 FOR ALL TABLES EXCEPT TABLE
ALTER PUBLICATION testpub_foralltables_excepttable SET EXCEPT TABLE testpub_tbl1;
\dRp+ testpub_foralltables_excepttable
--- fail - Dropping EXCEPT table is not supported.
+-- Drop table from the EXCEPT list of a FOR ALL TABLES publication.
ALTER PUBLICATION testpub_foralltables_excepttable DROP EXCEPT TABLE testpub_tbl1;
+\dRp+ testpub_foralltables_excepttable
-- fail - Adding EXCEPT table is not supported.
ALTER PUBLICATION testpub_foralltables_excepttable ADD EXCEPT TABLE testpub_tbl1;
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 786799ed260..a0c15184bd3 100644
--- a/src/test/subscription/t/037_rep_changes_except_table.pl
+++ b/src/test/subscription/t/037_rep_changes_except_table.pl
@@ -166,6 +166,35 @@ $result =
$node_subscriber->safe_psql('postgres', "SELECT count(*) FROM sch1.child1");
is($result, qq(10), 'check replicated inserts on subscriber');
+# Remove sch1.tab1 from the publication's EXCEPT list so that it becomes part
+# of the ALL TABLES publication.
+$node_publisher->safe_psql('postgres',
+ "ALTER PUBLICATION tap_pub_schema DROP EXCEPT TABLE sch1.tab1, sch1.parent, only sch1.parent1");
+
+# Refresh the subscription so the subscriber picks up the updated
+# publication definition and initiates table synchronization.
+$node_subscriber->safe_psql('postgres',
+ "ALTER SUBSCRIPTION tap_sub_schema REFRESH PUBLICATION");
+
+# Wait for initial table sync to finish
+$node_subscriber->wait_for_subscription_sync($node_publisher,
+ 'tap_sub_schema');
+
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM sch1.tab1");
+is($result, qq(20), 'check replicated inserts on subscriber');
+
+# Insert additional rows on the publisher after synchronization.
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO sch1.tab1 VALUES(generate_series(21,30))");
+
+$node_publisher->wait_for_catchup('tap_sub_schema');
+
+# Verify that the new inserts are also replicated.
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM sch1.tab1");
+is($result, qq(30), 'check replicated inserts on subscriber');
+
# cleanup
$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_schema");
$node_publisher->safe_psql(
--
2.43.0
[application/octet-stream] v53-0002-Support-SET-EXCEPT-TABLE-in-ALTER-PUBLICATION.patch (24.7K, 4-v53-0002-Support-SET-EXCEPT-TABLE-in-ALTER-PUBLICATION.patch)
download | inline diff:
From 7df6aa8158313de71fdde409d41ef704e5e88b37 Mon Sep 17 00:00:00 2001
From: Vignesh C <[email protected]>
Date: Sat, 28 Feb 2026 12:12:38 +0530
Subject: [PATCH v53 2/3] Support SET EXCEPT TABLE in ALTER PUBLICATION
Extend ALTER PUBLICATION to support SET EXCEPT TABLE for
publications defined with FOR ALL TABLES.
---
doc/src/sgml/ref/alter_publication.sgml | 29 ++++++---
src/backend/catalog/pg_publication.c | 10 +--
src/backend/commands/publicationcmds.c | 39 ++++++++----
src/backend/commands/tablecmds.c | 3 +-
src/backend/parser/gram.y | 77 +++++++++++++++++++----
src/bin/psql/tab-complete.in.c | 6 +-
src/include/catalog/pg_publication.h | 3 +-
src/test/regress/expected/publication.out | 37 +++++++++++
src/test/regress/sql/publication.sql | 24 +++++++
9 files changed, 188 insertions(+), 40 deletions(-)
diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index 028770f2149..7c28391033e 100644
--- a/doc/src/sgml/ref/alter_publication.sgml
+++ b/doc/src/sgml/ref/alter_publication.sgml
@@ -21,18 +21,24 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-ALTER PUBLICATION <replaceable class="parameter">name</replaceable> ADD <replaceable class="parameter">publication_object</replaceable> [, ...]
-ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET <replaceable class="parameter">publication_object</replaceable> [, ...]
+ALTER PUBLICATION <replaceable class="parameter">name</replaceable> ADD <replaceable class="parameter">publication_add_object</replaceable> [, ...]
+ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET <replaceable class="parameter">publication_set_object</replaceable> [, ...]
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> DROP <replaceable class="parameter">publication_drop_object</replaceable> [, ...]
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> OWNER TO { <replaceable>new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <replaceable>new_name</replaceable>
-<phrase>where <replaceable class="parameter">publication_object</replaceable> is one of:</phrase>
+<phrase>where <replaceable class="parameter">publication_add_object</replaceable> is one of:</phrase>
TABLE <replaceable class="parameter">table_and_columns</replaceable> [, ... ]
TABLES IN SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ... ]
+<phrase>where <replaceable class="parameter">publication_set_object</replaceable> is one of:</phrase>
+
+ EXCEPT TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [, ... ]
+ TABLE <replaceable class="parameter">table_and_columns</replaceable> [, ... ]
+ TABLES IN SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ... ]
+
<phrase>and <replaceable class="parameter">publication_drop_object</replaceable> is one of:</phrase>
TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ... ]
@@ -55,11 +61,12 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
<para>
The first three variants change which tables/schemas are part of the
publication. The <literal>SET</literal> clause will replace the list of
- tables/schemas in the publication with the specified list; the existing
- tables/schemas that were present in the publication will be removed. The
- <literal>ADD</literal> and <literal>DROP</literal> clauses will add and
- remove one or more tables/schemas from the publication. Note that adding
- tables/schemas to a publication that is already subscribed to will require an
+ except tables/tables/schemas in the publication with the specified list; the
+ existing except tables/tables/schemas that were present in the publication
+ will be removed. The <literal>ADD</literal> and <literal>DROP</literal>
+ clauses will add and remove one or more tables/schemas from the publication.
+ Note that adding tables/schemas to a publication that is already subscribed
+ to will require an
<link linkend="sql-altersubscription-params-refresh-publication">
<literal>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</literal></link> action on the
subscribing side in order to become effective. Note also that
@@ -222,6 +229,12 @@ ALTER PUBLICATION mypublication ADD TABLE users (user_id, firstname), department
Change the set of columns published for a table:
<programlisting>
ALTER PUBLICATION mypublication SET TABLE users (user_id, firstname, lastname), TABLE departments;
+</programlisting></para>
+
+ <para>
+ Replace the publication's EXCEPT table list:
+<programlisting>
+ALTER PUBLICATION mypublication SET EXCEPT TABLE users, departments;
</programlisting></para>
<para>
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index e1b1532c464..48570fcff82 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -441,7 +441,7 @@ attnumstoint2vector(Bitmapset *attrs)
*/
ObjectAddress
publication_add_relation(Oid pubid, PublicationRelInfo *pri,
- bool if_not_exists)
+ bool if_not_exists, bool is_alter)
{
Relation rel;
HeapTuple tup;
@@ -545,10 +545,12 @@ publication_add_relation(Oid pubid, PublicationRelInfo *pri,
/*
* Relations excluded via the EXCEPT clause do not need explicit
- * invalidation as CreatePublication() function invalidates all relations
- * as part of defining a FOR ALL TABLES publication.
+ * invalidation during CREATE PUBLICATION, as CreatePublication() function
+ * invalidates all relations as part of defining a FOR ALL TABLES
+ * publication. For ALTER PUBLICATION, explicit invalidation is still
+ * required.
*/
- if (!pri->except)
+ if (!pri->except || is_alter)
{
/*
* Invalidate relcache so that publication info is rebuilt.
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index 6a3ca4751fa..166fe3b0c3d 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -1272,15 +1272,24 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
PublicationDropTables(pubid, rels, false);
else /* AP_SetObjects */
{
- List *oldrelids = GetIncludedPublicationRelations(pubid,
- PUBLICATION_PART_ROOT);
+ bool isexcept = pubform->puballtables;
+ List *oldrelids;
List *delrels = NIL;
ListCell *oldlc;
- TransformPubWhereClauses(rels, queryString, pubform->pubviaroot);
+ if (isexcept)
+ oldrelids = GetExcludedPublicationTables(pubid,
+ PUBLICATION_PART_ROOT);
+ else
+ {
+ oldrelids = GetIncludedPublicationRelations(pubid,
+ PUBLICATION_PART_ROOT);
- CheckPubRelationColumnList(stmt->pubname, rels, publish_schema,
- pubform->pubviaroot);
+ TransformPubWhereClauses(rels, queryString, pubform->pubviaroot);
+
+ CheckPubRelationColumnList(stmt->pubname, rels, publish_schema,
+ pubform->pubviaroot);
+ }
/*
* To recreate the relation list for the publication, look for
@@ -1488,7 +1497,7 @@ AlterPublicationSchemas(AlterPublicationStmt *stmt,
*/
static void
CheckAlterPublication(AlterPublicationStmt *stmt, HeapTuple tup,
- List *tables, List *schemaidlist)
+ List *tables, List *excepttables, List *schemaidlist)
{
Form_pg_publication pubform = (Form_pg_publication) GETSTRUCT(tup);
@@ -1546,6 +1555,14 @@ CheckAlterPublication(AlterPublicationStmt *stmt, HeapTuple tup,
NameStr(pubform->pubname)),
errdetail("Tables or sequences cannot be added to or dropped from FOR ALL SEQUENCES publications."));
}
+
+ /* Check that user is allowed to manipulate the publication tables. */
+ if (excepttables && !pubform->puballtables)
+ ereport(ERROR,
+ errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("publication \"%s\" is not defined as FOR ALL TABLES",
+ NameStr(pubform->pubname)),
+ errdetail("EXCEPT Tables cannot be added to publications that are not defined as FOR ALL TABLES."));
}
/*
@@ -1591,10 +1608,8 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt)
ObjectsInPublicationToOids(stmt->pubobjects, pstate, &relations,
&exceptrelations, &schemaidlist);
- /* EXCEPT clause is not supported with ALTER PUBLICATION */
- Assert(exceptrelations == NIL);
-
- CheckAlterPublication(stmt, tup, relations, schemaidlist);
+ CheckAlterPublication(stmt, tup, relations, exceptrelations,
+ schemaidlist);
heap_freetuple(tup);
@@ -1615,6 +1630,7 @@ AlterPublication(ParseState *pstate, AlterPublicationStmt *stmt)
errmsg("publication \"%s\" does not exist",
stmt->pubname));
+ relations = list_concat(relations, exceptrelations);
AlterPublicationTables(stmt, tup, relations, pstate->p_sourcetext,
schemaidlist != NIL);
AlterPublicationSchemas(stmt, tup, schemaidlist);
@@ -1953,7 +1969,8 @@ PublicationAddTables(Oid pubid, List *rels, bool if_not_exists,
aclcheck_error(ACLCHECK_NOT_OWNER, get_relkind_objtype(rel->rd_rel->relkind),
RelationGetRelationName(rel));
- obj = publication_add_relation(pubid, pub_rel, if_not_exists);
+ obj = publication_add_relation(pubid, pub_rel, if_not_exists,
+ (stmt != NULL));
if (stmt)
{
EventTriggerCollectSimpleCommand(obj, InvalidObjectAddress,
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index c0104f084fb..cedbdd38570 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -20394,7 +20394,8 @@ ATExecAttachPartition(List **wqueue, Relation rel, PartitionCmd *cmd,
list_length(exceptpuboids),
RelationGetRelationName(attachrel),
pubnames.data),
- errdetail("The publication EXCEPT clause cannot contain tables that are partitions."));
+ errdetail("The publication EXCEPT clause cannot contain tables that are partitions."),
+ errhint("Modify the publication's EXCEPT clause using ALTER PUBLICATION ... SET EXCEPT TABLE before attaching the table."));
}
list_free(exceptpuboids);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index f4d49b6018d..0544908fcb9 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -207,7 +207,7 @@ static void preprocess_pub_all_objtype_list(List *all_objects_list,
bool *all_tables,
bool *all_sequences,
core_yyscan_t yyscanner);
-static void preprocess_pubobj_list(List *pubobjspec_list,
+static bool preprocess_pubobj_list(List *pubobjspec_list,
core_yyscan_t yyscanner);
static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
@@ -581,7 +581,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
Bit ConstBit BitWithLength BitWithoutLength
%type <str> character
%type <str> extract_arg
-%type <boolean> opt_varying opt_timezone opt_no_inherit
+%type <boolean> opt_varying opt_timezone opt_no_inherit opt_except
%type <ival> Iconst SignedIconst
%type <str> Sconst comment_text notify_payload
@@ -10830,16 +10830,28 @@ CreatePublicationStmt:
}
| CREATE PUBLICATION name FOR pub_obj_list opt_definition
{
+ bool has_except_table;
CreatePublicationStmt *n = makeNode(CreatePublicationStmt);
n->pubname = $3;
n->options = $6;
n->pubobjects = (List *) $5;
- preprocess_pubobj_list(n->pubobjects, yyscanner);
+ has_except_table = preprocess_pubobj_list(n->pubobjects,
+ yyscanner);
+ if (has_except_table)
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("EXCEPT TABLE clause allowed only for ALL TABLES PUBLICATION"));
+
$$ = (Node *) n;
}
;
+opt_except:
+ EXCEPT { $$ = true; }
+ | /*EMPTY*/ { $$ = false; }
+ ;
+
/*
* FOR TABLE and FOR TABLES IN SCHEMA specifications
*
@@ -10854,14 +10866,14 @@ CreatePublicationStmt:
* relation_expr here.
*/
PublicationObjSpec:
- TABLE relation_expr opt_column_list OptWhereClause
+ opt_except TABLE relation_expr opt_column_list OptWhereClause
{
$$ = makeNode(PublicationObjSpec);
- $$->pubobjtype = PUBLICATIONOBJ_TABLE;
+ $$->pubobjtype = ($1) ? PUBLICATIONOBJ_EXCEPT_TABLE : PUBLICATIONOBJ_TABLE;
$$->pubtable = makeNode(PublicationTable);
- $$->pubtable->relation = $2;
- $$->pubtable->columns = $3;
- $$->pubtable->whereClause = $4;
+ $$->pubtable->relation = $3;
+ $$->pubtable->columns = $4;
+ $$->pubtable->whereClause = $5;
}
| TABLES IN_P SCHEMA ColId
{
@@ -11010,11 +11022,18 @@ AlterPublicationStmt:
}
| ALTER PUBLICATION name ADD_P pub_obj_list
{
+ bool has_except_table = false;
AlterPublicationStmt *n = makeNode(AlterPublicationStmt);
n->pubname = $3;
n->pubobjects = $5;
- preprocess_pubobj_list(n->pubobjects, yyscanner);
+ has_except_table = preprocess_pubobj_list(n->pubobjects,
+ yyscanner);
+ if (has_except_table)
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("EXCEPT TABLE clause allowed only for SET clause"));
+
n->action = AP_AddObjects;
$$ = (Node *) n;
}
@@ -11030,11 +11049,18 @@ AlterPublicationStmt:
}
| ALTER PUBLICATION name DROP pub_obj_list
{
+ bool has_except_table = false;
AlterPublicationStmt *n = makeNode(AlterPublicationStmt);
n->pubname = $3;
n->pubobjects = $5;
- preprocess_pubobj_list(n->pubobjects, yyscanner);
+ has_except_table = preprocess_pubobj_list(n->pubobjects,
+ yyscanner);
+ if (has_except_table)
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("EXCEPT TABLE clause allowed only for SET clause"));
+
n->action = AP_DropObjects;
$$ = (Node *) n;
}
@@ -19880,16 +19906,19 @@ preprocess_pub_all_objtype_list(List *all_objects_list, List **pubobjects,
/*
* Process pubobjspec_list to check for errors in any of the objects and
* convert PUBLICATIONOBJ_CONTINUATION into appropriate PublicationObjSpecType.
+ *
+ * Return true if an EXCEPT table is found.
*/
-static void
+static bool
preprocess_pubobj_list(List *pubobjspec_list, core_yyscan_t yyscanner)
{
ListCell *cell;
PublicationObjSpec *pubobj;
PublicationObjSpecType prevobjtype = PUBLICATIONOBJ_CONTINUATION;
+ bool foundexcepttable = false;
if (!pubobjspec_list)
- return;
+ return false;
pubobj = (PublicationObjSpec *) linitial(pubobjspec_list);
if (pubobj->pubobjtype == PUBLICATIONOBJ_CONTINUATION)
@@ -19906,7 +19935,8 @@ preprocess_pubobj_list(List *pubobjspec_list, core_yyscan_t yyscanner)
if (pubobj->pubobjtype == PUBLICATIONOBJ_CONTINUATION)
pubobj->pubobjtype = prevobjtype;
- if (pubobj->pubobjtype == PUBLICATIONOBJ_TABLE)
+ if (pubobj->pubobjtype == PUBLICATIONOBJ_TABLE ||
+ pubobj->pubobjtype == PUBLICATIONOBJ_EXCEPT_TABLE)
{
/* relation name or pubtable must be set for this type of object */
if (!pubobj->name && !pubobj->pubtable)
@@ -19925,6 +19955,25 @@ preprocess_pubobj_list(List *pubobjspec_list, core_yyscan_t yyscanner)
pubobj->pubtable = pubtable;
pubobj->name = NULL;
}
+
+ if (pubobj->pubobjtype == PUBLICATIONOBJ_EXCEPT_TABLE)
+ {
+ foundexcepttable = true;
+
+ /* WHERE clause is not allowed on an except table */
+ if (pubobj->pubtable->whereClause)
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("WHERE clause not allowed for except table"),
+ parser_errposition(pubobj->location));
+
+ /* Column list is not allowed on a except table */
+ if (pubobj->pubtable && pubobj->pubtable->columns)
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("column specification not allowed for except table"),
+ parser_errposition(pubobj->location));
+ }
}
else if (pubobj->pubobjtype == PUBLICATIONOBJ_TABLES_IN_SCHEMA ||
pubobj->pubobjtype == PUBLICATIONOBJ_TABLES_IN_CUR_SCHEMA)
@@ -19960,6 +20009,8 @@ preprocess_pubobj_list(List *pubobjspec_list, core_yyscan_t yyscanner)
prevobjtype = pubobj->pubobjtype;
}
+
+ return foundexcepttable;
}
/*----------
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 8927f7e6f22..f45f3b76523 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2318,9 +2318,11 @@ match_previous_words(int pattern_id,
/* ALTER PUBLICATION <name> DROP */
else if (Matches("ALTER", "PUBLICATION", MatchAny, "DROP"))
COMPLETE_WITH("TABLES IN SCHEMA", "TABLE");
- /* ALTER PUBLICATION <name> SET */
+ /* ALTER PUBLICATION <name> SET */
else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET"))
- COMPLETE_WITH("(", "TABLES IN SCHEMA", "TABLE");
+ COMPLETE_WITH("(", "EXCEPT", "TABLES IN SCHEMA", "TABLE");
+ else if (Matches("ALTER", "PUBLICATION", MatchAny, "SET", "EXCEPT"))
+ COMPLETE_WITH("TABLE");
else if (Matches("ALTER", "PUBLICATION", MatchAny, "ADD|DROP|SET", "TABLES", "IN", "SCHEMA"))
COMPLETE_WITH_QUERY_PLUS(Query_for_list_of_schemas
" AND nspname NOT LIKE E'pg\\\\_%%'",
diff --git a/src/include/catalog/pg_publication.h b/src/include/catalog/pg_publication.h
index a9f89e89006..b012e753b64 100644
--- a/src/include/catalog/pg_publication.h
+++ b/src/include/catalog/pg_publication.h
@@ -199,7 +199,8 @@ 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,
- bool if_not_exists);
+ bool if_not_exists,
+ bool is_alter);
extern Bitmapset *pub_collist_validate(Relation targetrel, List *columns);
extern ObjectAddress publication_add_schema(Oid pubid, Oid schemaid,
bool if_not_exists);
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 85da81a1f27..3c70c51ddec 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -126,6 +126,16 @@ ALTER PUBLICATION testpub_fortable SET TABLES IN SCHEMA pub_test;
Tables from schemas:
"pub_test"
+-- fail - can't add an EXCEPT TABLE to 'FOR TABLE' publication
+ALTER PUBLICATION testpub_fortable ADD EXCEPT TABLE testpub_tbl1;
+ERROR: EXCEPT TABLE clause allowed only for SET clause
+-- fail - can't drop an EXCEPT TABLE from 'FOR TABLE' publication
+ALTER PUBLICATION testpub_fortable DROP EXCEPT TABLE testpub_tbl1;
+ERROR: EXCEPT TABLE clause allowed only for SET clause
+-- fail - can't set an EXCEPT TABLE to 'FOR TABLE' publication
+ALTER PUBLICATION testpub_fortable SET EXCEPT TABLE testpub_tbl1;
+ERROR: publication "testpub_fortable" is not defined as FOR ALL TABLES
+DETAIL: EXCEPT Tables cannot be added to publications that are not defined as FOR ALL TABLES.
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub_forschema FOR TABLES IN SCHEMA pub_test;
-- should be able to create publication with schema and table of the same
@@ -213,6 +223,16 @@ Not-null constraints:
regress_publication_user | t | f | t | t | f | f | none | f
(1 row)
+-- fail - can't add an EXCEPT TABLE to schema publication
+ALTER PUBLICATION testpub_forschema ADD EXCEPT TABLE pub_test.testpub_nopk;
+ERROR: EXCEPT TABLE clause allowed only for SET clause
+-- fail - can't drop an EXCEPT TABLE from schema publication
+ALTER PUBLICATION testpub_forschema DROP EXCEPT TABLE pub_test.testpub_nopk;
+ERROR: EXCEPT TABLE clause allowed only for SET clause
+-- fail - can't set an EXCEPT TABLE to schema publication
+ALTER PUBLICATION testpub_forschema SET EXCEPT TABLE pub_test.testpub_nopk;
+ERROR: publication "testpub_forschema" is not defined as FOR ALL TABLES
+DETAIL: EXCEPT Tables cannot be added to publications that are not defined as FOR ALL TABLES.
---------------------------------------------
-- EXCEPT TABLE tests for normal tables
---------------------------------------------
@@ -255,6 +275,22 @@ Except Publications:
"testpub_foralltables_excepttable"
"testpub_foralltables_excepttable1"
+-- Replace the publication EXCEPT table list with a specific EXCEPT table.
+ALTER PUBLICATION testpub_foralltables_excepttable SET EXCEPT TABLE testpub_tbl1;
+\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"
+
+-- fail - Dropping EXCEPT table is not supported.
+ALTER PUBLICATION testpub_foralltables_excepttable DROP EXCEPT TABLE testpub_tbl1;
+ERROR: EXCEPT TABLE clause allowed only for SET clause
+-- fail - Adding EXCEPT table is not supported.
+ALTER PUBLICATION testpub_foralltables_excepttable ADD EXCEPT TABLE testpub_tbl1;
+ERROR: EXCEPT TABLE clause allowed only for SET clause
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;
@@ -344,6 +380,7 @@ CREATE TABLE tab_main (a int) PARTITION BY RANGE(a);
ALTER TABLE tab_main ATTACH PARTITION testpub_root FOR VALUES FROM (0) TO (200);
ERROR: cannot attach table "testpub_root" as partition because it is referenced in publication "'testpub8'" EXCEPT clause
DETAIL: The publication EXCEPT clause cannot contain tables that are partitions.
+HINT: Modify the publication's EXCEPT clause using ALTER PUBLICATION ... SET EXCEPT TABLE before attaching the table.
RESET client_min_messages;
DROP TABLE testpub_root, testpub_part1, testpub_part2, tab_main;
DROP PUBLICATION testpub8;
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 507028bed32..957b2d183f9 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -74,6 +74,13 @@ ALTER PUBLICATION testpub_fortable DROP TABLES IN SCHEMA pub_test;
ALTER PUBLICATION testpub_fortable SET TABLES IN SCHEMA pub_test;
\dRp+ testpub_fortable
+-- fail - can't add an EXCEPT TABLE to 'FOR TABLE' publication
+ALTER PUBLICATION testpub_fortable ADD EXCEPT TABLE testpub_tbl1;
+-- fail - can't drop an EXCEPT TABLE from 'FOR TABLE' publication
+ALTER PUBLICATION testpub_fortable DROP EXCEPT TABLE testpub_tbl1;
+-- fail - can't set an EXCEPT TABLE to 'FOR TABLE' publication
+ALTER PUBLICATION testpub_fortable SET EXCEPT TABLE testpub_tbl1;
+
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub_forschema FOR TABLES IN SCHEMA pub_test;
-- should be able to create publication with schema and table of the same
@@ -105,6 +112,13 @@ SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_forall
\d+ testpub_tbl2
\dRp+ testpub_foralltables
+-- fail - can't add an EXCEPT TABLE to schema publication
+ALTER PUBLICATION testpub_forschema ADD EXCEPT TABLE pub_test.testpub_nopk;
+-- fail - can't drop an EXCEPT TABLE from schema publication
+ALTER PUBLICATION testpub_forschema DROP EXCEPT TABLE pub_test.testpub_nopk;
+-- fail - can't set an EXCEPT TABLE to schema publication
+ALTER PUBLICATION testpub_forschema SET EXCEPT TABLE pub_test.testpub_nopk;
+
---------------------------------------------
-- EXCEPT TABLE tests for normal tables
---------------------------------------------
@@ -120,6 +134,16 @@ CREATE PUBLICATION testpub_foralltables_excepttable1 FOR ALL TABLES EXCEPT TABLE
-- in the EXCEPT clause
\d testpub_tbl1
+-- Replace the publication EXCEPT table list with a specific EXCEPT table.
+ALTER PUBLICATION testpub_foralltables_excepttable SET EXCEPT TABLE testpub_tbl1;
+\dRp+ testpub_foralltables_excepttable
+
+-- fail - Dropping EXCEPT table is not supported.
+ALTER PUBLICATION testpub_foralltables_excepttable DROP EXCEPT TABLE testpub_tbl1;
+
+-- fail - Adding EXCEPT table is not supported.
+ALTER PUBLICATION testpub_foralltables_excepttable ADD EXCEPT TABLE testpub_tbl1;
+
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;
--
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], [email protected], [email protected]
Subject: Re: Skipping schema changes in publication
In-Reply-To: <CALDaNm32g7c323M4mgZ5Wn8sbYp_=uQ6G_u0f9qfBCzuHP8jgQ@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