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, Dec 04 '12 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, Dec 04 '12 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, Dec 04 '12 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, Dec 04 '12 at 18:02
yes, commands within a transaction block must be made on the same connection. — araqnid, Dec 04 '12 at 18:03

score 3 · Accepted Answer · answered Dec 06 '12 at 15:54

3

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

answered Dec 06 '12 at 15:54

ferson2020

3,015
3
18
26

How do transactions work with node-postgres?

1 Answers1