PostgreSQL Savepoint

• SAVEPOINT -- define a new savepoint within the current transaction
• SAVEPOINT establishes a new savepoint within the current transaction.
• A savepoint is a special mark inside a transaction that allows all commands that are executed after it was established to be rolled back, restoring the transaction state to what it was at the time of the savepoint.

PRACTICAL FOR SAVEPOINT:
--Consider DEMO table is having the following records:

postgres=# SELECT * FROM DEMO;                                      
 id |  name   |   place    | pincode 
----+---------+------------+---------
  1 | anbu    | pulicat    |  601205
  2 | nijam   | pulicat    |  601206
  3 | umar    | pulicat    |  601205
  4 | junaith | pulicat    |  601206
  3 | umar    | pulicat    |  601205
  4 | junaith | pulicat    |  601206
  9 | mohad   | pulicat    |  601202
  7 | kadahar | pulicat    |  601201
  1 | ARNOLD  | CALIFORNIA |  601210
(9 rows)                                    

--To establish a savepoint and later undo the effects of all commands executed after it was established:

BEGIN;
postgres=# INSERT INTO DEMO VALUES (10,'JACKIE','CHICAGO',601211);
INSERT 0 1

postgres=# SAVEPOINT CHICA;
SAVEPOINT

postgres=# INSERT INTO DEMO VALUES (11,'SMITH','KASHMIR',601212);
INSERT 0 1
postgres=# ROLLBACK TO SAVEPOINT CHICA;
ROLLBACK

postgres=# INSERT INTO DEMO VALUES (12,'BUSH','KIRUKKAN',601213);
INSERT 0 1
postgres=# COMMIT;
COMMIT

The above transaction will insert the values COLUMN'S ID 10 and 12, but not 11.

--If you  check DEMO table is still having the following records ONLY:

postgres=# SELECT * FROM DEMO;                                    
 id |  name   |   place    | pincode 
----+---------+------------+---------
  1 | anbu    | pulicat    |  601205
  2 | nijam   | pulicat    |  601206
  3 | umar    | pulicat    |  601205
  4 | junaith | pulicat    |  601206
  3 | umar    | pulicat    |  601205
  4 | junaith | pulicat    |  601206
  9 | mohad   | pulicat    |  601202
  7 | kadahar | pulicat    |  601201
  1 | ARNOLD  | CALIFORNIA |  601210
 10 | JACKIE  | CHICAGO    |  601211
 12 | BUSH    | KIRUKKAN   |  601213
(11 rows)

To establish and later destroy a savepoint:

BEGIN;
postgres=# INSERT INTO DEMO VALUES (13,'TRUMP','LOS ANGLES',601214);
INSERT 0 1

postgres=# SAVEPOINT CHICA;                                         
SAVEPOINT

postgres=# INSERT INTO DEMO VALUES (14,'HILARY','SIDNEY',601215);
INSERT 0 1

postgres=# RELEASE SAVEPOINT CHICA;
RELEASE

postgres=# COMMIT;
COMMIT

The above transaction will insert both 13 and 14.

--Let us check the demo table

postgres=# SELECT * FROM DEMO;                                      
 id |  name   |   place    | pincode 
----+---------+------------+---------
  1 | anbu    | pulicat    |  601205
  2 | nijam   | pulicat    |  601206
  3 | umar    | pulicat    |  601205
  4 | junaith | pulicat    |  601206
  3 | umar    | pulicat    |  601205
  4 | junaith | pulicat    |  601206
  9 | mohad   | pulicat    |  601202
  7 | kadahar | pulicat    |  601201
  1 | ARNOLD  | CALIFORNIA |  601210
 10 | JACKIE  | CHICAGO    |  601211
 12 | BUSH    | KIRUKKAN   |  601213
 13 | TRUMP   | LOS ANGLES |  601214
 14 | HILARY  | SIDNEY     |  601215
(13 rows)