Hi Kevin,
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.mahansaria@xxxxxxx
Website: http://www.tcs.com
____________________________________________
Experience certainty. IT Services
Business Solutions
Consulting
____________________________________________
-----Kevin Grittner <kgrittn@xxxxxxxxx> wrote: -----
To: Medhavi Mahansaria <medhavi.mahansaria@xxxxxxx>, "pgsql-general@xxxxxxxxxxxxxx" <pgsql-general@xxxxxxxxxxxxxx>
From: Kevin Grittner <kgrittn@xxxxxxxxx>
Date: 02/18/2015 09:40PM
Subject: Re: #Personal#: Reg: Multiple queries in a transaction
From: Kevin Grittner <kgrittn@xxxxxxxxx>
Date: 02/18/2015 09:40PM
Subject: Re: #Personal#: Reg: Multiple queries in a transaction
Medhavi Mahansaria <medhavi.mahansaria@xxxxxxx> 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
> 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
=====-----=====-----=====
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
