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 ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly