Re: Support EXCEPT for TABLES IN SCHEMA publications

On Wed, Jul 8, 2026 at 11:58 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Tue, Jul 7, 2026 at 4:13 PM shveta malik <shveta.malik@gmail.com> wrote:
> >
> > On Tue, Jul 7, 2026 at 2:31 PM shveta malik <shveta.malik@gmail.com> wrote:
> > >
> > > On Fri, Jul 3, 2026 at 10:15 PM Nisha Moond <nisha.moond412@gmail.com> 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.
>

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 = 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