BUG #2115: If we CLOSE a Closed Cursor subsequent OPENs fail. - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      2115
Logged by:          David S. Edwards
Email address:      dave.edwards@bull.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