node-postgres客户端遇错误后无法执行后续查询求助

问题背景 #

接手一套基于NodeJS和PostgreSQL的遗留系统，遇到两个核心问题：

• 数据库调用出错（如插入违反唯一约束）时，虽捕获错误，但客户端无法执行后续查询，系统挂起；尝试通过错误监听器重建客户端无效，且多文件调用场景下逐个catch重建客户端不现实。
• 日志显示，明明传入硬编码的INSERT查询字符串，客户端却执行了UPDATE命令。

数据库连接类代码 #

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;

出错的查询代码示例 #

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命令，而非代码中硬编码的INSERT：

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}

1. 修复客户端重建逻辑 #

当前错误监听器中重新赋值的client变量并未替换已导出的旧实例，后续代码仍在使用失效连接。改用单例工厂模式管理客户端，确保每次获取的都是可用连接：

const { Client } = require('pg');
const config = require('../../config');
const log = require('../../logger').LOG;

let client;

function getClient() {
    // 检查客户端是否已失效或未初始化
    if (!client || client.connection?._ending) {
        // 清理旧连接
        if (client) {
            client.end().catch(err => log.info('关闭旧客户端失败: ' + err.stack));
        }
        // 创建新客户端
        client = new Client({ connectionString: config.dbUrl });
        
        client.on('error', err => {
            log.info('客户端连接错误: ' + err.stack);
            // 标记客户端失效，下次调用getClient时重建
            client = null;
        });
        
        client.on('end', () => {
            log.info('客户端连接终止');
            client = null;
        });
        
        // 建立新连接
        client.connect().catch(err => log.info('新客户端连接失败: ' + err.stack));
    }
    return client;
}

module.exports = { getClient };

2. 修改查询调用逻辑 #

所有数据库调用处，通过getClient()获取可用实例，并完善错误处理：

const { getClient } = require('./path-to-db-connection');

function createGame(gameHash) {
    const client = getClient();
    client.query("INSERT INTO games (hash) values($1) RETURNING gId", [gameHash], (err, db_res) => {
        if (err) {
            log.info('创建游戏记录错误: ' + err.stack);
            // 连接级错误（如连接断开）标记客户端失效
            if (['57P01', '08006'].includes(err.code)) {
                client = null;
            }
            return; // 终止后续逻辑，避免访问无效的db_res
        }
        
        if (db_res?.rows.length) {
            gameId = db_res.rows[0].gId;
            log.info('创建记录响应: ' + JSON.stringify(db_res));
        } else {
            log.info('插入操作未返回数据');
        }
    });
}

3. 排查命令异常根源 #

日志中INSERT变UPDATE的问题，本质是失效连接复用导致的请求混淆。旧连接出错后未被正确销毁，后续查询被路由到异常连接，或异步逻辑中查询语句被意外覆盖。通过上述工厂模式确保连接有效性后，该问题会自然解决。同时需检查：

• 有无复用同一个查询回调函数，导致多次执行
• 异步逻辑中是否存在变量共享，引发查询语句被覆盖

4. 完善错误处理规范 #

• 业务错误（如唯一约束违反，错误码23505）无需重建客户端，仅连接级错误需处理
• 错误分支必须return，避免后续代码访问无效的db_res引发新错误

内容的提问来源于stack exchange，提问作者martin kimani