Re: plpgsql error handling bug

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