Re: Update aborted if trigger function fails?

Carlos Moreno wrote:
>
> Hi,
>
> I just noticed this (odd?) behaviour, and it kind of
> scares me.

> Isn't this a little fragile? Is there something I
> could do to avoid this situation? Should trigger
> functions be extremely simple as to guarantee that
> an error would never happen?

There's nothing else it can do, really. Far better that the whole update
fails than you get an inconsistent database.

Imagine you have a banking system, and every time you add a row to the
transaction-history, you update the "current_balance" table. Which would
you prefer, both updates fail, or the two get out of sync?

Now, there is room for improved dependency checking, but functions pose
certain difficulties.
1. The body of the function is opaque to PostgreSQL - it's only plpgsql
that it handles itself. It knows nothing about Perl/Python/PHP/Java/C.
2. Functions can create queries from text - even if PG understood all
these languages, it couldn't determine which tables were accessed.

So - how do you deal with this? Well, you test. Ideally, you should have
a set of tests and re-run them to ensure all your functions work as desired.

--
Richard Huxton
Archonet Ltd