Hi Bill, Thanks! But savepoint concept will not work for me as desired. Is there any other way apart from SAVEPOINT that can be incorporated. I am not using a script. I am writing a c++ program. My problem is that I have 2 cases: Case 1: When Q2 fails (we delete the error), i want to continue to Q3 and commit changes done by Q1 and Q3 once Q3 has executed successfully. Case 2: When Q2 fails, I want it to throw an error. and rollback the changes made by Q1 and not proceed to Q3 at all. Note: This is just a small example. I need a solution for an entire application which follows the same concept across multiple queries. How can I incorporate this?
Thanks & Regards Medhavi Mahansaria Tata Consultancy Services Limited Unit-VI, No.78, 79& 83, L-Centre, EPIP Industrial Estate, Whitefield Bangalore - 560066,Karnataka India Ph:- +91 80 67253769 Cell:- +91 9620053040 Mailto: medhavi.mahansa...@tcs.com Website: http://www.tcs.com ____________________________________________ Experience certainty. IT Services Business Solutions Consulting ____________________________________________ -----Bill Moran <wmo...@potentialtech.com> wrote: ----- To: Medhavi Mahansaria <medhavi.mahansa...@tcs.com> From: Bill Moran <wmo...@potentialtech.com> Date: 02/18/2015 09:23PM Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] #Personal#: Reg: Multiple queries in a transaction On Wed, 18 Feb 2015 20:36:45 +0530 Medhavi Mahansaria <medhavi.mahansa...@tcs.com> wrote: > I need to execute a series of queries in a transaction, say Q1, Q2, Q3. > > Q1 -> success > Q2 -> Failed > Q3 -> Success > > My issue is that after Q2 fails all the queries that follow give error "E > RROR: current transaction is aborted, commands ignored until end of > transaction block" > > I want to move ahead in the transaction and execute Q3 also even though Q2 > was a failure. > > Can you please suggest a way to do so in PostgreSQL 9.3. I believe savepoints are what you want: http://www.postgresql.org/docs/9.3/static/sql-savepoint.html Create a savepoint prior to each query, then decide how to proceed based on the success status of that query. For example, in the scenario you describe above: BEGIN SAVEPOINT q1 Q1 -> success RELEASE SAVEPOINT q1 SAVEPOINT q2 Q2 -> failure ROLLBACK TO SAVEPOINT q2 SAVEPOINT q3 Q3 -> success RELEASE SAVEPOINT q3 COMMIT In which case Q1 and Q3 would successfully be committed. -- Bill Moran =====-----=====-----===== Notice: The information contained in this e-mail message and/or attachments to it may contain confidential or privileged information. If you are not the intended recipient, any dissemination, use, review, distribution, printing or copying of the information contained in this e-mail message and/or attachments to it are strictly prohibited. If you have received this communication in error, please notify us by reply e-mail or telephone and immediately and permanently delete the message and any attachments. Thank you
