Re: Transactions involving multiple postgres foreign servers

On Thu, Jul 27, 2017 at 8:02 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, Jul 27, 2017 at 5:08 AM, Stas Kelvich <s(dot)kelvich(at)postgrespro(dot)ru> wrote:
>> 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.
>
> Ah, OK. I was imagining that a transaction manager might be
> responsible for managing both snapshots and distributed commit. But
> if the transaction manager only handles the snapshots (how?) and the
> commit has to be done using 2PC, then we need this.

One way to provide snapshots to participant nodes is giving a snapshot
data to them using libpq protocol with the query when coordinator
nodes starts transaction on a remote node (or we now can use exporting
snapshot infrastructure?). IIUC Postgres-XL/XC uses this approach.
That also requires to share the same XID space with all remote nodes.
Perhaps the CSN based snapshot can make this more simple.

>> 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.
>
> OK, thanks for weighing in.
>

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center