I'm working with a customer that recently discovered that some code had
generated the following nice query...
SELECT ... WHERE table_id = 92838278! AND ...
So their production server now has several processes that are trying to
compute some absurdly large factorial. There's two issues here:
1) the computation doesn't check for signals. This means both a plain
kill and pg_cancel_backend() are useless.
2) Even though the answer is going to be an obscene number of digits,
and that's supposed to be fed into a numeric, there's no overflow or
bounds checking occurring. This is true even if I store into a field
defined as numeric:
decibel=# create table n(n numeric);
CREATE TABLE
decibel=# insert into n select 3333!;
INSERT 0 1
decibel=# select char_length(trim(n, '0')) from n;char_length
------------- 9466
(1 row)
So at the very least the documentation is confusing:
The type numeric can store numbers with up to 1000 digits of precision
and perform calculations exactly.
...
Specifying
NUMERIC
without any precision or scale creates a column in which numeric values
of any precision and scale can be stored, up to the implementation limit
on precision.
Yet here we have a numeric that's storing nearly 10,000 digits of
precision.
--
Jim Nasby decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)