pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
how to make a trigger to copy files.
3+ messages / 3 participants
[nested] [flat]

* how to make a trigger to copy files.
@ 2005-01-23 18:47 Mitchell Laks <mlaks@verizon.net>
  2005-01-23 19:05 ` Re: how to make a trigger to copy files. Tom Lane <tgl@sss.pgh.pa.us>
  2005-01-23 19:35 ` Re: how to make a trigger to copy files. Michael Fuhr <mike@fuhr.org>
  0 siblings, 2 replies; 3+ messages in thread

From: Mitchell Laks @ 2005-01-23 18:47 UTC (permalink / raw)
  To: pgsql-admin

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



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

* Re: how to make a trigger to copy files.
  2005-01-23 18:47 how to make a trigger to copy files. Mitchell Laks <mlaks@verizon.net>
@ 2005-01-23 19:05 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 3+ messages in thread

From: Tom Lane @ 2005-01-23 19:05 UTC (permalink / raw)
  To: Mitchell Laks <mlaks@verizon.net>; +Cc: pgsql-admin

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



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

* Re: how to make a trigger to copy files.
  2005-01-23 18:47 how to make a trigger to copy files. Mitchell Laks <mlaks@verizon.net>
@ 2005-01-23 19:35 ` Michael Fuhr <mike@fuhr.org>
  1 sibling, 0 replies; 3+ messages in thread

From: Michael Fuhr @ 2005-01-23 19:35 UTC (permalink / raw)
  To: Mitchell Laks <mlaks@verizon.net>; +Cc: pgsql-admin

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/



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


end of thread, other threads:[~2005-01-23 19:35 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-23 18:47 how to make a trigger to copy files. Mitchell Laks <mlaks@verizon.net>
2005-01-23 19:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-01-23 19:35 ` Michael Fuhr <mike@fuhr.org>

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