server side resultset search (for performance reasons) - Mailing list pgsql-jdbc

I have a requirement that don't know how to accomplish:

The application is returning 300,000 records in some SELECT clause (a
ResultSet) this finish the client's JVM memory, so my solution to this
was to use the SELECT's LIMIT clause returning 600 records and making
the internal logic that when the user gets past the 600th record the
application automatically issues another query with LIMIT and OFFSET
clauses causing a little delay but this is better than finishing the
memory in the client's machine (if you know a better solution or have
some advices, please let me know).

My request here is, that if the user makes the record 250,000 the
current record, because in the grphical interface (a JTable by the way),
and selects to ORDER BY a different column, the record number 250,000
will be completely a different record, and the application have to point
to the same record before the order by.
Because the JTable is always showing a postgres table, and my postgres
tables always have a primary key, I can identify (by means of the
primary key) one record, I can get the Primary Key of the actual record,
and after the ORDER BY, I can search (secuentially in the resultset) the
record pointer (this is actually the row index in the rresultset) until
the PK matches.

Before I change to the LIMIT version of my SELECTs, this was no problem,
the search was in memory within the entire ResultSet, but this is
prohibitive in very large resultsets, because of the memory constraints.

I have looked that in the server side the query is made very
efficiently, because the response time for the first 600 records is very
fast, ( the last 600 records are no as fasts as the firsts).

So I was trying to find some way to make the search of the record index
in the server side if possible, because actually, I make the search on
the client side, but it is very slow, the client gets 600 records, make
the search and if the record is not found gets another 600 records
(other SELECT clause) and searches again, and if not found, gets another
600 records...... and so on, making this process very slow.

Some ideas that came to me reading the docs, are a Stored Procedure that
can reach the resultset produced in the server side, that receives the
target record primary key, and makes the search returning -1 or the
record index.

Other idea is send with the SELECT a function like
"SELECT *, getIndex(currRecordPK) FROM mytable WHERE mycondition ORDER
BY micolumn"

in this case getIndex is a function that compares the Primary Key of the
current record with the one in the parameter (I have never made a
Postgresql function so maybe the way I say this works is wrong), and put
the index of the record that matches (if in the resultset is the record)
in somewhere that I can retrieve to make the current Record of the new
ResultSet.

I hope someone can help me further in this.

Thanks in advance

Jesus Sandoval