PG DOCS - logical replication filtering

From e39bd692eb3adeb8029b1ff409aca49b861d46d9 Mon Sep 17 00:00:00 2001
From: Peter Smith <peter.b.smith@fujitsu.com>
Date: Wed, 2 Mar 2022 15:31:45 +1100
Subject: [PATCH] Update the documentation for logical replication.

This patch introduces a new documentation page for giving an overview of
what kinds of logical replication filtering are available, and how to use them.
The main new content addition to this page is the section describing
"Row Filtering".

Author: Peter Smith
Reviewed By: Greg Nancarrow, Aleksander Alekseev, Amit Kapila
Discussion: https://www.postgresql.org/message-id/CAHut%2BPtnsBr59%3D_NvxXp_%3DS-em0WxyuDOQmSTuHGb4sVhkHffg%40mail.gmail.com
---
 doc/src/sgml/logical-replication.sgml    | 353 ++++++++++++++++++++++-
 doc/src/sgml/ref/create_publication.sgml |   2 +
 2 files changed, 353 insertions(+), 2 deletions(-)

diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index fb4472356d..88ecf2b914 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -116,8 +116,10 @@
   <para>
    Publications can choose to limit the changes they produce to
    any combination of <command>INSERT</command>, <command>UPDATE</command>,
-   <command>DELETE</command>, and <command>TRUNCATE</command>, similar to how triggers are fired by
-   particular event types.  By default, all operation types are replicated.
+   <command>DELETE</command>, and <command>TRUNCATE</command> by using
+   <quote>operation filters</quote>. By default, all operation types are
+   replicated. See <xref linkend="logical-replication-operation-filtering"/>
+   for details.
   </para>
 
   <para>
@@ -317,6 +319,353 @@
   </sect2>
  </sect1>
 
+ <sect1 id="logical-replication-filtering">
+  <title>Filtering</title>
+
+   <para>
+    By default, all data from all published tables will be replicated to the
+    appropriate subscribers.
+   </para>
+
+   <para>
+    The default data replication can be reduced by using filters. A user might
+    choose to use filters for behavioral or for performance reasons.
+   </para>
+
+   <para>
+    There are 2 different ways to filter what data gets replicated.
+   </para>
+
+  <sect2 id="logical-replication-operation-filtering">
+   <title>Operation Filters</title>
+
+   <para>
+    Publications can choose to limit the changes they produce to any combination
+    of <command>INSERT</command>, <command>UPDATE</command>, <command>DELETE</command>,
+    and <command>TRUNCATE</command>, similar to how triggers are fired by
+    particular event types.
+   </para>
+
+   <para>
+    By default, all operation types are replicated.
+   </para>
+
+   <para>
+    Use the <literal>PUBLICATION ...<link linkend="sql-createpublication">
+    WITH (publish = <replaceable class="parameter">value</replaceable>)
+    </link></literal> clause to modify which operations will do replication.
+   </para>
+
+   <sect3 id="logical-replication-opf-examples">
+    <title>Examples</title>
+
+    <para>
+     Create a publication that only publishes <command>INSERT</command>
+     operations in one table:
+<programlisting>
+CREATE PUBLICATION insert_only FOR TABLE mydata WITH (publish = 'insert');
+</programlisting>
+    </para>
+
+   </sect3>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filtering">
+   <title>Row Filters</title>
+
+   <para>
+    With row filtering, the decision whether to replicate row data depends on
+    the row data values. Rows that don't satisfy an optional <literal>WHERE</literal>
+    clause will be filtered out. This allows a database or set of tables to be
+    partially replicated.
+   </para>
+
+   <para>
+    Use the <literal>PUBLICATION ... FOR TABLE </literal>
+    <link linkend="sql-createpublication"><literal>
+    <replaceable class="parameter">table_name</replaceable> WHERE (
+    <replaceable class="parameter">expression</replaceable>)</literal></link>
+    to specify which rows will be replicated.
+   </para>
+
+   <para>
+    The row filtering is defined per table. A new row filter can be added simply
+    by specifying a <literal>WHERE</literal> clause after the table name. The
+    <literal>WHERE</literal> clause must be enclosed by parentheses.
+   </para>
+
+   <sect3 id="logical-replication-rf-rules">
+    <title>Row filter Rules</title>
+
+    <para>
+     If the row filter evaluates to <literal>false</literal> or
+     <literal>NULL</literal> then the row is not replicated.
+    </para>
+
+    <para>
+     Row filters are applied <emphasis>before</emphasis> publishing the changes.
+    </para>
+
+    <para>
+     The <literal>WHERE</literal> clause expression is evaluated with the same
+     role used for the replication connection.
+    </para>
+
+    <para>
+     Row filters have no effect for <command>TRUNCATE</command> commands.
+    </para>
+
+   </sect3>
+
+   <sect3 id="logical-replication-rf-transformations">
+    <title>UPDATE transformations</title>
+
+    <para>
+     Whenever an <command>UPDATE</command> is processed, the row filter
+     expression is evaluated for both the old and new row (i.e. before
+     and after the data is updated).
+    </para>
+
+    <para>
+     If both evaluations are <literal>true</literal>, it replicates the
+     <command>UPDATE</command>.
+    </para>
+
+    <para>
+     If both evaluations are <literal>false</literal>, it doesn't replicate
+     anything.
+    </para>
+
+    <para>
+     If only one of the old/new rows matches the row filter expression, the
+     <command>UPDATE</command> is transformed to <command>INSERT</command> or
+     <command>DELETE</command>, to avoid any data inconsistency, as follows:
+<synopsis>
+Case 1: old-row (no match)    new-row (no match)   -->   (drop change)
+Case 2: old-row (no match)    new row (match)      -->   INSERT
+Case 3: old-row (match)       new-row (no match)   -->   DELETE
+Case 4: old-row (match)       new row (match)      -->   UPDATE
+</synopsis>
+    </para>
+
+   </sect3>
+
+   <sect3 id="logical-replication-rf-partitions">
+    <title>Partition tables</title>
+
+    <para>
+     If the publication contains a partitioned table, the publication parameter
+     <literal>publish_via_partition_root</literal> determines which row filter
+     is used.
+     <itemizedlist>
+
+      <listitem>
+       <para>
+        If <literal>publish_via_partition_root</literal> is <literal>false</literal>
+       (default), each <emphasis>partition's</emphasis> row filter is used.
+       </para>
+      </listitem>
+
+      <listitem>
+       <para>
+        If <literal>publish_via_partition_root</literal> is <literal>true</literal>,
+        the <emphasis>root partitioned table's</emphasis> row filter is used.
+       </para>
+      </listitem>
+
+     </itemizedlist>
+    </para>
+
+   </sect3>
+
+   <sect3 id="logical-replication-rf-restrictions">
+    <title>WHERE clause Expression Restrictions</title>
+
+    <para>
+     The row filter <literal>WHERE</literal> clause for a table added to a
+     publication that publishes <command>UPDATE</command> and/or
+     <command>DELETE</command> operations must contain only columns that are
+     covered by the <quote>replica identity</quote>. The row filter
+     <literal>WHERE</literal> clause for a table added to a publication that
+     publishes <command>INSERT</command> can use any column.
+    </para>
+
+    <para>
+     The <literal>WHERE</literal> clause allows only simple expressions that
+     don't have user-defined functions, operators, non-immutable built-in
+     functions, or references to system columns.
+    </para>
+
+   </sect3>
+
+   <sect3 id="logical-replication-rf-tablesync">
+    <title>Subscriber initial table synchronization</title>
+
+    <para>
+     If you choose to do the initial table synchronization, only data that
+     satisfies the row filters is copied to the subscriber.
+    </para>
+
+    <para>
+     If the subscription has several publications in which a table has been
+     published with different <literal>WHERE</literal> clauses, rows that satisfy
+     <emphasis>any</emphasis> of the expressions will be copied. (see
+     <xref linkend="logical-replication-rf-combining"/>).
+    </para>
+
+    <note>
+     <para>
+      Publication operations of filters are ignored during initial table
+      synchronization.
+     </para>
+    </note>
+
+    <note>
+     <para>
+      If a subscriber is a pre-15 version, the initial table synchronization
+      won't use row filters even if they are defined in the publication.
+     </para>
+    </note>
+
+   </sect3>
+
+   <sect3 id="logical-replication-rf-combining">
+    <title>Combining multiple row filters for the same table</title>
+
+    <para>
+     If the subscription has several publications in which the same table has
+     been published with different row filters (for the same publish
+     operation), those expressions get OR'ed together, so that rows satisfying
+     <emphasis>any</emphasis> of the expressions will be replicated.
+   </para>
+
+   <para>
+    This means all the other filters (for the same table) become redundant if:
+    <itemizedlist>
+
+     <listitem>
+      <para>
+       one of the publications has no filter at all.
+      </para>
+     </listitem>
+
+     <listitem>
+      <para>
+       one of the publications was created using <literal>FOR ALL TABLES</literal>.
+      </para>
+     </listitem>
+
+     <listitem>
+      <para>
+       one of the publications was created using
+       <literal>FOR ALL TABLES IN SCHEMA</literal> and the table belongs to
+       that schema.
+      </para>
+     </listitem>
+
+    </itemizedlist>
+   </para>
+
+   </sect3>
+
+   <sect3 id="logical-replication-rf-psql">
+    <title>PSQL commands to display Row Filters</title>
+
+    <para>
+     PSQL commands <command>\dRp+</command> and <command>\d</command> will
+     display row filter information.
+    </para>
+
+   </sect3>
+
+   <sect3 id="logical-replication-rf-examples">
+    <title>Examples</title>
+
+    <para>
+     Create some tables to be used in the following examples.
+<programlisting>
+test_pub=# CREATE TABLE t1(a int, b int, c text);
+CREATE TABLE
+test_pub=# CREATE TABLE t2(d int, e int, f int);
+CREATE TABLE
+</programlisting>
+    </para>
+
+    <para>
+     Create some publications.
+    </para>
+
+    <para>
+     Notice that table <literal>t1</literal> is a member of multiple
+     publications.
+<programlisting>
+test_pub=# CREATE PUBLICATION p1 FOR TABLE t1 WHERE (a > 5 AND c IS NULL);
+CREATE PUBLICATION
+test_pub=# CREATE PUBLICATION p2 FOR TABLE t1, t2 WHERE (e = 99);
+CREATE PUBLICATION
+</programlisting>
+    </para>
+
+    <para>
+     The PSQL command <command>\dRp+</command> shows what row filters are
+     defined for the publications.
+<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" WHERE ((a > 5) AND (c IS NULL))
+
+                               Publication p2
+  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root 
+----------+------------+---------+---------+---------+-----------+----------
+ postgres | f          | t       | t       | t       | t         | f
+Tables:
+    "public.t1"
+    "public.t2" WHERE (e = 99)
+</programlisting>
+    </para>
+
+    <para>
+     The PSQL command <command>\d</command> shows what publications the table is
+     a member of, as well as that table's row filter in those publications.
+    </para>
+
+    <para>
+     Notice that table <literal>t1</literal> is a member of two publications, but
+     only has a <literal>WHERE</literal> clause for <literal>p1</literal>.
+<programlisting>
+test_pub=# \d t1
+                 Table "public.t1"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           |          | 
+ b      | integer |           |          | 
+ c      | text    |           |          | 
+Publications:
+    "p1" WHERE ((a > 5) AND (c IS NULL))
+    "p2"
+
+test_pub=# \d t2
+                 Table "public.t2"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ d      | integer |           |          | 
+ e      | integer |           |          | 
+ f      | integer |           |          | 
+Publications:
+    "p2" WHERE (e = 99)
+</programlisting>
+    </para>
+
+   </sect3>
+
+  </sect2>
+ </sect1>
+
  <sect1 id="logical-replication-conflicts">
   <title>Conflicts</title>
 
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index 4979b9b646..cb16e97b22 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -247,6 +247,8 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
    <literal>publish_via_partition_root</literal> determines if it uses the
    partition's row filter (if the parameter is false, the default) or the root
    partitioned table's row filter.
+   See <xref linkend="logical-replication-row-filtering"/> for more details about 
+   row filters.
   </para>
 
   <para>
-- 
2.35.1

From 48359d69107da581d004da37230f949cb8796eb8 Mon Sep 17 00:00:00 2001
From: Peter Smith <peter.b.smith@fujitsu.com>
Date: Fri, 4 Mar 2022 14:32:33 +1100
Subject: [PATCH v3] Update the documentation for logical replication.

This patch introduces a new documentation page for describing
the "Row Filtering" feature.

Author: Peter Smith
Reviewed By: Greg Nancarrow, Aleksander Alekseev, Amit Kapila, Ajin Cherian
Discussion: https://www.postgresql.org/message-id/CAHut%2BPtnsBr59%3D_NvxXp_%3DS-em0WxyuDOQmSTuHGb4sVhkHffg%40mail.gmail.com
---
 doc/src/sgml/logical-replication.sgml    | 521 +++++++++++++++++++++++++++++++
 doc/src/sgml/ref/create_publication.sgml |   2 +
 2 files changed, 523 insertions(+)

diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index 96b4886..657b460 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -118,6 +118,8 @@
    any combination of <command>INSERT</command>, <command>UPDATE</command>,
    <command>DELETE</command>, and <command>TRUNCATE</command>, similar to how triggers are fired by
    particular event types.  By default, all operation types are replicated.
+   (Row filters have no effect for <command>TRUNCATE</command>. See 
+   <xref linkend="logical-replication-row-filtering"/>).
   </para>
 
   <para>
@@ -317,6 +319,525 @@
   </sect2>
  </sect1>
 
+ <sect1 id="logical-replication-row-filtering">
+  <title>Row Filters</title>
+
+  <para>
+   By default, all data from all published tables will be replicated to the
+   appropriate subscribers.
+  </para>
+
+  <para>
+   The default data replication can be reduced by using <firstterm>row filters</firstterm>.
+   A user might choose to use row filters for behavioral or for performance
+   reasons.
+  </para>
+
+  <para>
+   With row filtering, the decision whether to replicate row data depends on
+   the row data values. Rows that don't satisfy an optional <literal>WHERE</literal>
+   clause will be filtered out. This allows a database or set of tables to be
+   partially replicated.
+  </para>
+
+  <para>
+   Use the <literal>PUBLICATION ... FOR TABLE </literal>
+   <link linkend="sql-createpublication"><literal>
+   <replaceable class="parameter">table_name</replaceable> WHERE (
+   <replaceable class="parameter">expression</replaceable>)</literal></link>
+   to specify which rows will be replicated.
+  </para>
+
+  <para>
+   The row filtering is defined per table. A new row filter can be added simply
+   by specifying a <literal>WHERE</literal> clause after the table name. The
+   <literal>WHERE</literal> clause must be enclosed by parentheses.
+  </para>
+
+  <sect2 id="logical-replication-rf-rules">
+   <title>Row filter Rules</title>
+
+   <para>
+    If the row filter evaluates to <literal>false</literal> or
+    <literal>NULL</literal> then the row is not replicated.
+   </para>
+
+   <para>
+    Row filters are applied <emphasis>before</emphasis> publishing the changes.
+   </para>
+
+   <para>
+    The <literal>WHERE</literal> clause expression is evaluated with the same
+    role used for the replication connection.
+   </para>
+
+   <para>
+    Row filters have no effect for <command>TRUNCATE</command> commands.
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-rf-transformations">
+   <title>UPDATE transformations</title>
+
+   <para>
+    Whenever an <command>UPDATE</command> is processed, the row filter
+    expression is evaluated for both the old and new row (i.e. before
+    and after the data is updated).
+   </para>
+
+   <para>
+    If both evaluations are <literal>true</literal>, it replicates the
+    <command>UPDATE</command>.
+   </para>
+
+   <para>
+    If both evaluations are <literal>false</literal>, it doesn't replicate
+    anything.
+   </para>
+
+   <para>
+    If only one of the old/new rows matches the row filter expression, the
+    <command>UPDATE</command> is transformed to <command>INSERT</command> or
+    <command>DELETE</command>, to avoid any data inconsistency, as follows:
+<synopsis>
+Case 1: old-row (no match)    new-row (no match)   -->   (drop change)
+Case 2: old-row (no match)    new row (match)      -->   INSERT
+Case 3: old-row (match)       new-row (no match)   -->   DELETE
+Case 4: old-row (match)       new row (match)      -->   UPDATE
+</synopsis>
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-rf-partitions">
+   <title>Partition tables</title>
+
+   <para>
+    If the publication contains a partitioned table, the publication parameter
+    <literal>publish_via_partition_root</literal> determines which row filter
+    is used.
+    <itemizedlist>
+
+     <listitem>
+      <para>
+       If <literal>publish_via_partition_root</literal> is <literal>false</literal>
+       (default), each <emphasis>partition's</emphasis> row filter is used.
+      </para>
+     </listitem>
+
+     <listitem>
+      <para>
+       If <literal>publish_via_partition_root</literal> is <literal>true</literal>,
+       the <emphasis>root partitioned table's</emphasis> row filter is used.
+      </para>
+     </listitem>
+
+    </itemizedlist>
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-rf-restrictions">
+   <title>WHERE clause Expression Restrictions</title>
+
+   <para>
+    The row filter <literal>WHERE</literal> clause for a table added to a
+    publication that publishes <command>UPDATE</command> and/or
+    <command>DELETE</command> operations must contain only columns that are
+    covered by the <quote>replica identity</quote>. The row filter
+    <literal>WHERE</literal> clause for a table added to a publication that
+    publishes <command>INSERT</command> can use any column.
+   </para>
+
+   <para>
+    The <literal>WHERE</literal> clause allows only simple expressions that
+    don't have user-defined functions, operators, non-immutable built-in
+    functions, or references to system columns.
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-rf-tablesync">
+   <title>Subscriber initial table synchronization</title>
+
+   <para>
+    If you choose to do the initial table synchronization, only data that
+    satisfies the row filters is copied to the subscriber.
+   </para>
+
+   <para>
+    If the subscription has several publications in which a table has been
+    published with different <literal>WHERE</literal> clauses, rows that satisfy
+    <emphasis>any</emphasis> of the expressions will be copied. (see
+    <xref linkend="logical-replication-rf-combining"/>).
+   </para>
+
+   <note>
+    <para>
+     Publication operations of filters are ignored during initial table
+     synchronization.
+    </para>
+   </note>
+
+   <note>
+    <para>
+     If a subscriber is a pre-15 version, the initial table synchronization
+     won't use row filters even if they are defined in the publication.
+    </para>
+   </note>
+
+  </sect2>
+
+  <sect2 id="logical-replication-rf-combining">
+   <title>Combining multiple row filters for the same table</title>
+
+   <para>
+    If the subscription has several publications in which the same table has
+    been published with different row filters (for the same publish
+    operation), those expressions get OR'ed together, so that rows satisfying
+    <emphasis>any</emphasis> of the expressions will be replicated.
+   </para>
+
+   <para>
+    This means all the other filters (for the same table) become redundant if:
+    <itemizedlist>
+
+     <listitem>
+      <para>
+       one of the publications has no filter at all.
+      </para>
+     </listitem>
+
+     <listitem>
+      <para>
+       one of the publications was created using <literal>FOR ALL TABLES</literal>.
+      </para>
+     </listitem>
+
+     <listitem>
+      <para>
+       one of the publications was created using
+       <literal>FOR ALL TABLES IN SCHEMA</literal> and the table belongs to
+       that schema.
+      </para>
+     </listitem>
+
+    </itemizedlist>
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-rf-psql">
+   <title>PSQL commands to display Row Filters</title>
+
+   <para>
+    PSQL commands <command>\dRp+</command> and <command>\d</command> will
+    display row filter information.
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-rf-examples">
+   <title>Examples</title>
+
+   <para>
+    Create some tables to be used in the following examples.
+<programlisting>
+test_pub=# CREATE TABLE t1(a int, b int, c text, primary key(a,c));
+CREATE TABLE
+test_pub=# CREATE TABLE t2(d int primary key, e int, f int);
+CREATE TABLE
+test_pub=# CREATE TABLE t3(g int primary key, h int, i int);
+CREATE TABLE
+</programlisting>
+   </para>
+
+   <para>
+    Create some publications.
+   </para>
+
+   <para>
+    Notice that publication <literal>p2</literal> has tables with and without
+    row filters.
+   </para>
+
+   <para>
+    Notice that publication <literal>p3</literal> has row filters on multiple
+    tables.
+<programlisting>
+test_pub=# CREATE PUBLICATION p1 FOR TABLE t1 WHERE (a > 5 AND c = 'NSW');
+CREATE PUBLICATION
+test_pub=# CREATE PUBLICATION p2 FOR TABLE t1, t2 WHERE (e = 99);
+CREATE PUBLICATION
+test_pub=# CREATE PUBLICATION p3 FOR TABLE t2 WHERE (d = 10), t3 WHERE (g = 10);
+CREATE PUBLICATION
+</programlisting>
+   </para>
+
+   <para>
+    The PSQL command <command>\dRp+</command> shows what row filters are
+    defined for the publications.
+<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" WHERE ((a > 5) AND (c = 'NSW'::text))
+
+                               Publication p2
+  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root 
+----------+------------+---------+---------+---------+-----------+----------
+ postgres | f          | t       | t       | t       | t         | f
+Tables:
+    "public.t1"
+    "public.t2" WHERE (e = 99)
+
+                               Publication p3
+  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root 
+----------+------------+---------+---------+---------+-----------+----------
+ postgres | f          | t       | t       | t       | t         | f
+Tables:
+    "public.t2" WHERE (d = 10)
+    "public.t3" WHERE (g = 10)
+</programlisting>
+   </para>
+
+   <para>
+    The PSQL command <command>\d</command> shows what publications the table is
+    a member of, as well as that table's row filter in those publications.
+   </para>
+
+   <para>
+    Notice that table <literal>t1</literal> is a member of two publications, but
+    has a <literal>WHERE</literal> clause only for <literal>p1</literal>.
+   </para>
+
+   <para>
+    Notice that table <literal>t2</literal> is a member of two publications, and
+    has a different <literal>WHERE</literal> clause for each of them.
+<programlisting>
+test_pub=# \d t1
+                 Table "public.t1"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           | not null | 
+ b      | integer |           |          | 
+ c      | text    |           | not null | 
+Indexes:
+    "t1_pkey" PRIMARY KEY, btree (a, c)
+Publications:
+    "p1" WHERE ((a > 5) AND (c = 'NSW'::text))
+    "p2"
+</programlisting>
+<programlisting>
+test_pub=# \d t2
+                 Table "public.t2"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ d      | integer |           | not null | 
+ e      | integer |           |          | 
+ f      | integer |           |          | 
+Indexes:
+    "t2_pkey" PRIMARY KEY, btree (d)
+Publications:
+    "p2" WHERE (e = 99)
+    "p3" WHERE (d = 10)
+</programlisting>
+<programlisting>
+test_pub=# \d t3
+                 Table "public.t3"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ g      | integer |           | not null | 
+ h      | integer |           |          | 
+ i      | integer |           |          | 
+Indexes:
+    "t3_pkey" PRIMARY KEY, btree (g)
+Publications:
+    "p3" WHERE (g = 10)
+</programlisting>
+   </para>
+
+   <para>
+    On the subscriber node, create a table <literal>t1</literal> and a
+    subscription <literal>s1</literal> to publication <literal>p1</literal>. 
+<programlisting>
+test_sub=# CREATE TABLE t1(a int, b int, c text, primary key(a,c));
+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>
+    <literal>INSERT</literal> some data.
+   </para>
+
+   <para>
+    Notice that only the rows satisfying the <literal>t1 WHERE</literal>
+    clause of publication <literal>p1</literal> are replicated.
+<programlisting>
+test_pub=# INSERT INTO t1 VALUES (2, 102, 'NSW');
+INSERT 0 1
+test_pub=# INSERT INTO t1 VALUES (3, 103, 'QLD');
+INSERT 0 1
+test_pub=# INSERT INTO t1 VALUES (4, 104, 'VIC');
+INSERT 0 1
+test_pub=# INSERT INTO t1 VALUES (5, 105, 'ACT');
+INSERT 0 1
+test_pub=# INSERT INTO t1 VALUES (6, 106, 'NSW');
+INSERT 0 1
+test_pub=# INSERT INTO t1 VALUES (7, 107, 'NT');
+INSERT 0 1
+test_pub=# INSERT INTO t1 VALUES (8, 108, 'QLD');
+INSERT 0 1
+test_pub=# INSERT INTO t1 VALUES (9, 109, 'NSW');
+INSERT 0 1
+</programlisting>
+<programlisting>
+test_pub=# SELECT * FROM t1; 
+ a |  b  |  c
+---+-----+-----
+ 2 | 102 | NSW
+ 3 | 103 | QLD
+ 4 | 104 | VIC
+ 5 | 105 | ACT
+ 6 | 106 | NSW
+ 7 | 107 | NT
+ 8 | 108 | QLD
+ 9 | 109 | NSW
+(8 rows)
+</programlisting>
+<programlisting>
+test_sub=# SELECT * FROM t1;
+ a |  b  |  c  
+---+-----+-----
+ 6 | 106 | NSW
+ 9 | 109 | NSW
+(2 rows)
+</programlisting>
+   </para>
+
+   <para>
+    <literal>UPDATE</literal> some data, where the old and new values both
+    satisfy the <literal>t1 WHERE</literal> clause of publication
+    <literal>p1</literal>.
+   </para>
+
+   <para>
+    Notice this replicates as a normal <literal>UPDATE</literal>.
+<programlisting>
+test_pub=# UPDATE t1 SET b = 999 WHERE a = 6;
+UPDATE 1
+</programlisting>
+<programlisting>
+test_pub=# SELECT * FROM t1;
+ a |  b  |  c  
+---+-----+-----
+ 2 | 102 | NSW
+ 3 | 103 | QLD
+ 4 | 104 | VIC
+ 5 | 105 | ACT
+ 7 | 107 | NT
+ 8 | 108 | QLD
+ 9 | 109 | NSW
+ 6 | 999 | NSW
+(8 rows)
+</programlisting>
+<programlisting>
+test_sub=# SELECT * FROM t1;
+ a |  b  |  c  
+---+-----+-----
+ 9 | 109 | NSW
+ 6 | 999 | NSW
+(2 rows)
+</programlisting>
+   </para>
+
+   <para>
+    <literal>UPDATE</literal> some data, where the old values did not satisfy
+    the <literal>t1 WHERE</literal> clause of publication <literal>p1</literal>,
+    but the new values do satisfy it.
+   </para>
+
+   <para>
+    Notice the <literal>UPDATE</literal> is transformed and replicates as an
+    <literal>INSERT</literal>.
+<programlisting>
+test_pub=# UPDATE t1 SET a = 999 WHERE a = 2;
+UPDATE 1
+</programlisting>
+<programlisting>
+test_pub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   3 | 103 | QLD
+   4 | 104 | VIC
+   5 | 105 | ACT
+   7 | 107 | NT
+   8 | 108 | QLD
+   9 | 109 | NSW
+   6 | 999 | NSW
+ 999 | 102 | NSW
+(8 rows)
+</programlisting>
+<programlisting>
+test_sub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   9 | 109 | NSW
+   6 | 999 | NSW
+ 999 | 102 | NSW
+(3 rows)
+</programlisting>
+   </para>
+
+   <para>
+    <literal>UPDATE</literal> some data, where the old values satisfied
+    the <literal>t1 WHERE</literal> clause of publication <literal>p1</literal>,
+    but the new values do not satisfy it.
+   </para>
+
+   <para>
+    Notice the <literal>UPDATE</literal> is transformed and replicates as a
+    <literal>DELETE</literal>.
+<programlisting>
+test_pub=# UPDATE t1 SET c = 'VIC' WHERE a = 9;
+UPDATE 1
+</programlisting>
+<programlisting>
+test_pub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   3 | 103 | QLD
+   4 | 104 | VIC
+   5 | 105 | ACT
+   7 | 107 | NT
+   8 | 108 | QLD
+   6 | 999 | NSW
+ 999 | 102 | NSW
+   9 | 109 | VIC
+(8 rows)
+</programlisting>
+<programlisting>
+test_sub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   6 | 999 | NSW
+ 999 | 102 | NSW
+(2 rows)
+</programlisting>
+   </para>
+
+  </sect2>
+
+ </sect1>
+
  <sect1 id="logical-replication-conflicts">
   <title>Conflicts</title>
 
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index 4979b9b..cb16e97 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -247,6 +247,8 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
    <literal>publish_via_partition_root</literal> determines if it uses the
    partition's row filter (if the parameter is false, the default) or the root
    partitioned table's row filter.
+   See <xref linkend="logical-replication-row-filtering"/> for more details about 
+   row filters.
   </para>
 
   <para>
-- 
1.8.3.1

From aea0c822489fbdeba14141e215f76e3777521bb2 Mon Sep 17 00:00:00 2001
From: Euler Taveira <euler.taveira@enterprisedb.com>
Date: Thu, 10 Mar 2022 17:38:24 -0300
Subject: [PATCH v4] doc: new section for row filter.

---
 doc/src/sgml/logical-replication.sgml    | 444 +++++++++++++++++++++++
 doc/src/sgml/ref/create_publication.sgml |   2 +
 2 files changed, 446 insertions(+)

diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index 82326c3901..38ac77b6e1 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -118,6 +118,8 @@
    any combination of <command>INSERT</command>, <command>UPDATE</command>,
    <command>DELETE</command>, and <command>TRUNCATE</command>, similar to how triggers are fired by
    particular event types.  By default, all operation types are replicated.
+   (Row filters have no effect for <command>TRUNCATE</command>. See 
+   <xref linkend="logical-replication-row-filter"/>).
   </para>
 
   <para>
@@ -317,6 +319,448 @@
   </sect2>
  </sect1>
 
+ <sect1 id="logical-replication-row-filter">
+  <title>Row Filter</title>
+
+  <para>
+   The published tables replicate all data to the subscribers. The replicated
+   data can be selected using a <firstterm>row filter</firstterm>. The user
+   might choose to use row filters for behavioral, security or performance
+   reasons.
+  </para>
+
+  <para>
+   If a published table sets a row filter, a row is replicated if its data
+   satisfies the row filter expression. It means if row filters are used in a
+   set of tables, data will be partially replicated.
+  </para>
+
+  <para>
+   The row filter is defined per table. Use a <literal>WHERE</literal> clause
+   after the table name for each published table that requires data to be
+   filtered out. The <literal>WHERE</literal> clause must be enclosed by
+   parentheses. See <xref linkend="sql-createpublication"/> for details.
+  </para>
+
+  <sect2 id="logical-replication-row-filter-rules">
+   <title>Row Filter Rules</title>
+
+   <para>
+    Row filter is applied <emphasis>before</emphasis> publishing the changes.
+   </para>
+
+   <para>
+    If the row filter evaluates to <literal>false</literal> or
+    <literal>NULL</literal> then the row is not replicated.
+   </para>
+
+   <para>
+    The <literal>WHERE</literal> clause expression is evaluated with the same
+    role used for the replication connection. It means the role specified in the
+    <literal>CONNECTION</literal> clause of the <xref linkend="sql-createsubscription"/>.
+   </para>
+
+   <para>
+    Row filter has no effect for <command>TRUNCATE</command> command.
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-restrictions">
+   <title>Expression Restrictions</title>
+
+   <para>
+    The <literal>WHERE</literal> clause allows only simple expressions. It
+    cannot contain user-defined functions, operators, types, and collations,
+    system column references and non-immutable built-in functions.
+   </para>
+
+   <para>
+    If a publication publishes <literal>update</literal> or
+    <literal>delete</literal> operations, the <literal>WHERE</literal> clause
+    must contain only columns that are part of the replica identity (see
+    <xref linkend="sql-altertable-replica-identity"/>). If it publishes only
+    <literal>insert</literal> operation, the expression can use any column.
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-transformations">
+   <title>UPDATE Transformations</title>
+
+   <para>
+    Whenever an <command>UPDATE</command> is processed, the row filter
+    expression is evaluated for the old and new row. If both evaluations are
+    <literal>true</literal>, it replicates the <command>UPDATE</command> change.
+    If both evaluations are <literal>false</literal>, it doesn't replicate the
+    change. If only one of the rows matches the row filter expression, a
+    transformation is required. <command>UPDATE</command> should be transformed
+    into a <command>DELETE</command> or <command>INSERT</command> to avoid data
+    inconsistency. The row on the subscriber should reflect what is defined by
+    the row filter expression on the publisher.
+   </para>
+
+   <para>
+    If the old row satisfies the row filter expression (it was sent to the
+    subscriber) but the new row doesn't, after the <command>UPDATE</command>,
+    from a data consistency perspective, the old row should be removed from the
+    subscriber. Hence, <command>UPDATE</command> should be transformed into a
+    <command>DELETE</command>.
+   </para>
+
+   <para>
+    On the other hand, if the old row doesn't satisfy the row filter expression
+    (it wasn't sent to the subscriber) but the new row does, after the
+    <command>UPDATE</command>, from a data consistency perspective, that new row
+    should be inserted into the subscriber. Hence, <command>UPDATE</command>
+    should be transformed into an <command>INSERT</command>.
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-partitioned-table">
+   <title>Partitioned Table</title>
+
+   <para>
+    If the publication contains a partitioned table, the parameter
+    <literal>publish_via_partition_root</literal> determines which row filter
+    expression is used. If the parameter
+    <literal>publish_via_partition_root</literal> is true, the row filter
+    expression associated with the partitioned table is used. Otherwise, the row
+    filter expression associated with the individual partition is used.
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-initial-data-sync">
+   <title>Initial Data Synchronization</title>
+
+   <para>
+    If the subscription requires copying pre-existing data in the publications
+    and a publication contains <literal>WHERE</literal> clauses, only data that
+    satisfies the row filter expressions is copied to the subscriber.
+   </para>
+
+   <para>
+    If the subscription has several publications in which a table has been
+    published with different <literal>WHERE</literal> clauses, rows that satisfy
+    any of the expressions will be copied (see
+    <xref linkend="logical-replication-row-filter-combining"/> for details).
+   </para>
+
+   <para>
+    If the subscriber is in a release prior to 15, copy pre-existing data
+    doesn't use row filter expressions even if it is defined in the publication.
+    This is because old releases can only copy the entire table data.
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-combining">
+   <title>Combining Multiple Row Filters</title>
+
+   <para>
+    If the subscription has several publications in which the same table has
+    been published with different row filters (for the same
+    <literal>publish</literal> operation), rows that satisfy any of the row
+    filter expressions is replicated. It means all other row filters for the
+    same table become irrelevant if:
+
+    <itemizedlist>
+     <listitem>
+      <para>
+       one of the publications has no row filter;
+      </para>
+     </listitem>
+     <listitem>
+      <para>
+       one of the publications was created using
+       <literal>FOR ALL TABLES</literal> clause. This clause does not allow row
+       filters;
+      </para>
+     </listitem>
+     <listitem>
+      <para>
+       one of the publications was created using
+       <literal>FOR ALL TABLES IN SCHEMA</literal> clause and the table belongs
+       to the referred schema. This clause does not allow row filters.
+      </para>
+     </listitem>
+    </itemizedlist>
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-examples">
+   <title>Examples</title>
+
+   <para>
+    Create some tables to be used in the following examples.
+<programlisting>
+testpub=# CREATE TABLE t1(a int, b int, c text, primary key(a,c));
+CREATE TABLE
+testpub=# CREATE TABLE t2(d int, e int, f int, primary key(d));
+CREATE TABLE
+testpub=# CREATE TABLE t3(g int, h int, i int, primary key(g));
+CREATE TABLE
+</programlisting>
+   </para>
+
+   <para>
+    Create some publications.
+<programlisting>
+testpub=# CREATE PUBLICATION p1 FOR TABLE t1 WHERE (a > 5 AND c = 'NSW');
+CREATE PUBLICATION
+testpub=# CREATE PUBLICATION p2 FOR TABLE t1, t2 WHERE (e = 99);
+CREATE PUBLICATION
+testpub=# CREATE PUBLICATION p3 FOR TABLE t2 WHERE (d = 10), t3 WHERE (g = 10);
+CREATE PUBLICATION
+</programlisting>
+   </para>
+
+   <para>
+    The publication <literal>p1</literal> contains only one table
+    (<literal>t1</literal>) that has a row filter expression for it.
+   </para>
+
+   <para>
+    The publication <literal>p2</literal> contains 2 tables. The table
+    <literal>t1</literal> has no row filter and the table <literal>t2</literal>
+    has a row filter.
+   </para>
+
+   <para>
+    The publication <literal>p3</literal> contains 2 tables and each one has a
+    defined row filter.
+   </para>
+
+   <para>
+    The <command>psql</command> shows the row filter expressions (if defined)
+    for each table.
+
+<programlisting>
+testpub=# \dRp+
+                               Publication p1
+  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root 
+----------+------------+---------+---------+---------+-----------+----------
+ postgres | f          | t       | t       | t       | t         | f
+Tables:
+    "public.t1" WHERE ((a > 5) AND (c = 'NSW'::text))
+
+                               Publication p2
+  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root 
+----------+------------+---------+---------+---------+-----------+----------
+ postgres | f          | t       | t       | t       | t         | f
+Tables:
+    "public.t1"
+    "public.t2" WHERE (e = 99)
+
+                               Publication p3
+  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root 
+----------+------------+---------+---------+---------+-----------+----------
+ postgres | f          | t       | t       | t       | t         | f
+Tables:
+    "public.t2" WHERE (d = 10)
+    "public.t3" WHERE (g = 10)
+
+testpub=# \d t1
+                 Table "public.t1"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           | not null | 
+ b      | integer |           |          | 
+ c      | text    |           | not null | 
+Indexes:
+    "t1_pkey" PRIMARY KEY, btree (a, c)
+Publications:
+    "p1" WHERE ((a > 5) AND (c = 'NSW'::text))
+    "p2"
+
+testpub=# \d t2
+                 Table "public.t2"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ d      | integer |           | not null | 
+ e      | integer |           |          | 
+ f      | integer |           |          | 
+Indexes:
+    "t2_pkey" PRIMARY KEY, btree (d)
+Publications:
+    "p2" WHERE (e = 99)
+    "p3" WHERE (d = 10)
+</programlisting>
+   </para>
+
+   <para>
+    On the subscriber, create the table <literal>t1</literal> with the same
+    definition as the one on the publisher and also create the subscription
+    <literal>s1</literal> that subscribes to the publication
+    <literal>p1</literal>.
+<programlisting>
+testsub=# CREATE TABLE t1(a int, b int, c text, primary key(a,c));
+CREATE TABLE
+testsub=# CREATE SUBSCRIPTION s1
+testsub-# CONNECTION 'host=10.1.2.3 port=5432 user=repuser dbname=testpub'
+testsub-# PUBLICATION p1;
+CREATE SUBSCRIPTION
+</programlisting>
+   </para>
+
+   <para>
+    Insert some rows.
+<programlisting>
+testpub=# INSERT INTO t1 VALUES (2, 102, 'NSW');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (3, 103, 'QLD');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (4, 104, 'VIC');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (5, 105, 'ACT');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (6, 106, 'NSW');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (7, 107, 'NT');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (8, 108, 'QLD');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (9, 109, 'NSW');
+INSERT 0 1
+</programlisting>
+<programlisting>
+testpub=# SELECT * FROM t1; 
+ a |  b  |  c
+---+-----+-----
+ 2 | 102 | NSW
+ 3 | 103 | QLD
+ 4 | 104 | VIC
+ 5 | 105 | ACT
+ 6 | 106 | NSW
+ 7 | 107 | NT
+ 8 | 108 | QLD
+ 9 | 109 | NSW
+(8 rows)
+</programlisting>
+   </para>
+
+   <para>
+    Notice that only the rows satisfying the <literal>t1</literal>
+    <literal>WHERE</literal> clause of the publication
+    <literal>p1</literal> are replicated.
+<programlisting>
+testsub=# SELECT * FROM t1;
+ a |  b  |  c  
+---+-----+-----
+ 6 | 106 | NSW
+ 9 | 109 | NSW
+(2 rows)
+</programlisting>
+   </para>
+
+   <para>
+    Update a row whose both (old and new) rows satisfy the <literal>t1</literal>
+    <literal>WHERE</literal> clause of the publication <literal>p1</literal>. It
+    replicates the <command>UPDATE</command> as expected.
+<programlisting>
+testpub=# UPDATE t1 SET b = 999 WHERE a = 6;
+UPDATE 1
+</programlisting>
+<programlisting>
+testpub=# SELECT * FROM t1;
+ a |  b  |  c  
+---+-----+-----
+ 2 | 102 | NSW
+ 3 | 103 | QLD
+ 4 | 104 | VIC
+ 5 | 105 | ACT
+ 7 | 107 | NT
+ 8 | 108 | QLD
+ 9 | 109 | NSW
+ 6 | 999 | NSW
+(8 rows)
+</programlisting>
+<programlisting>
+testsub=# SELECT * FROM t1;
+ a |  b  |  c  
+---+-----+-----
+ 9 | 109 | NSW
+ 6 | 999 | NSW
+(2 rows)
+</programlisting>
+   </para>
+
+   <para>
+    Update a row whose the old row doesn't satisfy the <literal>t1</literal>
+    <literal>WHERE</literal> clause of the publication <literal>p1</literal>
+    but the new row satisfies it. The <literal>UPDATE</literal> is transformed
+    into an <command>INSERT</command> and the change is replicated. See the new
+    row on the subscriber.
+<programlisting>
+testpub=# UPDATE t1 SET a = 555 WHERE a = 2;
+UPDATE 1
+</programlisting>
+<programlisting>
+testpub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   3 | 103 | QLD
+   4 | 104 | VIC
+   5 | 105 | ACT
+   7 | 107 | NT
+   8 | 108 | QLD
+   9 | 109 | NSW
+   6 | 999 | NSW
+ 555 | 102 | NSW
+(8 rows)
+</programlisting>
+<programlisting>
+testsub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   9 | 109 | NSW
+   6 | 999 | NSW
+ 555 | 102 | NSW
+(3 rows)
+</programlisting>
+   </para>
+
+   <para>
+    Update a row whose the old row satisfies the <literal>t1</literal>
+    <literal>WHERE</literal> clause of the publication <literal>p1</literal>
+    but the new row doesn't satisfy it. The <command>UPDATE</command> is
+    transformed into a <command>DELETE</command> and the change is replicated.
+    See that the row is removed from the subscriber.
+<programlisting>
+testpub=# UPDATE t1 SET c = 'VIC' WHERE a = 9;
+UPDATE 1
+</programlisting>
+<programlisting>
+testpub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   3 | 103 | QLD
+   4 | 104 | VIC
+   5 | 105 | ACT
+   7 | 107 | NT
+   8 | 108 | QLD
+   6 | 999 | NSW
+ 999 | 102 | NSW
+   9 | 109 | VIC
+(8 rows)
+</programlisting>
+<programlisting>
+testsub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   6 | 999 | NSW
+ 999 | 102 | NSW
+(2 rows)
+</programlisting>
+   </para>
+
+  </sect2>
+
+ </sect1>
+
  <sect1 id="logical-replication-conflicts">
   <title>Conflicts</title>
 
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index 4979b9b646..3591d51af3 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -247,6 +247,8 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
    <literal>publish_via_partition_root</literal> determines if it uses the
    partition's row filter (if the parameter is false, the default) or the root
    partitioned table's row filter.
+   See <xref linkend="logical-replication-row-filter"/> for details about row
+   filters.
   </para>
 
   <para>
-- 
2.30.2

From 7ec8041194b458b71c2a4ca531de2f345d4d6f09 Mon Sep 17 00:00:00 2001
From: postgres <postgres@localhost.localdomain>
Date: Thu, 24 Mar 2022 16:38:29 +1100
Subject: [PATCH v5] This patch introduces a new documentation page for
 describing the "Row Filters" feature.

Author: Peter Smith, Euler Taveira
Reviewed By: Greg Nancarrow, Aleksander Alekseev, Amit Kapila, Ajin Cherian
Discussion: https://www.postgresql.org/message-id/CAHut%2BPtnsBr59%3D_NvxXp_%3DS-em0WxyuDOQmSTuHGb4sVhkHffg%40mail.gmail.com
---
 doc/src/sgml/logical-replication.sgml    | 521 +++++++++++++++++++++++++++++++
 doc/src/sgml/ref/create_publication.sgml |   2 +
 2 files changed, 523 insertions(+)

diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index 6431d47..504af09 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -118,6 +118,8 @@
    any combination of <command>INSERT</command>, <command>UPDATE</command>,
    <command>DELETE</command>, and <command>TRUNCATE</command>, similar to how triggers are fired by
    particular event types.  By default, all operation types are replicated.
+   (Row filters have no effect for <command>TRUNCATE</command>. See 
+   <xref linkend="logical-replication-row-filter"/>).
   </para>
 
   <para>
@@ -317,6 +319,525 @@
   </sect2>
  </sect1>
 
+ <sect1 id="logical-replication-row-filter">
+  <title>Row Filters</title>
+
+  <para>
+   By default, all data from all published tables will be replicated to the
+   appropriate subscribers.
+  </para>
+
+  <para>
+   The replicated data can be reduced by using a <firstterm>row filter</firstterm>.
+   A user might choose to use row filters for behavioral, security or performance
+   reasons.
+  </para>
+
+  <para>
+   If a published table sets a row filter, a row is replicated only if its data
+   satisfies the row filter expression. This allows a set of tables to be
+   partially replicated.
+  </para>
+
+  <para>
+   The row filter is defined per table. Use a <literal>WHERE</literal> clause
+   after the table name for each published table that requires data to be
+   filtered out. The <literal>WHERE</literal> clause must be enclosed by
+   parentheses. See <xref linkend="sql-createpublication"/> for details.
+  </para>
+
+  <sect2 id="logical-replication-row-filter-rules">
+   <title>Row Filter Rules</title>
+
+   <para>
+    Row filters are applied <emphasis>before</emphasis> publishing the changes.
+   </para>
+
+   <para>
+    If the row filter evaluates to <literal>false</literal> or
+    <literal>NULL</literal> then the row is not replicated.
+   </para>
+
+   <para>
+    The <literal>WHERE</literal> clause expression is evaluated with the same
+    role used for the replication connection. i.e. the role specified in the
+    <literal>CONNECTION</literal> clause of the <xref linkend="sql-createsubscription"/>.
+   </para>
+
+   <para>
+    Row filters have no effect for <command>TRUNCATE</command> command.
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-restrictions">
+   <title>Expression Restrictions</title>
+
+   <para>
+    The <literal>WHERE</literal> clause allows only simple expressions. It
+    cannot contain user-defined functions, operators, types, and collations,
+    system column references or non-immutable built-in functions.
+   </para>
+
+   <para>
+    If a publication publishes <command>UPDATE</command> and/or
+    <command>DELETE</command> operations, the row filter <literal>WHERE</literal> clause
+    must contain only columns that are covered by the replica identity (see
+    <xref linkend="sql-altertable-replica-identity"/>). If a publication publishes only
+    <command>INSERT</command>, the row filter <literal>WHERE</literal> clause
+    can use any column.
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-transformations">
+   <title>UPDATE Transformations</title>
+
+   <para>
+    Whenever an <command>UPDATE</command> is processed, the row filter
+    expression is evaluated for both the old and new row (i.e. before
+    and after the data is updated).
+   </para>
+
+   <para>
+    If both evaluations are <literal>true</literal>, it replicates the
+    <command>UPDATE</command> change.
+   </para>
+
+   <para>
+    If both evaluations are <literal>false</literal>, it doesn't replicate
+    the change.
+   </para>
+
+   <para>
+    If only one of the old/new rows matches the row filter expression, the
+    <command>UPDATE</command> is transformed to <command>INSERT</command> or
+    <command>DELETE</command>, to avoid any data inconsistency. The row on the
+    subscriber should reflect what is defined by the row filter expression on
+    the publisher.
+
+   <itemizedlist>
+    <listitem>
+     <para>
+      If the old row satisfies the row filter expression (it was sent to the
+      subscriber) but the new row doesn't, then from a data consistency
+      perspective the old row should be removed from the subscriber.
+      So the <command>UPDATE</command> is transformed into a <command>DELETE</command>.
+     </para>
+    </listitem>
+    <listitem>
+     <para>
+      If the old row doesn't satisfy the row filter expression (it wasn't sent
+      to the subscriber) but the new row does, then from a data consistency
+      perspective the new row should be added to the subscriber.
+      So the <command>UPDATE</command> is transformed into an <command>INSERT</command>.
+     </para>
+    </listitem>
+   </itemizedlist>
+
+Summary:
+<synopsis>
+Case 1: old-row (no match)    new-row (no match)   -->   (drop change)
+Case 2: old-row (no match)    new row (match)      -->   INSERT
+Case 3: old-row (match)       new-row (no match)   -->   DELETE
+Case 4: old-row (match)       new row (match)      -->   UPDATE
+</synopsis>
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-partitioned-table">
+   <title>Partitioned Tables</title>
+
+   <para>
+    If the publication contains a partitioned table, the publication parameter
+    <literal>publish_via_partition_root</literal> determines which row filter
+    is used.
+    <itemizedlist>
+
+     <listitem>
+      <para>
+       If <literal>publish_via_partition_root</literal> is <literal>false</literal>
+       (default), each <emphasis>partition's</emphasis> row filter is used.
+      </para>
+     </listitem>
+
+     <listitem>
+      <para>
+       If <literal>publish_via_partition_root</literal> is <literal>true</literal>,
+       the <emphasis>root partitioned table's</emphasis> row filter is used.
+      </para>
+     </listitem>
+
+    </itemizedlist>
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-initial-data-sync">
+   <title>Initial Data Synchronization</title>
+
+   <para>
+    If the subscription requires copying pre-existing table data
+    and a publication contains <literal>WHERE</literal> clauses, only data that
+    satisfies the row filter expressions is copied to the subscriber.
+   </para>
+
+   <para>
+    If the subscription has several publications in which a table has been
+    published with different <literal>WHERE</literal> clauses, rows that satisfy
+    <emphasis>any</emphasis> of the expressions will be copied. (see
+    <xref linkend="logical-replication-row-filter-combining"/> for details).
+   </para>
+
+   <note>
+    <para>
+     Publication <literal>publish</literal> operations are ignored when copying pre-existing table data.
+    </para>
+   </note>
+
+   <note>
+    <para>
+     If the subscriber is in a release prior to 15, copy pre-existing data
+     doesn't use row filters even if they are defined in the publication.
+     This is because old releases can only copy the entire table data.
+    </para>
+   </note>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-combining">
+   <title>Combining Multiple Row Filters</title>
+
+   <para>
+    If the subscription has several publications in which the same table has
+    been published with different row filters (for the same <literal>publish</literal>
+    operation), those expressions get OR'ed together, so that rows satisfying
+    <emphasis>any</emphasis> of the expressions will be replicated.
+   </para>
+
+   <para>
+    This means all the other row filters for the same table become redundant if:
+    <itemizedlist>
+
+     <listitem>
+      <para>
+       one of the publications has no row filter.
+      </para>
+     </listitem>
+
+     <listitem>
+      <para>
+       one of the publications was created using <literal>FOR ALL TABLES</literal>.
+       This clause does not allow row filters.
+      </para>
+     </listitem>
+
+     <listitem>
+      <para>
+       one of the publications was created using
+       <literal>FOR ALL TABLES IN SCHEMA</literal> and the table belongs to
+       the referred schema. This clause does not allow row filters.
+      </para>
+     </listitem>
+
+    </itemizedlist>
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-examples">
+   <title>Examples</title>
+
+   <para>
+    Create some tables to be used in the following examples.
+<programlisting>
+testpub=# CREATE TABLE t1(a int, b int, c text, primary key(a,c));
+CREATE TABLE
+testpub=# CREATE TABLE t2(d int primary key, e int, f int);
+CREATE TABLE
+testpub=# CREATE TABLE t3(g int primary key, h int, i int);
+CREATE TABLE
+</programlisting>
+   </para>
+
+   <para>
+    Create some publications.
+   </para>
+   <para>
+    - notice publication <literal>p1</literal> has 1 table with a row filter.
+   </para>
+   <para>
+    - notice publication <literal>p2</literal> has 2 tables, one without a row
+    filter, and one with a row filter.
+   </para>
+   <para>
+    - notice publication <literal>p3</literal> has 2 tables, both with row filters.
+<programlisting>
+testpub=# CREATE PUBLICATION p1 FOR TABLE t1 WHERE (a > 5 AND c = 'NSW');
+CREATE PUBLICATION
+testpub=# CREATE PUBLICATION p2 FOR TABLE t1, t2 WHERE (e = 99);
+CREATE PUBLICATION
+testpub=# CREATE PUBLICATION p3 FOR TABLE t2 WHERE (d = 10), t3 WHERE (g = 10);
+CREATE PUBLICATION
+</programlisting>
+   </para>
+
+   <para>
+    The PSQL command <command>\dRp+</command> shows the row filter expressions
+    (if defined) for each table of the publications.
+<programlisting>
+testpub=# \dRp+
+                               Publication p1
+  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root 
+----------+------------+---------+---------+---------+-----------+----------
+ postgres | f          | t       | t       | t       | t         | f
+Tables:
+    "public.t1" WHERE ((a > 5) AND (c = 'NSW'::text))
+
+                               Publication p2
+  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root 
+----------+------------+---------+---------+---------+-----------+----------
+ postgres | f          | t       | t       | t       | t         | f
+Tables:
+    "public.t1"
+    "public.t2" WHERE (e = 99)
+
+                               Publication p3
+  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root 
+----------+------------+---------+---------+---------+-----------+----------
+ postgres | f          | t       | t       | t       | t         | f
+Tables:
+    "public.t2" WHERE (d = 10)
+    "public.t3" WHERE (g = 10)
+</programlisting>
+   </para>
+
+   <para>
+    The PSQL command <command>\d</command> shows what publications the table is
+    a member of, as well as that table's row filter expression (if defined) in
+    those publications.
+   </para>
+   <para>
+    - notice table <literal>t1</literal> is a member of 2 publications, but
+    has a row filter only in <literal>p1</literal>.
+   </para>
+   <para>
+    - notice table <literal>t2</literal> is a member of 2 publications, and
+    has a different row filter in each of them.
+<programlisting>
+testpub=# \d t1
+                 Table "public.t1"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           | not null | 
+ b      | integer |           |          | 
+ c      | text    |           | not null | 
+Indexes:
+    "t1_pkey" PRIMARY KEY, btree (a, c)
+Publications:
+    "p1" WHERE ((a > 5) AND (c = 'NSW'::text))
+    "p2"
+
+testpub=# \d t2
+                 Table "public.t2"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ d      | integer |           | not null | 
+ e      | integer |           |          | 
+ f      | integer |           |          | 
+Indexes:
+    "t2_pkey" PRIMARY KEY, btree (d)
+Publications:
+    "p2" WHERE (e = 99)
+    "p3" WHERE (d = 10)
+
+testpub=# \d t3
+                 Table "public.t3"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ g      | integer |           | not null | 
+ h      | integer |           |          | 
+ i      | integer |           |          | 
+Indexes:
+    "t3_pkey" PRIMARY KEY, btree (g)
+Publications:
+    "p3" WHERE (g = 10)
+</programlisting>
+   </para>
+
+   <para>
+    On the subscriber node, create a table <literal>t1</literal> with the same
+    definition as the one on the publisher, and also create the subscription
+    <literal>s1</literal> that subscribes to the publication <literal>p1</literal>.
+<programlisting>
+testsub=# CREATE TABLE t1(a int, b int, c text, primary key(a,c));
+CREATE TABLE
+testsub=# CREATE SUBSCRIPTION s1
+testsub-# CONNECTION 'host=localhost dbname=testpub application_name=s1'
+testsub-# PUBLICATION p1;
+CREATE SUBSCRIPTION
+</programlisting>
+   </para>
+
+   <para>
+    Insert some rows.
+   </para>
+   <para>
+    - notice that only the rows satisfying the <literal>t1 WHERE</literal>
+    clause of publication <literal>p1</literal> are replicated.
+<programlisting>
+testpub=# INSERT INTO t1 VALUES (2, 102, 'NSW');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (3, 103, 'QLD');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (4, 104, 'VIC');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (5, 105, 'ACT');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (6, 106, 'NSW');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (7, 107, 'NT');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (8, 108, 'QLD');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (9, 109, 'NSW');
+INSERT 0 1
+
+testpub=# SELECT * FROM t1; 
+ a |  b  |  c
+---+-----+-----
+ 2 | 102 | NSW
+ 3 | 103 | QLD
+ 4 | 104 | VIC
+ 5 | 105 | ACT
+ 6 | 106 | NSW
+ 7 | 107 | NT
+ 8 | 108 | QLD
+ 9 | 109 | NSW
+(8 rows)
+</programlisting>
+<programlisting>
+testsub=# SELECT * FROM t1;
+ a |  b  |  c  
+---+-----+-----
+ 6 | 106 | NSW
+ 9 | 109 | NSW
+(2 rows)
+</programlisting>
+   </para>
+
+   <para>
+    Update some data, where the old and new row values both
+    satisfy the <literal>t1 WHERE</literal> clause of publication
+    <literal>p1</literal>.
+   </para>
+
+   <para>
+    - notice this replicates as a normal <command>UPDATE</command>.
+<programlisting>
+testpub=# UPDATE t1 SET b = 999 WHERE a = 6;
+UPDATE 1
+
+testpub=# SELECT * FROM t1;
+ a |  b  |  c  
+---+-----+-----
+ 2 | 102 | NSW
+ 3 | 103 | QLD
+ 4 | 104 | VIC
+ 5 | 105 | ACT
+ 7 | 107 | NT
+ 8 | 108 | QLD
+ 9 | 109 | NSW
+ 6 | 999 | NSW
+(8 rows)
+</programlisting>
+<programlisting>
+testsub=# SELECT * FROM t1;
+ a |  b  |  c  
+---+-----+-----
+ 9 | 109 | NSW
+ 6 | 999 | NSW
+(2 rows)
+</programlisting>
+   </para>
+
+   <para>
+    Update some data, where the old row values did not satisfy
+    the <literal>t1 WHERE</literal> clause of publication <literal>p1</literal>,
+    but the new row values do satisfy it.
+   </para>
+
+   <para>
+    - notice the <command>UPDATE</command> is transformed into an <command>INSERT</command>
+    and the change is replicated.
+<programlisting>
+testpub=# UPDATE t1 SET a = 555 WHERE a = 2;
+UPDATE 1
+
+testpub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   3 | 103 | QLD
+   4 | 104 | VIC
+   5 | 105 | ACT
+   7 | 107 | NT
+   8 | 108 | QLD
+   9 | 109 | NSW
+   6 | 999 | NSW
+ 555 | 102 | NSW
+(8 rows)
+</programlisting>
+<programlisting>
+testsub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   9 | 109 | NSW
+   6 | 999 | NSW
+ 555 | 102 | NSW
+(3 rows)
+</programlisting>
+   </para>
+
+   <para>
+    Update some data, where the old row values satisfied
+    the <literal>t1 WHERE</literal> clause of publication <literal>p1</literal>,
+    but the new row values do not satisfy it.
+   </para>
+   <para>
+    - notice the <command>UPDATE</command> is transformed into a <command>DELETE</command>
+    and the change is replicated.
+<programlisting>
+testpub=# UPDATE t1 SET c = 'VIC' WHERE a = 9;
+UPDATE 1
+
+testpub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   3 | 103 | QLD
+   4 | 104 | VIC
+   5 | 105 | ACT
+   7 | 107 | NT
+   8 | 108 | QLD
+   6 | 999 | NSW
+ 555 | 102 | NSW
+   9 | 109 | VIC
+(8 rows)
+</programlisting>
+<programlisting>
+testsub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   6 | 999 | NSW
+ 555 | 102 | NSW
+(2 rows)
+</programlisting>
+   </para>
+
+  </sect2>
+
+ </sect1>
+
  <sect1 id="logical-replication-conflicts">
   <title>Conflicts</title>
 
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index 4979b9b..3591d51 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -247,6 +247,8 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
    <literal>publish_via_partition_root</literal> determines if it uses the
    partition's row filter (if the parameter is false, the default) or the root
    partitioned table's row filter.
+   See <xref linkend="logical-replication-row-filter"/> for details about row
+   filters.
   </para>
 
   <para>
-- 
1.8.3.1

From 47eab24dc65167ff4cd8c35fa86f596139f0afa3 Mon Sep 17 00:00:00 2001
From: Peter Smith <peter.b.smith@fujitsu.com>
Date: Fri, 8 Apr 2022 15:45:27 +1000
Subject: [PATCH v6] PG DOCS page for row filters.

This patch introduces a new documentation page for describing the "Row Filters" feature.

Author: Peter Smith, Euler Taveira
Reviewed By: Greg Nancarrow, Aleksander Alekseev, Amit Kapila, Ajin Cherian
Discussion: https://www.postgresql.org/message-id/CAHut%2BPtnsBr59%3D_NvxXp_%3DS-em0WxyuDOQmSTuHGb4sVhkHffg%40mail.gmail.com
---
 doc/src/sgml/logical-replication.sgml    | 551 +++++++++++++++++++++++++++++++
 doc/src/sgml/ref/create_publication.sgml |   2 +
 2 files changed, 553 insertions(+)

diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index 555fbd7..6e23be9 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -118,6 +118,8 @@
    any combination of <command>INSERT</command>, <command>UPDATE</command>,
    <command>DELETE</command>, and <command>TRUNCATE</command>, similar to how triggers are fired by
    particular event types.  By default, all operation types are replicated.
+   (Row filters have no effect for <command>TRUNCATE</command>. See 
+   <xref linkend="logical-replication-row-filter"/>).
   </para>
 
   <para>
@@ -317,6 +319,555 @@
   </sect2>
  </sect1>
 
+ <sect1 id="logical-replication-row-filter">
+  <title>Row Filters</title>
+
+  <para>
+   By default, all data from all published tables will be replicated to the
+   appropriate subscribers.
+  </para>
+
+  <para>
+   The replicated data can be reduced by using a <firstterm>row filter</firstterm>.
+   A user might choose to use row filters for behavioral, security or performance
+   reasons.
+  </para>
+
+  <para>
+   If a published table sets a row filter, a row is replicated only if its data
+   satisfies the row filter expression. This allows a set of tables to be
+   partially replicated.
+  </para>
+
+  <para>
+   The row filter is defined per table. Use a <literal>WHERE</literal> clause
+   after the table name for each published table that requires data to be
+   filtered out. The <literal>WHERE</literal> clause must be enclosed by
+   parentheses. See <xref linkend="sql-createpublication"/> for details.
+  </para>
+
+  <sect2 id="logical-replication-row-filter-rules">
+   <title>Row Filter Rules</title>
+
+   <para>
+    Row filters are applied <emphasis>before</emphasis> publishing the changes.
+   </para>
+
+   <para>
+    If the row filter evaluates to <literal>false</literal> or
+    <literal>NULL</literal> then the row is not replicated.
+   </para>
+
+   <para>
+    The <literal>WHERE</literal> clause expression is evaluated with the same
+    role used for the replication connection (i.e. the role specified in the
+    <literal>CONNECTION</literal> clause of the <xref linkend="sql-createsubscription"/>).
+   </para>
+
+   <para>
+    Row filters have no effect for <command>TRUNCATE</command> command.
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-restrictions">
+   <title>Expression Restrictions</title>
+
+   <para>
+    The <literal>WHERE</literal> clause allows only simple expressions. It
+    cannot contain user-defined functions, operators, types, and collations,
+    system column references or non-immutable built-in functions.
+   </para>
+
+   <para>
+    If a publication publishes <command>UPDATE</command> and/or
+    <command>DELETE</command> operations, the row filter <literal>WHERE</literal> clause
+    must contain only columns that are covered by the replica identity (see
+    <xref linkend="sql-altertable-replica-identity"/>). If a publication publishes only
+    <command>INSERT</command>, the row filter <literal>WHERE</literal> clause
+    can use any column.
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-transformations">
+   <title>UPDATE Transformations</title>
+
+   <para>
+    Whenever an <command>UPDATE</command> is processed, the row filter
+    expression is evaluated for both the old and new row (i.e. using the data
+    before and after the update).
+   </para>
+
+   <para>
+    If both evaluations are <literal>true</literal>, it replicates the
+    <command>UPDATE</command> change.
+   </para>
+
+   <para>
+    If both evaluations are <literal>false</literal>, it doesn't replicate
+    the change.
+   </para>
+
+   <para>
+    If only one of the old/new rows matches the row filter expression, the
+    <command>UPDATE</command> is transformed to <command>INSERT</command> or
+    <command>DELETE</command>, to avoid any data inconsistency. The row on the
+    subscriber should reflect what is defined by the row filter expression on
+    the publisher.
+
+   <itemizedlist>
+    <listitem>
+     <para>
+      If the old row satisfies the row filter expression (it was sent to the
+      subscriber) but the new row doesn't, then from a data consistency
+      perspective the old row should be removed from the subscriber.
+      So the <command>UPDATE</command> is transformed into a <command>DELETE</command>.
+     </para>
+    </listitem>
+    <listitem>
+     <para>
+      If the old row doesn't satisfy the row filter expression (it wasn't sent
+      to the subscriber) but the new row does, then from a data consistency
+      perspective the new row should be added to the subscriber.
+      So the <command>UPDATE</command> is transformed into an <command>INSERT</command>.
+     </para>
+    </listitem>
+   </itemizedlist>
+
+Summary:
+<synopsis>
+Case 1: old-row (no match)    new-row (no match)   -->   (drop change)
+Case 2: old-row (no match)    new row (match)      -->   INSERT
+Case 3: old-row (match)       new-row (no match)   -->   DELETE
+Case 4: old-row (match)       new row (match)      -->   UPDATE
+</synopsis>
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-partitioned-table">
+   <title>Partitioned Tables</title>
+
+   <para>
+    If the publication contains a partitioned table, the publication parameter
+    <literal>publish_via_partition_root</literal> determines which row filter
+    is used.
+    <itemizedlist>
+
+     <listitem>
+      <para>
+       If <literal>publish_via_partition_root</literal> is <literal>false</literal>
+       (default), each <emphasis>partition's</emphasis> row filter is used.
+      </para>
+     </listitem>
+
+     <listitem>
+      <para>
+       If <literal>publish_via_partition_root</literal> is <literal>true</literal>,
+       the <emphasis>root partitioned table's</emphasis> row filter is used.
+      </para>
+     </listitem>
+
+    </itemizedlist>
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-initial-data-sync">
+   <title>Initial Data Synchronization</title>
+
+   <para>
+    If the subscription requires copying pre-existing table data
+    and a publication contains <literal>WHERE</literal> clauses, only data that
+    satisfies the row filter expressions is copied to the subscriber.
+   </para>
+
+   <para>
+    If the subscription has several publications in which a table has been
+    published with different <literal>WHERE</literal> clauses, rows that satisfy
+    <emphasis>any</emphasis> of the expressions will be copied. (see
+    <xref linkend="logical-replication-row-filter-combining"/> for details).
+   </para>
+
+   <note>
+    <para>
+     Publication <literal>publish</literal> operations are ignored when copying pre-existing table data.
+    </para>
+   </note>
+
+   <note>
+    <para>
+     If the subscriber is in a release prior to 15, copy pre-existing data
+     doesn't use row filters even if they are defined in the publication.
+     This is because old releases can only copy the entire table data.
+    </para>
+   </note>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-combining">
+   <title>Combining Multiple Row Filters</title>
+
+   <para>
+    If the subscription has several publications in which the same table has
+    been published with different row filters (for the same <literal>publish</literal>
+    operation), those expressions get OR'ed together, so that rows satisfying
+    <emphasis>any</emphasis> of the expressions will be replicated.
+   </para>
+
+   <para>
+    This means all the other row filters for the same table become redundant if:
+    <itemizedlist>
+
+     <listitem>
+      <para>
+       one of the publications has no row filter.
+      </para>
+     </listitem>
+
+     <listitem>
+      <para>
+       one of the publications was created using <literal>FOR ALL TABLES</literal>.
+       This clause does not allow row filters.
+      </para>
+     </listitem>
+
+     <listitem>
+      <para>
+       one of the publications was created using
+       <literal>FOR ALL TABLES IN SCHEMA</literal> and the table belongs to
+       the referred schema. This clause does not allow row filters.
+      </para>
+     </listitem>
+
+    </itemizedlist>
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-examples">
+   <title>Examples</title>
+
+   <para>
+    Create some tables to be used in the following examples.
+<programlisting>
+testpub=# CREATE TABLE t1(a int, b int, c text, primary key(a,c));
+CREATE TABLE
+testpub=# CREATE TABLE t2(d int, e int, f int, primary key(d));
+CREATE TABLE
+testpub=# CREATE TABLE t3(g int, h int, i int, primary key(g));
+CREATE TABLE
+</programlisting>
+   </para>
+
+   <para>
+    Create some publications.
+<programlisting>
+testpub=# CREATE PUBLICATION p1 FOR TABLE t1 WHERE (a > 5 AND c = 'NSW');
+CREATE PUBLICATION
+testpub=# CREATE PUBLICATION p2 FOR TABLE t1, t2 WHERE (e = 99);
+CREATE PUBLICATION
+testpub=# CREATE PUBLICATION p3 FOR TABLE t2 WHERE (d = 10), t3 WHERE (g = 10);
+CREATE PUBLICATION
+</programlisting>
+    <itemizedlist>
+     <listitem>
+      <para>
+       Publication <literal>p1</literal> has 1 table (<literal>t1</literal>) and
+       that table has a row filter.
+      </para>
+     </listitem>
+     <listitem>
+      <para>
+       Publication <literal>p2</literal> has 2 tables. Table <literal>t1</literal>
+       has no row filter, and table <literal>t2</literal> has a row filter.
+      </para>
+     </listitem>
+     <listitem>
+      <para>
+       Publication <literal>p3</literal> has 2 tables, and both of them have a
+       row filter.
+      </para>
+     </listitem>
+    </itemizedlist>
+   </para>
+
+   <para>
+    The PSQL command <command>\dRp+</command> shows the row filter expressions
+    (if defined) for each table of the publications.
+<programlisting>
+testpub=# \dRp+
+                               Publication p1
+  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root 
+----------+------------+---------+---------+---------+-----------+----------
+ postgres | f          | t       | t       | t       | t         | f
+Tables:
+    "public.t1" WHERE ((a > 5) AND (c = 'NSW'::text))
+
+                               Publication p2
+  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root 
+----------+------------+---------+---------+---------+-----------+----------
+ postgres | f          | t       | t       | t       | t         | f
+Tables:
+    "public.t1"
+    "public.t2" WHERE (e = 99)
+
+                               Publication p3
+  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root 
+----------+------------+---------+---------+---------+-----------+----------
+ postgres | f          | t       | t       | t       | t         | f
+Tables:
+    "public.t2" WHERE (d = 10)
+    "public.t3" WHERE (g = 10)
+</programlisting>
+   </para>
+
+   <para>
+    The PSQL command <command>\d</command> shows what publications the table is
+    a member of, as well as that table's row filter expression (if defined) in
+    those publications.
+<programlisting>
+testpub=# \d t1
+                 Table "public.t1"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           | not null | 
+ b      | integer |           |          | 
+ c      | text    |           | not null | 
+Indexes:
+    "t1_pkey" PRIMARY KEY, btree (a, c)
+Publications:
+    "p1" WHERE ((a > 5) AND (c = 'NSW'::text))
+    "p2"
+
+testpub=# \d t2
+                 Table "public.t2"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ d      | integer |           | not null | 
+ e      | integer |           |          | 
+ f      | integer |           |          | 
+Indexes:
+    "t2_pkey" PRIMARY KEY, btree (d)
+Publications:
+    "p2" WHERE (e = 99)
+    "p3" WHERE (d = 10)
+
+testpub=# \d t3
+                 Table "public.t3"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ g      | integer |           | not null | 
+ h      | integer |           |          | 
+ i      | integer |           |          | 
+Indexes:
+    "t3_pkey" PRIMARY KEY, btree (g)
+Publications:
+    "p3" WHERE (g = 10)
+</programlisting>
+    <itemizedlist>
+     <listitem>
+      <para>
+       Table <literal>t1</literal> is a member of 2 publications, but
+       has a row filter only in <literal>p1</literal>.
+      </para>
+     </listitem>
+     <listitem>
+      <para>
+       Table <literal>t2</literal> is a member of 2 publications, and
+       has a different row filter in each of them.
+      </para>
+     </listitem>
+    </itemizedlist>
+   </para>
+
+   <para>
+    On the subscriber node, create a table <literal>t1</literal> with the same
+    definition as the one on the publisher, and also create the subscription
+    <literal>s1</literal> that subscribes to the publication <literal>p1</literal>.
+<programlisting>
+testsub=# CREATE TABLE t1(a int, b int, c text, primary key(a,c));
+CREATE TABLE
+testsub=# CREATE SUBSCRIPTION s1
+testsub-# CONNECTION 'host=localhost dbname=testpub application_name=s1'
+testsub-# PUBLICATION p1;
+CREATE SUBSCRIPTION
+</programlisting>
+   </para>
+
+   <para>
+    Insert some rows.
+<programlisting>
+testpub=# INSERT INTO t1 VALUES (2, 102, 'NSW');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (3, 103, 'QLD');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (4, 104, 'VIC');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (5, 105, 'ACT');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (6, 106, 'NSW');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (7, 107, 'NT');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (8, 108, 'QLD');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (9, 109, 'NSW');
+INSERT 0 1
+
+testpub=# SELECT * FROM t1; 
+ a |  b  |  c
+---+-----+-----
+ 2 | 102 | NSW
+ 3 | 103 | QLD
+ 4 | 104 | VIC
+ 5 | 105 | ACT
+ 6 | 106 | NSW
+ 7 | 107 | NT
+ 8 | 108 | QLD
+ 9 | 109 | NSW
+(8 rows)
+</programlisting>
+<programlisting>
+testsub=# SELECT * FROM t1;
+ a |  b  |  c  
+---+-----+-----
+ 6 | 106 | NSW
+ 9 | 109 | NSW
+(2 rows)
+</programlisting>
+    <itemizedlist>
+     <listitem>
+      <para>
+       Only the rows satisfying the <literal>t1 WHERE</literal>
+       clause of publication <literal>p1</literal> are replicated.
+      </para>
+     </listitem>
+    </itemizedlist>
+   </para>
+
+   <para>
+    Update some data, where the old and new row values both
+    satisfy the <literal>t1 WHERE</literal> clause of publication
+    <literal>p1</literal>.
+<programlisting>
+testpub=# UPDATE t1 SET b = 999 WHERE a = 6;
+UPDATE 1
+
+testpub=# SELECT * FROM t1;
+ a |  b  |  c  
+---+-----+-----
+ 2 | 102 | NSW
+ 3 | 103 | QLD
+ 4 | 104 | VIC
+ 5 | 105 | ACT
+ 7 | 107 | NT
+ 8 | 108 | QLD
+ 9 | 109 | NSW
+ 6 | 999 | NSW
+(8 rows)
+</programlisting>
+<programlisting>
+testsub=# SELECT * FROM t1;
+ a |  b  |  c  
+---+-----+-----
+ 9 | 109 | NSW
+ 6 | 999 | NSW
+(2 rows)
+</programlisting>
+    <itemizedlist>
+     <listitem>
+      <para>
+       The <command>UPDATE</command> replicates the change as normal.
+      </para>
+     </listitem>
+    </itemizedlist>
+   </para>
+
+   <para>
+    Update some data, where the old row values did not satisfy
+    the <literal>t1 WHERE</literal> clause of publication <literal>p1</literal>,
+    but the new row values do satisfy it.
+<programlisting>
+testpub=# UPDATE t1 SET a = 555 WHERE a = 2;
+UPDATE 1
+
+testpub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   3 | 103 | QLD
+   4 | 104 | VIC
+   5 | 105 | ACT
+   7 | 107 | NT
+   8 | 108 | QLD
+   9 | 109 | NSW
+   6 | 999 | NSW
+ 555 | 102 | NSW
+(8 rows)
+</programlisting>
+<programlisting>
+testsub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   9 | 109 | NSW
+   6 | 999 | NSW
+ 555 | 102 | NSW
+(3 rows)
+</programlisting>
+    <itemizedlist>
+     <listitem>
+      <para>
+       The <command>UPDATE</command> is transformed into an <command>INSERT</command>
+       and the change is replicated.
+      </para>
+     </listitem>
+    </itemizedlist>
+   </para>
+
+   <para>
+    Update some data, where the old row values satisfied
+    the <literal>t1 WHERE</literal> clause of publication <literal>p1</literal>,
+    but the new row values do not satisfy it.
+<programlisting>
+testpub=# UPDATE t1 SET c = 'VIC' WHERE a = 9;
+UPDATE 1
+
+testpub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   3 | 103 | QLD
+   4 | 104 | VIC
+   5 | 105 | ACT
+   7 | 107 | NT
+   8 | 108 | QLD
+   6 | 999 | NSW
+ 555 | 102 | NSW
+   9 | 109 | VIC
+(8 rows)
+</programlisting>
+<programlisting>
+testsub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   6 | 999 | NSW
+ 555 | 102 | NSW
+(2 rows)
+</programlisting>
+    <itemizedlist>
+     <listitem>
+      <para>
+       The <command>UPDATE</command> is transformed into a <command>DELETE</command>
+       and the change is replicated.
+      </para>
+     </listitem>
+    </itemizedlist>
+   </para>
+
+  </sect2>
+
+ </sect1>
+
  <sect1 id="logical-replication-conflicts">
   <title>Conflicts</title>
 
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index fb2d013..23d883c 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -254,6 +254,8 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
    <literal>publish_via_partition_root</literal> determines if it uses the
    partition's row filter (if the parameter is false, the default) or the root
    partitioned table's row filter.
+   See <xref linkend="logical-replication-row-filter"/> for details about row
+   filters.
   </para>
 
   <para>
-- 
1.8.3.1

From a1734d6dfdf4c0f3582d9b0da55fa383cf12ac19 Mon Sep 17 00:00:00 2001
From: Peter Smith <peter.b.smith@fujitsu.com>
Date: Mon, 11 Apr 2022 16:54:54 +1000
Subject: [PATCH v7] PG DOCS page for row filters.

This patch introduces a new documentation page for describing the "Row Filters" feature.

Author: Peter Smith, Euler Taveira
Reviewed By: Greg Nancarrow, Aleksander Alekseev, Amit Kapila, Ajin Cherian
Discussion: https://www.postgresql.org/message-id/CAHut%2BPtnsBr59%3D_NvxXp_%3DS-em0WxyuDOQmSTuHGb4sVhkHffg%40mail.gmail.com
---
 doc/src/sgml/logical-replication.sgml    | 642 +++++++++++++++++++++++++++++++
 doc/src/sgml/ref/create_publication.sgml |   2 +
 2 files changed, 644 insertions(+)

diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index 555fbd7..ca47fe1 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -118,6 +118,8 @@
    any combination of <command>INSERT</command>, <command>UPDATE</command>,
    <command>DELETE</command>, and <command>TRUNCATE</command>, similar to how triggers are fired by
    particular event types.  By default, all operation types are replicated.
+   (Row filters have no effect for <command>TRUNCATE</command>. See 
+   <xref linkend="logical-replication-row-filter"/>).
   </para>
 
   <para>
@@ -317,6 +319,646 @@
   </sect2>
  </sect1>
 
+ <sect1 id="logical-replication-row-filter">
+  <title>Row Filters</title>
+
+  <para>
+   By default, all data from all published tables will be replicated to the
+   appropriate subscribers. The replicated data can be reduced by using a 
+   <firstterm>row filter</firstterm>. A user might choose to use row filters
+   for behavioral, security or performance reasons. If a published table sets a
+   row filter, a row is replicated only if its data satisfies the row filter
+   expression. This allows a set of tables to be partially replicated. The row
+   filter is defined per table. Use a <literal>WHERE</literal> clause after the
+   table name for each published table that requires data to be filtered out.
+   The <literal>WHERE</literal> clause must be enclosed by parentheses. See
+   <xref linkend="sql-createpublication"/> for details.
+  </para>
+
+  <sect2 id="logical-replication-row-filter-rules">
+   <title>Row Filter Rules</title>
+
+   <para>
+    Row filters are applied <emphasis>before</emphasis> publishing the changes.
+    If the row filter evaluates to <literal>false</literal> or <literal>NULL</literal>
+    then the row is not replicated. The <literal>WHERE</literal> clause expression
+    is evaluated with the same role used for the replication connection (i.e.
+    the role specified in the <literal>CONNECTION</literal> clause of the
+    <xref linkend="sql-createsubscription"/>). Row filters have no effect for
+    <command>TRUNCATE</command> command.
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-restrictions">
+   <title>Expression Restrictions</title>
+
+   <para>
+    The <literal>WHERE</literal> clause allows only simple expressions. It
+    cannot contain user-defined functions, operators, types, and collations,
+    system column references or non-immutable built-in functions.
+   </para>
+
+   <para>
+    If a publication publishes <command>UPDATE</command> and/or
+    <command>DELETE</command> operations, the row filter <literal>WHERE</literal> clause
+    must contain only columns that are covered by the replica identity (see
+    <xref linkend="sql-altertable-replica-identity"/>). If a publication publishes only
+    <command>INSERT</command>, the row filter <literal>WHERE</literal> clause
+    can use any column.
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-transformations">
+   <title>UPDATE Transformations</title>
+
+   <para>
+    Whenever an <command>UPDATE</command> is processed, the row filter
+    expression is evaluated for both the old and new row (i.e. using the data
+    before and after the update). If both evaluations are <literal>true</literal>,
+    it replicates the <command>UPDATE</command> change. If both evaluations are
+    <literal>false</literal>, it doesn't replicate the change. If only one of
+    the old/new rows matches the row filter expression, the <command>UPDATE</command>
+    is transformed to <command>INSERT</command> or <command>DELETE</command>, to
+    avoid any data inconsistency. The row on the subscriber should reflect what
+    is defined by the row filter expression on the publisher.
+   </para>
+
+   <para>
+    If the old row satisfies the row filter expression (it was sent to the
+    subscriber) but the new row doesn't, then from a data consistency
+    perspective the old row should be removed from the subscriber.
+    So the <command>UPDATE</command> is transformed into a <command>DELETE</command>.
+   </para>
+
+   <para>
+    If the old row doesn't satisfy the row filter expression (it wasn't sent
+    to the subscriber) but the new row does, then from a data consistency
+    perspective the new row should be added to the subscriber.
+    So the <command>UPDATE</command> is transformed into an <command>INSERT</command>.
+   </para>
+
+   <para>
+Summary:
+<synopsis>
+Case 1: old-row (no match)    new-row (no match)   -->   (drop change)
+Case 2: old-row (no match)    new row (match)      -->   INSERT
+Case 3: old-row (match)       new-row (no match)   -->   DELETE
+Case 4: old-row (match)       new row (match)      -->   UPDATE
+</synopsis></para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-partitioned-table">
+   <title>Partitioned Tables</title>
+
+   <para>
+    If the publication contains a partitioned table, the publication parameter
+    <literal>publish_via_partition_root</literal> determines which row filter
+    is used.
+    <itemizedlist>
+     <listitem>
+      <para>
+       If <literal>publish_via_partition_root</literal> is <literal>false</literal>
+       (default), each <emphasis>partition's</emphasis> row filter is used.
+      </para>
+     </listitem>
+     <listitem>
+      <para>
+       If <literal>publish_via_partition_root</literal> is <literal>true</literal>,
+       the <emphasis>root partitioned table's</emphasis> row filter is used.
+      </para>
+     </listitem>
+    </itemizedlist></para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-initial-data-sync">
+   <title>Initial Data Synchronization</title>
+
+   <para>
+    If the subscription requires copying pre-existing table data
+    and a publication contains <literal>WHERE</literal> clauses, only data that
+    satisfies the row filter expressions is copied to the subscriber.
+   </para>
+
+   <para>
+    If the subscription has several publications in which a table has been
+    published with different <literal>WHERE</literal> clauses, rows that satisfy
+    <emphasis>any</emphasis> of the expressions will be copied. (see
+    <xref linkend="logical-replication-row-filter-combining"/> for details).
+   </para>
+
+   <note>
+    <para>
+     If the subscriber is in a release prior to 15, copy pre-existing data
+     doesn't use row filters even if they are defined in the publication.
+     This is because old releases can only copy the entire table data.
+    </para>
+   </note>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-combining">
+   <title>Combining Multiple Row Filters</title>
+
+   <para>
+    If the subscription has several publications in which the same table has
+    been published with different row filters (for the same <literal>publish</literal>
+    operation), those expressions get OR'ed together, so that rows satisfying
+    <emphasis>any</emphasis> of the expressions will be replicated.
+   </para>
+
+   <para>
+    This means all the other row filters for the same table become redundant if:
+    <itemizedlist>
+     <listitem>
+      <para>
+       one of the publications has no row filter.
+      </para>
+     </listitem>
+     <listitem>
+      <para>
+       one of the publications was created using <literal>FOR ALL TABLES</literal>.
+       This clause does not allow row filters.
+      </para>
+     </listitem>
+     <listitem>
+      <para>
+       one of the publications was created using
+       <literal>FOR ALL TABLES IN SCHEMA</literal> and the table belongs to
+       the referred schema. This clause does not allow row filters.
+      </para>
+     </listitem>
+    </itemizedlist></para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-examples">
+   <title>Examples</title>
+
+   <para>
+    Create some tables to be used in the following examples.
+<programlisting>
+testpub=# CREATE TABLE t1(a int, b int, c text, primary key(a,c));
+CREATE TABLE
+testpub=# CREATE TABLE t2(d int, e int, f int, primary key(d));
+CREATE TABLE
+testpub=# CREATE TABLE t3(g int, h int, i int, primary key(g));
+CREATE TABLE
+</programlisting></para>
+
+   <para>
+    Create some publications.
+<programlisting>
+testpub=# CREATE PUBLICATION p1 FOR TABLE t1 WHERE (a > 5 AND c = 'NSW');
+CREATE PUBLICATION
+testpub=# CREATE PUBLICATION p2 FOR TABLE t1, t2 WHERE (e = 99);
+CREATE PUBLICATION
+testpub=# CREATE PUBLICATION p3 FOR TABLE t2 WHERE (d = 10), t3 WHERE (g = 10);
+CREATE PUBLICATION
+</programlisting>
+    <itemizedlist>
+     <listitem>
+      <para>
+       Publication <literal>p1</literal> has one table (<literal>t1</literal>) and
+       that table has a row filter.
+      </para>
+     </listitem>
+     <listitem>
+      <para>
+       Publication <literal>p2</literal> has two tables. Table <literal>t1</literal>
+       has no row filter, and table <literal>t2</literal> has a row filter.
+      </para>
+     </listitem>
+     <listitem>
+      <para>
+       Publication <literal>p3</literal> has two tables, and both of them have a
+       row filter.
+      </para>
+     </listitem>
+    </itemizedlist></para>
+
+   <para>
+    The PSQL command <command>\dRp+</command> shows the row filter expressions
+    (if defined) for each table of the publications.
+<programlisting>
+testpub=# \dRp+
+                               Publication p1
+  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root 
+----------+------------+---------+---------+---------+-----------+----------
+ postgres | f          | t       | t       | t       | t         | f
+Tables:
+    "public.t1" WHERE ((a > 5) AND (c = 'NSW'::text))
+
+                               Publication p2
+  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root 
+----------+------------+---------+---------+---------+-----------+----------
+ postgres | f          | t       | t       | t       | t         | f
+Tables:
+    "public.t1"
+    "public.t2" WHERE (e = 99)
+
+                               Publication p3
+  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root 
+----------+------------+---------+---------+---------+-----------+----------
+ postgres | f          | t       | t       | t       | t         | f
+Tables:
+    "public.t2" WHERE (d = 10)
+    "public.t3" WHERE (g = 10)
+</programlisting></para>
+
+   <para>
+    The PSQL command <command>\d</command> shows what publications the table is
+    a member of, as well as that table's row filter expression (if defined) in
+    those publications.
+<programlisting>
+testpub=# \d t1
+                 Table "public.t1"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           | not null | 
+ b      | integer |           |          | 
+ c      | text    |           | not null | 
+Indexes:
+    "t1_pkey" PRIMARY KEY, btree (a, c)
+Publications:
+    "p1" WHERE ((a > 5) AND (c = 'NSW'::text))
+    "p2"
+
+testpub=# \d t2
+                 Table "public.t2"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ d      | integer |           | not null | 
+ e      | integer |           |          | 
+ f      | integer |           |          | 
+Indexes:
+    "t2_pkey" PRIMARY KEY, btree (d)
+Publications:
+    "p2" WHERE (e = 99)
+    "p3" WHERE (d = 10)
+
+testpub=# \d t3
+                 Table "public.t3"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ g      | integer |           | not null | 
+ h      | integer |           |          | 
+ i      | integer |           |          | 
+Indexes:
+    "t3_pkey" PRIMARY KEY, btree (g)
+Publications:
+    "p3" WHERE (g = 10)
+</programlisting>
+    <itemizedlist>
+     <listitem>
+      <para>
+       Table <literal>t1</literal> is a member of two publications, but
+       has a row filter only in <literal>p1</literal>.
+      </para>
+     </listitem>
+     <listitem>
+      <para>
+       Table <literal>t2</literal> is a member of two publications, and
+       has a different row filter in each of them.
+      </para>
+     </listitem>
+    </itemizedlist></para>
+
+   <para>
+    On the subscriber node, create a table <literal>t1</literal> with the same
+    definition as the one on the publisher, and also create the subscription
+    <literal>s1</literal> that subscribes to the publication <literal>p1</literal>.
+<programlisting>
+testsub=# CREATE TABLE t1(a int, b int, c text, primary key(a,c));
+CREATE TABLE
+testsub=# CREATE SUBSCRIPTION s1
+testsub-# CONNECTION 'host=localhost dbname=testpub application_name=s1'
+testsub-# PUBLICATION p1;
+CREATE SUBSCRIPTION
+</programlisting></para>
+
+   <para>
+    Insert some rows.
+<programlisting>
+testpub=# INSERT INTO t1 VALUES (2, 102, 'NSW');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (3, 103, 'QLD');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (4, 104, 'VIC');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (5, 105, 'ACT');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (6, 106, 'NSW');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (7, 107, 'NT');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (8, 108, 'QLD');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (9, 109, 'NSW');
+INSERT 0 1
+
+testpub=# SELECT * FROM t1; 
+ a |  b  |  c
+---+-----+-----
+ 2 | 102 | NSW
+ 3 | 103 | QLD
+ 4 | 104 | VIC
+ 5 | 105 | ACT
+ 6 | 106 | NSW
+ 7 | 107 | NT
+ 8 | 108 | QLD
+ 9 | 109 | NSW
+(8 rows)
+</programlisting>
+<programlisting>
+testsub=# SELECT * FROM t1;
+ a |  b  |  c  
+---+-----+-----
+ 6 | 106 | NSW
+ 9 | 109 | NSW
+(2 rows)
+</programlisting>
+    <itemizedlist>
+     <listitem>
+      <para>
+       Only the rows satisfying the <literal>t1 WHERE</literal>
+       clause of publication <literal>p1</literal> are replicated.
+      </para>
+     </listitem>
+    </itemizedlist></para>
+
+   <para>
+    Update some data, where the old and new row values both
+    satisfy the <literal>t1 WHERE</literal> clause of publication
+    <literal>p1</literal>.
+<programlisting>
+testpub=# UPDATE t1 SET b = 999 WHERE a = 6;
+UPDATE 1
+
+testpub=# SELECT * FROM t1;
+ a |  b  |  c  
+---+-----+-----
+ 2 | 102 | NSW
+ 3 | 103 | QLD
+ 4 | 104 | VIC
+ 5 | 105 | ACT
+ 7 | 107 | NT
+ 8 | 108 | QLD
+ 9 | 109 | NSW
+ 6 | 999 | NSW
+(8 rows)
+</programlisting>
+<programlisting>
+testsub=# SELECT * FROM t1;
+ a |  b  |  c  
+---+-----+-----
+ 9 | 109 | NSW
+ 6 | 999 | NSW
+(2 rows)
+</programlisting>
+    <itemizedlist>
+     <listitem>
+      <para>
+       The <command>UPDATE</command> replicates the change as normal.
+      </para>
+     </listitem>
+    </itemizedlist></para>
+
+   <para>
+    Update some data, where the old row values did not satisfy
+    the <literal>t1 WHERE</literal> clause of publication <literal>p1</literal>,
+    but the new row values do satisfy it.
+<programlisting>
+testpub=# UPDATE t1 SET a = 555 WHERE a = 2;
+UPDATE 1
+
+testpub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   3 | 103 | QLD
+   4 | 104 | VIC
+   5 | 105 | ACT
+   7 | 107 | NT
+   8 | 108 | QLD
+   9 | 109 | NSW
+   6 | 999 | NSW
+ 555 | 102 | NSW
+(8 rows)
+</programlisting>
+<programlisting>
+testsub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   9 | 109 | NSW
+   6 | 999 | NSW
+ 555 | 102 | NSW
+(3 rows)
+</programlisting>
+    <itemizedlist>
+     <listitem>
+      <para>
+       The <command>UPDATE</command> is transformed into an <command>INSERT</command>
+       and the change is replicated.
+      </para>
+     </listitem>
+    </itemizedlist></para>
+
+   <para>
+    Update some data, where the old row values satisfied
+    the <literal>t1 WHERE</literal> clause of publication <literal>p1</literal>,
+    but the new row values do not satisfy it.
+<programlisting>
+testpub=# UPDATE t1 SET c = 'VIC' WHERE a = 9;
+UPDATE 1
+
+testpub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   3 | 103 | QLD
+   4 | 104 | VIC
+   5 | 105 | ACT
+   7 | 107 | NT
+   8 | 108 | QLD
+   6 | 999 | NSW
+ 555 | 102 | NSW
+   9 | 109 | VIC
+(8 rows)
+</programlisting>
+<programlisting>
+testsub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   6 | 999 | NSW
+ 555 | 102 | NSW
+(2 rows)
+</programlisting>
+    <itemizedlist>
+     <listitem>
+      <para>
+       The <command>UPDATE</command> is transformed into a <command>DELETE</command>
+       and the change is replicated.
+      </para>
+     </listitem>
+    </itemizedlist></para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-example-partitions">
+   <title>Example (Partitioned tables)</title>
+
+   <para>
+    Create the same partitioned tables on the publisher and subscriber.
+<programlisting>
+testpub=# CREATE TABLE parent(a int primary key) PARTITION BY RANGE(a);
+CREATE TABLE
+testpub=# CREATE TABLE child PARTITION OF parent DEFAULT;
+CREATE TABLE
+</programlisting>
+<programlisting>
+testsub=# CREATE TABLE parent(a int primary key) PARTITION BY RANGE(a);
+CREATE TABLE
+testsub=# CREATE TABLE child PARTITION OF parent DEFAULT;
+CREATE TABLE
+</programlisting></para>
+
+   <para>
+    Create a publication <literal>p4</literal>, and then subscribe to it.
+<programlisting>
+testpub=# CREATE PUBLICATION p4 FOR TABLE parent WHERE (a &lt; 5), child WHERE (a >= 5)
+testpub-# WITH (publish_via_partition_root=true);
+CREATE PUBLICATION
+</programlisting>
+    <itemizedlist>
+     <listitem>
+      <para>
+       The publication is using <literal>publish_via_partition_root</literal> set true.
+      </para>
+     </listitem>
+     <listitem>
+      <para>
+       There are row filters defined on both the partitioned table
+       (<literal>parent</literal>), and on the partition (<literal>child</literal>).
+      </para>
+     </listitem>
+    </itemizedlist>
+<programlisting>
+testsub=# CREATE SUBSCRIPTION s4
+testsub-# CONNECTION 'host=localhost dbname=testpub application_name=s4'
+testsub-# PUBLICATION p4;
+CREATE SUBSCRIPTION
+</programlisting></para>
+
+   <para>
+    Insert some values directly into the <literal>parent</literal> and
+    <literal>child</literal> tables.
+<programlisting>
+test_pub=# INSERT INTO parent VALUES (2), (4), (6);
+INSERT 0 3
+test_pub=# INSERT INTO child VALUES (3), (5), (7);
+INSERT 0 3
+</programlisting>
+<programlisting>
+test_pub=# SELECT * FROM parent ORDER BY a;
+ a 
+---
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+(6 rows)
+</programlisting>
+<programlisting>
+test_sub=# SELECT * FROM parent ORDER BY a;
+ a 
+---
+ 2
+ 3
+ 4
+(3 rows)
+</programlisting>
+    <itemizedlist>
+     <listitem>
+      <para>
+       The row filter of <literal>parent</literal> was used (because
+       <literal>publish_via_partition_root</literal> was true).
+      </para>
+     </listitem>
+    </itemizedlist></para>
+
+   <para>
+    Repeat the same test, but with a different value for <literal>publish_via_partition_root</literal>.
+<programlisting>
+testpub=# DROP PUBLICATION p4;
+DROP PUBLICATION
+testpub=# CREATE PUBLICATION p4 FOR TABLE parent, child WHERE (a >= 5)
+testpub-# WITH (publish_via_partition_root=false);
+CREATE PUBLICATION
+</programlisting>
+    <itemizedlist>
+     <listitem>
+      <para>
+       The publication is using <literal>publish_via_partition_root</literal> set false.
+      </para>
+     </listitem>
+     <listitem>
+      <para>
+       A row filter is defined on the partition (<literal>child</literal>).
+      </para>
+     </listitem>
+    </itemizedlist>
+<programlisting>
+testsub=# ALTER SUBSCRIPTION s4 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+</programlisting></para>
+
+   <para>
+    Do the inserts same as before.
+<programlisting>
+testpub=# TRUNCATE parent;
+TRUNCATE TABLE
+testpub=# INSERT INTO parent VALUES (2), (4), (6);
+INSERT 0 3
+testpub=# INSERT INTO child VALUES (3), (5), (7);
+INSERT 0 3
+
+testpub=# SELECT * FROM parent ORDER BY a;
+ a 
+---
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+(6 rows)
+</programlisting>
+<programlisting>
+testsub=# SELECT * FROM child ORDER BY a;
+ a 
+---
+ 5
+ 6
+ 7
+(3 rows)
+</programlisting>
+    <itemizedlist>
+     <listitem>
+      <para>
+       The row filter of <literal>child</literal> was used (because
+       <literal>publish_via_partition_root</literal> was false).
+      </para>
+     </listitem>
+    </itemizedlist></para>
+
+  </sect2>
+
+ </sect1>
+
  <sect1 id="logical-replication-conflicts">
   <title>Conflicts</title>
 
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index fb2d013..23d883c 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -254,6 +254,8 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
    <literal>publish_via_partition_root</literal> determines if it uses the
    partition's row filter (if the parameter is false, the default) or the root
    partitioned table's row filter.
+   See <xref linkend="logical-replication-row-filter"/> for details about row
+   filters.
   </para>
 
   <para>
-- 
1.8.3.1

From 3ca87f788d96939632d3cb9c3221d6c0ed9bc712 Mon Sep 17 00:00:00 2001
From: Amit Kapila <akapila@postgresql.org>
Date: Mon, 11 Apr 2022 15:11:59 +0530
Subject: [PATCH v8] Add additional documentation for row filters.

Commit 52e4f0cd47 added a feature to allow specifying row filters for
logical replication of tables. This patch adds detailed documentation on
that feature including examples to make it easier for users to understand.

Author: Peter Smith, Euler Taveira
Reviewed By: Greg Nancarrow, Aleksander Alekseev, Amit Kapila, Ajin Cherian, Alvaro Herrera
---
 doc/src/sgml/logical-replication.sgml    | 646 +++++++++++++++++++++++
 doc/src/sgml/ref/create_publication.sgml |   2 +
 2 files changed, 648 insertions(+)

diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index 555fbd749c..04793c51be 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -118,6 +118,8 @@
    any combination of <command>INSERT</command>, <command>UPDATE</command>,
    <command>DELETE</command>, and <command>TRUNCATE</command>, similar to how triggers are fired by
    particular event types.  By default, all operation types are replicated.
+   (Row filters have no effect for <command>TRUNCATE</command>. See 
+   <xref linkend="logical-replication-row-filter"/>).
   </para>
 
   <para>
@@ -317,6 +319,650 @@
   </sect2>
  </sect1>
 
+ <sect1 id="logical-replication-row-filter">
+  <title>Row Filters</title>
+
+  <para>
+   By default, all data from all published tables will be replicated to the
+   appropriate subscribers. The replicated data can be reduced by using a 
+   <firstterm>row filter</firstterm>. A user might choose to use row filters
+   for behavioral, security or performance reasons. If a published table sets a
+   row filter, a row is replicated only if its data satisfies the row filter
+   expression. This allows a set of tables to be partially replicated. The row
+   filter is defined per table. Use a <literal>WHERE</literal> clause after the
+   table name for each published table that requires data to be filtered out.
+   The <literal>WHERE</literal> clause must be enclosed by parentheses. See
+   <xref linkend="sql-createpublication"/> for details.
+  </para>
+
+  <sect2 id="logical-replication-row-filter-rules">
+   <title>Row Filter Rules</title>
+
+   <para>
+    Row filters are applied <emphasis>before</emphasis> publishing the changes.
+    If the row filter evaluates to <literal>false</literal> or <literal>NULL</literal>
+    then the row is not replicated. The <literal>WHERE</literal> clause expression
+    is evaluated with the same role used for the replication connection (i.e.
+    the role specified in the <literal>CONNECTION</literal> clause of the
+    <xref linkend="sql-createsubscription"/>). Row filters have no effect for
+    <command>TRUNCATE</command> command.
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-restrictions">
+   <title>Expression Restrictions</title>
+
+   <para>
+    The <literal>WHERE</literal> clause allows only simple expressions. It
+    cannot contain user-defined functions, operators, types, and collations,
+    system column references or non-immutable built-in functions.
+   </para>
+
+   <para>
+    If a publication publishes <command>UPDATE</command> and/or
+    <command>DELETE</command> operations, the row filter <literal>WHERE</literal> clause
+    must contain only columns that are covered by the replica identity (see
+    <xref linkend="sql-altertable-replica-identity"/>). If a publication publishes only
+    <command>INSERT</command>, the row filter <literal>WHERE</literal> clause
+    can use any column.
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-transformations">
+   <title>UPDATE Transformations</title>
+
+   <para>
+    Whenever an <command>UPDATE</command> is processed, the row filter
+    expression is evaluated for both the old and new row (i.e. using the data
+    before and after the update). If both evaluations are <literal>true</literal>,
+    it replicates the <command>UPDATE</command> change. If both evaluations are
+    <literal>false</literal>, it doesn't replicate the change. If only one of
+    the old/new rows matches the row filter expression, the <command>UPDATE</command>
+    is transformed to <command>INSERT</command> or <command>DELETE</command>, to
+    avoid any data inconsistency. The row on the subscriber should reflect what
+    is defined by the row filter expression on the publisher.
+   </para>
+
+   <para>
+    If the old row satisfies the row filter expression (it was sent to the
+    subscriber) but the new row doesn't, then from a data consistency
+    perspective the old row should be removed from the subscriber.
+    So the <command>UPDATE</command> is transformed into a <command>DELETE</command>.
+   </para>
+
+   <para>
+    If the old row doesn't satisfy the row filter expression (it wasn't sent
+    to the subscriber) but the new row does, then from a data consistency
+    perspective the new row should be added to the subscriber.
+    So the <command>UPDATE</command> is transformed into an <command>INSERT</command>.
+   </para>
+
+   <para>
+Summary:
+<synopsis>
+Case 1: old-row (no match)    new-row (no match)   -->   (drop change)
+Case 2: old-row (no match)    new row (match)      -->   INSERT
+Case 3: old-row (match)       new-row (no match)   -->   DELETE
+Case 4: old-row (match)       new row (match)      -->   UPDATE
+</synopsis></para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-partitioned-table">
+   <title>Partitioned Tables</title>
+
+   <para>
+    If the publication contains a partitioned table, the publication parameter
+    <literal>publish_via_partition_root</literal> determines which row filter
+    is used.
+    <itemizedlist>
+     <listitem>
+      <para>
+       If <literal>publish_via_partition_root</literal> is <literal>false</literal>
+       (default), each <emphasis>partition's</emphasis> row filter is used.
+      </para>
+     </listitem>
+     <listitem>
+      <para>
+       If <literal>publish_via_partition_root</literal> is <literal>true</literal>,
+       the <emphasis>root partitioned table's</emphasis> row filter is used.
+      </para>
+     </listitem>
+    </itemizedlist></para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-initial-data-sync">
+   <title>Initial Data Synchronization</title>
+
+   <para>
+    If the subscription requires copying pre-existing table data
+    and a publication contains <literal>WHERE</literal> clauses, only data that
+    satisfies the row filter expressions is copied to the subscriber.
+   </para>
+
+   <para>
+    If the subscription has several publications in which a table has been
+    published with different <literal>WHERE</literal> clauses, rows that satisfy
+    <emphasis>any</emphasis> of the expressions will be copied. (see
+    <xref linkend="logical-replication-row-filter-combining"/> for details).
+   </para>
+
+   <note>
+    <para>
+     If the subscriber is in a release prior to 15, copy pre-existing data
+     doesn't use row filters even if they are defined in the publication.
+     This is because old releases can only copy the entire table data.
+    </para>
+   </note>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-combining">
+   <title>Combining Multiple Row Filters</title>
+
+   <para>
+    If the subscription has several publications in which the same table has
+    been published with different row filters (for the same <literal>publish</literal>
+    operation), those expressions get OR'ed together, so that rows satisfying
+    <emphasis>any</emphasis> of the expressions will be replicated.
+   </para>
+
+   <para>
+    This means all the other row filters for the same table become redundant if:
+    <itemizedlist>
+     <listitem>
+      <para>
+       one of the publications has no row filter.
+      </para>
+     </listitem>
+     <listitem>
+      <para>
+       one of the publications was created using <literal>FOR ALL TABLES</literal>.
+       This clause does not allow row filters.
+      </para>
+     </listitem>
+     <listitem>
+      <para>
+       one of the publications was created using
+       <literal>FOR ALL TABLES IN SCHEMA</literal> and the table belongs to
+       the referred schema. This clause does not allow row filters.
+      </para>
+     </listitem>
+    </itemizedlist></para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-examples">
+   <title>Examples</title>
+
+   <para>
+    Create some tables to be used in the following examples.
+<programlisting>
+testpub=# CREATE TABLE t1(a int, b int, c text, primary key(a,c));
+CREATE TABLE
+testpub=# CREATE TABLE t2(d int, e int, f int, primary key(d));
+CREATE TABLE
+testpub=# CREATE TABLE t3(g int, h int, i int, primary key(g));
+CREATE TABLE
+</programlisting></para>
+
+   <para>
+    Create some publications.
+<programlisting>
+testpub=# CREATE PUBLICATION p1 FOR TABLE t1 WHERE (a > 5 AND c = 'NSW');
+CREATE PUBLICATION
+testpub=# CREATE PUBLICATION p2 FOR TABLE t1, t2 WHERE (e = 99);
+CREATE PUBLICATION
+testpub=# CREATE PUBLICATION p3 FOR TABLE t2 WHERE (d = 10), t3 WHERE (g = 10);
+CREATE PUBLICATION
+</programlisting>
+    <itemizedlist>
+     <listitem>
+      <para>
+       Publication <literal>p1</literal> has one table (<literal>t1</literal>) and
+       that table has a row filter.
+      </para>
+     </listitem>
+     <listitem>
+      <para>
+       Publication <literal>p2</literal> has two tables. Table <literal>t1</literal>
+       has no row filter, and table <literal>t2</literal> has a row filter.
+      </para>
+     </listitem>
+     <listitem>
+      <para>
+       Publication <literal>p3</literal> has two tables, and both of them have a
+       row filter.
+      </para>
+     </listitem>
+    </itemizedlist></para>
+
+   <para>
+    The PSQL command <command>\dRp+</command> shows the row filter expressions
+    (if defined) for each table of the publications.
+<programlisting>
+testpub=# \dRp+
+                               Publication p1
+  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root 
+----------+------------+---------+---------+---------+-----------+----------
+ postgres | f          | t       | t       | t       | t         | f
+Tables:
+    "public.t1" WHERE ((a > 5) AND (c = 'NSW'::text))
+
+                               Publication p2
+  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root 
+----------+------------+---------+---------+---------+-----------+----------
+ postgres | f          | t       | t       | t       | t         | f
+Tables:
+    "public.t1"
+    "public.t2" WHERE (e = 99)
+
+                               Publication p3
+  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root 
+----------+------------+---------+---------+---------+-----------+----------
+ postgres | f          | t       | t       | t       | t         | f
+Tables:
+    "public.t2" WHERE (d = 10)
+    "public.t3" WHERE (g = 10)
+</programlisting></para>
+
+   <para>
+    The PSQL command <command>\d</command> shows what publications the table is
+    a member of, as well as that table's row filter expression (if defined) in
+    those publications.
+<programlisting>
+testpub=# \d t1
+                 Table "public.t1"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           | not null | 
+ b      | integer |           |          | 
+ c      | text    |           | not null | 
+Indexes:
+    "t1_pkey" PRIMARY KEY, btree (a, c)
+Publications:
+    "p1" WHERE ((a > 5) AND (c = 'NSW'::text))
+    "p2"
+
+testpub=# \d t2
+                 Table "public.t2"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ d      | integer |           | not null | 
+ e      | integer |           |          | 
+ f      | integer |           |          | 
+Indexes:
+    "t2_pkey" PRIMARY KEY, btree (d)
+Publications:
+    "p2" WHERE (e = 99)
+    "p3" WHERE (d = 10)
+
+testpub=# \d t3
+                 Table "public.t3"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ g      | integer |           | not null | 
+ h      | integer |           |          | 
+ i      | integer |           |          | 
+Indexes:
+    "t3_pkey" PRIMARY KEY, btree (g)
+Publications:
+    "p3" WHERE (g = 10)
+</programlisting>
+    <itemizedlist>
+     <listitem>
+      <para>
+       Table <literal>t1</literal> is a member of two publications, but
+       has a row filter only in <literal>p1</literal>.
+      </para>
+     </listitem>
+     <listitem>
+      <para>
+       Table <literal>t2</literal> is a member of two publications, and
+       has a different row filter in each of them.
+      </para>
+     </listitem>
+    </itemizedlist></para>
+
+   <para>
+    On the subscriber node, create a table <literal>t1</literal> with the same
+    definition as the one on the publisher, and also create the subscription
+    <literal>s1</literal> that subscribes to the publication <literal>p1</literal>.
+<programlisting>
+testsub=# CREATE TABLE t1(a int, b int, c text, primary key(a,c));
+CREATE TABLE
+testsub=# CREATE SUBSCRIPTION s1
+testsub-# CONNECTION 'host=localhost dbname=testpub application_name=s1'
+testsub-# PUBLICATION p1;
+CREATE SUBSCRIPTION
+</programlisting></para>
+
+   <para>
+    Insert some rows.
+<programlisting>
+testpub=# INSERT INTO t1 VALUES (2, 102, 'NSW');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (3, 103, 'QLD');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (4, 104, 'VIC');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (5, 105, 'ACT');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (6, 106, 'NSW');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (7, 107, 'NT');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (8, 108, 'QLD');
+INSERT 0 1
+testpub=# INSERT INTO t1 VALUES (9, 109, 'NSW');
+INSERT 0 1
+
+testpub=# SELECT * FROM t1; 
+ a |  b  |  c
+---+-----+-----
+ 2 | 102 | NSW
+ 3 | 103 | QLD
+ 4 | 104 | VIC
+ 5 | 105 | ACT
+ 6 | 106 | NSW
+ 7 | 107 | NT
+ 8 | 108 | QLD
+ 9 | 109 | NSW
+(8 rows)
+</programlisting>
+<programlisting>
+testsub=# SELECT * FROM t1;
+ a |  b  |  c  
+---+-----+-----
+ 6 | 106 | NSW
+ 9 | 109 | NSW
+(2 rows)
+</programlisting>
+    <itemizedlist>
+     <listitem>
+      <para>
+       Only the rows satisfying the <literal>t1 WHERE</literal>
+       clause of publication <literal>p1</literal> are replicated.
+      </para>
+     </listitem>
+    </itemizedlist></para>
+
+   <para>
+    Update some data, where the old and new row values both
+    satisfy the <literal>t1 WHERE</literal> clause of publication
+    <literal>p1</literal>.
+<programlisting>
+testpub=# UPDATE t1 SET b = 999 WHERE a = 6;
+UPDATE 1
+
+testpub=# SELECT * FROM t1;
+ a |  b  |  c  
+---+-----+-----
+ 2 | 102 | NSW
+ 3 | 103 | QLD
+ 4 | 104 | VIC
+ 5 | 105 | ACT
+ 7 | 107 | NT
+ 8 | 108 | QLD
+ 9 | 109 | NSW
+ 6 | 999 | NSW
+(8 rows)
+</programlisting>
+<programlisting>
+testsub=# SELECT * FROM t1;
+ a |  b  |  c  
+---+-----+-----
+ 9 | 109 | NSW
+ 6 | 999 | NSW
+(2 rows)
+</programlisting>
+    <itemizedlist>
+     <listitem>
+      <para>
+       The <command>UPDATE</command> replicates the change as normal.
+      </para>
+     </listitem>
+    </itemizedlist></para>
+
+   <para>
+    Update some data, where the old row values did not satisfy
+    the <literal>t1 WHERE</literal> clause of publication <literal>p1</literal>,
+    but the new row values do satisfy it.
+<programlisting>
+testpub=# UPDATE t1 SET a = 555 WHERE a = 2;
+UPDATE 1
+
+testpub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   3 | 103 | QLD
+   4 | 104 | VIC
+   5 | 105 | ACT
+   7 | 107 | NT
+   8 | 108 | QLD
+   9 | 109 | NSW
+   6 | 999 | NSW
+ 555 | 102 | NSW
+(8 rows)
+</programlisting>
+<programlisting>
+testsub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   9 | 109 | NSW
+   6 | 999 | NSW
+ 555 | 102 | NSW
+(3 rows)
+</programlisting>
+    <itemizedlist>
+     <listitem>
+      <para>
+       The <command>UPDATE</command> is transformed into an <command>INSERT</command>
+       and the change is replicated.
+      </para>
+     </listitem>
+    </itemizedlist></para>
+
+   <para>
+    Update some data, where the old row values satisfied
+    the <literal>t1 WHERE</literal> clause of publication <literal>p1</literal>,
+    but the new row values do not satisfy it.
+<programlisting>
+testpub=# UPDATE t1 SET c = 'VIC' WHERE a = 9;
+UPDATE 1
+
+testpub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   3 | 103 | QLD
+   4 | 104 | VIC
+   5 | 105 | ACT
+   7 | 107 | NT
+   8 | 108 | QLD
+   6 | 999 | NSW
+ 555 | 102 | NSW
+   9 | 109 | VIC
+(8 rows)
+</programlisting>
+<programlisting>
+testsub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   6 | 999 | NSW
+ 555 | 102 | NSW
+(2 rows)
+</programlisting>
+    <itemizedlist>
+     <listitem>
+      <para>
+       The <command>UPDATE</command> is transformed into a <command>DELETE</command>
+       and the change is replicated.
+      </para>
+     </listitem>
+    </itemizedlist></para>
+
+   <para>
+    The following examples show how the publication parameter
+    <literal>publish_via_partition_root</literal> determines whether the row
+    filter of the parent or child table will be used in the case of partitioned
+    tables.
+   </para>
+
+   <para>
+    Create the same partitioned tables on the publisher and subscriber.
+<programlisting>
+testpub=# CREATE TABLE parent(a int primary key) PARTITION BY RANGE(a);
+CREATE TABLE
+testpub=# CREATE TABLE child PARTITION OF parent DEFAULT;
+CREATE TABLE
+</programlisting>
+<programlisting>
+testsub=# CREATE TABLE parent(a int primary key) PARTITION BY RANGE(a);
+CREATE TABLE
+testsub=# CREATE TABLE child PARTITION OF parent DEFAULT;
+CREATE TABLE
+</programlisting></para>
+
+   <para>
+    Create a publication <literal>p4</literal>, and then subscribe to it.
+<programlisting>
+testpub=# CREATE PUBLICATION p4 FOR TABLE parent WHERE (a &lt; 5), child WHERE (a >= 5)
+testpub-# WITH (publish_via_partition_root=true);
+CREATE PUBLICATION
+</programlisting>
+    <itemizedlist>
+     <listitem>
+      <para>
+       The publication parameter <literal>publish_via_partition_root</literal>
+       is set as true.
+      </para>
+     </listitem>
+     <listitem>
+      <para>
+       There are row filters defined on both the partitioned table
+       (<literal>parent</literal>), and on the partition (<literal>child</literal>).
+      </para>
+     </listitem>
+    </itemizedlist>
+<programlisting>
+testsub=# CREATE SUBSCRIPTION s4
+testsub-# CONNECTION 'host=localhost dbname=testpub application_name=s4'
+testsub-# PUBLICATION p4;
+CREATE SUBSCRIPTION
+</programlisting></para>
+
+   <para>
+    Insert some values directly into the <literal>parent</literal> and
+    <literal>child</literal> tables.
+<programlisting>
+test_pub=# INSERT INTO parent VALUES (2), (4), (6);
+INSERT 0 3
+test_pub=# INSERT INTO child VALUES (3), (5), (7);
+INSERT 0 3
+</programlisting>
+<programlisting>
+test_pub=# SELECT * FROM parent ORDER BY a;
+ a 
+---
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+(6 rows)
+</programlisting>
+<programlisting>
+test_sub=# SELECT * FROM parent ORDER BY a;
+ a 
+---
+ 2
+ 3
+ 4
+(3 rows)
+</programlisting>
+    <itemizedlist>
+     <listitem>
+      <para>
+       The row filter of <literal>parent</literal> was used (because
+       <literal>publish_via_partition_root</literal> was true).
+      </para>
+     </listitem>
+    </itemizedlist></para>
+
+   <para>
+    Repeat the same test, but with a different value for <literal>publish_via_partition_root</literal>.
+<programlisting>
+testpub=# DROP PUBLICATION p4;
+DROP PUBLICATION
+testpub=# CREATE PUBLICATION p4 FOR TABLE parent, child WHERE (a >= 5)
+testpub-# WITH (publish_via_partition_root=false);
+CREATE PUBLICATION
+</programlisting>
+    <itemizedlist>
+     <listitem>
+      <para>
+       The publication parameter <literal>publish_via_partition_root</literal>
+       is set as false.
+      </para>
+     </listitem>
+     <listitem>
+      <para>
+       A row filter is defined on the partition (<literal>child</literal>).
+      </para>
+     </listitem>
+    </itemizedlist>
+<programlisting>
+testsub=# ALTER SUBSCRIPTION s4 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+</programlisting></para>
+
+   <para>
+    Do the inserts same as before.
+<programlisting>
+testpub=# TRUNCATE parent;
+TRUNCATE TABLE
+testpub=# INSERT INTO parent VALUES (2), (4), (6);
+INSERT 0 3
+testpub=# INSERT INTO child VALUES (3), (5), (7);
+INSERT 0 3
+
+testpub=# SELECT * FROM parent ORDER BY a;
+ a 
+---
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+(6 rows)
+</programlisting>
+<programlisting>
+testsub=# SELECT * FROM child ORDER BY a;
+ a 
+---
+ 5
+ 6
+ 7
+(3 rows)
+</programlisting>
+    <itemizedlist>
+     <listitem>
+      <para>
+       The row filter of <literal>child</literal> was used (because
+       <literal>publish_via_partition_root</literal> was false).
+      </para>
+     </listitem>
+    </itemizedlist></para>
+
+  </sect2>
+
+ </sect1>
+
  <sect1 id="logical-replication-conflicts">
   <title>Conflicts</title>
 
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index fb2d013393..23d883c158 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -254,6 +254,8 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
    <literal>publish_via_partition_root</literal> determines if it uses the
    partition's row filter (if the parameter is false, the default) or the root
    partitioned table's row filter.
+   See <xref linkend="logical-replication-row-filter"/> for details about row
+   filters.
   </para>
 
   <para>
-- 
2.28.0.windows.1

From c3e79a600199941d761c4cdc27e0737285cb046e Mon Sep 17 00:00:00 2001
From: Peter Smith <peter.b.smith@fujitsu.com>
Date: Tue, 12 Apr 2022 17:46:45 +1000
Subject: [PATCH v9] Add additional documentation for row filters.

Commit 52e4f0cd47 added a feature to allow specifying row filters for
logical replication of tables. This patch adds detailed documentation on
that feature including examples to make it easier for users to understand.

Author: Peter Smith, Euler Taveira
Reviewed By: Greg Nancarrow, Aleksander Alekseev, Amit Kapila, Ajin Cherian, Alvaro Herrera
---
 doc/src/sgml/logical-replication.sgml    | 547 +++++++++++++++++++++++++++++++
 doc/src/sgml/ref/create_publication.sgml |   2 +
 2 files changed, 549 insertions(+)

diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index 555fbd7..c2441f6 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -118,6 +118,8 @@
    any combination of <command>INSERT</command>, <command>UPDATE</command>,
    <command>DELETE</command>, and <command>TRUNCATE</command>, similar to how triggers are fired by
    particular event types.  By default, all operation types are replicated.
+   (Row filters have no effect for <command>TRUNCATE</command>. See 
+   <xref linkend="logical-replication-row-filter"/>).
   </para>
 
   <para>
@@ -317,6 +319,551 @@
   </sect2>
  </sect1>
 
+ <sect1 id="logical-replication-row-filter">
+  <title>Row Filters</title>
+
+  <para>
+   By default, all data from all published tables will be replicated to the
+   appropriate subscribers. The replicated data can be reduced by using a 
+   <firstterm>row filter</firstterm>. A user might choose to use row filters
+   for behavioral, security or performance reasons. If a published table sets a
+   row filter, a row is replicated only if its data satisfies the row filter
+   expression. This allows a set of tables to be partially replicated. The row
+   filter is defined per table. Use a <literal>WHERE</literal> clause after the
+   table name for each published table that requires data to be filtered out.
+   The <literal>WHERE</literal> clause must be enclosed by parentheses. See
+   <xref linkend="sql-createpublication"/> for details.
+  </para>
+
+  <sect2 id="logical-replication-row-filter-rules">
+   <title>Row Filter Rules</title>
+
+   <para>
+    Row filters are applied <emphasis>before</emphasis> publishing the changes.
+    If the row filter evaluates to <literal>false</literal> or <literal>NULL</literal>
+    then the row is not replicated. The <literal>WHERE</literal> clause expression
+    is evaluated with the same role used for the replication connection (i.e.
+    the role specified in the <literal>CONNECTION</literal> clause of the
+    <xref linkend="sql-createsubscription"/>). Row filters have no effect for
+    <command>TRUNCATE</command> command.
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-restrictions">
+   <title>Expression Restrictions</title>
+
+   <para>
+    The <literal>WHERE</literal> clause allows only simple expressions. It
+    cannot contain user-defined functions, operators, types, and collations,
+    system column references or non-immutable built-in functions.
+   </para>
+
+   <para>
+    If a publication publishes <command>UPDATE</command> and/or
+    <command>DELETE</command> operations, the row filter <literal>WHERE</literal> clause
+    must contain only columns that are covered by the replica identity (see
+    <xref linkend="sql-altertable-replica-identity"/>). If a publication publishes only
+    <command>INSERT</command>, the row filter <literal>WHERE</literal> clause
+    can use any column.
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-transformations">
+   <title>UPDATE Transformations</title>
+
+   <para>
+    Whenever an <command>UPDATE</command> is processed, the row filter
+    expression is evaluated for both the old and new row (i.e. using the data
+    before and after the update). If both evaluations are <literal>true</literal>,
+    it replicates the <command>UPDATE</command> change. If both evaluations are
+    <literal>false</literal>, it doesn't replicate the change. If only one of
+    the old/new rows matches the row filter expression, the <command>UPDATE</command>
+    is transformed to <command>INSERT</command> or <command>DELETE</command>, to
+    avoid any data inconsistency. The row on the subscriber should reflect what
+    is defined by the row filter expression on the publisher.
+   </para>
+
+   <para>
+    If the old row satisfies the row filter expression (it was sent to the
+    subscriber) but the new row doesn't, then, from a data consistency
+    perspective the old row should be removed from the subscriber.
+    So the <command>UPDATE</command> is transformed into a <command>DELETE</command>.
+   </para>
+
+   <para>
+    If the old row doesn't satisfy the row filter expression (it wasn't sent
+    to the subscriber) but the new row does, then, from a data consistency
+    perspective the new row should be added to the subscriber.
+    So the <command>UPDATE</command> is transformed into an <command>INSERT</command>.
+   </para>
+
+   <para>
+Summary:
+<synopsis>
+Old-row       New-row            Transformation
+(no match)    (no match)   -->   (drop change)
+(no match)    (match)      -->   INSERT
+(match)       (no match)   -->   DELETE
+(match)       (match)      -->   UPDATE
+</synopsis></para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-partitioned-table">
+   <title>Partitioned Tables</title>
+
+   <para>
+    If the publication contains a partitioned table, the publication parameter
+    <literal>publish_via_partition_root</literal> determines which row filter
+    is used. If <literal>publish_via_partition_root</literal> is <literal>true</literal>,
+    the <emphasis>root partitioned table's</emphasis> row filter is used. Otherwise,
+    if <literal>publish_via_partition_root</literal> is <literal>false</literal>
+    (default), each <emphasis>partition's</emphasis> row filter is used.
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-initial-data-sync">
+   <title>Initial Data Synchronization</title>
+
+   <para>
+    If the subscription requires copying pre-existing table data
+    and a publication contains <literal>WHERE</literal> clauses, only data that
+    satisfies the row filter expressions is copied to the subscriber.
+   </para>
+
+   <para>
+    If the subscription has several publications in which a table has been
+    published with different <literal>WHERE</literal> clauses, rows that satisfy
+    <emphasis>any</emphasis> of the expressions will be copied. See
+    <xref linkend="logical-replication-row-filter-combining"/> for details.
+   </para>
+
+   <note>
+    <para>
+     If the subscriber is in a release prior to 15, copy pre-existing data
+     doesn't use row filters even if they are defined in the publication.
+     This is because old releases can only copy the entire table data.
+    </para>
+   </note>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-combining">
+   <title>Combining Multiple Row Filters</title>
+
+   <para>
+    If the subscription has several publications in which the same table has
+    been published with different row filters (for the same <literal>publish</literal>
+    operation), those expressions get OR'ed together, so that rows satisfying
+    <emphasis>any</emphasis> of the expressions will be replicated. This means all
+    the other row filters for the same table become redundant if:
+    <itemizedlist>
+     <listitem>
+      <para>
+       one of the publications has no row filter.
+      </para>
+     </listitem>
+     <listitem>
+      <para>
+       one of the publications was created using <literal>FOR ALL TABLES</literal>.
+       This clause does not allow row filters.
+      </para>
+     </listitem>
+     <listitem>
+      <para>
+       one of the publications was created using
+       <literal>FOR ALL TABLES IN SCHEMA</literal> and the table belongs to
+       the referred schema. This clause does not allow row filters.
+      </para>
+     </listitem>
+    </itemizedlist></para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-examples">
+   <title>Examples</title>
+
+   <para>
+    Create some tables to be used in the following examples.
+<programlisting>
+test_pub=# CREATE TABLE t1(a int, b int, c text, primary key(a,c));
+CREATE TABLE
+test_pub=# CREATE TABLE t2(d int, e int, f int, primary key(d));
+CREATE TABLE
+test_pub=# CREATE TABLE t3(g int, h int, i int, primary key(g));
+CREATE TABLE
+</programlisting></para>
+
+   <para>
+    Create some publications. Publication <literal>p1</literal> has one table
+    (<literal>t1</literal>) and that table has a row filter. Publication
+    <literal>p2</literal> has two tables. Table <literal>t1</literal> has no row
+    filter, and table <literal>t2</literal> has a row filter. Publication
+    <literal>p3</literal> has two tables, and both of them have a row filter.
+<programlisting>
+test_pub=# CREATE PUBLICATION p1 FOR TABLE t1 WHERE (a > 5 AND c = 'NSW');
+CREATE PUBLICATION
+test_pub=# CREATE PUBLICATION p2 FOR TABLE t1, t2 WHERE (e = 99);
+CREATE PUBLICATION
+test_pub=# CREATE PUBLICATION p3 FOR TABLE t2 WHERE (d = 10), t3 WHERE (g = 10);
+CREATE PUBLICATION
+</programlisting></para>
+
+   <para>
+    The psql command <command>\dRp+</command> shows the row filter expressions
+    (if defined) for each of the publications.
+<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" WHERE ((a > 5) AND (c = 'NSW'::text))
+
+                               Publication p2
+  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root 
+----------+------------+---------+---------+---------+-----------+----------
+ postgres | f          | t       | t       | t       | t         | f
+Tables:
+    "public.t1"
+    "public.t2" WHERE (e = 99)
+
+                               Publication p3
+  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root 
+----------+------------+---------+---------+---------+-----------+----------
+ postgres | f          | t       | t       | t       | t         | f
+Tables:
+    "public.t2" WHERE (d = 10)
+    "public.t3" WHERE (g = 10)
+</programlisting></para>
+
+   <para>
+    The psql command <command>\d</command> shows what publications the table is
+    a member of, as well as that table's row filter expression (if defined) in
+    those publications. See that table <literal>t1</literal> is a member of two
+    publications, but has a row filter only in <literal>p1</literal>. See that
+    table <literal>t2</literal> is a member of two publications, and has a
+    different row filter in each of them.
+<programlisting>
+test_pub=# \d t1
+                 Table "public.t1"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           | not null | 
+ b      | integer |           |          | 
+ c      | text    |           | not null | 
+Indexes:
+    "t1_pkey" PRIMARY KEY, btree (a, c)
+Publications:
+    "p1" WHERE ((a > 5) AND (c = 'NSW'::text))
+    "p2"
+
+test_pub=# \d t2
+                 Table "public.t2"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ d      | integer |           | not null | 
+ e      | integer |           |          | 
+ f      | integer |           |          | 
+Indexes:
+    "t2_pkey" PRIMARY KEY, btree (d)
+Publications:
+    "p2" WHERE (e = 99)
+    "p3" WHERE (d = 10)
+
+test_pub=# \d t3
+                 Table "public.t3"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ g      | integer |           | not null | 
+ h      | integer |           |          | 
+ i      | integer |           |          | 
+Indexes:
+    "t3_pkey" PRIMARY KEY, btree (g)
+Publications:
+    "p3" WHERE (g = 10)
+</programlisting></para>
+
+   <para>
+    On the subscriber node, create a table <literal>t1</literal> with the same
+    definition as the one on the publisher, and also create the subscription
+    <literal>s1</literal> that subscribes to the publication <literal>p1</literal>.
+<programlisting>
+test_sub=# CREATE TABLE t1(a int, b int, c text, primary key(a,c));
+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. Only the rows satisfying the <literal>t1 WHERE</literal>
+    clause of publication <literal>p1</literal> are replicated.
+<programlisting>
+test_pub=# INSERT INTO t1 VALUES (2, 102, 'NSW');
+INSERT 0 1
+test_pub=# INSERT INTO t1 VALUES (3, 103, 'QLD');
+INSERT 0 1
+test_pub=# INSERT INTO t1 VALUES (4, 104, 'VIC');
+INSERT 0 1
+test_pub=# INSERT INTO t1 VALUES (5, 105, 'ACT');
+INSERT 0 1
+test_pub=# INSERT INTO t1 VALUES (6, 106, 'NSW');
+INSERT 0 1
+test_pub=# INSERT INTO t1 VALUES (7, 107, 'NT');
+INSERT 0 1
+test_pub=# INSERT INTO t1 VALUES (8, 108, 'QLD');
+INSERT 0 1
+test_pub=# INSERT INTO t1 VALUES (9, 109, 'NSW');
+INSERT 0 1
+
+test_pub=# SELECT * FROM t1; 
+ a |  b  |  c
+---+-----+-----
+ 2 | 102 | NSW
+ 3 | 103 | QLD
+ 4 | 104 | VIC
+ 5 | 105 | ACT
+ 6 | 106 | NSW
+ 7 | 107 | NT
+ 8 | 108 | QLD
+ 9 | 109 | NSW
+(8 rows)
+</programlisting>
+<programlisting>
+test_sub=# SELECT * FROM t1;
+ a |  b  |  c  
+---+-----+-----
+ 6 | 106 | NSW
+ 9 | 109 | NSW
+(2 rows)
+</programlisting></para>
+
+   <para>
+    Update some data, where the old and new row values both
+    satisfy the <literal>t1 WHERE</literal> clause of publication
+    <literal>p1</literal>. The <command>UPDATE</command> replicates
+    the change as normal.
+<programlisting>
+test_pub=# UPDATE t1 SET b = 999 WHERE a = 6;
+UPDATE 1
+
+test_pub=# SELECT * FROM t1;
+ a |  b  |  c  
+---+-----+-----
+ 2 | 102 | NSW
+ 3 | 103 | QLD
+ 4 | 104 | VIC
+ 5 | 105 | ACT
+ 7 | 107 | NT
+ 8 | 108 | QLD
+ 9 | 109 | NSW
+ 6 | 999 | NSW
+(8 rows)
+</programlisting>
+<programlisting>
+test_sub=# SELECT * FROM t1;
+ a |  b  |  c  
+---+-----+-----
+ 9 | 109 | NSW
+ 6 | 999 | NSW
+(2 rows)
+</programlisting></para>
+
+   <para>
+    Update some data, where the old row values did not satisfy
+    the <literal>t1 WHERE</literal> clause of publication <literal>p1</literal>,
+    but the new row values do satisfy it. The <command>UPDATE</command> is
+    transformed into an <command>INSERT</command> and the change is replicated.
+    See the new row on the subscriber.
+<programlisting>
+test_pub=# UPDATE t1 SET a = 555 WHERE a = 2;
+UPDATE 1
+
+test_pub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   3 | 103 | QLD
+   4 | 104 | VIC
+   5 | 105 | ACT
+   7 | 107 | NT
+   8 | 108 | QLD
+   9 | 109 | NSW
+   6 | 999 | NSW
+ 555 | 102 | NSW
+(8 rows)
+</programlisting>
+<programlisting>
+test_sub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   9 | 109 | NSW
+   6 | 999 | NSW
+ 555 | 102 | NSW
+(3 rows)
+</programlisting></para>
+
+   <para>
+    Update some data, where the old row values satisfied
+    the <literal>t1 WHERE</literal> clause of publication <literal>p1</literal>,
+    but the new row values do not satisfy it. The <command>UPDATE</command> is
+    transformed into a <command>DELETE</command> and the change is replicated.
+    See that the row is removed from the subscriber.
+<programlisting>
+test_pub=# UPDATE t1 SET c = 'VIC' WHERE a = 9;
+UPDATE 1
+
+test_pub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   3 | 103 | QLD
+   4 | 104 | VIC
+   5 | 105 | ACT
+   7 | 107 | NT
+   8 | 108 | QLD
+   6 | 999 | NSW
+ 555 | 102 | NSW
+   9 | 109 | VIC
+(8 rows)
+</programlisting>
+<programlisting>
+test_sub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   6 | 999 | NSW
+ 555 | 102 | NSW
+(2 rows)
+</programlisting></para>
+
+   <para>
+    The following examples show how the publication parameter
+    <literal>publish_via_partition_root</literal> determines whether the row
+    filter of the parent or child table will be used in the case of partitioned
+    tables.
+   </para>
+
+   <para>
+    Create the same partitioned tables on the publisher and subscriber.
+<programlisting>
+test_pub=# CREATE TABLE parent(a int primary key) PARTITION BY RANGE(a);
+CREATE TABLE
+test_pub=# CREATE TABLE child PARTITION OF parent DEFAULT;
+CREATE TABLE
+</programlisting>
+<programlisting>
+test_sub=# CREATE TABLE parent(a int primary key) PARTITION BY RANGE(a);
+CREATE TABLE
+test_sub=# CREATE TABLE child PARTITION OF parent DEFAULT;
+CREATE TABLE
+</programlisting></para>
+
+   <para>
+    Create a publication <literal>p4</literal>, and then subscribe to it. The
+    publication parameter <literal>publish_via_partition_root</literal> is set
+    as true. There are row filters defined on both the partitioned table
+    (<literal>parent</literal>), and on the partition (<literal>child</literal>).
+<programlisting>
+test_pub=# CREATE PUBLICATION p4 FOR TABLE parent WHERE (a &lt; 5), child WHERE (a >= 5)
+test_pub-# WITH (publish_via_partition_root=true);
+CREATE PUBLICATION
+</programlisting>
+<programlisting>
+test_sub=# CREATE SUBSCRIPTION s4
+test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=s4'
+test_sub-# PUBLICATION p4;
+CREATE SUBSCRIPTION
+</programlisting></para>
+
+   <para>
+    Insert some values directly into the <literal>parent</literal> and
+    <literal>child</literal> tables. They replicate using the row filter of
+    <literal>parent</literal> (because <literal>publish_via_partition_root</literal>
+    is true).
+<programlisting>
+test_pub=# INSERT INTO parent VALUES (2), (4), (6);
+INSERT 0 3
+test_pub=# INSERT INTO child VALUES (3), (5), (7);
+INSERT 0 3
+
+test_pub=# SELECT * FROM parent ORDER BY a;
+ a 
+---
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+(6 rows)
+</programlisting>
+<programlisting>
+test_sub=# SELECT * FROM parent ORDER BY a;
+ a 
+---
+ 2
+ 3
+ 4
+(3 rows)
+</programlisting></para>
+
+   <para>
+    Repeat the same test, but with a different value for <literal>publish_via_partition_root</literal>.
+    The publication parameter <literal>publish_via_partition_root</literal> is
+    set as false. A row filter is defined on the partition (<literal>child</literal>).
+<programlisting>
+test_pub=# DROP PUBLICATION p4;
+DROP PUBLICATION
+test_pub=# CREATE PUBLICATION p4 FOR TABLE parent, child WHERE (a >= 5)
+test_pub-# WITH (publish_via_partition_root=false);
+CREATE PUBLICATION
+</programlisting>
+<programlisting>
+test_sub=# ALTER SUBSCRIPTION s4 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+</programlisting></para>
+
+   <para>
+    Do the inserts on the publisher same as before. They replicate using the
+    row filter of <literal>child</literal> (because
+    <literal>publish_via_partition_root</literal> is false).
+<programlisting>
+test_pub=# TRUNCATE parent;
+TRUNCATE TABLE
+test_pub=# INSERT INTO parent VALUES (2), (4), (6);
+INSERT 0 3
+test_pub=# INSERT INTO child VALUES (3), (5), (7);
+INSERT 0 3
+
+test_pub=# SELECT * FROM parent ORDER BY a;
+ a 
+---
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+(6 rows)
+</programlisting>
+<programlisting>
+test_sub=# SELECT * FROM child ORDER BY a;
+ a 
+---
+ 5
+ 6
+ 7
+(3 rows)
+</programlisting></para>
+
+  </sect2>
+
+ </sect1>
+
  <sect1 id="logical-replication-conflicts">
   <title>Conflicts</title>
 
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index fb2d013..23d883c 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -254,6 +254,8 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
    <literal>publish_via_partition_root</literal> determines if it uses the
    partition's row filter (if the parameter is false, the default) or the root
    partitioned table's row filter.
+   See <xref linkend="logical-replication-row-filter"/> for details about row
+   filters.
   </para>
 
   <para>
-- 
1.8.3.1

From c78599c747b1e72cde5ca56da5eae481fd9f3bd8 Mon Sep 17 00:00:00 2001
From: Peter Smith <peter.b.smith@fujitsu.com>
Date: Wed, 13 Apr 2022 13:20:09 +1000
Subject: [PATCH v10] Add additional documentation for row filters.

Commit 52e4f0cd47 added a feature to allow specifying row filters for
logical replication of tables. This patch adds detailed documentation on
that feature including examples to make it easier for users to understand.

Author: Peter Smith, Euler Taveira
Reviewed By: Greg Nancarrow, Aleksander Alekseev, Amit Kapila, Ajin Cherian, Alvaro Herrera
---
 doc/src/sgml/logical-replication.sgml    | 562 +++++++++++++++++++++++++++++++
 doc/src/sgml/ref/create_publication.sgml |   2 +
 2 files changed, 564 insertions(+)

diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index 1126ce4..bc4d700 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -118,6 +118,8 @@
    any combination of <command>INSERT</command>, <command>UPDATE</command>,
    <command>DELETE</command>, and <command>TRUNCATE</command>, similar to how triggers are fired by
    particular event types.  By default, all operation types are replicated.
+   (Row filters have no effect for <command>TRUNCATE</command>. See 
+   <xref linkend="logical-replication-row-filter"/>).
   </para>
 
   <para>
@@ -317,6 +319,566 @@
   </sect2>
  </sect1>
 
+ <sect1 id="logical-replication-row-filter">
+  <title>Row Filters</title>
+
+  <para>
+   By default, all data from all published tables will be replicated to the
+   appropriate subscribers. The replicated data can be reduced by using a 
+   <firstterm>row filter</firstterm>. A user might choose to use row filters
+   for behavioral, security or performance reasons. If a published table sets a
+   row filter, a row is replicated only if its data satisfies the row filter
+   expression. This allows a set of tables to be partially replicated. The row
+   filter is defined per table. Use a <literal>WHERE</literal> clause after the
+   table name for each published table that requires data to be filtered out.
+   The <literal>WHERE</literal> clause must be enclosed by parentheses. See
+   <xref linkend="sql-createpublication"/> for details.
+  </para>
+
+  <sect2 id="logical-replication-row-filter-rules">
+   <title>Row Filter Rules</title>
+
+   <para>
+    Row filters are applied <emphasis>before</emphasis> publishing the changes.
+    If the row filter evaluates to <literal>false</literal> or <literal>NULL</literal>
+    then the row is not replicated. The <literal>WHERE</literal> clause expression
+    is evaluated with the same role used for the replication connection (i.e.
+    the role specified in the <literal>CONNECTION</literal> clause of the
+    <xref linkend="sql-createsubscription"/>). Row filters have no effect for
+    <command>TRUNCATE</command> command.
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-restrictions">
+   <title>Expression Restrictions</title>
+
+   <para>
+    The <literal>WHERE</literal> clause allows only simple expressions. It
+    cannot contain user-defined functions, operators, types, and collations,
+    system column references or non-immutable built-in functions.
+   </para>
+
+   <para>
+    If a publication publishes <command>UPDATE</command> or
+    <command>DELETE</command> operations, the row filter <literal>WHERE</literal>
+    clause must contain only columns that are covered by the replica identity
+    (see <xref linkend="sql-altertable-replica-identity"/>). If a publication
+    publishes only <command>INSERT</command> operations, the row filter
+    <literal>WHERE</literal> clause can use any column.
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-transformations">
+   <title>UPDATE Transformations</title>
+
+   <para>
+    Whenever an <command>UPDATE</command> is processed, the row filter
+    expression is evaluated for both the old and new row (i.e. using the data
+    before and after the update). If both evaluations are <literal>true</literal>,
+    it replicates the <command>UPDATE</command> change. If both evaluations are
+    <literal>false</literal>, it doesn't replicate the change. If only one of
+    the old/new rows matches the row filter expression, the <command>UPDATE</command>
+    is transformed to <command>INSERT</command> or <command>DELETE</command>, to
+    avoid any data inconsistency. The row on the subscriber should reflect what
+    is defined by the row filter expression on the publisher.
+   </para>
+
+   <para>
+    If the old row satisfies the row filter expression (it was sent to the
+    subscriber) but the new row doesn't, then, from a data consistency
+    perspective the old row should be removed from the subscriber.
+    So the <command>UPDATE</command> is transformed into a <command>DELETE</command>.
+   </para>
+
+   <para>
+    If the old row doesn't satisfy the row filter expression (it wasn't sent
+    to the subscriber) but the new row does, then, from a data consistency
+    perspective the new row should be added to the subscriber.
+    So the <command>UPDATE</command> is transformed into an <command>INSERT</command>.
+   </para>
+
+   <table>
+    <title><command>UPDATE</command> Transformation Summary</title>
+    <tgroup cols="3">
+    <thead>
+     <row>
+      <entry>Old row</entry><entry>New row</entry><entry>Transformation</entry>
+     </row>
+    </thead>
+    <tbody>
+     <row>
+      <entry>no match</entry><entry>no match</entry><entry>don't replicate</entry>
+     </row>
+     <row>
+      <entry>no match</entry><entry>match</entry><entry><literal>INSERT</literal></entry>
+     </row>
+     <row>
+      <entry>match</entry><entry>no match</entry><entry><literal>DELETE</literal></entry>
+     </row>
+     <row>
+      <entry>match</entry><entry>match</entry><entry><literal>UPDATE</literal></entry>
+     </row>
+    </tbody>
+   </tgroup>
+   </table>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-partitioned-table">
+   <title>Partitioned Tables</title>
+
+   <para>
+    If the publication contains a partitioned table, the publication parameter
+    <literal>publish_via_partition_root</literal> determines which row filter
+    is used. If <literal>publish_via_partition_root</literal> is <literal>true</literal>,
+    the <emphasis>root partitioned table's</emphasis> row filter is used. Otherwise,
+    if <literal>publish_via_partition_root</literal> is <literal>false</literal>
+    (default), each <emphasis>partition's</emphasis> row filter is used.
+   </para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-initial-data-sync">
+   <title>Initial Data Synchronization</title>
+
+   <para>
+    If the subscription requires copying pre-existing table data
+    and a publication contains <literal>WHERE</literal> clauses, only data that
+    satisfies the row filter expressions is copied to the subscriber.
+   </para>
+
+   <para>
+    If the subscription has several publications in which a table has been
+    published with different <literal>WHERE</literal> clauses, rows that satisfy
+    <emphasis>any</emphasis> of the expressions will be copied. See
+    <xref linkend="logical-replication-row-filter-combining"/> for details.
+   </para>
+
+   <note>
+    <para>
+     If the subscriber is in a release prior to 15, copy pre-existing data
+     doesn't use row filters even if they are defined in the publication.
+     This is because old releases can only copy the entire table data.
+    </para>
+   </note>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-combining">
+   <title>Combining Multiple Row Filters</title>
+
+   <para>
+    If the subscription has several publications in which the same table has
+    been published with different row filters (for the same <literal>publish</literal>
+    operation), those expressions get ORed together, so that rows satisfying
+    <emphasis>any</emphasis> of the expressions will be replicated. This means all
+    the other row filters for the same table become redundant if:
+    <itemizedlist>
+     <listitem>
+      <para>
+       one of the publications has no row filter.
+      </para>
+     </listitem>
+     <listitem>
+      <para>
+       one of the publications was created using <literal>FOR ALL TABLES</literal>.
+       This clause does not allow row filters.
+      </para>
+     </listitem>
+     <listitem>
+      <para>
+       one of the publications was created using
+       <literal>FOR ALL TABLES IN SCHEMA</literal> and the table belongs to
+       the referred schema. This clause does not allow row filters.
+      </para>
+     </listitem>
+    </itemizedlist></para>
+
+  </sect2>
+
+  <sect2 id="logical-replication-row-filter-examples">
+   <title>Examples</title>
+
+   <para>
+    Create some tables to be used in the following examples.
+<programlisting>
+test_pub=# CREATE TABLE t1(a int, b int, c text, PRIMARY KEY(a,c));
+CREATE TABLE
+test_pub=# CREATE TABLE t2(d int, e int, f int, PRIMARY KEY(d));
+CREATE TABLE
+test_pub=# CREATE TABLE t3(g int, h int, i int, PRIMARY KEY(g));
+CREATE TABLE
+</programlisting></para>
+
+   <para>
+    Create some publications. Publication <literal>p1</literal> has one table
+    (<literal>t1</literal>) and that table has a row filter. Publication
+    <literal>p2</literal> has two tables. Table <literal>t1</literal> has no row
+    filter, and table <literal>t2</literal> has a row filter. Publication
+    <literal>p3</literal> has two tables, and both of them have a row filter.
+<programlisting>
+test_pub=# CREATE PUBLICATION p1 FOR TABLE t1 WHERE (a > 5 AND c = 'NSW');
+CREATE PUBLICATION
+test_pub=# CREATE PUBLICATION p2 FOR TABLE t1, t2 WHERE (e = 99);
+CREATE PUBLICATION
+test_pub=# CREATE PUBLICATION p3 FOR TABLE t2 WHERE (d = 10), t3 WHERE (g = 10);
+CREATE PUBLICATION
+</programlisting></para>
+
+   <para>
+    <command>psql</command> can be used to show the row filter expressions (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" WHERE ((a > 5) AND (c = 'NSW'::text))
+
+                               Publication p2
+  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root 
+----------+------------+---------+---------+---------+-----------+----------
+ postgres | f          | t       | t       | t       | t         | f
+Tables:
+    "public.t1"
+    "public.t2" WHERE (e = 99)
+
+                               Publication p3
+  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root 
+----------+------------+---------+---------+---------+-----------+----------
+ postgres | f          | t       | t       | t       | t         | f
+Tables:
+    "public.t2" WHERE (d = 10)
+    "public.t3" WHERE (g = 10)
+</programlisting></para>
+
+   <para>
+    <command>psql</command> can be used to show the row filter expressions (if
+    defined) for each table. See that table <literal>t1</literal> is a member
+    of two publications, but has a row filter only in <literal>p1</literal>.
+    See that table <literal>t2</literal> is a member of two publications, and
+    has a different row filter in each of them.
+<programlisting>
+test_pub=# \d t1
+                 Table "public.t1"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           | not null | 
+ b      | integer |           |          | 
+ c      | text    |           | not null | 
+Indexes:
+    "t1_pkey" PRIMARY KEY, btree (a, c)
+Publications:
+    "p1" WHERE ((a > 5) AND (c = 'NSW'::text))
+    "p2"
+
+test_pub=# \d t2
+                 Table "public.t2"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ d      | integer |           | not null | 
+ e      | integer |           |          | 
+ f      | integer |           |          | 
+Indexes:
+    "t2_pkey" PRIMARY KEY, btree (d)
+Publications:
+    "p2" WHERE (e = 99)
+    "p3" WHERE (d = 10)
+
+test_pub=# \d t3
+                 Table "public.t3"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ g      | integer |           | not null | 
+ h      | integer |           |          | 
+ i      | integer |           |          | 
+Indexes:
+    "t3_pkey" PRIMARY KEY, btree (g)
+Publications:
+    "p3" WHERE (g = 10)
+</programlisting></para>
+
+   <para>
+    On the subscriber node, create a table <literal>t1</literal> with the same
+    definition as the one on the publisher, and also create the subscription
+    <literal>s1</literal> that subscribes to the publication <literal>p1</literal>.
+<programlisting>
+test_sub=# CREATE TABLE t1(a int, b int, c text, PRIMARY KEY(a,c));
+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. Only the rows satisfying the <literal>t1 WHERE</literal>
+    clause of publication <literal>p1</literal> are replicated.
+<programlisting>
+test_pub=# INSERT INTO t1 VALUES (2, 102, 'NSW');
+INSERT 0 1
+test_pub=# INSERT INTO t1 VALUES (3, 103, 'QLD');
+INSERT 0 1
+test_pub=# INSERT INTO t1 VALUES (4, 104, 'VIC');
+INSERT 0 1
+test_pub=# INSERT INTO t1 VALUES (5, 105, 'ACT');
+INSERT 0 1
+test_pub=# INSERT INTO t1 VALUES (6, 106, 'NSW');
+INSERT 0 1
+test_pub=# INSERT INTO t1 VALUES (7, 107, 'NT');
+INSERT 0 1
+test_pub=# INSERT INTO t1 VALUES (8, 108, 'QLD');
+INSERT 0 1
+test_pub=# INSERT INTO t1 VALUES (9, 109, 'NSW');
+INSERT 0 1
+
+test_pub=# SELECT * FROM t1; 
+ a |  b  |  c
+---+-----+-----
+ 2 | 102 | NSW
+ 3 | 103 | QLD
+ 4 | 104 | VIC
+ 5 | 105 | ACT
+ 6 | 106 | NSW
+ 7 | 107 | NT
+ 8 | 108 | QLD
+ 9 | 109 | NSW
+(8 rows)
+</programlisting>
+<programlisting>
+test_sub=# SELECT * FROM t1;
+ a |  b  |  c  
+---+-----+-----
+ 6 | 106 | NSW
+ 9 | 109 | NSW
+(2 rows)
+</programlisting></para>
+
+   <para>
+    Update some data, where the old and new row values both
+    satisfy the <literal>t1 WHERE</literal> clause of publication
+    <literal>p1</literal>. The <command>UPDATE</command> replicates
+    the change as normal.
+<programlisting>
+test_pub=# UPDATE t1 SET b = 999 WHERE a = 6;
+UPDATE 1
+
+test_pub=# SELECT * FROM t1;
+ a |  b  |  c  
+---+-----+-----
+ 2 | 102 | NSW
+ 3 | 103 | QLD
+ 4 | 104 | VIC
+ 5 | 105 | ACT
+ 7 | 107 | NT
+ 8 | 108 | QLD
+ 9 | 109 | NSW
+ 6 | 999 | NSW
+(8 rows)
+</programlisting>
+<programlisting>
+test_sub=# SELECT * FROM t1;
+ a |  b  |  c  
+---+-----+-----
+ 9 | 109 | NSW
+ 6 | 999 | NSW
+(2 rows)
+</programlisting></para>
+
+   <para>
+    Update some data, where the old row values did not satisfy
+    the <literal>t1 WHERE</literal> clause of publication <literal>p1</literal>,
+    but the new row values do satisfy it. The <command>UPDATE</command> is
+    transformed into an <command>INSERT</command> and the change is replicated.
+    See the new row on the subscriber.
+<programlisting>
+test_pub=# UPDATE t1 SET a = 555 WHERE a = 2;
+UPDATE 1
+
+test_pub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   3 | 103 | QLD
+   4 | 104 | VIC
+   5 | 105 | ACT
+   7 | 107 | NT
+   8 | 108 | QLD
+   9 | 109 | NSW
+   6 | 999 | NSW
+ 555 | 102 | NSW
+(8 rows)
+</programlisting>
+<programlisting>
+test_sub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   9 | 109 | NSW
+   6 | 999 | NSW
+ 555 | 102 | NSW
+(3 rows)
+</programlisting></para>
+
+   <para>
+    Update some data, where the old row values satisfied
+    the <literal>t1 WHERE</literal> clause of publication <literal>p1</literal>,
+    but the new row values do not satisfy it. The <command>UPDATE</command> is
+    transformed into a <command>DELETE</command> and the change is replicated.
+    See that the row is removed from the subscriber.
+<programlisting>
+test_pub=# UPDATE t1 SET c = 'VIC' WHERE a = 9;
+UPDATE 1
+
+test_pub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   3 | 103 | QLD
+   4 | 104 | VIC
+   5 | 105 | ACT
+   7 | 107 | NT
+   8 | 108 | QLD
+   6 | 999 | NSW
+ 555 | 102 | NSW
+   9 | 109 | VIC
+(8 rows)
+</programlisting>
+<programlisting>
+test_sub=# SELECT * FROM t1;
+  a  |  b  |  c  
+-----+-----+-----
+   6 | 999 | NSW
+ 555 | 102 | NSW
+(2 rows)
+</programlisting></para>
+
+   <para>
+    The following examples show how the publication parameter
+    <literal>publish_via_partition_root</literal> determines whether the row
+    filter of the parent or child table will be used in the case of partitioned
+    tables.
+   </para>
+
+   <para>
+    Create a partitioned table on the publisher.
+<programlisting>
+test_pub=# CREATE TABLE parent(a int PRIMARY KEY) PARTITION BY RANGE(a);
+CREATE TABLE
+test_pub=# CREATE TABLE child PARTITION OF parent DEFAULT;
+CREATE TABLE
+</programlisting>
+   Create the same tables on the subscriber.
+<programlisting>
+test_sub=# CREATE TABLE parent(a int PRIMARY KEY) PARTITION BY RANGE(a);
+CREATE TABLE
+test_sub=# CREATE TABLE child PARTITION OF parent DEFAULT;
+CREATE TABLE
+</programlisting></para>
+
+   <para>
+    Create a publication <literal>p4</literal>, and then subscribe to it. The
+    publication parameter <literal>publish_via_partition_root</literal> is set
+    as true. There are row filters defined on both the partitioned table
+    (<literal>parent</literal>), and on the partition (<literal>child</literal>).
+<programlisting>
+test_pub=# CREATE PUBLICATION p4 FOR TABLE parent WHERE (a &lt; 5), child WHERE (a >= 5)
+test_pub-# WITH (publish_via_partition_root=true);
+CREATE PUBLICATION
+</programlisting>
+<programlisting>
+test_sub=# CREATE SUBSCRIPTION s4
+test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=s4'
+test_sub-# PUBLICATION p4;
+CREATE SUBSCRIPTION
+</programlisting></para>
+
+   <para>
+    Insert some values directly into the <literal>parent</literal> and
+    <literal>child</literal> tables. They replicate using the row filter of
+    <literal>parent</literal> (because <literal>publish_via_partition_root</literal>
+    is true).
+<programlisting>
+test_pub=# INSERT INTO parent VALUES (2), (4), (6);
+INSERT 0 3
+test_pub=# INSERT INTO child VALUES (3), (5), (7);
+INSERT 0 3
+
+test_pub=# SELECT * FROM parent ORDER BY a;
+ a 
+---
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+(6 rows)
+</programlisting>
+<programlisting>
+test_sub=# SELECT * FROM parent ORDER BY a;
+ a 
+---
+ 2
+ 3
+ 4
+(3 rows)
+</programlisting></para>
+
+   <para>
+    Repeat the same test, but with a different value for <literal>publish_via_partition_root</literal>.
+    The publication parameter <literal>publish_via_partition_root</literal> is
+    set as false. A row filter is defined on the partition (<literal>child</literal>).
+<programlisting>
+test_pub=# DROP PUBLICATION p4;
+DROP PUBLICATION
+test_pub=# CREATE PUBLICATION p4 FOR TABLE parent, child WHERE (a >= 5)
+test_pub-# WITH (publish_via_partition_root=false);
+CREATE PUBLICATION
+</programlisting>
+<programlisting>
+test_sub=# ALTER SUBSCRIPTION s4 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+</programlisting></para>
+
+   <para>
+    Do the inserts on the publisher same as before. They replicate using the
+    row filter of <literal>child</literal> (because
+    <literal>publish_via_partition_root</literal> is false).
+<programlisting>
+test_pub=# TRUNCATE parent;
+TRUNCATE TABLE
+test_pub=# INSERT INTO parent VALUES (2), (4), (6);
+INSERT 0 3
+test_pub=# INSERT INTO child VALUES (3), (5), (7);
+INSERT 0 3
+
+test_pub=# SELECT * FROM parent ORDER BY a;
+ a 
+---
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+(6 rows)
+</programlisting>
+<programlisting>
+test_sub=# SELECT * FROM child ORDER BY a;
+ a 
+---
+ 5
+ 6
+ 7
+(3 rows)
+</programlisting></para>
+
+  </sect2>
+
+ </sect1>
+
  <sect1 id="logical-replication-conflicts">
   <title>Conflicts</title>
 
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index fb2d013..23d883c 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -254,6 +254,8 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
    <literal>publish_via_partition_root</literal> determines if it uses the
    partition's row filter (if the parameter is false, the default) or the root
    partitioned table's row filter.
+   See <xref linkend="logical-replication-row-filter"/> for details about row
+   filters.
   </para>
 
   <para>
-- 
1.8.3.1