Re: how to continue using a connection after an error with autocommit=false

Hi,

I just find out that I cannot continue using a connection when I use
autocommit=false and that an sql insert failed.

In pseudo code this is what I'd like to do:

1) create connection
2) set autocommit = false
3) do an insert that succeed
4) do an insert that fails
5) catch the SQLException (I don't do a rollback on purpose)
6) do a select with the same connection
7) do another insert
8) commit

Everything goes well until step (6). where I receive the following error:

ERROR: current transaction is aborted, commands ignored until end of
transaction block
Exception: org.postgresql.util.PSQLException
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1525)
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1309)
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:354)
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:258)

Can I do something is step (5) so that I don't get the error in step (6) ?
I need to continue using the same connection.

Thanks you your help.

_________________________________________________________________
Vlg selv hvordan du vil kommunikere - skrift, tale, video eller billeder
med MSN Messenger: http://messenger.msn.dk/ - her kan du det hele

James Im wrote:
> I just find out that I cannot continue using a connection when I use
> autocommit=false and that an sql insert failed.

That's intended behavior in PostgreSQL. I just wrote this in another
thread last week:

If you have a statement in your transaction that you know might fail,
you can use savepoints to avoid having to restart the whole transaction:

Savepoint sp = conn.setSavepoint();
try {
stmt.executeQuery("SELECT 1 FROM table_that_might_not_exist");
} catch(SQLException ex)
{
sp.rollback(sp);
}
stmt.executeQuery("SELECT * FROM table_that_exists");
...

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

You can either commit or rollback the transaction when you encounter an SQL error. This will alow the next statements to execute successfully.

--Altaf Malik
EnterpriseDB
www.enterprisedb.com

James Im <im-james(at)hotmail(dot)com> wrote:
Hi,

I just find out that I cannot continue using a connection when I use
autocommit=false and that an sql insert failed.

In pseudo code this is what I'd like to do:

1) create connection
2) set autocommit = false
3) do an insert that succeed
4) do an insert that fails
5) catch the SQLException (I don't do a rollback on purpose)
6) do a select with the same connection
7) do another insert
8) commit

Everything goes well until step (6). where I receive the following error:

ERROR: current transaction is aborted, commands ignored until end of
transaction block
Exception: org.postgresql.util.PSQLException
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1525)
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1309)
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:354)
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:258)

Can I do something is step (5) so that I don't get the error in step (6) ?
I need to continue using the same connection.

Thanks you your help.

_________________________________________________________________
Vælg selv hvordan du vil kommunikere - skrift, tale, video eller billeder
med MSN Messenger: http://messenger.msn.dk/ - her kan du det hele

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

---------------------------------
Don't get soaked. Take a quick peak at the forecast
with theYahoo! Search weather shortcut.

On Tue, 2007-02-20 at 04:53, Altaf Malik wrote:
> You can either commit or rollback the transaction when you encounter
> an SQL error. This will alow the next statements to execute
> successfully.

You can't commit after an error in PostgreSQL unless you've rolled back
to a savepoint from before the error.

On Tuesday 20 February 2007 10:50, Heikki Linnakangas wrote:
> James Im wrote:
> > I just find out that I cannot continue using a connection when I use
> > autocommit=false and that an sql insert failed.
>
> That's intended behavior in PostgreSQL.
[snip]
This is new behaviour in version 8, I believe.

I think this behaviour is unique to Postgresql?

Melati was developed with Postgresql version 6 as the target dbms
back in 2000, since then it has been extended to work with the other common
dbmsen, but now it looks like it won't work with V8, without quite a bit of
rework.

Is this behaviour specified by a standard?

What are the advantages?

yours
Tim Pizey

--
http://melati.org/ - the flower of Java.

On 28-Feb-07, at 3:41 PM, Tim Pizey wrote:

> On Tuesday 20 February 2007 10:50, Heikki Linnakangas wrote:
>> James Im wrote:
>>> I just find out that I cannot continue using a connection when I use
>>> autocommit=false and that an sql insert failed.
>>
>> That's intended behavior in PostgreSQL.
> [snip]
> This is new behaviour in version 8, I believe.
>
No, this has been around since 7.x
> I think this behaviour is unique to Postgresql?
>
Possibly
> Melati was developed with Postgresql version 6 as the target dbms
> back in 2000, since then it has been extended to work with the
> other common
> dbmsen, but now it looks like it won't work with V8, without quite
> a bit of
> rework.
>
> Is this behaviour specified by a standard?
>
> What are the advantages?
>
The advantage is that a transaction is atomic.

You can continue to use the connection, you simply have to rollback
the transaction after the error and start over.
You can also use savepoints.
> yours
> Tim Pizey
>
> --
> http://melati.org/ - the flower of Java.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that
> your
> message can get through to the mailing list cleanly
>

On Wednesday 28 February 2007 22:33, Dave Cramer wrote:
> On 28-Feb-07, at 3:41 PM, Tim Pizey wrote:
> > On Tuesday 20 February 2007 10:50, Heikki Linnakangas wrote:
> >> James Im wrote:
> >>> I just find out that I cannot continue using a connection when I use
> >>> autocommit=false and that an sql insert failed.
> >>
> >> That's intended behavior in PostgreSQL.
> >
> > [snip]
> > This is new behaviour in version 8, I believe.
>
> No, this has been around since 7.x
>
> > I think this behaviour is unique to Postgresql?
>
> Possibly
>
> > Melati was developed with Postgresql version 6 as the target dbms
> > back in 2000, since then it has been extended to work with the
> > other common
> > dbmsen, but now it looks like it won't work with V8, without quite
> > a bit of
> > rework.
> >

Sorry, my bad, all Melati's tests pass on 8.1.4, without modification.

yours
Tim Pizey

--
http://melati.org/ - the flower of Java.