Reduce NUMERIC size by 2 bytes, reduce max length to 508 digits - Mailing list pgsql-hackers

[ Moved to hackers for patch discussion.]

John D. Burger wrote:
> >> There are practical applications, eg, 1024-bit keys are fairly common
> >> objects in cryptography these days, and that equates to about 10^308.
> >> I don't really foresee anyone trying to run crypto algorithms with SQL
> >> NUMERIC arithmetic, though ...
> >
> > 2046 bit keys are becoming more common. However, math using these keys 
> > is
> > usually done modulo a product of two primes and there are ways of 
> > doing the
> > calculations that are going to be much faster than doing them the way
> > Postgres does. So it is unlikely that anyone would be using Postgres' 
> > numeric
> > type to do this in any case.
> 
> Nonetheless, the fact that people can think of practical applications 
> for numbers whose length is easily within a factor of two of the 
> proposed limitation makes me squeamish about it being shrunk.  Also, I 
> would say the same arguments about doing math with NUMERICs suggest 
> that saving a few byes in representation is not a big deal.  On the few 
> occasions where I have used NUMERICs, I didn't care about stuff like 
> that.
> 
> For what it's worth.

Good point, but I am not 100% sure on the limitation.  Look at this:
test=> CREATE TABLE test(x NUMERIC);CREATE TABLEtest=> INSERT INTO test SELECT pow(10::numeric, 10000) + 1;INSERT 0
1test=>SELECT log(x) FROM test;          log------------------------ 10000.0000000000000000(1 row)test=> SELECT x % 10
FROMtest;      ?column?-------------------- 1.0000000000000000(1 row)
 

And this seems to work too:
test=> INSERT INTO test SELECT pow(10::numeric, 120000) + 1;INSERT 0 1

The limit seems to be around 150k digits:
test=> INSERT INTO test SELECT pow(10::numeric, 150000) + 1;ERROR:  value overflows numeric format

With current code, you can not define a NUMERIC column with greater than
1000 digits because we just placed an arbitrary limit on the length, but
the computational length was obviously much larger than the storage
limit.  And I suppose you could exceed 1000 if you stored the result as
text and converted it to NUMERIC just for computations.

In fact we have this TODO, but I wonder if it is still an open issue:
* Change NUMERIC to enforce the maximum precision

We seem to enforce things just fine.
Now, with the new patch, I see a _much_ lower limit:
test=> SELECT pow(10::NUMERIC, 511) + 1;...(1 row)test=> SELECT pow(10::NUMERIC, 512) + 1;ERROR:  value overflows
numericformattest=> SELECT pow(10::NUMERIC, 512);ERROR:  value overflows numeric format
 

I thought maybe I could do the computations at least and then convert
into text, but seeing the above it seems higher precision computation is
just not possible --- it is more than just storage in a table that is
changed.

So, with the patch, the storage length is going from 1000 digits to 508,
but the computational length is reduced from around 150k digits to 508. 
Now, because no one has complained about the 1000-digit limit, it is
unlikely that anyone is doing calculations over 1000 or the would have
had problems with storing the value, but I felt I should point out that
we are dramatically changing the computational length.

In fact, for the tests we have been running to debug the *printf
problem, none of those queries will work with the patch:
stest=> SELECT factorial(4000);ERROR:  value overflows numeric formattest=> SELECT factorial(400);ERROR:  value
overflowsnumeric format
 

Not only does 4000! not work, but 400! doesn't even work.  I just lost
demo "wow" factor points!

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073