On Thu, Sep 8, 2022 at 5:06 PM Peter Eisentraut <peter.eisentr...@enterprisedb.com> wrote: > > Apparently, you can't add a table to a publication if its schema is > already part of the publication (and vice versa), e.g., > > =# alter publication p1 add table s1.t1; > ERROR: 22023: cannot add relation "s1.t1" to publication > DETAIL: Table's schema "s1" is already part of the publication or part > of the specified schema list. > > Is there a reason for this? >
Yes, because otherwise, there was confusion while dropping the objects from publication. Consider in the above case, if we would have allowed it and then the user performs ALTER PUBLICATION p1 DROP ALL TABLES IN SCHEMA s1, then (a) shall we remove both schema s1 and a table that is separately added (s1.t1) from that schema, or (b) just remove schema s1? There is a point that the user can expect that as she has added them separately, so we should allow her to drop them separately as well. OTOH, if we go that way, then it is again questionable that when the user has asked to Drop all the tables in the schema (via DROP ALL TABLES IN SCHEMA command) then why keep some tables? The other confusion from allowing publications to have schemas and tables from the same schema is that say the user has created a publication with the command CREATE PUBLICATION pub1 FOR ALL TABLES IN SCHEMA s1, and then she can ask to allow dropping one of the tables in the schema by ALTER PUBLICATION pub1 DROP TABLE s1.t1. Now, it will be tricky to support and I think it doesn't make sense as well. Similarly, what if the user has first created a publication with "CREATE PUBLICATION pub1 ADD TABLE s1.t1, s1.t2, ... s1.t99;" and then tries to drop all tables in one shot by ALTER PUBLICATION DROP ALL TABLES IN SCHEMA sch1;? To avoid these confusions, we have disallowed adding a table if its schema is already part of the publication and vice-versa. Also, there won't be any additional benefit to doing so. -- With Regards, Amit Kapila.
