Re: Added schema level support for publication.

On Tue, Aug 31, 2021 at 8:57 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> I find the way it is implemented to be more intuitive as that gives
> users more flexibility to retain certain tables from the schema and
> appears to be exactly what users intended by the command. I don't
> think finding duplicates among different object lists (schema, table)
> is a good idea because tomorrow for some other objects the same thing
> can happen. It might be better to get some other opinions on this
> matter though.
>

I think that such functionality needs to be clearly documented (but
currently the documentation doesn't sufficiently explain it).
Yes, I would definitely like to hear other opinions on this.

Note also that currently parts of the documentation are still
referring to "ADD SCHEMA/DROP SCHEMA/SET SCHEMA" instead of the new
syntax "ADD ALL TABLES IN SCHEMA/DROP ALL TABLES IN SCHEMA/SET ALL
TABLES IN SCHEMA":

e.g.
v23-0003:
doc/src/sgml/ref/alter_publication.sgml
+   The fourth, fifth and sixth variants of this command change which schemas
+   are part of the publication.  The <literal>SET SCHEMA</literal> clause will
+   replace the list of schemas in the publication with the specified one.
+   The <literal>ADD SCHEMA</literal> and <literal>DROP SCHEMA</literal> clauses
+   will add and remove one or more schemas from the publication.  Note that
+   adding schemas to a publication that is already subscribed to will require
+   a <literal>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</literal> action on
+   the subscribing side in order to become effective.

With the new syntax changes (SCHEMA -> ALL TABLES IN SCHEMA), it seems
less intuitive that there are separate schema and table operations on
the publication.

I'd expect a lot of users to naturally think that "ALTER PUBLICATION
pub1 DROP ALL TABLES IN SCHEMA sc1;" would drop from the publication
all tables that are in schema "sc1", which is not what it is currently
doing.
Since the syntax was changed to specifically refer to FOR ALL TABLES
IN SCHEMA rather than FOR SCHEMA, then now it's clear we're referring
to tables only, when specifying "... FOR ALL TABLES in sc1, TABLE
sc1.test", so IMHO it's reasonable to remove duplicates here, rather
than treating these as somehow separate ways of referencing the same
table.

One thing the current scheme doesn't allow is to publish all tables in
a schema except for certain table(s) - and you can't achieve that by
adding all tables from a schema to the publication and then
selectively dropping some of those tables. I thought that this would
be a more common pattern than adding separate tables from schemas that
are already included as part of the publication, in order to "retain"
them if "all tables from schema ..." are later dropped.

postgres=# create schema sc1;
CREATE SCHEMA
postgres=# create table sc1.test(i int);
CREATE TABLE
postgres=# create publication pub1 for all tables in schema sc1;
CREATE PUBLICATION
postgres=# \dRp+

                            Publication pub1
 Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
-------+------------+---------+---------+---------+-----------+----------
 gregn | f          | t       | t       | t       | t         | f
Schemas:
    "sc1"

postgres=# alter publication pub1 drop table sc1.test;
ERROR:  relation "test" is not part of the publication

The above error message seems slightly misleading (as that table IS
published by that publication) and also note the relation is not
schema-qualified.



Regards,
Greg Nancarrow
Fujitsu Australia