Re: Skipping schema changes in publication

On Thu, Feb 26, 2026 at 10:26 AM shveta malik <shveta.malik@gmail.com> wrote:
>
> On Thu, Feb 26, 2026 at 8:26 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
> >
> > On Wed, Feb 25, 2026 at 7:09 PM Andrei Lepikhov <lepihov@gmail.com> wrote:
> > >
> > > On 25/2/26 08:04, vignesh C wrote:
> > > > On Mon, 23 Feb 2026 at 16:46, Amit Kapila <amit.kapila16@gmail.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.

One correction here:  Except is for ALL-TABLES pub alone for which
base-table entries are "fetched" from pg_publication_tables view
instead of catalog pg_publication_rel.

> 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