Using two phase commits on postgres

Question

Asumming that a have a table called "t1" in a "db1" and other table called "t2" in a "db2", and i need to insert a record on both tables or fails.

Connected to the db1 i guess i shall type this

BEGIN;
PREPARE TRANSACTION 'pepe'; -- this says the manual that makes your transaction gets stored on disk, so what is the purpose if i can't use it from another database?)

insert into t1 (field) values ('a_value');

COMMIT PREPARED 'pepe'

Connected to the db2 i guess that

BEGIN;
PREPARE TRANSACTION 'pepe'; -- this fails (the name of the transacttion, what is the meaning, what is use for?)
 -- It complains about this "ERROR:  transaction identifier "pepe" is already in use"

insert into t2 (field) values ('another_value');

COMMIT PREPARED 'pepe'

As you may see i don't get how to use two phase commits on postgres.

TL;DR

I'm not getting how to perform syncronization commands on differents DB within the same RDBMS.

I have read at oficial postgres documentation that for syncronizing works across two or more unrelated postgres databases an implementation of the so called "two-phases commits" protocol is at our disposal.

So i start trying to see how people do actually use them within the postgres, i do not see any actual example, at most i get to this post of a guy that was trying to experiment with several postgres client connected to the differents databases in order to emulate the multiple process running in pararell doing things to the several dbs that should end in a gratefully (all commit) or dreadfully way (all rollback).

Other sources i have peek looking foward examples were:

• https://en.wikipedia.org/wiki/Two-phase_commit_protocol (this source explain well the protocol but really makes me wonder where or who is my "Coordinator" and how to send messages to the "participants"... i only got prepare transaction <id>, commit prepared <id> or rollback prepared <id> commands at my disposal)
• Two phase commit
• https://dba.stackexchange.com/questions/145656/dependent-transaction-in-separate-database-connections
• https://www.endpoint.com/blog/2010/07/29/distributed-transactions-and-two-phase-
• https://www.citusdata.com/blog/2017/11/22/how-citus-executes-distributed-transactions/
• (From a golang client-app) https://github.com/go-pg/pg/issues/490

Please i'm really confused, i hope horse_with_no_name to appear here and enlightme (as happen in the past) or any other charity soul that can help me.

Thanks in advance!

Resolution (After Laurenz's Answer)

Connected to the db1, these are the sql lines to execute:

BEGIN;
-- DO THINGS TO BE DONE IN A ALL OR NOTHING FASHION
-- Stop point --    
PREPARE TRANSACTION 't1';
COMMIT PREPARED 't1' || ROLLBACK PREPARED 't2' (decision requires awareness and coordination)

meanwhile connected to the db2 these will be the script to execute:

BEGIN;
-- DO THINGS TO BE DONE IN A ALL OR NOTHING FASHION
-- Stop point --  
PREPARE TRANSACTION 't2';

COMMIT PREPARED 't2' || ROLLBACK PREPARED 't2'

• The -- Stop point -- is where a coordinator process (for example an application executing the statement, or a human behind a psql client console or pgAdminII) shall stop the execution of both scripts (actually not execute any further instruction, that is what i mean by stop).

• Then, first on db1 (and then on db2, or viceversa) the coordinator process (whatever been human or not) must run PREPARE TRANSACTION on each connection.

  • If one of then fails, then the coordinator must run ROLLBACK PREPARED on those database where the transaction was already prepared and ROLLBACK on the others.
  • If no one fails the coordinator must run COMMIT PREPARED on all involved databases, an operation that shall not fail ever (like existing the home when you are one step outside your house with all the things properly set to exit safely)

Answer 1

I think you misunderstood PREPARE TRANSACTION.

That statement ends work on the transaction, that is, it should be issued after all the work is done. The idea is that PREPARE TRANSACTION does everything that could potentially fail during a commit except for the commit itself. That is to guarantee that a subsequent COMMIT PREPARED cannot fail.

The idea is that processing is as follows:

• Run START TRANSACTION on all database involved in the distributed transaction.

• Do all the work. If there are errors, ROLLBACK all transactions.

• Run PREPARE TRANSACTION on all databases. If that fails anywhere, run ROLLBACK PREPARED on those database where the transaction was already prepared and ROLLBACK on the others.

• Once PREPARE TRANSACTION has succeeded everywhere, run COMMIT PREPARED on all involved databases.

That way, you can guarantee “all or nothing” across several databases.

One important component here that I haven't mentioned is the distributed transaction manager. It is a piece of software that persistently memorizes where in the above algorithm processing currently is so that it can clean up or continue committing after a crash.

Without a distributed transaction manager, two-phase commit is not worth a lot, and it is actually dangerous: if transactions get stuck in the “prepared” phase but are not committed yet, they will continue to hold locks and (in the case of PostgreSQL) block autovacuum work even through server restarts, as such transactions must needs be persistent.

This is difficult to get right.