From: | Bill Moran <wmoran(at)potentialtech(dot)com> |
---|---|
To: | Medhavi Mahansaria <medhavi(dot)mahansaria(at)tcs(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: #Personal#: Reg: Multiple queries in a transaction |
Date: | 2015-02-18 15:53:08 |
Message-ID: | 20150218105308.762def8569412c8036d0187d@potentialtech.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2015-02-18 16:06:40 | Re: Starting new cluster from base backup |
Previous Message | Tom Lane | 2015-02-18 15:34:33 | Re: Failure loading materialized view with pg_restore |