Home • Explore • Blog • CI
☰



postgresql
11th chunk of `doc/src/sgml/logical-replication.sgml`
027fa005387ecb2e34b47fd76e6e211f6f528d0522e4ce5f0000000100000fa3

   <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
    <link linkend="sql-createsubscription-params-connection"><literal>CONNECTION</literal></link>
    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>

   <para>
    <xref linkend="logical-replication-row-filter-transformations-summary"/>
    summarizes the applied transformations.
   </para>

   <table id="logical-replication-row-filter-transformations-summary">
    <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
Title: Detailed Behavior and Restrictions of Row Filters in Logical Replication
Summary
This section details the rules and restrictions surrounding row filters in logical replication. Row filters, applied before publishing changes, use the role of the replication connection for evaluation. They don't affect TRUNCATE commands. The WHERE clause is restricted to simple expressions, excluding user-defined elements and non-immutable functions. If publishing UPDATE or DELETE operations, the filter must use columns covered by the replica identity; INSERT operations allow any column. UPDATE operations are transformed into INSERT or DELETE operations when only one of the old/new rows matches the filter, ensuring data consistency on the subscriber. A table summarizes the transformations applied to UPDATE operations based on whether the old and new rows match the filter.