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

On Wed, 18 Feb 2015 20:36:45 +0530
Medhavi Mahansaria <medhavi(dot)mahansaria(at)tcs(dot)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