Ludwig,
> Has anyone done performance comparison between
> triggers/functions in C vs. PL/PGSQL?
On simple ON UPDATE triggers that update an archive table, but are called many
times per minute, about 20:1 in favor of C triggers. Partly that depends on
whether you load the C function as an external file or compile it into the
database. The latter is, of course, faster by far less flexible.
Partly this is because C is fast, being a lower-level language. Partly this
is because the PL/pgSQL parser is in *desperate* need of an overhaul, as it
was written in a hurry and has since suffered incremental development.
> What are the drawbacks of functions written using
> C?
Writing C is harder. Gotta manage your own memory. Plus a badly written C
function can easily crash Postgres, whereas that's much harder to do with
PL/pgSQL.
Usually I just write the original Trigger in PL/pgSQL, test & debug for data
errors, and then farm it out to a crack C programmer to convert to C.
--
-Josh Berkus
Aglio Database Solutions
San Francisco