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). Each of these processes has several threads. Some database connections are opened before the threads are created (this is part of the basic design of the system), but each tread only uses one specific connection assigned to it, no "cross" connection use.

We are using Embedded SQL in C mainly located in 1 source module, accessed from all threads using function calls. Each thread 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 of these crashes we had to dig deep into the source of the ecpg library. In doing this we have been wondering why the descriptors are not connection specific when most other db accesses are. It seems that if the db connection is not opened within the thread itself, the descriptor to be used is randomly chosen among opened connections. Specifically we are wondering about the reason for the descriptor name to be statically assigned at compile time (fixed text) and not a variable that 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 reproducible on others. All these systems are having the same hardware (HP based PCs) with exactly the same Linux and PostgreSQL installation.

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 will change 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> );
.
}