Mastering PostgreSQL: Rollback to Savepoints !!


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.


  1. Start Loop-1 through each department in the parent table “osdb_dept" 
  2. Start another Loop-2 (inside Loop-1) through each employee in the child table “osdb_emp" for a given department from Loop-1. 
  3. Start SAVEPOINT
  4. If the Department Location is:
    • Hyderabad then set commission to 2000
    • Bangalore then set commission to 2500
    • Others then set commission to 1500
  5. 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%
  6. 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.
  7. 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; 
select * from osdb_emp;

Below is the actual script that shows the implementation of rollback to savepoint in PostgreSQL pl/pgsql:

DO $
    l_return_msg   text;
    l_dept_rec     record;
    l_emp_rec      record;
    FOR l_dept_rec IN (SELECT * FROM osdb_dept ORDER BY dept_id)
        --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)
            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/100end
                 where emp_id     = l_emp_rec.emp_id;
                 if l_emp_rec.grade_name is null or l_emp_rec.status = 'NEW_HIRE' 
                    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.
                    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;
                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;
            --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

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 !!


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;



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

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>