I have inherited a legacy system made in nodeJS and postgres. Whenever I encounter a database call error e.g let's say an insert violates a duplicate constraint the db client throws an error which I handle but its unable to make subsequent queries to the db and it hangs. I have tried adding an error listener which recreates the client on error message but to no avail. I have many files calling the db so Its not ideal to recreate the client on each catch clause. db connection class
const { Client } = require('pg');
const config = require('../../config');
const log = require('../../logger').LOG;
const client = new Client({
connectionString: config.dbUrl
});
client.on('error', err => {
log.info('client connection Error!'+ err.stack);
client = null;
client = new Client({
connectionString: config.dbUrl
});
client.connect();
});
client.on('end', () => {
log.info('client connection! End client sent');
});
client.on('notification', msg => {
log.info('client connection! notification message sent'+ msg);
});
client.connect();
// Export the Postgres Client module
module.exports = client;
sample query that is encountering
function createGame(gameHash) {
Model.query("INSERT INTO games (hash) values($1) RETURNING gId",[gameHash], function(err,db_res) {
if(err) {
log.info('Game record creation error: '+err.stack);
}
log.info('create record db resp: '+JSON.stringify(db_res));
gameId = db_res.rows[0].gId;
});
}
UPDATE: so after reviemwing the logs keenly I have observed the issue occurs when the client decides to call an update command instead of the insert command in the query above. Interestingly the query passed to it is hard coded string clearly indicating insert but for some reason the update command is called.
2023-02-03 01:34:04 : create record db resp- with hash=>:
{"command":"UPDATE","rowCount":1,"oid":null,"rows":[],"fields":[],"_types":{"_types":{"arrayParser":{},
"builtins":{"BOOL":16,"BYTEA":17,"CHAR":18,"INT8":20,"INT2":21,"INT4":23,"REGPROC":24,"TEXT":25,"OID":26,"TID":27,"XID":28,"CID":29,"JSON":114,
"XML":142,"PG_NODE_TREE":194,"SMGR":210,"PATH":602,"POLYGON":604,
"CIDR":650,"FLOAT4":700,"FLOAT8":701,"ABSTIME":702,"RELTIME":703,
"TINTERVAL":704,"CIRCLE":718,"MACADDR8":774,"MONEY":790,"MACADDR":829,"INET":869,"ACLITEM":1033,"BPCHAR":1042,"VARCHAR":1043,"DATE":1082,
"TIME":1083,"TIMESTAMP":1114,"TIMESTAMPTZ":1184,"INTERVAL":1186,
"TIMETZ":1266,"BIT":1560,"VARBIT":1562,"NUMERIC":1700,"REFCURSOR":1790,"REGPROCEDURE":2202,"REGOPER":2203,"REGOPERATOR":2204,"REGCLASS":2205,"REGTYPE":2206,"UUID":2950,"TXID_SNAPSHOT":2970,"PG_LSN":3220,
"PG_NDISTINCT":3361,"PG_DEPENDENCIES":3402,"TSVECTOR":3614,"TSQUERY":3615,"GTSVECTOR":3642,"REGCONFIG":3734,"REGDICTIONARY":3769,
"JSONB":3802,"REGNAMESPACE":4089,"REGROLE":4096}},"text":{},"binary":{}},"RowCtor":null,"rowAsArray":false}
client.on('error')event handler?