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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Transitioning from Oracle: A Brief Overview<\/b><\/p>\n\n\n\n
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. <\/p>\n\n\n\n
Here’s how to achieve rollback to a savepoint in Oracle:<\/p>\n\n\n\n
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\u2019s but not in a Database function or a procedure where we need to handle a Business Scenario.<\/p>\n\n\n\n
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:<\/p>\n\n\n\n
SQL Error [0A000]: ERROR: unsupported transaction command in PL\/pgSQL<\/pre>\n\n\n\nThe good news is that we can achieve Rollback To Savepoint in PostgreSQL procedures and functions.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Understanding Rollback to Savepoint in PostgreSQL<\/b><\/p>\n\n\n\n
To grasp how to implement rollback to savepoint in PostgreSQL, let’s consider a practical example with our favorite two tables “
osdb_dept<\/code>” and “osdb_emp<\/code>“, with “osdb_dept<\/code>” serving as the parent table and linked to “osdb_emp<\/code>” child table, through the “dept_id<\/code>” column.<\/p>\n\n\n\nScenario:<\/b><\/h4>\n\n\n\n
\n
- Start Loop-1 through each department in the parent table “
osdb_dept\"<\/code> <\/li>\n\n\n\n- Start another Loop-2 (inside Loop-1) through each employee in the child table “
osdb_emp\"<\/code> for a given department from Loop-1. <\/li>\n\n\n\n- Start SAVEPOINT<\/li>\n\n\n\n
- If the Department Location is:\n
\n
- Hyderabad then set commission to 2000<\/li>\n\n\n\n
- Bangalore then set commission to 2500<\/li>\n\n\n\n
- Others then set commission to 1500<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- If Employee Role is:\n
\n
- Manager then set salary to salary + 5%<\/li>\n\n\n\n
- Accountant then set salary to salary + 8%<\/li>\n\n\n\n
- Others then set salary to salary + 10%<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- If Employee Grade is:\n
\n
- M1 and status = ACTIVE then set bonus to bonus + 1000<\/li>\n\n\n\n
- A1 and status = ACTIVE then set bonus to bonus + 500<\/li>\n\n\n\n
- NULL or status = NEW_HIRE then rollback all changes done so far, for that employee only.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
- Update all Employee bonus to bonus + 300<\/li>\n<\/ol>\n\n\n\n
Below is the script for creating the necessary tables and data:<\/span><\/p>\n\n\n\n
drop table osdb_dept;\ndrop table osdb_emp;\n\n\ncreate table osdb_dept\n(dept_id int2\n,dept_name text\n,dept_location text);\n\n\ninsert into osdb_dept values(10,'ACCOUNTING','HYDERABAD');\ninsert into osdb_dept values(20,'HR','BANGALORE');\ninsert into osdb_dept values(30,'IT','HYDERABAD');\n\n\ncreate table osdb_emp\n(emp_id int2\n,emp_name text\n,dept_id int2\n,role_name text\n,grade_name text\n,status text\n,salary int4\n,commission int4\n,bonus int4\n,comments text);\n\n\ninsert into osdb_emp values(101,'Govind' ,10,'Accountant','A1','ACTIVE' ,10000,200,500 ,null);\ninsert into osdb_emp values(102,'Divya' ,20,'HR' ,'A1','ACTIVE' ,12000,300,600 ,null);\ninsert into osdb_emp values(103,'Vinay' ,30,'Manager' ,'M1','ACTIVE' ,32000,600,1000 ,null);\ninsert into osdb_emp values(104,'Krishna' ,30,'Manager' ,'M1','NEW_HIRE' ,32000,600,1000 ,null);\ninsert into osdb_emp values(105,'Ramya' ,30,'Associate' ,'C1','ACTIVE' ,15000,300,600 ,null);\ninsert into osdb_emp values(106,'Sanjay' ,30,'DBA' ,'D1','ACTIVE' ,18000,400,700 ,null);<\/code><\/pre>\n\n\n\nselect * from osdb_dept; <\/pre>\n\n\n\n
DEPT_ID<\/b><\/td> DEPT_NAME<\/b><\/td> DEPT_LOCATION<\/b><\/td><\/tr> 10<\/span><\/td> ACCOUNTING<\/span><\/td> HYDERABAD<\/span><\/td><\/tr> 20<\/span><\/td> HR<\/span><\/td> BANGALORE<\/span><\/td><\/tr> 30<\/span><\/td> IT<\/span><\/td> HYDERABAD<\/span><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n select<\/span> *<\/span> from<\/span> osdb_emp;<\/span><\/pre>\n\n\n\n
EMPID<\/b><\/td> EMP NAME<\/b><\/td> DEPT<\/b>ID<\/b><\/td> ROLE<\/b>NAME<\/b><\/td> GRADE<\/b>NAME<\/b><\/td> STATUS<\/b><\/td> SALARY<\/b><\/td> COMMISSION<\/b><\/td> BONUS<\/b><\/td><\/tr> 101<\/span><\/td> Govind<\/span><\/td> 10<\/span><\/td> Accountant<\/span><\/td> A1<\/span><\/td> ACTIVE<\/span><\/td> 10000<\/span><\/td> 200<\/span><\/td> 500<\/span><\/td><\/tr> 102<\/span><\/td> Divya<\/span><\/td> 20<\/span><\/td> HR<\/span><\/td> A1<\/span><\/td> ACTIVE<\/span><\/td> 12000<\/span><\/td> 300<\/span><\/td> 600<\/span><\/td><\/tr> 103<\/span><\/td> Vinay<\/span><\/td> 30<\/span><\/td> Manager<\/span><\/td> M1<\/span><\/td> ACTIVE<\/span><\/td> 32000<\/span><\/td> 600<\/span><\/td> 1000<\/span><\/td><\/tr> 104<\/span><\/td> Krishna<\/span><\/td> 30<\/span><\/td> Manager<\/span><\/td> M1<\/span><\/td> NEW_HIRE<\/span><\/td> 32000<\/span><\/td> 600<\/span><\/td> 1000<\/span><\/td><\/tr> 105<\/span><\/td> Ramya<\/span><\/td> 30<\/span><\/td> Associate<\/span><\/td> C1<\/span><\/td> ACTIVE<\/span><\/td> 15000<\/span><\/td> 300<\/span><\/td> 600<\/span><\/td><\/tr> 106<\/span><\/td> Sanjay<\/span><\/td> 30<\/span><\/td> DBA<\/span><\/td> D1<\/span><\/td> ACTIVE<\/span><\/td> 18000<\/span><\/td> 400<\/span><\/td> 700<\/span><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n Below is the actual script that shows the implementation of rollback to savepoint in PostgreSQL pl\/pgsql:<\/b><\/p>\n\n\n\n
DO $<\/span><\/pre>\n\n\n\nDECLARE<\/span><\/pre>\n\n\n\nl_return_msg <\/span>text<\/span>;<\/span><\/pre>\n\n\n\nl_dept_rec record;<\/span><\/pre>\n\n\n\nl_emp_rec record;<\/span><\/pre>\n\n\n\nBEGIN<\/span><\/pre>\n\n\n\n<\/span>FOR<\/span> l_dept_rec <\/span>IN<\/span> (<\/span>SELECT<\/span> *<\/span> FROM<\/span> osdb_dept <\/span>ORDER BY<\/span> dept_id)<\/span><\/pre>\n\n\n\n<\/span>LOOP<\/span><\/pre>\n\n\n\n<\/span>--RAISE NOTICE 'l_dept_rec.dept_id = %', l_dept_rec.dept_id;<\/span><\/pre>\n\n\n\n<\/span>FOR<\/span> l_emp_rec <\/span>IN<\/span> (<\/span>SELECT<\/span> *<\/span> FROM<\/span> osdb_emp <\/span><\/pre>\n\n\n\nWHERE<\/span> dept_id <\/span>=<\/span> l_dept_rec.dept_id <\/span>ORDER BY<\/span> emp_id)<\/span><\/pre>\n\n\n\n<\/span>LOOP<\/span><\/pre>\n\n\n\nRAISE NOTICE <\/span>'emp_id = %'<\/span>, l_emp_rec.emp_id;<\/span><\/pre>\n\n\n\n<\/span>--BEGIN-EXCEPTION Block - START<\/span><\/pre>\n\n\n\n<\/span>BEGIN<\/span> <\/span>--> This is equal to SAVEPOINT. <\/span><\/pre>\n\n\n\n\u2013-> So for each emp record this serves as a savepoint<\/span><\/pre>\n\n\n\n<\/span>update<\/span> osdb_emp<\/span><\/pre>\n\n\n\n<\/span>set<\/span> commission <\/span>=<\/span> case<\/span> when<\/span> l_dept_rec.dept_location <\/span>=<\/span> 'HYDERABAD'<\/span> <\/span><\/pre>\n\n\n\nthen<\/span> 2000<\/span><\/pre>\n\n\n\n<\/span>when<\/span> l_dept_rec.dept_location <\/span>=<\/span> 'BANGALORE'<\/span> <\/span><\/pre>\n\n\n\nthen<\/span> 2500<\/span><\/pre>\n\n\n\n<\/span>else<\/span> 1500<\/span> end<\/span><\/pre>\n\n\n\n<\/span>where<\/span> emp_id <\/span>=<\/span> l_emp_rec.emp_id;<\/span><\/pre>\n\n\n\n<\/span>--<\/span><\/pre>\n\n\n\n<\/span>update<\/span> osdb_emp<\/span><\/pre>\n\n\n\n<\/span>set<\/span> salary <\/span>=<\/span> case<\/span> when<\/span> role_name <\/span>=<\/span> 'Manager'<\/span> <\/span><\/pre>\n\n\n\nthen<\/span> salary <\/span>+<\/span> (salary<\/span>*<\/span>5<\/span>\/<\/span>100<\/span>)<\/span><\/pre>\n\n\n\n<\/span>when<\/span> role_name <\/span>=<\/span> 'Accountant'<\/span> <\/span><\/pre>\n\n\n\nthen<\/span> salary <\/span>+<\/span> (salary<\/span>*<\/span>8<\/span>\/<\/span>100<\/span>)<\/span><\/pre>\n\n\n\n<\/span>else<\/span> salary <\/span>+<\/span> (salary<\/span>*<\/span>10<\/span>\/<\/span>100<\/span>) <\/span>end<\/span><\/pre>\n\n\n\n<\/span>where<\/span> emp_id <\/span>=<\/span> l_emp_rec.emp_id;<\/span><\/pre>\n\n\n\n<\/span>--<\/span><\/pre>\n\n\n\n<\/span>if<\/span> l_emp_rec.grade_name <\/span>is<\/span> null<\/span> or<\/span> l_emp_rec.status <\/span>=<\/span>