agora inbox for pljava-dev@postgresql.org  

help / color / mirror / Atom feed

[Pljava-dev] advice needed
12+ messages / 0 participants
[nested] [flat]

* [Pljava-dev] advice needed
@ 1970-01-01 00:00  
  parent: 
  1 sibling, 2 replies; 12+ messages in thread

From:  @ 1970-01-01 00:00 UTC (permalink / raw)

On Wed, 16 Feb 2005 17:53:46 +0100, Thomas Hallgren wrote:

>Keep in mind that the PostgreSQL backend is inherently single threaded. 
>You have one JVM per session. This means two things. 1) you hardly ever 
>need to worry about synchronization. The only time you'll need it is 
>when you expect finalizers to do something that might conflict with the 
>main thread. 2) Since you hardly ever will encounter a contended 
>synchronization locks, the overhead of using synchronizers will be 
>almost none at all. A modern JVM is extremely fast when it comes to 
>uncontendend synchronizations.
>
>Don't rely on finalizers to do cleanup. Do that just prior to returning 
>false in the assignRowValues.

Frankly, the whole idea of having a method called each time to set a ResultSet containing one row does not inspire me at all - it has to be said!

Wouldn't it be better to have a single method like:


// client interface method
public ResultSet getResult();

The receiver presented in the assignRowValues is already a ResultSet consisting of a single row. If you are worried about type matching you can easily check for that by gathering the appropriate meta data (as you do with a normal ResultSet) or throw an exception if type constraints have been violated (see below).
Why is it a problem to supplement it with a ResultSet object containing not one row, but the entire set and then iterate over it *internally*, using next? 
In a way what I am suggesting is that the call to 'next' instead of being in the assignRowValues as you suggested to be processed internally after a call is made to getResult() for the entire set instead, i.e: 

current approach:
~~~~~~~~~~~~
1. set a one-row ResultSet object;
2. set row number value;
3. call assignRowValues;
4. process result;

new approach:
~~~~~~~~~~
1. call getResult();
2. process result (iterate over each row, if necessary, otherwise store the ResultSet internally for later processing);


If there is a requirement/constraint from PostgreSQL to process a single row at a time, then call getResult once, store the result internally and use it in another private get-one-resultset-row method (probably similar to assignRowValues) within pljava.
That I think would be much easier for developers like myself to handle it - all I have to worry about then is to prepare the ResultSet in a way I want it without the need to get bogged down in implementing iteractions and mess about with row numbers and the like.

Better still, you can define and 'fire' different events through the entire process to give developers more control of what is being done. If adopted I have a suggestion of (at least) three such events (I suspect these methods will be in addition to the once controlling the pool behaviour, like 'make', 'activate', 'passivate' and 'destroy'):

public void initialise(); // fired before the getResult() interface method is actually called to give the client class a chance to initialise itself
public void lastRowProcessed(); // fired after the last row of the ResultSet has been processed;
public void processException(Exception e); // when pljava/processing exception has occured


>
>>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.
>>  
>>
>Yes, using a pool is an excellent idea. Stay tuned for next release ;-)

I can't wait 'til the next release - I want it *NOW* (;

Seriously though, by having supplemented the assignRowValues thingy with one swift ResultSet getter is going to be a lot better me thinks.

>Meanwhile, try something like this (replace PooledProvider with a name 
>of choice):

Nah! When I have time (probably around Friday time by the looks of it) I'll post the code of our pool framework based on Jakarta's Generic Object Pool which has been used by us for the past 7 months - all that without a single glitch (I can hardly recall stopping the service or bringing down the servers more than 2-3 times since it was launched). 
The framework is very robuslt and as I pointed out earlier has dealt with a lot of pounding in the past.


>>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 (;
>>  
>>
>The reason is a combination of factors:
>1. The PostgreSQL backend function interface stipulates that you 
>implement a function that returns one row at a time.
>2. Returning all in one go implies that you either build everything up 
>in memory (not an option for you), or that you create some kind of
>implementation that in turn can return one row at a time. Then you've 
>gained nothing since that's exactly what the ResultSetProvider does.

There is one single most important and precious benefit of it all: *TIME*. By doing a single call you are going to save developers a great deal of development time, which would be otherwise spent/wasted on implementing the assignRowValues classes. By having a single call (getResult() as I suggested above) I wouldn't worry too much about what goes on and can concentrate on other important tasks.
This may not sound much but when you have about 800+ classes spawned in over 54 packages to deal with it becomes a real issue and time is a precious commodity indeed.

>Having said that, perhaps PLJava should provide a variant that allowes 
>you to return a ResultSet and thereby bypass the transfer between 
>ResultSet's that has to take place today. We are currently adding 
>DatabaseMetaData support to PLJava and in that addition a new 
>SyntheticResultSet is included that would make such an approach even 
>more valuable.

Eureka! As if you have read my mind!

>
>>Once I clarify the above questions I am willing to contribute.
>>  
>>
>Super!

As I said I'll post the pool framework we use here to start with. Will see how it all goes after that.


Regards,


George






^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* [Pljava-dev] advice needed
@ 1970-01-01 00:00  
  parent: 
  1 sibling, 2 replies; 12+ messages in thread

From:  @ 1970-01-01 00:00 UTC (permalink / raw)

On Wed, 16 Feb 2005 23:29:04 +0100, Thomas Hallgren wrote:

>>// client interface method
>>public ResultSet getResult();
>>The receiver presented in the assignRowValues is already a ResultSet consisting of a single row. If you are worried about type matching you can easily check for that by gathering the appropriate meta data (as you do with a normal ResultSet) or throw an exception if type constraints have been violated (see below).
>>Why is it a problem to supplement it with a ResultSet object containing not one row, but the entire set and then iterate over it *internally*, using next? 
>Let's assume that you want to return a set that cannot be expressed as a 
>single query.

What's the likelyhood of that happening? 

If I have defined a type and then a SET OF return function I would like
to return a homogenous data, otherwise I may as well define an
ArrayList and stuff it with all sort of objects and use the iterator to
iterate over (therefore no SET OF needed at all).

Even if I assume for the moment that you are correct and more likely
than not I would need to gather information from different sources and
prepare the ResultSet (*highly* unlikely, but for the sake of the
argument let's assume that this is the case) then the method described
above can return the entire ResultSet by building each row within the
method itself (no multiple calls necessary), i.e. I can call the
standard rs.moveToInsertRow method as many times as I like until my
ResultSet is ready to be returned.

I don't see why would I need a method which is called separately and
gives me the row number (which I would never use anyway since it is
going to match the 'next' method call or even if I did need it it is
easy enough to set up a class variable called count which increases
each time a call is made - all this is academic, of course) when with a
single call I can build the entire result set whether gathered from
multiple sources or a single one. Again, it is highly likely that since
I'll be using data iteraction I would need to use java.sql.ResultSet.

>Each row in your result contains data that you create from 
>one or more sources. The source might be a socket, a file, a query 
>combined with other sources, etc.

Again, what is the likelihood of that happening - why would I need to
create a SETOF function involved in a query to gather data from a file
or a socket for example? Even if I do, as I pointed out above this can
easily be build within the getResult() method call and the resulting
set (i..e java.sql.ResultSet) created there and then - no need for
multiple calls and no need for passing arguments unnecessarily - so
where is the use of 'fair amount of code' or 'unnaceptable amount of
memory' - no prizes for guessing which one's would perform better -
calling a method once and returning the result in a single statement or
calling another method 1000s of times and passing two parameters each
time the method is called? 

It is your call.

>Point is, you don't have a ResultSet 
>to return. Using the current approach you have no problem doing this. 
>You simply update the row that is passed to with data from your sources, 
>once for each row, and you're all set. The tailor made, single row 
>ResultSet object that is passed to this method of course reused for each 
>call.

Actually the receiver is just that - a result set! Being one row or
multiple rows - it doesn't matter much since it *has* to be of the same
type, so why bother building it step-by-step when it can be build in a
single call?

>
>You must look at this ResultSet object, not as a "set" per se, but as a 
>single Tuple. There's no way to position within this set or add a row. 

Yes, there is - when you build a result set (that is ResultSet as
specified by java.sql) you can always use rs.moveToInsertRow and
rs.updateXXX as many times as you like. Again, this won't be needed if
SQL query is used to get the results. That is only going to be needed
if I decide for whatever reason to integrate my Java code within the
database and *not* use any database iteraction whatsoever, but some
fancy information sources instead as you are suggesting above.

>So current approach:
>~~~~~~~~~~~~
>on first call:
>   1. create a single-row ResultSet object.
>   2. call assignRowValues.
>   3. process result (extract the tuple data from the ResultSet object).
>
>on each subsequent call:
>   1. call assignRowValues using the same single-row ResultSet object.
>   2. process result (extract the tuple data from the ResultSet object).
>
>Now, with your suggested approach you have two choices:
>1. Build a SyntheticResultSet in memory and return it.

Nope! Standard java.sql ResultSet will do fine thank you (which is
*not* entirely in memory but value is retracted on call to next and
getXXX - *big* plus).

> A fair amount of code and the result might consume an unacceptable amount of memory.

Not at all! 

When you build the reult set all you have to do is st.executeQuery
(which you will do anyway if you have to gather data from the database)
and ... well ...that's it - return the ResultSet to the caller and
don't worry about it.

>2. Create your own implementation of ResultSet where you are the 
>implementor of the next() method.  This is a great deal of work. 

Why would I want to do that - with what I was suggesting in my previous
posting is that pljava handles the iteraction from the resulting
ResultSet object depending on whether or not PostgreSQL needs a
single-row iteractions, in other words:

within the C function you call the java class to return the ResultSet
(i.e. getResult) and then if a single-step iteraction is required
pljava (i.e. the C function or whatever the internal implementation is)
iterates over the set - that is it. From developer's point of view the
only 'great deal of work' is supplying the entire ResultSet, which in
case of having this as a result of a SQL query is a piece of cake,
otherwise the steps performed to build it will be *exactly the same*
(if not better) as if a single-row method is called, but more efficient
since the parameter passing and single-row ResultSet creation would not
exist. 


>A lot more then just implementing the assignRowValues method.

Am I talking a different language here? See above!

>
>New approach:
>~~~~~~~~~~~~
>on first call:
>  1. call getResult()
>  2. call next() on the obtained ResultSet
>  3. process result (extract the tuple from the ResultSet object).
>
>on each subsequent call:
>  1. call next()
>  2. process result (extract the tuple from the ResultSet object).
>
>Same amount of work for both approaches. The first approach doesn't 
>suffer from any of the disadvantages that the second approach has so 
>there's a good motivation to keep it.

This is not at all what I was suggesting.

All I was suggesting is that:

1. assignRowValues method be scrapped completely;
2. replaced with a single interface method (I used getResult, but I
think I should have called it getJDBCResultSet instead for clarity),
which returns ResultSet (as in java.sql) and that result set is
processed either as a whole or in a loop internally depending on
whether PostgreSQL internals need it to be preprocessed step by step  -
all that *without any further involvement of the client interface*. 

In other words the client only implements getJDBCResultSet method -
*THAT IS IT*! 

I fail to see where is the 'fair amount of code' or the 'large memory
consumption' as you are suggesting?

>
>
>>That I think would be much easier for developers like myself to handle it - all I have to worry about then is to prepare the ResultSet in a way I want it without the need to get bogged down in implementing iteractions and mess about with row numbers and the like.
>>  
>>
>Yes. The use case you have, when a function actually executes a query 
>and want to return the result of that query is a good motivation to add 
>the new approach.

Which is what happens in 99% of all cases - you have to use java.sql
ResultSet in every database itteration - there is no other choice -
data retrieved is through this result set. 

The primary motive to put java code in a database is that it stays as
close to the data as possible so that queries are much quicker to
execute and so are the updates, otherwise why would you need to put
your java code in the database and run classes to make socket
connections to different places as you suggested - if that was the case
then I may as well have a separate application server to reside the jar
file on and not bother with the damn thing!

>
>>Better still, you can define and 'fire' different events through the entire process to give developers more control of what is being done. If adopted I have a suggestion of (at least) three such events (I suspect these methods will be in addition to the once controlling the pool behaviour, like 'make', 'activate', 'passivate' and 'destroy'):
>>
>>public void initialise(); // fired before the getResult() interface method is actually called to give the client class a chance to initialise itself
>>public void lastRowProcessed(); // fired after the last row of the ResultSet has been processed;
>>public void processException(Exception e); // when pljava/processing exception has occured
>>  
>>
>I assume that 'activate' is the same as 'initialise' and 'passivate' is 
>the same as 'lastRowProcessed'? 

Not quite! Activate occurs after initialise, make occurs before
anything else (make instantiates it - i.e. creates new class, which is
not necessarily the case with a pool since objects are reused).
lastRowProcessed occurs as soon as the last row has been processed, but
before the object has been put back into the pool. One example of use
of these two methods is: use lasRowProcessed to reinitialise text
patterns and write transaction logs for successful operation, use
passivate to validate the object before it is placed back in the pool
(this has a special meaning for the pool factory). Just to clarify the
events as they occur:

new Class();
fire make // after that object is placed in the pool
// when needed object is taken from the pool
fire initialise // validation, re-initialisation of internal variables
and states may occur here
fire activate
call getJDBCResultSet
// process the results
fire lastRowProcessed when the last row of the result set has been
processed
fire passivate
// when object needs to be destroyed
fire destroy
class = null // to be processed by the gc later

process Exception will be invaluable if exception occurs in the
processing of the ResultSet (i.e. internally, within pljava) so that it
can be propagated on (to be logged or appropriate action taken) by the
Java code - i.e. the client interface would know about it - it will
only serve as a feedback method, nothing else.


Regards,



George





^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* [Pljava-dev] advice needed
@ 2005-02-16 01:01  
  0 siblings, 1 reply; 12+ messages in thread

From:  @ 2005-02-16 01:01 UTC (permalink / raw)

Hi, 

'quick' query to all of you gurus:

I need to return a setof predefined complex type (similar to what a view
does in 8.0) using pljava class, but found the pljava interface rather
confusing:

According to the docs I would need to implement assignRowValues to set the
resulting columns for each row returned (defined by the 'currentRow' int
parameter of this method). I found this rather inefficient - if I use a
ResultSet within my class do I have to call rs.seek(currentRow) each time
the assignRowValues method is called (i.e. rs.seek(2), then rs.seek(3) when
the enxt call comes etc)?

Am I missing something here?

Also, in the jdbc sub-package there are quite a few SPI* methods which seem
to mirror their counterparts from the SQL standard (or so was my
impression). 

What is the purpose of these and how can I utilise them, am I better of
using them instead of the 'standard' ones (I have absolutely *no* knowledge
of PostgreSQL internals)? Is it better to use SPIConnection for e.g. rather
than the 'normal' SQL Connection?


The examples given in the examples.jar for complex types return in pljava
are rather daft as they do not use sql result set at all (simple int
addition and a timestamp) so I can't see how to utilise a ResultSet rows
and return them one by one (if that is the idea of assignRowValues).

Appologiles for getting my hands on so many things - I just got into this
(with Oracle is so much easier - you just type #sql select ?,?,? from
my.table in java and off-you-go - it is all done without much hasle.


TA!


George

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






^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* [Pljava-dev] advice needed
@ 2005-02-16 07:41  
  parent: 
  0 siblings, 0 replies; 12+ messages in thread

From:  @ 2005-02-16 07:41 UTC (permalink / raw)

info at wyse-systems.ltd.uk wrote:

>Hi, 
>
>'quick' query to all of you gurus:
>
>I need to return a setof predefined complex type (similar to what a view
>does in 8.0) using pljava class, but found the pljava interface rather
>confusing:
>
>According to the docs I would need to implement assignRowValues to set the
>resulting columns for each row returned (defined by the 'currentRow' int
>parameter of this method). I found this rather inefficient - if I use a
>ResultSet within my class do I have to call rs.seek(currentRow) each time
>the assignRowValues method is called (i.e. rs.seek(2), then rs.seek(3) when
>the enxt call comes etc)?
>
>Am I missing something here?
>  
>
Yes, you miss the whole point. The ResultSet is used because it exposes 
a standard way of building a tuple. The assingRowValues builds one tuple 
at a time and "returns" that tuple to the PostgreSQL backend. You do 
not' need to do any positioning at all. The ResultSet that is used 
contains exactly one row and it is positioned on that row.

Look at the examples in package org.postgersql.pljava.example
ComplexReturn.java
UsingProperties.java

>Also, in the jdbc sub-package there are quite a few SPI* methods which seem
>to mirror their counterparts from the SQL standard (or so was my
>impression). 
>
>What is the purpose of these and how can I utilise them, am I better of
>using them instead of the 'standard' ones (I have absolutely *no* knowledge
>of PostgreSQL internals)? Is it better to use SPIConnection for e.g. rather
>than the 'normal' SQL Connection?
>  
>
You should always use the standard interfaces. The SPI stuff is an 
implementation of those interfaces. Never use the implementation 
directly. If you do, you'll never be able to port your code to other 
databases.

>The examples given in the examples.jar for complex types return in pljava
>are rather daft as they do not use sql result set at all (simple int
>addition and a timestamp) so I can't see how to utilise a ResultSet rows
>and return them one by one (if that is the idea of assignRowValues).
>  
>
You could at least look at the code before you make statements like 
that. ComplexReturn.java and UsingProperties.java both use assignRowValues.

>Appologiles for getting my hands on so many things - I just got into this
>(with Oracle is so much easier - you just type #sql select ?,?,? from
>my.table in java and off-you-go - it is all done without much hasle.
>  
>
Your claim is based on SQLJ and Oracle is dropping its support for that. 
You'll be reduced to using their Java interface directly. It is not 
simpler then PLJava. IMHO, rather the opposite.

Regards,
Thomas Hallgren






^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* [Pljava-dev] advice needed
@ 2005-02-16 11:34  
  0 siblings, 0 replies; 12+ messages in thread

From:  @ 2005-02-16 11:34 UTC (permalink / raw)

>>Hi, 
>>
>>'quick' query to all of you gurus:
>>
>>I need to return a setof predefined complex type (similar to what a view
>>does in 8.0) using pljava class, but found the pljava interface rather
>>confusing:
>>
>>According to the docs I would need to implement assignRowValues to set the
>>resulting columns for each row returned (defined by the 'currentRow' int
>>parameter of this method). I found this rather inefficient - if I use a
>>ResultSet within my class do I have to call rs.seek(currentRow) each time
>>the assignRowValues method is called (i.e. rs.seek(2), then rs.seek(3)
when
>>the enxt call comes etc)?
>>
>>Am I missing something here?
>>  
>>
>Yes, you miss the whole point. 

I don't think so. What I need to build is the following (very simple
example to illustrate my point):

Standard approach:
~~~~~~~~~~~~~~~~~~
1. create table details_tbl (title text, forename text, surname text);
2. create view details_v as select forename || ' ' || surname as name from
details_tbl where title ilike 'mr';
3. getting the result: select * from details_v;

pljava approach:
~~~~~~~~~~~~~~~~

1. same as above (assume the table is properly populated);
2. 
   - CREATE TYPE details_v 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?

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?

>The ResultSet is used because it exposes 
>a standard way of building a tuple. The assingRowValues builds one tuple 
>at a time and "returns" that tuple to the PostgreSQL backend. You do 
>not' need to do any positioning at all. The ResultSet that is used 
>contains exactly one row and it is positioned on that row.
>
>Look at the examples in package org.postgersql.pljava.example
>ComplexReturn.java
>UsingProperties.java
>
>>Also, in the jdbc sub-package there are quite a few SPI* methods which
seem
>>to mirror their counterparts from the SQL standard (or so was my
>>impression). 
>>
>>What is the purpose of these and how can I utilise them, am I better of
>>using them instead of the 'standard' ones (I have absolutely *no*
knowledge
>>of PostgreSQL internals)? Is it better to use SPIConnection for e.g.
rather
>>than the 'normal' SQL Connection?
>  
>
>You should always use the standard interfaces. The SPI stuff is an 
>implementation of those interfaces. Never use the implementation 
>directly. If you do, you'll never be able to port your code to other 
>databases.

Let me just say that at this point I don't really care about portability -
the faster, the better! So, based on that statement, am I better off using
the SPI* methods?

>>The examples given in the examples.jar for complex types return in pljava
>>are rather daft as they do not use sql result set at all (simple int
>>addition and a timestamp) so I can't see how to utilise a ResultSet rows
>>and return them one by one (if that is the idea of assignRowValues).
>>  
>>
>You could at least look at the code before you make statements like 
>that. ComplexReturn.java and UsingProperties.java both use assignRowValues.

See my comments above.


Regards,


George

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






^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* [Pljava-dev] advice needed
@ 2005-02-16 11:35  
  0 siblings, 1 reply; 12+ messages in thread

From:  @ 2005-02-16 11:35 UTC (permalink / raw)

>>Hi, 
>>
>>'quick' query to all of you gurus:
>>
>>I need to return a setof predefined complex type (similar to what a view
>>does in 8.0) using pljava class, but found the pljava interface rather
>>confusing:
>>
>>According to the docs I would need to implement assignRowValues to set the
>>resulting columns for each row returned (defined by the 'currentRow' int
>>parameter of this method). I found this rather inefficient - if I use a
>>ResultSet within my class do I have to call rs.seek(currentRow) each time
>>the assignRowValues method is called (i.e. rs.seek(2), then rs.seek(3)
when
>>the enxt call comes etc)?
>>
>>Am I missing something here?
>>  
>>
>Yes, you miss the whole point. 

I don't think so. What I need to build is the following (very simple
example to illustrate my point):

Standard approach:
~~~~~~~~~~~~~~~~~~
1. create table details_tbl (title text, forename text, surname text);
2. create view details_v as select forename || ' ' || surname as name from
details_tbl where title ilike 'mr';
3. getting the result: select * from details_v;

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?

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?

>The ResultSet is used because it exposes 
>a standard way of building a tuple. The assingRowValues builds one tuple 
>at a time and "returns" that tuple to the PostgreSQL backend. You do 
>not' need to do any positioning at all. The ResultSet that is used 
>contains exactly one row and it is positioned on that row.
>
>Look at the examples in package org.postgersql.pljava.example
>ComplexReturn.java
>UsingProperties.java
>
>>Also, in the jdbc sub-package there are quite a few SPI* methods which
seem
>>to mirror their counterparts from the SQL standard (or so was my
>>impression). 
>>
>>What is the purpose of these and how can I utilise them, am I better of
>>using them instead of the 'standard' ones (I have absolutely *no*
knowledge
>>of PostgreSQL internals)? Is it better to use SPIConnection for e.g.
rather
>>than the 'normal' SQL Connection?
>  
>
>You should always use the standard interfaces. The SPI stuff is an 
>implementation of those interfaces. Never use the implementation 
>directly. If you do, you'll never be able to port your code to other 
>databases.

Let me just say that at this point I don't really care about portability -
the faster, the better! So, based on that statement, am I better off using
the SPI* methods?

>>The examples given in the examples.jar for complex types return in pljava
>>are rather daft as they do not use sql result set at all (simple int
>>addition and a timestamp) so I can't see how to utilise a ResultSet rows
>>and return them one by one (if that is the idea of assignRowValues).
>>  
>>
>You could at least look at the code before you make statements like 
>that. ComplexReturn.java and UsingProperties.java both use assignRowValues.

See my comments above.


Regards,


George

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






^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* [Pljava-dev] advice needed
@ 2005-02-16 12:31  
  parent: 
  0 siblings, 0 replies; 12+ messages in thread

From:  @ 2005-02-16 12:31 UTC (permalink / raw)

info at wyse-systems.ltd.uk wrote:

>I don't think so. What I need to build is the following (very simple
>example to illustrate my point):
>
>Standard approach:
>~~~~~~~~~~~~~~~~~~
>1. create table details_tbl (title text, forename text, surname text);
>2. create view details_v as select forename || ' ' || surname as name from
>details_tbl where title ilike 'mr';
>3. getting the result: select * from details_v;
>
>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.

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.

>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.

>Let me just say that at this point I don't really care about portability -
>the faster, the better! So, based on that statement, am I better off using
>the SPI* methods?
>  
>
You already use the SPI* methods. There is no other  implementation 
available to you. When you do:

Connection con = DriverManager.getConnection("jdbc:default:connection");

you actually get a SPIConnection. The fact that it's all hidden behind standard interfaces has no negative performance impact.

Regards,
Thomas Hallgren







^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* [Pljava-dev] advice needed
@ 2005-02-16 15:43  
  0 siblings, 2 replies; 12+ messages in thread

From:  @ 2005-02-16 15:43 UTC (permalink / raw)

>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/ .






^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* [Pljava-dev] advice needed
@ 2005-02-16 16:53  
  parent: 
  1 sibling, 0 replies; 12+ messages in thread

From:  @ 2005-02-16 16:53 UTC (permalink / raw)

info at wyse-systems.ltd.uk wrote:

>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). 
>  
>
Keep in mind that the PostgreSQL backend is inherently single threaded. 
You have one JVM per session. This means two things. 1) you hardly ever 
need to worry about synchronization. The only time you'll need it is 
when you expect finalizers to do something that might conflict with the 
main thread. 2) Since you hardly ever will encounter a contended 
synchronization locks, the overhead of using synchronizers will be 
almost none at all. A modern JVM is extremely fast when it comes to 
uncontendend synchronizations.

Don't rely on finalizers to do cleanup. Do that just prior to returning 
false in the assignRowValues.

>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.
>  
>
Yes, using a pool is an excellent idea. Stay tuned for next release ;-)
Meanwhile, try something like this (replace PooledProvider with a name 
of choice):

public class PooledProvider implements ResultSetProvider
{
    private static PooledProvider s_pool;   
    private PooledProvider m_next;

    public boolean assignRowValues(ResultSet receiver, int currentRow)
    throws SQLException
    {
        // Code that returns true omitted. This happens just before you 
return false.
        this.deactivate();
        m_next = s_pool;
        s_pool = this;
        return false;
    }

    public static ResultSetProvider getPooledProvider()
    throws SQLException
    {
        PooledProvider ret = s_pool;
        if(ret != null)
            s_pool = ret.m_next;
        else
            ret =  new PooledProvider();
        ret.activate();
        return ret;
    }

    void activate() {}
    void deactivate() {}
}


>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 (;
>  
>
The reason is a combination of factors:
1. The PostgreSQL backend function interface stipulates that you 
implement a function that returns one row at a time.
2. Returning all in one go implies that you either build everything up 
in memory (not an option for you), or that you create some kind of
implementation that in turn can return one row at a time. Then you've 
gained nothing since that's exactly what the ResultSetProvider does.
3. A lot of implementations will return data that doesn't origin from an 
SQL query.

Having said that, perhaps PLJava should provide a variant that allowes 
you to return a ResultSet and thereby bypass the transfer between 
ResultSet's that has to take place today. We are currently adding 
DatabaseMetaData support to PLJava and in that addition a new 
SyntheticResultSet is included that would make such an approach even 
more valuable.

>Once I clarify the above questions I am willing to contribute.
>  
>
Super!

Regards,
Thomas Hallgren






^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* [Pljava-dev] advice needed
@ 2005-02-16 22:29  
  parent: 
  1 sibling, 0 replies; 12+ messages in thread

From:  @ 2005-02-16 22:29 UTC (permalink / raw)

George,
I think we agree that it would be beneficial to add an alternative way 
of doing things such as the getResult() you suggest. I'd like to clarify 
a couple of things with the current design though.

>// client interface method
>public ResultSet getResult();
>
>The receiver presented in the assignRowValues is already a ResultSet consisting of a single row. If you are worried about type matching you can easily check for that by gathering the appropriate meta data (as you do with a normal ResultSet) or throw an exception if type constraints have been violated (see below).
>Why is it a problem to supplement it with a ResultSet object containing not one row, but the entire set and then iterate over it *internally*, using next? 
>  
>
Let's assume that you want to return a set that cannot be expressed as a 
single query. Each row in your result contains data that you create from 
one or more sources. The source might be a socket, a file, a query 
combined with other sources, etc. Point is, you don't have a ResultSet 
to return. Using the current approach you have no problem doing this. 
You simply update the row that is passed to with data from your sources, 
once for each row, and you're all set. The tailor made, single row 
ResultSet object that is passed to this method of course reused for each 
call.

You must look at this ResultSet object, not as a "set" per se, but as a 
single Tuple. There's no way to position within this set or add a row. 
The one and only row is always present and the set is always positioned 
on this row. If there was a standard interface in java.sql representing 
a single Tuple, that interface would have been used instead. But there 
is no such interface.

So current approach:
~~~~~~~~~~~~
on first call:
   1. create a single-row ResultSet object.
   2. call assignRowValues.
   3. process result (extract the tuple data from the ResultSet object).

on each subsequent call:
   1. call assignRowValues using the same single-row ResultSet object.
   2. process result (extract the tuple data from the ResultSet object).

Now, with your suggested approach you have two choices:
1. Build a SyntheticResultSet in memory and return it. A fair amount of 
code and the result might consume an unacceptable amount of memory.
2. Create your own implementation of ResultSet where you are the 
implementor of the next() method. This is a great deal of work. A lot 
more then just implementing the assignRowValues method.

New approach:
~~~~~~~~~~~~
on first call:
  1. call getResult()
  2. call next() on the obtained ResultSet
  3. process result (extract the tuple from the ResultSet object).

on each subsequent call:
  1. call next()
  2. process result (extract the tuple from the ResultSet object).

Same amount of work for both approaches. The first approach doesn't 
suffer from any of the disadvantages that the second approach has so 
there's a good motivation to keep it.


>That I think would be much easier for developers like myself to handle it - all I have to worry about then is to prepare the ResultSet in a way I want it without the need to get bogged down in implementing iteractions and mess about with row numbers and the like.
>  
>
Yes. The use case you have, when a function actually executes a query 
and want to return the result of that query is a good motivation to add 
the new approach.

>Better still, you can define and 'fire' different events through the entire process to give developers more control of what is being done. If adopted I have a suggestion of (at least) three such events (I suspect these methods will be in addition to the once controlling the pool behaviour, like 'make', 'activate', 'passivate' and 'destroy'):
>
>public void initialise(); // fired before the getResult() interface method is actually called to give the client class a chance to initialise itself
>public void lastRowProcessed(); // fired after the last row of the ResultSet has been processed;
>public void processException(Exception e); // when pljava/processing exception has occured
>  
>
I assume that 'activate' is the same as 'initialise' and 'passivate' is 
the same as 'lastRowProcessed'? If so, I must say I like the original 
names better. 'initialise' sounds like a constructor. 'activate' is a 
well known term for patterns that use pooling, and if we use 'activate', 
then 'passivate'  comes natural.

'destroy' is fine but I don't think we need 'make' since that's the same 
as the constructor and I'm opposed to a special processException method. 
Let the implementor decide how and when he want to deal with exceptions. 
It's easy enough to implement and several patterns are possible.

>Nah! When I have time (probably around Friday time by the looks of it) I'll post the code of our pool framework based on Jakarta's Generic Object Pool which has been used by us for the past 7 months - all that without a single glitch (I can hardly recall stopping the service or bringing down the servers more than 2-3 times since it was launched). 
>The framework is very robuslt and as I pointed out earlier has dealt with a lot of pounding in the past.
>  
>
Great. I look forward to reviewing it.

Regards,
Thomas Hallgren







^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* [Pljava-dev] advice needed
@ 2005-02-17 01:51  
  parent: 
  1 sibling, 0 replies; 12+ messages in thread

From:  @ 2005-02-17 01:51 UTC (permalink / raw)

George,
Lot of words. It all boils down to two things:
1. Creating a ResultSet in memory is often unacceptable.
2. Not all sets are the result of a query towards the database.

There's no point in discussing the likelyhood of this versus that. I'm 
trying to explain to you why the current design makes perfect sense but 
I don't seem to communicate that very well. I suggest you think of the 
following scenarios, perhaps that will make you realize what all of this 
is good for.

1. A big mainframe system has a Java API that allows you to query 
certain information. The system uses Java serialization to stream the 
result back to you through an ObjectInputStream. The objects that you 
read follow Java beans standard with getters for each column. Millions 
of rows might be returned as the result of a query. You want a function 
that returns a SETOF a type that corresponds to the Java beans. What do 
you do?

2. You have several very large XML documents on disk. The documents 
contain millions of elements of a certain type. You have a streaming SAX 
parser that can parse the documents, one element at a time. You want a 
function that returns elements from such a document as a SETOF a type 
that matches the type of the element. Reading all of the document into 
memory is not an option. How do you solve this?

3. You need a type where some columns match columns in an existing table 
but two or more columns are computed using some advanced algorithm that 
you've implemented in Java. The database table contains millions of 
records. You want a function that returns a SETOF this type. How would 
you create such a function?

Regards,
Thomas Hallgren






^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* [Pljava-dev] advice needed
@ 2005-02-17 07:50  
  parent: 
  1 sibling, 0 replies; 12+ messages in thread

From:  @ 2005-02-17 07:50 UTC (permalink / raw)

George,
As I wrote in my previous mail, I feel that I'm not communicating why 
the current solution makes good sense. One reason may be that we talk 
passed eachother. I read you mail again, looking for replies that might 
indicate that things need to be further clarified. I found a couple:

>If I have defined a type and then a SET OF return function I would like
>to return a homogenous data, otherwise I may as well define an
>ArrayList and stuff it with all sort of objects and use the iterator to
>iterate over (therefore no SET OF needed at all).
>  
>
When I talk about different sources, I don't mean that each row is of a 
different type. I mean that each row is of the same type and that each 
row can contain columns with values that originates from different 
sources (like a join, but not limited to database tables).

>I can call the standard rs.moveToInsertRow method as many times as I like until my
>ResultSet is ready to be returned.
>  
>
Two things:
1. Where did you get the empty ResultSet that you start with?
2. This means you build everythin in memory, no?

>Actually the receiver is just that - a result set! Being one row or
>multiple rows - it doesn't matter much since it *has* to be of the same
>type, so why bother building it step-by-step when it can be build in a
>single call?
>  
>
Here's some major misconception. You seem to think that a ResultSet is 
built up always. It isn't. One row at a time is streamed back to the 
PostgresSQL query evaluator. No set is ever built up. One way of 
accessing the set from a client is to use JDBC and there you would have 
a ResultSet. Using some other client, you'd have some other mechanism. 
Common for all of them is that no ResultSet containing all rows is ever 
instantiated. Data is always streamed.

>>You must look at this ResultSet object, not as a "set" per se, but as a 
>>single Tuple. There's no way to position within this set or add a row. 
>>    
>>
>
>Yes, there is - when you build a result set (that is ResultSet as
>specified by java.sql) you can always use rs.moveToInsertRow and
>rs.updateXXX as many times as you like.
>
This seem to be another major misconception. The ResultSet that you find 
in the java.sql package is an *interface*. The ResultSet that is passed 
to the assignRowValues is an implementation of that interface. You have 
to belive me that this implementation represents one single tuple and 
that there's no way you can do positioning or adding. I should know. I 
wrote it.

As I said, if java.sql had an interface that only contained all the 
getXXX and updateXXX mehods and nothing else, that interface would have 
been used instead. Unfortunately, no such interface exists.

>>So current approach:
>>~~~~~~~~~~~~
>>on first call:
>>  1. create a single-row ResultSet object.
>>  2. call assignRowValues.
>>  3. process result (extract the tuple data from the ResultSet object).
>>
>>on each subsequent call:
>>  1. call assignRowValues using the same single-row ResultSet object.
>>  2. process result (extract the tuple data from the ResultSet object).
>>
>>Now, with your suggested approach you have two choices:
>>1. Build a SyntheticResultSet in memory and return it.
>>    
>>
>
>Nope! Standard java.sql ResultSet will do fine thank you (which is
>*not* entirely in memory but value is retracted on call to next and
>getXXX - *big* plus).
>  
>
You contradict yourself. First you say that the way to do this is by 
doing lots of moveToInsertRow and add values, then you say it's not 
entirely in memory. Where do you think your data goes?

>>A fair amount of code and the result might consume an unacceptable amount of memory.
>>    
>>
>
>Not at all! 
>
>When you build the reult set all you have to do is st.executeQuery
>(which you will do anyway if you have to gather data from the database)
>  
>
As stated earlier. If all you want to do is return a ResultSEt that 
originates from one single executeQuery, then the idea of a getResult() 
API is excellent. The whole point whith this exercise was to show how a 
SETOF can be returned when you have a result that does *not* originate 
from a single database query.

>>2. Create your own implementation of ResultSet where you are the 
>>implementor of the next() method.  This is a great deal of work. 
>>    
>>
>
>Why would I want to do that
>
For one simple reason. You don't want to build a ResultSet completely in 
memory before you return it.

>within the C function you call the java class to return the ResultSet
>(i.e. getResult) and then if a single-step iteraction is required
>pljava (i.e. the C function or whatever the internal implementation is)
>iterates over the set - that is it.
>
No, that's not it. The C function is a streaming function and it is 
called by the PostgreSQL query evaluator. It doesn't require access to 
the entire set. It only wants one row. And that's the essense of 
everything. No huge set of data is built up in memory, ever!

Further more, as soon as a requested row satisfies a query, no further 
rows will be requested. Very significant if you for instance use your 
function as the source of an ANY or IN predicate in a SELECT.

> 1. assignRowValues method be scrapped completely;

That won't happen since it's the only way to stream data from a source 
to the caller of a function.

>2. replaced with a single interface method (I used getResult, but I
>think I should have called it getJDBCResultSet instead for clarity),
>which returns ResultSet (as in java.sql) and that result set is
>processed either as a whole or in a loop internally depending on
>whether PostgreSQL internals need it to be preprocessed step by step  -
>all that *without any further involvement of the client interface*. 
>  
>
As I said, this is a good idea in cases where you already have a 
ResultSet handy. But it does not give you the ability to stream data 
when the source is something other than a query.

>I fail to see where is the 'fair amount of code' or the 'large memory
>consumption' as you are suggesting?
>  
>
Perhaps you see it now?

>Which is what happens in 99% of all cases - you have to use java.sql
>ResultSet in every database itteration - there is no other choice -
>data retrieved is through this result set. 
>  
>
What if your function wants to filter some of the rows based on a rule 
that cannot be expressed in a where clause?

>The primary motive to put java code in a database is that it stays as
>close to the data as possible so that queries are much quicker to
>execute and so are the updates, otherwise why would you need to put
>your java code in the database and run classes to make socket
>connections to different places as you suggested - if that was the case
>then I may as well have a separate application server to reside the jar
>file on and not bother with the damn thing!
>  
>
Yeah right. And then join the result with something from the database 
and add a where clause to the expression... Read - there are other 
reasons to produce a SETOF that the database query evaluator can make 
use of.

>>I assume that 'activate' is the same as 'initialise' and 'passivate' is 
>>the same as 'lastRowProcessed'? 
>>    
>>
>
>Not quite! Activate occurs after initialise, make occurs before
>anything else (make instantiates it - i.e. creates new class, which is
>not necessarily the case with a pool since objects are reused).
>  
>
So make *is* the constructor. It's only called when the object is 
created, not when it is reused.

>lastRowProcessed occurs as soon as the last row has been processed, but
>before the object has been put back into the pool. One example of use
>of these two methods is: use lasRowProcessed to reinitialise text
>patterns and write transaction logs for successful operation, use
>passivate to validate the object before it is placed back in the pool
>(this has a special meaning for the pool factory). Just to clarify the
>events as they occur:
>
>new Class();
>fire make // after that object is placed in the pool
>// when needed object is taken from the pool
>fire initialise // validation, re-initialisation of internal variables
>and states may occur here
>fire activate
>call getJDBCResultSet
>// process the results
>fire lastRowProcessed when the last row of the result set has been
>processed
>fire passivate
>// when object needs to be destroyed
>fire destroy
>class = null // to be processed by the gc later
>  
>
Right. Nothing happens between initialise and activate and nothing 
happens between lastRowProcessed and passivate. So why have four calls 
instead of two? If you want to call a lastRowProcessed first thing from 
within your passivate, then do so. If not, you've just saved a call. Why 
should pljava always do this call? Seems to me you are forced to 
implement two methods that you are unlikely to need and pljava is forced 
to make four calls instead of two.

- thomas







^ permalink  raw  reply  [nested|flat] 12+ messages in thread

end of thread, other threads:[~2005-02-17 07:50 UTC | newest]

Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-16 01:01 [Pljava-dev] advice needed 
2005-02-16 07:41 ` 
2005-02-16 11:34 [Pljava-dev] advice needed 
2005-02-16 11:35 [Pljava-dev] advice needed 
2005-02-16 12:31 ` 
2005-02-16 15:43 [Pljava-dev] advice needed 
1970-01-01 00:00 ` 
1970-01-01 00:00   ` 
2005-02-17 01:51     ` 
2005-02-17 07:50     ` 
2005-02-16 22:29   ` 
2005-02-16 16:53 ` 

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox