Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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)

Comments

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

vacuumlo - removing large objects orphans from a database PostgreSQL