.NET or Mono functions in PG

I am investigating the possibility of having stored procedures
and functions written in .NET language flavors. I remember a long time
ago there was a gborg project wanting to implement it, but that is long
gone I guess. anyway....

I think there are two possibilities:

a) Use of MS.NET core and implement it only for Windows.
b) Use of Mono core and have the possibility to run it also on *nix systems.
c) Or perhaps a hybrid of the above would be possible.

Any implementation would require to invoke/call an external runtime (Mono or
MS.CRL).
I wonder what the performance would be.

I would like to have your opinion about this idea.

Regards,
Gevik Babakhani

------------------------------------------------
PostgreSQL NL http://www.postgresql.nl
TrueSoftware BV http://www.truesoftware.nl
------------------------------------------------

Hello

I thought about it. It can be great project. Oracle has similar
project. Performance will be same as PL/Java probably. There is slow
only first call.

Regards
Pavel Stehule

On 30/11/2007, Gevik Babakhani <pgdev(at)xs4all(dot)nl> wrote:
>
> I am investigating the possibility of having stored procedures
> and functions written in .NET language flavors. I remember a long time
> ago there was a gborg project wanting to implement it, but that is long
> gone I guess. anyway....
>
> I think there are two possibilities:
>
> a) Use of MS.NET core and implement it only for Windows.
> b) Use of Mono core and have the possibility to run it also on *nix systems.
> c) Or perhaps a hybrid of the above would be possible.
>
> Any implementation would require to invoke/call an external runtime (Mono or
> MS.CRL).
> I wonder what the performance would be.
>
> I would like to have your opinion about this idea.
>
> Regards,
> Gevik Babakhani
>
> ------------------------------------------------
> PostgreSQL NL http://www.postgresql.nl
> TrueSoftware BV http://www.truesoftware.nl
> ------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

On Nov 30, 2007 8:31 AM, Gevik Babakhani <pgdev(at)xs4all(dot)nl> wrote:
> I am investigating the possibility of having stored procedures
> and functions written in .NET language flavors. I remember a long time
> ago there was a gborg project wanting to implement it, but that is long
> gone I guess. anyway....

Yeah, I have a copy of the old PL/.NET backed up somewhere. IIRC, it
needs to be rewritten, but it would be possible to make it work for
both Mono and .NET.

--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah(dot)harris(at)enterprisedb(dot)com
Edison, NJ 08837 | http://www.enterprisedb.com/

On Nov 30, 2007 9:20 AM, Jonah H. Harris <jonah(dot)harris(at)gmail(dot)com> wrote:
> Yeah, I have a copy of the old PL/.NET backed up somewhere. IIRC, it
> needs to be rewritten, but it would be possible to make it work for
> both Mono and .NET.

Actually, it was PL/Mono. I'll dig through my stuff and post it
somewhere for you.

--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah(dot)harris(at)enterprisedb(dot)com
Edison, NJ 08837 | http://www.enterprisedb.com/

It would we great. Thank you.

Regards,
Gevik Babakhani
------------------------------------------------
PostgreSQL NL http://www.postgresql.nl
TrueSoftware BV http://www.truesoftware.nl
------------------------------------------------

> -----Original Message-----
> From: pgsql-hackers-owner(at)postgresql(dot)org
> [mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of
> Jonah H. Harris
> Sent: Friday, November 30, 2007 4:10 PM
> To: Gevik Babakhani
> Cc: pgsql-hackers(at)postgresql(dot)org
> Subject: Re: [HACKERS] .NET or Mono functions in PG
>
> On Nov 30, 2007 9:20 AM, Jonah H. Harris
> <jonah(dot)harris(at)gmail(dot)com> wrote:
> > Yeah, I have a copy of the old PL/.NET backed up somewhere.
> IIRC, it
> > needs to be rewritten, but it would be possible to make it work for
> > both Mono and .NET.
>
> Actually, it was PL/Mono. I'll dig through my stuff and post
> it somewhere for you.
>
> --
> Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
> EnterpriseDB Corporation | fax: 732.331.1301
> 499 Thornall Street, 2nd Floor |
> jonah(dot)harris(at)enterprisedb(dot)com
> Edison, NJ 08837 | http://www.enterprisedb.com/
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

On Fri, Nov 30, 2007 at 02:31:44PM +0100, Gevik Babakhani wrote:
>
> I am investigating the possibility of having stored procedures
> and functions written in .NET language flavors. I remember a long time
> ago there was a gborg project wanting to implement it, but that is long
> gone I guess. anyway....
>
> I think there are two possibilities:
>
> a) Use of MS.NET core and implement it only for Windows.
> b) Use of Mono core and have the possibility to run it also on *nix systems.
> c) Or perhaps a hybrid of the above would be possible.
>
> Any implementation would require to invoke/call an external runtime (Mono or
> MS.CRL).
> I wonder what the performance would be.
>
> I would like to have your opinion about this idea.

I did look at this at some earlier point as well. One big problem at that
time was that once you embedded mono, yuo had all sorts of threads running
in your backend ;-)

Another way to do it is "the PL/J" way (I think). Which is starting up a
separate process with the VM in it and then do RPC of some kind to it.
Which has more overhead per call, but lower per backend etc. And a lot less
"dangerous".

I never got past the thinking stage though. But it would certanily be nice
to have!

//Magnus

> I did look at this at some earlier point as well. One big
> problem at that time was that once you embedded mono, you had
> all sorts of threads running in your backend ;-)

yes, threads running around could become a show stopper for both Mono and
MS.NET
This is something I want to be sure of before I begin.

> Another way to do it is "the PL/J" way (I think). Which is
> starting up a separate process with the VM in it and then do
> RPC of some kind to it.
> Which has more overhead per call, but lower per backend etc.
> And a lot less "dangerous".

This is actually my plan B, hence it is less dangerous.
I am thinking the "started process" can stay alive and act
as a service of some kind to handle/answer calls form multiple
backends and shutdown itself after a period of time being
idle.

Regards,
Gevik Babakhani
------------------------------------------------
PostgreSQL NL http://www.postgresql.nl
TrueSoftware BV http://www.truesoftware.nl
------------------------------------------------

On Fri, Nov 30, 2007 at 04:43:03PM +0100, Gevik Babakhani wrote:
> > I did look at this at some earlier point as well. One big
> > problem at that time was that once you embedded mono, you had
> > all sorts of threads running in your backend ;-)
>
> yes, threads running around could become a show stopper for both Mono and
> MS.NET
> This is something I want to be sure of before I begin.

Yaeh.

> > Another way to do it is "the PL/J" way (I think). Which is
> > starting up a separate process with the VM in it and then do
> > RPC of some kind to it.
> > Which has more overhead per call, but lower per backend etc.
> > And a lot less "dangerous".
>
> This is actually my plan B, hence it is less dangerous.
> I am thinking the "started process" can stay alive and act
> as a service of some kind to handle/answer calls form multiple
> backends and shutdown itself after a period of time being
> idle.

Yes, you should have just one such process with .Net managed threads
running inside it. You just need to find a good RPC library to pass things
back and forth between the backend running natively and this special
process running in .net.

//Magnus

On Nov 30, 2007 10:09 AM, Jonah H. Harris <jonah(dot)harris(at)gmail(dot)com> wrote:
> Actually, it was PL/Mono. I'll dig through my stuff and post it
> somewhere for you.

Just searched my backups and can't seem to find it :( It was on gborg
CVS. Where is that now?

--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah(dot)harris(at)enterprisedb(dot)com
Edison, NJ 08837 | http://www.enterprisedb.com/

Jonah H. Harris wrote:
> On Nov 30, 2007 10:09 AM, Jonah H. Harris <jonah(dot)harris(at)gmail(dot)com> wrote:
>> Actually, it was PL/Mono. I'll dig through my stuff and post it
>> somewhere for you.
>
> Just searched my backups and can't seem to find it :( It was on gborg
> CVS. Where is that now?

Dead.
But Marc has a tarball of the whole thing somewhere and can find it.

//Magnus

On Nov 30, 2007 1:21 PM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> > Just searched my backups and can't seem to find it :( It was on gborg
> > CVS. Where is that now?
>
> Dead.
> But Marc has a tarball of the whole thing somewhere and can find it.

OK, I'll see if I can get a copy of it.

--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah(dot)harris(at)enterprisedb(dot)com
Edison, NJ 08837 | http://www.enterprisedb.com/

Magnus Hagander wrote:
> I did look at this at some earlier point as well. One big problem at that
> time was that once you embedded mono, yuo had all sorts of threads running
> in your backend ;-)
>
Is that necessarily a problem? You have to compile with a
thread-capable libc and take some
care that the heap implementation is well tuned, but there's no reason
why the mono housekeeping
threads should cause you any problem is there? It should be much like
the embedded Java.
> Another way to do it is "the PL/J" way (I think). Which is starting up a
> separate process with the VM in it and then do RPC of some kind to it.
> Which has more overhead per call, but lower per backend etc. And a lot less
> "dangerous".
>
>
Given that one would want to embed to have very low latency both on
trigger invocation and
on calls back into the data engine, I don't really see the point personally.

I'm not sure how important it is to make the embeded interface look like
a standard
interface (in that way that the embedded CLR in MSSQL does - mostly) or
whether
it can be a thin wrapper over the C API. I think there's good mileage
in starting
with the thin wrapper, then at least some common business logic code can
be used.

James

James Mansion wrote:
> Magnus Hagander wrote:
>> I did look at this at some earlier point as well. One big problem at that
>> time was that once you embedded mono, yuo had all sorts of threads
>> running
>> in your backend ;-)
>>
> Is that necessarily a problem? You have to compile with a
> thread-capable libc and take some
> care that the heap implementation is well tuned, but there's no reason
> why the mono housekeeping
> threads should cause you any problem is there? It should be much like
> the embedded Java.

Depends on what you mean by a problem. It's something you need to think
about, and think hard about, and be sure you deal with. But it can be done.

And yes, the housekeeping threads could be a problem. If you end up with
for example something called on the GC thread calling back out into
"native mode", while the backend is doing something completely different.

And yes, it would be the same as embedding Java. And it has been done
with pl/java, so it can be done :)

An interesting thing could be to look at if code could be "stolen from"
or even better shared with pl/java, if this is the road to go down.

>> Another way to do it is "the PL/J" way (I think). Which is starting up a
>> separate process with the VM in it and then do RPC of some kind to it.
>> Which has more overhead per call, but lower per backend etc. And a lot
>> less
>> "dangerous".
>>
>>
> Given that one would want to embed to have very low latency both on
> trigger invocation and
> on calls back into the data engine, I don't really see the point
> personally.
>
> I'm not sure how important it is to make the embeded interface look like
> a standard
> interface (in that way that the embedded CLR in MSSQL does - mostly) or
> whether
> it can be a thin wrapper over the C API. I think there's good mileage
> in starting
> with the thin wrapper, then at least some common business logic code can
> be used.

Agreed, that'd be a good start. But it would certainly be convenient if
you could have access compatible with the System.Data stuff, since
there's bound to be a lot of code (and coders) that know about that...

//Magnus

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> James Mansion wrote:
>> Is that necessarily a problem?
> ...
> And yes, it would be the same as embedding Java. And it has been done
> with pl/java, so it can be done :)

It is also pretty well established that if pltcl or plperl cause the
backend to become multithreaded, things break horribly. I strongly
suspect that the only reason we've not seen similar reports about
other PLs that might introduce extra threads is that the market
penetration of other PLs is epsilon.

regards, tom lane

Tom Lane wrote:
> It is also pretty well established that if pltcl or plperl cause the
> backend to become multithreaded, things break horribly. I strongly
>
Isn't that something that needs to be fixed? Its one thing not to allow
for multiple threads to
be in your code, but not having a threaded library linked in is going to
become more and
more of a problem.

James