[Pljava-dev] advice needed

>pljava approach:
>~~~~~~~~~~~~~~~~
>
>>1. same as above (assume the table is properly populated);
>>2.
>> - CREATE TYPE details_fn AS (name text);
>> - CREATE FUNCTION details_fn()
>> RETURNS SETOF details_v
>> AS 'whatever-package.DetailsView'
>> IMMUTABLE LANGUAGE java;
>>
>> - now, my understanding is that in the Java class DetailsView I have to
>>implement a method
>>
>> public boolean assignRowValues(ResultSet receiver, int currentRow)
>>throws SQLException {
>> }
>>
>> which is called until the return value is false (i.e. no more rows are
>>to be returned) and this needs to populate the one-row resultset which is
>>due to be displayed to the client, i.e.
>> select * from details_fn() should, in theory, get the desired results.
>>
>> public class DetailsView implements ResultSetProvider{
>> private ResultSet theResult;
>> public DetailsView() {
>> Connection con =
>>DriverManager.getConnection("jdbc:default:connection");
>> Statement st = con.createStatement();
>> this.theResult = con.executeQuery("select forename, surname from
>>details_tbl where title ilike 'mr'");
>> }
>> }
>>
>> The difficulty I am having is that even though I can determine the
>>entire rowset at the constructor, when the 'select * from details_fn()'
>>statement is run the assignRowValues method gets called to populate a
>>single row of the receiver. So, in order to build the entire set I would
>>need to do something like:
>>
>> public boolean assignRowValues(ResultSet receiver, int currentRow)
>>throws SQLException {
>> if (this.theResult != null && this.theResult.absolute(currentRow)
{
>> receiver.updateString(1, this.theResult.getString(1) + " " +
>>this.theResult.getString(2));
>> return true;
>> else return false;
>> }
>>
>>If the above is correct as I already pointed out this would be very
>>inneficient, because of all the 'absolute' method calls (and subsequent
>>scans of the entire result set object).
>>
>>If I am not correct I welcome suggestions as to how this can be
implemented?
>>
>>
>Firs of all, I'm sorry I misread you question. There are two ResultSet's
>involved in your scenario, not just the one I thought you where
>referring to, namely the parameter. I missed the whole point :-)

>You are guaranteed that the currentRow will be increased with exactly
>one for each call, so there's no need to do absolute positioning. A
>simple next will be sufficient.

Nice to know. In this case a couple more questions:

1. I assume the main class is created (and the class constructor is called)
upon a direct call to the function and then instance is left to be
destroyed by the gc.
If that is the case this wouldn't be the most wise thing to do because of
(at least) 2 reasons: 1. Class cleanup (in my example I have to perform
connection shutdown as well as Resultset and Statement close upon
'completion' or exception event of each task), and 2: there is no way on
Earth you can manage multiple instances and synchorization (unless you
synchronise all methods - way too slow).

Wouldn't it be better for pljava to create a pool of instances and present
them to the caller each time a call to the function is made. If you adopt
this approach and extend the ResultSetProvider class to include at least
two more methods for managing class 'activate' and 'deactivate' events
while keeping/recycle the class instance that would bring a performance
boost (not to mention the memory management improvements). You can then add
a few more set of options in postgresql.conf file to configure the object
pool. I am using a similar pool here on our system (it deals with between
30 and 120 different class instances existing in the pool at any point in
time, each of which has an independent network connection to a client) and
by using a pool of objects (as oppose to class instances
creation/destruction upon every call) this brings a performance boost
between 17 and 28% of the entire system.

2. I am no expert in PostgreSQL internals (in fact I don't know anything
about that at all), but with the above class (DetailsView) wouldn't be
wiser to call 'a method' once and get the result in one go, instead of
calling assignRowValues for each individual row. I think I know the answer
to that one, but it is worth asking and give it a go anyway (;

>
>Another approach could be to build an ArrayList of objects (some class
>that contains the two strings) in the constructor or on the first call
>to assignRowValues. It all depends on what you are after (I assume its
>more then just implementing a view), how much data that is expected to
>build up, etc.
>

This is worth considering providing I work with small amount of data, which
I don't - it ranges in the 100K-740K records mark and about 10% of it is
bytea (hence my delight that the new version of the postgresql.jar has been
enchanced to include proper treatment of bytea objects without eating
3-times as much memory as before - it was a real nightmare). To answer you
question - no, it is better to utilise ResultSet as I would get the result
on a per-required basis with little memory footprint.

>>The reason I've said that the examples are rather daft (and I stick with
>>what I've said) is that, yes, even though I *did* look at the code and all
>>the examples, in all of them the SETOF returned is determined without any
>>involvement of datasets or database iteraction in general, i.e. in
>>assignRowValues the receiver set row is determined purely on the basis of
>>simple integer additions and the current timestamp - no database
iteraction
>>whatsoever as shown in my example above. In other words, if I want to
>>implement a view result set based on *real* data (and *not* just simple
>>integer additions) there is nowhere to look at!
>>
>>Do you get my point?
>>
>>
>I do. And if I succeed helping you implement what you need then you're
>more then welcome to submit additional examples.

Once I clarify the above questions I am willing to contribute.

Regards,

George

--------------------------------------------------------------------
mail2web - Check your email from the web at
http://mail2web.com/ .