Re: Transactions involving multiple postgres foreign servers, take 2

On Thu, 16 Jul 2020 at 13:53, tsunakawa.takay@fujitsu.com
<tsunakawa.takay@fujitsu.com> wrote:
>
> Hi Sawada san,
>
>
> I'm reviewing this patch series, and let me give some initial comments and questions.  I'm looking at this with a
hopethat this will be useful purely as a FDW enhancement for our new use cases, regardless of whether the FDW will be
usedfor Postgres scale-out. 

Thank you for reviewing this patch!

Yes, this patch is trying to resolve the generic atomic commit problem
w.r.t. FDW, and will be useful also for Postgres scale-out.

>
> I don't think it's necessarily required to combine 2PC with the global visibility.  X/Open XA specification only
handlesthe atomic commit.  The only part in the XA specification that refers to global visibility is the following: 
>
>
> [Quote from XA specification]
> --------------------------------------------------
> 2.3.2 Protocol Optimisations
> ・ Read-only
> An RM can respond to the TM’s prepare request by asserting that the RM was not
> asked to update shared resources in this transaction branch. This response
> concludes the RM’s involvement in the transaction; the Phase 2 dialogue between
> the TM and this RM does not occur. The TM need not stably record, in its list of
> participating RMs, an RM that asserts a read-only role in the global transaction.
>
> However, if the RM returns the read-only optimisation before all work on the global
> transaction is prepared, global serialisability1 cannot be guaranteed. This is because
> the RM may release transaction context, such as read locks, before all application
> activity for that global transaction is finished.
>
> 1.
> Serialisability is a property of a set of concurrent transactions. For a serialisable set of transactions, at least
one
> serial sequence of the transactions exists that produces identical results, with respect to shared resources, as does
> concurrent execution of the transaction.
> --------------------------------------------------
>

Agreed.

>
> (1)
> Do other popular DBMSs (Oracle, MySQL, etc.)  provide concrete functions that can be used for the new FDW
commit/rollback/prepareAPI?  I'm asking this to confirm that we really need to provide these functions, not as the
transactioncallbacks for postgres_fdw. 
>

I have briefly checked the only oracle_fdw but in general I think that
if an existing FDW supports transaction begin, commit, and rollback,
these can be ported to new FDW transaction APIs easily.

Regarding the comparison between FDW transaction APIs and transaction
callbacks, I think one of the benefits of providing FDW transaction
APIs is that the core is able to manage the status of foreign
transactions. We need to track the status of individual foreign
transactions to support atomic commit. If we use transaction callbacks
(XactCallback) that many FDWs are using, I think we will end up
calling the transaction callback and leave the transaction work to
FDWs, leading that the core is not able to know the return values of
PREPARE TRANSACTION for example. We can add more arguments passed to
transaction callbacks to get the return value from FDWs but I don’t
think it’s a good idea as transaction callbacks are used not only by
FDW but also other external modules.

>
> (2)
> How are data modifications tracked in local and remote transactions?  0001 seems to handle local
INSERT/DELETE/UPDATE. Especially: 
>
> * COPY FROM to local/remote tables/views.
>
> * User-defined function calls that modify data, e.g. SELECT func1() WHERE col = func2()
>

With the current version patch (v23), it supports only
INSERT/DELETE/UPDATE. But I'm going to change the patch so that it
supports other writes SQLs as Fujii-san also pointed out.

>
> (3)
> Does the 2PC processing always go through the background worker?
> Is the group commit effective on the remote server? That is, PREPARE and COMMIT PREPARED issued from multiple remote
sessionsare written to WAL in batch? 

No, in the current design, the backend who received a query from the
client does PREPARE, and then the transaction resolver process, a
background worker, does COMMIT PREPARED.

Regards,

--
Masahiko Sawada            http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services