Re: [GENERAL] Postgres 8.3.5 - ECPG and the use of descriptors and cursors in multi-threaded programs
Merlin Moncure wrote: > On Tue, May 31, 2011 at 7:35 PM, Bosco Rama 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres 8.3.5 - ECPG and the use of descriptors and cursors in multi-threaded programs
On Tue, May 31, 2011 at 7:35 PM, Bosco Rama 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres 8.3.5 - ECPG and the use of descriptors and cursors in multi-threaded programs
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 > " 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 ; > 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres 8.3.5 - ECPG and the use of descriptors and cursors in multi-threaded programs
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" wrote: > 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 > " 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 ; > 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" 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 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 " 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 ; 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" 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres 8.3.5 - ECPG and the use of descriptors and cursors in multi-threaded programs
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 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" 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres 8.3.5 - ECPG and the use of descriptors and cursors in multi-threaded programs
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
