edb_stmt_level_tx v14

In Oracle, when a runtime error occurs in a SQL command, all the updates on the database caused by that single command are rolled back. This is called statement level transaction isolation. For example, if a single UPDATE command successfully updates five rows but an attempt to update a sixth row results in an exception, the updates to all six rows made by this UPDATE command are rolled back. The effects of prior SQL commands that weren't yet committed or rolled back are pending until a COMMIT or ROLLBACK command is executed.

In PostgreSQL, if an exception occurs while executing a SQL command, all the updates on the database since the start of the transaction are rolled back. In addition, the transaction is left in an aborted state, and either a COMMIT or ROLLBACK command must be issued before another transaction can start.

If edb_stmt_level_tx is set to TRUE, then an exception doesn't automatically roll back prior uncommitted database updates, emulating the Oracle behavior. If edb_stmt_level_tx is set to FALSE, then an exception rolls back uncommitted database updates.

The following example run in PSQL shows that when edb_stmt_level_tx is FALSE, the abort of the second INSERT command also rolls back the first INSERT command. In PSQL, the command \set AUTOCOMMIT off must be issued, otherwise every statement commits automatically, which defeats the purpose of showing the effect of edb_stmt_level_tx.

\set AUTOCOMMIT off
SET edb_stmt_level_tx TO off;

INSERT INTO emp (empno,ename,deptno) VALUES (9001, 'JONES', 40);
INSERT INTO emp (empno,ename,deptno) VALUES (9002, 'JONES', 00);
ERROR:  insert or update on table "emp" violates foreign key constraint
"emp_ref_dept_fk"
DETAIL:  Key (deptno)=(0) is not present in table "dept".

COMMIT;
SELECT empno, ename, deptno FROM emp WHERE empno > 9000;

empno  | ename | deptno
-------+-------+--------
(0 rows)

In the following example, with edb_stmt_level_tx set to TRUE, the first INSERT command wasn't rolled back after the error on the second INSERT command. At this point, the first INSERT command can either be committed or rolled back.

\set AUTOCOMMIT off
SET edb_stmt_level_tx TO on;

INSERT INTO emp (empno,ename,deptno) VALUES (9001, 'JONES', 40);
INSERT INTO emp (empno,ename,deptno) VALUES (9002, 'JONES', 00);
ERROR:  insert or update on table "emp" violates foreign key constraint
"emp_ref_dept_fk"
DETAIL:  Key (deptno)=(0) is not present in table "dept".

SELECT empno, ename, deptno FROM emp WHERE empno > 9000;

empno  | ename | deptno
-------+-------+--------
9001   | JONES |     40
(1 row)

COMMIT;

You can issue a ROLLBACK command instead of the COMMIT command. In that case, the insertion of employee number 9001 is rolled back as well.