Re: Support EXCEPT for TABLES IN SCHEMA publications

On Thu, Jul 2, 2026 at 10:21 AM Nisha Moond <nisha(dot)moond412(at)gmail(dot)com> wrote:
>
> On Wed, Jul 1, 2026 at 3:46 PM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> >
> > On Wed, Jul 1, 2026 at 3:37 PM Nisha Moond <nisha(dot)moond412(at)gmail(dot)com> wrote:
> > >
> > >
> > > Besides the above comments, I found and fixed a few additional issues
> > > during further testing:
> > > 1) Fixed an issue where UPDATE/DELETE on an excluded table without
> > > replica identity was still blocked. e.g.,
> > >
> > > postgres=# create publication pub_h2 for tables in schema s1 except ( table t1);
> > > postgres=# update s1.t1 set c1=3 where c1=1;
> > > ERROR: cannot update table "t1" because it does not have a replica
> > > identity and publishes updates
> > > HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
> > >
> > > Excluded tables should not be treated as published for update/delete checks.
> >
> > Nisha, this bug was introduced by your patch or does it exist in 'ALL
> > TABLES Except TABLE' too?
> >
>
> The same case for 'ALL TABLES Except Table' was already handled
> correctly as part of that feature (Ref
> -RelationBuildPublicationDesc()). The bug was only in my patch where I
> missed handling it earlier.
>

Okay, then we are good.

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.

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?

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

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?

thanks
Shveta