Re: [GENERAL] ECPG and multiple threads

On 11/10/06, Albe Laurenz <all(at)adv(dot)magwien(dot)gv(dot)at> wrote:

> > I'm porting an embedded-SQL application to PostgreSQL so embedded sql
> > is a must for me. My application is multi-threaded. SQL queries are
> > used by several threads. The program keeps terminating unexpectedly in
> > deemingly "innocent" places.
> > I figured out that ecpg library can be responsible for this.
> > Here:
> > http://www.postgresql.org/docs/8.1/interactive/ecpg-process.html
> > I found the information that:
> > "The ecpg library is thread-safe if it is built using the
> > --enable-thread-safety command-line option to configure."
> > However my instance of PostgreSQL was installed from _binaries_ that I
> > downloaded from http://www.postgresql.org
> >
> > The questions:
> > 1. How do I know how my copy of libecpg.lib was built and if it is
> > thread-safe?
> > 2. Is there a place I could get the thread-safe version of this
> > library if I don't want to get into building the whole stuff from the
> > source code?
>

> 2. You did not mention the operating system you use.
Sorry. Forgot to write this.
WinXP
PostgreSQL 8.1.4

> 1. You can call 'pg_config' in the bin directory,
> it will tell you the build flags.

Thanks. Good to know.
pg_config sais (among others):

CONFIGURE = '--with-openssl' '--with-perl' '--with-tcl'
'--with-python' '--enable-nls' '--enable-thread-safety' '--with-krb5'
'--with-includes=/mingw/include/krb5'

which implies that libecpg (.lib and .dll) were build with thread safety.
However when I disassembled the libecpg.dll (the one included in
binary release), there was no code related to threads as if
enable-thread-safety option hadn't been used and thus some code
excluded from compilation by #ifdef ENABLE_THREAD_SAFETY.

So I forced myself to download MSYS, PTHREADS and source release of
psql, launched cofigure and make and, after half a day, got correct
versions of libecpg.dll, which now contains calls to pthreads as seen
in disassembler.

However my program still doesn't work and behaves as it did before.
Maybe someone will try to suggest something.

The main thread issues EXEC SQL CONNECT and then spawns several
threads (with _beginthreadex). Each of them interacts with database
but they do not do it simulteneously (for sure). By "interaction" I
mean series of cursor open/fetch/close sequences from different tables
(which is responsible for reading some configuration from the
database). The part of the code, that deals with database is really as
simple as hello-world programs.
The symptoms are strange: ECPGgo (mostly in EXEC SQL OPEN and EXEC SQL
FETCH) causes GPF in random places (i.e. NOT during the first use).
However if I execute the configuration-read procedure from the main
thread (which created the datbase connection), everything works fine
every time.
Any suggestions??

Thanks in advance.

Adam