Choosing Between PL/PGSQL or C/C++ for Triggers/Store Procs

I use C for the following reasons (in order of importance - to me, of
course):

1. tired of having to lear new languages :-) I was always wonderring why
does every database vendor have to invent their own syntax? Why not
stick to something existing, well defined and known to work - like perl,
for example....
2. Flexibility and functionality - you can do *anything* in a "C" stored
procedure. Literally - it is loaded into the postgres process, and you
can call any function you want, without being limited to what pgsql
thinks you are going to need... Of course, if you do it wrong, it'll
crash, and you are on your own.... But I am willing to take the risk for
the benefit of being able to do stuff I need to do.
3. Maintainability - if you wrote a function (*especially* a trigger),
and it doesn't seem to be doing what you want, what do you do? If it is
in "C", you can attach to it with a debugger, set a breakpoint, and look
at what is going on, and if it is in pgsql... well, unless I don't know
something important about it, your only choice is going to be to stare
at the code and try to guess what the hell is wrong with it .
4. Performance - generally, a C function will be faster just because it
does not have to be interpreted, but, I believe, the difference will be
miniscule... The main advantage here boils down to the item #2 above -
the flexibility. In a C function you can go right after what you want to
do, and do it just the way that will be quickest in your particular
situation, you deal directly with the data structures you intend to
modify, and do not waste time on converting them back and forth, you can
keep caches, do binary searches or hash lookups... whatever you want to
speed it up.

Dima

Clay Luther wrote:

On Thu, 2003-06-12 at 14:54, Clay Luther wrote:

For a very complex database in which we plan to implement database rules
via triggers, I need some information on which language, PL/PGSQL or
C/C++ provides the most "bang for the buck" with respect to performance.
One initially presumes the C/C+ stored procs/triggers will be faster,
but after reading some posts on the list about the Query Planner and
PL/PGSQL, I was wondering if using PL/PGSQL might not be better.

1) Is there a clear-cut choice when performance is the issue?

If performance is the issue then C functions are the way to go.

2) Is there a fuzzy choice at work here? Perhaps PL/PGSQL for simple,
obvious stored proces/triggers, but C/C++ for complexity?

Generally I'd say no, *if performance is the issue*. The main case
(imho) you'd want to use plpgsql or sql functions is becuase they are a
lot easier to write and maintain. If you don't care about that (and your
skill level with C might be such that you don't) then C functions are
the way to go.

BTW - If you ever feel the motivation to write a tutorial, a basic
tutorial on converting plpgsql functions to C functions would be pretty
neat.

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

On Thu, Jun 12, 2003 at 16:49:01 -0400,
Robert Treat <xzilla@users.sourceforge.net> wrote:

Generally I'd say no, *if performance is the issue*. The main case
(imho) you'd want to use plpgsql or sql functions is becuase they are a
lot easier to write and maintain. If you don't care about that (and your
skill level with C might be such that you don't) then C functions are
the way to go.

sql functions are inlined in 7.4, so they may be faster than c functions
under many circumstances.