Re: #Personal#: Reg: Multiple queries in a transaction - Mailing list pgsql-general

On Thu, 19 Feb 2015 11:12:38 +0530
Medhavi Mahansaria <medhavi.mahansaria@tcs.com> wrote:

> Hi Bill,
>
> Thanks!
>
> But savepoint concept will not work for me as desired.

Why not? The scenerio you describe below can be perfectly implemented
using savepoints. Describe in more detail, please, why savepoints
won't accomplish it, otherwise I'm not sure I can offer any better
suggestions.

> 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
hasexecuted 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
multiplequeries. 
>
> 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.mahansaria@tcs.com
> Website: http://www.tcs.com
> ____________________________________________
> Experience certainty. IT Services
> Business Solutions
> Consulting
> ____________________________________________
>
>
> -----Bill Moran <wmoran@potentialtech.com> wrote: -----
> To: Medhavi Mahansaria <medhavi.mahansaria@tcs.com>
> From: Bill Moran <wmoran@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.mahansaria@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
>
>


--
Bill Moran