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

Question

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?

score 0 · Accepted Answer · edited May 23 '17 at 12:34

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, from the driver point of view, 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);

The following is a mistake in your code:

t.any('SELECT * FROM users')

which is supposed to be:

return t.any('SELECT * FROM users')

or else there is no transaction logic there, since you are not returning anything from the callback.

The shortest and most efficient way to do what you were trying there:

db.tx(t => {
    return t.map('SELECT * FROM users', [], user => {
        return t.none("INSERT INTO stocks_owned(ticker, shares, user_id) VALUES('GOOG', 10, ${user_id})", user);
    }).then(t.batch);
})
    .then(data => {
        // success, data = [null, null, ...]
    })
    .catch(error => {
        // error
    });

UPDATE

The above example is no longer the most efficient way to do it. The most efficient approach would be to execute one select, and then a single multi-row insert. See Multi-Row Inserts.

Thank you! Your package is great btw (no raunchy pun intended). Cheers. — Charles Riley, Aug 07 '16 at 23:52
"There is no such thing as sub-queries". Well, there is such a thing as subqueries, but not apparently at the node-pg level. A subquery is a construct in SQL where one query contains another, e.g. `SELECT ... FROM ... WHERE x = (SELECT ...)` or `SELECT ... FROM (SELECT ..)` — Craig Ringer, Aug 08 '16 at 03:52
@CraigRinger I have corrected it to explicitly say `from the driver point of view`, as this is what we were discussing, i.e. for the driver it is always just a query, irrelevant of its level. — vitaly-t, Aug 08 '16 at 08:07
@CraigRinger added explanation of a mistake in your code ;) Also see [Chaining Queries](https://github.com/vitaly-t/pg-promise/wiki/chaining-queries). — vitaly-t, Aug 10 '16 at 05:39

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

1 Answers1