Working with PostgreSQL transactions

PostgreSQL provides you with highly advanced transaction machinery that offers countless features to developers and administrators alike. In this section, we will look at the basic concept of transactions.

The first important thing to know is that, in PostgreSQL, everything is a transaction. If you send a simple query to the server, it is already a transaction. Here is an example:

test=# SELECT now(), now();
              now              |             now 
-------------------------------+-------------------------------
 2019-07-10 14:25:08.406051+02 | 2019-07-10 14:25:08.406051+02
(1 row)

In this case, the SELECT statement will be a separate transaction. If the same command is executed again, different timestamps will be returned.

If more than one statement has to be a part of the same transaction, the BEGIN statement must be used, as follows:

test=# \h BEGIN
Command: BEGIN
Description: start a transaction block
Syntax:
BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]

where transaction_mode is one of:

 ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
 READ WRITE | READ ONLY
 [ NOT ] DEFERRABLE

URL: https://www.postgresql.org/docs/12/sql-begin.html

The BEGIN statement will ensure that more than one command is packed into a transaction. Here is how it works:

test=# BEGIN; 
BEGIN
test=# SELECT now();
              now 
-------------------------------
 2019-07-10 14:26:55.665943+02
(1 row)

test=# SELECT now();
              now 
-------------------------------
 2019-07-10 14:26:55.665943+02
(1 row)

test=# COMMIT;
COMMIT 

The important point here is that both timestamps will be identical. As we mentioned earlier, we are talking about transaction time.

To end the transaction, COMMIT can be used:

test=# \h COMMIT
Command: COMMIT
Description: commit the current transaction
Syntax:
COMMIT [ WORK | TRANSACTION ] [ AND [ NO ] CHAIN ]

URL: https://www.postgresql.org/docs/12/sql-commit.html

There are a couple of syntax elements here. You can just use COMMIT, COMMIT WORK, or COMMIT TRANSACTION. All three commands have the same meaning. If this is not enough, there's more:

test=# \h END  
Command:  END 
Description: commit the current transaction 
Syntax: 
END [ WORK | TRANSACTION ] 

The END clause is the same as the COMMIT clause.

ROLLBACK is the counterpart of COMMIT. Instead of successfully ending a transaction, it will simply stop the transaction without ever making things visible to other transactions, as shown in the following code:

test=# \h ROLLBACK
Command: ROLLBACK
Description: abort the current transaction
Syntax:
ROLLBACK [ WORK | TRANSACTION ] [ AND [ NO ] CHAIN ]

URL: https://www.postgresql.org/docs/12/sql-rollback.html 

Some applications use ABORT instead of ROLLBACK. The meaning is the same. What is new in PostgreSQL is the concept of a chained transaction. What is the point of all this? The following listing shows an example:

test=# SHOW transaction_read_only;
 transaction_read_only 
-----------------------
 off
(1 row)

test=# BEGIN TRANSACTION READ ONLY;
BEGIN
test=# SELECT 1;
 ?column? 
----------
        1
(1 row)

test=# COMMIT AND CHAIN;
COMMIT
test=# SHOW transaction_read_only;
 transaction_read_only 
-----------------------
 on
(1 row)

test=# COMMIT AND NO CHAIN;
COMMIT
test=# SHOW transaction_read_only;
 transaction_read_only 
-----------------------
 off
(1 row)

test=# COMMIT;
psql: WARNING: there is no transaction in progress
COMMIT

Let's go through this example step by step:

1. Display the content of the transaction_read_only setting. It is off because, by default, we are in read/write mode.
2. Start a read-only transaction using BEGIN. This will automatically adjust the transaction_read_only variable. 
3. Commit the transaction using AND CHAIN, then PostgreSQL will automatically start a new transaction featuring the same properties as the previous transaction.

In our example, we will also be in read-only mode, just like the transaction before. There is no need to explicitly open a new transaction and set whatever values again, which can dramatically reduce the number of roundtrips between application and server. If a transaction is committed normally (= NO CHAIN) the read-only attribute of the transaction will be gone.