Skip site navigation (1) Skip section navigation (2)

ECPG: EXEC SQL execute sometimes not returning?

From: Philip Yarra <philip(at)utiba(dot)com>
To: pgsql-interfaces(at)postgresql(dot)org
Subject: ECPG: EXEC SQL execute sometimes not returning?
Date: 2003-02-27 02:05:27
Message-ID: 200302271305.27788.philip@utiba.com (view raw or flat)
Thread:
Lists: pgsql-interfaces
I have some ECPG code that is failing intermittently. It's in C, using 
pthreads. There are n threads, each has its own connection to the DB server. 
The code section that fails looks like this:

log_event(0,SMS_LOG_INFO,tptr->thread_num,"got here 6");
  EXEC SQL execute insert_cache using :session_id, :subscriber_id, 
:service_id, :points_hist
ory_id, :service_result_code, :source_address, :source_address_type_id, 
:destination_address
, :destination_address_type_id, :log_category_id, :severity_id, 
:transaction_id, :event_loca
tion, :country_code, :operator_code, :dvr_data;
   log_event(0,SMS_LOG_INFO,tptr->thread_num,"got here 7");

In a test run with 20 transactions:
log_event 6 was executed 20 times
20 records got inserted in the DB
log_event 7 was executed 19 times

Oh yes, the app is compiled using ecpg -t, so it's all auto-committing.
It looks to me like EXEC SQL is not returning... even though a DB record is 
actually inserted. Problem seems to occur if a new insert is attempted while 
one is still executing. (the actual code section shown here takes just under 
40 ms - if a request comes within that time, the first insert never returns).

If the app is run with only one DB thread, it works fine... when run with 3 it 
eventually dies as one thread locks up on the "EXEC SQL execute" call.

Does anyone have any clues about this? I'm not really sure how to proceed. 
Detail about platform etc below. I can send more detail if required. 

Regards, Philip Yarra.

Detail:
DB server: PostgreSQL 7.3.2 on alphaev6-dec-osf4.0f, compiled by GCC 2.95.2.1

client app: Linux (RH7.3) using gcc 2.96, plus ECPG of course to compile .pgc 
files. Using PostgreSQL 7.3.2 libs.

The overall app is a little complicated to explain... but basically, each DB 
thread starts, opens its connection to the DB server, EXEC SQL PREPAREs the 
insert_cache variable (one per thread), stores that in a thread-local 
variable and gets ready for insert requests. At each request, it retrieves 
the PREPAREd statement, and EXECUTEs it with the values it was passed. I've 
checked the mem location of the insert_cache per each thread - they're not 
stomping on each other that I can tell. It looks like a threading problem 
with ecpg?

Responses

pgsql-interfaces by date

Next:From: ashDate: 2003-02-27 04:40:55
Subject: Darwin libpq link failing
Previous:From: Tom LaneDate: 2003-02-26 21:34:21
Subject: Re: debug a mess

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group