Charles Riley Charles Riley - 3 months ago 19
Node.js Question

How do sub-queries contained within queries work in pg-promise when using transactions?

I am using

pg-promise
for node.js, and I wanted to make sure I am understanding the documentation about transactions correctly.

Say I executed the following transaction:

db.tx(function(t) {
t.any('SELECT * FROM users')
.then(function(users) {
var queries = [];
for (var i =0; i < users.length; i++) {
queries.push(t.any("INSERT INTO stocks_owned (ticker, shares, user_id) VALUES ('GOOG', 10, $1)", users[i].user_id));
}
return t.batch(queries);
})
})


What postgres queries will this end up performing?

Will the postgres transaction be:

BEGIN;
SELECT * FROM users;
SAVEPOINT my_savepoint;
INSERT INTO stocks_owned (ticker, shares, user_id) VALUES ('GOOG', 10, 1);
INSERT INTO stocks_owned (ticker, shares, user_id) VALUES ('GOOG', 10, 2);
...
INSERT INTO stocks_owned (ticker, shares, user_id) VALUES ('GOOG', 10, 999);
COMMIT;


In other words, do sub-queries contained within other queries get included in the same
BEGIN/COMMIT
block?

Answer

What postgres queries will this end up performing?

The ones you listed, except there won't be any SAVEPOINT, because savepoints are used in place of nested transactions only.

do sub-queries contained within other queries get included in the same BEGIN/COMMIT block?

There is no such thing as sub-queries, there are just queries, and all those executed inside a transaction will be inside BEGIN/COMMIT block.


To see what is being executed by pg-promise exactly, you should use pg-monitor, or at the very least - handle event query:

var pgOptions = {
    query: function (e) {
        console.log(e.query); // log the query being executed
    }
};

var pgp = require('pg-promise')(pgOptions);
Comments