Re: Support EXCEPT for TABLES IN SCHEMA publications

On Fri, Jul 3, 2026 at 10:15 PM Nisha Moond <nisha.moond412@gmail.com> wrote:
>
> On Thu, Jul 2, 2026 at 2:19 PM shveta malik <shveta.malik@gmail.com> wrote:
> >
> > A few comments:
> >
> > 1)
> > postgres=# alter publication pub1 add table s2.t1;
> > ERROR:  table "s2.t1" cannot be added because it is excluded from
> > publication "pub1"
> >
> > It will be good to add hint in above error.
> >
> > Suggestion:
> > ERROR: cannot add table "s2.t1" to publication "pub1"
> > DETAIL: Table "s2.t1" is currently in EXCEPT list
> > HINT: Change EXCEPT list using ALTER PUBLICATION ..SET TABLES IN
> > SCHEMA .. EXCEPT
> >
> > Please rephrase above as needed. Look earlier such messages/hints
> > already added for ALL TABLES.
> >
>
> Updated the error as suggested, with a slight change in DETAIL message.
>
> > From the user's perspective, it seems slightly odd that if he has
> > excluded a table from publication, he can not add it back using 'add
> > table'. Was this point discussed earlier?
> >
>
> This hasn't been discussed yet.
>
> I kept the current behavior, as IMO, it is hard to tell whether the
> user is trying to re-include a table or whether the ADD is unintended
> and should be blocked. To avoid ambiguity, I think EXCEPT entries
> should be modified only through the EXCEPT (...) clause itself, such
> as ALTER PUBLICATION ... ADD/SET ... EXCEPT(TABLE ...), or by omitting
> EXCEPT in the SET case.
>
> This would also keep the behavior of ADD TABLE and DROP TABLE aligned,
> as both would operate only on the include list without touching the
> EXCEPT list.
> I think this also aligns with FOR ALL TABLES, where ADD TABLE is
> disallowed, so the EXCEPT list can only be modified through SET ..
> EXCEPT (...).
>
> Thoughts?

Okay. Makes sense.

>
> > 2)
> > Currently:
> > postgres=# alter publication pub1 drop table s2.t1;
> > ERROR:  relation "t1" is not part of the publication
> >
> > s2.t1 is EXCEPT list here.
> >
> > Should we have below error for this case rather than above generic
> > error, it will be more intuitive for user. Plus both add and drop
> > table commands will have similar error-style for EXCEPT list.
> > Thoughts?
> >
> > ERROR: cannot drop table "s2.t1" from publication "pub1"
> > DETAIL: Table "s2.t1" is currently in EXCEPT list
> > HINT: Change EXCEPT list using ALTER PUBLICATION ..SET TABLES IN
> > SCHEMA .. EXCEPT
> >
>
> Makes sense. Fixed.
>
> > 3)
> > Please see below test:
> >
> > --Setting Except list for pub1 which has all tables of s2 included:
> > alter publication pub1 set tables in schema s2 EXCEPT ( TABLE t1, t2);
> >
> > --pg_publication_rel entries:
> > pubname | schema_name | table_name | prexcept
> > --------+-------------+------------+---------
> > pub1    | s2          | t1         | t
> > pub1    | s2          | t2         | t
> >
> >
> > --Then added a partition:
> > alter publication pub1 add table s2.tab_part_1_p2;
> >
> > --pg_publication_rel entries:
> > pubname | schema_name |  table_name   | prexcept
> > --------+-------------+---------------+---------
> > pub1    | s2          | t1            | t
> > pub1    | s2          | t2            | t
> > pub1    | s2          | tab_part_1_p2 | f
> >
> >
> > --Then changed pub1 to exclude tab_root (parent of tab_part_1_p2) as well:
> >
> > alter publication pub1 set tables in schema s2 EXCEPT ( TABLE t1, t2, tab_root);
> >
> > --pg_publication_rel entries:
> > pubname | schema_name | table_name | prexcept
> > --------+-------------+------------+---------
> > pub1    | s2          | t1         | t
> > pub1    | s2          | t2         | t
> > pub1    | s2          | tab_root   | t
> >
> > Note theoutput above, tab_part_1_p2 is removed internally as part of
> > earlier command since its parent tab_root is now excluded.
> >
> > --Now add parition back, it allowed to add it:
> > alter publication pub1 add table s2.tab_part_1_p2;
> >
> > pg_publication_rel entries:
> > pubname | schema_name |  table_name   | prexcept
> > --------+-------------+---------------+---------
> > pub1    | s2          | t1            | t
> > pub1    | s2          | t2            | t
> > pub1    | s2          | tab_root      | t
> > pub1    | s2          | tab_part_1_p2 | f
> >
> >
> > FOR ALL TABLES publicaiton, if root is excluded, there is no way
> > publication can have parition included independently. Same should hold
> > true here. Thoughts?
>
> Right, based on the discussion at [1], since we follow "all partitions
> are excluded if the root is excluded", a partition child should not be
> allowed to be added when its partition root is already excluded from
> the publication.
> Fixed. Such an operation will now raise an error:
>
> 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.
> ~~~

Okay, looks good.

thanks
Shveta