Thread

implemention of calls to stored procs.

Nic Ferrier <nferrier@tapsellferrier.co.uk> — 2002-01-23T00:11:09Z

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

Re: implemention of calls to stored procs.

Doug McNaught <doug@wireboard.com> — 2002-01-23T00:13:37Z

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

Re: implemention of calls to stored procs.

Tom Lane <tgl@sss.pgh.pa.us> — 2002-01-23T00:15:14Z

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

Re: implemention of calls to stored procs.

Nic Ferrier <nferrier@tapsellferrier.co.uk> — 2002-01-23T00:56:28Z

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

Re: implemention of calls to stored procs.

Barry Lind <barry@xythos.com> — 2002-01-23T01:54:16Z

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

Re: implemention of calls to stored procs.

Doug McNaught <doug@wireboard.com> — 2002-01-23T02:01:38Z

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

Re: implemention of calls to stored procs.

Nic Ferrier <nferrier@tapsellferrier.co.uk> — 2002-01-23T02:27:49Z

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

Re: implemention of calls to stored procs.

Holger Krug <hkrug@rationalizer.com> — 2002-01-23T07:31:37Z

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

Re: implemention of calls to stored procs.

Holger Krug <hkrug@rationalizer.com> — 2002-01-23T11:30:53Z

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

Re: implemention of calls to stored procs.

Nic Ferrier <nferrier@tapsellferrier.co.uk> — 2002-01-23T11:32:04Z

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

Re: implemention of calls to stored procs.

Nic Ferrier <nferrier@tapsellferrier.co.uk> — 2002-01-23T11:58:12Z

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

Re: implemention of calls to stored procs.

Holger Krug <hkrug@rationalizer.com> — 2002-01-23T12:56:58Z

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

Can I use PG_FUNCTION_INFO_V1 in C++

Nic Ferrier <nferrier@tapsellferrier.co.uk> — 2002-01-24T16:00:06Z

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

Re: Can I use PG_FUNCTION_INFO_V1 in C++

Tom Lane <tgl@sss.pgh.pa.us> — 2002-01-24T16:20:49Z

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