Redshift Long Running Query hang indefinitely: Query completes on RedShift #1863
Description
First off love the module and use it often for large ETL processes for multiple clients, certainly the best Db connection module on npm (and I often have to connect to literally every type of Db in the same @project).
After upgrading from v6.4 to v7.9 long running queries began to hang indefinitely despite completing successfully on the Redshift (RS) instance. Cant find a similar issue.
Other queries can still be run against RS (and return results)
- during long running query
- and after long running query's completion on the RS server.
No errors thrown, no evidence of success or failure. Queries work fine if they are shorter.
I've tried listening to every error event I can find in the API and using pg.Client rather than pg.Pool but there is no difference.
I'm out of ideas, code below.
Help much appreciated
const {Pool} = require('pg'); // "pg": "^7.8.2",
const env = require("../../envs").get;
const DEFAULT_RS_AUTH = env("REDSHIFT_DEFAULTS");
const REDSHIFT_USERS = env("REDSHIFT_USERS");
const Pools = {};
function getPgConfig(userId){
const user = REDSHIFT_USERS[userId];
const config = Object.assign({},DEFAULT_RS_AUTH);
config.user = user.user;
config.password = user.password;
config.idleTimeoutMillis = 0;
config.keepAlive = true //this setting isn't in the API DOCs btw (but makes no difference here)
return config
};
function initPool(userId){
let config = getAuth(userId);
Pools[userId] = new Pool(config);
Pools[userId].on('error', (err, client) => { // never thrown
console.error('Unexpected error on idle client', err,userId)
process.exit(-1)
});
return
}
function getPool(userId){
if(!Pools[userId]){ // init the pool if it doesnt exist ( manage mulitple pools );
initPool(userId);
}
return Pools[userId];
};
async function runQueriesList(){
const queries = ['SQL1','SQL2','....','SQL25','....'];
for(let sql of queries){
// queries 1 through 24 run fine, all complete in less than 3mins
// SQL25 runs and completed on Redshift in 3mins +
// SQL25 never fails or returns in Node,
// no errors thrown, no timeout, other queries can be run with the same pool
let res = await getPool('etl_user').query(sql);
console.log(res);
};
}
runQueriesList()