3

I am currently working on a nodeJS application that will soon need to write simultaneously on two PostgreSQL databases. This is meant for redudancy purposes: the writing operations must be made in the same time on both databases, or rollbacked if there is anything wrong with one of the databases.

In order to achieve that, I need to rely on a transaction manager that would (or at least could) use two-phase commit in the persisting process.

Currently, the application uses an out-of-the-box solution to perform actions on the current database (i.e. the pg-promise package, which for now stands as the application's transaction manager).

To put it all in a nutshell, I am going to need every persisting action in the Node application to be performed on two databases simultaneously.

For now, I merely use the pg-promise to persist data on one database like on this example:

db.one(mySQLRequest)

but I only found to create a db object for one specific database at a time.

I have found no example for such a vast problematic on a NodeJS RESTful application, but I suspect it might be a rather common issue. And I would be glad if it was not necessary that I recode the whole transaction manager on my own...

If you have any lead about how I could allow my NodeJS application to perform two-phase commits, it would be greatly appreciated. :)

The Once-ler
  • 220
  • 3
  • 16
  • *"I have to implement a two-phase commit in the persisting process"* Rather, an *external transaction manager* has to implement a two-phase commit? (Not sure how PostgreSQL deals with distributed transactions.) In any case, the implementer is unlikely to be you. – Mike Sherrill 'Cat Recall' May 22 '18 at 16:32
  • `two-phase commit` sounds like a nested transaction, which pg-promise supports well. You should include a code example what you are trying to do and explain what exactly doesn't work. – vitaly-t May 22 '18 at 16:52
  • 1
    @vitaly-t: The phrase *write simultaneously on several PostgreSQL databases* struck me as more like distributed transactions than as nested transactions. But you're right--need more code, or at least more detail. – Mike Sherrill 'Cat Recall' May 22 '18 at 17:01
  • I'll try to improve my question to make it clearer. I guess my point is more about distributed transactions, as all persisting actions in the application must be performed in the same time on two databases. – The Once-ler May 22 '18 at 17:21
  • Thank you for your answers. I tried to improve my question, please tell me if I am still unclear. – The Once-ler May 22 '18 at 17:38

2 Answers2

3

nodeJS application that will soon need to write simultaneously on several PostgreSQL databases

Within pg-promise each Database object contains a separate connection pool, with unique connection.

So when communicating with several databases you simply create one Database object for each, and in this scenario it is also a good idea to make use of the Database Context parameter, which can be anything.

Example

const pgp = require('pg-promise')(/* initialization options */);

const db1 = pgp(connection1, dbContext1);
const db2 = pgp(connection2, dbContext2);
const db3 = pgp(connection3, dbContext3);

I have to implement a two-phase commit in the persisting process

This would have to be a custom implementation that depends on the business logic of your application. The library cannot help you there, as there is no such thing as inter-database transaction, you will have to implement it on your own.

I even thought of rewriting some parts of the pg-promise module myself

I don't see what it can give you. The parts that can be automated for transactions are already all there, and the parts for the inter-database integrity are all on your side.

Community
  • 1
  • 1
vitaly-t
  • 24,279
  • 15
  • 116
  • 138
  • Thank you for your answer. Using two databases connections could be an idea, but in order to perform a two phase commit, I would expect these two objects to communicate in order to know if and when they can actually write in the database. Could that be parametrized through the dbContext parameter? – The Once-ler May 23 '18 at 14:15
  • @Pikuni dbContext parameter is there to help differentiate the two databases when it comes to their extension, event logging, etc. However they are meant to communicate with each other is entirely up to you, for the database objects are completely independent of each other. – vitaly-t May 23 '18 at 14:25
  • okay, this makes things clearer. However, I am afraid that the general functionning of pg-promise won't fit the situation. As a matter of fact, I need a similar transaction to be prepared on the two databases, juste as described on [PGSQL documentation](https://www.postgresql.org/docs/current/static/sql-prepare-transaction.html). And from what I have seen, the whole processing of transactions is handled differently (in the normal way of things) in pg-promise. And I cannot see how to have it using prepared statements instead. – The Once-ler May 23 '18 at 15:16
  • @Pikuni General functioning of pg-promise is there to fit any general purpose, including any custom transaction logic one might need. And I'm not sure what prepared statements have to do with any of it, it is just a type of query. – vitaly-t May 23 '18 at 15:24
  • Sorry, used the wrong URL and got confused between prepared statements and the prepare transaction command, though there's no connection. I meant [prepared transactions](https://www.postgresql.org/docs/current/static/sql-prepare-transaction.html). – The Once-ler May 23 '18 at 15:36
  • From your own link - `PREPARE TRANSACTION is not intended for use in applications or interactive sessions. Its purpose is to allow an external transaction manager to perform atomic global transactions across multiple databases or other transactional resources. Unless you're writing a transaction manager, you probably shouldn't be using PREPARE TRANSACTION.`. I think you are looking in the wrong place. And again, pg-promise got nothing to do with prepared transactions ;) – vitaly-t May 23 '18 at 16:26
  • Yes, I had come across that line, but I was reluctant to write a specific transaction manager. But you were right, the library cannot help me there. Anyway, thank you so much for taking the time to answer my questions. I had actually tried to find a way to contact you about pg-promise, but I had not expected you to answer directly on SO. :) Thank you very much for your time. – The Once-ler May 24 '18 at 09:21
2

I've searched and haven't found any node.js modules for distributed transaction management.

If you're working on a roll-your-own, take a look at XA transactions, a distributed database transaction standard. Java has robust and mature support via JTA (Java Transaction API) which helps a node developer only as inspiration and reference.

postgresql has two phase commit support so the building blocks are there for either XA support or a hand-rolled postgresql-specific implementation.

I'm working on an XA-ish transaction manager myself, but the going will be slow with work and kids; side projects are last in line for time. Look for ivolucien/coaction in a year or two...

Ivo Havener
  • 51
  • 4
  • Thank for your answer. I'll keep in touch with your work, for as you mentioned, there does not seem to be a node module for distributed transaction management. As for me, I coped with that issue by using the PgPool II middleware to distribute transactions between two master databases. Unfortunately, PgPool II was not designed for that purpose, and the implementation was quite painful. So I think the node community could definitely use your work. ;) – The Once-ler Oct 10 '18 at 07:21