Re: Execute Shell script after insert

On Mon, Oct 27, 2008 at 04:16:16PM -0400, Tom Lane wrote:
> Sam Mason <sam(at)samason(dot)me(dot)uk> writes:
> > 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.

Yes, that's probably better.

As a meta-comment, doing things in a stored procedure is still subject
to rollback (albeit with *many* less provisos) and hence there's still a
possibility of things getting out of step with each other. In general,
multiple failure domains (i.e. more than one) are difficult however
they are arranged. This is mainly why databases are nice, you can
normally just hide away in your own little world and pretend that there
is only one failure domain and, as an added bonus, it's even got nice
transactional semantics.

Sam