Thread: Update aborted if trigger function fails?

Hi,

I just noticed this (odd?) behaviour, and it kind of
scares me.

For testing purposes, I put a deliberate syntax error;
this wouldn't happen in a real-life situation.  But what
if the error gets triggered by something that happens
later on?  say, if the trigger function uses a field
that later on gets dropped from the table -- something
that was working fine all of a sudden produces syntax
errors while executing the function

(I just verified, to see if the server would let me
drop a column that is being referenced by a function
invoked by an active trigger, and it did let me drop
it).

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?

Thanks,

Carlos
--

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

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

On Tue, Apr 12, 2005 at 10:55:30AM -0400, Carlos Moreno wrote:
> 
> 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

If you can do some things asynchronously, and you don't care about
them very much, then you can use LISTEN/NOTIFY to do such processing.

A
-- 
Andrew Sullivan  | ajs@crankycanuck.ca
Information security isn't a technological problem.  It's an economics
problem.    --Bruce Schneier

Andrew Sullivan wrote:
> On Tue, Apr 12, 2005 at 10:55:30AM -0400, Carlos Moreno wrote:
> 
>>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
> 
> 
> If you can do some things asynchronously, and you don't care about
> them very much, then you can use LISTEN/NOTIFY to do such processing.

Thanks Andrew for the suggestion -- this may as well be
exactly the feature that I needed to begin with.  I'm
not familiar with the details, but I'll go right away
to dig through the docs.

Thanks!

Carlos
--