[Pljava-dev] Stale Handle to native structure?

Mitch,
PostgreSQL has some limitations with nested functions that produce
result sets. PL/Java tries to make the best of it but some things just
cant be done. Streaming data one row at a time is especially difficult.
I can't tell from your code if that's what you are doing (I don't know
what your "getter" is or what it does behind the scenes) but if it
provides your assignRowValues with stuff that in turn is read from
another ResultSet, then that might be the cause of your problem.

If so, and unless you have huge sets involved, you could try to build
the set completely in memory in the constructor and then pick the rows
from there.

Regards,
Thomas Hallgren

Mitch Chapman wrote:

> 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
>
> _______________________________________________
> Pljava-dev mailing list
> Pljava-dev at gborg.postgresql.org
> http://gborg.postgresql.org/mailman/listinfo/pljava-dev