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

Hello Bosco,

   Thank you for your comment. Yes, it would be nice to get some more comments on the allocate/deallocate on a
connectionissue. 

   I have verified that in my case deallocating a prepared statement, it guesses the wrong connection and returns an
error.(The right one is doing auto-deallocation at disconnect time, though). 

   However, I just noticed that allocating a descriptor with the "AT <connection>" clause,
EXEC SQL AT :_thisDbConn ALLOCATE DESCRIPTOR :descname;
generates an ECPGallocate_desc() call without any connection name and that this can "screw up" the ECPGget_desc()
functionwhen guessing a connection. I could of course use: 
EXEC SQL SET CONNECTION <connection name>;
before the allocate, but that would need mutex's all over to make sure that other threads will not set the connection
too.

   Any idea why the ecpg pre-compiler doesn't use the named connection for the ALLOCATE DESCRIPTOR statement even
thoughit allows it ? 

   Please help,

 Leif


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

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

Leif Jensen wrote:
>
> Thank you for your comment. Yes, it would be nice to get some more
> comments on the allocate/deallocate on a connection issue.
>
> I have verified that in my case deallocating a prepared statement,
> it guesses the wrong connection and returns an error. (The right
> one is doing auto-deallocation at disconnect time, though).
>
> However, I just noticed that allocating a descriptor with the "AT
>  <connection>" clause,
> EXEC SQL AT :_thisDbConn ALLOCATE DESCRIPTOR :descname;
> generates an ECPGallocate_desc() call without any connection name and
> that this can "screw up" the ECPGget_desc() function when guessing a
> connection. I could of course use:
> EXEC SQL SET CONNECTION <connection name>;
> before the allocate, but that would need mutex's all over to make sure
> that other threads will not set the connection too.
>
> Any idea why the ecpg pre-compiler doesn't use the named connection
> for the ALLOCATE DESCRIPTOR statement even though it allows it ?

Unfortunately, like you, I am just a user of this wonderful DB.  Since
we are not seeing any other input here on the 'general' list it may be
time to move this thread to the pgsql-interfaces list.  Are you subscribed
to it?  It is a very low bandwidth list but it does tend to highlight the
interface issues distinct from the general DB discussions.

BTW, your PG install is 10 'point' releases behind the current release for
the 8.3.x branch.  While I am at 8.4.7 (one point release behind) I seem to
be seeing a similar set of issues and nothing in the 8.4.8 change-list says
anything about ecpg.

Bosco.

On Tue, May 31, 2011 at 7:35 PM, Bosco Rama <postgres@boscorama.com> wrote:
> Unfortunately, like you, I am just a user of this wonderful DB.  Since
> we are not seeing any other input here on the 'general' list it may be
> time to move this thread to the pgsql-interfaces list.  Are you subscribed
> to it?  It is a very low bandwidth list but it does tend to highlight the
> interface issues distinct from the general DB discussions.

hm, iirc pg-interfaces is deprecated.

merlin

Merlin Moncure wrote:
> On Tue, May 31, 2011 at 7:35 PM, Bosco Rama <postgres@boscorama.com> wrote:
>> Unfortunately, like you, I am just a user of this wonderful DB.  Since
>> we are not seeing any other input here on the 'general' list it may be
>> time to move this thread to the pgsql-interfaces list.  Are you subscribed
>> to it?  It is a very low bandwidth list but it does tend to highlight the
>> interface issues distinct from the general DB discussions.
>
> hm, iirc pg-interfaces is deprecated.

There was discussion of that some time ago.  I'm not sure what the final
decision was.  I still get the occasional message on that list.  And in
the past, messages sent to that list got some sort of attention.  It
seems that ecpg gets lost in the crowd here on the general list.  I'm not
sure if this is because of the ecpg folks not being subscribed to general
(which I highly doubt since I see Tom here, though I don't see Michael) or
if it's due to the different SNR.

Bosco.