> I don't see why would my patch cause inconsistencies. It can > cause dangling PREPAREd transactions and I have already > acknowledged that fact. > > Am I missing something?
To me that is the big problem. Where I have run into ad hoc distributed transaction managers it has usually been because a crash left prepared transactions dangling, without cleaning them up when the transaction manager was restarted. This tends to wreak havoc one way or another.
If we are going to include a distributed transaction manager with PostgreSQL, it *must* persist enough information about the transaction ID and where it is used in a way that will survive a subsequent crash before beginning the PREPARE on any of the systems.
Thanks a lot. I hadn't thought of this.
After all nodes are PREPAREd it must flag that persisted data to indicate that it is now at a point where ROLLBACK is no longer an option. Only then can it start committing the prepared transactions. After the last node is committed it can clear this information. On start-up the distributed transaction manager must check for any distributed transactions left "in progress" and commit or rollback based on the preceding; doing retries indefinitely until it succeeds or is told to stop.
Agreed.
Doing this incompletely (i.e., not identifying and correctly handling the various failure modes) is IMO far worse than not attempting it. If we could build in something that did this completely and well, that would be a cool selling point; but let's not gloss over the difficulties. We must recognize how big a problem it would be to include a low-quality implementation.
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.
I described one possibility in my reply to Tom's mail. Let me repeat it here.
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.
