Allow publications with schema and table of the same schema.

We previously thought that allowing such cases can confuse users when they
specify DROP TABLES IN SCHEMA but that doesn't seem to be the case based
on discussion. This helps to uplift the restriction during
ALTER TABLE ... SET SCHEMA which used to ensure that we couldn't end up
with a publication having both a schema and the same schema's table.

To allow this, we need to forbid having any schema on a publication if
column lists on a table are specified (and vice versa). This is because
otherwise we still need a restriction during ALTER TABLE ... SET SCHEMA to
forbid cases where it could lead to a publication having both a schema and
the same schema's table with column list.

Based on suggestions by Peter Eisentraut.

Author: Hou Zhijie and Vignesh C
Reviewed-By: Peter Smith, Amit Kapila
Backpatch-through: 15, where it was introduced
Discussion: https://postgr.es/m/2729c9e2-9aac-8cda-f2f4-34f2bcc18f4e@enterprisedb.com

3 files changed, 25 insertions, 15 deletions

diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index 7fe3a1043e6..77be4c37e7f 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -1120,6 +1120,11 @@ test_sub=# SELECT * FROM child ORDER BY a;
   </para>
 
   <para>
+   Specifying a column list when the publication also publishes
+   <literal>FOR TABLES IN SCHEMA</literal> is not supported.
+  </para>
+
+  <para>
    For partitioned tables, the publication parameter
    <literal>publish_via_partition_root</literal> determines which column list
    is used. If <literal>publish_via_partition_root</literal> is
diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index fc2d6d4885f..c84b11f47a7 100644
--- a/doc/src/sgml/ref/alter_publication.sgml
+++ b/doc/src/sgml/ref/alter_publication.sgml
@@ -52,9 +52,11 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
    remove one or more tables/schemas from the publication.  Note that adding
    tables/schemas to a publication that is already subscribed to will require an
    <literal>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</literal> action on the
-   subscribing side in order to become effective. Note also that the combination
-   of <literal>DROP</literal> with a <literal>WHERE</literal> clause is not
-   allowed.
+   subscribing side in order to become effective. Note also that
+   <literal>DROP TABLES IN SCHEMA</literal> will not drop any schema tables
+   that were specified using <literal>FOR TABLE</literal>/
+   <literal>ADD TABLE</literal>, and the combination of <literal>DROP</literal>
+   with a <literal>WHERE</literal> clause is not allowed.
   </para>
 
   <para>
@@ -82,11 +84,8 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
   </para>
 
   <para>
-   Adding/Setting a table that is part of schema specified in
-   <literal>TABLES IN SCHEMA</literal>, adding/setting a schema to a
-   publication that already has a table that is part of the specified schema or
-   adding/setting a table to a publication that already has a table's schema as
-   part of the specified schema is not supported.
+   Adding/Setting any schema when the publication also publishes a table with a
+   column list, and vice versa is not supported.
   </para>
  </refsect1>
 
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index 2e097a81e52..c5190f0ce64 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -103,17 +103,17 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
      </para>
 
      <para>
+      Specifying a column list when the publication also publishes
+      <literal>FOR TABLES IN SCHEMA</literal> is not supported.
+     </para>
+
+     <para>
       When a partitioned table is added to a publication, all of its existing
       and future partitions are implicitly considered to be part of the
       publication.  So, even operations that are performed directly on a
       partition are also published via publications that its ancestors are
       part of.
      </para>
-
-     <para>
-      Specifying a table that is part of a schema specified by
-      <literal>FOR TABLES IN SCHEMA</literal> is not supported.
-     </para>
     </listitem>
    </varlistentry>
 
@@ -136,8 +136,8 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
      </para>
 
      <para>
-      Specifying a schema along with a table which belongs to the specified
-      schema using <literal>FOR TABLE</literal> is not supported.
+      Specifying a schema when the publication also publishes a table with a
+      column list is not supported.
      </para>
 
      <para>
@@ -274,6 +274,12 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
   </para>
 
   <para>
+   The row filter on a table becomes redundant if
+   <literal>FOR TABLES IN SCHEMA</literal> is specified and the table
+   belongs to the referred schema.
+  </para>
+
+  <para>
    For published partitioned tables, the row filter for each
    partition is taken from the published partitioned table if the
    publication parameter <literal>publish_via_partition_root</literal> is true,