Re: Lisp as PostgreSQL trigger / stored procedure language

On Fri, 05 Nov 2004 05:27:50 -0500, Travis Cross wrote:

> After years of loyalty to MySQL, I have finally jumped ship and landed 
> squarely in the PostgreSQL camp.  Now I'm in love with the ability to 
> craft database-side functions, rules, and triggers.  The flexibility to 
> write functions and triggers in PL/pgSQL, PL/Tcl, PL/Perl, and PL/Python 
> has completely changed the way I approach an application.
> 
> There's just one problem.  More and more of my application logic is 
> being crafted in languages other than Lisp!  The cause is that there 
> doesn't seem to be a call handler that would allow Lisp to run inside 
> the PostgreSQL server, and I can't determine if or how a compiled Lisp 
> function might be able to be used in place of a compiled C function.
> 
> I am considering writing the needed call handler (PL/CL, PL/CLu), but 
> I'm kind of a hack when it comes to producing substantial amounts of C 
> code, especially in unfamiliar territory.  (The PostgreSQL documentation 
> gives a short example call handler and notes, "Only a few thousand lines 
> of code have to be added instead of the dots." ;) )

I once (long ago) worked on a similar thing for Guile (a scheme dialect).
This is anything but easy. Todays postgresql is a rather C-biased
application, so any extention language you want to implement needs to have
a rather broad support for interaction with the calling (C) environment.
One of the "issues" i recall was memory management interaction (postgresql
uses stacked/nested  memory pools for different levels of handlers, all
memory in a pool is freed once the handler context is done. Interfacing
this with other garbage collected interpreters is pretty tricky. Another
issue (at least back then - no idea how it is done today): in case of
an aborted transaction nonlocal exits are done by means of longjmp. Again,
something that's hard to cope with in interpreters.

> Since Lisp is a compiled language (I generally use SBCL), it seems to me
> that there should be some way to call a compiled Lisp binary in a
> somewhat analogous fashion to calling a C binary.  Does anyone have
> insights here on how to make this work?

Hmm, you'd have to use a Lisp that produces DSOs/DLLs ...
Maybe GLC or ECL would be an option.
 
> Has anyone else looked into this or taken an interest?  Is there
> anything that's fallen below my radar screen?

Never came across such a beast - sadly, when you look at the innermost
guts of postgresql you'll find a lot of lispish code (some of which shows
when you switch on extensive debugging  - query plans etc. come out in
some sort of S-expression). IIRC the core once _was_ written in LISP.

> Insights? Suggestions?  Comments welcome.
> 
> As Lisp is generally well regarded and received as a powerful extension
> language, it would seem to me that Lisp integration in PostgreSQL could
> do well to boost the profile of both CL and PostgreSQL.

Well, of course. Query plan rewritng etc. would be sooo nice in Lisp.

 Just my 0.02$

   Ralf Mattes

> Thanks,
> 
> -- Travis
> 
> Sidenote for those unfamiliar with PostgreSQL: PostgreSQL doesn't
> naturally support any languages for functions or triggers per se (except
> compiled C).  Instead, PostgreSQL provides an interface for procedural
> language handlers.  The call handler receives a pointer containing
> argument information and OID of the function to call, and the handler is
> expected to return a result (Datum).  There are also two classes of
> embedded languages in PostgreSQL: trusted and untrusted, which indicate
> if the language can cause side-effects outside of the database (such as
> sending mail).
> http://www.postgresql.com/docs/7.4/static/plhandler.html