Message-ID: <425B7920.4040002@archonet.com>
Date: Tue, 12 Apr 2005 08:30:40 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
MIME-Version: 1.0
To: Carlos Moreno <moreno@mochima.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Update aborted if trigger function fails?
References: <425ADE3F.1090206@mochima.com>
In-Reply-To: <425ADE3F.1090206@mochima.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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