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." -- With Regards, Amit Kapila.
