Re: Transactions involving multiple postgres foreign servers

On 2016/12/05 14:42, Ashutosh Bapat wrote:
> On Mon, Dec 5, 2016 at 11:04 AM, Haribabu Kommi
> <kommi(dot)haribabu(at)gmail(dot)com> wrote:
>
>
> On Fri, Nov 11, 2016 at 5:38 PM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
> wrote:
>>>
>>> 2PC is a basic building block to support the atomic commit and there
>>> are some optimizations way in order to reduce disadvantage of 2PC. As
>>> you mentioned, it's hard to support a single model that would suit
>>> several type of FDWs. But even if it's not a purpose for sharding,
>>> because many other database which could be connected to PostgreSQL via
>>> FDW supports 2PC, 2PC for FDW would be useful for not only sharding
>>> purpose. That's why I was focusing on implementing 2PC for FDW so far.
>>
>> Moved to next CF with "needs review" status.
> I think this should be changed to "returned with feedback.". The
> design and approach itself needs to be discussed. I think, we should
> let authors decide whether they want it to be added to the next
> commitfest or not.
>
> When I first started with this work, Tom had suggested me to try to
> make PREPARE and COMMIT/ROLLBACK PREPARED involving foreign servers or
> at least postgres_fdw servers work. I think, most of my work that
> Vinayak and Sawada have rebased to the latest master will be required
> for getting what Tom suggested done. We wouldn't need a lot of changes
> to that design. PREPARE involving foreign servers errors out right
> now. If we start supporting prepared transactions involving foreign
> servers that will be a good improvement over the current status-quo.
> Once we get that done, we can continue working on the larger problem
> of supporting ACID transactions involving foreign servers.
In the pgconf ASIA depelopers meeting Bruce Momjian and other developers
discussed
on FDW based sharding [1]. The suggestions from other hackers was that
we need to discuss
the big picture and use cases of sharding. Bruce has listed all the
building blocks of built-in sharding
on wiki [2]. IIUC,transaction manager involving foreign servers is one
part of sharding.
As per the Bruce's wiki page there are two use cases for transactions
involved multiple foreign servers:
1. Cross-node read-only queries on read/write shards:
This will require a global snapshot manager to make sure the shards
return consistent data.
2. Cross-node read-write queries:
This will require a global snapshot manager and global transaction
manager.

I agree with you that if we start supporting PREPARE and COMMIT/ROLLBACK
PREPARED
involving foreign servers that will be good improvement.

[1] https://wiki.postgresql.org/wiki/PgConf.Asia_2016_Developer_Meeting
[2] https://wiki.postgresql.org/wiki/Built-in_Sharding

Regards,
Vinayak Pokale
NTT Opern Source Software Center