Thread: Postgres 8.3.5 - ECPG and the use of descriptors and cursors in multi-threaded programs

Hello Guys,

    In a multi-threaded server program using Postgresql 8.3.5 with ECPG interface for C, we have problems using
descriptors(and possibly cursors). 

    We have created a common database interface module with basically 1 function: SQLExec(). In the 'select' part of
thisfunction we (statically) allocate a descriptor as shown below. 

    This seems to be working most of the time, but looking at the generated C code from the ecpg compiler and the
associatedlibrary functions, we are not sure whether we should put mutex locks around the 'select' part to avoid
severalthreads are using "the same" execdesc at the same time. 

    We have made sure that each thread uses their own and only their own database connection, but are unsure whether
theecpg library functions is able to handle multiple use of the statical name "execdesc" ? 


static int SQLExec( const char *thisDbConn, char *paramStmt )
{
  EXEC SQL BEGIN DECLARE SECTION;
  const char *_thisDbConn = thisDbConn;
  char *stmt = paramStmt;
  EXEC SQL END DECLARE SECTION;

     .
     .
  if( "select" ) {
     .
     .
    EXEC SQL AT :_thisDbConn ALLOCATE DESCRIPTOR execdesc; line = __LINE__;

      EXEC SQL AT :_thisDbConn PREPARE execquery FROM :stmt; line = __LINE__;

      EXEC SQL AT :_thisDbConn DECLARE execcurs CURSOR FOR execquery; line = __LINE__;

      EXEC SQL AT :_thisDbConn OPEN execcurs; line = __LINE__;

    while( ok ) {

      EXEC SQL AT :_thisDbConn FETCH IN execcurs INTO SQL DESCRIPTOR execdesc;
       .
       (handle data per row, using execdesc)
       .
    }
    .
    (deallocation of stuff)
    .
  }
}

   We experience spurious crashes with SIGSEGV and tracebacks of the core dump usually ends within some ecpg library
function,hence this question. 

   Please help,

 Leif

Leif Jensen wrote:
>
> This seems to be working most of the time, but looking at the generated C
> code from the ecpg compiler and the associated library functions, we are
> not sure whether we should put mutex locks around the 'select' part to
> avoid several threads are using "the same" execdesc at the same time.
>
> We have made sure that each thread uses their own and only their own
> database connection, but are unsure whether the ecpg library functions is
> able to handle multiple use of the statical name "execdesc" ?

You are most probably trashing memory by using the same descriptor name in
multiple threads.  However, given that you have already spent the effort to
have the connections 'thread-dedicated' I think that rather than creating a
critical path through an area that is intentionally supposed to be mutli-
hreaded, I'd be inclined to use the connection name (or some derivation of
it) as the name of the descriptor.  I haven't used descriptors in ecpg so I
don't know if the syntax works, but you could try:

    exec sql char *dname = _thisDbConn;  // Or some derivation

    EXEC SQL AT :_thisDbConn ALLOCATE DESCRIPTOR :dname;
    ...
    EXEC SQL AT :_thisDbConn FETCH IN execcurs INTO SQL DESCRIPTOR :dname;
    ...
    EXEC SQL DEALLOCATE DESCRIPTOR :dname;


Just a thought.

Bosco.