0

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;
2
  • 1
    Is that a function or a procedure? You can not commit inside a function in Postgres
    – user1822
    Mar 20 at 9:23
  • It is stored procedure.
    – RAJIV
    Mar 21 at 6:37

1 Answer 1

0

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.

4
  • Thank you for the input Laurenz Albe. Let me try with your suggestions
    – RAJIV
    Mar 21 at 6:37
  • Should i remove the ROLLBACK as well @Laurenz Albe
    – RAJIV
    Mar 24 at 13:12
  • Yes. Either not catch the exception (be more specific than WHEN OTHERS) or re-raise the exception with RAISE; so that you can catch the error on the client side and roll back. Mar 28 at 11:03
  • Thank you @Laurenz Albe.
    – RAJIV
    Mar 30 at 5:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.