Function with raise notice statements taking too long

From: Altaf Malik <mmalik_altaf(at)yahoo(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Function with raise notice statements taking too long
Date: 2010-05-26 08:50:16
Message-ID: 34816.46946.qm@web110412.mail.gq1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Hi,
I have a plpgsql function with raise notice statements in a loop. When I call this function from JDBC, it takes almost 5 minutes to execute. However, if I invoke it through pgAdminIII, it gets executed in just 12 seconds. The code of the function is given below:

CREATE OR REPLACE FUNCTION f1() RETURNS void AS
$BODY$ declare
i integer := 0;
start_time timestamp;
end_time timestamp;
begin
start_time := now();
LOOP
exit when i = 100000;
RAISE NOTICE 'Program is on the line number: %',i ;
i := i + 1;
END LOOP;

end_time := now();

RAISE NOTICE 'Program start time: %', start_time;
RAISE NOTICE 'Program End time: %', end_time;

end; $BODY$
LANGUAGE 'plpgsql'

I invoke it from pgAdminIII using: select f1()

I used the following java program to execute it and it takes too long.

Class.forName("org.postgresql.Driver");
Connection con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/postgres","postgres","postgres");
System.out.println("Starting ...");

CallableStatement stmt = con.prepareCall("{call f1()}");
stmt.execute();

SQLWarning warn = stmt.getWarnings();
while (warn != null) {
System.out.println(warn.getMessage());
warn = warn.getNextWarning();
}
System.out.println("Done");

Any clue why JDBC is performing too slow?

I am using:

Databse: "PostgreSQL 8.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-10), 32-bit"
Driver: 8.5devel

Regards,
Altaf Malik

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Kris Jurka 2010-05-26 19:21:33 Re: Function with raise notice statements taking too long
Previous Message Kris Jurka 2010-05-24 18:25:51 Re: Using java.lang.Character for "char" data type