Content-Type: text/plain;
  charset="us-ascii"
From: Philip Yarra <philip@utiba.com>
Organization: Utiba
To: pgsql-interfaces@postgresql.org
Subject: ECPG:  EXEC SQL execute sometimes not returning?
Date: Thu, 27 Feb 2003 13:05:27 +1100
User-Agent: KMail/1.4.1
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200302271305.27788.philip@utiba.com>

I have some ECPG code that is failing intermittently. It's in C, using=20
pthreads. There are n threads, each has its own connection to the DB server=
.=20
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,=20
:service_id, :points_hist
ory_id, :service_result_code, :source_address, :source_address_type_id,=20
:destination_address
, :destination_address_type_id, :log_category_id, :severity_id,=20
: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=
=20
actually inserted. Problem seems to occur if a new insert is attempted whil=
e=20
one is still executing. (the actual code section shown here takes just unde=
r=20
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=20
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.=
=20
Detail about platform etc below. I can send more detail if required.=20

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 .p=
gc=20
files. Using PostgreSQL 7.3.2 libs.

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