RE: Transactions involving multiple postgres foreign servers, take 2

From: Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com>
> I don't understand why we hate ERRORs from fdw-2pc-commit routine so
> much. I think remote-commits should be performed before local commit
> passes the point-of-no-return and the v26-0002 actually places
> AtEOXact_FdwXact() before the critical section.

I don't hate ERROR, but it would be simpler and understandable for the FDW commit routine to just return control to the caller (TM) and let TM do whatever is appropriate (asks the resolver to handle the failed commit, and continues to request next FDW to commit.)

> > 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.
> > --------------------------------------------------
>
> That's not a retry timeout but a timeout for total time of all
> 2nd-phase-commits. But I think it would be sufficient. Even if an
> fdw could retry 2pc-commit, it's a matter of that fdw and the core has
> nothing to do with.

Yeah, the WebLogic documentation doesn't say whether it performs retries during the timeout period. I just cited as an example that has a timeout parameter for the second phase of 2PC.

> At least postgres_fdw is interruptible while waiting the remote.
>
> create view lt as select 1 as slp from (select pg_sleep(10)) t;
> create foreign table ft(slp int) server sv1 options (table_name 'lt');
> select * from ft;
> ^CCancel request sent
> ERROR: canceling statement due to user request

I'm afraid the cancellation doesn't work while postgres_fdw is trying to connect to a down server. Also, Postgres manual doesn't say about cancellation, so we cannot expect FDWs to respond to user's cancel request.

Regards
Takayuki Tsunakawa