Re: Transactions involving multiple postgres foreign servers

> On 27 Jul 2017, at 04:28, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> However, you would not
> be guaranteed that all of those commits or rollbacks happen at
> anything like the same time. So, you would have a sort of eventual
> consistency.

As far as I understand any solution that provides proper isolation for distributed
transactions in postgres will require distributed 2PC somewhere under the hood.
That is just consequence of parallelism that database allows — transactions can
abort due concurrent operations. So dichotomy is simple: either we need 2PC or
restrict write transactions to be physically serial.

In particular both Postgres-XL/XC and postgrespro multimaster are using 2PC to
commit distributed transaction.

Some years ago we created patches to implement transaction manager API and
that is just a way to inject consistent snapshots on different nodes, but atomic
commit itself is out of scope of TM API (hmm, may be it is better to call it snapshot
manager api?). That allows us to use it in quite different environments like fdw and
logical replication and both are using 2PC.

I want to submit TM API again during this release cycle along with implementation
for fdw. And I planned to base it on top of this patch. So I already rebased Masahiko’s
patch to current -master and started writing long list of nitpicks, but not finished yet.

Also I see the quite a big value in this patch even without tm/snapshots/whatever.
Right now fdw doesn’t guarantee neither isolation nor atomicity. And if one isn’t
doing cross-node analytical transactions it will be safe to live without isolation.
But living without atomicity means that some parts of data can be lost without simple
way to detect and fix that.

Stas Kelvich
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company