v33-0004-Documentation-for-FOR-ALL-TABLES-IN-SCHEMA-publi.patch

From d39c5e98bba80b2bfcdb306a726e624f2d019677 Mon Sep 17 00:00:00 2001
From: Vignesh C <vignesh21@gmail.com>
Date: Fri, 3 Sep 2021 17:46:08 +0530
Subject: [PATCH v33 4/6] Documentation for "FOR ALL TABLES IN SCHEMA"
 publication.

Documentation for "FOR ALL TABLES IN SCHEMA" publication.
---
 doc/src/sgml/catalogs.sgml               | 72 ++++++++++++++++++-
 doc/src/sgml/ref/alter_publication.sgml  | 88 +++++++++++++++++++-----
 doc/src/sgml/ref/create_publication.sgml | 73 ++++++++++++++++++--
 3 files changed, 207 insertions(+), 26 deletions(-)

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 2f0def9b19..c18a90a691 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -240,6 +240,11 @@
       <entry>publications for logical replication</entry>
      </row>
 
+     <row>
+      <entry><link linkend="catalog-pg-publication-namespace"><structname>pg_publication_namespace</structname></link></entry>
+      <entry>schema to publication mapping</entry>
+     </row>
+
      <row>
       <entry><link linkend="catalog-pg-publication-rel"><structname>pg_publication_rel</structname></link></entry>
       <entry>relation to publication mapping</entry>
@@ -6236,6 +6241,67 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
   </table>
  </sect1>
 
+ <sect1 id="catalog-pg-publication-namespace">
+  <title><structname>pg_publication_namespace</structname></title>
+
+  <indexterm zone="catalog-pg-publication-namespace">
+   <primary>pg_publication_namespace</primary>
+  </indexterm>
+
+  <para>
+   The catalog <structname>pg_publication_namespace</structname> contains the
+   mapping between schemas and publications in the database.  This is a
+   many-to-many mapping.  
+  </para>
+
+  <table>
+   <title><structname>pg_publication_namespace</structname> Columns</title>
+   <tgroup cols="1">
+    <thead>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       Column Type
+      </para>
+      <para>
+       Description
+      </para></entry>
+     </row>
+    </thead>
+
+    <tbody>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>oid</structfield> <type>oid</type>
+      </para>
+      <para>
+       Row identifier
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>pnpubid</structfield> <type>oid</type>
+       (references <link linkend="catalog-pg-publication"><structname>pg_publication</structname></link>.<structfield>oid</structfield>)
+      </para>
+      <para>
+       Reference to publication
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>pnnspid</structfield> <type>oid</type>
+       (references <link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.<structfield>oid</structfield>)
+      </para>
+      <para>
+       Reference to schema
+      </para></entry>
+     </row>
+    </tbody>
+   </tgroup>
+  </table>
+ </sect1>
+
  <sect1 id="catalog-pg-range">
   <title><structname>pg_range</structname></title>
 
@@ -11276,9 +11342,9 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
    information about the mapping between publications and the tables they
    contain.  Unlike the underlying catalog
    <link linkend="catalog-pg-publication-rel"><structname>pg_publication_rel</structname></link>,
-   this view expands
-   publications defined as <literal>FOR ALL TABLES</literal>, so for such
-   publications there will be a row for each eligible table.
+   this view expands publications defined as <literal>FOR ALL TABLES</literal>
+   and <literal>FOR ALL TABLES IN SCHEMA</literal>, so for such publications
+   there will be a row for each eligible table.
   </para>
 
   <table>
diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index faa114b2c6..981e38189f 100644
--- a/doc/src/sgml/ref/alter_publication.sgml
+++ b/doc/src/sgml/ref/alter_publication.sgml
@@ -21,12 +21,17 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
-ALTER PUBLICATION <replaceable class="parameter">name</replaceable> ADD TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ...]
-ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ...]
-ALTER PUBLICATION <replaceable class="parameter">name</replaceable> DROP TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ...]
+ALTER PUBLICATION <replaceable class="parameter">name</replaceable> ADD <replaceable class="parameter">publication_object</replaceable> [, ...]
+ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET <replaceable class="parameter">publication_object</replaceable> [, ...]
+ALTER PUBLICATION <replaceable class="parameter">name</replaceable> DROP <replaceable class="parameter">publication_object</replaceable> [, ...]
 ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )
 ALTER PUBLICATION <replaceable class="parameter">name</replaceable> OWNER TO { <replaceable>new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
 ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <replaceable>new_name</replaceable>
+
+<phrase>where <replaceable class="parameter">publication_object</replaceable> is one of:</phrase>
+
+    TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ... ]
+    ALL TABLES IN SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ... ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -39,14 +44,16 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
   </para>
 
   <para>
-   The first three variants change which tables are part of the publication.
-   The <literal>SET TABLE</literal> clause will replace the list of tables in
-   the publication with the specified one.  The <literal>ADD TABLE</literal>
-   and <literal>DROP TABLE</literal> clauses will add and remove one or more
-   tables from the publication.  Note that adding tables 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.
+   The first three variants change which tables/schemas are part of the
+   publication.  The <literal>SET</literal> clause will replace the list of
+   tables/schemas in the publication with the specified list; the existing
+   tables/schemas that were present in the publication will be removed.  The
+   <literal>ADD</literal> clause will add one or more tables/schemas to the
+   publication. The <literal>DROP</literal> clauses will remove one or more
+   tables/schemas from the publication.  Note that adding tables/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.
   </para>
 
   <para>
@@ -63,11 +70,24 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
   <para>
    You must own the publication to use <command>ALTER PUBLICATION</command>.
    Adding a table to a publication additionally requires owning that table.
-   To alter the owner, you must also be a direct or indirect member of the new
-   owning role. The new owner must have <literal>CREATE</literal> privilege on
-   the database.  Also, the new owner of a <literal>FOR ALL TABLES</literal>
-   publication must be a superuser.  However, a superuser can change the
-   ownership of a publication regardless of these restrictions.
+   The <literal>ADD ALL TABLES IN SCHEMA</literal> and
+   <literal>SET ALL TABLES IN SCHEMA</literal> to a publication requires the
+   invoking user to be a superuser.  To alter the owner, you must also be a
+   direct or indirect member of the new owning role. The new owner must have
+   <literal>CREATE</literal> privilege on the database.  Also, the new owner
+   of a <literal>FOR ALL TABLES</literal> publication must be a superuser.
+   However, a superuser can change the ownership of a publication regardless
+   of these restrictions.
+  </para>
+
+  <para>
+   Adding/Setting a table that is part of schema specified in
+   <literal>ALL TABLES IN SCHEMA</literal>, adding/setting a schema to a
+   publication along with same schema's table specified with
+   <literal>TABLE</literal>, adding/setting a schema to a publication that
+   already has a table that is part of specified schema or adding/setting a
+   table to a publication that already has a table's schema as part of
+   specified schema is not supported.
   </para>
  </refsect1>
 
@@ -97,6 +117,15 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><replaceable class="parameter">schema_name</replaceable></term>
+    <listitem>
+     <para>
+      Name of an existing schema.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>SET ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
     <listitem>
@@ -141,6 +170,33 @@ ALTER PUBLICATION noinsert SET (publish = 'update, delete');
    Add some tables to the publication:
 <programlisting>
 ALTER PUBLICATION mypublication ADD TABLE users, departments;
+</programlisting></para>
+
+  <para>
+   Add some schemas to the publication:
+<programlisting>
+ALTER PUBLICATION sales_publication ADD ALL TABLES IN SCHEMA marketing_june, sales_june;
+</programlisting>
+  </para>
+
+  <para>
+   Add some tables and schemas to the publication:
+<programlisting>
+ALTER PUBLICATION production_publication ADD TABLE users, departments, ALL TABLES IN SCHEMA production;
+</programlisting>
+  </para>
+
+  <para>
+   Drop some schemas from the publication:
+<programlisting>
+ALTER PUBLICATION production_quarterly_publication DROP ALL TABLES IN SCHEMA production_july, production_august;
+</programlisting>
+  </para>
+
+  <para>
+   Set some schemas to the publication:
+<programlisting>
+ALTER PUBLICATION production_publication SET ALL TABLES IN SCHEMA production_september, production_october;
 </programlisting></para>
  </refsect1>
 
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index ff82fbca55..938237ae05 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -22,9 +22,14 @@ PostgreSQL documentation
  <refsynopsisdiv>
 <synopsis>
 CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
-    [ FOR TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ...]
-      | FOR ALL TABLES ]
+    [ FOR ALL TABLES 
+      | FOR <replaceable class="parameter">publication object</replaceable> [, ... ] ]
     [ WITH ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
+
+<phrase>where <replaceable class="parameter">publication object</replaceable> is one of:</phrase>
+
+    TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ... ]
+    ALL TABLES IN SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ... ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -86,6 +91,11 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
       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 ALL TABLES IN SCHEMA</literal> is not supported.
+     </para>
     </listitem>
    </varlistentry>
 
@@ -99,6 +109,28 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>FOR ALL TABLES IN SCHEMA</literal></term>
+    <listitem>
+     <para>
+      Marks the publication as one that replicates changes for all tables in
+      the specified list of schemas, including tables created in the future.
+     </para>
+
+     <para>
+      Specifying a schema along with a table which belongs to the specified
+      schema using <literal>FOR TABLE</literal> is not supported.
+     </para>
+
+     <para>
+      Only persistent base tables and partitioned tables present in the schema 
+      will be included as part of the publication.  Temporary tables, unlogged
+      tables, foreign tables, materialized views, and regular views from the
+      schema will not be part of the publication.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>WITH ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
     <listitem>
@@ -153,9 +185,10 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
   <title>Notes</title>
 
   <para>
-   If neither <literal>FOR TABLE</literal> nor <literal>FOR ALL
-   TABLES</literal> is specified, then the publication starts out with an
-   empty set of tables.  That is useful if tables are to be added later.
+   If <literal>FOR TABLE</literal>, <literal>FOR ALL TABLES</literal> or
+   <literal>FOR ALL TABLES IN SCHEMA</literal> is not specified, then the
+   publication starts out with an empty set of tables.  That is useful if
+   tables or schemas are to be added later.
   </para>
 
   <para>
@@ -171,8 +204,9 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
 
   <para>
    To add a table to a publication, the invoking user must have ownership
-   rights on the table.  The <command>FOR ALL TABLES</command> clause requires
-   the invoking user to be a superuser.
+   rights on the table.  The <command>FOR ALL TABLES</command> and
+   <command>FOR ALL TABLES IN SCHEMA</command> clauses require the invoking
+   user to be a superuser.
   </para>
 
   <para>
@@ -222,6 +256,31 @@ CREATE PUBLICATION alltables FOR ALL TABLES;
 <programlisting>
 CREATE PUBLICATION insert_only FOR TABLE mydata
     WITH (publish = 'insert');
+</programlisting>
+  </para>
+
+  <para>
+   Create a publication that publishes all changes for all the tables present in
+   the schema "production":
+<programlisting>
+CREATE PUBLICATION production_publication FOR ALL TABLES IN SCHEMA production;
+</programlisting>
+  </para>
+
+  <para>
+   Create a publication that publishes all changes for tables "users" and
+   "departments" and that publishes all changes for all the tables present in
+   the schema "production":
+<programlisting>
+CREATE PUBLICATION production_publication FOR TABLE users, departments, ALL TABLES IN SCHEMA production;
+</programlisting>
+  </para>
+
+  <para>
+   Create a publication that publishes all changes for all the tables present in
+   the schemas "marketing" and "sales":
+<programlisting>
+CREATE PUBLICATION sales_publication FOR ALL TABLES IN SCHEMA marketing, sales;
 </programlisting></para>
  </refsect1>
 
-- 
2.30.2