Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 34F7B477031 for ; Wed, 22 Jan 2003 14:22:14 -0500 (EST) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id 725094770AD for ; Wed, 22 Jan 2003 13:55:02 -0500 (EST) Received: from [66.219.92.2] (HELO chocolate-mousse) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 2322107; Wed, 22 Jan 2003 10:55:00 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: Ludwig Lim , PostgreSQL Mailing List Subject: Re: Performance between triggers/functions written in C and PL/PGSQL Date: Wed, 22 Jan 2003 10:57:29 -0800 X-Mailer: KMail [version 1.4] References: <20030122025039.18791.qmail@web80302.mail.yahoo.com> In-Reply-To: <20030122025039.18791.qmail@web80302.mail.yahoo.com> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200301221057.29207.josh@agliodbs.com> X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/226 X-Sequence-Number: 873 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 m= any=20 times per minute, about 20:1 in favor of C triggers. Partly that depends o= n=20 whether you load the C function as an external file or compile it into the= =20 database. The latter is, of course, faster by far less flexible. Partly this is because C is fast, being a lower-level language. Partly thi= s=20 is because the PL/pgSQL parser is in *desperate* need of an overhaul, as it= =20 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= =20 function can easily crash Postgres, whereas that's much harder to do with= =20 PL/pgSQL. Usually I just write the original Trigger in PL/pgSQL, test & debug for dat= a=20 errors, and then farm it out to a crack C programmer to convert to C. --=20 -Josh Berkus Aglio Database Solutions San Francisco