public inbox for [email protected]
help / color / mirror / Atom feedFrom: Amit Kapila <[email protected]>
To: shveta malik <[email protected]>
Cc: Nisha Moond <[email protected]>
Cc: Peter Smith <[email protected]>
Cc: Zsolt Parragi <[email protected]>
Cc: [email protected]
Cc: shveta malik <[email protected]>
Subject: Re: Support EXCEPT for TABLES IN SCHEMA publications
Date: Wed, 8 Jul 2026 11:58:00 +0530
Message-ID: <CAA4eK1KT=6zT9dy8iF0320VFHZ4Gs9=zknuXA92Wuj4tsEU0ow@mail.gmail.com> (raw)
In-Reply-To: <CAJpy0uA0w9EERLvE5z8FMy6RxdA26FjcjA3mHYcr1ydz0XnjGw@mail.gmail.com>
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>
On Tue, Jul 7, 2026 at 4:13 PM shveta malik <[email protected]> wrote:
>
> On Tue, Jul 7, 2026 at 2:31 PM shveta malik <[email protected]> wrote:
> >
> > On Fri, Jul 3, 2026 at 10:15 PM Nisha Moond <[email protected]> wrote:
> > >
> > >
> > > postgres=# 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 but
> > parittion is included .
> >
> > postgres=# create publication pub1 for table s2.tab_part_1, tables in
> > schema s2 except (table tab_root);
> > CREATE PUBLICATION
> > postgres=# \dRp+
> > Publication pub1
> > 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.
> 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 = 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.
--
With Regards,
Amit Kapila.
view thread (67+ 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]
Subject: Re: Support EXCEPT for TABLES IN SCHEMA publications
In-Reply-To: <CAA4eK1KT=6zT9dy8iF0320VFHZ4Gs9=zknuXA92Wuj4tsEU0ow@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