2

In traditional programming there is an axiom that states, "do not use errors for flow control". A general example is to throw an error then catch the error instead of using an ordinary conditional statement or break statement. This is harmful because the application has to unwind the call stack and invoke some relatively expensive exception handling logic versus simply handling a conditional statement.

I am working with a postgres system where a user is calling a function in postgres that throws an error instead of returning no rows when a condition is not met. The condition is roughly a "this input value does not exist, nothing to do" as opposed to a truly exceptional case.

Does postgres incur runtime costs when an error is thrown in a similar manner traditional programming? In other words is using exceptions as flow control in postgres harmful to performance or just sloppy?

  • I assume you refer to plpgsql functions? And I assume, by "stored procedures" you mean "functions"? (But the same happens to apply to actual stored procedures as well.) – Erwin Brandstetter Mar 28 at 14:22
2

Can't tell, from the top of my head, the exact sequence of events in case of an exception. But I can tell you that it is comparatively expensive and should be avoided unless needed. An exception (error) causes the surrounding transaction to be rolled back, unless trapped.

But to trap an error, you need an EXCEPTION clause in a plpgsql block (in a function, procedure or a DO statement). And it is even expensive to have an EXCEPTION clause in a plpgsql function block to begin with (before any exceptions are raised), because such a block effectively forms a subtransaction (that can potentially be rolled back) incurring substantially more overhead. Consequently, the manual warns:

A block containing an EXCEPTION clause is significantly more expensive to enter and exit than a block without one. Therefore, don't use EXCEPTION without need.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.