Re: Support EXCEPT for TABLES IN SCHEMA publications

On Fri, Aug 21, 2026 at 2:55 PM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
>
> On Thu, Aug 20, 2026 at 2:09 PM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> >
> > I had a quick look, it looks better than v28. I will review and
> > validate it in detail by tomorrow.
> >
>
> The code looks simpler IMO and manageable for supporting all these
> contradictory error scenarios.
>
> I verified the inherited table cases alone. I have a few concerns:
>
> 1)
> postgres=# CREATE PUBLICATION pub1 FOR TABLE s2.child, TABLES IN
> SCHEMA s1 EXCEPT (TABLE s1.parent);
> ERROR: table "s2.child" cannot be both published and excluded
> DETAIL: It descends from "s1.parent", which is named in the EXCEPT
> clause of schema "s1".

Hmm. See below.

>
> postgres=# CREATE PUBLICATION pub1 FOR TABLE s1.parent*, TABLES IN
> SCHEMA s2 EXCEPT (TABLE s2.child);
> ERROR: table "s2.child" cannot be both published and excluded
>
> Can we have a similar DETAIL message in second as well?
>
> 2)
> I find this case still a little ambiguous:
>
> CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA s1, TABLES IN SCHEMA s2
> EXCEPT (TABLE s2.child);
>
> Currently it works, but should it?
>
> a) Should s2.child be published because s1.parent is included, and
> schema s2 is also included, meaning cross-schema inheritance
> boundaries are respected and brought back in?
>
> b) Or should s2.child remain excluded because of the explicit EXCEPT
> (TABLE s2.child) clause?
>

From the documentation [1] it explicitly says:

"FOR TABLE" ... "If ONLY is not specified, the table and all its
descendant tables (if any) are added."
That means "FOR TABLE s1.parent" (without ONLY) should reach out and
drag in the s2.child even though the schema s2 is not published.

OTOH, there is no special documentation about inheritance for "TABLE IN SCHEMA"
That means "FOR TABLES IN SCHEMA s1" will *not* reach out to drag in
the s2.child from the otherwise unpublished s2 schema.

~~

That documented behaviour is the same as what I observed by experimentation

CASE 1.

-- Setup
DROP SCHEMA IF EXISTS s1 CASCADE;
DROP SCHEMA IF EXISTS s2 CASCADE;
CREATE SCHEMA s1;
CREATE SCHEMA s2;

CREATE TABLE s1.parent (id int PRIMARY KEY, val text);
CREATE TABLE s2.child (extra text) INHERITS (s1.parent);

-- Publish just the parent, no ONLY keyword, no schema clause
CREATE PUBLICATION pub FOR TABLE s1.parent;

The following result shows s2.child gets dragged in:

test_pub=# SELECT schemaname, tablename FROM pg_publication_tables
WHERE pubname = 'pub';
schemaname | tablename
------------+-----------
s1 | parent
s2 | child
(2 rows)

~~~

And CASE 2:

test_pub=# CREATE PUBLICATION pub2 FOR TABLES IN SCHEMA s1;
CREATE PUBLICATION

The following result shows s2.child is NOT dragged in:

test_pub=# SELECT schemaname, tablename FROM pg_publication_tables
WHERE pubname = 'pub2';
schemaname | tablename
------------+-----------
s1 | parent
(1 row)

~~~

I think this answers your question about:
CREATE PUBLICATION pub1 FOR TABLES IN SCHEMA s1, TABLES IN SCHEMA s2
EXCEPT (TABLE s2.child);

AFAICT -- s2.child should be excluded.

It is not a conflict. Why? Because the s1.parent trying to include
s2.child, because FOR TABLES IN SCHEMA does *not* reach across schemas
to include the descendant tables. So it's only the TABLES IN SCHEMA s2
that was causing s2.child to be published in the first place, and the
EXCEPT negates that.

~~~

In fact, it is this other example that I think may be broken:

> postgres=# CREATE PUBLICATION pub1 FOR TABLE s2.child, TABLES IN
> SCHEMA s1 EXCEPT (TABLE s1.parent);
> ERROR: table "s2.child" cannot be both published and excluded
> DETAIL: It descends from "s1.parent", which is named in the EXCEPT
> clause of schema "s1".

Having shown above already that TABLES IN SCHEMA does *not* reach
across into unpublished schemas to include descendants, we cannot turn
around to say the EXCEPT case *will* reach across schemas and exclude
them! So that means that s2.child is not being excluded at all by the
parent. So AFAICT that means that there is really no conflict here at
all.

======
[1] https://www.postgresql.org/docs/current/sql-createpublication.html

Kind Regards,
Peter Smith.
Fujitsu Australia