Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 6DEBF9FB89B for ; Fri, 5 Oct 2007 05:51:19 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 21137-07 for ; Fri, 5 Oct 2007 05:51:10 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from fk-out-0910.google.com (fk-out-0910.google.com [209.85.128.187]) by postgresql.org (Postfix) with ESMTP id 3ED089FB85E for ; Fri, 5 Oct 2007 05:51:12 -0300 (ADT) Received: by fk-out-0910.google.com with SMTP id b27so435275fka for ; Fri, 05 Oct 2007 01:51:10 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=VZnmj4rQzIqg1TplZwfyMAWC2mphohPbsa5d7ZSTgeg=; b=mK/o4k1hBt7itnmaYbH9r37KL9yDMSLJsIQqUy00bdH6ne4RKU3GrG98KQSJbGixQ0B4T/MbgWhqqv+WYixgLx8PXigooF6zGM6Kaik6mXG69KXQ5VPfp+wkvD0AGUdyvo7rymt2Zl44T8X5jGMkQMUnAhpNVj5Hnuqn3LuFQZk= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=hZuTtpQSsEEQsE0otyn1upBmjidiW3tUk2z8bztivtcwLXJgBYcRc9OCfczYWNfROUnIMJKS/NpTsG+Mkq+uaMfosFadANEhf5cT5orGJ826vT9Vpw+5m7OHWyZRb4LHNzDGDhdeVItrekWGrtWstluYI+cOehnZEuILHvUJhqU= Received: by 10.82.119.17 with SMTP id r17mr16834364buc.1191574269538; Fri, 05 Oct 2007 01:51:09 -0700 (PDT) Received: by 10.82.170.9 with HTTP; Fri, 5 Oct 2007 01:51:09 -0700 (PDT) Message-ID: <758d5e7f0710050151v23e30a58yb89d8e1e4e533153@mail.gmail.com> Date: Fri, 5 Oct 2007 10:51:09 +0200 From: "Dawid Kuroczko" To: "pgsql-admin@postgresql.org" Subject: Re: Executing external program from stored procedure In-Reply-To: <758d5e7f0710050149m3c0da31eh717b117e5632c04d@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <758d5e7f0710050149m3c0da31eh717b117e5632c04d@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/23 X-Sequence-Number: 26937 On 10/5/07, Benjamin Krajmalnik wrote: > > > Is there a way to execute an external program from within a stored > procedure? > In pl/pgsql it does not appear to be supported. > Will pl/perl allow me to do so? PL/PerlU ^ -- 'U' is for unsafe. PL/Perl runs in tainted mode, so it will prevent you from accessing any files (which means you can relatively safely give an access to it for ordinary users and not worry too much about them doing system("rm -rf ."); PL/PerlU is unsafe meaning that these safety checks are turned off, and you could do anything you like -- including rm -rf. :) > A little background. I have a network monitoring platform which we > developed. The collector has the capability of issuing a single query for > each test it performs. We hae a massive stored procedure which aggreates > data in real time and then stores the individual test data in the database. > This has some great pluses, in which we can graph averages of various tests, > which cannot be done with something like rrdtool. On the downside, while > relatively fast, it is not as fast in rendering a graph as rrdtool. > > The stored procedure is written in pl/pgsql. I would like, for each test, > to call rrdtool and insert a reading in the tests rrd database. This will > allow us to render certain graphs much quicker. > > Is there a way in pl/perl to call an outside program? IN this case I would > have out pl/pgsql procedure call a pl/perl p[rocedure, passing the relevant > arguments, and have it call rrdtool to insert the data point. Other approach, I think a bit better, would be to devise a scheme like this: 1) Your stored procedure works as it does now (PL/PgSQL), 2) but at the end it puts relevat information somewhere (a "queue table"?) and calls NOTIFY rrdgraph; 3) You write a simple daemon (in perl, I think), which LISTEN rrdgraph and upon receiving notification it reads info from queue table and performs graphing. See: http://www.postgresql.org/docs/8.2/static/sql-listen.html Regards, Dawid