0

I have a PostgreSQL-powered web app that does some non-essential, simple calculations involving getting values from outside sources, multiplication and division for reporting purposes. Today an error where a multiplication that exceeded the value domain of a numeric( 10, 4 ) field led to an application crash. It would be much better if the relevant field had just been set to null and a notice be generated. The way the bug worked was that a wrong value in one field caused several views to become unavailable, and while a missing value in that place would have been sad but no big problem, the blocked view is still essential for the app to work.

Now I'm aware that in this particular case, setting that field to numeric( 11, 4 ) would have prevented the bailout, but that is, of course, only postponing the issue at hand. Since the error happened in a function call, I could also have written an exception handler; lastly, one could check either the multiplicands or the result for sane values (but that is in itself a little strange as I would either have to do a guess based on magnitudes or else do the multiplication in another numeric type that can probably handle a value whose magnitude is in principle not known to me with certainty, because external sources).

Exception handling is probably what this will boil down to, which, however, entails that all numeric calculations will have to be done via PL/pgSQL function calls, and will have to be implemented in many different places. None of the options seems particularly maintainable or elegant. So the question is: Can I somehow configure PostgreSQL to ignore some or all arithmetic errors and use default values in such cases? If so, can that be done per database or will I have to configure the server? If this is impossible or a Bad Idea, what are the best practices to avoid arithmetic errors?

Clarification This is not a question about how to rewrite numeric( 10, 4 ) so that the field can hold values of 1e6 and above, and also not so much about error handling in the application that uses the DB. It's more about whether there is an operator, a function call, a general configuration or a general pattern that is most commonly recommended to deal with situations where a (non-essential) computation normally results in a number (or in fact other value type) except with some inputs that cause exceptions, which is when the result could fully well and safely be discarded. Think Excel printing out #### when cell is too narrow for the digits to be displayed, or JavaScript giving you NaN in place of arithmetic errors. Returning null instead of raising an exception may be a bad idea in general programming but legitimate in specific case.

Observe that PostGreSQL error codes does have e.g. invalid_argument_for_logarithm, invalid_argument_for_ntile_function, division_by_zero all grouped together under Class 22 — Data Exception and does allow exception handling in function bodies, so I can also specifically ask: How to catch all class 22 exceptions short of listing all the error codes?, but then I still hope for a more principled approach.

Improve this question
4
  • Look like the numeric range can be huge. Are you sure you only want 9 or 10 digit left of the decimal point? Why not set it it to 32,4 or something? Is there any reasonable domain for input values? postgresql.org/docs/10/static/datatype-numeric.html Commented Jan 16, 2018 at 23:59
  • you could say it's about securing the operation of the DB in principle and explicitly. Like I said expanding the range is possible but unsatisfactory. BTW setting numeric(10,4) leaves you with 6 places left of the point, not ten, but even setting it to 32 means only waiting for the moment when that measure is exceeded. The 'reasonable domain' approach works as long as you both check all inputs (good idea anyway) and have reasonably simple maths to deal with; throw in a single exponential function and checking input domains becomes a wacky and erroneous process WRT bounding your outputs. Commented Jan 17, 2018 at 1:27
  • 1
    I think this question invites opinion-based answers. Commented Jan 17, 2018 at 8:21
  • C'mon, "what are best practices in environment X to deal with errors of class Y' is a genuine programming question. I didn't ask 'what ways to deal with errors do you like most'. Commented Jan 17, 2018 at 9:10

1 Answer 1

Reset to default
1

Arguably the type numeric (without type modifiers) would be the right thing for you if you want to avoid overflows (that's what you seem to mean with “arithmetic error”) as much as possible.
However, there will still be the possibility of value overflows numeric format.

There is no way to configure PostgreSQL so that it ignores a numeric overflow.

If the result of an operation cannot be represented in a data type, there should be an error. If the data supplied by the application can lead to an error, the application should be ready to handle such an error rather than “crash”. Failure to do so is an application bug.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks for your consideration. 'Arithmetic error' also covers division by zero and like errors; come to think of it, it may be hard to delineate and clearly separate these from formulation errors. "...should be an error ... ready to handle rather than crash"—I almost 100% agree, except the application should perhaps not just proceed on any kind of error, so you'll have to distinguish error types, and the fewer error types come from the DB, the easier that gets.
Also, regarding "If the result of an operation cannot be represented in a data type, there should be an error": I believe this really depends on the case. If I can divide by null to get a null result (which may in turn be made illegal by some column definition and lead to an exception), then why should I not make it so that the (mathematically speaking more reasonable operation) 1 / 0 results in `null´, too? Also, I'm afraid you are conflating 'error' to mean both 'a value indicating failure to calculate' and 'exception thrown breaking control flow'. I think both are legitimate ways to go.
The NULL case is different. If you divide 1 by an unknown number the result is unknown, and that's for sure. Whereas 1 / 0 is clearly an error. To be more precise about "error": Maybe I should say that a numeric overflow is an error condition that should lead to an exception that should be handled by the application. It would be a viable option to set a variable to a "not a number" value on arithmetic errors, but that's not the way that PostgreSQL does it.
I agree but "...leads to an exception that should be handled by the application" is too dogmatic I'm afraid. Sometimes there is no application to do error handling, and sometimes out-of-bounds and illegal operations can be considered 'just data' and shouldn't cause exception handling beyond (cascading) 'missing' / 'marked' output fields. I'm asking for a general method to organize error condition handling within PostGreSQL without involving an external application; I'm not asking for a general discussion on the merits of 'always throwing on any error', altho that's interesting too.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.