Fix the handling of the failover option in subscription commands.

Do not allow ALTER SUBSCRIPTION ... SET (failover = on|off) in a
transaction block as the changed failover option of the slot can't be
rolled back. For the same reason, we refrain from altering the replication
slot's failover property if the subscription is created with a valid
slot_name and create_slot=false.

Reprted-by: Kuroda Hayato
Author: Hou Zhijie
Reviewed-by: Shveta Malik, Bertrand Drouvot, Kuroda Hayato
Discussion: https://postgr.es/m/OS0PR01MB57165542B09DFA4943830BF294082@OS0PR01MB5716.jpnprd01.prod.outlook.com

3 files changed, 21 insertions, 10 deletions

diff --git a/doc/src/sgml/ref/alter_subscription.sgml b/doc/src/sgml/ref/alter_subscription.sgml
index 413ce68ce2c..a78c1c3a478 100644
--- a/doc/src/sgml/ref/alter_subscription.sgml
+++ b/doc/src/sgml/ref/alter_subscription.sgml
@@ -66,10 +66,11 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO <
   </para>
 
   <para>
-   Commands <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command> and
+   Commands <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command>,
    <command>ALTER SUBSCRIPTION ... {SET|ADD|DROP} PUBLICATION ...</command>
-   with <literal>refresh</literal> option as <literal>true</literal> cannot be
-   executed inside a transaction block.
+   with <literal>refresh</literal> option as <literal>true</literal> and
+   <command>ALTER SUBSCRIPTION ... SET (failover = on|off)</command>
+   cannot be executed inside a transaction block.
 
    These commands also cannot be executed when the subscription has
    <link linkend="sql-createsubscription-params-with-two-phase"><literal>two_phase</literal></link>
diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml
index 15794731bbb..740b7d94210 100644
--- a/doc/src/sgml/ref/create_subscription.sgml
+++ b/doc/src/sgml/ref/create_subscription.sgml
@@ -122,8 +122,7 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
           (You cannot combine setting <literal>connect</literal>
           to <literal>false</literal> with
           setting <literal>create_slot</literal>, <literal>enabled</literal>,
-          <literal>copy_data</literal>, or <literal>failover</literal> to
-          <literal>true</literal>.)
+          or <literal>copy_data</literal> to <literal>true</literal>.)
          </para>
 
          <para>
@@ -183,6 +182,21 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
           <xref linkend="logical-replication-subscription-examples-deferred-slot"/>
           for examples.
          </para>
+
+         <para>
+          When setting <literal>slot_name</literal> to a valid name and
+          <literal>create_slot</literal> to false, the
+          <literal>failover</literal> property value of the named slot may
+          differ from the counterpart <literal>failover</literal> parameter
+          specified in the subscription. Always ensure the slot property
+          <literal>failover</literal> matches the counterpart parameter of the
+          subscription and vice versa. 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 <literal>failover</literal>
+          option is disabled or could be disabled for sync even when the
+          subscription's <literal>failover</literal> option is enabled.
+         </para>
         </listitem>
        </varlistentry>
       </variablelist>
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index b99793e4148..671df4b60ef 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -1611,11 +1611,7 @@ CREATE DATABASE foo WITH TEMPLATE template0;
    dump can be restored without requiring network access to the remote
    servers.  It is then up to the user to reactivate the subscriptions in a
    suitable way.  If the involved hosts have changed, the connection
-   information might have to be changed.  If the subscription needs to
-   be enabled for
-   <link linkend="sql-createsubscription-params-with-failover"><literal>failover</literal></link>,
-   execute <link linkend="sql-altersubscription-params-set"><literal>ALTER SUBSCRIPTION ... SET (failover = true)</literal></link>
-   after the slot has been created.  It might also be appropriate to
+   information might have to be changed.  It might also be appropriate to
    truncate the target tables before initiating a new full table copy.  If users
    intend to copy initial data during refresh they must create the slot with
    <literal>two_phase = false</literal>.  After the initial sync, the