Why should we use rollback in sql explicitly?

Question

I'm using PostgreSQL 9.3

I have one misunderstanding about transactions and how they work. Suppose we wrapped some SQL operator within a transaction like the following:

BEGIN;
   insert into tbl (name, val) VALUES('John', 'Doe');
   insert into tbl (name, val) VALUES('John', 'Doee');
COMMIT;

If something goes wrong the transaction will automatically be rolled back. Taking that into account I can't get when should we use ROLLBACK explicitly? Could you get an example when it's necessary?

Answer 1

In PostgreSQL the transaction is not automatically rolled back on error.

It is set to the aborted state, where further commands will fail with an error until you roll the transaction back.

Observe:

regress=> BEGIN;
BEGIN
regress=> LOCK TABLE nosuchtable;
ERROR:  relation "nosuchtable" does not exist
regress=> SELECT 1;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
regress=> ROLLBACK;
ROLLBACK

This is important, because it prevents you from accidentally executing half a transaction. Imagine if PostgreSQL automatically rolled back, allowing new implicit transactions to occur, and you tried to run the following sequence of statements:

BEGIN;
INSERT INTO archive_table SELECT * FROM current_tabble;
DELETE FROM current_table;
COMMIT;

PostgreSQL will abort the transaction when it sees the typo current_tabble. So the DELETE will never happen - all statements get ignored after the error, and the COMMIT is treated as a ROLLBACK for an aborted transaction:

regress=> BEGIN;
BEGIN
regress=> SELECT typo;
ERROR:  column "typo" does not exist
regress=> COMMIT;
ROLLBACK

If it instead automatically rolled the transaction back, it'd be like you ran:

BEGIN;
INSERT INTO archive_table SELECT * FROM current_tabble;
ROLLBACK; -- automatic
BEGIN; -- automatic
DELETE FROM current_table;
COMMIT; -- automatic

... which, needless to say, would probably make you quite upset.

Answer 2

Other uses for explicit ROLLBACK are manual modification and test cases:

• Do some changes to the data (UPDATE, DELETE ...).
• Run SELECT statements to check results of data modification.
• Do ROLLBACK if results are not as expected.

In Postgres DB you can do this even with DDL statements (CREATE TABLE, ...)