Thread: current transaction is aborted, commands ignored until end of transaction...

Hello,

I'm having a bit of trouble with the error message from the subject. Is
there a way to simply go on with my commands if I hit this error?

One example would be when I drop a table (and it doesn't exist) I still
want to run the following CREATE TABLE. But due to the error even the
following valid command will fail.
This problem is not limited to DDL. When I try to write the result sets for
several queries to disk it's the same problem, if e.g. the first select
reports an error (e.g. due to a typo in the column list) all subsequent
ones will no run as well.

I know that I can "fix" this state by issuing a rollback (or commit). The
problem is, that this stuff is part of a bigger program, and I cannot
determine whether I'm allowed to do a rollback or commit at that point.

Is this a problem with the JDBC interface that I'm using, or is this a
general Postgres problem?

Is there a setting where I can control this behaviour?

I'm using 8.0.2 on Windows 2000 (and XP)

Thanks for your help
Thomas

Thomas Kellerer wrote:
> Hello,
>
> I'm having a bit of trouble with the error message from the subject. Is
> there a way to simply go on with my commands if I hit this error?

Only if you are using savepoints (nested transactions) otherwise you
must rollback.

> Is this a problem with the JDBC interface that I'm using, or is this a
> general Postgres problem?

I do not know if the JDBC interface supports savepoints.

>
> Is there a setting where I can control this behaviour?

If you can turn on autocommit with JDBC that will help.

SIncerely,

Joshua D. Drake


>
> I'm using 8.0.2 on Windows 2000 (and XP)
>
> Thanks for your help
> Thomas
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend


--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedication Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

On Mon, 2005-04-18 at 17:09, Thomas Kellerer wrote:
> Hello,
>
> I'm having a bit of trouble with the error message from the subject. Is
> there a way to simply go on with my commands if I hit this error?
>
> One example would be when I drop a table (and it doesn't exist) I still
> want to run the following CREATE TABLE. But due to the error even the
> following valid command will fail.
> This problem is not limited to DDL. When I try to write the result sets for
> several queries to disk it's the same problem, if e.g. the first select
> reports an error (e.g. due to a typo in the column list) all subsequent
> ones will no run as well.
>
> I know that I can "fix" this state by issuing a rollback (or commit). The
> problem is, that this stuff is part of a bigger program, and I cannot
> determine whether I'm allowed to do a rollback or commit at that point.
>
> Is this a problem with the JDBC interface that I'm using, or is this a
> general Postgres problem?
>
> Is there a setting where I can control this behaviour?
>
> I'm using 8.0.2 on Windows 2000 (and XP)


You could try using rollback and save points.  set  savepoint before you
drop the table, and if the drop fails, issue a rollback.

Joshua D. Drake wrote on 19.04.2005 00:21:
>> I'm having a bit of trouble with the error message from the subject.
>> Is there a way to simply go on with my commands if I hit this error?
>
> Only if you are using savepoints (nested transactions) otherwise you
> must rollback.
>
>> Is this a problem with the JDBC interface that I'm using, or is this a
>> general Postgres problem?
>
> I do not know if the JDBC interface supports savepoints.
The problem is, that I need to support several different DBMS and only for
handling Postgres stuff I don't really want to introduce this.


>> Is there a setting where I can control this behaviour?
>
> If you can turn on autocommit with JDBC that will help.

Hmm not really an option as well (I don't really have control over the
transaction handling at that point).

Thanks for your answer
Thoams

Scott Marlowe wrote on 19.04.2005 00:26:

>>One example would be when I drop a table (and it doesn't exist) I still
>>want to run the following CREATE TABLE. But due to the error even the
>>following valid command will fail.
>>This problem is not limited to DDL. When I try to write the result sets for
>>several queries to disk it's the same problem, if e.g. the first select
>>reports an error (e.g. due to a typo in the column list) all subsequent
>>ones will no run as well.
 >
> You could try using rollback and save points.  set  savepoint before you
> drop the table, and if the drop fails, issue a rollback.

As I said, I'm aware of that, but I have to support different DBMS and this
behaviour is pretty different to the other ones. The DDL stuff isn't that
big of a problem, but the example with multiple selects is a bit more
tricky to solve as I don't have control over the transaction handling.

Thanks for your answer, I guess I'll simply have to live with that.

Cheers
Thoams

On Tue, 19 Apr 2005, Thomas Kellerer wrote:

> Joshua D. Drake wrote on 19.04.2005 00:21:
> >> I'm having a bit of trouble with the error message from the subject.
> >> Is there a way to simply go on with my commands if I hit this error?
> >
> > Only if you are using savepoints (nested transactions) otherwise you
> > must rollback.
> >
> >> Is this a problem with the JDBC interface that I'm using, or is this a
> >> general Postgres problem?
> >
> > I do not know if the JDBC interface supports savepoints.
>
> The problem is, that I need to support several different DBMS and only
> for handling Postgres stuff I don't really want to introduce this.
>

This is an open todo item for the JDBC driver.  It could be done by
automatically wrapping all statements in savepoints behind the scenes.
Then any error would rollback to the savepoint and you could continue on
with your transaction.  This option would only be used for
compatibility which makes it a low priority for people who are mostly
PostgreSQL users.  Also, it'll certainly decrease performance.

Kris Jurka

On 19.04.2005 02:33 Kris Jurka wrote:
>>>>Is this a problem with the JDBC interface that I'm using, or is this a
>>>>general Postgres problem?
>>>
>
> This is an open todo item for the JDBC driver.  It could be done by
> automatically wrapping all statements in savepoints behind the scenes.
> Then any error would rollback to the savepoint and you could continue on
> with your transaction.  This option would only be used for
> compatibility which makes it a low priority for people who are mostly
> PostgreSQL users.  Also, it'll certainly decrease performance.
>

No problem. I can work around most of the areas where this can occur and I have
control over the transaction handling. So it's not a big issue. It would have
been nice though :)

Thanks
Thomas