Re: Support EXCEPT for TABLES IN SCHEMA publications

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Nisha Moond <[email protected]>
To: Zsolt Parragi <[email protected]>
Cc: [email protected]
Subject: Re: Support EXCEPT for TABLES IN SCHEMA publications
Date: Wed, 10 Jun 2026 14:33:04 +0530
Message-ID: <CABdArM7x2cz9DiCHy-2n8PdScQex+qQQX7W7OLmA0rjWT8Sb1A@mail.gmail.com> (raw)
In-Reply-To: <CAN4CZFOaHBAUUrHmcCayUHVzcSw+SjWPEDuWT-5ij-KgxUP5=g@mail.gmail.com>
References: <CABdArM5sw4Q1ZU8HGdo4BSc1A_+8xtUNq17j6wcir=yMUy19Cg@mail.gmail.com>
	<CAHut+PvnH8QHa035Skoh1e9jm_H08DO9fQ=F-NAMsEpYf0RZ2Q@mail.gmail.com>
	<CAJpy0uDu0LcNXcZCP0cR_LHqo+sau33KwPFHemmGVYf_JTxRBQ@mail.gmail.com>
	<CAA4eK1KbCWBmEXH-rhQjKgNwq=onZp8vRR-QkRhPpbKwL-kQdw@mail.gmail.com>
	<CAHut+Pvj4=GWoJEd4EBdp4pi6KxXQ46ioW=PV+=UktiXr2gCvg@mail.gmail.com>
	<CABdArM75F0A+DGP8AOt-_b_XREX40rvFid1jRjnr_+S5b51t8Q@mail.gmail.com>
	<CAJpy0uDTshb243L5yEYWB3uO-JrwSoRqQDNovh03K2GZuuR3Pg@mail.gmail.com>
	<CAJpy0uDy97ULmJUwPacAzc5u2seuPK6RXgCS1rnsW2MfR4eeSw@mail.gmail.com>
	<CABdArM6oXXXSAxxXFktTTfBf4kyxJCvdNtTbUZtSwJ=CepN+Xw@mail.gmail.com>
	<CAJpy0uBqM+fq7+g1ZRATuY16H10MFP9i25wfFCYCE5MGu+PE0Q@mail.gmail.com>
	<CABdArM4uKaS1coCQj6rAwMmHqU_cCJyEWNic-PFF1_ZjDDM82Q@mail.gmail.com>
	<CAHut+Pu5VNakf5JAhKM7T-P_q37eN1Qgv5nvZUe+8RAAT41y4g@mail.gmail.com>
	<CABdArM6WTm2gP4pcjVdHT1Nx6zdLKTq7nLPUkzZOhprc95a6Rw@mail.gmail.com>
	<CAHut+Ptthc1X-UA8-6zG-iFeCDuoNd+oJRBZ1eCnJ9RNOXjfBQ@mail.gmail.com>
	<CABdArM79m7-CTf6KGGGU2QBydFtuonGgfxRSqk-vhwTsH8z1ow@mail.gmail.com>
	<CAHut+PuiK_Pa=BkSgBxYzqf1PYh+mcUcUQCr8r1e69-y1r+hhw@mail.gmail.com>
	<CABdArM7rH+3GekRgufEOwrJxUeQk=LB182CQwJD35e0oN7q8ZA@mail.gmail.com>
	<CAHut+PtP1mbQT==xo=G-37dV9Lt3q7YO2eMEAqSbZuszy93LcQ@mail.gmail.com>
	<CABdArM6XRpUR86a-daYMXFqhH-spJQiQAVfJ2+GFiAqeup2jyA@mail.gmail.com>
	<CAHut+PuDB=doKUSf94cs8hOo2d5mOc+GxxPOf57xGhdE6e-Aog@mail.gmail.com>
	<CABdArM76MZ4dYC87zCv67fVY9t5aFLd2nD5tNBxFFizESyBOSg@mail.gmail.com>
	<CAN4CZFM9xXkw8FvHOTan+m4rASSoxbJTWXMbZ_-v2M-84w5Wzg@mail.gmail.com>
	<CABdArM5wj0dtieSVMvzvJDh5fJ-GGwh3PxV+E_MbPEntCk4KRg@mail.gmail.com>
	<CAN4CZFMaB1a89NZjRT9bWFZ8-XN02amye_fnYAg0NM_23DAwQw@mail.gmail.com>
	<CABdArM5qHR=Zmyfqs6AuCn-Crj=_w3CXA-1K9rQ53_zismXCNQ@mail.gmail.com>
	<CAN4CZFOaHBAUUrHmcCayUHVzcSw+SjWPEDuWT-5ij-KgxUP5=g@mail.gmail.com>

On Wed, Jun 10, 2026 at 3:06 AM Zsolt Parragi <[email protected]> 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

view thread (25+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Support EXCEPT for TABLES IN SCHEMA publications
  In-Reply-To: <CABdArM7x2cz9DiCHy-2n8PdScQex+qQQX7W7OLmA0rjWT8Sb1A@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox