RE: [HACKERS] Transactions involving multiple postgres foreign servers

From: Masahiko Sawada [mailto:sawada(dot)mshk(at)gmail(dot)com]
> > I'm for the latter. That is, COMMIT or PREPARE TRANSACTION statement
> issued from an application reports an error.
>
> I'm not sure that we should end up with an error in such case, but if
> we want then we can raise an error when the transaction tries to
> modify 2PC-non-capable server after modified 2PC-capable server.

Such early reporting would be better, but I wonder if we can handle the opposite order: update data on a 2PC-capable server after a 2PC-non-capable server. If it's not easy or efficient, I think it's enough to report the error at COMMIT and PREPARE TRANSACTION, just like we report "ERROR: cannot PREPARE a transaction that has operated on temporary tables" at PREPARE TRANSACTION.

> Honestly I'm not sure we should use atomic commit by default at this
> point. Because it also means to change default behavior though the
> existing users use them without 2PC. But I think control of global
> transaction atomicity by GUC parameter would be a good idea. For
> example, synchronous_commit = 'global' makes backends wait for
> transaction to be resolved globally before returning to the user.

Regarding the incompatibility of default behavior, Postgres has the history to pursue correctness and less confusion, such as the handling of backslash characters in strings and automatic type casts below.

Non-character data types are no longer automatically cast to TEXT (Peter, Tom)
Previously, if a non-character value was supplied to an operator or function that requires text input, it was automatically cast to text, for most (though not all) built-in data types. This no longer happens: an explicit cast to text is now required for all non-character-string types. ... The reason for the change is that these automatic casts too often caused surprising behavior.

> I might not understand your comment correctly but the current patch is
> implemented in such way. The patch introduces new FDW APIs:
> PrepareForeignTransaction, EndForeignTransaction,
> ResolvePreparedForeignTransaction and GetPreapreId. The postgres core
> calls each APIs at appropriate timings while managing each foreign
> transactions. FDWs that don't support 2PC set the function pointers of
> them to NULL.

Ouch, you are right.

> Also, regarding the current API design it might not fit to other
> databases than PostgreSQL. For example, in MySQL we have to start xa
> transaction explicitly using by XA START whereas PostgreSQL can
> prepare the transaction that is started by BEGIN TRANSACTION. So in
> MySQL global transaction id is required at beginning of xa
> transaction. And we have to execute XA END is required before we
> prepare or commit it at one phase. So it would be better to define
> APIs according to X/Open XA in order to make it more general.

I thought of:

* Put the functions that implement xa_prepare(), xa_commit() and xa_rollback() in libxa.so or libpq.so.
* PrepareForeignTransaction and EndForeignTransaction for postgres_fdw call them.

I meant just code reuse for Postgres. But this is my simple intuition, so don't mind.

Regards
Takayuki Tsunakawa