Thread: Problems with trigger and function.

Hi all,

Id like to to function with plperlu which write's db-events to log-file.
My trigger looks like this:

CREATE TRIGGER trapd
     BEFORE INSERT OR UPDATE ON events FOR EACH ROW
     EXECUTE PROCEDURE write_trapd();

But now i have a problem how to write that event information for specific
file. I dont want to write whole event, only part offit.

Does anyone have suggestion how to do this?

My goal is to read that log file (eg. tail) and when there comes new event
some other programs executes some other programs.

Best regards,

Name: Ville Leinonen
E-mail: ville.leinonen@megata.vu

On Mon, Jun 13, 2005 at 02:15:17PM +0300, Ville Leinonen wrote:
>
> Id like to to function with plperlu which write's db-events to log-file.

What version of PostgreSQL are you using?  PL/Perl trigger functions
are supported only in 8.0 and later.

> My trigger looks like this:
>
> CREATE TRIGGER trapd
>      BEFORE INSERT OR UPDATE ON events FOR EACH ROW
>      EXECUTE PROCEDURE write_trapd();
>
> But now i have a problem how to write that event information for specific
> file. I dont want to write whole event, only part offit.

It's not clear what you're having trouble with.  Getting the event
info?  If so then see the documentation for the $_TD hash reference:

http://www.postgresql.org/docs/8.0/static/plperl-triggers.html

If something else then please be more specific.

> My goal is to read that log file (eg. tail) and when there comes new event
> some other programs executes some other programs.

That might not be the best design because the log file won't have
transaction semantics.  Suppose you update the table and write a
message to the log file, then some other program reads the log file
and does something based on the update, then you roll back the
update.  Now you've done something based on an update that effectively
didn't happen.  You might wish to consider using NOTIFY and LISTEN
if you want to do event notification.

http://www.postgresql.org/docs/8.0/static/sql-notify.html
http://www.postgresql.org/docs/8.0/static/sql-listen.html
http://www.postgresql.org/docs/8.0/static/libpq-notify.html

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

"Ville Leinonen" <ville.leinonen@megata.vu> writes:
> Id like to to function with plperlu which write's db-events to log-file.

You want the trigger to write something to a file outside the database?
That is a really, really awful idea, and the reason is simple: if the
transaction rolls back after your trigger runs, then the table insertion
or update is cancelled, but there is no way to undo the addition to the
log file.  So whatever reads the log file *will do the wrong thing*.

A better way to design this sort of thing is to have the trigger issue
a NOTIFY indicating that something (probably) happened to the events
table, and then have a background client that is always listening for
that notify event.  When it gets one, it looks at the events table to
see what happened, and then does the appropriate outside-the-database
action.  This avoids ever doing anything in response to uncommitted
updates.

The only tricky part of this is setting things up so that the listener
can easily find what changed.  The common solution is for the
insert/update trigger to store a new value into a serial column, eg
    new.lastchange = nextval('lastchange_seq');
If this column is indexed then it's cheap for the listener to find
recently-changed rows.  Or if you're willing to incur an extra update,
it's even easier: use a boolean "recentlychanged" column that is set
true by the trigger, and false by the listener after it's processed the
row.  A partial index "where recentlychanged" makes it easy for the
listener to find the rows.  (This is probably the safest way, since it
avoids any issues with transactions committing in an order different
from their sequence number assignments.)

You can find lots of discussion about this sort of thing in the PG list
archives --- try searching for threads that mention listen/notify.

            regards, tom lane