Re: JDBC driver bug?

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

5 марта 2007 г. в 12:05:35

Heikki Linnakangas  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

JDBC driver bug?

От:

YourSoft <yoursoft@freemail.hu>

Дата:

5 марта 2007 г. в 07:20:21

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

Re: JDBC driver bug?

От:

Heikki Linnakangas <heikki@enterprisedb.com>

Дата:

5 марта 2007 г. в 07:43:32

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

Re: JDBC driver bug?

От:

Heikki Linnakangas <heikki@enterprisedb.com>

Дата:

5 марта 2007 г. в 12:19:45

Tom Lane wrote:
> Heikki Linnakangas  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

Re: JDBC driver bug?

От:

"Albe Laurenz" <all@adv.magwien.gv.at>

Дата:

5 марта 2007 г. в 10:36:21

> 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