为什么我的postgresql函数插入回滚? [英] Why is my postgresql function insert rolling back?

问题描述

我具有以下功能:

CREATE OR REPLACE FUNCTION "Sensor"."PersistTelemetry"(sid character varying, measurement character varying, val numeric, ts character varying)
    RETURNS boolean
    LANGUAGE 'plpgsql'
    VOLATILE
    COST 100
AS $BODY$DECLARE SUCCESS BOOLEAN;

BEGIN
    BEGIN
        SUCCESS = false;
        INSERT INTO
          "Sensor"."SensorReadings" (
            sensorid,
            reservoirid,
            timestamp,
            measurement,
            value
          )
        VALUES
          (
            sid,
            (
              SELECT
                reservoirid
              FROM
                "Sensor"."SystemSensors"
              WHERE
                sensorid = sid
            ),
            to_timestamp(ts, 'YYYY/MM/DD hh24:mi:ss'),
            measurement,
            val
          );
          SUCCESS = true;
    EXCEPTION WHEN OTHERS THEN
        SUCCESS = false;      
        RAISE NOTICE 'ErError % %', SQLERRM, SQLSTATE;
    END;

    RETURN SUCCESS;
END; $BODY$;

我用flask-sqlalchemy调用它，并具有以下执行和有效负载:

I am calling it with flask-sqlalchemy with the following execution and payload:

@app.route('/api/telemetry', methods=['POST'])
def persist_telemetry():

    if not request.json:
        abort(400)

    sensorID = request.json['sensorID']
    measurement = request.json['measurement']
    value = request.json['value']
    timestamp = request.json['timestamp']

    params = {
        'sensorid' : sensorID,
        'measurement' : measurement,
        'val' : value,
        'ts' : timestamp
    }

    print(params)

    result = db.session.execute("""select "Sensor"."PersistTelemetry"(:sensorid, :measurement, :val, :ts)""", params)
    for r in result:
        print(r)
    return "success", 201

---

{'val': 8.8, 'sensorid': 'phSensorA.haoshiAnalogPh', 'ts': '2019-12-06 18:32:36', 'measurement': 'ph'}

我已启用服务器登录功能，并设置了log_min_messages=notice 但是，在查看日志时，我所看到的是这样的:

I have enabled logging on my server, and set log_min_messages=notice But when viewing the logs, All I see is this:

2019-12-07 02:17:00 CST [14757-15] moedepi@SnooSongFarms LOG:  statement: BEGIN
2019-12-07 02:17:00 CST [14757-16] moedepi@SnooSongFarms LOG:  statement: select "Sensor"."PersistTelemetry"('phSensorA.haoshiAnalogPh', 'ph', 8.8, '2019-12-06 18:32:36')
2019-12-07 02:17:00 CST [14757-17] moedepi@SnooSongFarms LOG:  statement: ROLLBACK

该函数返回true，并且在日志中没有看到字符串' ErError '，因此这告诉我没有引发异常.

The function is returning true, and I don't see the string 'ErError' in the log, so this tells me that an exception is not being raised.

是什么原因导致此插入回滚?如何进一步调试此插入项?

What could be causing this insert to rollback? How do I go about debugging this further?

我们非常感谢您的帮助.

Any help is much appreciated.

推荐答案

日志消息表明PG端没有错误.事务序列号(14757-15/16/17)表示会话中没有其他查询被调用，因此它闻起来像是应用程序端的某些不良行为.可能是因为您关闭了自动提交功能，然后关闭了连接而没有提交.要解决此问题，您需要添加db.session.commit

The log messages indicate that there's no error on the PG end. The transaction sequence numbers (14757-15/16/17) indicate there are no other queries called in the session, so it smells like some bad behavior on the application-side. Could be that you have auto-commit turned off, and you close the connection without committing. To address that, you would need to add a db.session.commit

披露:我为 EnterpriseDB(EDB)

这篇关于为什么我的postgresql函数插入回滚?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！