Search Postgresql Archives

JDBC query creates a suspended Linux process

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux