Re: Support EXCEPT for TABLES IN SCHEMA publications

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, 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?

thanks
Shveta