Re: Support EXCEPT for TABLES IN SCHEMA publications

On Wed, Jun 3, 2026 at 2:51 AM Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com> wrote:
>
> Hello
>

Hi, thanks for the tests.

> In this simple script:
>
> CREATE SCHEMA s;
> CREATE TABLE s.a(i int);
> CREATE TABLE s.b(i int);
> CREATE PUBLICATION p FOR TABLES IN SCHEMA s EXCEPT (TABLE s.b);
>
> SELECT * FROM pg_publication_tables WHERE pubname = 'p';
> SELECT * FROM pg_get_publication_tables(ARRAY['p'], 's.b'::regclass);
>
> Shouldn't the second select return an empty list? Currently it returns
> one row for s.b.
>

Yes, it is a bug.
The issue was that pg_get_publication_tables() could not distinguish
between prexcept=true and prexcept=false.

A reference note: This worked fine for FOR ALL TABLES publications
because entries in pg_publication_rel always had prexcept=false, so
the presence of an entry implied the table was not published. With
non-ALL TABLES publications, a table can now have both prexcept=true
(explicitly excluded) and prexcept=false (excluded from a schema
publication).

Fixed.

> And I think there's also a bug with CREATE PUBLICATION with IN SCHEMA
> CURRENT_SCHEMA:
>
> CREATE SCHEMA s1;
> CREATE TABLE s1.a(i int);
> CREATE TABLE public.x(i int);
> CREATE TABLE public.onlypub(i int); -- exists only in public, not in s1
>
> CREATE PUBLICATION pn FOR TABLES IN SCHEMA s1 EXCEPT (TABLE public.x);
> -- error out, good
> SET search_path = s1, public;
> CREATE PUBLICATION pn2 FOR TABLES IN SCHEMA s1 EXCEPT (TABLE onlypub);
> -- error out, good
> SET search_path = s1, public;
> SELECT current_schema(); -- s1
> CREATE PUBLICATION pc FOR TABLES IN SCHEMA CURRENT_SCHEMA EXCEPT
> (TABLE public.x); -- doesn't error out
> CREATE PUBLICATION pc4 FOR TABLES IN SCHEMA CURRENT_SCHEMA EXCEPT
> (TABLE onlypub); -- also doesn't error out
>

Yes, this is a bug. The CURRENT_SCHEMA case was missed when the patch
was updated to allow only immediate schema tables in the EXCEPT
clause.

Attached are v10 patches with fixes for both issues. I also added the
missing tab-completion for CURRENT_SCHEMA and added regression tests
in publication.sql covering both scenarios.

--
Thanks,
Nisha