我具有以下功能:
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.sessionmit
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.sessionmit
披露:我为 EnterpriseDB(EDB)
更多推荐
为什么我的postgresql函数插入回滚?
发布评论