SUMMARY: This article covers PostgreSQL transactions. It defines the required keywords within the transaction syntax, explains the safeguards transactions provide in case of error and other benefits of transactions, then lists their parameters.

1. Transactions

         a. BEGIN

         b. COMMIT

         c. ROLLBACK

         d. SAVEPOINT

2. SQL query error

3. Benefits of transactions

4. Transaction parameters 

 

In this blog post, we are going to see what PostgreSQL transactions are and how they work.

Transactions

A transaction is a logical unit of work that contains one or more than one SQL statements where either all statements will succeed or all will fail. The SQL statements are NOT visible to other user sessions, and if something goes wrong, it won’t affect the database. 

BEGIN

The BEGIN keyword is used to start a transaction block.  

Example 

-bash-4.2$ ./psql  postgres -p 9033

psql (11.5)

Type "help" for help.

postgres=# begin;

BEGIN

postgres=# 

 

All the SQL statements that follow BEGIN will be executed as a single transaction unit. The transaction block will end after it reaches the COMMIT or ROLLBACK keywords, which we need to provide explicitly.

COMMIT

The COMMIT keyword saves changes to the database. 

Example

postgres=# begin;

BEGIN

postgres=# create table test(n int);

CREATE TABLE

postgres=# insert into test values(1);

INSERT 0 1

postgres=# end transaction; 

COMMIT

 

In this example, a table and row has been created in the database, and now other users who have connected to another session/terminal can access this table, provided if they have permission to access it. 

Please note: The COMMIT and END TRANSACTION keywords are same. 

ROLLBACK 

As the name suggests, ROLLBACK undoes the changes that were issued in the transaction block before it.

Example 

postgres=# begin;

BEGIN

postgres=# delete from test;

DELETE 1

postgres=# drop table test;

DROP TABLE

postgres=# rollback;

ROLLBACK

postgres=# 

 

After firing the ROLLBACK command, despite the DROP TABLE statement the table “test” remains in the database and also keeps the data as it is, because we had issued the ROLLBACK statement at the end of the transaction block. 

Please note: In PostgreSQL, we can rollback DDL objects as well. 

SAVEPOINT

SAVEPOINT is a boundary defined within a transaction that allows for a partial rollback.

It gives the user the ability to roll the transaction back to a certain point without rolling back the entire transaction.

Example 

postgres=# begin;

BEGIN

postgres=# create table my_table(n int);

CREATE TABLE

postgres=# insert into my_table values (1);

INSERT 0 1

postgres=# savepoint my_savepoint;

SAVEPOINT

postgres=# insert into my_table values(2);

INSERT 0 1

postgres=# insert into my_table values(3);

INSERT 0 1



postgres=# select * from my_table;

 n 

---

 1

 2

 3

(3 rows)



postgres=# rollback to my_savepoint; 

ROLLBACK

postgres=# select * from my_table;   

 n 

---

 1

(1 row)



postgres=# commit;

COMMIT

postgres=# 

 

In this example:  

1. First, the user creates a table and inserts 1 row. A SAVEPOINT with the name “my_savepoint” is created after that.

2. Then, 2 more rows inserted in the table, so a total or 3 rows are visible to the user now. 

3. After firing ROLLBACK to “my_savepoint,” it will undo all the changes that have been done after the point when the SAVEPOINT was created.

4. As a result of step c, only 1 row is now visible to the user.

5. The transaction block is still active, so to finish we need to fire either COMMIT or a second ROLLBACK command.

Please note: We can have multiple SAVEPOINTs within a transaction block.

SQL query error 

If there is an error in the SQL query then entire transaction block will be aborted immediately and any SQL statement that was written before the error will automatically roll back.

Example

postgres=# begin;

BEGIN

postgres=# create table test123(n int);

CREATE TABLE

postgres=# select * from does_not_exist_table;

ERROR:  relation "does_not_exist_table" does not exist

LINE 1: select * from does_not_exist_table;

                      ^

postgres=# select 'Hi';

ERROR:  current transaction is aborted, commands ignored until end of transaction block

postgres=# rollback;

ROLLBACK

postgres=# \d test123

Did not find any relation named "test123".

postgres=# 

 

As we can see, table “test123” has been rolled back and cannot be found.

To avoid such a scenario where work is lost, using SAVEPOINT is a must. 

Example  

postgres=# begin;

BEGIN

postgres=# create table edb(n int);

CREATE TABLE

postgres=# insert into edb values (1);

INSERT 0 1

postgres=# savepoint my_point;

SAVEPOINT

postgres=# insert into edb values (2);

INSERT 0 1

postgres=# insert into edb values (3);

INSERT 0 1

postgres=# insert into does_not_exist values (1);

ERROR:  relation "does_not_exist" does not exist

LINE 1: insert into does_not_exist values (1);

                    ^

postgres=# insert into edb values (4);

ERROR:  current transaction is aborted, commands ignored until end of transaction block

postgres=# rollback to my_point;

ROLLBACK

postgres=# select * from edb;

 n 

---

 1

(1 row)

postgres=# commit;

COMMIT

postgres=# 

 

As we can see, only the data entered into the table “edb” before the SAVEPOINT has been preserved.

Benefits of transactions

  • Recover from user error: We can ROLLBACK (erase) a mistake. For instance, I want to delete a particular row from a table. I fired a DELETE statement without a WHERE condition (by mistake). Unfortunately that would delete all records from my table, but I just wanted to delete only a few rows. However, I can easily recover from my mistake if I am inside a  transaction block —  by firing the ROLLBACK command. 
  • As mentioned earlier, other concurrent user sessions would not get affected by a transaction unless it gets locked. In the example below, I have inserted 1 row into a table inside a transaction (following a BEGIN command), and am trying to drop the same table from another session. This will not work, because a lock is held on the table until the end of the transaction.

 

Example

psql (X terminal)

postgres=# create table test1(n int);

CREATE TABLE

postgres=# begin;

BEGIN

postgres=# insert into test1 values(1);

INSERT 0 1

postgres=#

 

psql (Y terminal)

postgres=# drop table test1;

 

The session on the Y terminal will be in hung state until we end the transaction block in X terminal. 

Please refer to the PostgreSQL community documentation for locking: https://www.postgresql.org/docs/11/explicit-locking.html.

  • Data is not visible to other users until the transaction block is finished.

 

Example

psql (X terminal) 

postgres=# create table test3(n int);

CREATE TABLE

postgres=# insert into test3 values(1);

INSERT 0 1



postgres=# begin;

BEGIN

postgres=# insert into test3 values(2);

INSERT 0 1

postgres=# select * from test3;

 n 

---

 1

 2

(2 rows)



postgres=# 

 

psql (Y terminal)  

postgres=# select * from test3;

 n 

---

 1                        ← only showing committed row.

(1 row)

 

In the Y terminal, we won’t be able to see the second row until the transaction block gets committed. 

Transaction parameters

The characteristics of transactions can be set within the BEGIN keyword:

postgres=# \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

 

The WORK and TRANSACTION keywords are optional in a BEGIN command.

Example

postgres=# begin transaction;

BEGIN

postgres=# \c 

You are now connected to database "postgres" as user "postgres".

postgres=# begin work;

BEGIN

postgres=#

 

In transaction_mode, we have the following options:

1. READ WRITE – User can perform R/W operations.

2. READ ONLY – User can perform Read operation.

Example

postgres=# begin  read only;

BEGIN

postgres=# create table rty(n int);

ERROR:  cannot execute CREATE TABLE in a read-only transaction

 

3. ISOLATION LEVEL –  User can define what data the transaction can see when other transactions are running concurrently. There are 4 different levels:

 

        i) READ COMMITTED – This is the default isolation level in Postgres. At this level, a query within a transaction will pick up changes made by another committed transaction. 

Example

psql (X terminal)

postgres=# begin isolation level read committed;  

BEGIN

postgres=# select * from test1;

n 

---

(0 rows)

 

psql (Y terminal)

postgres=# insert into test1 values (8);

INSERT 0 1

postgres=# 

 

psql (X terminal) 

postgres=# select * from test1;     <--Data is visible

 n 

---

 8

(1 row)

 

        ii) READ UNCOMMITTED is not supported in PostgreSQL. Even if we specify it, it will be treated the same as READ COMMITTED.  

        iii) In SERIALIZABLE  and iv) REPEATABLE READ –  Committed rows in other sessions are only visible to the transaction block when it ends.

Example

psql  (X terminal)

[tushar@localhost bin]$ ./psql  postgres -U postgres

psql (11.5)

Type "help" for help.



postgres=# begin isolation level serializable ;

BEGIN

postgres=# select * from test1;

 n 

---

(0 rows)

 

psql (Y terminal)

postgres=# insert into test1 values (13);

INSERT 0 1

postgres=# 

 

If we go back again to the X terminal, the row added in the Y terminal is not visible until we end the transaction block. 

postgres=# select * from test1;

 n 

---

(0 rows)



postgres=# 

 

The difference between these two isolation levels is that REPEATABLE READ allows Serialization Anomaly whereas SERIALIZABLE does not.

Please refer to the Postgres community documentation for more on transaction_mode parameters: https://www.postgresql.org/docs/11/sql-set-transaction.html.

Hope this helps!