Thread: PL/Java issues

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

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Jan Wieck wrote:
> 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?

How do our other procedural languages handle this problem?  Are none of
the OO or interface via OO?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

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
>

Barry,

Ok, so if we drop this limitation then we leave it up to the architect
to manage the caching problem themselves.

The class loader issue is interesting, this would mean that each object
static or otherwise would not be able to overwrite others data.

--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
>
>
--
Dave Cramer
519 939 0336
ICQ # 1467551

Barry,

This appears in principal to be very close to a servlet container. I'm
wondering if we could just use something like jetty as the basis for the
work?

It has already defined class loading per the servlet spec. It is already
setup to handle multiple requests, and load balancing, etc.

Dave
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 4: Don't 'kill -9' the postmaster
>
--
Dave Cramer
519 939 0336
ICQ # 1467551

Jan Wieck wrote:

> 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.


It's not clear to me that this object <--> row mapping is workable. It
looks like Oracle, by allowing only static methods, has basically
abandoned any possibility of it.

ISTM that if you want to live in the object world, you have to take care
of marshalling and unmarshalling the data yourself - either by manual
methods or using some of the increasingly sophisticated automated tools
that are available. OTOH, if you want to live in the table world, you
have to live without the hard ties between data in different tables that
the object world wants. PL/Java must surely live in the table world.

IOW, the Java interface would probably need to function in a fairly
similar way to the way the current C interface does.

Or have I missed something?

Also, what does the Standard say about all this? Has anyone actually
seen it?

cheers

andrew

Where is the standard, I for one would be interested in seeing it?

Dave
On Fri, 2004-01-02 at 01:09, Andrew Dunstan wrote:
> Jan Wieck wrote:
>
> > 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.
>
>
> It's not clear to me that this object <--> row mapping is workable. It
> looks like Oracle, by allowing only static methods, has basically
> abandoned any possibility of it.
>
> ISTM that if you want to live in the object world, you have to take care
> of marshalling and unmarshalling the data yourself - either by manual
> methods or using some of the increasingly sophisticated automated tools
> that are available. OTOH, if you want to live in the table world, you
> have to live without the hard ties between data in different tables that
> the object world wants. PL/Java must surely live in the table world.
>
> IOW, the Java interface would probably need to function in a fairly
> similar way to the way the current C interface does.
>
> Or have I missed something?
>
> Also, what does the Standard say about all this? Has anyone actually
> seen it?
>
> cheers
>
> andrew
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
--
Dave Cramer
519 939 0336
ICQ # 1467551

Dave Cramer said:
> Where is the standard, I for one would be interested in seeing it?
>

AFAIK it is not available except for $$$. It looks like the relevant
standards are parts 1 and 2 of the SQLJ standard (Part 0 covers embedded
SQL).

cheers

andrew

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 #

Will we need to address this TODO item:

  . Add capability to create and call PROCEDURES

before proceding to do PL/Java? It would add to the usefulness greatly,
I should think. I'm not sure how hard it would be.

cheers

andrew

Andrew Dunstan wrote:
> AFAIK it is not available except for $$$. It looks like the relevant
> standards are parts 1 and 2 of the SQLJ standard (Part 0 covers embedded
> SQL).
>

For working drafts try:

http://www.wiscorp.com/sql/sql_2003_standard.zip
(5WD-13-JRT-2003-09.pdf)

http://www.wiscorp.com/sql/sqljrout.zip
(SQLJ Part 1--SQL Routines)

http://www.wiscorp.com/sql/sqljtype.zip
(SQLJ Part 2--SQL Types)

Joe

Can you explain what you mean by this?

Dave
On Fri, 2004-01-02 at 20:21, Andrew Dunstan wrote:
> Will we need to address this TODO item:
>
>   . Add capability to create and call PROCEDURES
>
> before proceding to do PL/Java? It would add to the usefulness greatly,
> I should think. I'm not sure how hard it would be.
>
> cheers
>
> andrew
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>
--
Dave Cramer
519 939 0336
ICQ # 1467551

Barry,

Ok, so if we drop this limitation then we leave it up to the architect
to manage the caching problem themselves.

The class loader issue is interesting, this would mean that each object
static or otherwise would not be able to overwrite others data.

--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
>
>

Dave Cramer said:
> Can you explain what you mean by this?
>
> On Fri, 2004-01-02 at 20:21, Andrew Dunstan wrote:
>> Will we need to address this TODO item:
>>
>>   . Add capability to create and call PROCEDURES
>>
>> before proceding to do PL/Java? It would add to the usefulness
>> greatly,  I should think. I'm not sure how hard it would be.
>>

Well, it is syntactically unclean IMNSHO to have to define a return type
on an SP when you don't really need one, and to have to call it by
saying "select foo(bar)" rather than "call foo(bar)". Also, IIRC PL/SQL
lets you bind host language variables to OUT parameters of such SPs, which
can be quite useful. (It's a year or three since I touched Oracle in
anger.)

I would be mildly surprised if the SQL/JRT standard didn't expect to be
able to bind to static methods of type void.

(The item I quoted is on the TODO list - I didn't invent it ;-)

cheers

andrew

Joe Conway said:
> Andrew Dunstan wrote:
>> AFAIK it is not available except for $$$. It looks like the relevant
>> standards are parts 1 and 2 of the SQLJ standard (Part 0 covers
>> embedded SQL).
>>
>
> For working drafts try:
>
> http://www.wiscorp.com/sql/sql_2003_standard.zip
> (5WD-13-JRT-2003-09.pdf)
>
[snip]

Cool. Specifically, try this one and have a look at Appendices E and F.

cheers

andrew

I'm working on a pl/java project and have come quite far with it. Triggers
and Functions are both callable, there's support for complex types etc. I
have a project on GBorg (pljava) where I'll post all source in a CVS
repository in a matter of days (the code is ready but my ISP have a router
problem preventing me from anything but slow modem contact right now).

I've glanced at the SQL standard too of course. From what I could find, it
says surprisingly little about triggers. Triggers are, IMO one place where
you very often have a natural row association (ON EACH ROW). Such triggers
would be ideal as instance methods on a type. AFAICS, there no mentioning of
that in the standard. Nor does the standard mention how the "new" and "old"
tuples can be manipulated.

At present, I map complex types to Tuple instances (a Tuple is a Java object
that has a mapping to it's native C structure correspondance, the HeapTuple
and it's associated TupleDesc) both in functions and triggers. I have a
TriggerData java class also from which you can obtain the "trigger tuple"
and the "new tuple" and I follow the postgres model of returning a modified
(or not) tuple from triggers. As this is very Postgres specific, I plan to
add a ResultSet mapping on top later on.

Another thing that surprised me with the SQL standard was that although you
can override default mapping of types on parameters in order to pass NULL
values (a primitive int can be mapped to java.lang.Integer in the parameter
list by explicitly declaring a java parameter list), there's no way to
specify the same explicit mapping of return types.

One place where the SQL standard is talking about instance mapping is when
you map specific types to Java objects. A row that in fact reflects such a
type should of course be mapped to it's respective Java object and not to an
anonymous Tuple.

Stay tuned to GBorg and pljava. I hope my ISP will fix their router problem
real soon.

- thomas

""Andrew Dunstan"" <andrew@dunslane.net> wrote in message
news:4366.24.211.141.25.1073178456.squirrel@www.dunslane.net...
> Dave Cramer said:
> > Can you explain what you mean by this?
> >
> > On Fri, 2004-01-02 at 20:21, Andrew Dunstan wrote:
> >> Will we need to address this TODO item:
> >>
> >>   . Add capability to create and call PROCEDURES
> >>
> >> before proceding to do PL/Java? It would add to the usefulness
> >> greatly,  I should think. I'm not sure how hard it would be.
> >>
>
> Well, it is syntactically unclean IMNSHO to have to define a return type
> on an SP when you don't really need one, and to have to call it by
> saying "select foo(bar)" rather than "call foo(bar)". Also, IIRC PL/SQL
> lets you bind host language variables to OUT parameters of such SPs, which
> can be quite useful. (It's a year or three since I touched Oracle in
> anger.)
>
> I would be mildly surprised if the SQL/JRT standard didn't expect to be
> able to bind to static methods of type void.
>
> (The item I quoted is on the TODO list - I didn't invent it ;-)
>
> cheers
>
> andrew
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

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
>

Andrew Dunstan wrote:
> Will we need to address this TODO item:
>
>   . Add capability to create and call PROCEDURES
>
> before proceding to do PL/Java? It would add to the usefulness
> greatly, I should think. I'm not sure how hard it would be.

This should be relatively easy if you omit OUT parameters.  Just treat
CALL foo(...) as SELECT foo(...) and throw away the result (or avoid
generating one).

For OUT parameters, we'd probably have to learn their full semantics
first.

Peter Eisentraut wrote:

>Andrew Dunstan wrote:
>  
>
>>Will we need to address this TODO item:
>>
>>  . Add capability to create and call PROCEDURES
>>
>>before proceding to do PL/Java? It would add to the usefulness
>>greatly, I should think. I'm not sure how hard it would be.
>>    
>>
>
>This should be relatively easy if you omit OUT parameters.  Just treat 
>CALL foo(...) as SELECT foo(...) and throw away the result (or avoid 
>generating one).
>  
>

That did occur to me. Perhaps in a similar vein we could treat CREATE 
PROCEDURE as being the same as CREATE FUNCTION but without a return 
type? I seem to recall someone asking for that quite recently, anyway.

>For OUT parameters, we'd probably have to learn their full semantics 
>first.
>
>  
>

As an old Ada programmer, I am quite familiar with the basic semantics 
:-) I see that the grammar actually has provision currently for 
IN/OUT/INOUT (although they cause "feature not supported" errors) which 
is slightly strange without also having provision for parameter names, 
which is not in the grammar at all.

The draft standard for SQL/JRT contains some interesting examples, 
including having a procedure return a result set via an implicit (from 
the SQL point of view) parameter (It's not clear if you can use this 
mechanism to return multiple result sets, but I assume you can).

cheers

andrew