Обсуждение: Bug #504: multiple cursors cause transaction problems

Tony Griffiths (griffitt@cs.man.ac.uk) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
multiple cursors cause transaction problems

Long Description
I'm trying to query several tables with different cursors within a c++ program. I use ecpg -t to compile the code (see
examplebelow). When I run the code I can open anf fetch the first cursor,. However after the first cursor is exhausted
(i.e.,sqlca.sqlcode== ECPG_NOT_FOUND) and the loop finished, any attempt to open a new cursor gets the following error: 

-601: current transaction is aborted, queries ignored until end of transact

For each subsequent attempt to open another cursor the same error occurs.

I'm using postgreSQL version 7.1.3, built from source code using gcc3.0

I'm unsure whether this is a bug, but cannot find a solution.



Sample Code
  EXEC SQL BEGIN TRANSACTION;

  ::strcpy(my_defined_in, this->getOID()->toString());

  // Classes
  EXEC SQL DECLARE class_curs CURSOR FOR SELECT objectIdentifier, trim(both ' ' from owningObject), isowned,
ispersisted,persistence_capable, has_extent, extent_name, name, comment FROM d_Class WHERE defined_in = :my_defined_in; 
  EXEC SQL OPEN class_curs;
  for(;;) {
    EXEC SQL FETCH class_curs into :objectIdentifier, :owningObject :owningObject_ind, :isowned, :ispersisted,
:persistence_capable,:has_extent, :extent_name :extent_name_ind, :name, :comment; 

    if(sqlca.sqlcode == ECPG_NOT_FOUND) {
      cerr << sqlca.sqlcode << ": " << sqlca.sqlerrm.sqlerrmc << endl;
      EXEC SQL CLOSE class_curs;
      break;
    }
    // process information here
  }

  // Structures
  EXEC SQL DECLARE struct_curs CURSOR FOR SELECT objectIdentifier, trim(both ' ' from owningObject), isowned,
ispersisted,name, comment FROM d_Structure_Type WHERE defined_in = :my_defined_in; 
  EXEC SQL OPEN struct_curs;
  for(;;) {
    EXEC SQL FETCH struct_curs into :objectIdentifier, :owningObject :owningObject_ind, :isowned, :ispersisted, :name,
:comment;
    if(sqlca.sqlcode == ECPG_NOT_FOUND){
      cerr << sqlca.sqlcode << ": " << sqlca.sqlerrm.sqlerrmc << endl;
      EXEC SQL CLOSE struct_curs;
      break;
    }
    // process information here
  }

  cerr << "2" << endl;

  // References
  EXEC SQL DECLARE ref_curs CURSOR FOR SELECT objectidentifier, trim(both ' ' from owningObject), isowned, ispersisted,
ref_kind_,name, comment, references_collection_type_, references_keyed_coll_type_, references_ref_type_,
references_class_type_,references_primitive_type_, references_alias_type_, references_structure_type_,
references_enumeration_type_FROM d_Ref_Type WHERE defined_in = :my_defined_in; 
  EXEC SQL OPEN ref_curs;
  for(;;) {
    EXEC SQL FETCH refs_curs into :objectIdentifier, :owningObject :owningObject_ind, :isowned, :ispersisted,
:ref_kind_,:name, :comment, :references_collection_type_ :references_collection_type_ind, :references_keyed_coll_type_
:references_keyed_coll_type_ind,:references_ref_type_ :references_ref_type_ind, :references_class_type_
:references_class_type_ind,:references_primitive_type_ :references_primitive_type_ind, :references_alias_type_
:references_alias_type_ind,:references_structure_type_ :references_structure_type_ind, :references_enumeration_type_
:references_enumeration_type_ind;

    if(sqlca.sqlcode == ECPG_NOT_FOUND){
      cerr << sqlca.sqlcode << ": " << sqlca.sqlerrm.sqlerrmc << endl;
      EXEC SQL CLOSE ref_curs;
      break;
    }
    // process information here
  }

  EXEC SQL END TRANSACTION;

No file was uploaded with this report

pgsql-bugs@postgresql.org writes:
> I'm trying to query several tables with different cursors within a c++
> program. I use ecpg -t to compile the code (see example below). When I
> run the code I can open anf fetch the first cursor,. However after the
> first cursor is exhausted (i.e.,sqlca.sqlcode == ECPG_NOT_FOUND) and
> the loop finished, any attempt to open a new cursor gets the following
> error:

> -601: current transaction is aborted, queries ignored until end of transact

You have omitted to tell us what error is causing the backend to abort
the transaction in the first place.  If ecpg is failing to tell you
about an error reported by the backend (and you've not simply missed
a check for error somewhere you need one), that might be a bug we need
to fix.

If you're not sure what's going on, try turning on query logging at the
postmaster and examine the postmaster log to see which query is
provoking what error.

            regards, tom lane