Thread: Supported plpgsql BEFORE ... EACH ROW behavior

Hi,

I want to write a plpgsql function for use as a
BEFORE ... EACH ROW function.  I want to modify
other tables even when the function returns NULL
and therefore the table on which the BEFORE
trigger is defined is not updated.

Can I count on this behavior being supported
in the future?  There's nothing in the docs,
or my past experience with Postgresql that
would lead me to believe that the existing
behavior would change, but then the doc's
don't explicitly say what happens to side
effects produced by a BEFORE trigger when
the code returns NULL.

Sorry for being paranoid about this but I
want to double check before relying on
behavior that few people probably use.

Thanks for the help.

Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein

Karl O. Pinc wrote:
> Hi,
>
> I want to write a plpgsql function for use as a
> BEFORE ... EACH ROW function.  I want to modify
> other tables even when the function returns NULL
> and therefore the table on which the BEFORE
> trigger is defined is not updated.

> Sorry for being paranoid about this but I
> want to double check before relying on
> behavior that few people probably use.

I think it's fairly common, actually. Returning NULL is cancelling the
update *of that row* rather than aborting the transaction, so all
side-effects should always survive.

Otherwise you couldn't update 100 rows and just skip one or two by
returning NULL from a before trigger.

--
   Richard Huxton
   Archonet Ltd

On 02/23/2007 02:03:25 AM, Richard Huxton wrote:
> Karl O. Pinc wrote:

>> I want to write a plpgsql function for use as a
>> BEFORE ... EACH ROW function.  I want to modify
>> other tables even when the function returns NULL
>> and therefore the table on which the BEFORE
>> trigger is defined is not updated.

> I think it's fairly common, actually. Returning NULL is cancelling
> the update *of that row* rather than aborting the transaction, so all
> side-effects should always survive.

Thats good news.  Thanks for the reply.  But...

> Otherwise you couldn't update 100 rows and just skip one or two by
> returning NULL from a before trigger.

But wanting side effects _when_ those one or two rows are skipped
is probably not so common.  I could imagine a implimentation
of Postgresql that does a SAVEPOINT before executing
a BEFORE ... EACH ROW trigger and then decides whether or
not to ROLLBACK TO SAVEPOINT or RELEASE SAVEPOINT.
That would not break the usage case you give, but would
break what I want to do.  So this is what I'm wanting
assurance about.  I suppose this is kind of silly, seeing
as how it's a BEFORE trigger we're talking about the
db would not have been updated so a SAVEPOINT would
not really be appropriate.  But I did say I was
being paranoid.

Thanks again for the help.

Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein