AUTOCOMMIT in Oracle vs PostgreSQL: Transaction Behaviour Every Migration Must Handle

When an application moves from Oracle to PostgreSQL, the SQL usually converts cleanly. What breaks is often more subtle: a row that is "suddenly" visible to other sessions, a procedure that fails with invalid transaction termination, or a batch job that loses all its work because of one bad row.

Almost all of these come down to how the two databases handle transactions and autocommit. This post shows the differences side by side, Oracle in red and PostgreSQL in blue, so you can spot them before they reach production.

The short answer

	Oracle	PostgreSQL
Server-side autocommit	None. A transaction stays open until COMMIT or ROLLBACK	Always on. Every statement outside BEGIN … COMMIT is its own transaction
Who decides	The client or driver	The client or driver (by sending BEGIN for you)
DDL inside a transaction	Implicit COMMIT before and after	Fully transactional: can be rolled back
One statement fails in a transaction	Only that statement is rolled back	The whole transaction is aborted
COMMIT inside a procedure	Always allowed	Only when the CALL is not inside an outer transaction

See the difference: two sessions

Create an empty test table on both sides, open two sessions to each database, and run the same steps.

ORACLE · Session 1

SQL> INSERT INTO autoc_test VALUES (10);

1 row created.

POSTGRESQL · Session 1

postgres=# INSERT INTO autoc_test VALUES (10);
INSERT 0 1

ORACLE · Session 2

SQL> SELECT COUNT(*) FROM autoc_test;

  COUNT(*)
----------
         0

The row is not visible yet: session 1's transaction is still open.

POSTGRESQL · Session 2

postgres=# SELECT COUNT(*) FROM autoc_test;
 count
-------
     1

The row is already committed: psql runs every statement in its own transaction.

Oracle: autocommit is a client feature

The Oracle server has no autocommit mode at all. A transaction starts with the first DML statement and ends only with COMMIT, ROLLBACK, or an implicit commit. Some clients and drivers simulate autocommit by sending COMMIT after every statement:

SQL*Plus

SQL> SHOW AUTOCOMMIT
autocommit OFF                  -- SQL*Plus default
SQL> SET AUTOCOMMIT ON           -- commit after every statement
SQL> SET AUTOCOMMIT 100          -- commit after every 100 statements

Two Oracle behaviours often surprise people:

• DDL commits implicitly. Any open DML in your session is committed before a DDL statement runs, and the DDL itself commits when it finishes.
• SQL*Plus commits on a normal exit. EXITCOMMIT is ON by default, so typing EXIT commits your open transaction. Set SET EXITCOMMIT OFF if you want an exit to roll back instead.

ORACLE · Session 1

SQL> INSERT INTO autoc_test VALUES (20);
1 row created.

SQL> CREATE TABLE new_table (i NUMBER);
Table created.               -- the INSERT is now committed too

POSTGRESQL · Session 2

SQL> SELECT COUNT(*) FROM autoc_test;

  COUNT(*)
----------
         1

PostgreSQL: always on, unless you open a transaction

The PostgreSQL server runs every statement in its own transaction unless you start a block with BEGIN (or START TRANSACTION). There is no server parameter to change this; the old autocommit setting was removed in version 7.4. "Autocommit off" in a client simply means the client sends BEGIN for you before the first statement.

psql

postgres=# \echo :AUTOCOMMIT
on
postgres=# \set AUTOCOMMIT off       -- psql now sends BEGIN automatically

-- or put it in ~/.psqlrc to make it permanent (think twice, see below)

The explicit way, which works the same in every client:

PostgreSQL

BEGIN;
INSERT INTO autoc_test VALUES (30);
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;            -- or ROLLBACK;

Client and driver defaults

What actually happens depends on the tool, and the defaults are not what many people expect:

Client / driver	Oracle default	PostgreSQL default	How to change
SQL*Plus / psql	Autocommit off	Autocommit on	SET AUTOCOMMIT ON / \set AUTOCOMMIT off
SQL Developer / pgAdmin	Off (commit button)	On (toggle in the query tool)	Tool preferences
DBeaver	On (per connection setting)	On	Toolbar: Auto-commit / Manual commit
JDBC (any database)	On	On	conn.setAutoCommit(false)
Python: python-oracledb / psycopg2 / psycopg 3	Off	Off	conn.autocommit = True
Spring @Transactional, Hibernate	Framework manages it	Framework manages it	Transaction boundaries in code

Good news for Java migrations: JDBC is autocommit-on by default for both databases, and most Java applications already set transaction boundaries explicitly. The surprises usually come from PL/SQL logic, scripts and Python jobs.

GOTCHA 1DDL is transactional in PostgreSQL

In Oracle a DDL statement commits everything before it. In PostgreSQL, DDL takes part in the transaction like any other statement, and can be rolled back:

ORACLE · Rollback after DDL

SQL> INSERT INTO autoc_test VALUES (40);
SQL> CREATE TABLE t2 (i NUMBER);
SQL> ROLLBACK;
Rollback complete.
-- t2 still exists, and the row with 40 is committed

POSTGRESQL · Rollback after DDL

postgres=# BEGIN;
postgres=*# INSERT INTO autoc_test VALUES (40);
postgres=*# CREATE TABLE t2 (i int);
postgres=*# ROLLBACK;
-- t2 does not exist, and the row is gone

This is a big advantage for deployments: a whole schema migration script can run inside BEGIN … COMMIT and either apply completely or not at all. (A few commands, such as CREATE INDEX CONCURRENTLY, VACUUM and CREATE DATABASE, cannot run inside a transaction block.)

GOTCHA 2One error aborts the whole transaction

Oracle rolls back only the failed statement and keeps the rest of the transaction. PostgreSQL marks the whole transaction as failed, and every following statement is refused until you roll back:

ORACLE · Error in the middle

SQL> INSERT INTO autoc_test VALUES (50);
SQL> INSERT INTO autoc_test VALUES ('x');
ORA-01722: invalid number
SQL> INSERT INTO autoc_test VALUES (60);
1 row created.
SQL> COMMIT;                -- 50 and 60 are saved

POSTGRESQL · Error in the middle

postgres=# BEGIN;
postgres=*# INSERT INTO autoc_test VALUES (50);
postgres=*# INSERT INTO autoc_test VALUES ('x');
ERROR:  invalid input syntax for type integer: "x"
postgres=!# INSERT INTO autoc_test VALUES (60);
ERROR:  current transaction is aborted, commands
        ignored until end of transaction block
postgres=!# COMMIT;
ROLLBACK                    -- nothing is saved

Code ported from PL/SQL that "catches an error and carries on" needs a SAVEPOINT in PostgreSQL. In PL/pgSQL a BEGIN … EXCEPTION … END block creates one for you automatically. In psql, \set ON_ERROR_ROLLBACK interactive does the same for interactive sessions.

GOTCHA 3COMMIT inside a procedure fails when autocommit is off

PostgreSQL 11+ allows COMMIT and ROLLBACK inside procedures, but only if the CALL is not already inside a transaction block. With autocommit off, the client has already sent BEGIN, so the procedure's COMMIT fails:

PostgreSQL

CREATE OR REPLACE PROCEDURE load_batch()
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO autoc_test VALUES (1);
  COMMIT;
END;
$$;

postgres=# \set AUTOCOMMIT off
postgres=# CALL load_batch();
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function load_batch() line 4 at COMMIT

Fix: run the CALL in autocommit mode. In psycopg2, set conn.autocommit = True before calling the procedure. In JDBC, keep autocommit on for that call.

GOTCHA 4Forgotten open transactions

With autocommit off and no COMMIT, a PostgreSQL session sits idle in transaction. While it does, it holds its row locks and blocks other writers. It also stops VACUUM from cleaning dead rows, so tables bloat. Find these sessions:

PostgreSQL

SELECT pid, usename, application_name, state,
       now() - xact_start AS open_for, left(query, 60) AS last_query
FROM   pg_stat_activity
WHERE  state LIKE 'idle in transaction%'
ORDER  BY xact_start;

And put a safety net in place, at database or role level:

PostgreSQL

ALTER DATABASE appdb SET idle_in_transaction_session_timeout = '10min';

Oracle has the same risk, because an uncommitted transaction holds its locks and undo, but Oracle users are used to typing COMMIT. On PostgreSQL the danger mostly appears when someone switches autocommit off to "make it feel like Oracle".

Migration checklist

☐ Find PL/SQL that relies on an implicit commit from DDL (for example TRUNCATE or CREATE in the middle of a load) and add an explicit COMMIT where it matters.

☐ Find code that catches an exception and continues: it needs an EXCEPTION block or SAVEPOINT in PostgreSQL.

☐ Check every procedure that COMMITs is called in autocommit mode.

☐ Review Python jobs: psycopg2/psycopg 3 start a transaction by default. Commit explicitly, or set autocommit for simple scripts.

☐ Do not switch psql or DBeaver to autocommit off globally just to copy Oracle habits. Use explicit BEGIN … COMMIT instead.

☐ Set idle_in_transaction_session_timeout and monitor idle in transaction sessions.

☐ Remember that SQL*Plus commits on exit when you compare test results between the two systems.

Conclusion

Oracle leaves transaction boundaries to you; PostgreSQL commits each statement unless you open a transaction. Neither is better, but code written for one makes assumptions the other does not honour. When migrating, do not try to make PostgreSQL behave like Oracle by turning autocommit off everywhere. Instead, make transaction boundaries explicit in the code, handle errors with savepoints or exception blocks, and let PostgreSQL's transactional DDL work for you.

Have you been caught by one of these during a migration? Share it in the comments.

Tamilselvan Perumal · Oracle ACE Apprentice · Lead Product Engineer & Senior DBA