Re: Transactions involving multiple postgres foreign servers, take 2

On Tue, Jun 8, 2021 at 5:28 PM tsunakawa(dot)takay(at)fujitsu(dot)com
<tsunakawa(dot)takay(at)fujitsu(dot)com> wrote:
>
> From: Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
> > On Tue, Jun 8, 2021 at 9:47 AM tsunakawa(dot)takay(at)fujitsu(dot)com
> > <tsunakawa(dot)takay(at)fujitsu(dot)com> wrote:
> > > Why does the client have to know the error on a remote server, whereas the
> > global transaction itself is destined to commit?
> >
> > It's not necessarily on a remote server. It could be a problem with
> > the local server.
>
> Then, in what kind of scenario are we talking about the difficulty, and how is it difficult to handle, when we adopt either the method 1 or 2? (I'd just like to have the same clear picture.)

IMO, even though FDW's commit/rollback transaction code could be
simple in some cases, I think we need to think that any kind of errors
(or even FATAL or PANIC) could be thrown from the FDW code. It could
be an error due to a temporary network problem, remote server down,
driver’s unexpected error, or out of memory etc. Errors that happened
after the local transaction commit doesn't affect the global
transaction decision, as you mentioned. But the proccess or system
could be in a bad state. Also, users might expect the process to exit
on error by setting exit_on_error = on. Your idea sounds like that we
have to ignore any errors happening after the local commit if they
don’t affect the transaction outcome. It’s too scary to me and I think
that it's a bad idea to blindly ignore all possible errors under such
conditions. That could make the thing worse and will likely be
foot-gun. It would be good if we can prove that it’s safe to ignore
those errors but not sure how we can at least for me.

This situation is true even today; an error could happen after
committing the transaction. But I personally don’t want to add the
code that increases the likelihood.

Just to be clear, with your idea, we will ignore only ERROR or also
FATAL and PANIC? And if an error happens during committing one of the
prepared transactions on the foreign server, will we proceed with
committing other transactions or return OK to the client?

Regards,

--
Masahiko Sawada
EDB: https://www.enterprisedb.com/