On Tue, Jun 16, 2026 at 2:36 PM Amit Kapila <[email protected]> wrote: > > On Tue, Jun 16, 2026 at 6:52 AM Peter Smith <[email protected]> wrote: > > > > This discussion about the impact of ALTER TABLE ... SET SCHEMA made me > > wonder what happens for the existing PG19 case of FOR ALL TABLES > > EXCEPT (TABLE ...) > > > > It turns out to be quite different: > > > > ====== > > > > test_pub=# CREATE SCHEMA myschema; > > CREATE SCHEMA > > test_pub=# CREATE TABLE t1(A INT); > > CREATE TABLE > > test_pub=# CREATE TABLE t2(A INT); > > CREATE TABLE > > test_pub=# CREATE TABLE myschema.myt1(A INT); > > CREATE TABLE > > test_pub=# CREATE PUBLICATION pub1 FOR ALL TABLES EXCEPT (TABLE > > myschema.myt1); > > CREATE PUBLICATION > > test_pub=# \d myschema.myt1 > > Table "myschema.myt1" > > Column | Type | Collation | Nullable | Default > > --------+---------+-----------+----------+--------- > > a | integer | | | > > Excluded from publications: > > "pub1" > > > > test_pub=# \dRp+ pub1 > > Publication pub1 > > Owner | All tables | All sequences | Inserts | Updates | Deletes | > > Truncates | Generated columns | Via root | Descri > > ption > > ----------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------- > > ------ > > postgres | t | f | t | t | t | > > t | none | f | > > Except tables: > > "myschema.myt1" > > > > test_pub=# ALTER TABLE myschema.myt1 SET SCHEMA public; > > ALTER TABLE > > test_pub=# \d myt1 > > Table "public.myt1" > > Column | Type | Collation | Nullable | Default > > --------+---------+-----------+----------+--------- > > a | integer | | | > > Excluded from publications: > > "pub1" > > > > test_pub=# \dRp+ pub1 > > Publication pub1 > > Owner | All tables | All sequences | Inserts | Updates | Deletes | > > Truncates | Generated columns | Via root | Descri > > ption > > ----------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+------- > > ------ > > postgres | t | f | t | t | t | > > t | none | f | > > Except tables: > > "public.myt1" > > > > ====== > > > > This experiment shows that moving the table did *not* remove the exclusion. > > > > It is kind of "explainable" in hindsight because the exclusion is by > > table OID, not name, so it follows the table around when it is moved. > > I don't think this is what a user would expect, given that they > > explicitly asked to exclude it from a different schema. > > > > Is it a PG19 exclusion bug? > > > > Is it behaviour that needs more documenting? > > > > ~ > > > > IMO it seemed like a bug of the PG19 FOR ALL TABLES EXCEPT, because it > > is the opposite of what the new FOR TABLES IN SCHEMA EXCEPT patch > > does: > > "If a table listed in the <literal>EXCEPT</literal> clause is later > > moved to a different schema using <command>ALTER TABLE ... SET > > SCHEMA</command>, the exclusion is removed;" > > > > No, I don't think this is a bug for PG19 and the new behaviour for > PG20 is intentional and required because in this case, the EXCEPT > clause is schema-scoped, so once schema is changed, the table should > be removed from the exclusion list. OTOH, in PG19, the exclusion list > follows the table-level exclusion based on its OID as we can see in > the example provided by you. I think we can consider adding a line for > this in docs if you and others feel that such explicit mention can > avoid ambiguity around this. How about something like the following as > a separate para in EXCEPT clause description: Once a table is > excluded, the exclusion applies to that table itself, regardless of > its name or schema. Renaming the table or moving it to another schema > with <command>ALTER TABLE ... SET SCHEMA</command> does not cancel the > exclusion." >
OK. The explanatory text LGTM. What's the next step? Should I create a new thread/patch to deal with this? ====== Kind Regards. Peter Smith Fujitsu Australia
