1

I have a SQL query that I am sending from the server side of my application and it seems to never time out. I am doing set statement_timeout TO 30000 in the beginning of my query, but that never works. Here is an example query:

set statement_timeout TO 30000; SELECT t1."building_id", t1."company_name", t1."secondary_name", t1."latitude", t1."longitude" FROM public."businesses" t1 JOIN ( SELECT name, (ST_Dump(geom)).geom as geom FROM public."us_states") t2 ON ST_Intersects(t1.geom, t2.geom) WHERE (t2."name" = 'California')

The query runs fine when I do a normal query, without a geospatial join, but something like the query above never times out. I also tried to add connectionTimeoutMillis and idleTimeoutMillis to my connection string, but that doesn't work either. Here is how i'm doing my connection to postgres:

const { Pool } = require("pg");

var config = {
    user: username,
    database: database,
    password: password,
    host: host,
    connectionTimeoutMillis: 30000,
};

// Postgres Connection
var client = new Pool(config);
client.connect();

client.on('error', (err) => {
  console.error('something bad has happened!', err.stack);
});

module.exports = {
    queryPostgres: function (sql, cb) {
        var query = client.query(sql, function (err, dbresult) {
            if (err) {
                console.log(err);
                cb([]);
            } else {
                cb(dbresult.rows);
            }
        });
    }
}

Then I call the module like this:

db.queryPostgres(sql, function (results) {});

Also in my package.json, I am doing "pg": "*" for pg

My query just seems to never time out no matter what I do when sending the query from the server to postgres... If I run the same query in pgAdmin, the query works and timeout since I use set statement_timeout Could someone help me with this? Does connectionTimeoutMillis even work with a Pool? Is there any reason why statement_timeout would work in pgAdmin but not work when i'm sending it from the server side of my application?

Also in my package.json, I am doing "pg": "*" for pg

Any help will be greatly appreciated!

fairlyMinty
  • 413
  • 8
  • 22
  • I know this question is a bit old now, but for what it is worth, have you tried setting `query_timeout`, which defaults to no timeout. ref https://node-postgres.com/api/client – waltron Dec 17 '21 at 08:18

0 Answers0