Postgresql Transaction Rollback On Error
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this postgres current transaction is aborted, commands ignored until end of transaction block site About Us Learn more about Stack Overflow the company Business Learn postgresql on_error_rollback more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x
Postgresql Set On_error_rollback
Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Rollback
Org.postgresql.util.psqlexception Error Current Transaction Is Aborted
after error in transaction up vote 4 down vote favorite This should be an easy one for those familiar with Postgresql: My application issues a begin_work, does a number of operations, and then issues a commit. The operations and the commit are wrapped inside a try-catch block, whose catch statement performs a rollback. Assumption: if an error occurs during a SQL operation, Postgresql will psycopg2 rollback automatically rollback the transaction, and therefore my rollback will be redundant but harmless. Is this assumption correct? (The reason why I'm rollbacking anyway: just in case an exception unrelated to a SQL operation ocurs.) postgresql share|improve this question asked Apr 4 '13 at 15:19 Jon Smark 890818 add a comment| 1 Answer 1 active oldest votes up vote 1 down vote accepted If an error occurs, PostgreSQL does not actually rollback the transaction. It fails every subsequent statements with an error. You can try this out in the client. You need to execute rollback before any statements can be executed successfully. In the case that you close the connection and start a new one, this is of little consequence. However, if you retain the connection and run other statements which you expect to execute successfully, it will not work. share|improve this answer answered Apr 4 '13 at 15:33 drone.ah 858926 So, it seems my assumption was wrong, but I was right to rollback anyway. Thanks! –Jon Smark Apr 4 '13 at 15:50 1 @JonSmark Correct. Pg marks the transaction as aborted but doesn't return to autocommit m
buildfarm-members pgsql-cluster-hackers pgsql-committers pgsql-hackers pgsql-rrreviewers pgsql-translators pgsql-www Regional lists Associations User groups Project lists Inactive lists IRC Local User Groups Featured Users International Sites Propaganda Resources Weekly News
Postgres Abort Transaction On Error
Re: "BEGIN TRANSACTION" and "START TRANSACTION": different error handling From: ning postgres rollback transaction
take to get it running on PostgreSQL. I just discovered one rather glaring difference between http://api.rubyonrails.org/classes/ActiveRecord/Transactions/ClassMethods.html PostgreSQL and most other DBMSs.Here is an example to demonstrate:I have a table: my_table, with the following on error row:----------------|id|val|----------------|3|row3|----------------Let's say I want to make sure I have rows for ids 1 through 5 in the table. This is a case where I would use MySQL's 'INSERT IGNORE' statement, which doesn't exist in current transaction is PostgreSQL. I have at least two options: I can create a stored procedure to do it, or I can do it in code.Let's say I create a procedure:CREATE OR REPLACE FUNCTION my_proc() RETURNS void AS$$BEGINFOR i IN 1..5 LOOPBEGININSERT INTO my_table(id, val) VALUES (i, 'row ' || i);EXCEPTION WHEN unique_violation THEN-- do nothingEND;END LOOP;END;$$ LANGUAGE plpgsql;All is well, this type of exception handling is standard in stored procedures. Here's the entire test:postgres=#createdatabasemy_test;CREATEDATABASEpostgres=#\cmy_test;Youarenowconnectedtodatabase"my_test".my_test=#createtablemy_table(idINTEGERPRIMARYKEY,valVARCHAR(64));NOTICE:CREATETABLE/PRIMARYKEYwillcreateimplicitindex"my_table_pkey"fortable"my_table"CREATETABLEmy_test=#CREATEORREPLACEFUNCTIONmy_proc()RETURNSvoidASmy_test-#$$my_test$#BEGINmy_test$#FORiIN1..5LOOPmy_test$#BEGINmy_test$#INSERTINTOmy_table(id,val)VALUES(i,'row'||i);my_test$#EXCEPTIONWHENunique_violationTHENmy_test$#--donothingmy_test$#END;my_test$#ENDLOOP;my_test$#END;my_test$#$$LANGUAGEplpgsql;CREATEFUNCTIONmy_test=#insertintomy_tablevalues(3,'row3');INSERT01my_test=#select*frommy_table;id|val----+-------3|row3(1row)my_test=#selectmy_proc();my_proc---------(1row)my_test=#select*frommy_table;id|val----+-------3|row31|row12|row24|row45|row5(5rows)my_test=#Now here's the twist - you can't (really) do the same thing outside of a stored procedure (without using savepoints, as I'll get to later). Here's what happens:my_test=#\setAUTOCOMMITOFFmy_test=#deletefrommy_tablewhereid<>3;DELETE4my_test=#commit;COMMITmy_test=#insertintomy_tablevalues(1,'row1');INSERT01my_test=#insertintomy_tablevalues(2,'row2');INSERT01my_test=#insertintomy_tablevalues(3,'row3');ERROR:duplicatekeyvalueviolatesuniqueconstraint"my_table_pkey"my_test=#insertintomy_tablevalues(4,'row4');ERROR:currenttransactionisaborted,commandsignoreduntilendoftransactionblockmy_test=#insertintomy_tablevalues(5,'row5');ERROR:currenttransactionisaborted,commandsignoreduntilendoftransactionblockmy_test=#commit;ROLLBACKmy_test=#select*frommy_table;id|val----+-------3|row3(1row)PostgreSQL f
succeed as one atomic action. The classic example is a transfer between two accounts where you can only have a deposit if the withdrawal succeeded and vice versa. Transactions enforce the integrity of the database and guard the data against program errors or database break-downs. So basically you should use transaction blocks whenever you have a number of statements that must be executed together or not at all. For example: ActiveRecord::Base.transaction do david.withdrawal(100) mary.deposit(100) end This example will only take money from David and give it to Mary if neither withdrawal nor deposit raise an exception. Exceptions will force a ROLLBACK that returns the database to the state before the transaction began. Be aware, though, that the objects will not have their instance data returned to their pre-transactional state. Different Active Record classes in a single transaction Though the transaction class method is called on some Active Record class, the objects within the transaction block need not all be instances of that class. This is because transactions are per-database connection, not per-model. In this example a balance record is transactionally saved even though transaction is called on the Account class: Account.transaction do balance.save! account.save! end The transaction method is also available as a model instance method. For example, you can also do this: balance.transaction do balance.save! account.save! end Transactions are not distributed across database connections A transaction acts on a single database connection. If you have multiple class-specific databases, the transaction will not protect interaction among them. One workaround is to begin a transaction on each class whose models you alter: Student.transaction do Course.transaction do course.enroll(student) student.units += course.units end end This is a poor solution, but fully distributed transactions are beyond the scope of Active Record. save and destroy are automatically wrapped in a transaction Both #save and #destroy come wrapped in a transaction that ensures that whatever you do in validations or callbacks will happen under its protected cover. So you can use validations to check for