Re: Skipping schema changes in publication

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'
>
> - check_publications_except_list()
> - describePublications()
> - getPublications()
> - LoadPublications() / publication_has_any_except_table()
>
> That means pass through all the publication tables to find (usually)
> nothing. It might be a costly operation, especially in the
> LoadPublications routine. Maybe slightly change index
> pg_publication_rel_prpubid_index a little and add 'prexcept' as a second
> column there? It might avoid performance issues that may arise during
> the upgrade.
>

Why do you think it can hurt when used via LoadPublications? That is
done only one time when the first relsync entry is built, after that
it is reused unless there is some DDL on the publication. Also, we are
not expecting thousands of tables in the except list for a
publication. So can traversing that few entries and that too first
time when cache is built can impact performance in a noticeable way?
If you think so, we can modify the index as you are suggesting but I
was thinking if the number of entries is not large per-publication
then why to permanently increase the size of the index.

-- 
With Regards,
Amit Kapila.