>> Also, as previously mentioned, it must behave in some reasonable >> way if a database is not configured to support 2PC, especially >> since 2PC is off by default in PostgreSQL.
> We can have a per foreign server option, which says whether the > corresponding server is able to participate in 2PC. A transaction > spanning multiple foreign server with at least one of them not > capable of participating in 2PC will be aborted. > > Will that work? > > In case a user flags a foreign server as capable to 2PC > incorrectly, I expect the corresponding FDW would raise error > (either because PREPARE fails or FDW doesn't handle that case) > and the transaction will be aborted anyway.
That sounds like one way to handle it. I'm not clear on how you plan to determine whether 2PC is required for a transaction. (Apologies if it was previously mentioned and I've forgotten it.)
Any transaction involving more than one server (including local one, I guess), will require two PC. A transaction may modify and access remote database but not local one. In such a case, the state of local transaction doesn't matter once the remote transaction is committed or rolled back.
I don't mean to suggest that these problems are insurmountable; I just think that people often underestimate the difficulty of writing a distributed transaction manager and don't always recognize the problems that it will cause if all of the failure modes are not considered and handled.