For this reason, the output of rows is 3 whereas the output of similar procedure in PostgreSQL is 2. So the insert performed through procedure : test_proc in Oracle, before entering into the exception block, is not rolled back.
Postgresql vs oracle code#
Once the code enters the exception block, it is going to rollback everything performed after entering the BEGIN.Īnd when you see the above result, you should see the result as 3 records. 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. Let's look at the behavior of Exception in PL/SQL in Oracle.Ĭonsider the following PL/SQL code which is similar to above PL/pgSQL code. If we explicitly specify ROLLBACK in the EXCEPTION block, the entire transaction is rolled back. 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. So we only see 2 rows inserted in the above output. For this reason, the Insert that got executed before entering the exception block in procedure test_proc in PostgreSQL is rolled back. When an exception is thrown, it is rolled back to the savepoint. 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 caught, the entire block is rolled back before the exception block is executed. The PL/pgSQL block is executed as an atomic sub-transaction in this case. Output of the above command and the number of Inserted rows appear as following. So, we would only see 2 records inserted to test_ts table. What this means is, everything except for procedure ( test_proc()) execution is going to succeed. In the above block, I am performing an Insert and calling the above procedure and again performing another Insert. I have now used the following anonymous block to call this procedure in PostgreSQL.
Once the code enters the exception block, it is going to rollback everything performed after entering the BEGIN. The above procedure is is going to enter the exception block because PERFORM 1/0 is going to throw an exception. In PL/pgSQL, an EXCEPTION clause in a block effectively creates a sub-transaction that can be rolled back without affecting the outer transaction.Ĭonsider the following PL/pgSQL code in PostgreSQL. Let's now consider the behavioral difference between an EXCEPTION block in Oracle vs PostgreSQL. Second observation is that a new transaction is begun only when the first SQL statement is encountered, but not immediately. The first observation is that the COMMIT ends the transaction. We can observe two things from the above output. Output of the above command appears as following. No DML performed after the above commit. CREATE OR REPLACE PROCEDURE test_proc ISĭBMS_OUTPUT.PUT_LINE('transaction id: ' || DBMS_TRANSACTION.LOCAL_TRANSACTION_ID) 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.Ĭonsider the following PL/SQL code in Oracle as an example. 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. Let's see what happens when we use COMMIT in Oracle The above output indicates that when a COMMIT is issued, the current transaction is ended and a new transaction is created. Now, let us call the above created procedure and see the transaction ID's. In PL/pgSQL in PostgreSQL, whenever a COMMIT occurs in a procedure, the current transaction is ended and a new transaction is started automatically.Ĭonsider the following PL/pgSQL code with COMMIT. Now, let's see what happens when we use COMMIT in PostgreSQL We can conclude from the above output that BEGIN and END do not start or end transactions. RAISE NOTICE 'current transaction id: %', txid_current() įollowing is the Output from the above anonymous block. They can't start or commit that transaction because there's no context in which they have to run.Ĭonsider the following PL/pgSQL code in PostgreSQL as an example.
Functions and trigger procedures are always run as part of a transaction started by an outer query. BEGIN/END here are used for grouping purposes only but they do not start or end a transaction. The BEGIN that starts a block in PL/SQL and PL/pgSQL is different from the SQL statement BEGIN that starts a transaction. Most of the time, we get confused with the similarly-named SQL commands for transaction control. 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.
0 kommentar(er)
