Re: Transactions involving multiple postgres foreign servers, take 2

On Fri, Jun 12, 2020 at 2:10 PM Masahiko Sawada
<masahiko.sawada@2ndquadrant.com> wrote:
>
> On Fri, 12 Jun 2020 at 15:37, Amit Kapila <amit.kapila16@gmail.com> wrote:
> >
> > > >
> > > > I think this is a corner case and it is better to simplify the state
> > > > recording of foreign transactions then to save a CLOG lookup.
> > > >
> > >
> > > The main usage of in-doubt flag is to distinguish between in-doubt
> > > transactions and other transactions that have their waiter (I call
> > > on-line transactions).
> > >
> >
> > Which are these other online transactions?  I had assumed that foreign
> > transaction resolver process is to resolve in-doubt transactions but
> > it seems it is also used for some other purpose which anyway was the
> > next question I had while reviewing other sections of docs but let's
> > clarify as it came up now.
>
> When a distributed transaction is committed by COMMIT command, the
> postgres backend process prepare all foreign transaction and commit
> the local transaction.
>

Does this mean that we will mark the xid as committed in CLOG of the
local server?  If so, why is this okay till we commit transactions in
all the foreign servers, what if we fail to commit on one of the
servers?

Few more comments on v22-0003-Documentation-update
--------------------------------------------------------------------------------------
1.
+          When <literal>disabled</literal> there can be risk of database
+          consistency among all servers that involved in the distributed
+          transaction when some foreign server crashes during committing the
+          distributed transaction.

Will it read better if rephrase above to something like: "When
<literal>disabled</literal> there can be a risk of database
consistency if one or more foreign servers crashes while committing
the distributed transaction."?

2.
+      <varlistentry
id="guc-foreign-transaction-resolution-rety-interval"
xreflabel="foreign_transaction_resolution_retry_interval">
+       <term><varname>foreign_transaction_resolution_retry_interval</varname>
(<type>integer</type>)
+        <indexterm>
+         <primary><varname>foreign_transaction_resolution_interval</varname>
configuration parameter</primary>
+        </indexterm>
+       </term>
+       <listitem>
+        <para>
+         Specify how long the foreign transaction resolver should
wait when the last resolution
+         fails before retrying to resolve foreign transaction. This
parameter can only be set in the
+         <filename>postgresql.conf</filename> file or on the server
command line.
+        </para>
+        <para>
+         The default value is 10 seconds.
+        </para>
+       </listitem>
+      </varlistentry>

Typo.  <varlistentry
id="guc-foreign-transaction-resolution-rety-interval", spelling of
retry is wrong.  Do we really need such a guc parameter?  I think we
can come up with some simple algorithm to retry after a few seconds
and then increase that interval of retry if we fail again or something
like that.  I don't know how users can come up with some non-default
value for this variable.

3
+      <varlistentry id="guc-foreign-transaction-resolver-timeout"
xreflabel="foreign_transaction_resolver_timeout">
+       <term><varname>foreign_transaction_resolver_timeout</varname>
(<type>integer</type>)
+        <indexterm>
+         <primary><varname>foreign_transaction_resolver_timeout</varname>
configuration parameter</primary>
+        </indexterm>
+       </term>
+       <listitem>
+        <para>
+         Terminate foreign transaction resolver processes that don't
have any foreign
+         transactions to resolve longer than the specified number of
milliseconds.
+         A value of zero disables the timeout mechanism, meaning it
connects to one
+         database until stopping manually.

Can we mention the function name using which one can stop the resolver process?

4.
+   Using the <productname>PostgreSQL</productname>'s atomic commit ensures that
+   all changes on foreign servers end in either commit or rollback using the
+   transaction callback routines

Can we slightly rephase this "Using the PostgreSQL's atomic commit
ensures that all the changes on foreign servers are either committed
or rolled back using the transaction callback routines"?

5.
+       Prepare all transactions on foreign servers.
+       <productname>PostgreSQL</productname> distributed transaction manager
+       prepares all transaction on the foreign servers if two-phase commit is
+       required. Two-phase commit is required when the transaction modifies
+       data on two or more servers including the local server itself and
+       <xref linkend="guc-foreign-twophase-commit"/> is
+       <literal>required</literal>.

/PostgreSQL/PostgreSQL's.

 If all preparations on foreign servers got
+       successful go to the next step.

How about "If the prepare on all foreign servers is successful then go
to the next step"?

 Any failure happens in this step,
+       the server changes to rollback, then rollback all transactions on both
+       local and foreign servers.

Can we rephrase this line to something like: "If there is any failure
in the prepare phase, the server will rollback all the transactions on
both local and foreign servers."?

What if the issued Rollback also failed, say due to network breakdown
between local and one of foreign servers?  Shouldn't such a
transaction be 'in-doubt' state?

6.
+      <para>
+       Commit locally. The server commits transaction locally.  Any
failure happens
+       in this step the server changes to rollback, then rollback all
transactions
+       on both local and foreign servers.
+      </para>
+     </listitem>
+     <listitem>
+      <para>
+       Resolve all prepared transaction on foreign servers. Pprepared
transactions
+       are committed or rolled back according to the result of the
local transaction.
+       This step is normally performed by a foreign transaction
resolver process.
+      </para>

When (in which step) do we commit on foreign servers?  Do Resolver
processes commit on foreign servers, if so, how can we commit locally
without committing on foreign servers, what if the commit on one of
the servers fails? It is not very clear to me from the steps mentioned
here?  Typo, /Pprepared/Prepared

7.
However, foreign transactions
+    become <firstterm>in-doubt</firstterm> in three cases: where the foreign
+    server crashed or lost the connectibility to it during preparing foreign
+    transaction, where the local node crashed during either preparing or
+    resolving foreign transaction and where user canceled the query.

Here the three cases are not very clear.  You might want to use (a)
..., (b) .. ,(c)..  Also, I think the state will be in-doubt even when
we lost connection to server during commit or rollback.

8.
+    One foreign transaction resolver is responsible for transaction resolutions
+    on which one database connecting.

Can we rephrase it to: "One foreign transaction resolver is
responsible for transaction resolutions on the database to which it is
connected."?

9.
+    Note that other <productname>PostgreSQL</productname> feature
such as parallel
+    queries, logical replication, etc., also take worker slots from
+    <varname>max_worker_processes</varname>.

/feature/features

10.
+   <para>
+    Atomic commit requires several configuration options to be set.
+    On the local node, <xref
linkend="guc-max-prepared-foreign-transactions"/> and
+    <xref linkend="guc-max-foreign-transaction-resolvers"/> must be
non-zero value.
+    Additionally the <varname>max_worker_processes</varname> may need
to be adjusted to
+    accommodate for foreign transaction resolver workers, at least
+    (<varname>max_foreign_transaction_resolvers</varname> +
<literal>1</literal>).
+    Note that other <productname>PostgreSQL</productname> feature
such as parallel
+    queries, logical replication, etc., also take worker slots from
+    <varname>max_worker_processes</varname>.
+   </para>

Don't we need to mention foreign_twophase_commit GUC here?

11.
+   <sect2 id="fdw-callbacks-transaction-managements">
+    <title>FDW Routines For Transaction Managements</title>

Managements/Management?

12.
+     Transaction management callbacks are used for doing commit, rollback and
+     prepare the foreign transaction.

Lets write the above sentence as: "Transaction management callbacks
are used to commit, rollback and prepare the foreign transaction."

13.
+    <para>
+     Transaction management callbacks are used for doing commit, rollback and
+     prepare the foreign transaction. If an FDW wishes that its foreign
+     transaction is managed by <productname>PostgreSQL</productname>'s global
+     transaction manager it must provide both
+     <function>CommitForeignTransaction</function> and
+     <function>RollbackForeignTransaction</function>. In addition, if an FDW
+     wishes to support <firstterm>atomic commit</firstterm> (as described in
+     <xref linkend="fdw-transaction-managements"/>), it must provide
+     <function>PrepareForeignTransaction</function> as well and can provide
+     <function>GetPrepareId</function> callback optionally.
+    </para>

What exact functionality a FDW can accomplish if it just supports
CommitForeignTransaction and RollbackForeignTransaction?  It seems it
doesn't care for 2PC, if so, is there any special functionality we can
achieve with this which we can't do without these APIs?

14.
+PrepareForeignTransaction(FdwXactRslvState *frstate);
+</programlisting>
+    Prepare the transaction on the foreign server. This function is
called at the
+    pre-commit phase of the local transactions if foreign twophase commit is
+    required. This function is used only for distribute transaction management
+    (see <xref linkend="distributed-transaction"/>).
+    </para>

/distribute/distributed

15.
+   <sect2 id="fdw-transaction-commit-rollback">
+    <title>Commit And Rollback Single Foreign Transaction</title>
+    <para>
+     The FDW callback function <literal>CommitForeignTransaction</literal>
+     and <literal>RollbackForeignTransaction</literal> can be used to commit
+     and rollback the foreign transaction. During transaction commit, the core
+     transaction manager calls
<literal>CommitForeignTransaction</literal> function
+     in the pre-commit phase and calls
+     <literal>RollbackForeignTransaction</literal> function in the
post-rollback
+     phase.
+    </para>

There is no reasoning mentioned as to why CommitForeignTransaction has
to be called in pre-commit phase and RollbackForeignTransaction in
post-rollback phase?  Basically why one in pre phase and other in post
phase?

16.
+       <entry>
+        <literal><function>pg_remove_foreign_xact(<parameter>transaction</parameter>
<type>xid</type>, <parameter>serverid</parameter> <type>oid</type>,
<parameter>userid</parameter> <type>oid</type>)</function></literal>
+       </entry>
+       <entry><type>void</type></entry>
+       <entry>
+        This function works the same as
<function>pg_resolve_foreign_xact</function>
+        except that this removes the foreign transcation entry
without resolution.
+       </entry>

Can we write why and when such a function can be used?  Typo,
/trasnaction/transaction

17.
+     <row>
+      <entry><literal>FdwXactResolutionLock</literal></entry>
+      <entry>Waiting to read or update information of foreign trasnaction
+       resolution.</entry>
+     </row>

/trasnaction/transaction


-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com