Re: Support EXCEPT for TABLES IN SCHEMA publications

On Thu, Jul 2, 2026 at 2:19 PM shveta malik <shveta(dot)malik(at)gmail(dot)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?

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

Attached are the updated patches v18.

[1] https://www.postgresql.org/message-id/CAA4eK1%2BNmQRjSHPLr0X8YBuC6joivFqgsY3_qJ5-RnuuwNGkRQ%40mail.gmail.com
--
Thanks,
Nisha