集成规则中的占位符问题

kevin9527 · 2025 年7 月 23 日 12:28

问题描述

在使用规则过程中遇到写入数据库异常，经检查为占位符问题导致

emqx版本-5.10.0

复现

1、创建postgresql数据库连接器，测试连接正常。打开禁用预处理语句功能

2、规则中使用SQL过滤数据，语法如下：

使用调试功能正常过滤数据，结果如下：

附，一组测试数据：

{"SN":"d6bb9e8ef40d95a2","Timestamp":1753196893990787600,"SensorNumber":5,"GasName":"VOC","GasUnit":"ppm","Concentration":0.0,"Range":200.0,"Status":1}

3、在动作输出中添加动作，测试连接正常，结果如下

4、在规则的模拟测试中，输入测试数据，结果提示Postgresql动作执行失败。日志如下：

{
  "time": "2025-07-23T20:22:38.449188+08:00",
  "msg": "action_template_rendered",
  "meta": {
    "trace_tag": "QUERY_RENDER",
    "rule_trigger_ts": [
      1753273358448
    ],
    "rule_ids": [
      "rule_nbpm"
    ],
    "result": {
      "statement_type": "query",
      "statement_or_name": "INSERT INTO $1(timestamp, concentration, range, gasunit) VALUES ($2, $3, $4, $5)",
      "data": [
        "voc",
        1753196893990787600,
        0,
        200,
        "ppm"
      ]
    },
    "client_ids": [
      "c_emqx"
    ],
    "action_info": {
      "type": "pgsql",
      "name": "test"
    }
  },
  "level": "debug"
}

同时，检查服务器Postgresql日志显示syntax error错误

2025-07-23 20:02:35.726 CST [3810412] Sensor@d6bb9e8ef40d95a2 ERROR:  syntax error at or near "$1" at character 13
2025-07-23 20:02:35.726 CST [3810412] Sensor@d6bb9e8ef40d95a2 STATEMENT:  INSERT INTO $1(timestamp, concentration, range, gasunit) VALUES ($2, $3, $4, $5)