Thread: Choosing Between PL/PGSQL or C/C++ for Triggers/Store Procs
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?
2) Is there a fuzzy choice at work here? Perhaps PL/PGSQL for simple, obvious stored proces/triggers, but C/C++ for complexity?
Thanks.
1) Is there a clear-cut choice when performance is the issue?
2) Is there a fuzzy choice at work here? Perhaps PL/PGSQL for simple, obvious stored proces/triggers, but C/C++ for complexity?
Thanks.
-- Clay claycle@cisco.com |
IMHO pl/psql triggers are much easier to mantain, not mentioning that triggers are often writen by programers who never learned "C".
But there are some tasks that You simple can't do with "pl/psql", You have much more "opened" interface to server, and to operating system(try to write function that writes log to some textual file with pl/psql.)
Few months ago I had to write trigger that generates UPDATE, INSERT or DELETE statements based on executed queries, and trigger should work on any table(no mather table structure). When I tried to write it with pl/psql I realized that this is simple not possible, and switched to "C".
Ok, It took me much more time to write trigger with "C", but once I did, there was no more need to write different function for each table.
If there is some difference in speed for regular functions/triggers, that difference is not significant(I'm speaking from my experience-someone else might think different.). When I say "regular" I mean symple checks and data manipulation.
So, I would say :chose the right tool for the task.
Regards !
----- Original Message -----
From: Clay LutherSent: Thursday, June 12, 2003 8:54 PMSubject: [GENERAL] Choosing Between PL/PGSQL or C/C++ for Triggers/Store ProcsFor 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?
2) Is there a fuzzy choice at work here? Perhaps PL/PGSQL for simple, obvious stored proces/triggers, but C/C++ for complexity?
Thanks.
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: > 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? > 2) Is there a fuzzy choice at work here? Perhaps PL/PGSQL for simple, > obvious stored proces/triggers, but C/C++ for complexity? > > Thanks. > >-- >Clay >claycle@cisco.com >
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.