Обсуждение: 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