Re: Support EXCEPT for TABLES IN SCHEMA publications

On Tue, Jul 7, 2026 at 4:13 PM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
>
> On Tue, Jul 7, 2026 at 2:31 PM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> >
> > On Fri, Jul 3, 2026 at 10:15 PM Nisha Moond <nisha(dot)moond412(at)gmail(dot)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.

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