How do transactions work with node-postgres?

Question

I'm using node-postgres to make SQL queries with callback style. I have a function that takes in a list of queries and executes them within a transaction block. My understanding is that I submit the query "BEGIN" using node-postgres, submit all the queries I need within my transaction block, and then submit the query "COMMIT".

However, event though my queries are valid (simple inserts, well-tested as stand-alone queries), and everything is definitely getting executed in the right order, when I test the state of the database after the transaction block, I'm getting inconsistent states. Sometimes all of my inserts occurred, and sometimes only some of them did. My understanding is that the transaction block is atomic, so the inserts within a block should be all or nothing on looking for them with a SELECT immediately after the commit.

Here's my function:

Db.prototype.makeTransaction = function (queries, callback) {
    var thisDb = this;
    thisDb.tryQuery("BEGIN", [], function () {
        async.forEach(queries, function (query, arrayCallback) {
            thisDb.tryQuery(query.sql, query.values, arrayCallback);
        }, function (err) {
            if (err) {
                thisDb.tryQuery("ROLLBACK", [], function () {
                    callback(err);
                });
            } else {
                thisDb.tryQuery("COMMIT", [], callback);
            }
        });
    });
};

With helper function tryQuery:

Db.prototype.tryQuery = function (query, values, callback) {
    pg.connect(this.conn, function (err, client) {
        if (!err) {
            client.query(query, values, callback);
        } else {
            // Failed to connect to the database
            callback(err);
        }
    });
};

Any thoughts on what is going wrong?

is pg.connect guaranteed to return the same connection on each call? consider enabling statement logging on the server to see what's going on (and include things like connection and session id in the server log_line_prefix) — araqnid
– araqnid, Commented Dec 4, 2012 at 17:36
I'm not guaranteed to return the same connection; in fact, I'm almost certainly getting different connections. However, this shouldn't be an issue, due to my callback structure. I callback after 'BEGIN', then I callback after the (asynchronous) queries within the transaction, and then I call back after 'COMMIT'. Regardless of whether different connections are used for the different queries, it should be executing in the right order, and that's all that matters, right? — ferson2020
– ferson2020, Commented Dec 4, 2012 at 17:58
Or do queries that make up a transaction block have to be done on the same connection? Could you explain what having different connections means, or provide me a link please? — ferson2020
– ferson2020, Commented Dec 4, 2012 at 18:02
but you're calling tryQuery repeatedly, and every time it will call pg.connect to perform the query, won't it? or does it skip connecting if this.conn is already connected? — araqnid
– araqnid, Commented Dec 4, 2012 at 18:02
yes, commands within a transaction block must be made on the same connection. — araqnid
– araqnid, Commented Dec 4, 2012 at 18:03

ferson2020 · Accepted Answer · 2012-12-06 15:54:19Z

3

Thanks to araqnid for answering my question: a transaction must be done on the same connection.

answered Dec 6, 2012 at 15:54

ferson2020

3,4253 gold badges20 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

How do transactions work with node-postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related