Getting ERROR: invalid transaction termination CONTEXT: PL/pgSQL function run(text,text,text,double precision) line 147 at COMMIT

Question

I am trying to execute an INSERT statement and, if there's an error then I rollback and retry the insert; otherwise I commit it to close the transaction. However, I am getting the error: "invalid transaction termination".

The below code is converted code from Oracle to PostgreSQL.

    sql_cur CURSOR FOR
    SELECT
        step_id, sql_text, exit_on_exception, error_code, error_message, always_run
        FROM ifm_app.sql_for_batch
        WHERE batch = p_batch AND step_id > beginstep
        /* 0 */
        ORDER BY step_id;
    SELECT
        COALESCE(MAX(step_id), 0)
        INTO STRICT beginstep
        FROM ifm_app.sql_for_batch_log
        WHERE insert_date = (SELECT
            MAX(insert_date)
            FROM ifm_app.sql_for_batch_log
            WHERE batch = p_batch AND error_code = 0) AND batch = p_batch AND error_code = 0;
    currentstep := 0;
    /* How many step will run? */
    SELECT
        COUNT(*), MAX(step_id)
        INTO STRICT stepcount, maxstep
        FROM ifm_app.sql_for_batch
        WHERE batch = p_batch AND step_id > 0;

    IF maxstep = beginstep THEN
        /* means, that previsious run was done successfully */
        beginstep := 0;
        /* batch will run from beginning */
    END IF;

    IF beginstep = 0 THEN
        INSERT INTO ifm_app.sql_for_batch_log_archive
        SELECT
            *
            FROM ifm_app.sql_for_batch_log
            WHERE batch = p_batch;
        DELETE FROM ifm_app.sql_for_batch_log
            WHERE batch = p_batch;
    END IF;
    /* set_parameter('^^P_STRING^^',p_string); */

    FOR sql_rec IN sql_cur LOOP
        currentstep := currentstep + 1;

        IF sql_rec.step_id > beginstep OR UPPER(sql_rec.always_run) = 'Y' THEN
            errstr := sql_rec.error_message;
            errcode := sql_rec.error_code;
            s := UPPER(sql_rec.sql_text);
            /* Replace parameters with values */

            FOR param_rec IN param_cursor LOOP
                s := replace(s, coalesce(param_rec.param_name, ''), coalesce(param_rec.param_value, ''));
            END LOOP;
            errstr := '';
            errcode := 0;

            BEGIN
                p_start_date := LOCALTIMESTAMP;
                EXECUTE (s);
                PERFORM pg_sleep(ifm_app.get_sleep_parameter());

                IF 'N' <> p_log THEN
                    s1 := substr(s, 1, 4000);
                    INSERT INTO ifm_app.sql_for_batch_log
                    VALUES (p_batch, sql_rec.step_id, s1, errcode, errstr, p_start_date, p_start_date);
                END IF;
                
                --[5035 - Severity CRITICAL - Your code ends a transaction inside a block with exception handlers. Revise your code to move transaction control to the application side and try again.]
                commit;
                --*/
                EXCEPTION
                    WHEN others THEN
                        errstr := SQLERRM;
                        errcode := SQLSTATE;

                        IF UPPER(sql_rec.exit_on_exception) = 'Y' THEN
                          --  [5035 - Severity CRITICAL - Your code ends a transaction inside a block with exception handlers. Revise your code to move transaction control to the application side and try again.]
                            rollback;
                            
                            errstr := CONCAT_WS('', errstr, ' process stopped');
                        END IF;

                        IF 'N' <> p_log THEN
                            s1 := substr(s, 1, 4000);
                            INSERT INTO ifm_app.sql_for_batch_log
                            VALUES (p_batch, sql_rec.step_id, s1, errcode, errstr, p_start_date, p_start_date);
                            
                            --[5035 - Severity CRITICAL - Your code ends a transaction inside a block with exception handlers. Revise your code to move transaction control to the application side and try again.]
                            commit;
                            
                        END IF;

                        IF UPPER(sql_rec.exit_on_exception) = 'Y' THEN
                            RAISE;
                        END IF;
            END;
        END IF;
    END LOOP;
END;
$BODY$
LANGUAGE plpgsql;

Answer 1

Your indentation is creative, but the problem is here:

BEGIN
    [...]
    commit;
EXCEPTION
    WHEN others THEN
        [...]
END;

When you enter a BEGIN / END block that has an EXCEPTION handler, PostgreSQL starts a subtransaction. COMMIT would close a subtransaction, so you cannot commit there. If you think about it, it is the only way to do that correctly, since everything after the BEGIN needs to be rolled back if you enter the exception handler. I know that Oracle does not care about transactional integrity in such a case, but PostgreSQL does.

My advice is to remove the COMMIT statements. It doesn't look like you really need them. Otherwise, you have to move the outer loop to client code and commit there.