0

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}
5
  • Can't reproduce with [email protected]. Perhaps queries are running within a transaction? Commented Jan 27, 2023 at 7:59
  • yes I have other queries running in a transaction and i'm experiencing this in both cases my version is 8.3.0 Commented Jan 27, 2023 at 14:54
  • 1
    When a query within a transaction fails, you cannot run any other queries until you rollback the transaction. If you run a query, it will just return an error immediately. Commented Jan 28, 2023 at 18:54
  • What happens when you remove the client.on('error') event handler? Commented Jan 28, 2023 at 18:55
  • still no change. However I have observed the logs painstakingly for the last few days and have gotten the issue but it has left me with more questions than answers. Apparently for some reason according to the logs the client is calling update instead of insert command and thats when it crashes. interestingly the query is a hard coded string clearly indicating insert so how it gets to call update is a mystery I'm yet to unravel. the logs are updated on the question Commented Feb 3, 2023 at 7:19

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.