Обсуждение: how to make a trigger to copy files.

Hi.

I have a postgresql database backed application. As soon as the application
stores a file on my server, it creates a entry in a table LOCATION_TABLE
which stores the file location in the table in my postgresql database (eg. in
LOCATION_TABLE in database MASTER).

I have read about triggers and wondered if I could use them.
I would like my server to immediately "scp" the file to another machine, as
soon as the application updates the table with the new entry. (right now  i
have a cron job that runs a perl script that queries the   database for new
entries every 5 minutes, but i would like to have the new file (whose
location is stored in the new LOCATION_TABLE entry) to be "scp"'d
immediately, not after 5 minutes. Also a trigger seems more elegant...

But when i look for sample triggers on the web they seem to only do things
internal to the database (change field entries etc, check them for business
logic rules etc).

I am running postgresql 7.4.6 on debian sarge. I am most familiar with perl.
Could i create a database trigger to run a script like
system("scp $filename $destination");
where $filename is extracted from the entry just made into the database, and
$destination is my other pc?

Thanks,
Mitchell Laks

Mitchell Laks <mlaks@verizon.net> writes:
> I would like my server to immediately "scp" the file to another machine, as
> soon as the application updates the table with the new entry.

> But when i look for sample triggers on the web they seem to only do things
> internal to the database (change field entries etc, check them for business
> logic rules etc).

That's generally a good design rule, for the simple reason that a
trigger can never know whether the transaction will roll back after
it executes.  If you change state outside the database then that state
may become inconsistent with the database.  However, if you can restrict
your operations to cases where that won't hurt much, you may be able to
get away with it.

            regards, tom lane

On Sun, Jan 23, 2005 at 01:47:53PM -0500, Mitchell Laks wrote:

> Could i create a database trigger to run a script like
> system("scp $filename $destination");
> where $filename is extracted from the entry just made into the database, and
> $destination is my other pc?

You can use procedural languages like PL/Perl, PL/Python, PL/Tcl,
etc., to do just about anything those languages allow.  Whether
it's appropriate for the database to do certain things is another
matter....

I don't think PostgreSQL 7.4.6 supports trigger functions written
in PL/Perl, but you could write the file-copying function in PL/Perl
and call it from a PL/pgSQL trigger.  Or you could upgrade to 8.0.0,
which does support PL/Perl triggers, or you could write the trigger
in one of the other procedural languages.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/