pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedECPG: EXEC SQL execute sometimes not returning?
3+ messages / 3 participants
[nested] [flat]
* ECPG: EXEC SQL execute sometimes not returning?
@ 2003-02-27 02:05 Philip Yarra <philip@utiba.com>
0 siblings, 2 replies; 3+ messages in thread
From: Philip Yarra @ 2003-02-27 02:05 UTC (permalink / raw)
To: 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?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: ECPG: EXEC SQL execute sometimes not returning?
@ 2003-02-27 10:13 Aidamir Lovpache <archibald@mastak.ru>
parent: Philip Yarra <philip@utiba.com>
1 sibling, 0 replies; 3+ messages in thread
From: Aidamir Lovpache @ 2003-02-27 10:13 UTC (permalink / raw)
To: pgsql-interfaces
On Thu, 27 Feb 2003 13:05:27 +1100
Philip Yarra <philip@utiba.com> wrote:
> 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?
>
I have not thread programming experience with ecpg. But if you want real nonblocking interface and get a best speed performance
with a lot simultaneos query processing use libpq PQsocket(), PQsendQuery(), PQconsumeInput(), PQbusy function with multiplae connections to PostgreSQL and poll() on connection sockets, including other sockets application handling, to catch up the query results. The idea is to have a free connections stack in a single process application (it may be a single thread). When the application wants to query postgres, it takes first free PGconn* from the stack and sends the query by PQsendQuery without any block of the process. Then poll() is awaiting the result of queries. If you are interest of details mail me. I have used this method and had a best speed of query processing and system resource economy then multithreading (I have tryed it, but with libpq).
mailto: archibald@mastak.ru < Archibald >
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: ECPG: EXEC SQL execute sometimes not returning?
@ 2003-02-27 13:50 Michael Meskes <meskes@postgresql.org>
parent: Philip Yarra <philip@utiba.com>
1 sibling, 0 replies; 3+ messages in thread
From: Michael Meskes @ 2003-02-27 13:50 UTC (permalink / raw)
To: Philip Yarra <philip@utiba.com>; +Cc: pgsql-interfaces
On Thu, Feb 27, 2003 at 01:05:27PM +1100, Philip Yarra wrote:
> 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:
ECPG is NOT thread save at the moment. Lee is working on it, but there
is not timetable yet.
Michael
--
Michael Meskes
Email: Michael@Fam-Meskes.De
ICQ: 179140304
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-02-27 13:50 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-27 02:05 ECPG: EXEC SQL execute sometimes not returning? Philip Yarra <philip@utiba.com>
2003-02-27 10:13 ` Aidamir Lovpache <archibald@mastak.ru>
2003-02-27 13:50 ` Michael Meskes <meskes@postgresql.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox