Home • Explore • Blog • CI
☰



postgresql
5th chunk of `doc/src/sgml/ref/alter_subscription.sgml`
ff06f42b06f5ec0d2c75ae222dada776206d5c75323af33c0000000100000cd6
 linkend="sql-createsubscription-params-with-slot-name"><literal>slot_name</literal></link>,
      the <literal>failover</literal> and <literal>two_phase</literal> property
      values of the named slot may differ from the counterpart
      <link linkend="sql-createsubscription-params-with-failover"><literal>failover</literal></link>
      and <link linkend="sql-createsubscription-params-with-two-phase"><literal>two_phase</literal></link>
      parameters specified in the subscription. When creating the slot, ensure
      the slot properties <literal>failover</literal> and <literal>two_phase</literal>
      match their counterpart parameters of the subscription.
      Otherwise, the slot on the publisher may behave differently from what these
      subscription options say: for example, the slot on the publisher could either be
      synced to the standbys even when the subscription's
      <link linkend="sql-createsubscription-params-with-failover"><literal>failover</literal></link>
      option is disabled or could be disabled for sync
      even when the subscription's
      <link linkend="sql-createsubscription-params-with-failover"><literal>failover</literal></link>
      option is enabled.
     </para>

     <para>
      The <link linkend="sql-createsubscription-params-with-failover"><literal>failover</literal></link>
      and <link linkend="sql-createsubscription-params-with-two-phase"><literal>two_phase</literal></link>
      parameters can only be altered when the subscription is disabled.
     </para>

     <para>
      When altering <link linkend="sql-createsubscription-params-with-two-phase"><literal>two_phase</literal></link>
      from <literal>true</literal> to <literal>false</literal>, the backend
      process reports an error if any prepared transactions done by the
      logical replication worker (from when <literal>two_phase</literal>
      parameter was still <literal>true</literal>) are found. You can resolve
      prepared transactions on the publisher node, or manually roll back them
      on the subscriber, and then try again. The transactions prepared by
      logical replication worker corresponding to a particular subscription have
      the following pattern: <quote><literal>pg_gid_%u_%u</literal></quote>
      (parameters: subscription <parameter>oid</parameter>, remote transaction id <parameter>xid</parameter>).
      To resolve such transactions manually, you need to roll back all
      the prepared transactions with corresponding subscription IDs in their
      names. Applications can check
      <link linkend="view-pg-prepared-xacts"><structname>pg_prepared_xacts</structname></link>
      to find the required prepared transactions. After the <literal>two_phase</literal>
      option is changed from <literal>true</literal> to <literal>false</literal>,
      the publisher will replicate the transactions again when they are committed.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-altersubscription-params-skip">
    <term><literal>SKIP ( <replaceable class="parameter">skip_option</replaceable> = <replaceable class="parameter">value</replaceable> )</literal></term>
    <listitem>
     <para>
      Skips applying all changes of the remote transaction.  If incoming data
Title: ALTER SUBSCRIPTION Parameters: Failover, Two-Phase Commit, and Skipping Transactions
Summary
This section details specific considerations when altering the 'failover' and 'two_phase' parameters of a subscription. It emphasizes the importance of matching the 'failover' and 'two_phase' properties of the replication slot on the publisher with the corresponding subscription parameters. It also explains that changing 'two_phase' from true to false requires resolving or rolling back any prepared transactions by the logical replication worker, providing a pattern for identifying these transactions and guidance on how to resolve them manually. Lastly, it introduces the SKIP clause, used to skip applying changes from a remote transaction.