a primer on trigger?

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, 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

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 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.

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, 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, 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:

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, 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

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 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, 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:

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 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, 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