v1-0002-Column-Lists-new-pgdocs-section.patch

From 508a24849f7ce177a8fa2d8da8daa9193570ee59 Mon Sep 17 00:00:00 2001
From: Peter Smith <peter.b.smith@fujitsu.com>
Date: Mon, 8 Aug 2022 18:28:06 +1000
Subject: [PATCH v1] Column Lists - new pgdocs section

Add a new logical replication pgdocs section for "Column Lists"
(analogous to the Row Filters page).

Also update xrefs to that new page from CREATE/ALTER PUBLICATION.
---
 doc/src/sgml/logical-replication.sgml    | 196 +++++++++++++++++++++++++++++++
 doc/src/sgml/ref/alter_publication.sgml  |  11 +-
 doc/src/sgml/ref/create_publication.sgml |   4 +-
 3 files changed, 201 insertions(+), 10 deletions(-)

diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index bdf1e7b..af0310d 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -1089,6 +1089,202 @@ test_sub=# SELECT * FROM child ORDER BY a;
 
  </sect1>
 
+ <sect1 id="logical-replication-col-lists">
+  <title>Column Lists</title>
+
+  <para>
+   By default, all columns of a published table will be replicated to the
+   appropriate subscribers. The subscriber table must have at least all the
+   columns of the published table. However, if a <firstterm>column list</firstterm>
+   is specified then only the columns named in the list will be replicated.
+   This means the subscriber-side table only needs to have those columns named
+   by the column list. A user might choose to use column lists for behavioral,
+   security or performance reasons.
+  </para>
+
+  <sect2 id="logical-replication-col-list-rules">
+   <title>Column List Rules</title>
+
+   <para>
+    A column list is specified per table following the tablename, and enclosed by
+    parenthesis. The column name order of the list has no effect. See
+    <xref linkend="sql-createpublication"/> for details.
+   </para>
+
+   <para>
+    When a column list is specified, only the named columns are replicated.
+    If no column list is specified, all columns of the table are replicated
+    through this publication, including any columns added later. This means
+    a column list which names all columns is not quite the same as having no
+    column list at all. For example, if additional columns are added to the
+    table, then (after a <literal>REFRESH PUBLICATION</literal>) if there was
+    a column list only those named columns will continue to be replicated.
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-col-list-restrictions">
+   <title>Column List Restrictions</title>
+
+   <para>
+    Column list can contain only simple column references. Complex
+    expressions, function calls etc. are not allowed.
+   </para>
+
+   <para>
+    If a publication publishes <command>UPDATE</command> or
+    <command>DELETE</command> operations, any column list must include the table's
+    replica identity columns (see <xref linkend="sql-altertable-replica-identity"/>).
+    If a publication publishes only <command>INSERT</command> operations, then
+    the column list is arbitrary and may omit some replica identity columns.
+   </para>
+
+   <para>
+    Furthermore, if the table uses <literal>REPLICA IDENTITY FULL</literal>,
+    specifying a column list is not allowed.
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-col-list-partitioned">
+   <title>Partitioned Tables</title>
+
+   <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
+    <literal>true</literal>, the root partitioned table's column list is used.
+    Otherwise, if <literal>publish_via_partition_root</literal> is
+    <literal>false</literal> (default), each partition's column list is used.
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-col-list-combining">
+   <title>Combining Multiple Column Lists</title>
+
+   <warning>
+    <para>
+     It is not supported to have a subscription comprising several publications
+     where the same table has been published with different column lists.
+     This means changing the column lists of the tables being subscribed could
+     cause inconsistency of column lists among publications, in which case
+     the <xref linkend="sql-alterpublication"/> will be successful but later the
+     WalSender on the publisher, or the subscriber may throw an error. In this
+     scenario, the user needs to recreate the subscription after adjusting the
+     column list or drop the problematic publication using
+     <literal>ALTER SUBSCRIPTION ... DROP PUBLICATION</literal> and then add it
+     back after adjusting the column list.
+    </para>
+    <para>
+     Background: The main purpose of the column list feature is to allow statically
+     different table shapes on publisher and subscriber or hide sensitive
+     column data. In both cases, it doesn't seem to make sense to combine
+     column lists.
+    </para>
+   </warning>
+
+  </sect2>
+
+  <sect2 id="logical-replication-col-list-examples">
+   <title>Examples</title>
+
+   <para>
+    Create a table <literal>t1</literal> to be used in the following example.
+<programlisting>
+test_pub=# CREATE TABLE t1(id int, a text, b text, c text, d text, e text, PRIMARY KEY(id));
+CREATE TABLE
+test_pub=#
+</programlisting></para>
+
+   <para>
+    Create a publication <literal>p1</literal>. A column list is defined for
+    table <literal>t1</literal> to reduce the number of columns that will be
+    replicated.
+<programlisting>
+test_pub=# CREATE PUBLICATION p1 FOR TABLE t1 (id, a, b, c);
+CREATE PUBLICATION
+test_pub=#
+</programlisting></para>
+
+    <para>
+     <literal>psql</literal> can be used to show the column lists (if defined)
+     for each publication.
+<programlisting>
+test_pub=# \dRp+
+                               Publication p1
+  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root
+----------+------------+---------+---------+---------+-----------+----------
+ postgres | f          | t       | t       | t       | t         | f
+Tables:
+    "public.t1" (id, a, b, c)
+</programlisting></para>
+
+    <para>
+     <literal>psql</literal> can be used to show the column lists (if defined)
+     for each table.
+<programlisting>
+test_pub=# \d t1
+                 Table "public.t1"
+ Column |  Type   | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ id     | integer |           | not null |
+ a      | text    |           |          |
+ b      | text    |           |          |
+ c      | text    |           |          |
+ d      | text    |           |          |
+ e      | text    |           |          |
+Indexes:
+    "t1_pkey" PRIMARY KEY, btree (id)
+Publications:
+    "p1" (id, a, b, c)
+</programlisting></para>
+
+    <para>
+     On the subscriber node, create a table <literal>t1</literal> which now
+     only needs a subset of the columns that were on the publisher table
+     <literal>t1</literal>, and also create the subscription <literal>s1</literal>
+     that subscribes to the publication <literal>p1</literal>.
+<programlisting>
+test_sub=# CREATE TABLE t1(id int, a text, b text, c text, PRIMARY KEY(id));
+CREATE TABLE
+test_sub=# CREATE SUBSCRIPTION s1
+test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=s1'
+test_sub-# PUBLICATION p1;
+CREATE SUBSCRIPTION
+</programlisting></para>
+
+    <para>
+     Insert some rows to table <literal>t1</literal>.
+<programlisting>
+test_pub=# INSERT INTO t1 VALUES(1, 'a-1', 'b-1', 'c-1', 'd-1', 'e-1');
+INSERT 0 1
+test_pub=# INSERT INTO t1 VALUES(2, 'a-2', 'b-2', 'c-2', 'd-2', 'e-2');
+INSERT 0 1
+test_pub=# INSERT INTO t1 VALUES(3, 'a-3', 'b-3', 'c-3', 'd-3', 'e-3');
+INSERT 0 1
+test_pub=# SELECT * FROM t1 ORDER BY id;
+ id |  a  |  b  |  c  |  d  |  e
+----+-----+-----+-----+-----+-----
+  1 | a-1 | b-1 | c-1 | d-1 | e-1
+  2 | a-2 | b-2 | c-2 | d-2 | e-2
+  3 | a-3 | b-3 | c-3 | d-3 | e-3
+(3 rows)
+</programlisting>
+<programlisting>
+test_sub=# SELECT * FROM t1 ORDER BY id;
+ id |  a  |  b  |  c
+----+-----+-----+-----
+  1 | a-1 | b-1 | c-1
+  2 | a-2 | b-2 | c-2
+  3 | a-3 | b-3 | c-3
+(3 rows)
+</programlisting></para>
+
+  </sect2>
+
+ </sect1>
+
  <sect1 id="logical-replication-conflicts">
   <title>Conflicts</title>
 
diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index 3e338f4..a8f283d 100644
--- a/doc/src/sgml/ref/alter_publication.sgml
+++ b/doc/src/sgml/ref/alter_publication.sgml
@@ -118,15 +118,8 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
       Optionally, a column list can be specified.  See <xref
       linkend="sql-createpublication"/> for details. Note that a subscription
       having several publications in which the same table has been published
-      with different column lists is not supported. So, changing the column
-      lists of the tables being subscribed could cause inconsistency of column
-      lists among publications, in which case <command>ALTER PUBLICATION</command>
-      will be successful but later the WalSender on the publisher or the
-      subscriber may throw an error. In this scenario, the user needs to
-      recreate the subscription after adjusting the column list or drop the
-      problematic publication using
-      <literal>ALTER SUBSCRIPTION ... DROP PUBLICATION</literal> and then add
-      it back after adjusting the column list.
+      with different column lists is not supported. See <xref linkend="logical-replication-col-list-combining"/>
+      for details of potential problems when altering column lists.
      </para>
 
      <para>
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index 2d6de8d..5669d51 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -90,7 +90,9 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
      <para>
       When a column list is specified, only the named columns are replicated.
       If no column list is specified, all columns of the table are replicated
-      through this publication, including any columns added later.
+      through this publication, including any columns added later. See
+      <xref linkend="logical-replication-col-lists"/> for details about column
+      lists.
      </para>
 
      <para>
-- 
1.8.3.1