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

The following bug has been logged online:

Bug reference: 2115
Logged by: David S. Edwards
Email address: dave(dot)edwards(at)bull(dot)com
PostgreSQL version: 8.1.0
Operating system: Red Hat Enterprise Linux AS release 4 Kernel 2.6.9-22
Description: If we CLOSE a Closed Cursor subsequent OPENs fail.
Details:

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 sequence is:

OPEN CURSOR c1;
FETCH
...
COMMIT; <<< this closes cursor c1, sqlcode = 0
CLOSE c1; <<< Postgres generates a -400 sqlcode
OPEN CURSOR c1; <<< Postgres generates a -400 sqlcode

The error status on the CLOSE is expected. The error status on the reOPEN is
not. Why cannot the cursor be reopened? Could we have an option to ecpg to
enable Postgres to process OPEN requests for cursors CLOSED twice?

Our test program uses your tenk1 demo table:

/*
closeOnCommit.pc

precompile:
ecpg closeOnCommit.pc

compile & link :
gcc -g -I $POSTGRES_HOME/include closeOnCommit.c -L $POSTGRES_HOME/lib
-lecpg -lpq -o closeOnCommit
*/

#include <stdio.h>
#include <string.h>
#include <stdlib.h>

EXEC SQL INCLUDE sqlca;

EXEC SQL BEGIN DECLARE SECTION;

char username[20];
char dbname[20];
char hvunique1[10];
char hvunique2[10];
char hvtwo[10];
char hvfour[10];
char hvten[10];
char hvtwenty[10];
char hvhundred[10];
char hvthousand[10];
char hvtwothousand[10];
char hvfivethous[10];
char hvtenthous[10];
char hvodd[10];
char hveven[10];
char hvstringu1[10];
char hvstringu2[10];
char hvstring4[10];

EXEC SQL END DECLARE SECTION;

int main()
{
printf ("\n*** closeOnCommit December 14, 2005 ***\n");
strcpy(username, "dbsp");
strcpy(dbname,"dbsp");
sqlca.sqlcode = 0;
EXEC SQL CONNECT TO :dbname user :username;
printf("\nConnected to database : %s as user %s\n", dbname, username);
strcpy (hvstringu1, "ZYAAAA");
EXEC SQL DECLARE C1 CURSOR FOR
SELECT
unique1,
unique2,
two,
four,
ten,
twenty,
hundred,
thousand,
twothousand,
fivethous,
tenthous,
odd,
even,
stringu1,
stringu2,
string4
FROM tenk1
WHERE stringu1 > :hvstringu1 ORDER BY stringu1
;
printf("\ndo OPEN");
EXEC SQL OPEN C1;
printf("\ndo FETCH");
EXEC SQL
FETCH C1 INTO
:hvunique1,
:hvunique2,
:hvtwo,
:hvfour,
:hvten,
:hvtwenty,
:hvhundred,
:hvthousand,
:hvtwothousand,
:hvfivethous,
:hvtenthous,
:hvodd,
:hveven,
:hvstringu1,
:hvstringu2,
:hvstring4
;
if (sqlca.sqlcode) {
printf ("\nfetch sqlcode = %d\n", sqlca.sqlcode);
}
printf("\ndo COMMIT");

EXEC SQL COMMIT;

printf ("\ncommit sqlcode = %d\n", sqlca.sqlcode);
printf("\ndo CLOSE");

EXEC SQL CLOSE C1;

printf ("\nclose sqlcode = %d\n", sqlca.sqlcode);
if (sqlca.sqlcode) {
printf("ERROR MESSAGE : %s\n", sqlca.sqlerrm.sqlerrmc);
}
printf("\ndo OPEN");
sqlca.sqlcode = 0;
/*
** This is the OPEN we expect to have succeed
*/
EXEC SQL OPEN C1;

printf ("\nopen sqlcode = %d\n", sqlca.sqlcode);
if (sqlca.sqlcode) {
printf("ERROR MESSAGE : %s\n", sqlca.sqlerrm.sqlerrmc);
}
printf("\n\n*** end of test***\n");
exit (0);
}

You will see that it produces the -400 sqlcode on both the CLOSE following
the COMMIT and the OPEN. A temporary workaround is to add WITH HOLD to the
cursor definition but not every situation is as simple as this one. Our goal
is to propose as few source changes for the potential use of Postgres as
possible and having the reOPEN occur would help us achieve this.

Regards,

David S. Edwards
BULL HN Information Systems