Re: Transactions involving multiple postgres foreign servers

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.

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.

> Attached latest 3 patches that incorporated review comments so far.
> But recovery speed improvement that is discussed on another thread is
> not incorporated yet.
> Please give me feedback.
>

[1] http://link.springer.com/article/10.1007/s00778-014-0359-9
[2] https://domino.mpi-inf.mpg.de/intranet/ag5/ag5publ.nsf/1c0a12a383dd2cd8c125613300585c64/7684dd8109a5b3d5c1256de40051686f/$FILE/tdd99.pdf
[3] http://docs.lib.purdue.edu/cgi/viewcontent.cgi?article=1713&context=cstech
[4] https://wiki.postgresql.org/wiki/DTM

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company