Query timeout in pg-promise

Question

I want to add timeout to pg-promise queries so they will fail after some amount of time if database have not yet responded. Is there any recommended way to do that or should I make custom wrapper that will handle timer and reject promise if it's too late?

Answer 1

From the author of pg-promise...

---

pg-promise doesn't support query cancellation, because it is a hack to work-around incorrect database design or bad query execution.

PostgreSQL supports events that should be used when executing time-consuming queries, so instead of waiting, one can set an event listener to be triggered when specific data/view becomes available. See LISTEN/NOTIFY example.

You can extend pg-promise with your own custom query method that will time out with a reject (see example below), but that's again another work-around on top of a design problem.

Example using Bluebird:

const Promise = require('bluebird');

Promise.config({
    cancellation: true
});


const initOptions = {
    promiseLib: Promise,
    extend(obj) {
        obj.queryTimeout = (query, values, delay) => {
            return obj.any(query, values).timeout(delay);
        }
    }
};

const pgp = require('pg-promise')(initOptions);
const db = pgp(/* connection details */);

Then you can use db.queryTimeout(query, values, delay) on every level.

Alternatively, if you are using Bluebird, you can chain .timeout(delay) to any of the existing methods:

db.any(query, values)
    .timeout(500)
    .then(data => {})
    .catch(error => {})

See also:

• extend event
• Bluebird.timeout

UPDATE

From version 8.5.3, pg-promise started supporting query timeouts, via property query_timeout within the connection object.

You can either override the defaults:

pgp.pg.defaults.query_timeout = 3000; // timeout every query after 3 seconds

Or specify it within the connection object:

const db = pgp({
    /* all connection details */

    query_timeout: 3000
});