Re: java stored procedures

Barry Lind <barry(at)xythos(dot)com> writes:

> Having one jvm that all the postgres backend processes communicate with makes
> the whole feature much more complicated, but is necessary in my opinion.

Agreed. Also, the JVM is a multithreaded app, and running it inside a
non-threaded program (the backend) might cause problems.

> Then the question becomes how does the jvm process interact with the database
> since they are two different processes. You will need some sort of
> interprocess communication between the two to execute sql statements. This
> could be accomplished by using the existing jdbc driver. But the bigest
> problem here is getting the transaction semantics right. How does a sql
> statement being run by a java stored procedure get access to the same
> connection/transaction as the original client? What you don't want happening
> is that sql issued in a stored java procedure executes in a different
> transaction as the caller, what would rollback of the stored function call
> mean in that case?

I think you would have to to expose the SPI layer to Java running in a
separate process, either using an RMI server written in C or a custom
protocol over a TCP socket (Java of course can't do Unix sockets).
This raises some thorny issues of authentication and security but I
don't think they're insurmountable. You could, for example, create a
cryptographically strong "cookie" in the backend when a Java function
is called. The cookie would be passed to the Java function when it
gets invoked, and then must be passed back to the SPI layer in order
for the latter to accept the call. A bit clunky but should be safe as
far as I can see.

The cookie would be needed anyhow, I think, in order for the SPI layer
to be able to find the transaction that the Java function was
originally invoked in.

You could make the SPI layer stuff look like a normal JDBC driver to
user code--PL/Perl does this kind of thing with the Perl DBI
interface.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863