Re: Transactions involving multiple postgres foreign servers

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Mon, Jan 5, 2015 at 2:47 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> That's a laudable goal, but I would bet that nothing built on the FDW
>> infrastructure will ever get there.

> Why?

> It would be surprising to me if, given that we have gone to some pains
> to create a system that allows cross-system queries, and hopefully
> eventually pushdown of quals, joins, and aggregates, we then made
> sharding work in some completely different way that reuses none of
> that infrastructure. But maybe I am looking at this the wrong way.

Well, we intentionally didn't couple the FDW stuff closely into
transaction commit, because of the thought that the "far end" would not
necessarily have Postgres-like transactional behavior, and even if it did
there would be about zero chance of having atomic commit with a
non-Postgres remote server. postgres_fdw is a seriously bad starting
point as far as that goes, because it encourages one to make assumptions
that can't possibly work for any other wrapper.

I think the idea I sketched upthread of supporting an external transaction
manager might be worth pursuing, in that it would potentially lead to
having at least an approximation of atomic commit across heterogeneous
servers.

Independently of that, I think what you are talking about would be better
addressed outside the constraints of the FDW mechanism. That's not to say
that we couldn't possibly make postgres_fdw use some additional non-FDW
infrastructure to manage commits; just that solving this in terms of the
FDW infrastructure seems wrongheaded to me.

regards, tom lane