Postgresql 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 postgres current transaction is aborted, commands ignored until end of transaction block of this site About Us Learn more about Stack Overflow the company
Postgresql On_error_rollback
Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users postgresql set on_error_rollback Badges Ask Question x 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 org.postgresql.util.psqlexception error current transaction is aborted minute: Sign up Rollback 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
Psycopg2 Rollback
during a SQL operation, Postgresql will 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 @JonS
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 site About Us Learn more about Stack Overflow the company Business Learn more about postgres abort transaction on error hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask postgres rollback transaction Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other.
Postgres Sql State: 25p02
Join them; it only takes a minute: Sign up Transaction roll back not working in Postgresql up vote 0 down vote favorite I am working on PostgreSQL 9.1.4 . I am inserting the data into 2 tables its working http://stackoverflow.com/questions/15815275/rollback-after-error-in-transaction nicely. I wish to apply transaction for my tables both table exist in same DB. If my 2nd table going fail on any moment that time my 1 st table should be rollback. I tried the properties in "max_prepared_transactions" to a non zero value in /etc/postgres/postgres.conf. But Still Transaction roll back is not working. sql postgresql postgresql-9.1 phppgadmin php-pgsql share|improve this question edited Jan 15 '14 at 6:07 Craig Ringer 134k19224307 asked Jan 15 '14 at 5:57 Kanchetianeel 4911 http://stackoverflow.com/questions/21130094/transaction-roll-back-not-working-in-postgresql 4 This doesn't make much sense. Please show the code you are running, and any error messages. –Craig Ringer Jan 15 '14 at 6:06 1 Maybe you didn't turn off autocommit? –a_horse_with_no_name Jan 15 '14 at 6:52 @a_horse_with_no_name: your comment probably was valid at the time, but from my understanding, turning off autocommit now is neither needed nor possible: stackoverflow.com/a/17936997 –ssc Sep 30 at 16:05 add a comment| 3 Answers 3 active oldest votes up vote 1 down vote Probably you didn't started transaction. Please, try BEGIN; INSERT INTO first_table VALUES(10); -- second insert should fail INSERT INTO second_table VALUES(10/0); ROLLBACK; share|improve this answer answered Jan 15 '14 at 13:22 Pavel Stehule 14.7k22230 add a comment| up vote 0 down vote in postgresql you cannot write commit or roll back explicitly within a function. I think you could have use a begin end block just write it simple BEGIN; insert into tst_table values ('ABC'); Begin insert into 2nd_table values ('ABC'); EXCEPTION when your_exception then ROLL BACK; END; END; share|improve this answer answered Jan 17 '14 at 11:07 smn_onrocks 8451618 add a comment| up vote -3 down vote I think it would be helpfull create proc DataInsertInTable as begin tran insert into Table1 values('Table1Data','XYZ') if(@@ERROR <>0) begin rollback tran; return 0 end insert into Table2 values('Table2Data','ABC') if(@@ERROR <>0) begin rollback tran; return 0 end commit Tran return 1 share|improve
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might http://dba.stackexchange.com/questions/27963/postgres-requires-commit-or-rollback-after-exception have Meta Discuss the workings and policies of this site About http://nathansnoggin.blogspot.com/2009/01/postgresql-transactions-and-error.html Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to on error improve their database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Postgres requires commit or rollback after exception up vote 3 down vote favorite current transaction is Just trying to understand the thinking here... This bug explains that with other databases, you are allowed to run additional commands after a SQLException occurs. It was written in 2001 but has never been touched. We're working with Postgres and SQL Server, and so now we're bumping into this issue. Suppose you have: start transaction; create table test(id int primary key); insert into test values (1); commit; -- Following statement throws a SQLException(duplicate key) in -- PG, SS and ORacle insert into test values (1); -- Following statement behaves differently for different DBMS: -- SS and OR: No error...statement runs fine -- PG: Another SQLException thrown...must rollback or commit insert into test values (99); Can anyone help me understand why Postgres disallows this? Or even better, is there a connection flag to change the behavior? (I looked and could not find anything.) I added the "start transaction" bit above to indicate that it was not an auto-commit tx. This is a Java app, so we're using the
take to get it running on PostgreSQL. I just discovered one rather glaring difference between PostgreSQL and most other DBMSs.Here is an example to demonstrate:I have a table: my_table, with the following 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 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:currenttran