Re: Support EXCEPT for TABLES IN SCHEMA publications

On Fri, 7 Aug 2026 at 19:51, vignesh C <vignesh21(at)gmail(dot)com> wrote:
>
> On Tue, 4 Aug 2026 at 14:11, Nisha Moond <nisha(dot)moond412(at)gmail(dot)com> wrote:
> >
> > Attached is the v25 patch set addressing all of the above, as well as
> > Shlok's and Peter's comments in [1] and [2].
>
> There is another issue with pg_dump when dumping a publication created
> using FOR TABLES IN SCHEMA ... EXCEPT.
> CREATE SCHEMA s1;
> CREATE SCHEMA s2;
> CREATE TABLE s1.parent (a int);
> CREATE TABLE s2.child (b int) INHERITS (s1.parent);
>
> -- Create a publication excluding the parent table
> CREATE PUBLICATION p FOR TABLES IN SCHEMA s1 EXCEPT (TABLE s1.parent);
>
> The publication correctly excludes both the parent and its inherited child:
> postgres=# \dRp+
> Publication p
> Owner | All tables | All sequences | Inserts | Updates | Deletes
> | Truncates | Generated columns | Via root | Description
> ------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
> test | f | f | t | t | t
> | t | none | f |
> Tables from schemas:
> "s1"
> Except tables:
> "s1.parent"
> "s2.child"

This implementation has now been modified to include only s1.parent
and not s2.child, since the s2 schema was not specified in the
publication.

> However, pg_dump generates the following definition:
> CREATE PUBLICATION p WITH (publish = 'insert, update, delete, truncate');
> ALTER PUBLICATION p OWNER TO test;
> ALTER PUBLICATION p ADD TABLES IN SCHEMA s1 EXCEPT (TABLE ONLY parent);

Since s2.child is not included in the publication relation catalog,
the dump retains the original publication definition. Consequently,
during restore, the original publication definition is recreated.

This issue is addressed in the v27 version patch attached at [1].
[1] - https://www.postgresql.org/message-id/CALDaNm0802S%3D557rKtYcV%2BoEbXFw23n%3DXtPHMCbKGSgOgOgk2A%40mail.gmail.com

Regards,
Vignesh