Re: [JDBC] PL/Java issues

I think much of the issues should be resolved using JDBC and
java.sql.ResultSet and a couple of interfaces that can be used when mapping
specific types to specific Java objects (SQLData, SQLReader/SQLWriter).

A PL/Java needs a "hook" where a connection is initialized for JDBC access
in the backend. Here, the programmer can register the mappings used for
parameters and return values during the lifetime of the connection the same
way this is done using JDBC in the client.

The SQL standard  (for backend Java mapping) now goes further and suggest
that Java types can be defined "on the fly" in the Database just by
declaring a Type as such with "language Java". Really neat although I think
lots of thought needs to go in to how inheritance and overloading will be
handled for those types. If indeed that should be an SQL concern at all.

A question regarding the "ON COMMIT/ON ROLLBACK". Is there any way to write
backend code that will get called when this happens? Does the SPI include
some XA support or something similar where you can register a listener to
the current transaction? For a PL/Java port that would be really interesting
and the javax.transaction interfaces could be used to map to native Postgres
functionality in a standardized way.

Another question regarding connection = thread in JVM. A Postgres connection
is currently running in its own process. Having each such process
communicate with another process for each call that is made will be fairly
expensive. Consider a "SELECT foo(x) FROM y" where foo() is a Java method
and y contains several thousands of rows. Using a thread in an external JVM,
each foo() call will cause IPC call. IPC calls are expensive (that's partly
why we want to move code to the backend in the first place). Furhter more,
if each connection is in its own thread, how do you maintain connection
isolation? Suddenly connections share volatile and dirty data! Ok, you can
avoid this by having a completely separate ClassLoader chains in each
thread, but that's almost as expensive as having separate JVM's.

Now add that most Java projects uses a J2EE architecture that has a
connection pool that ensures that the number of times a new connection is
established is fairly low and connection reuse is maximized. One might
consider a solution where we let each connection spawn its own internal JVM
(on demand of course). What seems to be costly at first might prove
extremely efficient for the majority of users.

- thomas

"Jan Wieck" <JanWieck@Yahoo.com> wrote in message
news:3FF5E941.60502@Yahoo.com...
> Dave Cramer wrote:
>
> > Barry,
> >
> > Ok, so if we drop this limitation then we leave it up to the architect
> > to manage the caching problem themselves.
>
> Maybe I don't understand enough about Java, but isn't this limitation
> (only static methods callable) exactly what avoids having to deal with
> the call->instance association in the PL framework?
>
> I think we still want to go a little further in the framework and
> provide at least the object caches. Since we don't have any ON COMMIT or
> ON ROLLBACK triggers, it'd be hard for the PL programmer to deal with
> cleanup and object dereferencing, especially in the case of transaction
> abort.
>
> >
> > The class loader issue is interesting, this would mean that each object
> > static or otherwise would not be able to overwrite others data.
>
> I think if we would create one thread per backend (on the first call of
> any PL/Java proc of course), and also have one class loader per thread,
> that'd be sufficient at least from a security point of view.
>
>
> Jan
>
> >
> > --dc--
> > On Wed, 2003-12-31 at 19:34, Barry Lind wrote:
> >> Jan,
> >>
> >> In Oracle a call from sql into java (be it trigger, stored procedure or
> >> function), is required to be a call to a static method.  Thus in Oracle
> >> all the work is left for the programmer to manage object instances and
> >> operate on the correct ones.  While I don't like this limitation in
> >> Oracle, I can't see a better way of implementing things.
> >>
> >> Therefore if you want to operate on object instances you (in Oracle)
> >> need to code up object caches that can hold the instances across
> >> function calls so that two or more functions can operate on the same
> >> instance as necessary.  What this implies to the implementation is that
> >> in order to be possible the multiple function calls need to run inside
> >> the same jvm (so you can access the static caches across the different
> >> calls).  If every call created a new jvm instance in Oracle you
couldn't
> >> do very much.  The Oracle jvm essentially gives you one jvm per
> >> connection (although technically it is somewhere between one jvm for
the
> >> whole server and one per connection - i.e. it has the memory and
process
> >> footprint of a single jvm for the entire server, but appears to the
user
> >> as a jvm per connection).  Having one jvm per connection is important
to
> >> limit multiple connections ability to stomp on each others data.
> >> Something similar could probably a done for postgres by having one jvm
> >> running, by having each postgres connection having a unique thread in
> >> that jvm and having each connection thread run with its own class
loader
> >> instance so that separate classes (and thus static members) are loaded
> >> for each connection.
> >>
> >> thanks,
> >> --Barry
> >>
> >>
> >> Jan Wieck wrote:
> >> > I have included the JDBC mailing list since I guess most Java
developers
> >> > are around here, but not necessarily on Hackers.
> >> >
> >> > Dave Cramer and I where discussing a few issues about the PL/Java
> >> > implementation last night and would like to get more input and
> >> > suggestions on the matter.
> >> >
> >> > The basic question is the definition of the lifetime of an object and
> >> > it's identificaition when doing nested calls in this context. In the
OO
> >> > world, ideally a real world object is translated into one instance of
a
> >> > class. And complex structures are trees of instances, possibly of
> >> > different classes. As an example, a sales order consists of the order
> >> > header and a variable number of order lines. Therefore, per order we
> >> > have one OH instance and several OL's. So far so good. Naturally, one
> >> > Java object instance would correspond to one row in a database.
> >> >
> >> > If we now implement a stored procedure in PL/Java, that means that a
> >> > pg_proc entry corresponds to a specific method of a specific class
(its
> >> > signature). But there is no obvious relationship between functions
and
> >> > tables or other objects. Because of that it is not implicitly clear
if
> >> > an incoming call to a method is meant for an existing instance or if
a
> >> > new one should be created.
> >> >
> >> > As an example, if a PL/Java trigger on the order header executes an
SPI
> >> > query on the order lines, a trigger on the order line (also in
PL/Java)
> >> > might now want to call a method on it's parent object (the order
header
> >> > that is waiting for the SPI result set). This should NOT result in
> >> > another OH instance being created for the same logical OH.
> >> >
> >> > Probably it is not possible to map these things automatically while
> >> > keeping the system flexible enough to be usefull. But is it feasable
to
> >> > require the programmer to provide glue code for every procedure that
> >> > does all these things? How does Oracle attack this problem?
> >> >
> >> >
> >> > Jan
> >> >
> >>
> >>
> >>
> >> ---------------------------(end of
broadcast)---------------------------
> >> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org
> >>
> >>
>
>
> -- 
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me.                                  #
> #================================================== JanWieck@Yahoo.com #
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>