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

Hello,

   Thank you for the suggestion, seems the way to go. I have implemented this using both variable descriptor and
preparedstatement (execquery) in my program and it works nicely, except in one specific situation. 

   What I didn't mention previously is that we are sometimes using 2 connections in the same thread: 1 for reading some
tables(doing SELECT), and 1 for writing other tables (doing INSERTs/UPDATEs) for each record from the first, after some
complexoperations on the data. 

   In this case when I deallocate the execquery (and descriptor) I get an error from the ecpg lib saying: -230:26000
invalidstatement name 

   Debugging into the ecpglib, I see that when 'get_connection()' is called (from ECPGdeallocate()) with NULL as
parameter,it returns the wrong connection and then uses this and the query name in a call to
'find_prepared_statement()'which of course doesn't find any because of the mismatch of name and connection, hence the
errormessage. 

   Is it really not possible to use 2 separate connection within 1 thread at the same time ? or is it an error in the
ecpglibrary ? 

   Please help,

 Leif


----- "Bosco Rama" <postgres@boscorama.com> wrote:

> 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.

Leif Jensen wrote:
>
> Is it really not possible to use 2 separate connection within 1 thread
> at the same time ? or is it an error in the ecpg library ?

It should be entirely possible to run multiple connections in a single
thread as long as you manage the 'AT connName' clauses properly.

Though, IIRC, using an 'AT connName' clause on any sort of 'deallocate'
statement generates an error in ecpg:

  ecpg -o test.c test.pgc
  test.pgc:35: ERROR: AT option not allowed in DEALLOCATE statement

This happens when trying to deallocate a query or a prepared statement.
I don't use descriptors but the error message indicates it's _any_ sort
of deallocate.

So, it would appear that you can allocate on a connection but not
deallocate from one.  :-(

I'm wonder if Tom or Michael can shine some light on this one?

Bosco.