Re: Skipping schema changes in publication

On Thu, Feb 26, 2026 at 8:26 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Wed, Feb 25, 2026 at 7:09 PM Andrei Lepikhov <lepihov(at)gmail(dot)com> wrote:
> >
> > On 25/2/26 08:04, vignesh C wrote:
> > > On Mon, 23 Feb 2026 at 16:46, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > > The attached patch has the changes for the same i.e.a) Raises an error
> > > when attempting to attach a partition to a root partitioned table if
> > > that table is referenced in an EXCEPT clause of any publication. b)
> > > Adds support for dropping excluded tables using: ALTER PUBLICATION ...
> > > DROP EXCEPT TABLE. c) Adds support for replacing the exclusion list
> > > using ALTER PUBLICATION ... SET EXCEPT TABLE.
> > > The changes related to DROP EXCEPT TABLE and SET EXCEPT TABLE have
> > > been kept separately into patch 0002 for easier review.
> >
> > I discovered this patch, maybe not deeply enough. But one question raised.
> >
> > I usually work with multiple tables (sometimes hundreds, if not
> > thousands). EXCEPT clause might be quite rare.
> >
>
> I think it will be useful for users using ALL TABLES IN SCHEMA and ALL
> TABLES publications where they don't want to replicate the entire
> schema or database.
>
> > Some commands want to
> > extract only excepted tables from the publication using the following
> > pattern:
> >
> > 'SELECT ... FROM pg_publication_rel WHERE prpubid = <X> AND pr.prexcept'
> >

I agree with Amit's point. I would like to add one more point here.

pg_publication_rel will not contain any regular table entries for a
given pubid when EXCEPT is used. Except is for ALL-TABLES pub alone
for which base-table entries are stored in pg_publication_tables. Thus
for an EXCEPT pub, anything we fetch from pg_publication_rel using the
pubid index
would necessarily be an EXCEPT entry. The scenario where we retrieve,
say, 1000 table entries for a pubid and only 1–2 of them are EXCEPT
entries will not occur. Given this, the need for a composite index on
(prpubid, prexcept) does not appear to be strongly justified to me.
Please ignore my comment if you are already aware of this situation.

thanks
Shveta