From: | P(dot)Rizzi Ag(dot)Mobilità Ambiente <paolo(dot)rizzi(at)ama-mi(dot)it> |
---|---|
To: | "PostgreSQL pgsql-general (E-mail)" <pgsql-general(at)postgresql(dot)org> |
Subject: | JDBC query creates a suspended Linux process |
Date: | 2006-01-18 17:00:45 |
Message-ID: | 1FED0E09CDDD1F4582BC454C4C879FAD72B61B@exchangesrv.agenzia.dom |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi everybody, this is my first post on this list.
I searched the archives but couldn't find a solution for this.
I have a PostgreSQL+PostGIS server installed on Linux with this
configuration:
PostgreSQL 8.0.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.1
(SuSE Linux)
Linux is a SuSE 9.0 and the box is a two Xeon processor with 2GB RAM and two
SATA HD
configured in RAID 1 through an hardware adapter using ReiserFS.
Now if I issue the query detailed below to this server using pgAdmin running
on Windows, the query takes a reasonable time to complete (around 12
seconds).
If I issue the same query from a Java application using JDBC, the same query
takes
a very long time and this happens both if I ran the Java program from the
same Windows
machine or from the same Linux box where the PostgreSQL server is running.
If I issue a ps command I can see that the Linux process running my query is
suspended
most of the time:
postgres 26992 1.9 0.9 36784 20040 ? S 18:13 0:13
postgres: postgres prova_sv3
192.1.1.36(1273) INSERT
For example in this case I run the query more then 10 minutes ago, but it
actually
ran for only 13 seconds!!!
The query is like this:
begin;
SELECT "sv_r".sv_ver_remarks('commit');
INSERT INTO "sv_r"."Corsie"
SELECT
"tipoCorsie_codice","posizione_codice","segm_progInizio","segm_progFine","se
nso_elementoStradale_id","senso_sensoMarcia","ord","corsieDisegnate","numero
Corsie","larghezzaMediaCorsie","geom","sv_ver"
FROM "sv_d"."Corsie";
commit;
SELECT "sv_r".sv_ver_remarks('commit'); invokes a simple pl/pgSQL function.
INSERT INTO "sv_r"."Corsie" instead invokes a pl/pgSQL trigger function on
each
row that does a certain amount of work.
SELECT ... FROM "sv_d"."Corsie"; reads from a complex view.
So the query is complex, but why it takes 12 seconds from pgAdmin and
forever from JDBC???
I'm pretty sure to be correctly using a single transaction from JDBC.
I'm not an expert with PostgreSQL nor I am a Linux guru so maybe is
something really stupid...
Thanks a lot in advance to anybody who'll help me!!!
Bye
Paolo Rizzi
AVVERTENZE AI SENSI DEL D. LGS. 196/2003
Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i
file/s allegato/i, sono da considerarsi strettamente riservate. Il loro
utilizzo è consentito esclusivamente al destinatario del messaggio, per le
finalità indicate nel messaggio stesso. Qualora riceveste questo messaggio
senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia
via e-mail e di procedere alla distruzione del messaggio stesso,
cancellandolo dal Vostro sistema; costituisce comportamento contrario ai
principi dettati dal D. Lgs. 196/2003 il trattenere il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse.
From | Date | Subject | |
---|---|---|---|
Next Message | Eric Davies | 2006-01-18 17:00:47 | bigger blob rows? |
Previous Message | Scott Marlowe | 2006-01-18 16:55:43 | Re: RAID 5 and postgresql |