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);

Laurenz Albe · Accepted Answer · 2021-01-21 17:07:24Z

3

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:

Create a normal transaction with BEGIN or START TRANSACTION on both databases.
Perform work on both databases.
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.
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!

answered Jan 21, 2021 at 17:07

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

José Castellanos Over a year ago

How can I say transaction that the databade is in another pc.

Laurenz Albe Over a year ago

With the connect string.

José Castellanos Over a year ago

That's my problem :"(, can you help me?

Laurenz Albe Over a year ago

That is a different question. But be warned that Stackoverflow is a Q&A forum, it is not suitable for tutorials or teaching sessions.

Laurenz Albe Over a year ago

I am not unwilling to help, otherwise I would not answer on this forum. But you essentially say, "I don't know how to connect to a remote database, can you teach me?" Can you imagine a clear, succinct answer to such a question?

|

Collectives™ on Stack Overflow

Distributed transaction on PostgreSQL

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related