Re: Cool PL/PgSQL hack :)

> > But, for the sake of testing, here are a few tests:
>
> These are way cool. Mind posting an article on Techdocs with them?
> On on Elein's site ... she asked me for an article ....

I'd love to, but I'm so behind with everything, I shouldn't dare add
another something to my plate at the moment. :( If someone wants to
take the bits that I've written, however, and bottle them up, go for
it. If that someone needs an authoritative response to questions, I
can play the role. If they want to include pl/ruby in their article,
that'd be very cool+ (note, pl/ruby hasn't been updated for 7.4 yet:
I'll patch it if someone's interested).

URL: http://moulon.inra.fr/ruby/plruby.html
Download: ftp://moulon.inra.fr/pub/ruby/

> > PS I know the C examples didn't connect to the SPI manager, but it
> > wasn't necessary for the tests given. Fetching rows is IO bound and
> > my laptop's HDD isn't what I'd call server quality so I stuck to
> > purely mathematical functions for now. pl/pgsql has a nasty habit of
> > copying data with ROWTYPE's/RECORD's, whereas in C you can use a fixed
> > buffer and just realloc() the space. *sigh* I wish pl/pgsql was
> > smarter in that regard and would only really copy if the function had
> > RETURN NEXT or returned a SETOF data.
>
> FWIW, PL/plSQL was originally written by Jan Wieck as an
> intermediate step to developing PL/tcl, which is what he uses. As
> such, the first version was quite hackish, and was only moderately
> cleaned up by Great Bridge for Oracle migration. A lot of the
> internals of PL/pgSQL are very inefficient and could do with an
> overhaul.

Heh, ain't that the truth. I've done some super cursory work (read:
basic investigation/design) for plpgsqlc: converts pl/pgsql to C, then
compiles the C into a .so that lives in the data directory and
automatically registers the function as C type + temp path. I think
it'd be foolish to revamp pl/pgsql and have it continue to use an AST
interpreted model when pl/pgsql can be converted oh so nicely to C
given pl/pgsql's strict block structure. It should be a blend of ecpg
and pl/pgsql's grammar files, for all intents and purposes.

Given the huge number of pl/pgsql functions that I've got, rewriting
them all by hand to C would be a nightmare and keeping them up to date
would be equally as ugly. Keeping them as pl/pgsql is a performance
boon given that stored procs and functions are write once, execute
zillions of times. In the above model, recreating the function would
do the trick. If plpgsqlc doesn't work, with syntactic compatibility
available, changing the language type back to just plpgsql solves
backward compatibility problems as well as handles the case where a
machine doens't have a compiler or *shudder* .so support. With the
libs being mmap()'ed too, this should save considerably on RAM.
*lusts for free time*

-sc

--
Sean Chittenden