Pg-promise: does changing the search_path inside a db.task() also change it for queries running outside?

Question

Let's say I have the following code:

db.task(t => {
    return t.none('set search_path to myschema').then(() => {
        return t.any('select * from mytable').then(results => {
            return t.none('set search_path to originalschema').then(() => {
                return results
      })
    })
  })
})

Could a query outside of db.task(), that happened to run in between of the two search_path changes inside db.task(), actually access the data in 'myschema' instead of 'originalschema'?

Answer 1

Could a query outside of db.task(), that happened to run in between of the two search_path changes inside db.task(), actually access the data in 'myschema' instead of 'originalschema'?

No.

SET search_path is a session-based operation, i.e. it applies only to the current connection, which the task allocates exclusively for the entire duration of its execution.

Once the task has finished, it releases the connection back to the pool. At that point, any query that gets that same connection will be working with the alternative schema, unless it is another task that sets the schema again. This gets tricky, if you are setting schema in just one task, and generally not recommended.

Here's how it should be instead:

• If you want to access a special-case schema inside just one task, best is to specify the schema name explicitly in the query.
• If you want to set custom schema(s) dynamically, for the entire app, best is to use option schema, of the Initialization Options. This will propagate the schema automatically through all new connections.
• If you want to set schema statically, there are queries for setting schema permanently.

Addition:

And if you have a very special case, whereby you have a task that needs to run reusable queries inside an alternative schema, then you would set the schema in the beginning of the task, and then restore it to the default schema at the end, so any other query that picks up that connection later won't try to use the wrong schema.

Extra:

Example below creates your own task method (I called it taskEx), consistent across the entire protocol, which accepts new option schema, to set the optional schema inside the task:

const initOptions = {
    extend(obj) {
        obj.taskEx = function () {
            const args = pgp.utils.taskArgs(arguments); // parse arguments
            const {schema} = args.options;
            delete args.options.schema; // to avoid error thrown
            if (schema) {
                return obj.task.call(this, args.options, t => {
                    return t.none('SET search_path to $1:name', [schema])
                        .then(args.cb.bind(t, t));
                });
            }
            return obj.task.apply(this, args);
        }
    }
});

const pgp = require('pg-promise')(initOptions);

So you can use anywhere in your code:

const schema = 'public';
// or as an array: ['public', 'my_schema'];

db.taskEx({schema}, t => {
    // schema set inside task already;
});

Note that taskEx implementation assumes that the schema is fully dynamic. If it is static, then there is no point re-issuing SET search_path on every task execution, and you would want to do it only for fresh connections, based on the following check:

const isFreshConnection = t.ctx.useCount === 0;

However, in that case you would be better off using initialization option schema instead, as explained earlier.