On Fri, Sep 9, 2022 at 11:44 AM Tom Lane <t...@sss.pgh.pa.us> wrote: > > Amit Kapila <amit.kapil...@gmail.com> writes: > > To avoid these confusions, we have disallowed adding a table if its > > schema is already part of the publication and vice-versa. > > Really? > > Is there logic in ALTER TABLE SET SCHEMA that rejects the command > dependent on the contents of the publication tables? >
Yes, it has. For example, postgres=# create schema s1; CREATE SCHEMA postgres=# create table s1.t1(c1 int); CREATE TABLE postgres=# create schema s2; CREATE SCHEMA postgres=# create publication pub1 for all tables in schema s2, table s1.t1; CREATE PUBLICATION postgres=# Alter table s1.t1 set schema s2; ERROR: cannot move table "t1" to schema "s2" DETAIL: The schema "s2" and same schema's table "t1" cannot be part of the same publication "pub1". > If so, are > there locks taken in both ALTER TABLE SET SCHEMA and the > publication-modifying commands that are sufficient to prevent > race conditions in such changes? > Good point. I have checked it and found that ALTER TABLE SET SCHEMA takes AccessExclusiveLock on relation and AccessShareLock on the schema which it is going to set. The alter publication command takes ShareUpdateExclusiveLock on relation for dropping/adding a table to publication which will prevent any race condition with ALTER TABLE SET SCHEMA. However, the alter publication command takes AccessShareLock for dropping/adding schema which won't block with ALTER TABLE SET SCHEMA command. So, I think we need to change the lock mode for it in alter publication command. > This position sounds quite untenable from here, even if I found > your arguments-in-support convincing, which I don't really. > ISTM the rule should be along the lines of "table S.T should > be published either if schema S is published or S.T itself is". > There's no obvious need to interconnect the two conditions. > This rule is currently followed when a subscription has more than one publication. It is just that we didn't allow it in the same publication because of a fear that it may cause confusion for some of the users. The other thing to look at here is that the existing case of a "FOR ALL TABLES" publication also follows a similar rule such that it doesn't allow adding individual tables if the publication is for all tables. For example, postgres=# create publication pub1 for all tables; CREATE PUBLICATION postgres=# alter publication pub1 add table t1; ERROR: publication "pub1" is defined as FOR ALL TABLES DETAIL: Tables cannot be added to or dropped from FOR ALL TABLES publications. So, why shouldn't a "FOR ALL TABLES IN SCHEMA" publication follow a similar behavior? -- With Regards, Amit Kapila.
