Hi All,

I was just mulling over how hard it would to implement Java stored
procedures (mulling being the operative word) and was thinking how to
best implement postgresql <-> java communications.  (ie shared memory
via JNI?)  I have read the past posts regarding possible PL/Java
implementations and it's basically stopped at "How to implement
Postrgesql <-> Java data passing?".

I'm not sure if I'd have time to (nor the skill to) actually implement
anything, but ...

Basically, I thought that pljava_init_all could fork and start the JVM
if it hasn't already been started.  Then using the single instance of
JVM, run a base class (PLJavaLoader) which (loops and) checks postgresql
(*) to see if there is a waiting procedure call.  If there is, then it
would dynamic load the procedure's class (which is of base class
PLJavaProcedure) and run it in a Thread.  Each class intance would run
in its own thread, so there would be only one JVM for all postgresql
processes, but one thread for each called pljava procedure.  For
functions, the thread would then load the data for the functions
arguments(*).  The java procedure / function would then do its work and
then return any data (*).

Instead of using SPI to execute sql, the class would use jdbc to connect
to the server.  The PLJavaLoader class could cache JDBC connections to
reduce the connection overhead (?).

Places marked (*) is where java and postgresql would need to
communicate.  It may be possible to use a tempory table to pass stuff
back and forth, but this may be problematic and slow (?).  I was
thinking JNI + shared memory or named pipes, but I could be way off
base.  Portability would problably be an issue here.

Another thought was to use a  SOAP interface to pass data between
postgresql and the JVM.  I think the overhead of SOAP using HTTP and
also the java class having a connection to postgresql may be too much (?).

I understand that this is probably way too simplistic, but looking at
pltcl.c, most of it (looks as though it) could be done farily simply.

Loading pljava classes could be done like C functions: ie

CREATE FUNCTION overpaid(int4, int4)
RETURNS bool
AS 'PGROOT/java/classes/funcs.class'
LANGUAGE 'java'
NAME 'MyClasses.CalcOverpaid';

which would have to be javac'ed before hand.  The NAME bit points to the
actual Java class that is run for the function.

package MyClasses;
public class CalcOverpaid extends PLJavaProcedure
{
     public CalcOverpaid()
     {
          super(); //this calls getDataFromPgsql (JNI function)
          //initialize class
     }
     public void run()
     {
         // do work here
     }
     //PLJavaProcedure calls sendDataToPgsql (somehow)
}

The actual Java structure would of course have to be worked out.  It
might be better if the class to run wasn't itself a Thread'ed class
(from PLJavaProcedure), but was run _in_ a thread from PLJavaLoader.
(then the programmer wouldn't necessary have to follow any real
convention, the Loader sets everything up, gets the necesarry data from
postgresql (argument data etc) and then loads and runs the class, which
then returns, and the Loader then returns the data to postgresql.

I'm sure that using this sort of single instance of the JVM would be
more desirable than starting the JVM each and everytime a java procedure
was run.

I appologise if I have this is all wrong.  I'm not a master coder, and
definiately don't know the internals of postgresql well, but I thought I
may as well put this out there for the hell of it.

Ashley Cambrell


Mon, 29 Oct 2001 17:35:35 +1100
Ashley Cambrell <ash@freaky-namuh.com>
Best way for Postrgesql to pass info to java and back again?
 (PL/Java)
----- Original Message ----- 
From: Ashley Cambrell <ash@freaky-namuh.com>
Sent: Monday, October 29, 2001 1:35 AM

> I was just mulling over how hard it would to implement Java stored
> procedures ...

This is an interesting proposal. I was thiniking of it also
some time ago when someone inquired whether PG has PL/Java or not...

> (mulling being the operative word) and was thinking how to
> best implement postgresql <-> java communications.  (ie shared memory
> via JNI?)  I have read the past posts regarding possible PL/Java
> implementations and it's basically stopped at "How to implement
> Postrgesql <-> Java data passing?".

Maybe the same or a similar way as JDBC folks address it?

> I'm not sure if I'd have time to (nor the skill to) actually implement
> anything, but ...

Same here, but besides having skilled people working on it one has
to initiate the idea first, that's what you did :) and it might
turn out to something more tangeable after fair amount of discussion.

> Instead of using SPI to execute sql, the class would use jdbc to connect
> to the server.  The PLJavaLoader class could cache JDBC connections to
> reduce the connection overhead (?).

I'm thinking not to use the JDBC directly, not only to reduce the
connection overhead but also calls to JDBC layer itself. Instead,
one can possibly reuse some of the JDBC code, me thinks.

Just a couple of quick thoughts...

-s


Mon, 29 Oct 2001 15:06:13 -0500
"Serguei Mokhov" <sa_mokho@alcor.concordia.ca>
Re: Best way for Postrgesql to pass info to java and back again?
	(PL/Java)
Hi Serguei,

Serguei Mokhov wrote:

>----- Original Message ----- 
>From: Ashley Cambrell <ash@freaky-namuh.com>
>Sent: Monday, October 29, 2001 1:35 AM
>
>>I was just mulling over how hard it would to implement Java stored
>>procedures ...
>>
>
>This is an interesting proposal. I was thiniking of it also
>some time ago when someone inquired whether PG has PL/Java or not...
>
>>(mulling being the operative word) and was thinking how to
>>best implement postgresql <-> java communications.  (ie shared memory
>>via JNI?)  I have read the past posts regarding possible PL/Java
>>implementations and it's basically stopped at "How to implement
>>Postrgesql <-> Java data passing?".
>>
>
>Maybe the same or a similar way as JDBC folks address it?
>
I have looked at the JDBC drivers, and it seems to be all native Java 
code. They actually use socket and standard buffered i/o readers to to 
the protcol transport.  (no JNI anywhere [I think])

The main question is still how to communicate low level (pre jdbc 
connection) to postgresql.  How could the waiting JVM (and procedure 
runner) get notified that a waiting procedure request was there.  Maybe 
 a very small network layer instead of shared memory.  I single shared 
"notifiy" socket on postgresql's end that the JVM that a request is 
there [this request contains enough info to dynamically load the base 
procedure class and start a thread], the JVM then opens another socket 
for 2 way comms (so as not to block the notify socket), it then gets the 
args for the procedure, runs the procedure and then sends the results 
back done that comms socket.

??

>>I'm not sure if I'd have time to (nor the skill to) actually implement
>>anything, but ...
>>
>
>Same here, but besides having skilled people working on it one has
>to initiate the idea first, that's what you did :) and it might
>turn out to something more tangeable after fair amount of discussion.
>
My thoughts exactly :-)

>>Instead of using SPI to execute sql, the class would use jdbc to connect
>>to the server.  The PLJavaLoader class could cache JDBC connections to
>>reduce the connection overhead (?).
>>
>
>I'm thinking not to use the JDBC directly, not only to reduce the
>connection overhead but also calls to JDBC layer itself. Instead,
>one can possibly reuse some of the JDBC code, me thinks.
>
The jdbc code is a very thin wrapper to a java implemented fe (?) 
protocol.  Stuff like:

/**
   * Sends an integer to the back end
   *
   * @param val the integer to be sent
   * @param siz the length of the integer in bytes (size of structure)
   * @exception IOException if an I/O error occurs
   */
  public void SendInteger(int val, int siz) throws IOException
  {
    byte[] buf = bytePoolDim1.allocByte(siz);

    while (siz-- > 0)
      {
    buf[siz] = (byte)(val & 0xff);
    val >>= 8;
      }
    Send(buf);
  }


There doesn't seem to be any advantage to using anything under the jdbc 
protocol.  It would only make it more complex.

Unless there was a reason to use SPI that I don't know about?

>
>Just a couple of quick thoughts...
>
Thanks Serguei

>
>-s
>
>
Ashley Cambrell







Hi Serguei,

Serguei Mokhov wrote:

----- Original Message ----- From: Ashley Cambrell <ash@freaky-namuh.com>Sent: Monday, October 29, 2001 1:35 AM

I was just mulling over how hard it would to implement Java storedprocedures ...

This is an interesting proposal. I was thiniking of it alsosome time ago when someone inquired whether PG has PL/Java or not...

(mulling being the operative word) and was thinking how tobest implement postgresql <-> java communications.  (ie shared memoryvia JNI?)  I have read the past posts regarding possible PL/Javaimplementations and it's basically stopped at "How to implementPostrgesql <-> Java data passing?".

Maybe the same or a similar way as JDBC folks address it?

I have looked at the JDBC drivers, and it seems to be all native Java code.
They actually use socket and standard buffered i/o readers to to the protcol
transport.  (no JNI anywhere [I think])

The main question is still how to communicate low level (pre jdbc connection)
to postgresql.  How could the waiting JVM (and procedure runner) get notified
that a waiting procedure request was there.  Maybe  a very small network
layer instead of shared memory.  I single shared "notifiy" socket on postgresql's
end that the JVM that a request is there [this request contains enough info
to dynamically load the base procedure class and start a thread], the JVM
then opens another socket for 2 way comms (so as not to block the notify
socket), it then gets the args for the procedure, runs the procedure and
then sends the results back done that comms socket.

??


I'm not sure if I'd have time to (nor the skill to) actually implementanything, but ...

Same here, but besides having skilled people working on it one hasto initiate the idea first, that's what you did :) and it mightturn out to something more tangeable after fair amount of discussion.

My thoughts exactly :-)


Instead of using SPI to execute sql, the class would use jdbc to connectto the server.  The PLJavaLoader class could cache JDBC connections toreduce the connection overhead (?).

I'm thinking not to use the JDBC directly, not only to reduce theconnection overhead but also calls to JDBC layer itself. Instead,one can possibly reuse some of the JDBC code, me thinks.

The jdbc code is a very thin wrapper to a java implemented fe (?) protocol.
 Stuff like:

/**
   * Sends an integer to the back end
   *
   * @param val the integer to be sent
   * @param siz the length of the integer in bytes (size of structure)
   * @exception IOException if an I/O error occurs
   */
  public void SendInteger(int val, int siz) throws IOException
  {
    byte[] buf = bytePoolDim1.allocByte(siz);

    while (siz-- > 0)
      {
    buf[siz] = (byte)(val & 0xff);
    val >>= 8;
      }
    Send(buf);
  }


There doesn't seem to be any advantage to using anything under the jdbc protocol.
 It would only make it more complex.

Unless there was a reason to use SPI that I don't know about?

Just a couple of quick thoughts...

Thanks Serguei

-s

Ashley Cambrell
Tue, 30 Oct 2001 08:56:32 +1100
Ashley Cambrell <ash@freaky-namuh.com>
Re: Best way for Postrgesql to pass info to java and back again?
	(PL/Java)