Re: Transactions involving multiple postgres foreign servers, take 2

On Tue, 22 Sep 2020 at 10:17, tsunakawa(dot)takay(at)fujitsu(dot)com
<tsunakawa(dot)takay(at)fujitsu(dot)com> wrote:
>
> 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.

So with your idea, I think we require FDW developers to not call
ereport(ERROR) as much as possible. If they need to use a function
including palloc, lappend etc that could call ereport(ERROR), they
need to use PG_TRY() and PG_CATCH() and return the control along with
the error message to the transaction manager rather than raising an
error. Then the transaction manager will emit the error message at an
error level lower than ERROR (e.g., WARNING), and call commit/rollback
API again. But normally we do some cleanup on error but in this case
the retrying commit/rollback is performed without any cleanup. Is that
right? I’m not sure it’s safe though.

>
>
> > 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.

IIUC aggregation functions can be pushed down to the foreign server
but I have not idea the normal UDF in the select list is pushed down.
I wonder if it isn't.

Regards,

--
Masahiko Sawada http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services