Re: Problems with trigger and function.

"Ville Leinonen" <ville(dot)leinonen(at)megata(dot)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