Re: Support EXCEPT for TABLES IN SCHEMA publications

On Sun, Jun 7, 2026 at 2:09 AM Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com> wrote:
>
> Hello!
>
> Thanks, I can confirm the fixes work.
>
> I did some more testing. I think I see two problems with ALTER TABLE
> ... SET SCHEMA:
>

Thanks for reporting this case.

The current patches did not handle schema changes for excluded tables.
Hou-san also reported the same design issue off-list to me.

After considering, I chose to follow behavior similar to existing FOR
ALL TABLES publications to handle schema-switch cases. Today, if a
table excluded via EXCEPT is dropped, the corresponding prexcept entry
is removed, and recreating a table with the same name does not
automatically restore the exclusion.

I applied the same principle to schema changes: once an excluded table
moves out of the schema, the exclusion is removed.

For example, consider the following cases:
CREATE PUBLICATION p FOR TABLES IN SCHEMA s1 EXCEPT (TABLE s1.t);

case-1: Table moves from s1 to s2: ALTER TABLE s1.t SET SCHEMA s2;
-- The exclusion entry s1.t is removed.

case-2: Table moves back from s2 to s1: ALTER TABLE s2.t SET SCHEMA s1;
-- The table is published again. The exclusion is not restored
automatically; the user must specify it again.

case-3: If s2 is also part of publication p
-- The behavior remains the same. Moving the table between schemas
does not recreate the exclusion entry.

IOW, once the exclusion is broken by a schema move (similar to a
drop), it must be re-established explicitly by the user.

The attached patch implements this behavior. I've also updated the
docs to describe it.

I also considered two alternatives:
1) Reject the schema change: Error out if a table with a prexcept
entry is moved between schemas. This feels overly restrictive.
2) Make exclusions schema-aware: Add a prexceptschema column and store
the schema OID along with the exclusion entry. The exclusion would
only apply while the table remains in that schema, allowing it to be
restored automatically if the table moves back later. IMO it
complicates the design.

Thoughts?

> 1.
>
> CREATE SCHEMA s;
> CREATE SCHEMA other;
> CREATE TABLE s.t(i int);
> CREATE PUBLICATION p FOR TABLES IN SCHEMA s EXCEPT (TABLE s.t);
> ALTER TABLE s.t SET SCHEMA other;
> ALTER PUBLICATION p ADD TABLES IN SCHEMA other;
> -- shouldn't s.t be there?
> SELECT schemaname, tablename FROM pg_publication_tables WHERE
> pubname='p' ORDER BY 1,2;
>

Do you mean other.t should be there?
This is now fixed. When t is moved to schema other, the exclusion is
removed and the table is published through p.

postgres=# SELECT schemaname, tablename FROM pg_publication_tables
WHERE pubname='p' ORDER BY 1,2;
schemaname | tablename
------------+-----------
other | t

>
> 2.
>
> CREATE SCHEMA s;
> CREATE SCHEMA other;
> CREATE TABLE s.t(i int);
> CREATE PUBLICATION p FOR TABLES IN SCHEMA s EXCEPT (TABLE s.t);
> ALTER TABLE s.t SET SCHEMA other;
> ALTER PUBLICATION p DROP TABLES IN SCHEMA s;
> -- should it still be there? it isn't without the alter set schema
> SELECT pr.prrelid::regclass AS rel, pub.pubname, pr.prexcept
> FROM pg_publication_rel pr JOIN pg_publication pub ON pub.oid=pr.prpubid;
>

Fixed.
Now, when s.t is moved out of schema s, it is removed from publication
p's exclusion list. Later, if schema s is dropped from publication p,
the exclusion entries associated with s are removed as well, and s.t
is no longer present there.

Attached are the updated v11 patches.
Patch 001 has the changes discussed above; patches 002 and 003 are unchanged.

--
Thanks,
Nisha