A transaction is atomic, consistent, isolated, and durable. This makes transactions extremely useful for atomic guarantees.
A PostgreSQL transaction follows the ACID (Atomicity, Consistency, Isolation, Durability) properties which guarantee that the database remains consistent despite the failures.
This tutorial explores how we can build the transactional queries in PostgreSQL.
Setup Sample Table
To better understand how to work with PostgreSQL transactions, let us set up a sample table and data as shown in the following queries:
id serial PRIMARY KEY,
name text,
price numeric
);
The query above should create a basic table called products. We can then insert sample data as shown:
The query above should create a basic table called products. We can then insert sample data as shown:
INSERT INTO products (name, price) VALUES
('Product A', 10.99),
('Product B', 15.99),
('Product C', 7.99);
Let us proceed and discuss how to work with PostgreSQL transactions.
PostgreSQL BEGIN TRANSACTION Clause
We use the BEGIN TRANSACTION statement to denote the start of a transaction in PostgreSQL.
This opens a transaction block which allows us to define a set of SQL statements that are executed as a single unit of work.
The clause syntax is as follows:
You can use either the BEGIN WORK or BEGIN TRANSACTION clause. Each works similarly.
To update the prices of multiple products in the products table, we can run a query as shown in the following:
-- Increase the price of Product A by 5 units
UPDATE products SET price = price + 5 WHERE name = 'Product A';
-- Decrease the price of Product B by 2 units
UPDATE products SET price = price - 2 WHERE name = 'Product B';
COMMIT;
In the given example, we begin a transaction using BEGIN. Then, we execute two separate UPDATE statements to modify the prices of products, “Product A” and “Product B”. Finally, we commit the changes using COMMIT to make them permanent.
If any error occurs during the execution of the transaction, you can roll back the changes by using the ROLLBACK statement instead of COMMIT. This ensures that none of the changes that are made within the transaction are applied to the database.
An example demonstration is as follows:
-- Increase the price of Product A by 5 units
UPDATE products SET price = price + 5 WHERE name = 'Product A';
-- Decrease the price of Product B by 2 units
UPDATE products SET price = price - 2 WHERE name = 'Product B';
-- Roll back the changes made within the transaction
ROLLBACK;
In this case, the statement rolls back the changes in the event of an error.
PostgreSQL Nested Transactions
PostgreSQL supports the nested transactions which allow us to create transactions within transactions. However, it’s important to note that PostgreSQL does not fully support the nested transactions as it treats all nested transactions as savepoints.
To create a nested transaction, we can use the SAVEPOINT and ROLLBACK TO SAVEPOINT statements as shown in the following:
SAVEPOINT savepoint_name;
ROLLBACK TO SAVEPOINT savepoint_name;
COMMIT;
It is good to keep in mind that rolling back to a savepoint only affects the operations that are performed after the savepoint, not the entire transaction.
Hence, a rollback to the savepoint removes all the changes that are made after that savepoint which allows us to restore the transaction to a previous state.
Conclusion
We discussed about creating the PostgreSQL transactions using the BEGIN TRANSACTION and the COMMIT/ROLLBACK clauses.
Transactions are practical tools which allow us to group the related SQL statements together and ensure that they either succeed or fail.
