Re: Support EXCEPT for TABLES IN SCHEMA publications

On Fri, Aug 14, 2026 at 12:25 PM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
>
> On Fri, Aug 14, 2026 at 3:59 PM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> >
> > On Thu, Aug 13, 2026 at 12:52 PM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
> > >
> > >
> > > 2b.
> > > /In the partition case/For partitions/
> > >
> > > AFAICT this case is referring to something like: "FOR TABLE part,
> > > TABLES IN SCHEMA EXCEPT (part_root)"
> > >
> > > But, isn't that just a variation of the 1st case issue? e.g. where
> > > table "part" is not yet visible for later lookup of "root", then you
> > > wont be able to check integrity of the partition tree regardless of
> > > the up/down traversal logic, so I wasn't sure why this 2nd case was
> > > separately mentioned at all.
> >
> > The second point is different from the first. Consider this case:
> >
> > CREATE PUBLICATION pub1 FOR s2.tab_part;
> > ALTER PUBLICATION pub1 ADD TABLES IN SCHEMA s2 EXCEPT (TABLE tab_root);
> >
> > Here, the partition entry in pg_publication_rel is visible to the
> > second command in publication_add_relation() and in
> > check_publication_add_relation(). But the checks there are not
> > sufficient to identify the error. If we try to detect the error while
> > adding tab_root in publication_add_relation(),
> > we would need to perform a full descendant search to determine whether
> > any of its descendants are already present in pg_publication_rel. This
> > downward traversal is what we are trying to avoid in
> > publication_add_relation(). Geenrally we rely on ancestor-lookup and
> > we want to stick to that instead of introducing a new logic.
> > Thus the logic in CheckExceptNotInTableList() is needed here. It reads
> > all explicitly added entries from pg_publication_rel, looks up their
> > ancestors to find the root, and checks the EXCEPT entries against that
> > root.
> >
> > This is my understanding, let's wait for Nisha's comments as well.
> >
>
> Shveta, thanks for the explanation.
>
> In hindsight, my confusion was due to the comment wording -- the 1st
> case was referring to "a single command", and the 2nd case just said,
> "root may be added to the EXCEPT list later."....
> I misinterpreted "later" as meaning later in the *same* command, like:
> CREATE PUBLICATION pub1 FOR TABLE s2.tab_part, TABLES IN SHEMA s2
> EXCEPT(TABLE tab_root);
> which is entirely a different horse....
>
> Perhaps the comments for these tricky cases should include tiny SQL
> examples (just like you wrote) to clarify exactly what the comment is
> trying to say.
>

Yes, that makes sense. Otherwise, reviewers might have doubts about
it. It is a somewhat tricky comment.

thanks
Shveta