Re: [GENERAL] java stored procedures - Mailing list pgsql-jdbc

Hi!

Barry Lind wrote:

> Laszlo,
>
> I think it would help a lot if you could take a little time to write
> down what your planned architecture for a pljava would be.  It then
> becomes much easier for myself and probably others reading these lists
> to make suggestions on ways to improve what you are planning (or
> possible problems with your strategy).  Without knowing what exactly
> you are thinking of doing it is difficult to comment.

>
>
> But let me try throwing out a few thoughts about how I think this
> should be done.
>
> First question is how will the jvm be run?  Since postgres is a
> multiprocess implementation (i.e. each connection has a separate
> process on the server) and since java is a multithreaded
> implementation (i.e. one process supporting multiple threads), what
> should the pljava implementation look like?  I think there should be a
> single jvm process for the entire db server that each postgresql
> process connects to through sockets/rmi.  It will be too expensive to
> create a new jvm process for each postgresql connection (expensive in
> both terms of memory and cpu, since the startup time for the jvm is
> significant and it requires a lot of memory).

I absolutely agree. OK, it`s done.

So, a late-night-brainstorming here:
What I would like to see in PL/JAVA is the object oriented features,
that makes postgresql nice. Creating a new table creates a new class in
the java side too. Instantiating an object of the newly created class
inserts a row into the table. In postgresql tables can be inherited, and
this could be easyly done by pl/java too. I think this would look nice.
But this is not the main feature. Why I would like to see a nice java
procedural language inside postgres is java`s advanced communication
features (I mean CORBA, jdbc, other protocols). This is the sugar in the
caffe.

I am very far from features like this.
PL/JAVA now:
-there is a separate process running java (kaffe). this process creates
a sys v message queue, that holds requests. almost forgot, a shared
memory segment too. I didn`t find better way to tell postgres the
informations about the java process.
-the java request_handler function on the server side attaches to the
shared memory, reads the key of the message queue., attaches to it,
sends the data of the function, and a signal for the pl/java. after, it
is waiting for a signal from the java thread.
-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.
-And postgres is runing, while java is waiting for postgres to say
something.

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.

The problems, for now:
I had a very simple system, that passed a very limited scale of argument
types, with a very limited quantity of parameters (int, varchar, bool).
Postgres has limits for the argument count too, but not for types. It
had too much limits, so I am working (or to tell the truth now only
thinking) on a new type handling that fits the felxibility of
Postgresql`s type flexibility. For this I will have to learn a lot about
Postgres`s type system. This will be my program this weekend. :)

thanks,
Laszlo Hornyak