上QQ阅读APP看书，第一时间看更新

Handling errors inside a transaction

It is not always the case that transactions are correct from beginning to end. However, in PostgreSQL, only error-free transactions can be committed. Here is what happens:

test=# BEGIN; 
BEGIN 
test=# SELECT 1; 
 ?column?  
---------- 
        1 
(1 row) 

test=# SELECT 1 / 0; 
ERROR:  division by zero 
test=# SELECT 1; 
ERROR:  current transaction is aborted, commands ignored until end of transaction block 
test=# COMMIT; 
ROLLBACK

Note that the division by zero did not work out.

In any proper database, an instruction like this will instantly error-out and make the statement fail.

It is important to point out that PostgreSQL will error-out, unlike MySQL, which does not seem to have a problem with a mathematically wrong result.

After an error has occurred, no more instructions will be accepted even if those instructions are semantically and syntactically correct. It is still possible to issue a COMMIT. However, PostgreSQL will roll back the transaction because it is the only thing at this point that can still be done.