Re: Support EXCEPT for TABLES IN SCHEMA publications

On Wed, Jun 10, 2026 at 3:06 AM Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com> wrote:
>
> > Do you mean other.t should be there?
>
> Yes, that was a typo in my example.
>
> > 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.
>
> I'm not that sure about the analogy. DROP TABLE is a destructive
> operation, executing DROP TABLE and then CREATE TABLE won't
> automatically bring back the data.
>
> With this approach, two cheap ALTER TABLE ... SET SCHEMA statements
> can clear an EXCEPT clause without the proper permissions.
>

We already have similar behavior today. A non-publication owner can
run ALTER TABLE ... SET SCHEMA and remove a table from a FOR TABLES IN
SCHEMA publication without any warning or publication-level permission
check.

> But I'm not sure what's the best solution for this. The v11 approach
> is at least more consistent than the previous behavior.
>
> > 1) Reject the schema change: Error out if a table with a prexcept
> > entry is moved between schemas. This feels overly restrictive.
>
> It is restrictive, but maybe it's the better solution? Or
> alternatively, maybe it should require proper permissions to remove
> the except clause?
>

My concern is that introducing a permission check only for the EXCEPT
case would create an inconsistency: one type of schema move affecting
publication behavior would require publication ownership, while
another would not.

That said, I'm okay with restricting schema changes for tables in an
EXCEPT list if others feel that's the right behavior. Let's wait for
feedback from others.

> Another thing that could improve this if we would print out a warning
> that the statement caused a change in the publication? But then that's
> also a question for the preexisting drop table case.
>

Right, As also mentioned above, ALTER TABLE changes that affect
publication membership currently do not emit any notice or warning, so
I'm not sure we need one here either.

--
Thanks,
Nisha