SEO

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.receiveEr rorResponse(QueryExecutorImpl.java:1525)
org.postgresql.core.v3.QueryExecutorImpl.processRe sults(QueryExecutorImpl.java:1309)
org.postgresql.core.v3.QueryExecutorImpl.execute(Q ueryExecutorImpl.java:188)
org.postgresql.jdbc2.AbstractJdbc2Statement.execut e(AbstractJdbc2Statement.java:452)
org.postgresql.jdbc2.AbstractJdbc2Statement.execut eWithFlags(AbstractJdbc2Statement.java:354)
org.postgresql.jdbc2.AbstractJdbc2Statement.execut eQuery(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

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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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@hotmail.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.receiveEr rorResponse(QueryExecutorImpl.java:1525)
org.postgresql.core.v3.QueryExecutorImpl.processRe sults(QueryExecutorImpl.java:1309)
org.postgresql.core.v3.QueryExecutorImpl.execute(Q ueryExecutorImpl.java:188)
org.postgresql.jdbc2.AbstractJdbc2Statement.execut e(AbstractJdbc2Statement.java:452)
org.postgresql.jdbc2.AbstractJdbc2Statement.execut eWithFlags(AbstractJdbc2Statement.java:354)
org.postgresql.jdbc2.AbstractJdbc2Statement.execut eQuery(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.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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@postgresql.org so that
> your
> message can get through to the mailing list cleanly
>


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org