On Fri, Sep 9, 2022 at 11:44 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Amit Kapila <amit.kapila16@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.
