Re: Support EXCEPT for TABLES IN SCHEMA publications

On Fri, Aug 14, 2026 at 7:52 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> On Wed, 12 Aug 2026 at 14:40, shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> >
> > On Tue, Aug 11, 2026 at 2:28 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> > >
> > >
> > > Apart from these, the rest of the comments are also fixed in the v26
> > > version attached.
> >
> > Thanks Vignesh. A few comments on v26-0001:
> >
> > 1)
> >
> > publication_add_relation() accepts 'if_not_exists' i.e. add the new
> > entry 'if already not present' else skip it (no error if it is a
> > duplicate addition). Most flows pass it as 'true'. The current code
> > skips raising error if if_not_exists=true and entry exists. It made
> > sense earlier, but in our implementation, I feel it should still raise
> > an error if entries are cross wired (i.e., if an exclusion is present
> > and we are trying to add it as an inclusion, or vice versa). The
> > 'if_not_exists' based 'skip logic' should only be exercised if the
> > nature of existing entity is of same kind as user is trying to add.
> >
> > Let me know if you have different understanding.
>
> That is my understanding as well.
> I tested a few scenarios to determine whether there was an actual
> issue and found that the following cases were succeeding when they
> should have thrown an error:
> Scenario 1:
> CREATE PUBLICATION p FOR TABLES IN SCHEMA s1 EXCEPT (TABLE s1.t);
> ALTER PUBLICATION p ADD TABLE s1.t;
>
> Scenario 2:
> CREATE PUBLICATION p2 FOR TABLE s1.t;
> ALTER PUBLICATION p2 ADD TABLES IN SCHEMA s1 EXCEPT (TABLE s1.t);
>
> Both scenarios should have resulted in an error, but they currently
> succeed. I have addressed these cases as well.
>

These are very simple scenarios which I tested multiple times. These
scenarios produced an error in the previous version; see:

postgres=# create publication pub1 for tables in schema s1 except (table s1.t1);
CREATE PUBLICATION
postgres=# alter publication pub1 add table s1.t1;
ERROR: cannot add table "s1.t1" to publication "pub1"
DETAIL: The table is named in the publication's EXCEPT clause for schema "s1".
HINT: Change the EXCEPT clause using ALTER PUBLICATION ... SET TABLES
IN SCHEMA ... EXCEPT.

postgres=# CREATE PUBLICATION pub2 FOR TABLE s1.t1;
CREATE PUBLICATION
postgres=# ALTER PUBLICATION pub2 ADD TABLES IN SCHEMA s1 EXCEPT (TABLE s1.t1);
ERROR: table "s1.t1" cannot be both published and excluded

By any chance did you comment out 'CheckExceptNotInTableList' while
testing these i.e. did you intend to test these through
publication_add_relation() alone?

thanks
Shveta