On Thu, Sep 8, 2022 at 5:06 PM Peter Eisentraut
<peter.eisentraut@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.