Skip site navigation (1) Skip section navigation (2)

Re: [GENERAL] java stored procedures

From: Barry Lind <barry(at)xythos(dot)com>
To: hornyakl(at)users(dot)sourceforge(dot)net
Cc: pgsql-hackers(at)postgresql(dot)org, pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [GENERAL] java stored procedures
Date: 2001-12-05 17:32:19
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-generalpgsql-hackerspgsql-jdbc

I have cc'ed the hackers mail list since that group of developers is 
probably better able than I to make suggestions on the best interprocess 
communication mechanism to use for this.  See 
for background on this thread.

I also stopped cc'ing the general list, since this is getting too 
detailed for most of the members on that list.

Now to your mail:

Laszlo Hornyak wrote:

> Hi!
> Barry Lind wrote:
>> Does the mechanism you are planning support running any JVM?  In my 
>> opionion Kaffe isn't good enough to be widely useful.  I think you 
>> should be able to plugin whatever jvm is best on your platform, which 
>> will likely be either the Sun or IBM JVMs.
> Ok, I also had problems with caffe, but it may work. I like it becouse 
> it is small (the source is about 6M). As much as I know Java VM`s has a 
> somewhat standard native interface called JNI. I use this to start the 
> VM, and communicate with it. If you think I should change I will do it, 
> but it may take a long time to get the new VM. For then I have to run 
> kaffe.

This seems like a reasonable approach and should work across different 
JVMs.  It would probably be a good experiment to try this with the Sun 
or IBM jvm at some point to verify.  What I was afraid of was that you 
were hacking the Kaffe code to perform the integration which would limit 
this solution to only using Kaffe.

>> Also, can you explain this a little bit more.  How does the jvm 
>> process get started? (I would hope that the postgresql server 
>> processes would start it when needed, as opposed to requiring that it 
>> be started separately.)  How does the jvm access these shared memory 
>> structures? Since there aren't any methods in the java API to do such 
>> things that I am aware of.
> JVM does not. 'the java process' does with simple posix calls. I use 
> debian potatoe, on any other posix system it should work, on any other 
> somewhat posix compatible system it may work, I am not sure...
>> I don't understand how you do this in java?  I must not be 
>> understanding  something correctly here.
> My failure.
> The 'java request_handler' is not a java function, it is the C 
> call_handler in the Postgres side, that is started when a function of 
> language 'pljava' is called.
> I made some failure in my previous mail. At home I named the pl/java 
> language pl/pizza (something that is not caffe, but well known enough 
> :). The application has two running binaries:
> -pizza (which was called 'java process' last time) This is a small C 
> program that uses JNI to start VM and call java methods.
> the shared object that contains the call_handler function.

Just a suggestion:  PL/J might be a good name, since as you probably 
know it can't be called pl/java because of the trademark restrictions on 
the word 'java'.

I am a little concerned about the stability and complexity of having 
this '-pizza' program be responsible for handling the calls on the java 
side.  My concern is that this will need to be a multithreaded program 
since multiple backends will concurrently be needing to interact with 
multiple java threads through this one program.  It might be simpler if 
each postgres process directly communicated to a java thread via a tcpip 
socket.  Then the "-pizza" program would only need to be responsible for 
starting up the jvm and creating java threads and sockets for a postgres 
process (it would perform a similar role to postmaster for postgres 
client connections).

>>> -when java thread receives the signal, it reads the message(s) from 
>>> the queue, and starts some actions. When done it tells postgres with 
>>> a signal that it is ready, and it can come for its results. This will 
>>> be rewritten see below problems.
>> Are signals the best way to accomplish this? 
> I don`t know if it is the best, it is the only way I know :)
> Do you know any other ways?

I don't know, but hopefully someone on the hackers list will chip in 
here with a comment.

>>> Threading on the java process side is not done yet, ok, it is not 
>>> that hard, I will write it, if it will be realy neccessary.
>> Agreed, this is important.
>> Shouldn't this code use all or most of the logic found in the FE/BE 
>> protocol?  Why invent and code another mechanism to transfer data when 
>> one already exists.  (I will admit that the current FE/BE mechanism 
>> isn't the ideal choice, but it seems easier to reuse what exists for 
>> now and improve on it later). 
> Well, I am relatively new to Postgres, and I don`t know these protocols. 
> In the weekend I will start to learn it, and in Sunday or Monday I maybe 
> I will understand it, if not, next weekend..
>> You didn't mention how you plan to deal with the transaction 
>> symantics.  So what happens when the pl/java function calls through 
>> jdbc back to the server to insert some data?  That should happen in 
>> the same transaction as the caller correct? 
> I don`t think this will be a problem, I have ideas for this. Idea mean: 
> I know how I will start it, it may be good, or it may be fataly stupid 
> idea, it will turn out when I tried it. Simply: The same way plpizza 
> tells pizza the request, pizza can talk back to plpizza. This is planed 
> to work with similar mechanism I described last time (shm+signals).

OK, so the same backend process that called the function gets messaged 
to process the sql.  This should work.  However it means you will need a 
special version of the jdbc driver that uses this shm+signals 
communication mechanism instead of what the current jdbc driver does. 
This is something I would be happy to help you with.

In response to


pgsql-hackers by date

Next:From: Manuel SugawaraDate: 2001-12-05 17:33:26
Subject: Re: date formatting and tab-complete patch
Previous:From: D'Arcy J.M. CainDate: 2001-12-05 16:52:15
Subject: database system was interrupted at...

pgsql-jdbc by date

Next:From: Dave CramerDate: 2001-12-05 18:26:58
Subject: Problems with numeric (10,2) and big Decimal
Previous:From: Laszlo HornyakDate: 2001-12-05 09:06:10
Subject: Re: java stored procedures

pgsql-general by date

Next:From: Patrick HenryDate: 2001-12-05 17:41:13
Subject: PostgreSQL Callback Mechanism
Previous:From: Peter DarleyDate: 2001-12-05 17:31:28
Subject: Re: Installing DBD::Pg module without Pg Database server

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group