Azure函数本地调试正常，部署至Azure后间歇性超时

背景 #

参考Azure PostgreSQL IoT Demo示例搭建数据流转流程：ESP32S3采集数据 → Azure IoT Hub → Azure Function Application → Azure PostgreSQL。

原始函数代码 #

module.exports = function (context, IoTHubMessages) {
    var pg = require('pg');
    
    const config = {
                        host: 'myserver.postgres.database.azure.com',
                        user: 'granted', 
                        password: 'mypassword',
                        database: 'telemetry',
                        port: 5432,
                        ssl: true
    };
    
    var client = new pg.Client(config);
    const query = 'insert into tempdata(deviceid, data) values(1,\'' + JSON.stringify(IoTHubMessages) + '\');';
    context.log(query);

    client.connect(err => {
        if (err) throw err;
        else {
            client
                .query(query)
                .then(() => {
                context.log('insert completed successfully!');
                client.end(console.log('Closed client connection'));
                 })
            .catch(err => console.log(err))
            .then(() => {
                context.log('Finished execution, exiting now');
                        });
            }
        });
        
};

部署后问题 #

VSCode本地调试每次完美运行，但部署至Azure后，函数正常执行一次，随后触发5分钟超时，之后又正常执行一次，再次超时。Azure日志如下：

2023-08-08T21:25:06Z [Information] insert into tempdata(deviceid,
data) values(1,'["{ "Temperature": 82.22}","{ "Temperature":
82.22}","{ "Temperature": 82.22}","{ "Temperature": 82.22}","{ "Temperature": 82.22}","{ "Temperature": 82.22}","{
"Temperature": 82.22}","{ "Temperature": 82.22}","{
"Temperature": 82.22}","{ "Temperature": 82.22}"]');
2023-08-08T21:25:06Z [Information] insert completed successfully!
2023-08-08T21:25:06Z [Information] Finished execution, exiting now
2023-08-08T21:30:06Z [Error] Timeout value of 00:05:00 exceeded by
function 'Functions.IoTHub_EventHub3' (Id:
'c7fddcd1-3953-4562-be81-0f04a8f29b48'). Initiating cancellation.
2023-08-08T21:30:06Z [Error] Executed 'Functions.IoTHub_EventHub3'
(Failed, Id=c7fddcd1-3953-4562-be81-0f04a8f29b48, Duration=300017ms)
2023-08-08T21:30:18Z [Information] Executing
'Functions.IoTHub_EventHub3' (Reason='(null)',
Id=70402ee6-e981-44ef-950c-2c7ced54156b)
2023-08-08T21:30:18Z [Information] Trigger Details: PartionId: 0, Offset:
4295065936-4295069176, EnqueueTimeUtc:
2023-08-08T20:32:44.3110000+00:00-2023-08-08T20:34:14.7240000+00:00,
SequenceNumber: 1831-1840, Count: 10
2023-08-08T21:30:18Z [Verbose]
Sending invocation id: '70402ee6-e981-44ef-950c-2c7ced54156b
2023-08-08T21:30:18Z [Verbose] Posting invocation
id:70402ee6-e981-44ef-950c-2c7ced54156b on
workerId:8ea99dea-4f2a-4325-854e-eb18ae1d2ab7
2023-08-08T21:30:18Z [Information] insert into tempdata(deviceid, data) values(1,'["{
"Temperature": 82.22}","{ "Temperature": 82.22}","{
"Temperature": 82.22}","{ "Temperature": 82.22}","{
"Temperature": 82.22}","{ "Temperature": 82.22}","{
"Temperature": 82.22}","{ "Temperature": 82.40}","{
"Temperature": 82.22}","{ "Temperature": 82.22}"]');
2023-08-08T21:30:19Z [Information] insert completed successfully!
2023-08-08T21:30:19Z [Information] Finished execution, exiting now
2023-08-08T21:35:18Z [Error] Timeout value of 00:05:00 exceeded by
function 'Functions.IoTHub_EventHub3' (Id:
'70402ee6-e981-44ef-950c-2c7ced54156b'). Initiating cancellation.

修改异步函数后的问题 #

尝试将代码改为异步函数后，本地调试出现连接终止错误：

module.exports = async function (context, IoTHubMessages) {
    var pg = require('pg');
    
    const config = {
                        host: 'arduinodata.postgres.database.azure.com',
                        user: 'granted', 
                        password: 'mypassword',
                        database: 'mydatabase',
                        port: 5432,
                        ssl: true
    };
    
    var client = new pg.Client(config);
    const query = 'insert into tempdata(deviceid, data) values(1,\'' + JSON.stringify(IoTHubMessages) + '\');';
    context.log(query);
    client.connect();
    client.query(query);
    

    client.end();
    await client.end();
    context.log('insert completed successfully!');  

/*
    client.connect(err => {
        if (err) throw err;
        else {
            client
                .query(query)
                .then(() => {
                context.log('insert completed successfully!');
                client.end(console.log('Closed client connection'));
                 })
            .catch(err => console.log(err))
            .then(() => {
                context.log('Finished execution, exiting now');
                        });
            }
        });*/
        
};

本地错误日志：

[error] Worker uncaught exception: Error: Connection terminated
at C:\Users\grant\Documents\GitHub\Arduino\AppFunc\node_modules\pg\lib\client.js:526:17
at process.processTicksAndRejections (node:internal/process/task_queues:95:5)

问题分析与解决方案 #

1. 原始代码超时原因 #

Azure Functions的Node.js运行时中，普通回调函数需要手动调用context.done()通知执行完成。原始代码完成数据库插入后未调用该方法，导致运行时认为函数仍在执行，直到5分钟超时才终止。本地调试环境未严格检查该逻辑，但Azure环境会强制执行超时规则。

2. 修改异步函数后的错误原因 #

异步函数版本存在多个问题：

• client.connect()、client.query()均为异步操作，未等待完成就调用client.end()，导致连接被提前终止，触发"Connection terminated"错误。
• 连续两次调用client.end()，第一次同步调用直接关闭连接，第二次await已无意义。

修复后的代码 #

使用async/await正确处理异步操作，确保函数正常结束：

module.exports = async function (context, IoTHubMessages) {
    const pg = require('pg');
    
    const config = {
        host: 'myserver.postgres.database.azure.com',
        user: 'granted', 
        password: 'mypassword',
        database: 'telemetry',
        port: 5432,
        ssl: true
    };
    
    const client = new pg.Client(config);
    try {
        // 等待连接建立
        await client.connect();
        // 使用参数化查询避免SQL注入，安全处理JSON数据
        const query = 'INSERT INTO tempdata(deviceid, data) VALUES($1, $2);';
        const values = [1, JSON.stringify(IoTHubMessages)];
        context.log(`执行查询: ${query} 参数: ${JSON.stringify(values)}`);
        // 等待查询执行完成
        await client.query(query, values);
        context.log('insert completed successfully!');
    } catch (err) {
        context.error('数据库操作失败:', err);
        throw err; // 抛出错误让Functions运行时处理
    } finally {
        // 确保连接关闭
        await client.end();
        context.log('Closed client connection');
    }
    // 异步函数无需调用context.done()，返回即可结束
};

额外建议 #

• 参数化查询：原始代码直接拼接JSON字符串到SQL语句，存在SQL注入风险，修复后使用$1、$2参数化方式更安全。
• 错误处理：添加try/catch捕获数据库操作错误，通过context.error记录日志，便于排查问题。
• 连接池优化：若函数调用频繁，建议使用pg的连接池（pg.Pool）代替每次创建新客户端，提升性能并避免连接泄漏。

内容的提问来源于stack exchange，提问作者Grant Fritchey