Thread: JDBC driver bug?
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
YourSoft wrote: > 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: That's a known issue. The first time you call the procedure, it's compiled and cached. The second time you call it, the cached plan is no longer valid because the function it depends on has been dropped and recreated. The good news is that Tom Lane has added support for plan invalidation for 8.3 branch, so this should be fixed in the next major release. Until that, you can just disconnect and reconnect, which clears the per-connection cache, and it should work. BTW, this problem is not specific to JDBC, another mailing list would've been more appropriate. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
> 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 Actually, that's a feature, and it has nothing to do with JDBC. Static SQL in PL/pgSQL ist parsed when you first execute the function, and the execution plan is retained and used in subsequent invocations. If you want a function to use a database object that is dropped and recreated with the same name, you should use dynamic SQL. Yours, Laurenz Albe
Heikki Linnakangas <heikki@enterprisedb.com> writes:
> YourSoft wrote:
>> 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:
> That's a known issue. The first time you call the procedure, it's
> compiled and cached. The second time you call it, the cached plan is no
> longer valid because the function it depends on has been dropped and
> recreated.
> The good news is that Tom Lane has added support for plan invalidation
> for 8.3 branch, so this should be fixed in the next major release.
This behavior will not change in 8.3, because I have no intention of
including function invalidation in the patch. The correct answer is
"don't do that --- use CREATE OR REPLACE FUNCTION instead".
regards, tom lane
Tom Lane wrote: > Heikki Linnakangas <heikki@enterprisedb.com> writes: >> The good news is that Tom Lane has added support for plan invalidation >> for 8.3 branch, so this should be fixed in the next major release. > > This behavior will not change in 8.3, because I have no intention of > including function invalidation in the patch. The correct answer is > "don't do that --- use CREATE OR REPLACE FUNCTION instead". Oh, sorry about that. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com