Transaction management in PostgreSQL and what is different from Oracle
TL;DR: Everything is different about transaction behavior. This may also change your ideas about “database independent” applications.
I like to explain some PostgreSQL concepts from an oracle DBA point of view. There are many things that are different in the two RDBMS and it is important to understand them.
Auto commit
Here is a short example where I create a table, insert one row and rollback:
psql -U postgres demo ### PostgreSQL 11.4 drop table if exists DEMO;
create table DEMO as select 1 n,current_timestamp t;
select * from DEMO;
insert into DEMO values (2,current_timestamp);
select * from DEMO;
rollback;
select * from DEMO;
insert into DEMO values (3,current_timestamp);
\q
I never executed any commit but my changes are saved and published. This is auto-commit by default in psql. I think I prefer the Oracle default where the commit is my decision, and only what I’ve committed is visible:
sqlplus demo/demo@//localhost/PDB1 ### Oracle Database 19.4 column t format a35
exec for i in (select table_name from user_tables where table_name='DEMO') loop execute immediate 'drop table "'||i.table_name||'" cascade constraints'; end loop;
create table DEMO as select 1 n,current_timestamp t from dual;
select * from DEMO;
insert into DEMO values (2,current_timestamp);
select * from DEMO;
rollback;
select * from DEMO;
insert into DEMO values (3,current_timestamp);
quit
So, is Oracle the best for transaction management?
Well… autocommit default is only one thing. Let’s go further.
First, this is only a client setting. SQL*Plus has it set to off:
SQL> show autocommit
autocommit OFFBut JDBC drivers may have it set to on. So, rather than a nice thing about the database, it is just a nice default of SQL*Plus.
Commit on exit
And wait… are all sqlplus defaults so smart? Let’s come back to my Oracle database where I committed nothing:
sqlplus demo/demo@//localhost/PDB1 ### Oracle Database 19.4 column t format a35
select * from DEMO;
quit
Are sqlplus defaults so smart? I didn’t commit the last insert but it was committed by default. Not because of auto-commit but because of exit-commit:
SQL> show exitcommit
exitcommit ONThat’s not a nice default. If I quit without saying ‘commit’ I want a rollback. It is highly recommended to set exit-commit off to avoid any unexpected commit. (also recommendedvfor scripts having a WHENEVER SQLERROR EXIT that mentions ROLLBACK because COMMIT is the default).
DDL auto-commit
And that’s not all… The first row (n=1) was never explicitly committed. It was inserted with DDL (CREATE TABLE) and DDL are always auto-committed. That’s the Oracle Database, nothing to do with the client: you cannot be transactional with DDL.
Start transaction
Back to PostgreSQL, the default is auto-commit but I have the choice. I can explicitly start a transaction and then I’ll have to explicitly commit it.
psql -U postgres demo ### PostgreSQL 11.4
start transaction;
drop table if exists DEMO;
create table DEMO as select 1 n,current_timestamp t;
select * from DEMO;
insert into DEMO values (2,current_timestamp);
select * from DEMO;
rollback;
select * from DEMO;
start transaction;
insert into DEMO values (3,current_timestamp);
\q
psql -U postgres demo ### PostgreSQL 11.4
select * from DEMO;
\q
See? I have the 3 rows from the first run which were all auto-committed. But now that I explicitly started a transaction, everything was transactional, even the DDL: the DROP TABLE, the CREATE TABLE, the INSERT were explicitly rolled-back. And even the last INSERT was implicitly rolled-back on exit.
Now, who is the winner in transaction management? There’s even more: you can send a multi-statement command to the backend and it will be processed as an implicit transaction.
Note that START TRANSACTION is the ANSI SQL syntax, but PostgreSQL accepts also BEGIN, BEGIN TRANSACTION and BEGIN WORK.
Set Transaction name
Don’t think that there are no “begin transaction” in Oracle. The SET TRANSACTION starts it, in order to define the isolation level, or simply to put a name to the transaction.
This example looks at the transaction address in V$SESSION and V$TRANSACTION
sqlplus demo/demo@//localhost/PDB1 ### Oracle Database 19.4select saddr,taddr from v$session
where sid=sys_context('userenv','sid');
select count(*) from DEMO;
select saddr,taddr from v$session
where sid=sys_context('userenv','sid');
update DEMO set t=current_timestamp;
select saddr,taddr from v$session
where sid=sys_context('userenv','sid');
rollback;
select saddr,taddr from v$session
where sid=sys_context('userenv','sid');
set transaction name 'my_transaction';
select saddr,taddr from v$session
where sid=sys_context('userenv','sid');
quit
This illustrates when a transaction starts (visible with a TADDR in V$SESSION and a row in V$TRANSACTION): the first INSERT/DELETE/UPDATE/MERGE/SELECT FOR UPDATE or a SET TRANSACTION
Autonomous Transaction
Not available in PostgreSQL but possible in Oracle: we can have nested transactions. This is very convenient in some limited cases, like logging the error in the database (and commit this insert) before the rollback of the transaction.
sqlplus demo/demo@//localhost/PDB1 ### Oracle Database 19.4 set serveroutput on
create table DEMO2 as select * from DEMO;
select saddr,taddr from v$session
where sid=sys_context('userenv','sid');
select count(*) from DEMO;
select saddr,taddr from v$session
where sid=sys_context('userenv','sid');
update DEMO set t=current_timestamp;
select saddr,taddr from v$session
where sid=sys_context('userenv','sid');
select addr,ses_addr from v$transaction;
declare
pragma autonomous_transaction;
begin
update DEMO2 set t=current_timestamp;
for i in ( select addr,ses_addr from v$transaction) loop
dbms_output.put_line(
'Transaction: ADDR: '||i.addr||' SES_ADDR: '||i.ses_addr
);
end loop;
rollback;
end;
/
rollback;
quit
This shows that there can be multiple transactions for the same session. The PADDR is only the address or the top-level one.
User Call level rollback
(Note that I’ve previously written “statement-level rollback” but this is misleading because a statement can be executed in a recursive call, as in a PL/SQL block. What I show here is about a user call statement which can actually be a SQL statement or a PL/SQL block or a procedure call ).
Still in Oracle, when a statement fails in a user call, the modifications done by this user call are rolled back, but not the previous modifications. The transaction can continue (like re-try, or do an alternative change):
sqlplus demo/demo@//localhost/PDB1 ### Oracle Database 19.4
column t format a35
exec for i in (select table_name from user_tables where table_name='DEMO') loop execute immediate 'drop table "'||i.table_name||'" cascade constraints'; end loop;
create table DEMO as select 1 n,current_timestamp t from dual;
alter table DEMO add unique(n);
select * from DEMO;
insert into DEMO values (2,current_timestamp);
select * from DEMO;
insert into DEMO values (1,current_timestamp);
select * from DEMO;
quit
ERROR: current transaction is aborted
That’s different in PostgreSQL where the transaction cannot continue when you encounter an error:
psql -U postgres demo ### PostgreSQL 11.4
drop table if exists DEMO;
start transaction;
create table DEMO(n,t) as select 1 n,current_timestamp t;
alter table DEMO add unique(n);
select * from DEMO;
insert into DEMO values (2,current_timestamp);
select * from DEMO;
insert into DEMO values (1,current_timestamp);
select * from DEMO;
rollback;
select * from DEMO;
\q
Here I rolled back. But I can also commit to terminate the transaction, but it will rollback anyway:
ROLLBACK
Savepoint
Actually, we can achieve user call level rollback even in PostgreSQL, using savepoints:
psql -U postgres demo ### PostgreSQL 11.4
start transaction;
drop table if exists DEMO;
create table DEMO(n,t) as select 1 n,current_timestamp t;
alter table DEMO add unique(n);
select * from DEMO;
insert into DEMO values (2,current_timestamp);
select * from DEMO;
savepoint my_before_insert_savepoint;
insert into DEMO values (1,current_timestamp);
select * from DEMO;
rollback to my_before_insert_savepoint;
select * from DEMO;
\q
With Oracle, you don’t need to because there is an implicit savepoint before each execution.
Well, the PostgreSQL client psql do the same:
\set ON_ERROR_ROLLBACK onand then psql will automatically create a “pg_psql_temporary_savepoint”.
PostgreSQL 11 Procedures
Before version 11 all commands (which can be one statement, or multiple ones, or PL/pgSQL anonymous or stored procedures) were run in an atomic context, with no intermediate commits possible without a roundtrip with the client. Like this:
psql -U postgres demo ### PostgreSQL 11.4
drop table if exists DEMO;
create table DEMO(n,t) as select 1 n,current_timestamp t;
alter table DEMO add unique(n);
create or replace procedure my_inserts(n1 int, n2 int) as
$$
begin
for i in n1..n2 loop
insert into DEMO values (i,current_timestamp);
end loop;
end;
$$ language plpgsql;
call my_inserts(10,12);
call my_inserts(5,15);
select * from DEMO;
\q
The first call has inserted values 10 and 12 and they were committed because I run in the default AUTOCOMMIT. But the second call has encountered a duplicate key and the whole was rolled-back. But in PostgreSQL 11 I can add an intermediate commit so that the first rows are committed before I encounter the error.
psql -U postgres demo ### PostgreSQL 11.4
drop table if exists DEMO;
create table DEMO(n,t) as select 1 n,current_timestamp t;
alter table DEMO add unique(n);
create or replace procedure my_inserts(n1 int, n2 int) as
$$
begin
for i in n1..n2 loop
insert into DEMO values (i,current_timestamp);
commit;
end loop;
end;
$$ language plpgsql;
call my_inserts(10,12);
call my_inserts(5,15);
select * from DEMO;
\q
Here the rows 5 to 9 have been committed before encountering the exception for row 10.
Still, no need for an explicit BEGIN here. The COMMIT in the loop will end the transaction (started implicitly on the server as I did no BEGIN before) and start a new one. The last transaction started will be committed implicitly.
Additionally, you can look at the timestamps in the two previous demos. In PostgreSQL, current_timestamp is consistent in the transaction.
Invalid transaction termination
Here is the same, but with AUTOCOMMIT off:
psql -U postgres demo ### PostgreSQL 11.4
drop table if exists DEMO;
create table DEMO(n,t) as select 1 n,current_timestamp t;
alter table DEMO add unique(n);
create or replace procedure my_inserts(n1 int, n2 int) as
$$
begin
for i in n1..n2 loop
insert into DEMO values (i,current_timestamp);
commit;
end loop;
end;
$$ language plpgsql;
\set AUTOCOMMIT off
call my_inserts(10,12);
call my_inserts(5,15);
select * from DEMO;
rollback;
select * from DEMO;
\q
Actually, this is what made me start this blog post. But that’s already a lot, and further investigation on this will be on the next post:
AUTOCOMMIT and PostgreSQL transaction management in procedures
Implicit transactions
Ok, just one more on this. The error above is not really caused by AUTOCOMMIT but by the way psql handles AUTOCOMMIT. Actually, with PostgreSQL, the AUTOCOMMIT is not a BEGIN statement added by the psql client, but the backend server creating an implicit transaction when there is not already one. It is even the opposite: when AUTOCOMMIT is off, the client adds a “BEGIN” so that the server does not create an implicit one. Which means that the “invalid transaction termination” occurs also without auto-commit when we CALL the procedure from an explicit transaction.
\echo :AUTOCOMMIT
begin transaction;
do $$
begin
update DEMO set t=null;
rollback;
end
$$;
rollback;\set AUTOCOMMIT off
\echo :AUTOCOMMIT
do $$
begin
update DEMO set t=current_timestamp;
commit;
end
$$;
This long post is only a short sample of the many differences between Oracle and PostgreSQL transaction management. And I’ve only used psql and sqlplus here. Do you want to test the same from Java JDBC and Python psycopg2?
Comments welcome on Twitter:
