Thread: Reduce NUMERIC size by 2 bytes, reduce max length to 508 digits
There is a patch under consideration for 8.2 that would reduce the storage requirement for numeric values by two bytes, but also reduce the range of allowed numeric values to 508 digits. The current specified maximum NUMERIC length is 1000 (NUMERIC(1000,0)), and the maximum computational length is 4096 digits. (Computations over 4096 digits are silently truncated. Throwing an error instead is a TODO item I hope will be worked on as part of this change.) Is that an acceptable tradeoff (reduced size, reduced range) for our users? --------------------------------------------------------------------------- Simon Riggs wrote: > > Now we're into 8.2devel mode, its time to submit the previously > discussed patch that: > > - reduces Numeric storage format by 2 bytes > - limits scale to +/- 508 decimal places > > This is sufficient to allow Numeric to continue to be used as the > default numeric representation for all numbers in the parser. > > Passes: make check on cvstip, as well as some tests not in there. > > Code comments explain the new format and consequences. > > As previously agreed, reviewing this is a 2 stage process: > 1. review/possibly agree OK to commit > 2. check with everybody on GENERAL that the restriction to 508 is > acceptable > > Figure there's no point doing (2) until we agree the proposal/code is > workable. > > As Atsushi-san point out, there is also come CPU optimization to be done > on Numeric comparison, and also on other areas such as aggregation. I've > not done this yet. > > Best Regards, Simon Riggs [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster -- 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, Pennsylvania 19073 Simon Riggs wrote: > > Now we're into 8.2devel mode, its time to submit the previously > discussed patch that: > > - reduces Numeric storage format by 2 bytes > - limits scale to +/- 508 decimal places > > This is sufficient to allow Numeric to continue to be used as the > default numeric representation for all numbers in the parser. > > Passes: make check on cvstip, as well as some tests not in there. > > Code comments explain the new format and consequences. > > As previously agreed, reviewing this is a 2 stage process: > 1. review/possibly agree OK to commit > 2. check with everybody on GENERAL that the restriction to 508 is > acceptable > > Figure there's no point doing (2) until we agree the proposal/code is > workable. > > As Atsushi-san point out, there is also come CPU optimization to be done > on Numeric comparison, and also on other areas such as aggregation. I've > not done this yet. > > Best Regards, Simon Riggs [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster -- 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, Pennsylvania 19073
Bruce Momjian Sent: Friday, December 02, 2005 9:39 AM > > There is a patch under consideration for 8.2 that would reduce the > storage requirement for numeric values by two bytes, but also reduce the > range of allowed numeric values to 508 digits. ... > Is that an acceptable tradeoff (reduced size, reduced range) for our > users? I would be in favor of this change. What's the plan for anyone who may be currently using > 508 digits (if there is anyone!)? -Roger
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> There is a patch under consideration for 8.2 that would reduce the
> storage requirement for numeric values by two bytes, but also reduce the
> range of allowed numeric values to 508 digits. The current specified
> maximum NUMERIC length is 1000 (NUMERIC(1000,0)), and the maximum
> computational length is 4096 digits. (Computations over 4096 digits are
> silently truncated.
Bruce is entirely incorrect on the last two points (although he may have
found a bug in his copy of psql, see thread in -patches if you care).
The actual effective limit on NUMERIC is presently 10^128K, which is
probably enough to count the femtoseconds remaining until the heat death
of the universe, and then multiply that by the number of elementary
particles therein ;-). And it is checked --- compare what you get from
select pow(10::numeric, 131071);
select pow(10::numeric, 131072);
Restricting NUMERIC to 10^508 would therefore be a significant reduction
in dynamic range. Whether anyone seriously cares is another question
--- if you do want unlimited-precision arithmetic, you should probably
be doing it in some other software anyway. (The NUMERIC routines get
painfully slow with tens of thousands of digits :-(.)
The current 1000-digit limit on declared NUMERIC columns is basically an
artificial limit, with pretty much the same reasoning as the artificial
limit on declared VARCHAR length: if you think you need more than 1000
digits then you probably ought not be declaring a specific upper limit
at all.
BTW, the limit is on dynamic range, not number of digits: the first
significant digit has to be within 128K places of the decimal point
(or, if this patch is applied, within 508 places of the decimal point),
but you can have as many digits as you like after that one. It would be
reasonable to describe the patched system as allowing 500 places before
and 500 places after the decimal point, or 1000 digits overall.
So the question is, is anyone doing anything with Postgres that would be
affected by a 500-place limit, or even come close to being affected?
regards, tom lane
"Roger Hand" <RHand@kailea.com> writes:
> I would be in favor of this change. What's the plan for anyone who
> may be currently using > 508 digits (if there is anyone!)?
You could store your data as text columns and do whatever arithmetic
you had in mind on the client side.
regards, tom lane
I wrote:
> The actual effective limit on NUMERIC is presently 10^128K, which is
> probably enough to count the femtoseconds remaining until the heat death
> of the universe, and then multiply that by the number of elementary
> particles therein ;-).
Should have done my research first. A little googling says that
* The total number of particles in the universe has been
variously estimated at numbers from 10^72 up to 10^87.
* The time to the heat death of the universe has been estimated at
10^200 years (and if there's one significant digit in that exponent
I'd be surprised...)
So the product I fancifully mentioned would weigh in somewhere around
10^300, and thus be *well* within the capability of even the proposed
restricted numeric format.
regards, tom lane
Re: Reduce NUMERIC size by 2 bytes, reduce max length to 508 digits
From
Martijn van Oosterhout
Date:
On Fri, Dec 02, 2005 at 07:37:49PM -0500, Tom Lane wrote: > So the product I fancifully mentioned would weigh in somewhere around > 10^300, and thus be *well* within the capability of even the proposed > restricted numeric format. I think numbers much bigger than that are only useful for theoretical mathemeticians. I also think that most of the people dealing with such numbers probably refer to the formula used to create them since manipulating that is likely to be faster than reading all the digits of such a large numbers. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
Martijn van Oosterhout <kleptog@svana.org> writes:
> On Fri, Dec 02, 2005 at 07:37:49PM -0500, Tom Lane wrote:
>> So the product I fancifully mentioned would weigh in somewhere around
>> 10^300, and thus be *well* within the capability of even the proposed
>> restricted numeric format.
> I think numbers much bigger than that are only useful for theoretical
> mathemeticians.
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 ...
regards, tom lane
On Sat, Dec 03, 2005 at 11:43:00 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Martijn van Oosterhout <kleptog@svana.org> writes: > > On Fri, Dec 02, 2005 at 07:37:49PM -0500, Tom Lane wrote: > >> So the product I fancifully mentioned would weigh in somewhere around > >> 10^300, and thus be *well* within the capability of even the proposed > >> restricted numeric format. > > > I think numbers much bigger than that are only useful for theoretical > > mathemeticians. > > 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.
>> 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. - John D. Burger MITRE
On Mon, Dec 05, 2005 at 08:24:16AM -0500, 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. I think that if there are any esoteric cases where people are doing these kinds of things with numeric, they could probably be best answered by offering a completely different system anyway, using a different type name. The 5 people in the world doing this will just have to change their code I guess... ;) -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461