Re: Transactions involving multiple postgres foreign servers

On Wed, Nov 2, 2016 at 9:22 PM, Ashutosh Bapat
<ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
> On Mon, Oct 31, 2016 at 6:17 AM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>> On Fri, Oct 28, 2016 at 3:19 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>> On Wed, Oct 26, 2016 at 2:00 AM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>>>> I think we can consider the atomic commit and the atomic visibility
>>>> separately, and the atomic visibility can build on the top of the
>>>> atomic commit.
>>>
>>> It is true that we can do that, but I'm not sure whether it's the best design.
>>
>> I'm not sure best design, too. We need to discuss more. But this is
>> not a particular feature for the sharing solution. The atomic commit
>> using 2PC is useful for other servers that can use 2PC, not only
>> postgres_fdw.
>>
>
> I think, we need to discuss the big picture i.e. architecture for
> distributed transaction manager for PostgreSQL. Divide it in smaller
> problems and then solve each of them as series of commits possibly
> producing a useful feature with each commit. I think, what Robert is
> pointing out is if we spend time solving smaller problems, we might
> end up with something which can not be used to solve the bigger
> problem. Instead, if we define the bigger problem and come up with
> clear subproblems that when solved would solve the bigger problem, we
> may not end up in such a situation.
>
> There are many distributed transaction models discussed in various
> papers like [1], [2], [3]. We need to assess which one/s, would suit
> PostgreSQL FDW infrastructure and may be specifically for
> postgres_fdw. There is some discussion at [4]. It lists a few
> approaches, but I could not find a discussion on pros and cons of each
> of them, and a conclusion as to which of the approaches suits
> PostgreSQL. May be we want to start that discussion.

Agreed. Let's start discussion.
I think that it's important to choose what type of transaction
coordination we employ; centralized or distributed.

> I know that it's hard to come up with a single model that would suit
> FDWs or would serve all kinds of applications. We may not be able to
> support a full distributed transaction manager for every FDW out
> there. It's possible that because of lack of the big picture, we will
> not see anything happen in this area for another release. Given that
> and since all of the models in those papers require 2PC as a basic
> building block, I was of the opinion that we could at least start with
> 2PC implementation. But I think request for bigger picture is also
> valid for reasons stated above.

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.

Regards,

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