Why is my postgresql function insert rolling back?

Question

I have the following function:

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$;

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'}

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

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.

Could it be that you have auto-commit turned off, and you close the connection without committing? Do you need a db.session.commit? — richyen
– richyen, Commented Dec 7, 2019 at 8:31
@richyen is this a setting in postgres or sqlalchemy? Thanks for the idea — Lamar
– Lamar, Commented Dec 7, 2019 at 8:33
Auto Commit seems to be enabled by default and I confirmed that mine is still on. hmm — Lamar
– Lamar, Commented Dec 7, 2019 at 8:36
Can you update with your sqlalchemy code? The log messages indicate that there's no error on the PG end (you may wish to set log_line_prefix to include a timestamp that's more granular, like with milliseconds--to see if there's some gap/wait in the app, before sending the rollback). 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 — richyen
– richyen, Commented Dec 7, 2019 at 8:40
@richyen - you were right, it needed a commit on the client side.. thanks for the help. I will accept your solution if you submit one. — Lamar
– Lamar, Commented Dec 7, 2019 at 8:45

richyen · Accepted Answer · 2019-12-10 22:56:11Z

1

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

Disclosure: I work for EnterpriseDB (EDB)

edited Dec 10, 2019 at 22:56

answered Dec 7, 2019 at 8:53

richyen

10.3k4 gold badges18 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why is my postgresql function insert rolling back?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related