Thread: #Personal#: Reg: Multiple queries in a transaction

=====-----=====-----=====
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

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

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"ERROR: 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.
>
> Is there a way to turn autocommit off?

The server does not support that, but if you're running a script
through psql you should look at using:

\set ON_ERROR_ROLLBACK on

Docs at:

http://www.postgresql.org/docs/9.3/interactive/app-psql.html

If you are not using psql you can use savepoints:

http://www.postgresql.org/docs/8.4/interactive/sql-savepoint.html

ROLLBACK TO SAVEPOINT if the statement fails.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Medhavi Mahansaria wrote
> I need to execute a series of queries in a transaction, say Q1, Q2, Q3.
> [...]
> Is there a way to turn autocommit off?

I assume you meant turn Autocommit on?

So, do you want to execute the queries in a transaction or do you want to
autocommit each one?

Autocommit is a behavior of your client library, not the server.  Check its
documentation, whatever it is, for details.

As mentioned save points will probably work too.

If you explain why you think you need this then you may also get alternative
suggestions.

David J.




--
View this message in context:
http://postgresql.nabble.com/Personal-Reg-Multiple-queries-in-a-transaction-tp5838427p5838444.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

=====-----=====-----=====
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

=====-----=====-----=====
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

Medhavi Mahansaria wrote
> 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?

Forgo transactions or use savepoints.  Those are your tools.  If you cannot
find a way to solve your problem with those tools you either need to choose,
or build, a different toolbox or explain your actual problem in greater
detail so that others can see if there are solutions you are overlooking.

Or redefine your problem.

David J.





--
View this message in context:
http://postgresql.nabble.com/Personal-Reg-Multiple-queries-in-a-transaction-tp5838427p5838539.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

David G Johnston wrote
>
> Medhavi Mahansaria wrote
>> 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?
> Forgo transactions or use savepoints.  Those are your tools.  If you
> cannot find a way to solve your problem with those tools you either need
> to choose, or build, a different toolbox or explain your actual problem in
> greater detail so that others can see if there are solutions you are
> overlooking.
>
> Or redefine your problem.
>
> David J.

You might be able to write the code in pl/pgsql and just call it from your
application.  You have a bit more options for flow control in that compared
to pure SQL.

http://www.postgresql.org/docs/9.3/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

David J.



--
View this message in context:
http://postgresql.nabble.com/Personal-Reg-Multiple-queries-in-a-transaction-tp5838427p5838540.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Thu, Feb 19, 2015 at 11:11:59AM +0530, Medhavi Mahansaria wrote:
>
> But savepoint concept will not work for me as desired.

I don't see why not.

>
> 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. 
>

So,

Q1;
SAVEPOINT foo;
Q2;
if error then
    ROLLBACK TO SAVEPOINT FOO;
    Q3;
    COMMIT or ROLLBACK;
else
COMMIT;

> 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.
>

Q1;
SAVEPOINT foo;
Q2;
if error then
    ROLLBACK;

These both work.  The problem is, I think, that you have different
rules for "when Q2 fails", and without knowing your exact
circumstances I suspect we can't say much more.  Indeed, however, it
sounds to me like you think these are in the same workflow, but
they're not.

A

--
Andrew Sullivan
ajs@crankycanuck.ca

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