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!
Popular Links
Featured Links
Relevant Blogs
EDB Tutorial: How to run a TPC-E-like benchmark easily
The TPC Benchmark(TM) E (TPC-E) was released back in 2007 as the successor to the TPC Benchmark(TM) C (TPC-C) for an OLTP type workload. To briefly recap, the TPC-E models...EDB Tutorial: How to Use Privilege Analysis to Identify [Un]Used Privileges
Have you ever searched for unnecessary granted privileges in your database? To find these, you need to know 1. The granted privileges 2. How many of the granted privileges have...EDB Tutorial: How To Get the Most Out of EDB Query Advisor
Enterprise-class databases require DBAs who are responsible for maximizing query performance. Therefore, when it comes to large scale deployment of databases, minimizing the work of DBA is important. One of...More Blogs
EDB Tutorial: How to Configure Databases for EDB JDBC SSL Factory Classes
The fact is, SSL configuration required by EDB JDBC SSL factory classes is considered confusing and challenging by many EDB customers. This stems from the concepts behind the SSL handshake...