Re: BUG #2115: If we CLOSE a Closed Cursor subsequent OPENs fail.

On Wed, Dec 14, 2005 at 06:16:19PM +0000, David S. Edwards wrote:
> We are evaluating Postgres for a very large customer who has a lot of legacy
> software that they have executed with several proprietary RDBMS. We have run
> into a situation that is common in their batch code. They OPEN a cursor
> (without WITH HOLD), FETCH rows, a COMMIT occurs, then sometime later they
> CLOSE then reOPEN the cursor. With Postgres, they are unable to reOPEN the
> cursor whereas with the other RDBMS they can.

The code you posted prints an error that tells what's wrong:

do OPEN
open sqlcode = -400
ERROR MESSAGE : 'current transaction is aborted, commands ignored until end of transa

The server log should show the complete error message:

ERROR: current transaction is aborted, commands ignored until end of transaction block

After the COMMIT ECPG starts a new transaction, so when the subsequent
CLOSE fails the transaction is aborted and no further commands will
be allowed. That's standard all-or-nothing transaction behavior:
everything succeeds or the transaction must be abandoned. Apparently
the other database works differently than PostgreSQL and allows the
transaction to continue after certain types of error.

In PostgreSQL 8.0 and later you can defend transactions against
errors by using savepoints. Declare a savepoint before code that
might fail, and if it does fail then rollback to the savepoint. In
any case release the savepoint after the section of code that it
protects.

--
Michael Fuhr