0

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.

3
  • Please identify which database system this is for. Terms have different meaning and behaviors are different depending on the flavor. Aug 2, 2016 at 17:31
  • @ Leigh Riffel I use postgres
    – user695652
    Aug 2, 2016 at 18:14
  • It is not from the book, yes. Furthermore, I think it is impossible to do it safely - why do you try this at all? Why not writing to both tables from a single transaction (and connection)? Aug 2, 2016 at 22:25

1 Answer 1

3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.