pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Executing external program from stored procedure
4+ messages / 4 participants
[nested] [flat]

* Executing external program from stored procedure
@ 2007-10-05 02:02  Benjamin Krajmalnik <kraj@illumen.com>
  0 siblings, 2 replies; 4+ messages in thread

From: Benjamin Krajmalnik @ 2007-10-05 02:02 UTC (permalink / raw)
  To: pgsql-admin

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?
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.
 
Any suggestions are welcome :)
 
Regards,
 
Benjamin

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Executing external program from stored procedure
@ 2007-10-05 08:51  Dawid Kuroczko <qnex42@gmail.com>
  parent: Benjamin Krajmalnik <kraj@illumen.com>
  1 sibling, 1 reply; 4+ messages in thread

From: Dawid Kuroczko @ 2007-10-05 08:51 UTC (permalink / raw)
  To: pgsql-admin

On 10/5/07, Benjamin Krajmalnik <kraj@illumen.com> 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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Executing external program from stored procedure
@ 2007-10-05 09:53  Simon Riggs <simon@2ndquadrant.com>
  parent: Benjamin Krajmalnik <kraj@illumen.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Simon Riggs @ 2007-10-05 09:53 UTC (permalink / raw)
  To: Benjamin Krajmalnik <kraj@illumen.com>; +Cc: pgsql-admin

On Thu, 2007-10-04 at 20:02 -0600, Benjamin Krajmalnik wrote:
> Is there a way to execute an external program from within a stored
> procedure?

Write a Function in C

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Executing external program from stored procedure
@ 2007-10-05 15:02  Greg Sabino Mullane <greg@turnstep.com>
  parent: Dawid Kuroczko <qnex42@gmail.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Greg Sabino Mullane @ 2007-10-05 15:02 UTC (permalink / raw)
  To: pgsql-admin


-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> 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 .");

Two minor nits: it's not taint mode (that's something else entirely 
in Perl). Pl/Perl runns in "safe mode" via the Safe module:

http://search.cpan.org/~jhi/perl-5.8.0/ext/Opcode/Safe.pm

Also, allowing a bare system call is really the fault of the person 
writing the function, and one should not rely upon trusted languages 
to prevent that sort of thing. :)

> Other approach, I think a bit better, would be to devise a scheme like this:

The listen approach is a good one, I agree, especially if the actual call 
takes a finite amount of time and is called from a trigger, as your insert 
or update will have to wait for the external program to finish before 
returning.

There are other untrusted languages (e.g. tcl) you could also try out, 
but Pl/Perl is probably the most functional and best supported.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200710051101
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFHBlHIvJuQZxSWSsgRA2ysAJ9STQNEHtCe0MvJ911QUrHkXJ+JswCgsE0o
qUcGE9gXUbClwx3KsLAjHNE=
=g3wJ
-----END PGP SIGNATURE-----





^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2007-10-05 15:02 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-05 02:02 Executing external program from stored procedure Benjamin Krajmalnik <kraj@illumen.com>
2007-10-05 08:51 ` Dawid Kuroczko <qnex42@gmail.com>
2007-10-05 15:02   ` Greg Sabino Mullane <greg@turnstep.com>
2007-10-05 09:53 ` Simon Riggs <simon@2ndquadrant.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox