RE: Transactions involving multiple postgres foreign servers, take 2

Sorry to be late to respond. (My PC is behaving strangely after upgrading Win10 2004)

From: Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
> After more thoughts on Tsunakawa-san’s idea it seems to need the
> following conditions:
>
> * At least postgres_fdw is viable to implement these APIs while
> guaranteeing not to happen any error.
> * A certain number of FDWs (or majority of FDWs) can do that in a
> similar way to postgres_fdw by using the guideline and probably
> postgres_fdw as a reference.
>
> These are necessary for FDW implementors to implement APIs while
> following the guideline and for the core to trust them.
>
> As far as postgres_fdw goes, what we need to do when committing a
> foreign transaction resolution is to get a connection from the
> connection cache or create and connect if not found, construct a SQL
> query (COMMIT/ROLLBACK PREPARED with identifier) using a fixed-size
> buffer, send the query, and get the result. The possible place to
> raise an error is limited. In case of failures such as connection
> error FDW can return false to the core along with a flag indicating to
> ask the core retry. Then the core will retry to resolve foreign
> transactions after some sleep. OTOH if FDW sized up that there is no
> hope of resolving the foreign transaction, it also could return false
> to the core along with another flag indicating to remove the entry and
> not to retry. Also, the transaction resolution by FDW needs to be
> cancellable (interruptible) but cannot use CHECK_FOR_INTERRUPTS().
>
> Probably, as Tsunakawa-san also suggested, it’s not impossible to
> implement these APIs in postgres_fdw while guaranteeing not to happen
> any error, although not sure the code complexity. So I think the first
> condition may be true but not sure about the second assumption,
> particularly about the interruptible part.

Yeah, I expect the commit of the second phase should not be difficult for the FDW developer.

As for the cancellation during commit retry, I don't think we necessarily have to make the TM responsible for retrying the commits. Many DBMSs have their own timeout functionality such as connection timeout, socket timeout, and statement timeout. Users can set those parameters in the foreign server options based on how long the end user can wait. That is, TM calls FDW's commit routine just once.

If the TM makes efforts to retry commits, the duration would be from a few seconds to 30 seconds. Then, we can hold back the cancellation during that period.

> I thought we could support both ideas to get their pros; supporting
> Tsunakawa-san's idea and then my idea if necessary, and FDW can choose
> whether to ask the resolver process to perform 2nd phase of 2PC or
> not. But it's not a good idea in terms of complexity.

I don't feel the need for leaving the commit to the resolver during normal operation.

seems like if failed to resolve, the backend would return an
> acknowledgment of COMMIT to the client and the resolver process
> resolves foreign prepared transactions in the background. So we can
> ensure that the distributed transaction is completed at the time when
> the client got an acknowledgment of COMMIT if 2nd phase of 2PC is
> successfully completed in the first attempts. OTOH, if it failed for
> whatever reason, there is no such guarantee. From an optimistic
> perspective, i.g., the failures are unlikely to happen, it will work
> well but IMO it’s not uncommon to fail to resolve foreign transactions
> due to network issue, especially in an unreliable network environment
> for example geo-distributed database. So I think it will end up
> requiring the client to check if preceding distributed transactions
> are completed or not in order to see the results of these
> transactions.

That issue exists with any method, doesn't it?

Regards
Takayuki Tsunakawa