When do Postgres transactions fail?-CodePudding

I was wondering at what point a transaction actually fails in Postgres. By this I mean stop working, and return an error. Is it after the first failed query? Does it try all queries and then return failure? Hopefully it fails fast on first failed query, but I don’t know for sure.

For instance, if I have a transaction with 5 insert statements, but insert 2 fails, does it stop right then and return an error?

Thanks

CodePudding user response：

If you have a transaction spanning multiple statements, and one of these statements causes an error, the whole transaction is aborted and can only be rolled back. Here is a psql session that exhibits that behavior:

test=> BEGIN;
BEGIN
test=*> SELECT 42;
 ?column? 
══════════
       42
(1 row)

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

CodePudding user response：

On PostgreSQL you can use transactions only in Procedures, in functions you cannot use commit or rollback transactions. But, PostgreSQL functions have a transaction, but these transactions are automatically controlled by Postgres. I explain to you in detail.

On PostgreSQL transactions are started after the beginning of the functions and automatically committed after the ending of functions. If inside the function when you will get any exceptions, then the transaction automatically rollbacking by PostgreSQL and all your SQL codes (all your inserts and updates) automatically rollbacking too. If you are calling functions inside the main function, so all your modified data in sub-functions and in the main functions will be recursively rollbacking.

ATTENTION! But, when you will write code to catch exceptions in the function then only data modified by this function will be rollbacking. Because exception will not be returned outside the functions. So, all your modified data written on the main function will be committed.