RE: Transactions involving multiple postgres foreign servers, take 2

From: Masahiko Sawada <masahiko(dot)sawada(at)2ndquadrant(dot)com>
> Yes, but it still seems hard to me that we require for all FDW
> implementations to commit/rollback prepared transactions without the
> possibility of ERROR.

Of course we can't eliminate the possibility of error, because remote servers require network communication. What I'm saying is to just require the FDW to return error like xa_commit(), not throwing control away with ereport(ERROR). I don't think it's too strict.

> I think it's not necessarily that all FDW implementations need to be
> able to support xa_complete(). We can support both synchronous and
> asynchronous executions of prepare/commit/rollback.

Yes, I think parallel prepare and commit can be an option for FDW. But I don't think it's an option for a serious scale-out DBMS. If we want to use FDW as part of PostgreSQL's scale-out infrastructure, we should design (if not implemented in the first version) how the parallelism can be realized. That design is also necessary because it could affect the FDW API.

> If you're concerned that executing a UDF function by like 'SELECT
> myfunc();' updates data on a foreign server, since the UDF should know
> which foreign server it modifies data on it should be able to register
> the foreign server and mark as modified. Or you’re concerned that a
> UDF function in WHERE condition is pushed down and updates data (e.g.,
> ‘SELECT … FROM foreign_tbl WHERE id = myfunc()’)?

What I had in mind is "SELECT myfunc(...) FROM mytable WHERE col = ...;" Does the UDF call get pushed down to the foreign server in this case? If not now, could it be pushed down in the future? If it could be, it's worth considering how to detect the remote update now.

Regards
Takayuki Tsunakawa