Dependent Transaction in separate Database-Connections

Question

I am quite new to database and I am in the following situation.

I have two tables defining a 1:n relation, let's say Company and Employee which I access in two separated database connections in postgres.

I am now writing a new company and some of its employees to the respective tables, in two separate transactions. I then commit the company transaction and then the employee transaction. But what should I do if the employee transaction failed, AFAIK I can't unroll the committed company transaction?

I have the feeling that my approach of handling dependent transaction in separate Database-Connections is not from the book.

Answer 1

If you really need to split data in multiple databases, you will need a PREPARED TRANSACTION[1] (A.K.A Two fase commit). Your process will be much simpler if you put all tables in the same database.

If you want, you can map the tables of one database into the another using a FDW[2] like postgres_fdw[3]. FDW can map a remote table to a postgres or another database (like oracle, mysql, mongo, etc).

If you provide more details, perhaps I can help you more.

[1] https://www.postgresql.org/docs/current/static/sql-prepare-transaction.html

[2] https://wiki.postgresql.org/wiki/Foreign_data_wrappers

[3] https://www.postgresql.org/docs/current/static/postgres-fdw.html