Re: Support EXCEPT for TABLES IN SCHEMA publications

On Tue, Jul 7, 2026 at 4:31 AM Peter Smith <smithpb2250@gmail.com> wrote:
>
> Hi Nisha.
>
> Bug: The current patch is allowing conflicting exclusion lists...
>
> ======
>
> Background:
>
> It's always been possible for CREATE PUBLICATION to specify the same
> schema or same table multiple times.
>
> test_pub=# CREATE SCHEMA MYSCHEMA;
> CREATE SCHEMA
> test_pub=# CREATE TABLE T1(A INT);
> CREATE TABLE
> test_pub=# CREATE TABLE MYSCHEMA.T2(A INT);
> CREATE TABLE
> test_pub=# CREATE TABLE MYSCHEMA.T3(A INT);
> CREATE TABLE
>
> // same table multiple times is OK
> test_pub=# CREATE PUBLICATION PUB3 FOR TABLE T1,T1,T1,T1;
> CREATE PUBLICATION
>
> // same schema multiple times is OK
> test_pub=# CREATE PUBLICATION PUB1 FOR TABLES IN SCHEMA MYSCHEMA,
> MYSCHEMA, MYSCHEMA;
> CREATE PUBLICATION
>
> ~~~
>
> But, this behaviour is only allowed if there are no specification
> conflicts. e.g. You can't have multiple tables with column lists:
>
> test_pub=# CREATE PUBLICATION PUB4 FOR TABLE T1,T1(A),T1,T1;
> ERROR:  conflicting or redundant column lists for table "t1"
>
> ======
>
> Bug:
>
> IMO similar restrictions and a similar error message needs to happen
> for clashing schema exclusions. Specifying the same schema multiple
> times is fine, but it must be disallowed if there are conflicting
> exclusion lists.
>

+1. Good Catch.

> For example, the command below currently works, but it should not:
>
> test_pub=# CREATE PUBLICATION PUB2 FOR TABLES IN SCHEMA MYSCHEMA
> EXCEPT (TABLE T2), MYSCHEMA EXCEPT (TABLE T3), MYSCHEMA;
> CREATE PUBLICATION
>
> test_pub=# \dRp+ PUB2
>                                                        Publication pub2
>   Owner   | All tables | All sequences | Inserts | Updates | Deletes |
> Truncates | Generated columns | Via root | Descri
> ption
> ----------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------
> ------
>  postgres | f          | f             | t       | t       | t       |
> t         | none              | f        |
> Tables from schemas:
>     "myschema"
> Except tables:
>     "myschema.t2"
>     "myschema.t3"
>
> ======
> Kind Regards,
> Peter Smith.
> Fujitsu Australia
>
>