Re: Trapping errors

Hello list,

I have a table with double precision columns and update
queries which multiply and divide these values. I am
wondering if it's possible to catch overflow and underflow
errors to set the column to 0 in the case of an underflow
and a large value in the case of an overflow.

Currently, I have an exception handler in a PLPGSQL
ufunction that sort of does this.

begin
update tbl set score = score/s
exception when numeric_value_out_of range then
update tbl set score=0
where cast(score/s as numeric) < 1e-200
end;

But this is messy since the exception needs to rescan the
entire table if even one row fails the update. Is there a
better way to do this?

Best,
Shane

UPDATE tbl SET score = divide_double_default(score, s, 1e-200) ...
UPDATE tbl SET score = multiply_double_default(score, s, 999999999) ...

Code the divide_double_default/multiply_double_default functions with error handling that will return the desired value (either zero or the supplied parameter) if an exception is thrown; probably with a WARNING/NOTICE raised as well.

David J.

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Shane W
> Sent: Monday, May 23, 2011 4:08 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] Trapping errors
>
> Hello list,
>
> I have a table with double precision columns and update queries which
> multiply and divide these values. I am wondering if it's possible to catch
> overflow and underflow errors to set the column to 0 in the case of an
> underflow and a large value in the case of an overflow.
>
> Currently, I have an exception handler in a PLPGSQL ufunction that sort of
> does this.
>
> begin
> update tbl set score = score/s
> exception when numeric_value_out_of range then update tbl set score=0
> where cast(score/s as numeric) < 1e-200 end;
>
> But this is messy since the exception needs to rescan the entire table if even
> one row fails the update. Is there a better way to do this?
>
> Best,
> Shane
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org) To make
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

On 23 May 2011, at 22:08, Shane W wrote:

> Hello list,
>
> I have a table with double precision columns and update
> queries which multiply and divide these values. I am
> wondering if it's possible to catch overflow and underflow
> errors to set the column to 0 in the case of an underflow
> and a large value in the case of an overflow.
>
> Currently, I have an exception handler in a PLPGSQL
> ufunction that sort of does this.
>
> begin
> update tbl set score = score/s
> exception when numeric_value_out_of range then
> update tbl set score=0
> where cast(score/s as numeric) < 1e-200
> end;
>
> But this is messy since the exception needs to rescan the
> entire table if even one row fails the update. Is there a
> better way to do this?

If you move the overflow/underflow check into a before-trigger, then you can use the NEW.* and OLD.* variables to alter the row before it gets written. That way you scan the table only once and you also moved your handling of such errors into the database (which means that if other applications than your client ever write values to that table, the same rules are applied).

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

!DSPAM:737,4ddb6e8111921119526771!