RE: Transactions involving multiple postgres foreign servers, take 2

From: Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
> On Tue, Jun 8, 2021 at 5:28 PM tsunakawa(dot)takay(at)fujitsu(dot)com
> <tsunakawa(dot)takay(at)fujitsu(dot)com> wrote:
> > 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.

I'm not talking about the code simplicity here (actually, I haven't reviewed the code around prepare and commit in the patch yet...) Also, I don't understand well what you're trying to insist and what realistic situations you have in mind by citing exit_on_error, FATAL, PANIC and so on. I just asked (in a different part) why the client has to know the error.

Just to be clear, I'm not saying that we should hide the error completely behind the scenes. For example, you can allow the FDW to emit a WARNING if the DBMS-specific client driver returns an error when committing. Further, if you want to allow the FDW to throw an ERROR when committing, the transaction manager in core can catch it by PG_TRY(), so that it can report back successfull commit of the global transaction to the client while it leaves the handling of failed commit of the FDW to the resolver. (I don't think we like to use PG_TRY() during transaction commit for performance reasons, though.)

Let's give it a hundred steps and let's say we want to report the error of the committing FDW to the client. If that's the case, we can use SQLSTATE 02xxx (Warning) and attach the error message.

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

Neither FATAL nor PANIC can be ignored. When FATAL, which means the termination of a particular session, the committing of the remote transaction should be taken over by the resolver. Not to mention PANIC; we can't do anything. Otherwise, we proceed with committing other FDWs, hand off the task of committing the failed FDW to the resolver, and report success to the client. If you're not convinced, I'd like to ask you to investigate the code of some Java EE app server, say GlassFish, and share with us how it handles an error during commit.

Regards
Takayuki Tsunakawa