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.
--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)
Comments
Post a Comment