MIME-Version: 1.0
References: 
 <CABdArM5sw4Q1ZU8HGdo4BSc1A_+8xtUNq17j6wcir=yMUy19Cg@mail.gmail.com>
 <CAHut+PvnH8QHa035Skoh1e9jm_H08DO9fQ=F-NAMsEpYf0RZ2Q@mail.gmail.com>
 <CAJpy0uDu0LcNXcZCP0cR_LHqo+sau33KwPFHemmGVYf_JTxRBQ@mail.gmail.com>
 <CAA4eK1KbCWBmEXH-rhQjKgNwq=onZp8vRR-QkRhPpbKwL-kQdw@mail.gmail.com>
 <CAHut+Pvj4=GWoJEd4EBdp4pi6KxXQ46ioW=PV+=UktiXr2gCvg@mail.gmail.com>
 <CABdArM75F0A+DGP8AOt-_b_XREX40rvFid1jRjnr_+S5b51t8Q@mail.gmail.com>
 <CAJpy0uDTshb243L5yEYWB3uO-JrwSoRqQDNovh03K2GZuuR3Pg@mail.gmail.com>
 <CAJpy0uDy97ULmJUwPacAzc5u2seuPK6RXgCS1rnsW2MfR4eeSw@mail.gmail.com>
 <CABdArM6oXXXSAxxXFktTTfBf4kyxJCvdNtTbUZtSwJ=CepN+Xw@mail.gmail.com>
 <CAJpy0uBqM+fq7+g1ZRATuY16H10MFP9i25wfFCYCE5MGu+PE0Q@mail.gmail.com>
 <CABdArM4uKaS1coCQj6rAwMmHqU_cCJyEWNic-PFF1_ZjDDM82Q@mail.gmail.com>
 <CAHut+Pu5VNakf5JAhKM7T-P_q37eN1Qgv5nvZUe+8RAAT41y4g@mail.gmail.com>
 <CABdArM6WTm2gP4pcjVdHT1Nx6zdLKTq7nLPUkzZOhprc95a6Rw@mail.gmail.com>
 <CAHut+Ptthc1X-UA8-6zG-iFeCDuoNd+oJRBZ1eCnJ9RNOXjfBQ@mail.gmail.com>
 <CABdArM79m7-CTf6KGGGU2QBydFtuonGgfxRSqk-vhwTsH8z1ow@mail.gmail.com>
 <CAHut+PuiK_Pa=BkSgBxYzqf1PYh+mcUcUQCr8r1e69-y1r+hhw@mail.gmail.com>
 <CABdArM7rH+3GekRgufEOwrJxUeQk=LB182CQwJD35e0oN7q8ZA@mail.gmail.com>
 <CAHut+PtP1mbQT==xo=G-37dV9Lt3q7YO2eMEAqSbZuszy93LcQ@mail.gmail.com>
 <CABdArM6XRpUR86a-daYMXFqhH-spJQiQAVfJ2+GFiAqeup2jyA@mail.gmail.com>
 <CAHut+PuDB=doKUSf94cs8hOo2d5mOc+GxxPOf57xGhdE6e-Aog@mail.gmail.com>
 <CABdArM76MZ4dYC87zCv67fVY9t5aFLd2nD5tNBxFFizESyBOSg@mail.gmail.com>
 <CAN4CZFM9xXkw8FvHOTan+m4rASSoxbJTWXMbZ_-v2M-84w5Wzg@mail.gmail.com>
 <CABdArM5wj0dtieSVMvzvJDh5fJ-GGwh3PxV+E_MbPEntCk4KRg@mail.gmail.com>
 <CAN4CZFMaB1a89NZjRT9bWFZ8-XN02amye_fnYAg0NM_23DAwQw@mail.gmail.com>
 <CABdArM5qHR=Zmyfqs6AuCn-Crj=_w3CXA-1K9rQ53_zismXCNQ@mail.gmail.com>
 <CAHut+Pu0-A92MyRxFCLNQFYgAKGrKbYChG_p4ARogEfbAtMm8A@mail.gmail.com>
 <CABdArM4fUnAZ10uyT61tNYu-WWHBiBOz75at9L90jBKLH5YR8g@mail.gmail.com>
 <CAHut+Pv-GA1oGa6+nwn_5AVhBg8NuJThQVUzqhQPXJge49jnew@mail.gmail.com>
 <CAA4eK1LMooRmK3_w_Zo-g3ftB7mDDkLitXjiEjgZY=0LTeZRoQ@mail.gmail.com>
 <CABdArM5j8gX_xYDU6_aPEOuPAiCGtF8QyrpHeqZXQDsQ_wpJKA@mail.gmail.com>
 <CAHut+PvAzsQA7BPKFC2dKWAr_kpAdHhLAaGOJQmZ_7=0paZCZA@mail.gmail.com>
 <CABdArM4peXa8W9AYw6OHWmBuP4kkN3e01TcrUq_arWQLacoiNw@mail.gmail.com>
 <CAHut+PucwGJ4Tyr9kRF4zNstAM_GGheU+-55u3JrKW8epKGSEQ@mail.gmail.com>
 <CABdArM48_Dm3cEcrzb0MRuaMoKhCtzbQE7mX7GyaoVOLc9fNXQ@mail.gmail.com>
 <CAHut+PsN+_2Me8khvk4bQ1+U8B73YykHv7qnQ7V2qtucaf2c4g@mail.gmail.com>
 <CABdArM6Vv9Z4Dyj3+Wvdcq_8AAL2PshoHObZhpi5aBRkRn1eQA@mail.gmail.com>
 <CAJpy0uCBkS2MyRzz0WSxg6Ch6Vi9-vNLCWLmEtcu_WwHWP7oiw@mail.gmail.com>
 <CABdArM6Ycpng+SYh733ZfZaFFDUkZmjgxh35+6utx03HOTvH2A@mail.gmail.com>
 <CAJpy0uD=deQJxbEuug-fbhvG6K47w1k_dwkZvbhAaBbBAgUPeg@mail.gmail.com>
 <CABdArM4PzvWHyqG-tdZpf9U+0_-23n8NXJKAPW+_NADOqU42xA@mail.gmail.com>
 <CAJpy0uAs2a2R+P=7cPo3iW4SgMUQVMf-1WwmPgJ5ttZErgzZvw@mail.gmail.com>
 <CABdArM7mJsqFCGe2AyaS-E6n7rk4SoDTSKD+D3hUNO=UaDPStA@mail.gmail.com>
In-Reply-To: 
 <CABdArM7mJsqFCGe2AyaS-E6n7rk4SoDTSKD+D3hUNO=UaDPStA@mail.gmail.com>
From: shveta malik <shveta.malik@gmail.com>
Date: Mon, 6 Jul 2026 08:41:53 +0530
Message-ID: 
 <CAJpy0uAh_85EnTdSo-3H0jb-L_THz0Vag89L6mvL2M-Ha1_g8g@mail.gmail.com>
Subject: Re: Support EXCEPT for TABLES IN SCHEMA publications
To: Nisha Moond <nisha.moond412@gmail.com>
Cc: Peter Smith <smithpb2250@gmail.com>,
 Amit Kapila <amit.kapila16@gmail.com>,
	Zsolt Parragi <zsolt.parragi@percona.com>,
 pgsql-hackers@lists.postgresql.org,
	shveta malik <shveta.malik@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAJpy0uAh_85EnTdSo-3H0jb-L_THz0Vag89L6mvL2M-Ha1_g8g%40mail.gmail.com>
Precedence: bulk

On Fri, Jul 3, 2026 at 10:15=E2=80=AFPM Nisha Moond <nisha.moond412@gmail.c=
om> wrote:
>
> On Thu, Jul 2, 2026 at 2:19=E2=80=AFPM shveta malik <shveta.malik@gmail.c=
om> wrote:
> >
> > A few comments:
> >
> > 1)
> > postgres=3D# alter publication pub1 add table s2.t1;
> > ERROR:  table "s2.t1" cannot be added because it is excluded from
> > publication "pub1"
> >
> > It will be good to add hint in above error.
> >
> > Suggestion:
> > ERROR: cannot add table "s2.t1" to publication "pub1"
> > DETAIL: Table "s2.t1" is currently in EXCEPT list
> > HINT: Change EXCEPT list using ALTER PUBLICATION ..SET TABLES IN
> > SCHEMA .. EXCEPT
> >
> > Please rephrase above as needed. Look earlier such messages/hints
> > already added for ALL TABLES.
> >
>
> Updated the error as suggested, with a slight change in DETAIL message.
>
> > From the user's perspective, it seems slightly odd that if he has
> > excluded a table from publication, he can not add it back using 'add
> > table'. Was this point discussed earlier?
> >
>
> This hasn't been discussed yet.
>
> I kept the current behavior, as IMO, it is hard to tell whether the
> user is trying to re-include a table or whether the ADD is unintended
> and should be blocked. To avoid ambiguity, I think EXCEPT entries
> should be modified only through the EXCEPT (...) clause itself, such
> as ALTER PUBLICATION ... ADD/SET ... EXCEPT(TABLE ...), or by omitting
> EXCEPT in the SET case.
>
> This would also keep the behavior of ADD TABLE and DROP TABLE aligned,
> as both would operate only on the include list without touching the
> EXCEPT list.
> I think this also aligns with FOR ALL TABLES, where ADD TABLE is
> disallowed, so the EXCEPT list can only be modified through SET ..
> EXCEPT (...).
>
> Thoughts?

Okay. Makes sense.

>
> > 2)
> > Currently:
> > postgres=3D# alter publication pub1 drop table s2.t1;
> > ERROR:  relation "t1" is not part of the publication
> >
> > s2.t1 is EXCEPT list here.
> >
> > Should we have below error for this case rather than above generic
> > error, it will be more intuitive for user. Plus both add and drop
> > table commands will have similar error-style for EXCEPT list.
> > Thoughts?
> >
> > ERROR: cannot drop table "s2.t1" from publication "pub1"
> > DETAIL: Table "s2.t1" is currently in EXCEPT list
> > HINT: Change EXCEPT list using ALTER PUBLICATION ..SET TABLES IN
> > SCHEMA .. EXCEPT
> >
>
> Makes sense. Fixed.
>
> > 3)
> > Please see below test:
> >
> > --Setting Except list for pub1 which has all tables of s2 included:
> > alter publication pub1 set tables in schema s2 EXCEPT ( TABLE t1, t2);
> >
> > --pg_publication_rel entries:
> > pubname | schema_name | table_name | prexcept
> > --------+-------------+------------+---------
> > pub1    | s2          | t1         | t
> > pub1    | s2          | t2         | t
> >
> >
> > --Then added a partition:
> > alter publication pub1 add table s2.tab_part_1_p2;
> >
> > --pg_publication_rel entries:
> > pubname | schema_name |  table_name   | prexcept
> > --------+-------------+---------------+---------
> > pub1    | s2          | t1            | t
> > pub1    | s2          | t2            | t
> > pub1    | s2          | tab_part_1_p2 | f
> >
> >
> > --Then changed pub1 to exclude tab_root (parent of tab_part_1_p2) as we=
ll:
> >
> > alter publication pub1 set tables in schema s2 EXCEPT ( TABLE t1, t2, t=
ab_root);
> >
> > --pg_publication_rel entries:
> > pubname | schema_name | table_name | prexcept
> > --------+-------------+------------+---------
> > pub1    | s2          | t1         | t
> > pub1    | s2          | t2         | t
> > pub1    | s2          | tab_root   | t
> >
> > Note theoutput above, tab_part_1_p2 is removed internally as part of
> > earlier command since its parent tab_root is now excluded.
> >
> > --Now add parition back, it allowed to add it:
> > alter publication pub1 add table s2.tab_part_1_p2;
> >
> > pg_publication_rel entries:
> > pubname | schema_name |  table_name   | prexcept
> > --------+-------------+---------------+---------
> > pub1    | s2          | t1            | t
> > pub1    | s2          | t2            | t
> > pub1    | s2          | tab_root      | t
> > pub1    | s2          | tab_part_1_p2 | f
> >
> >
> > FOR ALL TABLES publicaiton, if root is excluded, there is no way
> > publication can have parition included independently. Same should hold
> > true here. Thoughts?
>
> Right, based on the discussion at [1], since we follow "all partitions
> are excluded if the root is excluded", a partition child should not be
> allowed to be added when its partition root is already excluded from
> the publication.
> Fixed. Such an operation will now raise an error:
>
> postgres=3D# alter publication pub2 add table t_part_p1;
> ERROR:  cannot add table "public.t_part_p1" to publication "pub2"
> DETAIL:  Partition ancestor "public.t_part" of table
> "public.t_part_p1" is currently listed in the EXCEPT clause of the
> publication.
> HINT:  Change EXCEPT list using ALTER PUBLICATION ... SET TABLES IN
> SCHEMA ... EXCEPT.
> ~~~

Okay, looks good.

thanks
Shveta