Hi,
 
I've installed postgres 7.3.5 on a fedora server. It works fine, at
least on a local basis. I perform an easy select * from a table and I
get the answer in approximatively 1s. 
Now, I used the jdbc driver (jdbc3) for postgres. It connects fine but,
performing the same query, I get the answer in 7s.
What could be my problem?
 
Thanks in advance.














Hi,
 
I’ve installed postgres 7.3.5 on a fedora server. It works fine, at least
on a local basis. I perform an easy select * from a table and I get the answer
in approximatively 1s. 
Now, I used the jdbc driver (jdbc3) for postgres.
It connects fine but, performing the same query, I get the answer in 7s.
What could be my problem?
 
Thanks in advance.
Thu, 30 Sep 2004 11:50:25 +0200
"Johann Robette" <jrobette@onyme.com>
Remote query very slow
Johann Robette wrote:

> Iï¿½ve installed postgres 7.3.5 on a fedora server. It works fine, at 
> least on a local basis. I perform an easy select * from a table and I 
> get the answer in approximatively 1s.
> 
> Now, I used the jdbc driver (jdbc3) for postgres. It connects fine but, 
> performing the same query, I get the answer in 7s.
> 
> What could be my problem?

Is it really a "remote query" problem? Or is it just a difference 
between using psql and JDBC? How fast is the query if you run it 
"remotely" via psql? (psql -h server database)

GC is the most likely culprit if you are running with default heap 
settings and a large data set. Without instructions to the contrary 
(e.g. Statement.setFetchSize) the driver will try to pull the entire 
resultset into heap before returning from query execution. Try turning 
on -verbose:gc on your JVM and see how much time is spent doing GC.

You probably want to upgrade both your server and JDBC driver (assuming 
you're using the 7.3-era driver) if you can. The 7.3 server is getting 
quite old now, and there are a lot of bugfixes in the current driver 
that aren't in the 7.3 JDBC driver.

-O

Fri, 01 Oct 2004 09:55:14 +1200
Oliver Jowett <oliver@opencloud.com>
Re: Remote query very slow
Thanks for your answer.
It seems that my problem lie on the not use of Statement.setFetchSize
and the use of the heap.

In fact, it appears that when I select all the fields, it's very slow
but when I select only the PK it's already better. So it seems that the
driver is waiting for the whole data and, only then, returns.

I should investigate in that way.

Last question, I'm using pgAdminIII to perform my queries. I think that
I should have the same problem on the server side than on the remote
machine as it is using the same driver. Am I wrong on that point?

Thanks a lot

-----Message d'origine-----
De : pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] De la part de Oliver Jowett
Envoyé : jeudi 30 septembre 2004 23:55
À : Johann Robette
Cc : pgsql-jdbc@postgresql.org
Objet : Re: [JDBC] Remote query very slow

Johann Robette wrote:

> Ive installed postgres 7.3.5 on a fedora server. It works fine, at 
> least on a local basis. I perform an easy select * from a table and I 
> get the answer in approximatively 1s.
> 
> Now, I used the jdbc driver (jdbc3) for postgres. It connects fine
but, 
> performing the same query, I get the answer in 7s.
> 
> What could be my problem?

Is it really a "remote query" problem? Or is it just a difference 
between using psql and JDBC? How fast is the query if you run it 
"remotely" via psql? (psql -h server database)

GC is the most likely culprit if you are running with default heap 
settings and a large data set. Without instructions to the contrary 
(e.g. Statement.setFetchSize) the driver will try to pull the entire 
resultset into heap before returning from query execution. Try turning 
on -verbose:gc on your JVM and see how much time is spent doing GC.

You probably want to upgrade both your server and JDBC driver (assuming 
you're using the 7.3-era driver) if you can. The 7.3 server is getting 
quite old now, and there are a lot of bugfixes in the current driver 
that aren't in the 7.3 JDBC driver.

-O

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend




Fri, 1 Oct 2004 09:04:37 +0200
"Johann Robette" <jrobette@onyme.com>
RE : Remote query very slow
Johann Robette wrote:

> Last question, I'm using pgAdminIII to perform my queries. I think that
> I should have the same problem on the server side than on the remote
> machine as it is using the same driver. Am I wrong on that point?

pgAdmin uses libpq for communication with the server, and is written in 
C. The JDBC driver implements the client/server protocol itself, and is 
written in Java. So they're not really comparable.

-O

Fri, 01 Oct 2004 19:36:20 +1200
Oliver Jowett <oliver@opencloud.com>
Re: RE : Remote query very slow
Hello,

I'm experiencing a strange problem. Here it is :
I've created a function with a FOR loop.

DECLARE
Current RECORD;
BEGIN
FOR current IN SELECT * FROM employees LOOP
		Tmp := current.id;
END LOOP;
...

When I call the function, I get the error :
	ERROR:  record " current " is unassigned yet

Any idea?



Fri, 1 Oct 2004 11:24:39 +0200
"Johann Robette" <jrobette@onyme.com>
Record unassigned yet
On Fri, 1 Oct 2004 07:24 pm, Johann Robette wrote:
> Hello,
> 
> I'm experiencing a strange problem. Here it is :
> I've created a function with a FOR loop.
> 
> DECLARE
> Current RECORD;
> BEGIN
> FOR current IN SELECT * FROM employees LOOP
>   Tmp := current.id;
> END LOOP;
> ...
current != Current ?

> 
> When I call the function, I get the error :
>  ERROR:  record " current " is unassigned yet
> 
> Any idea?
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faqs/FAQ.html
> 
> 

Fri, 1 Oct 2004 21:19:51 +1000
Russell Smith <mr-russ@pws.com.au>
Re: Record unassigned yet
Oh sorry about that!
No, I was alright in the syntax. Both are defined as current.
I can't find where the problem lies...


-----Message d'origine-----
De : pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] De la part de Russell Smith
Envoyé : vendredi 1 octobre 2004 13:20
À : pgsql-hackers@postgresql.org
Objet : Re: [HACKERS] Record unassigned yet

On Fri, 1 Oct 2004 07:24 pm, Johann Robette wrote:
> Hello,
> 
> I'm experiencing a strange problem. Here it is :
> I've created a function with a FOR loop.
> 
> DECLARE
> Current RECORD;
> BEGIN
> FOR current IN SELECT * FROM employees LOOP
>   Tmp := current.id;
> END LOOP;
> ...
current != Current ?

> 
> When I call the function, I get the error :
>  ERROR:  record " current " is unassigned yet
> 
> Any idea?
> 
> 
> 
> ---------------------------(end of
broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faqs/FAQ.html
> 
> 

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your
      joining column's datatypes do not match




Fri, 1 Oct 2004 13:28:33 +0200
"Johann Robette" <jrobette@onyme.com>
RE : Record unassigned yet
Johann Robette wrote:
> Oh sorry about that!
> No, I was alright in the syntax. Both are defined as current.
> I can't find where the problem lies...

The following works fine for me.

=== BEGIN test ===
DROP TABLE employees;

CREATE TABLE employees (id integer, name text);
COPY employees FROM stdin;
1   Aaron Aardvark
2   Betty Bee
3   Carl Cat
\.

CREATE OR REPLACE FUNCTION emp_test_fn() RETURNS boolean AS '
DECLARE
     current RECORD;
     Tmp     integer;
BEGIN
     FOR current IN SELECT * FROM employees LOOP
         Tmp := current.id;
         RAISE NOTICE ''tmp = %'', Tmp;
     END LOOP;
     RETURN true;
END
'
LANGUAGE 'plpgsql';

SELECT emp_test_fn();
=== END test ===

PS - please reply to the -general list not -hackers.

-- 
   Richard Huxton
   Archonet Ltd

Fri, 01 Oct 2004 12:45:54 +0100
Richard Huxton <dev@archonet.com>
Re: RE : Record unassigned yet
"Johann Robette" <jrobette@onyme.com> writes:
> I've created a function with a FOR loop.

> DECLARE
> Current RECORD;
> BEGIN
> FOR current IN SELECT * FROM employees LOOP
> 		Tmp := current.id;
> END LOOP;
> ...

> When I call the function, I get the error :
> 	ERROR:  record " current " is unassigned yet

> Any idea?

What you showed us looks fine, therefore the problem is in something you
didn't show us.

			regards, tom lane

Fri, 01 Oct 2004 10:47:05 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Record unassigned yet 
Thanks for your answer.
In fact, I had a syntax error in my function.
Sorry about that!

-----Message d'origine-----
De : Tom Lane [mailto:tgl@sss.pgh.pa.us] 
Envoyé : vendredi 1 octobre 2004 16:47
À : Johann Robette
Cc : pgsql-hackers@postgresql.org
Objet : Re: [HACKERS] Record unassigned yet 

"Johann Robette" <jrobette@onyme.com> writes:
> I've created a function with a FOR loop.

> DECLARE
> Current RECORD;
> BEGIN
> FOR current IN SELECT * FROM employees LOOP
> 		Tmp := current.id;
> END LOOP;
> ...

> When I call the function, I get the error :
> 	ERROR:  record " current " is unassigned yet

> Any idea?

What you showed us looks fine, therefore the problem is in something you
didn't show us.

			regards, tom lane



Fri, 1 Oct 2004 16:51:14 +0200
"Johann Robette" <jrobette@onyme.com>
RE : Record unassigned yet 