pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUpdate aborted if trigger function fails?
5+ messages / 3 participants
[nested] [flat]
* Update aborted if trigger function fails?
@ 2005-04-11 20:29 Carlos Moreno <moreno@mochima.com>
0 siblings, 1 reply; 5+ messages in thread
From: Carlos Moreno @ 2005-04-11 20:29 UTC (permalink / raw)
To: pgsql-sql
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
--
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Update aborted if trigger function fails?
@ 2005-04-12 07:30 Richard Huxton <dev@archonet.com>
parent: Carlos Moreno <moreno@mochima.com>
0 siblings, 1 reply; 5+ messages in thread
From: Richard Huxton @ 2005-04-12 07:30 UTC (permalink / raw)
To: Carlos Moreno <moreno@mochima.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Update aborted if trigger function fails?
@ 2005-04-12 14:55 Carlos Moreno <moreno@mochima.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 5+ messages in thread
From: Carlos Moreno @ 2005-04-12 14:55 UTC (permalink / raw)
To: pgsql-sql
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
--
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Update aborted if trigger function fails?
@ 2005-04-12 15:17 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Carlos Moreno <moreno@mochima.com>
0 siblings, 1 reply; 5+ messages in thread
From: Andrew Sullivan @ 2005-04-12 15:17 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Update aborted if trigger function fails?
@ 2005-04-12 15:47 Carlos Moreno <moreno@mochima.com>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 0 replies; 5+ messages in thread
From: Carlos Moreno @ 2005-04-12 15:47 UTC (permalink / raw)
To: pgsql-sql
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
--
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-04-12 15:47 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-11 20:29 Update aborted if trigger function fails? Carlos Moreno <moreno@mochima.com>
2005-04-12 07:30 ` Richard Huxton <dev@archonet.com>
2005-04-12 14:55 ` Carlos Moreno <moreno@mochima.com>
2005-04-12 15:17 ` Andrew Sullivan <ajs@crankycanuck.ca>
2005-04-12 15:47 ` Carlos Moreno <moreno@mochima.com>
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