Thread: implemention of calls to stored procs.
I've been looking at the implementation of the procedural language support code with a view to writing a java plugin (ie: something to allow java classes to be used as stored procs). From what I udnerstand of the architecture it seems that the SPI API is inherently single threaded. I think this because of the way that the SPI stuff work, one calls SPI_connect and then you can do an SPI_exec to do a query, this sets a global value... Perhaps I've misunderstood the implementation details and there is some hidden black magic that means that more than one thread can be doing some SPI operations? If not, am I right? Can only one user supplied proc be running at once across the whole postgres engine? Nic Ferrier
Nic Ferrier <nferrier@tapsellferrier.co.uk> writes:
> From what I udnerstand of the architecture it seems that the SPI API
> is inherently single threaded.
The entire backend is inherently single-threaded.
> If not, am I right? Can only one user supplied proc be running at once
> across the whole postgres engine?
What do you consider "the whole engine"? Each connection has its own
backend process. Also, SPI is recursive even though not thread-safe:
it's possible for an SPI-executed query to call a function that
performs another SPI query.
regards, tom lane
Nic Ferrier <nferrier@tapsellferrier.co.uk> writes: > I've been looking at the implementation of the procedural language > support code with a view to writing a java plugin (ie: something to > allow java classes to be used as stored procs). Someone else has been talking about this--check the archives from the last six months. > >From what I udnerstand of the architecture it seems that the SPI API > is inherently single threaded. I think this because of the way that > the SPI stuff work, one calls SPI_connect and then you can do an > SPI_exec to do a query, this sets a global value... > > > Perhaps I've misunderstood the implementation details and there is > some hidden black magic that means that more than one thread can be > doing some SPI operations? > > If not, am I right? Can only one user supplied proc be running at once > across the whole postgres engine? The PG backend is not threaded. There is one Unix process per connection. Each process can be running its own stored procedure simultaneously, subject to the usual table/index locking constraints. -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863
Firstly, thanks for your responses... good to know I was thinking the right thing (and, yes, I was taking the process thing into account, tho' I didn't realise threads weren't used at all). Doug McNaught <doug@wireboard.com> writes: > Nic Ferrier <nferrier@tapsellferrier.co.uk> writes: > > > I've been looking at the implementation of the procedural language > > support code with a view to writing a java plugin (ie: something to > > allow java classes to be used as stored procs). > > Someone else has been talking about this--check the archives from the > last six months. I couldn't find any reference but the archive searcher is broken right now and a manual search is not very reliable. It's not terribly difficult to crack this actually... I was going to use GCJ as a platform for a base java class that could be used like a quick C stored proc. I envisage having a natively implemented JDBC Connection passed to an init method in such a class. GCJ is perfect for this task because it has a native call interface, CNI, which is a seamless part of the class heirarchy. Once I've got something working I'll drop a line here. Nic
Nic Ferrier <nferrier@tapsellferrier.co.uk> writes: > It's not terribly difficult to crack this actually... I was going to > use GCJ as a platform for a base java class that could be used like a > quick C stored proc. Interesting approach. The other person that posted recently was going to run Java as a separate daemon, since the idea of linking postgres with the JVM was causing loud gagging sounds among the members of this list (for reasons I agree with). Are you talking about Java compiled to native code rather than bytecode? I know GCJ can do that and it would seem to be a better way to do this (since you can already link in native compiled C libraries). The other thing to be aware of is that, if GCJ requires linking with thread libraries, you may have some problems, since the PG backend itself is not threaded--I'm not sure I'd want a threaded library call running in a non-thread-aware app. > I envisage having a natively implemented JDBC Connection passed to an > init method in such a class. > > GCJ is perfect for this task because it has a native call interface, > CNI, which is a seamless part of the class heirarchy. > > Once I've got something working I'll drop a line here. Have fun! Sounds a neat idea. -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863
Doug McNaught <doug@wireboard.com> writes: > Nic Ferrier <nferrier@tapsellferrier.co.uk> writes: > > > It's not terribly difficult to crack this actually... I was going to > > use GCJ as a platform for a base java class that could be used like a > > quick C stored proc. > > Interesting approach. The other person that posted recently was going > to run Java as a separate daemon, since the idea of linking postgres > with the JVM was causing loud gagging sounds among the members of this > list (for reasons I agree with). > > Are you talking about Java compiled to native code rather than > bytecode? I know GCJ can do that and it would seem to be a better way > to do this (since you can already link in native compiled C > libraries). Yes. But that's just the start. PostgreSQL can link in shared librarys, GCJ can make shared librarys from java source code (and more importantly from a combination of java and C++ source code!). But once it's doing that it should be possible to get GCJ to load class files as well (because GCJ can do that). > The other thing to be aware of is that, if GCJ requires linking with > thread libraries, you may have some problems, since the PG backend > itself is not threaded--I'm not sure I'd want a threaded library call > running in a non-thread-aware app. GCJ doesn't require thread libs, you can build with no-threads. Also, I have to admit that I'm not sure how a compiled lib works in terms of threads, whether it starts a new (real) thread only when the java code wants to start a new thread or whether it forks into a new thread straight away. I suspect the former (easy enough to find out on linux though). Anyway, should be a laugh! Nic
On Wed, Jan 23, 2002 at 12:11:09AM +0000, Nic Ferrier wrote:
> I've been looking at the implementation of the procedural language
> support code with a view to writing a java plugin (ie: something to
> allow java classes to be used as stored procs).
As you probably know, you are not the first attempting to allow java
classes as stored procs. I'm interested in why you people are
attempting this. I can imagine 2 goals, the one of which is a hot
issue also for me and should has a relative simple solution, the other
seems to be not very imported and heavy to achieve:
1. goal) Give PostgreSQL after triggers the ability to notify Java
apps about events in the database. In almost all cases this
would be done using a JMS (Java Message Service) implementation
in an application server which has to be called by the after
trigger.
2. goal) To have Java as a YAPL4P (yet another procedural language for
PostgresSQL).
I think all the big problems regularly reported on this list when a
new attempt is announced to be made to create a java plugin for
PostgreSQL are related to the second point: Java as a full-scale
YAPL4P - which is nice but should not be very important.
But if you have the same goal as I would like to have to time for:
using Java to notify outside Java apps about database events, then - I
think - your approach is promising and I wish you really good luck.
--
Holger Krug
hkrug@rationalizer.com
Holger Krug <hkrug@rationalizer.com> writes: > As you probably know, you are not the first attempting to allow java > classes as stored procs. I'm interested in why you people are > attempting this. I can imagine 2 goals, the one of which is a hot > issue also for me and should has a relative simple solution, the other > seems to be not very imported and heavy to achieve: > > 1. goal) Give PostgreSQL after triggers the ability to notify Java > apps about events in the database. In almost all cases this > would be done using a JMS (Java Message Service) implementation > in an application server which has to be called by the after > trigger. > 2. goal) To have Java as a YAPL4P (yet another procedural language for > PostgresSQL). > > I think all the big problems regularly reported on this list when a > new attempt is announced to be made to create a java plugin for > PostgreSQL are related to the second point: Java as a full-scale > YAPL4P - which is nice but should not be very important. > > But if you have the same goal as I would like to have to time for: > using Java to notify outside Java apps about database events, then - I > think - your approach is promising and I wish you really good luck. Actually my initial goal is neither of these things. My initial goal is to learn about the internals of pg. I am more of a Java programmer than a C programmer (just because I'm lazy) and therefore I would like to pursue your second option. The most difficult part of that is actually defining the java function interface, but other than that I think I've already cracked it (I wrote most of the JDBC implementation last night). As for your 1st objective... I don't think that would be easy, even with what I'm proposing there would have to be some sort of RMI or CORBA link to the remote app. Nic
On Wed, Jan 23, 2002 at 11:32:04AM +0000, Nic Ferrier wrote: > As for your 1st objective... I don't think that would be easy, even > with what I'm proposing there would have to be some sort of RMI or > CORBA link to the remote app. As far as I know, GCJ provides RMI libs, although not 100% tested. It should be relatively easy because you must not handle transactions. Concerning your implementation. Did I understand you right, that you've implemented your own Connection class, in an instance of which you encapsulate the current backend transaction ? If yes, so OK, it's probably simpler than getting RMI to work, and really nice. -- Holger Krug hkrug@rationalizer.com
Holger Krug <hkrug@rationalizer.com> writes: > On Wed, Jan 23, 2002 at 11:32:04AM +0000, Nic Ferrier wrote: > > As for your 1st objective... I don't think that would be easy, even > > with what I'm proposing there would have to be some sort of RMI or > > CORBA link to the remote app. > As far as I know, GCJ provides RMI libs, although not 100% tested. It > should be relatively easy because you must not handle transactions. We haven't tested RMI, I don't know anyone who has tried to use it with GCJ. No problem though because I think CORBA ORBS have been tested. > Concerning your implementation. Did I understand you right, that > you've implemented your own Connection class, in an instance of which > you encapsulate the current backend transaction ? If yes, so OK, it's > probably simpler than getting RMI to work, and really nice. Yes, I've implemented a native Connection class using the SPI stuff. I don't think that solves any problem with your second objective though, the problem is the remoteness of the java code. My java stored procs will be just like C ones, running on the database server machine (and tied to the pg process). Presumably what you're wanting to do is notify some Java code outside of the pg process, for that you'd need some remote signalling mechanism. Nic
On Wed, Jan 23, 2002 at 11:58:12AM +0000, Nic Ferrier wrote: > I don't think that solves any problem with your second objective > though, the problem is the remoteness of the java code. It doesn't. -- Holger Krug hkrug@rationalizer.com
Nic, Check out http://www.rootshell.be/~hornyakl/download This has the latest code for pl/pgj. The Java procedure language support that Laszlo Hornyak (hornyakl@users.sourceforge.net) has been working on for the last month or so. thanks, --Barry Nic Ferrier wrote: > Firstly, thanks for your responses... good to know I was thinking the > right thing (and, yes, I was taking the process thing into account, > tho' I didn't realise threads weren't used at all). > > > Doug McNaught <doug@wireboard.com> writes: > > >>Nic Ferrier <nferrier@tapsellferrier.co.uk> writes: >> >> >>>I've been looking at the implementation of the procedural language >>>support code with a view to writing a java plugin (ie: something to >>>allow java classes to be used as stored procs). >>> >> >>Someone else has been talking about this--check the archives from the >>last six months. >> > > I couldn't find any reference but the archive searcher is broken right > now and a manual search is not very reliable. > > It's not terribly difficult to crack this actually... I was going to > use GCJ as a platform for a base java class that could be used like a > quick C stored proc. > > I envisage having a natively implemented JDBC Connection passed to an > init method in such a class. > > > GCJ is perfect for this task because it has a native call interface, > CNI, which is a seamless part of the class heirarchy. > > Once I've got something working I'll drop a line here. > > > > Nic > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > >
I need to have a C++ class become a dynamically linked function. Can I use PG_FUNCTION_INFO_V1 on a class method? I'm guessing that I can't. Does anyone know? Nic
Nic Ferrier <nferrier@tapsellferrier.co.uk> writes:
> I need to have a C++ class become a dynamically linked function.
> Can I use PG_FUNCTION_INFO_V1 on a class method?
Only if it matches the expected signature, which is going to force
you to make it a static method. You'll probably want to have a set
of static linking methods that call into the regular methods of
the class.
regards, tom lane