RE: Transactions involving multiple postgres foreign servers, take 2

From: Masahiko Sawada <masahiko(dot)sawada(at)2ndquadrant(dot)com>
> What about temporary network failures? I think there are users who
> don't want to give up resolving foreign transactions failed due to a
> temporary network failure. Or even they might want to wait for
> transaction completion until they send a cancel request. If we want to
> call the commit routine only once and therefore want FDW to retry
> connecting the foreign server within the call, it means we require all
> FDW implementors to write a retry loop code that is interruptible and
> ensures not to raise an error, which increases difficulty.
>
> Yes, but if we don’t retry to resolve foreign transactions at all on
> an unreliable network environment, the user might end up requiring
> every transaction to check the status of foreign transactions of the
> previous distributed transaction before starts. If we allow to do
> retry, I guess we ease that somewhat.

OK. As I said, I'm not against trying to cope with temporary network failure. I just don't think it's mandatory. If the network failure is really temporary and thus recovers soon, then the resolver will be able to commit the transaction soon, too.

Then, we can have a commit retry timeout or retry count like the following WebLogic manual says. (I couldn't quickly find the English manual, so below is in Japanese. I quoted some text that got through machine translation, which appears a bit strange.)

https://docs.oracle.com/cd/E92951_01/wls/WLJTA/trxcon.htm
--------------------------------------------------
Abandon timeout
Specifies the maximum time (in seconds) that the transaction manager attempts to complete the second phase of a two-phase commit transaction.

In the second phase of a two-phase commit transaction, the transaction manager attempts to complete the transaction until all resource managers indicate that the transaction is complete. After the abort transaction timer expires, no attempt is made to resolve the transaction. If the transaction enters a ready state before it is destroyed, the transaction manager rolls back the transaction and releases the held lock on behalf of the destroyed transaction.
--------------------------------------------------

> Also, what if the user sets the statement timeout to 60 sec and they
> want to cancel the waits after 5 sec by pressing ctl-C? You mentioned
> that client libraries of other DBMSs don't have asynchronous execution
> functionality. If the SQL execution function is not interruptible, the
> user will end up waiting for 60 sec, which seems not good.

FDW functions can be uninterruptible in general, aren't they? We experienced that odbc_fdw didn't allow cancellation of SQL execution.

Regards
Takayuki Tsunakawa