From: Richard Huxton <dev@archonet.com>
To: Carlos Moreno <moreno@mochima.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Update aborted if trigger function fails?
Date: Tue, 12 Apr 2005 08:30:40 +0100
Message-ID: <425B7920.4040002@archonet.com> (raw)
In-Reply-To: <425ADE3F.1090206@mochima.com>
References: <425ADE3F.1090206@mochima.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, moreno@mochima.com
Subject: Re: Update aborted if trigger function fails?
In-Reply-To: <425B7920.4040002@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox