Introduction:
The word “Transaction” will ring so many bells and whistles, but in this topic’s context a “Transaction” is a call to a database function or procedure that can have one or multiple DML operations like Insert, Update, or Delete.
For database administrators tasked with managing PostgreSQL databases, ensuring data integrity and consistency during complex data manipulation operations is paramount. If you’re transitioning from Oracle or need to handle complex nested DML (Data Manipulation Language) operations, understanding how to achieve rollback to a savepoint in PostgreSQL is crucial.
Rolling back to a savepoint in PostgreSQL allows you to revert the database to a specific point within a transaction, rather than rolling back the entire transaction. This is useful when you want to undo part of a transaction while preserving changes made before the savepoint.
Transitioning from Oracle: A Brief Overview
SAVEPOINT and ROLLBACK TO are well-known concepts for those experienced with Oracle databases. These commands in Oracle enable the creation of savepoints within nested transactions and provide the ability to roll back to these savepoints when necessary.
Here’s how to achieve rollback to a savepoint in Oracle:
- Start a transaction block using `BEGIN`
- Set a Savepoint, use the SAVEPOINT command mentioned below:
`SAVEPOINT <savepoint name>;` - Perform one or more Database Operations within the transaction, such as INSERT, UPDATE, or DELETE
- In between these multiple Database operations, if an error or on any restricted Business Logic occurs then Roll Back to the Savepoint.
- To roll back to the savepoint, use the below command
`ROLLBACK TO <savepoint name>;` - At the end of the Business Logic, use a COMMIT or ROLLBACK, as per the need.
However, the transition to PostgreSQL may raise questions about the relevance of SAVEPOINT and ROLLBACK TO. PostgreSQL adheres to SQL:2016 standards, ensuring compatibility and consistency with your SQL database knowledge. PostgreSQL supports these commands only in developer IDE’s but not in a Database function or a procedure where we need to handle a Business Scenario.
It means, even though PostgreSQL has the rollback and savepoint keywords, they cannot be used inside a stored procedure or function. If we use them, we get below error:
SQL Error [0A000]: ERROR: unsupported transaction command in PL/pgSQL
The good news is that we can achieve Rollback To Savepoint in PostgreSQL procedures and functions.
Ironically you never use the keywords savepoint or rollback as they are automatically invoked in a BEGIN-EXCEPTION-BLOCK. The below example explains how to do it.
Understanding Rollback to Savepoint in PostgreSQL
To grasp how to implement rollback to savepoint in PostgreSQL, let’s consider a practical example with our favorite two tables “osdb_dept” and “osdb_emp“, with “osdb_dept” serving as the parent table and linked to “osdb_emp” child table, through the “dept_id” column.
Scenario:
- Start Loop-1 through each department in the parent table “
osdb_dept" - Start another Loop-2 (inside Loop-1) through each employee in the child table “
osdb_emp"for a given department from Loop-1. - Start SAVEPOINT
- If the Department Location is:
- Hyderabad then set commission to 2000
- Bangalore then set commission to 2500
- Others then set commission to 1500
- If Employee Role is:
- Manager then set salary to salary + 5%
- Accountant then set salary to salary + 8%
- Others then set salary to salary + 10%
- If Employee Grade is:
- M1 and status = ACTIVE then set bonus to bonus + 1000
- A1 and status = ACTIVE then set bonus to bonus + 500
- NULL or status = NEW_HIRE then rollback all changes done so far, for that employee only.
- Update all Employee bonus to bonus + 300
Below is the script for creating the necessary tables and data:
drop table osdb_dept;
drop table osdb_emp;
create table osdb_dept
(dept_id int2
,dept_name text
,dept_location text);
insert into osdb_dept values(10,'ACCOUNTING','HYDERABAD');
insert into osdb_dept values(20,'HR','BANGALORE');
insert into osdb_dept values(30,'IT','HYDERABAD');
create table osdb_emp
(emp_id int2
,emp_name text
,dept_id int2
,role_name text
,grade_name text
,status text
,salary int4
,commission int4
,bonus int4
,comments text);
insert into osdb_emp values(101,'Govind' ,10,'Accountant','A1','ACTIVE' ,10000,200,500 ,null);
insert into osdb_emp values(102,'Divya' ,20,'HR' ,'A1','ACTIVE' ,12000,300,600 ,null);
insert into osdb_emp values(103,'Vinay' ,30,'Manager' ,'M1','ACTIVE' ,32000,600,1000 ,null);
insert into osdb_emp values(104,'Krishna' ,30,'Manager' ,'M1','NEW_HIRE' ,32000,600,1000 ,null);
insert into osdb_emp values(105,'Ramya' ,30,'Associate' ,'C1','ACTIVE' ,15000,300,600 ,null);
insert into osdb_emp values(106,'Sanjay' ,30,'DBA' ,'D1','ACTIVE' ,18000,400,700 ,null);select * from osdb_dept;
| DEPT_ID | DEPT_NAME | DEPT_LOCATION |
| 10 | ACCOUNTING | HYDERABAD |
| 20 | HR | BANGALORE |
| 30 | IT | HYDERABAD |
select * from osdb_emp;
| EMPID | EMP NAME | DEPTID | ROLENAME | GRADENAME | STATUS | SALARY | COMMISSION | BONUS |
| 101 | Govind | 10 | Accountant | A1 | ACTIVE | 10000 | 200 | 500 |
| 102 | Divya | 20 | HR | A1 | ACTIVE | 12000 | 300 | 600 |
| 103 | Vinay | 30 | Manager | M1 | ACTIVE | 32000 | 600 | 1000 |
| 104 | Krishna | 30 | Manager | M1 | NEW_HIRE | 32000 | 600 | 1000 |
| 105 | Ramya | 30 | Associate | C1 | ACTIVE | 15000 | 300 | 600 |
| 106 | Sanjay | 30 | DBA | D1 | ACTIVE | 18000 | 400 | 700 |
Below is the actual script that shows the implementation of rollback to savepoint in PostgreSQL pl/pgsql:
DO $
DECLARE
l_return_msg text;
l_dept_rec record;
l_emp_rec record;
BEGIN
FOR l_dept_rec IN (SELECT * FROM osdb_dept ORDER BY dept_id)
LOOP
--RAISE NOTICE 'l_dept_rec.dept_id = %', l_dept_rec.dept_id;
FOR l_emp_rec IN (SELECT * FROM osdb_emp
WHERE dept_id = l_dept_rec.dept_id ORDER BY emp_id)
LOOP
RAISE NOTICE 'emp_id = %', l_emp_rec.emp_id;
--BEGIN-EXCEPTION Block - START
BEGIN --> This is equal to SAVEPOINT.
–-> So for each emp record this serves as a savepoint
update osdb_emp
set commission = case when l_dept_rec.dept_location = 'HYDERABAD'
then 2000
when l_dept_rec.dept_location = 'BANGALORE'
then 2500
else 1500 end
where emp_id = l_emp_rec.emp_id;
--
update osdb_emp
set salary = case when role_name = 'Manager'
then salary + (salary*5/100)
when role_name = 'Accountant'
then salary + (salary*8/100)
else salary + (salary*10/100) end
where emp_id = l_emp_rec.emp_id;
--
if l_emp_rec.grade_name is null or l_emp_rec.status = 'NEW_HIRE'
then
l_return_msg := 'Rollback for NEW_HIRE, ' || l_emp_rec.emp_name
|| ' (emp_id = ' || l_emp_rec.emp_id || ')';
RAISE EXCEPTION USING errcode = '50001';
--> The above raise statement is not rollback, but when this
--> raise gets caught in the exception block, Postgres executes
--> rollback to the DML's within that BEGIN-EXCEPTION Block.
else
update osdb_emp
set bonus = case when role_name = 'Manager'
then bonus + 1000
when role_name = 'Accountant'
then bonus + 500
else bonus end
where emp_id = l_emp_rec.emp_id;
end if;
--
EXCEPTION
WHEN SQLSTATE '50001' THEN --> This is where rollback happens
RAISE NOTICE '%', l_return_msg;
WHEN OTHERS THEN
l_return_msg := 'ERROR: ' || SQLERRM;
RAISE NOTICE '%', l_return_msg;
END;
--BEGIN-EXCEPTION Block - END
--
update osdb_emp set bonus = bonus + 300 where emp_id = l_emp_rec.emp_id;
--
END LOOP; -- For l_emp_rec
--
END LOOP; -- For l_dept_rec
END;
$$Script Output:
emp_id = 101
emp_id = 102
emp_id = 103
emp_id = 104
Rollback for NEW_HIRE, Krishna (emp_id = 104)
emp_id = 105
emp_id = 106
Explanation in short:
For employee ID 104, as per the logic for status = NEW_HIRE, salary and commission will not be updated but the bonus will be updated by 300.
So in the script, when we get an employee with status = NEW_HIRE, we raise an exception using errcode = 50001.
This exception allows us to roll back to the last savepoint (i.e., BEGIN statement inside employee loop), preserving data consistency.
We can see that all the other employees are updated as per the logic defined. Meaning, rollback only affected the one record that we wanted to, in the sample data. And that is rollback to savepoint for you !!
Caveat:
This sample script is only for your understanding, please don’t take this as a standard of writing your code. It all boils down to your Business Requirements.
After executing the script, the data in the “osdb_emp” table will look like this:
select * from osdb_emp;
| EMPID | EMP NAME | DEPTID | ROLENAME | GRADENAME | STATUS | SALARY | COMMISSION | BONUS |
| 101 | Govind | 10 | Accountant | A1 | ACTIVE | 10800 | 2000 | 1300 |
| 102 | Divya | 20 | HR | A1 | ACTIVE | 13200 | 2500 | 900 |
| 103 | Vinay | 30 | Manager | M1 | ACTIVE | 33600 | 2000 | 2300 |
| 104 | Krishna | 30 | Manager | M1 | NEW_HIRE | 32000 | 600 | 1300 |
| 105 | Ramya | 30 | Associate | C1 | ACTIVE | 16500 | 2000 | 900 |
| 106 | Sanjay | 30 | DBA | D1 | ACTIVE | 19800 | 2000 | 1000 |
Conclusion
As a proficient database administrator, mastering the PostgreSQL savepoint and rollback functionality is a crucial skill. This capability ensures data integrity, adheres to SQL:2016 standards, and guarantees the reliability of your PostgreSQL databases. If you have any inquiries or need additional assistance, please feel free to get in touch. Rest assured, we prioritize the security and consistency of your data.
Leave a Reply