Re: Execute Shell script after insert

Sam Mason <sam(at)samason(dot)me(dot)uk> writes:
> On Mon, Oct 27, 2008 at 03:09:31PM -0300, Anderson dos Santos Donda wrote:
>> Is there a way to execute a simple shell script in server after execute
>> INSERT INTO ?

> Yes; as other people have said most of the procedural languages allow
> you to run code outside PG. You'd just need to hook this procedure up
> to a trigger inside postgres that ran when data was inserted.

> As a rule, though, I'd tend not to do this. The rationale being,
> when something goes wrong (as code inevitably does) the database
> will continue doing things automatically for you (like touching the
> filesystem) when you're fighting against it trying to fix things.

The usual explanation of why this is a bad idea is that if the
transaction aborts at some point after running the trigger, then the
INSERT effectively didn't happen --- but the trigger's
outside-the-database effects still happened, and there's no way to cause
them to roll back. So you will inevitably end up with the database
being out of sync with whatever you're trying to use the trigger to update.

regards, tom lane