Aurora Babelfish transaction across PostgreSQL and TFS port

Question

Hi.
Given an application that used to run multiple DB scripts against SQL Server in a single transaction, how can I implement the same when moving the Aurora Postgres?

The challenge is as follows:
Some DB scripts use features not supported by Babelfish (e.g., XML parsing-related TSQL functionality); thus, these scripts must be translated to PL/pgSQL and executed against Aurora's Postgres endpoint.

A sample problematic use case: An application opens a SQL Server transaction against the TDS endpoint, runs a TSQL script to create a SQL Server schema, and then the application attempts to run a PL/pgSQL script against the Postgres using the schema created in the previous script, this will fail. The reason for the failure is that the first script was executed inside a transaction that yet to be committed; thus, the second script executed a different Aurora endpoint is executed outside of that transaction and cannot see the schema created and yet to be committed.
Another problem with this scenario is that rolling back the transaction against the TDS port doesn't affect scripts executed against the Postgres endpoint.

Is it possible to open a transaction spanning scripts executed against both the TDS and Postgres endpoints; thus, allowing the scripts executed against the different endpoints to be aware of the changes made by each other, and also implement a true (true="all or nothing") transaction across endpoints?

Answer

Hello,

In addition to the above, please let me inform you that XML parsing functionality does not require translation to PL/pgSQL. Many operations can be solved with the combination of using PG XPATH and UNNEST functions within T-SQL. This way you can avoid the nested transaction problem.

Using PG native XPATH() - https://www.postgresql.org/docs/15/functions-xml.html#FUNCTIONS-XML-PROCESSING

With PG native UNNEST() - https://www.postgresql.org/docs/15/functions-array.html

I would recommend you to please replicate on your instance and let us know if you face any issues over support case.

Thank you.

Answer

Short answer: no. One cannot combine T-SQL and PostgreSQL transaction semantics in the same transaction.

A common approach is to invoke a function in pg/PLsql from T-SQL, and implement the pg/PLsql function without transaction control commands.

Aurora Babelfish transaction across PostgreSQL and TFS port

Relevant content