PostgreSQL – SAVEPOINT

A savepoint is a feature that allows you to create a named point within a transaction to which you can later roll back, while leaving the rest of the transaction intact. Savepoints are useful when you want to handle errors or exceptions within a transaction and selectively roll back to a specific point in the transaction without having to undo all the changes made so far.

Here’s how you can work with savepoints in PostgreSQL:

Start a transaction using the BEGIN statement:

BEGIN; 

This begins a new transaction.

Within the transaction, you can create a savepoint using the SAVEPOINT statement, giving it a name:

SAVEPOINT my_savepoint; 

In this example, a savepoint named my_savepoint is created within the transaction.

Perform one or more SQL operations (e.g., INSERT, UPDATE, DELETE, etc.) within the transaction.

If at any point you want to roll back to the savepoint, you can use the ROLLBACK TO statement:

ROLLBACK TO my_savepoint; 

This will undo all changes made after the my_savepoint savepoint was created, effectively reverting the transaction to that point.

You can also release a savepoint using the RELEASE statement:

RELEASE my_savepoint; 

This removes the savepoint and allows the transaction to continue from that point.

Finally, when you’re ready to commit all the changes made within the transaction, you can use the COMMIT statement:

COMMIT; 

This saves all the changes made within the transaction to the database.

Here’s a complete example:

BEGIN; 
-- Perform some operations within the transaction 
INSERT INTO employees (name, salary) VALUES ('John', 50000); 
SAVEPOINT my_savepoint; 
-- Continue with more operations 
UPDATE departments SET manager_id = 1 WHERE department_name = 'HR'; 
-- Something went wrong, let's roll back to the savepoint 
ROLLBACK TO my_savepoint; 
-- Continue with other operations or release the savepoint 
-- RELEASE my_savepoint; 
-- Finally, when everything is fine, commit the transaction 
COMMIT;

Savepoints allow you to have finer control over transactions, especially in complex scenarios where you want to handle errors gracefully and selectively roll back to specific points within a transaction.