JDBC driver bug?
YourSoft
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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
Heikki Linnakangas
> 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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Albe Laurenz
> 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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Tom Lane
> 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
Heikki Linnakangas
> Heikki Linnakangas <hei...@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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?