Thread: a primer on trigger?
What I want to do is "trigger" a system call when a table is modified in any way. But it appears that trigger idea in postgres is not capable. Can any one confirm if that is the case? What I want to do is run an external perl script which needs the newly modified row as input. My web server modifies the database which is on a different physical machine. Currently I'm doing it via a complicated mechanism of sending an email to an account at database machine, catching it using procmail and then perl script performing the action. Perl script needs the newly modified row as input and then it makes system call. Thanks in advance
On Thu, May 03, 2001 at 10:00:38PM -0400, newsreader@mediaone.net
wrote:
> What I want to do is "trigger" a system call when a table is
> modified in any way. But it appears that trigger idea in
> postgres is not capable. Can any one confirm if that is the
> case?
>
> What I want to do is run an external perl script which needs
> the newly modified row as input.
>
> My web server modifies the database which is on a different
> physical machine. Currently I'm doing it via a complicated
> mechanism of sending an email to an account at database
> machine, catching it using procmail and then perl script
> performing the action. Perl script needs the newly modified
> row as input and then it makes system call.
wow. convoluted. it must've taken some doing!
postgresql servers can accept tcp connections on certain ports,
just as a web server listens to port 80. so you can have a script
written on perl on this.machine.over.here that inserts and
selects and updates on a database located on that.box.there
with a minimum of fuss.
and then you'd already have the data in a perl script, soyou'd be
off to the races.
look in /etc/postgresql/* and in /usr/share/doc/postgresql-doc/*
for how to enable tcp connections, and make sure your postgresql
user accounts (in pg_shadow) are comfortably secure, and then
from another machine instead of
use DBI;
my $DSN = 'dbi:Pg:dbname=lotsofdata';
my $USER = 'onlyme';
my $PASS = 'stupidity';
my $dbh=DBI->connect($DSN,$USER,$PASS);
you'd change this, and you're done:
my $DSN = 'dbi:Pg:dbname=lotsofdata;host=ip.address.over.there';
see "man DBD::Pg" for the full poop.
dbi:Pg:dbname=$dbname;host=$host;port=$port;options=$options;tty=$tty
if you have perl doing the inserts to begin with, then you've
already got the information where you need it. have perl massage
or blend it, whatever pickles your tink.
--
don't visit this page. it's bad for you. take my expert word for it.
http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
On Thu, May 03, 2001 at 11:40:14PM -0500, will trillich wrote: > > postgresql servers can accept tcp connections on certain ports, > just as a web server listens to port 80. so you can have a script > written on perl on this.machine.over.here that inserts and > selects and updates on a database located on that.box.there > with a minimum of fuss. > I asked for primer on trigger and I get a primer on DBI. I know how to make tcp connection and DBI/DBD. What I am doing which I did not tell you explicitly was that I can directly modify datbase from another box. What I want is to make a system on the database box whenever some table is modified.
On Thu, 3 May 2001 newsreader@mediaone.net wrote: > What I want to do is "trigger" a > system call when a table is modified > in any way. But it appears > that trigger idea in postgres > is not capable. Can any one > confirm if that is the case? I believe you can make syscalls if you write a trigger in C (rather than one of the procedural languages). However (and it's a big one), you should only ever consider modifying something outside of the database from a trigger if it's something that is absolutely safe to do if the transaction rolls back.
On Fri, May 04, 2001 at 09:16:12AM -0700, Stephan Szabo wrote: > > I believe you can make syscalls if you > write a trigger in C (rather than one > of the procedural languages). > > However (and it's a big one), you should > only ever consider modifying something outside > of the database from a trigger if it's > something that is absolutely safe to do > if the transaction rolls back. Thanks for the suggestion. I will investigate further along the line of writing the trigger in C. But at this point I am leaning towards sticking to my current routine which has worked flawlessly for many months. The reason I need some 'trigger' is it's a huge mess to maintain when it does come to the time to maintain. Right now I'm moving this scheme to a different set of hardware and it's quite a nightmare to juggle so many programs at once while keeping everything running. It _is_ quite trivial to write a safe perl trigger especially because I control both client and server end of postgres. With C it will be whole new game for me.
On Fri, 4 May 2001 newsreader@mediaone.net wrote: > It _is_ quite trivial to write a safe perl > trigger especially because I control both client > and server end of postgres. With C it > will be whole new game for me. What is the system call? Could you do this in pl/tclu, the untrusted version of pl/tcl? -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
On Fri, 4 May 2001, Stephan Szabo wrote: > However (and it's a big one), you should > only ever consider modifying something outside > of the database from a trigger if it's > something that is absolutely safe to do > if the transaction rolls back. Hmmm... this raises an interesting question. Would it be possible to hook into (via trigger or other mechanism) so that we could execute a function on commit? There are PG triggers to do things like send email, etc., which, yes, can't be undone if the transaction ultimately fails. Instead, could we: 1) have a trigger that (in this case) instead of sending email, just adds the information to an 'email-to-send' table. 2) commit the transaction 3) the trigger TRANSACTION_AFTER is called, and from that, we can scan the table, and actually do some of these system calls? Can this be done? Is this terrible design? Is there any other reasonable way to handle things like this? -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
Joel Burton <jburton@scw.org> writes:
> 3) the trigger TRANSACTION_AFTER is called, and from that, we can scan the
> table, and actually do some of these system calls?
The "deferred trigger" (AFTER trigger) mechanism already does that, ie,
let you get control just before commit. However, it's no solution to
the rollback problem. What if you have several deferred triggers and
one of the later ones fails (thereby aborting the transaction)? You
already did the unreversible outside-the-database operation...
regards, tom lane
On Fri, 4 May 2001, Stephan Szabo wrote: > > Can this be done? Is this terrible design? Is there any other reasonable > > way to handle things like this? > > Probably could be done, but the question would be what happens if the > TRANSACTION_AFTER raises an error condition for whatever reason? You > can't go back and un-commit the transaction. > > For that case above, you'd probably be better off having something in cron > or whatever looking at your email-to-send table since it'll get only those > things that have already committed. You could put all the logic in a > function on the server still. Yep, you wouldn't be able to raise a meaningful error at this point. Comes with the territory. Cron job scanning a table would work, and is easy to set up. I have a personal history of moving things like databases, and not always moving cron jobs with them. It's nice to have solutions stay somewhat contained. -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
On Fri, 4 May 2001, Joel Burton wrote: > Hmmm... this raises an interesting question. > > Would it be possible to hook into (via trigger or other mechanism) so that > we could execute a function on commit? There are PG triggers to do things > like send email, etc., which, yes, can't be undone if the transaction > ultimately fails. Instead, could we: > > 1) have a trigger that (in this case) instead of sending email, just adds > the information to an 'email-to-send' table. > > 2) commit the transaction > > 3) the trigger TRANSACTION_AFTER is called, and from that, we can scan the > table, and actually do some of these system calls? > > Can this be done? Is this terrible design? Is there any other reasonable > way to handle things like this? Probably could be done, but the question would be what happens if the TRANSACTION_AFTER raises an error condition for whatever reason? You can't go back and un-commit the transaction. For that case above, you'd probably be better off having something in cron or whatever looking at your email-to-send table since it'll get only those things that have already committed. You could put all the logic in a function on the server still.
On Fri, 4 May 2001, Joel Burton wrote: > On Fri, 4 May 2001, Stephan Szabo wrote: > > > > Can this be done? Is this terrible design? Is there any other reasonable > > > way to handle things like this? > > > > Probably could be done, but the question would be what happens if the > > TRANSACTION_AFTER raises an error condition for whatever reason? You > > can't go back and un-commit the transaction. > > > > For that case above, you'd probably be better off having something in cron > > or whatever looking at your email-to-send table since it'll get only those > > things that have already committed. You could put all the logic in a > > function on the server still. > > Yep, you wouldn't be able to raise a meaningful error at this point. > Comes with the territory. The problem is that you have the problem in reverse then, unless what's done by the trigger is "optional" sortof. What happens if there is an error, and you can't do the outside operation? You once again end up out of sync with the database, but this time the outside thing is missing the records. You still need something to come in every so often and make sure stuff is correct. This whole class of things is just ugly.
On Fri, 4 May 2001 newsreader@mediaone.net wrote: > It _is_ quite trivial to write a safe perl > trigger especially because I control both client > and server end of postgres. With C it > will be whole new game for me. I didn't quite follow the original explanation of what you're doing, but the safeness thing was more: You insert row (1,1,1) into table Your trigger goes off, does something that doesn't affect this database (say like writing a file with 1,1,1 or modifying another database) You rollback or an error occurs. The results of your trigger persist, but the data isn't in the database anymore (well it is, but marked dead). If you're expecting that the outside thing is an accurate representation of the db, you're in trouble.
On Fri, May 04, 2001 at 12:59:54PM -0700, Stephan Szabo wrote:
> The problem is that you have the problem in reverse then, unless what's
> done by the trigger is "optional" sortof. What happens if there is an
> error, and you can't do the outside operation? You once again end up
> out of sync with the database, but this time the outside thing is missing
> the records. You still need something to come in every so often and make
> sure stuff is correct.
>
> This whole class of things is just ugly.
I'm a little weak on database theory, but isn't this what two-phase
commit is for?
Chris
--
chris@mt.sri.com -----------------------------------------------------
Chris Jones SRI International, Inc.
www.sri.com
Attachment
On Fri, 4 May 2001, Chris Jones wrote: > On Fri, May 04, 2001 at 12:59:54PM -0700, Stephan Szabo wrote: > > > The problem is that you have the problem in reverse then, unless what's > > done by the trigger is "optional" sortof. What happens if there is an > > error, and you can't do the outside operation? You once again end up > > out of sync with the database, but this time the outside thing is missing > > the records. You still need something to come in every so often and make > > sure stuff is correct. > > > > This whole class of things is just ugly. > > I'm a little weak on database theory, but isn't this what two-phase > commit is for? I'm very weak on database theory, but the problem here is trying to interface to external things. With an after commit trigger, you can know that the rows are committed safely, but what happens if your external interface fails. Even if you can rollback the database changes, what about previous external changes that were made assuming that we were successful. For example: transaction adds three rows transaction commits post-trigger sends mail 1 post-trigger sends mail 2 post-trigger attempts to send mail 3, but for some reason it fails. <Now we want to uncommit the rows. But we also want to unsend the 2 mails to keep everything in the same state>
On Fri, May 04, 2001 at 12:47:02PM -0400, Joel Burton wrote: > > Hmmm... this raises an interesting question. > > Would it be possible to hook into (via trigger or other mechanism) so that > we could execute a function on commit? There are PG triggers to do things > like send email, etc., which, yes, can't be undone if the transaction Could you kindly point me a reference to this 'trigger that emails'? I just want to see how it's done and see if I can modify it to my need. Thanks
On Fri, May 04, 2001 at 12:48:24PM -0400, Joel Burton wrote: > > What is the system call? Could you do this in pl/tclu, the > untrusted version of pl/tcl? > I don't know anything about pl/tcl and all that stuff. I will take a look later. But because I'm pressed for time I will go with my existing stuff
On Fri, May 04, 2001 at 12:48:24PM -0400, Joel Burton wrote:
> On Fri, 4 May 2001 newsreader@mediaone.net wrote:
>
> What is the system call? Could you do this in pl/tclu, the
> untrusted version of pl/tcl?
>
"system" call is a perl script which does many things
including the following
1. check the data for validity
2. write a text file
3. submit an "at" job with that text file
as input. The time that "at"
job runs is also part of the
incoming data in the email.
4. capture "at" job number
5. upload the data to postgres including
"at" job number.
Sometime an email will come in to cancel
this "at" job. In which case a different
perl script is called to look up "at"
job number and then calls "atrm" to kill
that job.
On Fri, May 04, 2001 at 10:57:09AM -0700, Stephan Szabo wrote: > You rollback or an error occurs. > The results of your trigger persist, but > the data isn't in the database anymore (well > it is, but marked dead). If you're expecting > that the outside thing is an accurate representation > of the db, you're in trouble. > I can tolerate such troubles now and then as the stuff I store are not that important.
On Fri, 4 May 2001 newsreader@mediaone.net wrote: > On Fri, May 04, 2001 at 12:47:02PM -0400, Joel Burton wrote: > > > > Hmmm... this raises an interesting question. > > > > Would it be possible to hook into (via trigger or other mechanism) so that > > we could execute a function on commit? There are PG triggers to do things > > like send email, etc., which, yes, can't be undone if the transaction > > Could you kindly point me a reference > to this 'trigger that emails'? I just > want to see how it's done and see if > I can modify it to my need. Look at the pgMail message posted yesterday on the list. -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
On Fri, 4 May 2001 newsreader@mediaone.net wrote: > On Fri, May 04, 2001 at 12:48:24PM -0400, Joel Burton wrote: > > > > What is the system call? Could you do this in pl/tclu, the > > untrusted version of pl/tcl? > > > > I don't know anything about pl/tcl and all > that stuff. I will take a look later. But > because I'm pressed for time I will go > with my existing stuff If you've never used TCL, you're gonna hate it, I fear. (Apologies to the tcl fans, but it's synax can be rather weird to people weaned on perl, IMHO) Hopefully, one day, pl/perl will be a full PL, with support for much more stuff. pl/python, in beta, has these features, so perhaps pl/perl, out of competition or spite, will pull ahead. :-) -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
Joel Burton <jburton@scw.org> writes:
> If you've never used TCL, you're gonna hate it, I fear.
> (Apologies to the tcl fans, but it's synax can be rather
> weird to people weaned on perl, IMHO)
ROTFL ...
A Perl lover has *no* standing to call other languages' syntax "weird".
regards, tom lane
Stephan Szabo wrote:
>
> On Thu, 3 May 2001 newsreader@mediaone.net wrote:
>
> > What I want to do is "trigger" a
> > system call when a table is modified
> > in any way. But it appears
> > that trigger idea in postgres
> > is not capable. Can any one
> > confirm if that is the case?
>
> I believe you can make syscalls if you
> write a trigger in C (rather than one
> of the procedural languages).
C for sure, but PL/TclU can do it too.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com