PostgreSQL - ROLLBACK

What is ROLLBACK in PostgreSQL?

In PostgreSQL, ROLLBACK removes the changes made in a transaction before they are permanently saved. It is used to undo mistakes and restore the database to its original state.

Relationship Between ROLLBACK and COMMIT

• COMMIT permanently saves changes made in a transaction.
• ROLLBACK removes the changes if something goes wrong.
• Both work together to maintain database integrity.

When do we need ROLLBACK?

Imagine you transfers the money from one bank account to another. First, the amount is deducted from your account. If the deposit to the other account fails due to a system issue, the deducted money should be returned. This is where ROLLBACK helps − it cancels incomplete transactions and prevents data errors.

Importance of ROLLBACK in Transactions

• Preventing Data Errors − If an operation goes wrong, ROLLBACK cancels unwanted changes.
• Maintains Data Consistency − This check whether valid data is saved.
• Protects Against System Failures − If a system crashes mid-transaction, ROLLBACK prevents partial updates.
• Controlled Testing − The developer can test the transactions without makes any permanent changes.
• Enhances User Trust − The banking, e−commerce, and other critical systems use ROLLBACK to protect user transactions.

How ROLLBACK Works?

Suppose an online shopping site updates inventory when someone places an order. The process involves the following −

• It reduce the stocks for the buying items.
• It charges the customer payments.
• Suppose the payment fails, the stock should not be reduced. ROLLBACK ensures the stock is restored if the transaction is incomplete.

Example of Rollback in PostgreSQL

Now, please connect to PostgreSQL − COMMIT chapters where we entered the values of one new user. And it is supposed to change because of the wrong entry, so rollback works here.

i. Suppose, this is an wrong entry to the existable table −

ii. Next, use the ROLLBACK command to change the information of row number 6.

BEGIN;

INSERT INTO employees (id, name, position, salary, department, hire_date)
VALUES (6, 'Rahul', 'Developer', 72000.00, 'Engineering', '2023-10-01');

-- If something goes wrong, use ROLLBACK
ROLLBACK;

iii. Try to run select statement to check the new information present on the table.

SELECT * FROM employees;

While running the select statement, we get the following outcome with new result −

Therefore, this way ROLLBACK works in table operations or transactions.