Re: Experience with PL/xx?

On Mon, 2003-10-27 at 15:17, scott.marlowe wrote:
> On 27 Oct 2003, Klaus P. Pieper wrote:
>
> > Hi,
> >
> > does anybody out there have experience with the several PL's which are
> > available for PostgreSQL? I am currently evaluating several databases
> > (commercial as well as free & open source) for a new project and would
> > just like to hear some feedback.
> >
> > PL/Java seems to be developed by a fairly small team - no updates on
> > their website since December 2002 (and even what's available on this
> > web site is not very encouraging to use PL/Java on a production
> > system). Does anybody use PL/Java?
> >
> > What about PL/Python or PL/Ruby? Any experience with these two
> > implementations on a production system? Are there any PostgreSQL
> > specifics or limitations? Significant differences between these two
> > languages?
>
> The two or three "heavy lifters" of the pl/xx set are plSQL, which is just
> SQL wrapped in a function, and fairly limited, PL/pgSQL, which is
> Postgresql's implementation that is fairly similar to Oracle's plsql, and
> C functions. Generally, functions written in C are the fastest, but also
> require the most care, as improperly written ones can cause individual
> backends to crash.
>
> pl/pgsql is a good compromise between these two and probably has the most
> stored procs written in it.
>
> pl/python just lost its trusted status, so that might be an issue until
> they get rexec fixed.
>
> I haven't used plruby.
>
> pl/R is a nice choice if you're doing statistical analysis
>
> pl/tcl has gotten good marks
>
> pl/php seems to be maturing quickly, and will likely get a good shaking
> down in the next 6 months
>
> I'm not sure what the status of pl/J is, but I can't imagine a pl needing
> a lot of maintenance once the bugs are worked out, as it's mostly just
> glue code. I.e. the real updates come in Java and Postgresql
> individually, not in the interface layer.
>
> If you're looking to get started, plpgsql is a very good starting point.
>
> If you need a trusted language (i.e. sandboxed) plpgsql, pl/J, pl/php,
> pl/perl, and pl/tcl are good choices to explore, as they can all run in a
> trusted version.
>

I'm not as bullish on all of these as scott, heres my break down of
things I'd want to look at in a pl language (in no particular order):

1. trusted/untrusted status
this controls how what privileges the pllang will have, like opening
sockets, reading the filesystem, etc... there are pros and cons for each
type, but for maximum flexibility you want both.

2. trigger support
this means you can write triggers directly in the pllang in question.
this can be worked around but it's a sign of maturity for the pllang
imho

3. able to query against the database
some pllangs can not execute queries against the database, but are
limited to only being able to perform functions "native" to the lang.

4. is it part of the main distribution
this one is really unfair to some of the languages, like plr which has a
great maintainer in Joe Conway, but if a language isn't in the core
distribution, it's likely not getting banged on like the ones that are
in core. unless i was really familiar with the base language in question
I'd stick to what's in the main packages.

none of these are deal killers depending on what your use is for them,
but its a good way to size up the different choices. in my production
system i would say my current breakdown is 25% sql funcs, 70% plpgsql,
and 5% pltcl, with a couple of plc's from contrib I have modified. I've
also used plpython, plperl, and plphp at least somewhat in passing, but
none of those offer me things I don't already have in my main 3 (and
note I've never written a tcl program in my life)

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL