JDBC driver bug?

From: YourSoft <yoursoft(at)freemail(dot)hu>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: JDBC driver bug?
Date: 2007-03-05 10:56:50
Message-ID: 45EBF772.9070604@freemail.hu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Dear Developers!

I found the following bug??:
When you call a pgsql stored procedure (with PreparedStatement), that
calls an other stored procedure, and you recall the stored procedure
after dropping and recreating second stored procedure, the calling will
throw an exception with:

org.postgresql.util.PSQLException: ERROR: function with OID 63315074
does not exist
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:255)

If you restart the program (create new jvm, and driver reloading), the
result will be OK.

An example program:
// ***************************************************
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
*
* @author lutischan1fb16
*/
public class Test {

public static void main(String[] args) {
Connection con = null;
try {
Class.forName("org.postgresql.Driver");
con =
DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/test",
"user", "psw");
PreparedStatement pstm = con.prepareStatement("select
test.test1(?,?)");
pstm.setInt(1, 1 );
pstm.setString(2, "2" );
ResultSet rs = pstm.executeQuery();
rs.close();
pstm.close();

// Insert Into following line a break point
System.out.println("Now you can DROP and CREATE OR REPLACE
FUNCTION test.test2");

pstm = con.prepareStatement("select test.test1(?,?)");
pstm.setInt(1, 1 );
pstm.setString(2, "2" );
rs = pstm.executeQuery();
rs.close();
System.out.println("End OK");
} catch (Exception e) {
System.out.println("End NO OK");
e.printStackTrace();
} finally {
try { con.close(); } catch (Exception e) {}
}
}
}
// ***************************************************

Database scripts:

-- DROP FUNCTION test.test1(p_a integer, p_b character varying);

CREATE OR REPLACE FUNCTION test.test1(p_a integer, p_b character varying)
RETURNS void AS
$BODY$DECLARE p_a ALIAS FOR $1;
DECLARE p_b ALIAS FOR $2;

BEGIN
PERFORM test.test2(p_a, p_b);
RETURN;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION test.test1(p_a integer, p_b character varying) OWNER TO
postgres;
GRANT EXECUTE ON FUNCTION test.test1(p_a integer, p_b character varying)
TO public;
GRANT EXECUTE ON FUNCTION test.test1(p_a integer, p_b character varying)
TO postgres;

-- DROP FUNCTION test.test2(p_a integer, p_b character varying);

CREATE OR REPLACE FUNCTION test.test2(p_a integer, p_b character varying)
RETURNS void AS
$BODY$DECLARE p_a ALIAS FOR $1;
DECLARE p_b ALIAS FOR $2;

BEGIN
RETURN;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION test.test2(p_a integer, p_b character varying) OWNER TO
postgres;
GRANT EXECUTE ON FUNCTION test.test2(p_a integer, p_b character varying)
TO public;
GRANT EXECUTE ON FUNCTION test.test2(p_a integer, p_b character varying)
TO postgres;

Best Regards:
Ferenc Lutischan

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message YourSoft 2007-03-05 10:59:56 JDBC driver bug?2
Previous Message peter royal 2007-03-05 06:22:54 Re: statement caching proof of concept redux