In Oracle, when a runtime error occurs in a SQL statement, all the updates on the database caused by that single statement are rolled back. This is called statement-level transaction isolation. For example, if a single UPDATE statement updates five rows but an attempt to update a sixth row results in an error, the updates to all six rows made by this UPDATE statement are rolled back. The effects of prior SQL statements that have not yet been committed or rolled back are pending until a COMMIT or ROLLBACK statement is executed.
In PostgreSQL, if an error occurs while executing a SQL statement, all the updates on the database since the start of the transaction are rolled back. In addition, the transaction is left in a terminated state and either a COMMIT or ROLLBACK statement must be executed before another transaction can be started.
If polar_comp_stmt_level_tx is set to on, an error does not automatically roll back prior uncommitted database updates, similar to the Oracle behavior. If polar_comp_stmt_level_tx is set to off, an error rolls back uncommitted database updates.
As shown in the following example running in PSQL, if polar_comp_stmt_level_tx is set to off, the first INSERT statement is still rolled back after the second INSERT statement is terminated. In PSQL, the statement \set AUTOCOMMIT off must be used. Otherwise every statement commits automatically. This defeats the purpose of this demonstration of the effect of polar_comp_stmt_level_tx.
\set AUTOCOMMIT off
SET polar_comp_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, polar_comp_stmt_level_tx is set to on. The first INSERT statement has not been rolled back after an error occurs in the second INSERT statement. At this point, the first INSERT statement can either be committed or rolled back.
\set AUTOCOMMIT off
SET polar_comp_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; A ROLLBACK statement may be executed instead of the COMMIT statement. In this case, the insert of employee number 9001 is also rolled back.