Search

Top 60 Oracle Blogs

Recent comments

autocommit

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:

PostgreSQL transaction management in procedures

TL;DR: AUTOCOMMIT is required when calling a procedure which has some COMMIT inside.

In version 11 PostgreSQL has introduced the possibility to start, commit or rollback transactions in PL/pgSQL procedures (stored or anonymous). Most of the demos have been run from the psql default AUTOCOMMIT on, like 2ndQuadrant and dbi-services blogs. But Bryn Llewellyn (YugaByte) raised an issue when running without AUTOCOMMIT OFF (which, coming from Oracle, looks like the right choice). Here is my investigation on this.