Oracle vs PostgreSQL – Transaction control statements

While migrating from Oracle to PostgreSQL, the important fact to consider is that they both are 2 different database engines. The major mistake made by organizations during the course of migration is assuming that the behavior of both Oracle and PostgreSQL are same.  One of such areas with differences between Oracle vs PostgreSQL is while working with Transaction control statements. While migrating PL/SQL procedures and functions from Oracle to PL/pgSQL in PostgreSQL, we need to understand in detail about how transaction control statements work. By the end of this article, you would have clarity about Oracle vs PostgreSQL - Transaction control statements.

BEGIN and END

Let us start with an important fact that, BEGIN and END in PL/SQL and PL/pgSQL are merely syntactic elements, and have nothing to do with transactions. Most of the time, we get confused with the similarly-named SQL commands for transaction control. The BEGIN that starts a block in PL/SQL and PL/pgSQL is different from the SQL statement BEGIN that starts a transaction. BEGIN/END here are used for grouping purposes only but they do not start or end a transaction. Functions and trigger procedures are always run as part of a transaction started by an outer query. They can't start or commit that transaction because there's no context in which they have to run.

Consider the following PL/pgSQL code in PostgreSQL as an example.

CREATE OR REPLACE PROCEDURE test_proc ()
    AS $$
DECLARE
BEGIN
    RAISE NOTICE 'current transaction id: %', txid_current();
END;
$$
LANGUAGE PLPGSQL;

DO $$
BEGIN
   RAISE NOTICE 'current transaction id: %', txid_current();
   CALL test_proc ();
   RAISE NOTICE 'current transaction id: %', txid_current();
END;
$$;

Following is the Output from the above anonymous block.

NOTICE: current transaction id: 17788
NOTICE: current transaction id: 17788
NOTICE: current transaction id: 17788

We can conclude from the above output that BEGIN and END do not start or end transactions. The same is with PL/SQL in Oracle. 

Now, let's see what happens when we use COMMIT in PostgreSQL

In PL/pgSQL in PostgreSQL, whenever a COMMIT occurs in a procedure, the current transaction is ended and a new transaction is started automatically.

Consider the following PL/pgSQL code with COMMIT.

CREATE OR REPLACE PROCEDURE test_proc ()
    AS $$
DECLARE
BEGIN
    RAISE NOTICE 'current transaction id: %', txid_current();
    COMMIT;
    RAISE NOTICE 'current transaction id: %', txid_current();
END;
$$
LANGUAGE PLPGSQL;

Now, let us call the above created procedure and see the transaction ID's.

CALL test_proc();

Output of the above command appears as following. 

NOTICE: current transaction id: 1183970
NOTICE: current transaction id: 1183971

The above output indicates that when a COMMIT is issued, the current transaction is ended and a new transaction is created.

Let's see what happens when we use COMMIT in Oracle

PL/SQL in Oracle is similar to PL/pgSQL in PostgreSQL in this case, however, there is still an observable difference in the way new transactions are created. When a COMMIT is performed, the current transaction is ended and a new transaction begins whenever the first SQL statement (particularly, DML commands like INSERT , UPDATE , DELETE , SELECT ) is encountered. 

Consider the following PL/SQL code in Oracle as an example.

CREATE OR REPLACE PROCEDURE test_proc IS
BEGIN
    insert into test_ts values(1);
    DBMS_OUTPUT.PUT_LINE('transaction id: ' || DBMS_TRANSACTION.LOCAL_TRANSACTION_ID);
    COMMIT;

    -- No DML performed after the above commit.

    DBMS_OUTPUT.PUT_LINE('transaction id: ' || DBMS_TRANSACTION.LOCAL_TRANSACTION_ID);
    insert into test_ts values(1);

    DBMS_OUTPUT.PUT_LINE('transaction id: ' || DBMS_TRANSACTION.LOCAL_TRANSACTION_ID);
END;

Now, let us call the above created procedure and see the transaction ID's.

CALL test_proc();

Output of the above command appears as following. 

transaction id: 10.4.6777
transaction id:
transaction id: 10.19.6776

We can observe two things from the above output. The first observation is that the COMMIT ends the transaction. Second observation is that a new transaction is begun only when the first SQL statement is encountered, but not immediately.

Exception block in Oracle vs PostgreSQL

Let's now consider the behavioral difference between an EXCEPTION block in Oracle vs PostgreSQL.

Exception block In PostgreSQL

In PL/pgSQL, an EXCEPTION clause in a block effectively creates a sub-transaction that can be rolled back without affecting the outer transaction.

Consider the following PL/pgSQL code in PostgreSQL.

-- I have created the following procedure
CREATE OR REPLACE PROCEDURE test_proc() 
    AS $$
DECLARE
BEGIN
    insert into test_ts values(1);
    RAISE NOTICE 'current transaction id: %', txid_current();
    PERFORM 1/0;
        EXCEPTION
             WHEN OTHERS THEN
             RAISE NOTICE 'current transaction id: %', txid_current();
END;
$$ LANGUAGE PLPGSQL;

The above procedure is is going to enter the exception block because PERFORM 1/0 is going to throw an exception. Once the code enters the exception block, it is going to rollback everything performed after entering the BEGIN. 

I have now used the following anonymous block to call this procedure in PostgreSQL. 

DO
$$
BEGIN
    insert into test_ts values(1);
    RAISE NOTICE 'current transaction id: %', txid_current();
    CALL test_proc();
    insert into test_ts values(1);
    RAISE NOTICE 'current transaction id: %', txid_current();
END;
$$;

In the above block, I am performing an Insert and calling the above procedure and again performing another Insert. What this means is, everything except for procedure (test_proc()) execution is going to succeed. So, we would only see 2 records inserted to test_ts table.

Output of the above command and the number of Inserted rows appear as following.

-- Output
NOTICE: current transaction id: 17971
NOTICE: current transaction id: 17971
NOTICE: current transaction id: 17971
NOTICE: current transaction id: 17971

-- Rows Inserted
postgres=# SELECT * from test_ts;
 id
----
  1
  1
(2 rows)

The PL/pgSQL block is executed as an atomic sub-transaction in this case. When an exception is caught, the entire block is rolled back before the exception block is executed. Actually, the exception handler block is run in a sub-transaction, which is nothing but creating a savepoint at the BEGIN.

When an exception is thrown, it is rolled back to the savepoint. For this reason, the Insert that got executed before entering the exception block in procedure test_proc in PostgreSQL is rolled back. So we only see 2 rows inserted in the above output.

When an EXCEPTION clause catches an error, the PL/pgSQL function's local variables stay the same as they were before the error, but all changes to persistent database state within the block are rolled back. If we explicitly specify ROLLBACK in the EXCEPTION block, the entire transaction is rolled back.

Exception block In Oracle

Let's look at the behavior of Exception in PL/SQL in Oracle.

Consider the following PL/SQL code which is similar to above PL/pgSQL code.

CREATE OR REPLACE PROCEDURE test_proc IS
dummy number;
BEGIN
    insert into test_ts values(1);
    DBMS_OUTPUT.PUT_LINE('transaction id: ' || DBMS_TRANSACTION.LOCAL_TRANSACTION_ID);
    dummy := 1/0;
         EXCEPTION
              WHEN OTHERS THEN
              DBMS_OUTPUT.PUT_LINE('transaction id: ' || DBMS_TRANSACTION.LOCAL_TRANSACTION_ID);
END;

Similar to example we considered above for PostgreSQL, the above procedure of Oracle is going to enter the exception block because 1/0 is going to throw an exception. Once the code enters the exception block, it is going to rollback everything performed after entering the BEGIN. 

I have now executed the following.

BEGIN
   insert into test_ts values(1);
   DBMS_OUTPUT.PUT_LINE('transaction id: ' || DBMS_TRANSACTION.LOCAL_TRANSACTION_ID);

   test_proc();

   insert into test_ts values(1);
   DBMS_OUTPUT.PUT_LINE('transaction id: ' || DBMS_TRANSACTION.LOCAL_TRANSACTION_ID);
END;

Output of the above command and the number of Inserted rows appear as following. 

-- Output
transaction id: 8.24.3784
transaction id: 8.24.3784
transaction id: 8.24.3784
transaction id: 8.24.3784

-- Rows Inserted
SQL> select * from test_ts;
    ID
----------
    1
    1
    1

And when you see the above result, you should see the result as 3 records. So the insert performed through procedure : test_proc in Oracle, before entering into the exception block, is not rolled back. For this reason, the output of rows is 3 whereas the output of similar procedure in PostgreSQL is 2. 

In PL/SQL, the exception block has nothing to do with transaction control, and the block runs within an existing transaction. When an exception occurs, this is only about branching to another code path. Unlike in PL/pgSQL, the block is not rolled back when an EXCEPTION clause catches the error. For this reason, the insert performed before the exception is still visible in the transaction, and can be committed or rolled back.

Commit and Rollback in Functions - Oracle vs PostgreSQL

When it comes to functions in PL/SQL in Oracle, COMMIT and ROLLBACK behave the same way as procedures. But with PL/pgSQL functions in PostgreSQL, you can't use SAVEPOINT, COMMIT, or ROLLBACK. But, we can still handle exceptions using the EXCEPTION block in PostgreSQL functions. The behavior of EXCEPTION block in PL/SQL and PL/pgSQL functions is similar to procedures. In PL/pgSQL functions, when an EXCEPTION clause catches an error, the block is rolled back whereas PL/SQL function EXCEPTION block is just for code branching and the block is not rolled back.

In both PL/SQL and PL/pgSQL, the transaction calling the procedure or function is aborted if an error is raised and not handled within it. Aborted transactions cannot commit, and if they try to commit, the COMMIT is treated as a ROLLBACK.

Conclusion

We discussed in this article that BEGIN and END are just syntactic elements in PL/SQL in Oracle and PL/pgSQL in PostgreSQL. They have nothing to do with transactions. A new transaction is started automatically in PL/pgSQL after a COMMIT, whereas in PL/SQL, a new transaction begins only when the first SQL statement is encountered. The EXCEPTION block in PL/pgSQL rolls back the block until the last savepoint when an exception is caught, unlike in PL/SQL, where the EXCEPTION block is only used for code branching. Finally, we also understood that PL/pgSQL functions can't have SAVEPOINT, COMMIT, or ROLLBACK. 

It is important to understand the differences between Oracle and PostgreSQL before starting to migrate your databases. Some of such differences have been published in our previous articles : Handling trailing zeroes, OUT parameters in Stored procedures, Porting array length, etc. For more such differences between Oracle and PostgreSQL, please subscribe to our newsletters. 

If you are looking for support with Migrations from Oracle to PostgreSQL, please contact us or send an email to sales@migops.com or fill the following form and someone from our team will connect with you to discuss. 

Fill the following form to Contact Us for Migration Services to PostgreSQL