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

Поиск
Список
Период
Сортировка
От Medhavi Mahansaria
Тема Re: #Personal#: Reg: Multiple queries in a transaction
Дата
Msg-id OFE64C3B98.6406165B-ON65257DF1.001F5E86-65257DF1.001F5E8A@tcs.com
обсуждение исходный текст
Ответ на Re: #Personal#: Reg: Multiple queries in a transaction  (Bill Moran <wmoran@potentialtech.com>)
Ответы Re: #Personal#: Reg: Multiple queries in a transaction  (David G Johnston <david.g.johnston@gmail.com>)
Re: #Personal#: Reg: Multiple queries in a transaction  (Bill Moran <wmoran@potentialtech.com>)
Список pgsql-general
Дерево обсуждения
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.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

В списке pgsql-general по дате отправления:

Предыдущее
От: Medhavi Mahansaria
Дата:
Сообщение: Re: #Personal#: Reg: Multiple queries in a transaction
Следующее
От: David G Johnston
Дата:
Сообщение: Re: #Personal#: Reg: Multiple queries in a transaction