Re: Transactions involving multiple postgres foreign servers

>>
>> If we are successful in COMMITTING foreign transactions during
>> post-commit phase, COMMIT message will be returned after we have
>> committed all foreign transactions. But in case we can not reach a
>> foreign server, and request times out, we can not revert back our
>> decision that we are going to commit the transaction. That's my answer
>> to the timeout based heuristic.
>
> IIUC 2PC is the protocol that assumes that all of the foreign server live.

Do you have any references? Take a look at [1]. The first paragraph
itself mentions that 2PC can achieve its goals despite temporary
failures.

> In case we can not reach a foreign server during post-commit phase,
> basically the transaction and following transaction should stop until
> the crashed server revived.

I have repeatedly given reasons why this is not correct. You and Amit
seem to repeat this statement again and again in turns without giving
any concrete reasons about why this is so.

> This is the first place to implement 2PC
> for FDW, I think. The heuristically determination approach I mentioned
> is one of the optimization idea to avoid holding up transaction in
> case a foreign server crashed.
>
>> I don't see much point in holding up post-commit processing for a
>> non-responsive foreign server, which may not respond for days
>> together. Can you please elaborate a use case? Which commercial
>> transaction manager does that?
>
> For example, the client updates a data on foreign server and then
> commits. And the next transaction from the same client selects new
> data which was updated on previous transaction. In this case, because
> the first transaction is committed the second transaction should be
> able to see updated data, but it can see old data in your idea. Since
> these is obviously order between first transaction and second
> transaction I think that It's not problem of providing consistent
> view.

2PC doesn't guarantee this. For that you need other methods and
protocols. We have discussed this before. [2]

[1] https://en.wikipedia.org/wiki/Two-phase_commit_protocol
[2] https://www.postgresql.org/message-id/CAD21AoCTe1CFfA9g1uqETvLaJZfFH6QoPSDf-L3KZQ-CDZ7q8g%40mail.gmail.com
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company