Thread: ECPG & threads

Hello guys,

   PostgreSQL 8.3.5. Compiled and installed on Slackware 10.2, kernel 2.6.15.

   We have a transaction based system that creates a new process every time a user connects (just fork()s, no exec()s).
Eachof these processes has several threads. Some database connections are opened before the threads are created (this
ispart of the basic design of the system), but each tread only uses one specific connection assigned to it, no "cross"
connectionuse. 

   We are using Embedded SQL in C mainly located in 1 source module, accessed from all threads using function calls.
Eachthread is of course passing its allocated connection name in these calls. 

   However, we have had some problems of database calls locking up or crashing the program. In the investigation of one
ofthese crashes we had to dig deep into the source of the ecpg library. In doing this we have been wondering why the
descriptorsare not connection specific when most other db accesses are. It seems that if the db connection is not
openedwithin the thread itself, the descriptor to be used is randomly chosen among opened connections. Specifically we
arewondering about the reason for the descriptor name to be statically assigned at compile time (fixed text) and not a
variablethat could then be given a connection related name. 

   One strange thing about the behaviour of our program is that it "seems" to run ok on some machines but crashes
reproducibleon others. All these systems are having the same hardware (HP based PCs) with exactly the same Linux and
PostgreSQLinstallation. 


   To try to visualise our problem, I have included some pseudo code of our program flow below.

   What we would like to know is the philosophy of not having descriptors being connection specific and whether this
willchange or not. 

 Leif




DB access module:
   .
   Db_Open( char *conn )
   {
     EXEC SQL   EXEC SQL BEGIN DECLARE SECTION;
     const char *dbname = _dbname;
     const char *dbuser = _dbuser;
     const char *_thisDbConn = conn;
     EXEC SQL END DECLARE SECTION;

     EXEC SQL CONNECT TO :dbname AS :_thisDbConn USER :dbuser;
     if( sqlca.sqlcode ) {
       return( SQLErr_NotOpen );
     }
     EXEC SQL AT :_thisDbConn SET AUTOCOMMIT TO OFF;
     if( sqlca.sqlcode ) {
       return( SQLErr_NotOpen );
     }
     return( SQLErr_Ok );
   }

   Db_SQLExec( char *conn, char *stmt )
   {
     .
     EXEC SQL ALLOCATE DESCRIPTOR execdesc;
     .
     .
     EXEC SQL DEALLOCATE DESCRIPTOR execdesc;
     .
   }

Main source module:
   pthread_t thA = 0;
   pthread_t thB = 0;

   main()
   {
     .
     char *connA = "connA";
     char *connB = "connB";

     Db_Open( connA );
     Db_Open( connB );
     .
     .
     rval = pthread_create( &thA, NULL, funcA, NULL );
     .
     rval = pthread_create( &thB, NULL, funcB, NULL );
     .
     Db_Close( connA );
     Db_Close( connB );
   }

Various modules:

   funcA( ... )
   {
     .
     // Many calls like:
     Db_SQLExec( "connA", <some SQL statement> );
     .
   }

   funcB( ... )
   {
     .
     // Many calls like:
     Db_SQLExec( "connB", <some SQL statement> );
     .
   }