Re: Added schema level support for publication. - Mailing list pgsql-hackers

On Tue, Aug 31, 2021 at 1:41 PM vignesh C <vignesh21@gmail.com> wrote:
>
> >
> > I notice that "CREATE PUBLICATION pub1 FOR ALL TABLES IN SCHEMA sc1,
> > TABLE sc1.test;"  maintains the table separately and results in the
> > following in the \dRp+ output:
> >
> > Tables:
> >     "sc1.test"
> > Schemas:
> >     "sc1"
> >
> > and also then "ALTER PUBLICATION pub1 DROP ALL TABLES IN SCHEMA sc1;"
> > still leaves the "sc1.test" table in the publication.
>
> I had intentionally implemented this way, the reason being it gives
> the flexibility to modify the publications based on the way the
> publication is created. My idea was that if a user specified a
> table/schema of the same schema while creating the publication, the
> user should be allowed to drop any of them at any time. In the above
> case if we don't maintain the results separately, users will not be
> able to drop the table from the publication at a later point of time.
> Thoughts?
>

Hmmm. I'm not sure it should work like that (but maybe I'm wrong -
what do others think???).
I thought that "CREATE PUBLICATION pub1 FOR ALL TABLES IN SCHEMA sc1,
TABLE sc1.test;" should silently just ignore the "TABLE sc1.test"
part, as that is a table in schema sc1, so it's effectively a
duplicate.

Also, I noticed the following:

postgres=# CREATE PUBLICATION pub1 FOR ALL TABLES IN SCHEMA sc1,
postgres-# TABLE sc1.test;
CREATE PUBLICATION
postgres=# \dRp+
                            Publication pub1
 Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
-------+------------+---------+---------+---------+-----------+----------
 gregn | f          | t       | t       | t       | t         | f
Tables:
    "sc1.test"
Schemas:
    "sc1"

postgres=# ALTER PUBLICATION pub1 DROP ALL TABLES IN SCHEMA sc1;
ALTER PUBLICATION
postgres=# \dRp+
                            Publication pub1
 Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
-------+------------+---------+---------+---------+-----------+----------
 gregn | f          | t       | t       | t       | t         | f
Tables:
    "sc1.test"

postgres=# ALTER PUBLICATION pub1 DROP ALL TABLES IN SCHEMA sc1;
ERROR:  schema "sc1" is not part of the publication


I think it seems odd to not drop table "sc1.test" from the publication
after "ALTER PUBLICATION pub1 DROP ALL TABLES IN SCHEMA sc1;".
Also, after running that command again, it seems odd to report that
schema sc1 is not part of the publication, when there remains one
table from that schema in the publication.
And shouldn't it say "tables from schema ... are not part of the
publication" rather than "schema ... is not part of the publication"?
I think the former is better and more accurate. Schemas can contain
database objects other than tables.
Similarly, I'm also thinking that in the "\dRp+" output, it should say
"Tables from schemas:" instead of "Schemas:".


Regards,
Greg Nancarrow
Fujitsu Australia