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>
 <CAJpy0uAuL7SqowL7T6HCELuVUr5ubwoGsjV1SQQRCXGme5L=pA@mail.gmail.com>
 <CAJpy0uA0w9EERLvE5z8FMy6RxdA26FjcjA3mHYcr1ydz0XnjGw@mail.gmail.com>
 <CAA4eK1KT=6zT9dy8iF0320VFHZ4Gs9=zknuXA92Wuj4tsEU0ow@mail.gmail.com>
In-Reply-To: 
 <CAA4eK1KT=6zT9dy8iF0320VFHZ4Gs9=zknuXA92Wuj4tsEU0ow@mail.gmail.com>
From: Nisha Moond <nisha.moond412@gmail.com>
Date: Wed, 8 Jul 2026 20:52:45 +0530
Message-ID: 
 <CABdArM5YZ2=toaLvYAMXue9j7eepqUVyA5gv9Gcya_nGZJ_e7g@mail.gmail.com>
Subject: Re: Support EXCEPT for TABLES IN SCHEMA publications
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: shveta malik <shveta.malik@gmail.com>,
 Peter Smith <smithpb2250@gmail.com>,
	Zsolt Parragi <zsolt.parragi@percona.com>,
 pgsql-hackers@lists.postgresql.org,
	shveta malik <shvetamalik@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CABdArM5YZ2%3DtoaLvYAMXue9j7eepqUVyA5gv9Gcya_nGZJ_e7g%40mail.gmail.com>
Precedence: bulk

On Wed, Jul 8, 2026 at 11:58=E2=80=AFAM Amit Kapila <amit.kapila16@gmail.co=
m> wrote:
>
> On Tue, Jul 7, 2026 at 4:13=E2=80=AFPM shveta malik <shveta.malik@gmail.c=
om> wrote:
> >
> > On Tue, Jul 7, 2026 at 2:31=E2=80=AFPM shveta malik <shveta.malik@gmail=
.com> wrote:
> > >
> > > On Fri, Jul 3, 2026 at 10:15=E2=80=AFPM Nisha Moond <nisha.moond412@g=
mail.com> wrote:
> > > >
> > > >
> > > > 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.
> > > > ~~~
> > >
> > > Thanks. Do you think we should restrict this case too? To me, this
> > > seems like another variation of the above where conflicting entries
> > > are given at the time of sub-creation itself. The root is excluded bu=
t
> > > parittion is included .
> > >
> > > postgres=3D# create publication pub1 for table s2.tab_part_1, tables =
in
> > > schema s2 except (table tab_root);
> > > CREATE PUBLICATION
> > > postgres=3D# \dRp+
> > >                                                       Publication pub=
1
> > >  Owner  | All tables | All sequences | Inserts | Updates | Deletes |
> > > Truncates | Generated columns | Via root | Description
> > > --------+------------+---------------+---------+---------+---------+-=
----------+-------------------+----------+-------------
> > >  shveta | f          | f             | t       | t       | t       | =
t
> > >         | none              | f        |
> > > Tables:
> > >     "s2.tab_part_1"
> > > Tables from schemas:
> > >     "s2"
> > > Except tables:
> > >     "s2.tab_root"
> > >
> >
> > Since alter is blocked for above case while create-sub is not,
> >
>
> If a combination is not supported in ALTER, we should disallow the
> same in CREATE as well. So, +1 to restrict the case you reported.
>

Fixed in v19.

> > I had a
> > look at the new check added in check_publication_add_relation
> >
> > a) The check introduced in this function for partitions/ancestors is
> > sufficient for cases where an except-list is already present, but not
> > for cases where we are in the process of adding one. Thus the
> > create-sub scenario above is not covered by this.
> >
> > b) Also the new check in check_publication_add_relation() gets and
> > traverses complete list of ancestors
> >
> > + List    *ancestors =3D get_partition_ancestors(relid);
> > +
> > + foreach_oid(ancestor, ancestors)
> >
> > Isn't getting root (llast_oid(ancestors)) and checking that alone in
> > EXCEPT list will suffice? Or am I missing something?
> >
>
> I think this should be possible because we allow only root tables in
> the EXCEPT list. If so, and we are planning to change as per your
> suggestion then we should add a comment as to why checking only last
> is okay here.
>

Agreed; I implemented the suggested optimization and added comments
explaining why checking only the last element is sufficient.

--
Thanks,
Nisha