Re: Support EXCEPT for TABLES IN SCHEMA publications

On Tue, Jul 7, 2026 at 8:40 AM shveta malik <shveta.malik@gmail.com> wrote:
>
> 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.
>

Thanks for the bug report. I've fixed it and attached the fix as a
separate top-up patch, v19-0002.

Until v18, schema qualification checks for EXCEPT tables were split
between two places:

1) TABLES IN SCHEMA <schema-name> (EXCEPT TABLE <table-name>);
 -- the schema qualification check was performed in gram.y, since all
required information was available during parsing.

2) TABLES IN SCHEMA CURRENT_SCHEMA (EXCEPT TABLE <table-name>);
 -- CURRENT_SCHEMA is resolved only at execution time, so the schema
qualification and eligibility checks were performed there.

The reported bug also affects cases such as:
CREATE PUBLICATION PUB2 FOR TABLES IN SCHEMA MYSCHEMA EXCEPT (TABLE
T2), CURRENT_SCHEMA EXCEPT (TABLE T3);
 -- where CURRENT_SCHEMA = myschema. Correct handling of this case
requires the checks to be performed after schema resolution, i.e., at
execution time. To reduce duplicate code, I moved the schema
qualification and eligibility checks for both cases to execution time
and removed the corresponding processing from gram.y.

Since this changes a fair amount of code, I've kept it as a separate
patch (v19-0002) to make review easier. Depending on the feedback, we
can optimize the implementation or move parts of it back into gram.y
before merging it into patch 0001.
~~~

Attached is the v19 patch set.
 -- Also Addressed all comments from Peter at [1] and Shveta at [2].
 -- The code optimization suggestions from Shveta at [3] are still
pending. I'm evaluating them and plan to address them in the next
version.
~~~

[1] https://www.postgresql.org/message-id/CAHut%2BPt-%2B6Q4-kmhqCZ2_FpaNRR%3DhaJk-GK1eEyTXcTTDnRnXA%40mail.gmail.com
[2] https://www.postgresql.org/message-id/CAJpy0uAuL7SqowL7T6HCELuVUr5ubwoGsjV1SQQRCXGme5L%3DpA%40mail.gmail.com
[3] https://www.postgresql.org/message-id/CAJpy0uA_d-K-f94d-9Xw66Fqv7gdSGBYGzi9YcybtuEkYdQocQ%40mail.gmail.com

--
Thanks,
Nisha