plpgsql error handling bug

Hello.

I found a bug in the behaviour of plpgsql error handling system
while trying to handle foreign key violation exception.

When this error occured, control doesn't jump to exception handling block.
It moves to the next statement instead. When control leaves the
function exception is occured. So it's impossible to handle this kind of
exception.

Furthermore, the FOUND local variable is set to true after insert
statement which raises foreign key violation and
GET DIAGNOSTICS var = ROW_COUNT set var to 1.

Attached file contains sample that shows this bug.
Execute fk_violation_bug, fk_violation_bug2 and fk_violation_bug3
plpgsql functions to see this error.

---------
I'm using

"PostgreSQL 8.0.0beta1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.2.3 (mingw special 20030504-1)"

on

OS Name Microsoft Windows XP Professional
Version 5.1.2600 Service Pack 2 Build 2600

Thank you.

--
Best regards,
Ivan mailto:Ivan-Sun1(at)mail(dot)ru

On Sun, 5 Sep 2004 Ivan-Sun1(at)mail(dot)ru wrote:

> I found a bug in the behaviour of plpgsql error handling system
> while trying to handle foreign key violation exception.
>
> When this error occured, control doesn't jump to exception handling block.
> It moves to the next statement instead. When control leaves the
> function exception is occured. So it's impossible to handle this kind of
> exception.

IIRC that's because the check is not happening until after the function is
finished (at the end of the user sent statement). If so, then yes,
currently there'd be no way to handle the exception.

Ivan-Sun1(at)mail(dot)ru writes:
> I found a bug in the behaviour of plpgsql error handling system
> while trying to handle foreign key violation exception.

This is not a bug in the exception system.

The problem is that FK constraints are enforced by triggers that do not
fire until the end of the outer statement (that is, the SELECT that
calls the plpgsql function). So by the time the constraint error is
raised, we have long since exited the exception structure.

There has been some talk of changing trigger firing rules to make this
sort of thing behave more intuitively inside functions, but it hasn't
happened yet.

Maybe we should think about doing something about this for 8.0? It's a
larger behavioral change than I like to think about for post-beta, but
(a) the exception mechanism's usefulness is certainly going to be
severely limited if it can't catch FK errors; (b) 8.0 seems like a
more appropriate time to introduce backwards-incompatibilities than
future 8.x releases.

I would imagine that the right thing would be to fire pending
non-deferred triggers at the end of every SPI_exec/execp, not only at
the outer loop. Are there other places that it would be needed in
addition, or instead?

Comments?

regards, tom lane

Tom Lane wrote:

> Ivan-Sun1(at)mail(dot)ru writes:
>
>>I found a bug in the behaviour of plpgsql error handling system
>>while trying to handle foreign key violation exception.
>
>
> This is not a bug in the exception system.
>
> The problem is that FK constraints are enforced by triggers that do not
> fire until the end of the outer statement (that is, the SELECT that
> calls the plpgsql function). So by the time the constraint error is
> raised, we have long since exited the exception structure.
>
> There has been some talk of changing trigger firing rules to make this
> sort of thing behave more intuitively inside functions, but it hasn't
> happened yet.
>
> Maybe we should think about doing something about this for 8.0? It's a
> larger behavioral change than I like to think about for post-beta, but
> (a) the exception mechanism's usefulness is certainly going to be
> severely limited if it can't catch FK errors; (b) 8.0 seems like a
> more appropriate time to introduce backwards-incompatibilities than
> future 8.x releases.

Considering also that if you fix the BUG #1231 then 8.0 have already some
backwards-incompatibilities so one more is mitigated by the other one.

Regards
Gaetano Mendola