Postgresql Rollback Transaction On Error
Contents |
postgres (picture by Steve Wilson) Way back in 2005 I added the ON_ERROR_ROLLBACK feature to psql, the Postgres command line client. When postgres current transaction is aborted, commands ignored until end of transaction block enabled, any errors cause an immediate rollback to just before the postgresql on_error_rollback previous command. What this means is that you can stay inside your transaction, even if you make postgresql set on_error_rollback a typo (the main error-causing problem and the reason I wrote it!). Since I sometimes see people wanting to emulate this feature in their application or driver, I
Org.postgresql.util.psqlexception Error Current Transaction Is Aborted
thought I would explain exactly how it works in psql. First, it must be understood that this is not a Postgres feature, and there is no way you can instruct Postgres itself to ignore errors inside of a transaction. The work must be done by a client (such as psql) that can do some voodoo behind the psycopg2 rollback scenes. The ON_ERROR_ROLLBACK feature is available since psql version 8.1. Normally, any error you make will throw an exception and cause your current transaction to be marked as aborted. This is sane and expected behavior, but it can be very, very annoying if it happens when you are in the middle of a large transaction and mistype something! At that point, the only thing you can do is rollback the transaction and lose all of your work. For example: greg=# CREATE TABLE somi(fav_song TEXT, passphrase TEXT, avatar TEXT); CREATE TABLE greg=# begin; BEGIN greg=# INSERT INTO somi VALUES ('The Perfect Partner', 'ZrgRQaa9ZsUHa', 'Andrastea'); INSERT 0 1 greg=# INSERT INTO somi VALUES ('Holding Out For a Hero', 'dx8yGUbsfaely', 'Janus'); INSERT 0 1 greg=# INSERT INTO somi BALUES ('Three Little Birds', '2pX9V8AKJRzy', 'Charon'); ERROR: syntax error at or near "BALUES" LINE 1: INSERT INTO somi BALUES ('Three Little Birds', '2pX9V8AKJRzy'... greg=# INSERT INTO somi VALUES ('Three Little Birds', '2pX9V8AKJRzy', 'Charon'); ERROR: current transaction is aborted, commands ignored until end of
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and postgres abort transaction on error policies of this site About Us Learn more about Stack Overflow the
Postgres Rollback To Savepoint
company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags
Postgres Rollback Transaction
Users 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 http://blog.endpoint.com/2015/02/postgres-onerrorrollback-explained.html 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 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 http://stackoverflow.com/questions/21130094/transaction-roll-back-not-working-in-postgresql 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 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 ca
buildfarm-members pgsql-cluster-hackers pgsql-committers pgsql-hackers pgsql-rrreviewers pgsql-translators pgsql-www Regional lists Associations User groups Project lists https://www.postgresql.org/message-id/27f31620909240617u1a36b8bvffc91d06f54bcf27@mail.gmail.com Inactive lists IRC Local User Groups Featured Users International Sites Propaganda Resources Weekly News Re: "BEGIN TRANSACTION" and "START TRANSACTION": different error http://nathansnoggin.blogspot.com/2009/01/postgresql-transactions-and-error.html handling From: ning
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,commands