Re: Support EXCEPT for TABLES IN SCHEMA publications

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"

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);

The dumped definition excludes only the parent table and omits the
inherited child (s2.child), even though the publication excludes both
tables.
As a result, restoring the dump recreates a publication with different
semantics: s2.child becomes part of the publication after restore.
It appears that pg_dump does not preserve the complete exclusion list
for publications created using FOR TABLES IN SCHEMA ... EXCEPT when
inherited tables are involved.

Regards,
Vignesh