Distributed transaction on PostgreSQL

Question

Can someone tell me about how I can perform a distributed transaction on PostgreSQL?

I need to start transaction from a node x to node y (this node has a database). But I don't find information on internet on how I can do it.

All I can do is a distributed query with:

select dblink_connect
('conn','dbname=ConsultaRemota host=192.168.3.9 
 user=remoto password=12345 port=5432');

select * from dblink('conn','select * from tablaremota') as
temp (id_remoto int, nombre_remoto text, descripcion text);

question from:https://stackoverflow.com/questions/65830457/distributed-transaction-on-postgresql

Answer 1

Using dblink is no true distributed transaction, because it is possible that the remote transaction succeeds, while the local transaction fails.

To perform a distributed transaction:

1. Create a normal transaction with BEGIN or START TRANSACTION on both databases.

2. Perform work on both databases.

3. Once you are done, prepare the transaction on both databases:

   PREPARE TRANSACTION 'some_name';
   

   This step will perform everything that could potentially fail during COMMIT and persist the transaction, but it will not yet commit it.

   If that step fails somewhere, use ROLLBACK or ROLLBACK PREPARED to abort the transaction on all databases.

4. Commit the transaction on all databases:

   COMMIT PREPARED 'some_name';
   

   This is guaranteed to succeed.

To reliably perform a distributed transaction, you need a transaction manager: that is a piece of software that keeps track of all distributed transactions. This component has to persist its information, so that it can survive a crash. The job of the transaction manager is to commit or rollback any transaction that was left in an incomplete state after a crash.

This is necessary, because prepared transactions will stay around even if you restart the database, and they will hold locks and block VACUUM progress. Such orphaned prepared transactions can break your database.

Never use distributed transactions without a transaction manager!