From: mitchchapman at earthlink.net (Mitch Chapman)
Date: Thu, 21 Jul 2005 18:55:01 -0600
Subject: [Pljava-dev] Stale Handle to native structure?
Message-ID: <7D0D6BCD-95F7-4AF7-9B44-5EB7ED912590@earthlink.net>

Hi list,

I'm having problems with a PL/Java extension function which returns a  
SETOF records.  When I join the results from this function with  
another table, specifying an ORDER BY clause, I get the traceback  
seen below.

This is with pljava-1.0.0, on a Redhat Linux system running JRE 1.4.2- 
b28 and PostgreSQL 8.0.3.  The same function, and the same query,  
running on a Mac OS X 10.4.1 system with pljava-1.0.0, JRE 1.4.2_07,  
and PostgreSQL 8.0.3, produces no errors.

Thanks for any help.  Please let me know if you need more info.


Here's the traceback from the postmaster:
LOG:  Exception
LOG:  in thread "main"
java.sql.SQLException: Stale Handle to native structure
         at org.postgresql.pljava.internal.Tuple._getObject(Native  
Method)
         at org.postgresql.pljava.internal.Tuple.getObject(Tuple.java: 
32)
         at org.postgresql.pljava.jdbc.SPIResultSet.peekNext 
(SPIResultSet.java:172)
         at org.postgresql.pljava.jdbc.SPIResultSet.next 
(SPIResultSet.java:80)
         at org.postgresql.pljava.sqlj.Loader.getSchemaLoader 
(Loader.java:88)
ERROR:  java.sql.SQLException: Stale Handle to native structure


The function itself performs database queries to produce its results,  
but I don't have access to the relevant source code.  Here's a  
paraphrase of my code, modified to hide proprietary info (but  
hopefully not modified so much as to hide the cause of the problem):

public class plJavaExampleSearch extends MySearchAlgorithm
     implements ResultSetProvider {
     public plJavaExampleSearch(String pattern, float threshold)  
throws Exception {
         super();
         searchFor(pattern, threshold);
     }

     public boolean assignRowValues(ResultSet receiver, int currentRow)
     throws SQLException
     {
         boolean result = false;

         if ((0 <= currentRow) && (currentRow < getter.getResultCount 
())) {
             int record_id = getter.getResult(currentRow);
             float similarity = getter.getSimilarity(currentRow);
             receiver.updateInt(1, record_id);
             receiver.updateFloat(2, similarity);
             result = true;
         }
         return result;
     }

     public static ResultSetProvider findMatches(String pattern,  
float threshold) {
         ResultSetProvider result;

         try {
             result = new plJavaExampleSearch(pattern, threshold);
         } catch (Exception e) {
             result = new EmptyResultSetProvider();
         }
         return result;
     }

And here's the DDR with which it is installed:

SQLActions[] = {
"BEGIN INSTALL
   CREATE TYPE searchResult
       AS (record_id integer, similarity real);

   CREATE FUNCTION findMatches(varchar, float4, int4, int4)
     RETURNS SETOF searchResult
     AS 'plJavaExampleSearch.findMatches'
     IMMUTABLE LANGUAGE java;
     END INSTALL",

"BEGIN REMOVE
   DROP FUNCTION findMatches(varchar, float4, int4, int4);
   DROP TYPE searchResult;
END REMOVE"
}

This query (again, paraphrased, sorry) produces good results:

SELECT findMatches('abcd', 0.5);

This query does not:

SELECT fss.record_id, fss.similarity, src_table.another_attribute
FROM findMatches('abcd', 0.5) AS fss, src_table
WHERE fss.record_id = src_table.record_id
AND src_table.another_attribute > 1.2
ORDER BY fss.similarity DESC;


--
Mitch