Date: Tue, 12 Apr 2005 10:55:30 -0400
From: Carlos Moreno <moreno@mochima.com>
Subject: Re: Update aborted if trigger function fails?
In-reply-to: <425B7920.4040002@archonet.com>
To: pgsql-sql@postgresql.org
Message-id: <425BE162.6010501@mochima.com>
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii; format=flowed
Content-transfer-encoding: 7BIT
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7)
	Gecko/20040616
References: <425ADE3F.1090206@mochima.com> <425B7920.4040002@archonet.com>

Richard Huxton wrote:

>> 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?

Yes, you are absolutely correct.

I guess the concern came up as result of a particular
situation, in which failing to properly process the
trigger function is not that crucial (I wanted to
update some additional information that is "optional",
and that can be reconstructed easily after discovering
that the trigger function had been failing).  But in
our case, failing to complete the update is rather
critical (things can be reconstructed but under certain
conditions only, and only by temporarily shutting down
the system for a few minutes).


So, I was thinking that there may be a way for the user
to instruct PG to ignore the fact that the trigger
function failed -- that way, we would overcome the
difficulties that you mention in improving dependency
checking when functions are involved -- PG wouldn't have
to determine it:  the user would tell it.

> So - how do you deal with this? Well, you test. 

Fair enough.

Thanks!

Carlos
--