Postgres 8.3.5 - ECPG and the use of descriptors and cursors in multi-threaded programs
Hello Guys,
In a multi-threaded server program using Postgresql 8.3.5 with ECPG interface for C, we have problems using descriptors (and possibly cursors).
We have created a common database interface module with basically 1 function: SQLExec(). In the 'select' part of this function we (statically) allocate a descriptor as shown below.
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" ?
static int SQLExec( const char *thisDbConn, char *paramStmt )
{
EXEC SQL BEGIN DECLARE SECTION;
const char *_thisDbConn = thisDbConn;
char *stmt = paramStmt;
EXEC SQL END DECLARE SECTION;
.
.
if( "select" ) {
.
.
EXEC SQL AT :_thisDbConn ALLOCATE DESCRIPTOR execdesc; line = __LINE__;
EXEC SQL AT :_thisDbConn PREPARE execquery FROM :stmt; line = __LINE__;
EXEC SQL AT :_thisDbConn DECLARE execcurs CURSOR FOR execquery; line = __LINE__;
EXEC SQL AT :_thisDbConn OPEN execcurs; line = __LINE__;
while( ok ) {
EXEC SQL AT :_thisDbConn FETCH IN execcurs INTO SQL DESCRIPTOR execdesc;
.
(handle data per row, using execdesc)
.
}
.
(deallocation of stuff)
.
}
}
We experience spurious crashes with SIGSEGV and tracebacks of the core dump usually ends within some ecpg library function, hence this question.
Please help,
Leif
Import Notes
Reply to msg id not found: 24118307.1723.1306155087765.JavaMail.root@quick
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.
Hello,
Thank you for the suggestion, seems the way to go. I have implemented this using both variable descriptor and prepared statement (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 complex operations on the data.
In this case when I deallocate the execquery (and descriptor) I get an error from the ecpg lib saying: -230:26000 invalid statement 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 error message.
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 ?
Please help,
Leif
----- "Bosco Rama" <postgres@boscorama.com> wrote:
Show quoted text
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.
Import Notes
Reply to msg id not found: 11996818.3039.1306324876084.JavaMail.root@quick | Resolved by subject fallback
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.
Hello Bosco,
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 ?
Please help,
Leif
----- "Bosco Rama" <postgres@boscorama.com> wrote:
Show quoted text
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 statementThis 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.
Import Notes
Reply to msg id not found: 20583071.5618.1306747263161.JavaMail.root@quick | Resolved by subject fallback
PS.: That goes for the AT clause on the GET DESCRIPTOR statement too. The connection name is not included in the ECPGget_desc() call.
----- "Leif Jensen" <leif@crysberg.dk> wrote:
Show quoted text
Hello Bosco,
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 ?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 statementThis 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.
Import Notes
Reply to msg id not found: 20583071.5618.1306747263161.JavaMail.root@quick | Resolved by subject fallback
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.