Postgres Plus Advanced Server Oracle Compatibility Developer's Guide :
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.
The name to be given to the savepoint.
Use ROLLBACK TO SAVEPOINT to roll back to a savepoint.
Savepoints can only be established when inside a transaction block. There can be multiple savepoints defined within a transaction.
SAVEPOINT is not supported within SPL programs.
To establish a savepoint and later undo the effects of all commands executed after it was established:
\set AUTOCOMMIT off INSERT INTO dept VALUES (50, 'HR', 'NEW YORK'); SAVEPOINT depts; INSERT INTO emp (empno, ename, deptno) VALUES (9001, 'JONES', 50); INSERT INTO emp (empno, ename, deptno) VALUES (9002, 'ALICE', 50); SAVEPOINT emps; INSERT INTO jobhist VALUES (9001,'17-SEP-07',NULL,'CLERK',800,NULL,50,'New Hire'); INSERT INTO jobhist VALUES (9002,'20-SEP-07',NULL,'CLERK',700,NULL,50,'New Hire'); ROLLBACK TO depts; COMMIT;
The above transaction will commit a row into the dept table, but the inserts into the emp and jobhist tables are rolled back.