Re: Reduce NUMERIC size by 2 bytes, reduce max length to 508 digits

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

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

This makes the often discussed binary upgrade impossible, so I wonder if
two bytes savings are worth the trouble.

Regards,
Andreas

Andreas Pflug <pgadmin(at)pse-consulting(dot)de> writes:
> 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

> This makes the often discussed binary upgrade impossible, so I wonder if
> two bytes savings are worth the trouble.

Unless someone actually steps forward and produces a working pg_upgrade
in the 8.2 timeframe, this objection is moot.

regards, tom lane

Tom Lane wrote:
> Andreas Pflug <pgadmin(at)pse-consulting(dot)de> writes:
>
>>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
>
>
>>This makes the often discussed binary upgrade impossible, so I wonder if
>>two bytes savings are worth the trouble.
>
>
> Unless someone actually steps forward and produces a working pg_upgrade
> in the 8.2 timeframe, this objection is moot.

Hm, so if this patch is applied now, and in 5 months or so somebody
implements pg_upgrade, this numeric storage patch would be rolled back?
OTOH, an upgrade mechanism that's compatible for future 8.3+ versions
only seems not too attractive.
A solution might be to keep the current numeric implementation under a
different name (deprecatednumeric or so), for backward compatibility
(this should apply to future storage format changes as well).

Regards,
Andreas

>
> Hm, so if this patch is applied now, and in 5 months or so somebody
> implements pg_upgrade, this numeric storage patch would be rolled back?
> OTOH, an upgrade mechanism that's compatible for future 8.3+ versions
> only seems not too attractive.
With Slony and Replicator I don't really see the need for in place
upgrades.

Joshua D. Drake

> A solution might be to keep the current numeric implementation under a
> different name (deprecatednumeric or so), for backward compatibility
> (this should apply to future storage format changes as well).
>
> Regards,
> Andreas
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match

Joshua D. Drake wrote:

> With Slony and Replicator I don't really see the need for in place
> upgrades.
>

Maintaining a replica is hardly a cost-free exercise.

However, I don't think we can promise never to change the ondisk
representation of data, nor the page layout. Sometimes an inplace
upgrade just won't work, ISTM.

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> However, I don't think we can promise never to change the ondisk
> representation of data, nor the page layout. Sometimes an inplace
> upgrade just won't work, ISTM.

We have talked about batching on-disk changes so that they'd only occur
once every few release cycles. But until we have a pg_upgrade, there is
no reason to adopt such a policy.

regards, tom lane

On Thu, 2005-11-17 at 11:20 -0500, Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> > However, I don't think we can promise never to change the ondisk
> > representation of data, nor the page layout. Sometimes an inplace
> > upgrade just won't work, ISTM.
>
> We have talked about batching on-disk changes so that they'd only occur
> once every few release cycles. But until we have a pg_upgrade, there is
> no reason to adopt such a policy.

If the patch is accepted technically, in can be applied at any time,
right up to code freeze for this or the next release. It's a fairly
independent patch.

I'd suggest we check it out now, then put it in a holding pen for awhile
to see if an upgrade tool emerges.

Best Regards, Simon Riggs

Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> If the patch is accepted technically, in can be applied at any time,
> right up to code freeze for this or the next release. It's a fairly
> independent patch.

> I'd suggest we check it out now, then put it in a holding pen for awhile
> to see if an upgrade tool emerges.

I'm disinclined to do that unless there's a pretty firm commitment from
someone to work on pg_upgrade in the near future. Patches that are not
in the tree tend to suffer from code drift; if we wait six months or a
year to apply what you've done then we'll likely be looking at
significantly more work to get it in. We'd also be losing the direct
and indirect testing that the patch would get were it in the tree over
that length of time.

regards, tom lane

Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>
>>However, I don't think we can promise never to change the ondisk
>>representation of data, nor the page layout. Sometimes an inplace
>>upgrade just won't work, ISTM.
>
>
> We have talked about batching on-disk changes so that they'd only occur
> once every few release cycles. But until we have a pg_upgrade, there is
> no reason to adopt such a policy.

IMHO such a policy is a _prerequisite_ for somebody to come up
implementing pg_upgrade. Why spend time on pg_upgrade if there's no
policy to support it?

Regards,
Andreas

Andreas Pflug wrote:
> Tom Lane wrote:
> >Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> >
> >>However, I don't think we can promise never to change the ondisk
> >>representation of data, nor the page layout. Sometimes an inplace
> >>upgrade just won't work, ISTM.
> >
> >We have talked about batching on-disk changes so that they'd only occur
> >once every few release cycles. But until we have a pg_upgrade, there is
> >no reason to adopt such a policy.
>
> IMHO such a policy is a _prerequisite_ for somebody to come up
> implementing pg_upgrade. Why spend time on pg_upgrade if there's no
> policy to support it?

Is anybody working or considering to work on pg_upgrade, or is all this
hypothetical? Our past history has seen lots of people offering to work
on pg_upgrade, and none has produced a working version. Is it fair or
useful to impose restrictions on development just because it's remotely
possible that somebody is going to be motivated enough to consider
producing it?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera wrote:
>
> Is anybody working or considering to work on pg_upgrade, or is all this
> hypothetical? Our past history has seen lots of people offering to work
> on pg_upgrade, and none has produced a working version. Is it fair or
> useful to impose restrictions on development just because it's remotely
> possible that somebody is going to be motivated enough to consider
> producing it?

Depends on the impact the restriction imposes. If
stability/scalability/functionality or so is affected, this sounds not
tolerable. If it's about not saving two bytes that have been spoiled for
ages before, or keeping a backward compatibility type, it appears
feasible to me.
Changing on-disk structures at the start of the 8.2 dev cycle is a
guarantee that nobody will implement pg_upgrade for 8.2.

Regards,
Andreas

Andreas Pflug wrote:
> Alvaro Herrera wrote:
> >
> > Is anybody working or considering to work on pg_upgrade, or is all this
> > hypothetical? Our past history has seen lots of people offering to work
> > on pg_upgrade, and none has produced a working version. Is it fair or
> > useful to impose restrictions on development just because it's remotely
> > possible that somebody is going to be motivated enough to consider
> > producing it?
>
> Depends on the impact the restriction imposes. If
> stability/scalability/functionality or so is affected, this sounds not
> tolerable. If it's about not saving two bytes that have been spoiled for
> ages before, or keeping a backward compatibility type, it appears
> feasible to me.
> Changing on-disk structures at the start of the 8.2 dev cycle is a
> guarantee that nobody will implement pg_upgrade for 8.2.

Let's go ahead and apply the patch. While this change isn't very
significant, I bet there will be other changes in 8.2 where we will want
to change the database for a significant benefit, like reducing the
tuple header by 4 bytes by recompressing the four xid/cid fields back
into three.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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 wrote:

>
>Let's go ahead and apply the patch. While this change isn't very
>significant, I bet there will be other changes in 8.2 where we will want
>to change the database for a significant benefit, like reducing the
>tuple header by 4 bytes by recompressing the four xid/cid fields back
>into three.
>
>
>

All of which makes me very uncomfortable about doing 64 bit enums. I am
still trying to think of a better solution.

cheers

andrew

Where are we on this patch? It is ready for the patch queue?

---------------------------------------------------------------------------

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Thu, 2005-12-01 at 23:34 -0500, Bruce Momjian wrote:
> Where are we on this patch? It is ready for the patch queue?

It's good to be applied, AFAIK.

> Simon Riggs wrote:
> > 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.

I was hoping you'd give me a "this looks good enough to apply"
thumbs-up, then I'll ask for comments via a Weekly News item.

If I ask for comments and then it is technically rejected we would be
wasting everybody's time.

Best Regards, Simon Riggs

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.

OK, seems all objections have been dealt with so it goes into the patch
queue. I will ask on 'general'.

The only downside I see is that I can't impress people by doing:

SELECT factorial(4000);

I don't suppose the _impression_ factor is worth two bytes per value.
Shame.

I suppose people wanting to do such manipulations will have to store the
numbers as text and use a server-side library like perl to do
calculations.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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 wrote:
> OK, seems all objections have been dealt with so it goes into the patch
> queue. I will ask on 'general'.
>
> The only downside I see is that I can't impress people by doing:
>
> SELECT factorial(4000);
>
> I don't suppose the _impression_ factor is worth two bytes per value.
> Shame.
>
> I suppose people wanting to do such manipulations will have to store the
> numbers as text and use a server-side library like perl to do
> calculations.

Oops, I was wrong about this. The patch changes the maximum _specified_
precision:

/*
! * Hardcoded precision limit - arbitrary, but must be small enough that
! * dscale values will fit in 14 bits.
*/
! #define NUMERIC_MAX_PRECISION 1000

/*
* Internal limits on the scales chosen for calculation results
--- 15,23 ----
#define _PG_NUMERIC_H_

/*
! * Hardcoded precision limit - maximum that can fit in Numeric storage
*/
! #define NUMERIC_MAX_PRECISION 508

but in fact, our computational precision is 4096, and we silently
overflow for values greater than that:

test=> create table test(x numeric);
CREATE TABLE
test=> insert into test values (factorial(4000));
INSERT 0 1

The length is 4096 digits, and so is factorial(10000) --- clearly wrong.
I now see in the TODO:

* Change NUMERIC to enforce the maximum precision, and increase it

So we are really decreasing the specified precision from 1000 to 508,
and the computational precision from 4096 to 508. Is there any plan to
fix the silent overflow problem? Is that in the patch? I don't see it.

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

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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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 <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> So we are really decreasing the specified precision from 1000 to 508,
> and the computational precision from 4096 to 508.

The internal computational precision isn't any less, the limit is only
on the result of a function (ie, partial results within one of the
numeric.c routines could still exceed 10^508). Not sure how much that
distinction matters though.

> Is there any plan to
> fix the silent overflow problem? Is that in the patch? I don't see it.

It will get fixed before application ;-)

I haven't reviewed the patch yet; I think the gating factor at this
point is whether anyone protests losing dynamic range in NUMERIC,
and we ought to go ahead and ask that. After that we can look at the
code more closely.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > So we are really decreasing the specified precision from 1000 to 508,
> > and the computational precision from 4096 to 508.
>
> The internal computational precision isn't any less, the limit is only
> on the result of a function (ie, partial results within one of the
> numeric.c routines could still exceed 10^508). Not sure how much that
> distinction matters though.

Agreed.

> > Is there any plan to
> > fix the silent overflow problem? Is that in the patch? I don't see it.
>
> It will get fixed before application ;-)
>
> I haven't reviewed the patch yet; I think the gating factor at this
> point is whether anyone protests losing dynamic range in NUMERIC,
> and we ought to go ahead and ask that. After that we can look at the
> code more closely.

I am confused by your use of the term "dynamic" range. From what you
say above that we are just moving from 1000 to 508 for storage, and that
computational range would still be 4096?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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 <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> I am confused by your use of the term "dynamic" range. From what you
> say above that we are just moving from 1000 to 508 for storage, and that
> computational range would still be 4096?

No, computational range would still be on the order of 10^16G ... in the
computational format, the weight is an int. The restriction to 1000
digits was never anything but an artificial limit. (Of course, you
might not have the patience to actually do any arithmetic with that many
digits, but the point is there was a whole lot of headroom before, and
now there won't be.)

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > I am confused by your use of the term "dynamic" range. From what you
> > say above that we are just moving from 1000 to 508 for storage, and that
> > computational range would still be 4096?
>
> No, computational range would still be on the order of 10^16G ... in the
> computational format, the weight is an int. The restriction to 1000
> digits was never anything but an artificial limit. (Of course, you
> might not have the patience to actually do any arithmetic with that many
> digits, but the point is there was a whole lot of headroom before, and
> now there won't be.)

Sorry, I am confused. If our computational range is that high, why does
SELECT factorial(4000) and SELECT factorial(6000) produce the same
number of digits on my screen.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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 <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Sorry, I am confused. If our computational range is that high, why does
> SELECT factorial(4000) and SELECT factorial(6000) produce the same
> number of digits on my screen.

Are you counting correctly?

regression=# select log(factorial(4000));
log
------------------------
12673.2621666764869773
(1 row)

regression=# select log(factorial(6000));
log
--------------------
20065.428782473590
(1 row)

regression=# select factorial(4000)
regression-# \g z4000
regression=# select factorial(6000)
regression-# \g z6000
regression=# \q
$ wc z4000 z6000
5 5 38039 z4000
5 5 60215 z6000

The actual representation limit at the moment (with int16 weights in the
storable format) is 10^128K, as you can soon prove with pow():

regression=# select pow(10::numeric, 131071);
<< lots o zeroes >>
regression=# select pow(10::numeric, 131072);
ERROR: value overflows numeric format

I don't recall what factorial that might correspond to, but it's
considerably above 6000.

regards, tom lane

Uh, I tried factorial(4000) and for display I got:

182880195151406501331474317557391904421737771073043921970645269542089597979731773648503702868704841073364430415692855717546724618615435573339426156179569967167452848315973174988187609374828049804195765129487206105589281297880978006205934295377053267406244538842850917439517567461444473623787224694361945759295799001142129733606589980739777146972612050486637259363374904040660979666371702540213488009442803422853559466496813162601634597438035771759033947331700768417647790821668911845293242300334141454978018325982185184065522570973925300245827389829191044067821687088714956035019058673999662987985348777479231791957914165044080548789747703086507071208788376249865760733404494148545783673833017157063581941274008498556040804733051968334824080794209642751875388891152966555223977239248871546248106597883210056205583696047786579047719183880543192515139819542967416884472461850212504022250101164330168185880366901801776914617797131043016403957082747347011867727569660646110236565287651387357041908762006971458046921252368210668053371752206057453775574525922086539398532785238414483140265488023098603910879397832189461295826479284307399855548838061987498316336401962112027560860160391716077440787768762196616037027594548879452476057492055434640958836645149602938732448424098038014805666001244152937898314963095541171138885615694943149261344704775135164165601029840587519062088655701836838507913173957028613508214646536004694432790777339785687114042447740895092167279225106609414117164124674434454140011889159665472837739886707397928188973147620825689140419522117791940553114052591585389323887452923243868268301359048864722922899938484822892543076284676145232925192226876891802197883771840052462908967032605249103621366273211359765153585281501437967981168362630532297339716122751848961395396131293290084492147231967037891198209712059221955139155468147047786823734877189465608228116230388538870543573162906223784723220453166394184917985170772758396375254276014529618356744844344988856988406924685508257651316109259665853395618544561542290482957422747251262187997454480313918262952211143818906006832084415580882712286180065890594441088066529927878546344974871586757709834226109365906006271705009724813994441453985227568706260972502302291957992772999218449547156908832425535692566571325156635449318303933175188289864439421389716091426213976468083518096946037348729779841480026999651378704481998661671629492564350404161468868239421444591051750334883958699104052075213290168426731685638375315189183396277240661529336272367305611554182278886735139374545081038261028277061215603309060164041624200513733136545701111020033195778785022169191701120746087228523767999431915904806516239580629828294520352271190365024265837525121998240897256117110591539354344189851092414041350690471095275147306485020646304313711859225230369416210263927838134355401958005319886454303447452988456400170827326232488384737716034783363266625792191376014226320576487588079352339155275628179423786752439198868000562094347314076856919423270924641011362547954991593511035427477234345444363663131049963736616549894654981808927164628050422270382221047840626260274801515673778418213162920952956863686193004178633275307643013230819024359711659251635132255111762589194716734375533209349169105739990209660872076631338715165303917875357554203481745199540130159991933352050325711764601050057161153057486693646826752650143102232717628076202428056174355949278906764089530573848907196812258404003966981556247938888161565850436047829617049713977649594047513584458569145819571865335732076903558941507766477279941564256419537555177279654860966753842222323441855372798887805708540920842219936607611547883597774397984908511480312751237592867932246601885937688972917491803572971856504307350631260352783217496299957022001157223866000467728830189631662734637151868125433561735503412333101756008177714471706565175053852587720690671396654785062638983805263943882180363887689991615386454329321164711463925629922042594508892919282617805319256181483111512590885198697876379136078660799208307814332752984685342485954714853544207940898546931676443201159004044444712

and did factorial(6000) and see:

268399976572673959611631664746273551220501860468847256858242614456610709658685788594794097483463298361827030891288526326099395831604692015266370347869763966250758258875295848788615239847022662960185954275515801746542169234248956445498097321653975495002879600970444367760980971122859359020023183008177660749446398654191572805247906532032902539173561336339939785357602530693575551422196691299983170265180324028107514480105574840042547452741402638372101223251070623789275754151796407983255989209077389689634265538335191680339200971398676973450160815636763744412386642130802136662569290977913751994853502315954564979001492027467287553278798129715463733693706773857003416731922853609101127286793852057197105805836413716718924479768188292438178887547214489628871232322684249607541476504824159030266067904672098495636622714169522826493225987513978025239843199202535945837220824550067533621599059257410549700397263292773107837783018961116396473891051895246957317480397448313301711948498137501421985916613313677318972708276426636501211541798449272458694554528660905408695501946189847436491277565405953291476966829310127398586112042831345221430829782552000483383182291153826750774350110211222036572082966453541007117842465371083764279368883320838538657325445624710224662094282623678927668138225862387308841866706405996811552030532635792612643270369483840887567027622533066713611166970068436428344437921870681849674153297625433304802646941320010915365936588697883898144809271168852151697567870735519106890192837508950174932281111167303108281667660412005310563821643233905397132951167413884839677453056917865379413323398186679176765713700074193977734752619438945140523129322896246617698780123506764949477308668319248045528511977593451912540816314647384162691477898479638182122811558965504271703872859804604909516454412434978145140029728337310206814260951601497781410375736884915214425224285447889693895582787613974813482188771577391131805341100500232121264194386991727261544256550362495091543418463532461157575716553971729978886225152177256813555070098051132424613434627496714527886380771247504457759237254918078579046960158898105829151092672132122153152037131592086496741649797197273129205765115591964706021743613038992316498949125108561858147409172262391720725462015395849060504522468632113009485442625506623105299842269925826510851512148068627375440376241841700924369084385977739093116508393021241067435397561337014305498228351759791196475430817743347578460256487782755679664090171158282392788386089643981353002195923088605647717413703528027213285441960352032589136772883045832920269026502920226152339494774542473806139137272059629788082668430781817825215292956631787233099675515123199855701146059750816753705446068330515181719059082252289813159988885086413837541106841617105403795738627508478475051189321086873431162595048827079153319683349897664762150944390327639338292582968256008076593833963316569163794513447848024498992277763357233477663906896662419631703673933912108212320428083024732640632655701107277198314756895614049678770664304299038488801938643778814462182315714396317585684284639471481188587178099037484342226839564520436636188623810771667361639041436117718763725315902967260230353306737999520669241055296022230466287592454208018267220839809215680052112392508181570447124439985770512016943866666765484591390595349556034523324980915614115728536228512670733960540990149693668014945277065375953041388484806644767705332650903626423313863286739125786192092551628124455898401566515763784483104702008872999433928784349649981676639778828872212630963066247193525702160175379019190959971744914238250769159018448282997288670683589328931738555813393831524322836759323091391080868465062163708660330806215238405311467559340973074082156970244090516876036687772268068304154337954105931408904169310310580843570395681027026389994942257493794275398994779012537285255893335279592775544415882679886419718766728485222788854329500007131747178185641199051879252260306500569972158326067559151393339879121574198423317442886428899625776225591734610920613377827928006698899986559608527469421131645773273348845767558137

Both are 4096 characters.

You are right the log() works fine:

test=> select log(factorial(4000));
log
------------------------
12673.2621666764869773
(1 row)

test=> select log(factorial(6000));
log
--------------------
20065.428782473590
(1 row)

I tried your \g test and the file size difference is the length of the
dashed line in the file, not the number of digits display, which are
both 4096. One has 12550 dashes, the other 19950 dashes.

However, my 'wc' sizes are differnt from yours:

5 4 20881 /rtmp/4000
5 4 28273 /rtmp/6000

I ran your SELECT pow(10::numeric, 131071), and gain, 4096 0's are
displayed on my screen. SELECT pow(10::numeric, 7000) and SELECT
pow(10::numeric, 10000) generate identical displays on my screen.

I am even more confused.

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Sorry, I am confused. If our computational range is that high, why does
> > SELECT factorial(4000) and SELECT factorial(6000) produce the same
> > number of digits on my screen.
>
> Are you counting correctly?
>
> regression=# select log(factorial(4000));
> log
> ------------------------
> 12673.2621666764869773
> (1 row)
>
> regression=# select log(factorial(6000));
> log
> --------------------
> 20065.428782473590
> (1 row)
>
> regression=# select factorial(4000)
> regression-# \g z4000
> regression=# select factorial(6000)
> regression-# \g z6000
> regression=# \q
> $ wc z4000 z6000
> 5 5 38039 z4000
> 5 5 60215 z6000
>
> The actual representation limit at the moment (with int16 weights in the
> storable format) is 10^128K, as you can soon prove with pow():
>
> regression=# select pow(10::numeric, 131071);
> << lots o zeroes >>
> regression=# select pow(10::numeric, 131072);
> ERROR: value overflows numeric format
>
> I don't recall what factorial that might correspond to, but it's
> considerably above 6000.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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 <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Both are 4096 characters.

You forgot the part that scrolled off the screen. Or else your
installation is broken.

I get this for factorial(4000)
1828801951514065013314743175573919044217377710730439219706452695420895979797317736485037028687048410733644304156928557175467246186154355733394261561795699671674528483159731749881876093748280498041957651294872061055892812978809780062059342953770532674062445388428509174395175674614444736237872246943619457592957990011421297336065899807397771469726120504866372593633749040406609796663717025402134880094428034228535594664968131626016345974380357717590339473317007684176477908216689118452932423003341414549780183259821851840655225709739253002458273898291910440678216870887149560350190586739996629879853487774792317919579141650440805487897477030865070712087883762498657607334044941485457836738330171570635819412740084985560408047330519683348240807942096427518753888911529665552239772392488715462481065978832100562055836960477865790477191838805431925151398195429674168844724618502125040222501011643301681858803669018017769146177971310430164039570827473470118677275696606461102365652876513873570!
4190876200697145804692125236821066805337175220605745377557452592208653939853278523841448314026548802309860391087939783218946129582647928430739985554883806198749831633640196211202756086016039171607744078776876219661603702759454887945247605749205543464095883664514960293873244842409803801480566600124415293789831496309554117113888561569494314926134470477513516416560102984058751906208865570183683850791317395702861350821464653600469443279077733978568711404244774089509216727922510660941411716412467443445414001188915966547283773988670739792818897314762082568914041952211779194055311405259158538932388745292324386826830135904886472292289993848482289254307628467614523292519222687689180219788377184005246290896703260524910362136627321135976515358528150143796798116836263053229733971612275184896139539613129329008449214723196703789119820971205922195513915546814704778682373487718946560822811623038853887054357316290622378472322045316639418491798517077275839637525427601452961835674484434498885!
6988406924685508257651316109259665853395618544561542290482957422747251
26218799745448031391826295221114381890600683208441558088271228618006589059444108806652992787854634497487158675770983422610936590600627170500972481399444145398522756870626097250230229195799277299921844954715690883242553569256657132515663544931830393317518828986443942138971609142621397646808351809694603734872977984148002699965137870448199866167162949256435040416146886823942144459105175033488395869910405207521329016842673168563837531518918339627724066152933627236730561155418227888673513937454508103826102827706121560330906016404162420051373313654570111102003319577878502216919170112074608722852376799943191590480651623958062982829452035227119036502426583752512199824089725611711059153935434418985109241404135069047109527514730648502064630431371185922523036941621026392783813435540195800531988645430344745298845640017082732623248838473771603478336326662579219137601422632057648758807935233915527562817942378675243919886800056209434731407685691942327092464101136254795499159351103542747723!
4345444363663131049963736616549894654981808927164628050422270382221047840626260274801515673778418213162920952956863686193004178633275307643013230819024359711659251635132255111762589194716734375533209349169105739990209660872076631338715165303917875357554203481745199540130159991933352050325711764601050057161153057486693646826752650143102232717628076202428056174355949278906764089530573848907196812258404003966981556247938888161565850436047829617049713977649594047513584458569145819571865335732076903558941507766477279941564256419537555177279654860966753842222323441855372798887805708540920842219936607611547883597774397984908511480312751237592867932246601885937688972917491803572971856504307350631260352783217496299957022001157223866000467728830189631662734637151868125433561735503412333101756008177714471706565175053852587720690671396654785062638983805263943882180363887689991615386454329321164711463925629922042594508892919282617805319256181483111512590885198697876379136078660799208307!
8143327529846853424859547148535442079408985469316764432011590040444447
12660309427463760741879828098572928743828297343068794203087830107407847671558943630331861291837486983735992932677956206900039003485421405532082447711655937391173694524742950754451694883565501008544239290309671634013482235036740458218345267108303925209554788592023031753098157062595924161303887705354495701465861619542895292342093424320754612611732463831807028621720096248288525178612140082429449031624648169392786452106564311416530192247839060380628180064342584919527053417087812317673240265441652400124314579955652273319120562163961412779269323167836318998331068104928900458909472947317940163893853482940793609217575897156980984916122548982037875461240910578623872299874421317384997643910624222283957360997512778806612809092463696747923260569522205088612438756424828081757933037039333941912248321659503883379316266433399131920888087536190303869288083499317338810366904985137795878017644778657913514202229498607382289957280657634592836250969446785933783499902113174033583273676773588823770!
9591818584044134604576043558006391858795688808612703455806415483245609822466453242211458365376398953455980168326111373116203950006872922466150938226728648384186754230395516421339166353607274996659073789840814409269967579679316577589142999249195548586063962954241695575756516322382787419332127411993861870948202062774909491747375434632065316569012980974180688324676405396671725352114001080140999831849447053121905192035475255618347837823839858340276533792384316774973835504963671452984053414595037659880945929481919248609620050365253883541761300298393363447705382643881479123536393444095283521324080654380250524144590098115275384377863402130083229333243247340004010975428182895062546433140915196129231928786353574038244577112852234353305499307187252456412796230629821852166230420661001063848846115036178615183259518205501321023803505947347984141764444506655021709399474695956573046320199684575699957503195040133138568868357688342928927441920281610123907181777185361253462008885807490409523!
9406471009098851796038360101243053736361346640211872570375441319261567
13123816865223486418235966728553691384112297695600989297090940917886592847205669190457159696145864689513297258901209061388758443601869203894747593275308526810650755439456720165475793095722462957340194867715723782500554028263583989550901925424025691642222759003268500308732001723265842068120968138936417485403431915211170624656473389655163627174841162792046647547242124303380549205254223764709125257781195237624483684402567357093621694553920687573068412018777966475845150867878653988715496673299569221254420752945753493685390066352066510259227858121019210484601659484626552500039426420194631549266717467048945457661099372206366872093694969830864168443704648529290604036821870894719488299133382340067658455210867391366930611854915936973483307131950668087266188689822539817189632928873249950933783646316438461496769028328418413633400618467097003827515345813309877649317169324983672170763297353751827369292852749706812177960686516394369260659684894114368362762916987317111473918979463277570653!
6177409526869936538136348369630762610163490694986911903178859927739423796596582439105912599204583810744740479342404550513976713360591833475711496976836332005885151231030818986389033126325119226309584818058301429763149472903925739659774337307109687940297813700796873400566095281249251559137923743113469044584820584542770746490047535898370961450995287930441328833542933636539386097851378739174078158132593877463567524868470795313740375647059352650583871001646082364842709333314532933141725314262510333047099681128329792983694287727106008519184538377065089964546579000542260452715147077137452076302879112802094308224402060706282308208678850263037733351444756065285721900833083634023860920356321356382318484421995141074387149942542550133039926139155010190614633196727199469768170121209097613598302048321148497175007150184555423280152476407230065108963493864309805262458731715886593175689747530567359088332598961192319881186961400055356294945140450806114214490420494488195417136403967132286869!
4440626156228152333363962795174854698628808099560419383781613361569795
26713793454354319018535526139196959277008828634440008764782171726908279132449346924807112458895951881542382952497102061566169481361027449429416228859029591798532192088531772405831332410104172391828682208239266557146168642210154401086974397003647170356217905465594944933170065175291981240572835024374361017977695084363373226470846255498368067535698751768103817520605779489720985090002595078926475029239124403277040719184010486962844211160986922952144938430963756963408787147933184107904312838985994963982784496688190643908936394858404598837762653245968848299901455260080572627872653099337092686312650606546084656202210845053932679131706841390889951865628810708670194485207654079244417321332232196041180225814997707988397637369268804955010409293139079608620689856756139863786355461818276369292665568749282154818479663283704713663666842236273009790537968973379093662517795316051746410279831975339791651704861188979834156418998505732384020730350075052669695594594753081616632885311572351609352!
6311871293937295573989933217945181582696580736834175842602741842350308466836981938791099338635614350599935495973335142179739959157427962068898565845844938751938055046851073210114205022178151281184041375212505903613293851469974272920358295092058113673958856222475049508117521881656275521376810956188609556645053727634697884660679737487366587591055480490546582372000962937544792963441159155602311384325182484503832762347954001805356555512225366693907993170678967977562367834417529079488918024882982812456155901135124823445910922309096494239339917145571089413238495484686805093352037709704937850066367183942423260574161555090292700660962938130808773397877184427730148332327547557367833137895471455351295571935818859621586847564844752192308284237777560127212347313687852185643629532630922865351391604408387130138613284166629477561048573917407222093600210681588247179268508315895913673021435966254265537060385932558439195052231408715439952107204446375712686511951654835312437990870830709405442!
4151580913959415870484398056720248285664764444736260425296190770573874
03544603365867922146382253659739930019746681741572062425974525560466292095515996631179126097029443451766087576195612478720504096090627723627690457117097290610664115408987697078470429800349820333511363087796762879746276677038938880268487489532014384816320377178544620548618599866846369171208836027931940586585994257785155197137982144076898492114879291501799185472005585845241397924393774696760395181014516977128606342353524742905752601721515621292407207968356199678783830189610559216223714805626827146030855069328061879379766254310717428330077492356369907001205674258361562336075475008277895379090712635962949267638910754395759739725604783995107900787976630710241498267599345249805694328478194032192262070987017599524156284765195847522480505785841982117990920308389092082362251419759648927789441630088111797232193548477137530846288288833216925753278356587868920106488652050219855276426127956527593653593590480522055873069608538793513022153293803827092886725661695526601977880554500541776523!
7495531999280534598966239949401287401227867745105258416698708486626724549296878568417550851091049291379347855145699115229152275037730897331020050500790690667143583926211554675393439157986013302954440393242227121897504689638448225210882054641527417479169342607101992727851852052016462112727123174827797965928920485082301853519206732337236822483105064290215362684561847800962282473315076418832930481584365135273089783672702102302362732022837364580260249600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000!
0000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

and this for factorial(6000)
2683999765726739596116316647462735512205018604688472568582426144566107096586857885947940974834632983618270308912885263260993958316046920152663703478697639662507582588752958487886152398470226629601859542755158017465421692342489564454980973216539754950028796009704443677609809711228593590200231830081776607494463986541915728052479065320329025391735613363399397853576025306935755514221966912999831702651803240281075144801055748400425474527414026383721012232510706237892757541517964079832559892090773896896342655383351916803392009713986769734501608156367637444123866421308021366625692909779137519948535023159545649790014920274672875532787981297154637336937067738570034167319228536091011272867938520571971058058364137167189244797681882924381788875472144896288712323226842496075414765048241590302660679046720984956366227141695228264932259875139780252398431992025359458372208245500675336215990592574105497003972632927731078377830189611163964738910518952469573174803974483133017119484981375014219!
8591661331367731897270827642663650121154179844927245869455452866090540869550194618984743649127756540595329147696682931012739858611204283134522143082978255200048338318229115382675077435011021122203657208296645354100711784246537108376427936888332083853865732544562471022466209428262367892766813822586238730884186670640599681155203053263579261264327036948384088756702762253306671361116697006843642834443792187068184967415329762543330480264694132001091536593658869788389814480927116885215169756787073551910689019283750895017493228111116730310828166766041200531056382164323390539713295116741388483967745305691786537941332339818667917676571370007419397773475261943894514052312932289624661769878012350676494947730866831924804552851197759345191254081631464738416269147789847963818212281155896550427170387285980460490951645441243497814514002972833731020681426095160149778141037573688491521442522428544788969389558278761397481348218877157739113180534110050023212126419438699172726154425655036249509!
1543418463532461157575716553971729978886225152177256813555070098051132
42461343462749671452788638077124750445775923725491807857904696015889810582915109267213212215315203713159208649674164979719727312920576511559196470602174361303899231649894912510856185814740917226239172072546201539584906050452246863211300948544262550662310529984226992582651085151214806862737544037624184170092436908438597773909311650839302124106743539756133701430549822835175979119647543081774334757846025648778275567966409017115828239278838608964398135300219592308860564771741370352802721328544196035203258913677288304583292026902650292022615233949477454247380613913727205962978808266843078181782521529295663178723309967551512319985570114605975081675370544606833051518171905908225228981315998888508641383754110684161710540379573862750847847505118932108687343116259504882707915331968334989766476215094439032763933829258296825600807659383396331656916379451344784802449899227776335723347766390689666241963170367393391210821232042808302473264063265570110727719831475689561404967877066430429903!
8488801938643778814462182315714396317585684284639471481188587178099037484342226839564520436636188623810771667361639041436117718763725315902967260230353306737999520669241055296022230466287592454208018267220839809215680052112392508181570447124439985770512016943866666765484591390595349556034523324980915614115728536228512670733960540990149693668014945277065375953041388484806644767705332650903626423313863286739125786192092551628124455898401566515763784483104702008872999433928784349649981676639778828872212630963066247193525702160175379019190959971744914238250769159018448282997288670683589328931738555813393831524322836759323091391080868465062163708660330806215238405311467559340973074082156970244090516876036687772268068304154337954105931408904169310310580843570395681027026389994942257493794275398994779012537285255893335279592775544415882679886419718766728485222788854329500007131747178185641199051879252260306500569972158326067559151393339879121574198423317442886428899625776225591734!
6109206133778279280066988999865596085274694211316457732733488457675581
37864816347498772361271803508569077342556840114381080615791289767229565722934298027739515044609353834938942783126997639232455471756633973469890220153200106675259124576794005814837050195480568556727036748968547376782037763691407563760894624492800066656610356660069219509873868720944276753019576935022339105680491729061947469541258866262691728588159894768556743115238728463298214004007638276461305375272723509950909295171073810228041453836119389452901868773906682346610297990976816023795680291198385600912916140836780241136995122624142228271293713380794351102151727040497315609186992445097282835560698270377416730133421108782270165812086324476541688053383747854892290375041215576382133649697951346139468801981435987449342540490476520004356482604603156897189536715599310847832300228645635991743905612424165156499098852747041410424810898820303124561117115138027172401352424768544359188036051624859642568602857074371621615231508806022445880822887035524594125784843472499315417101339372683554705!
2556402531736647930226333329779322970172226851848774542330947645482588568856365632866990932896072622721774651397401260852383715789464612649715549584755877967077228105534575389652960928483544740837943723276659632524572944167842680401826648644604173802820121058897425972681873121269190720941045096001248926935930355714141826187287884015301843263274304749124114221315430726153197674498608576707865920496126741814472879127481258587737727078786018195229626943584603283543266501560449068689095824470668638182459947750186151668201937887155537251086580367865869169794110276632073131196915848730412028643786214573673239420367825608989504478857068816509590448868454154721326581476080090319222466843558763364624178795679467177702453553754867766934473439103370660294361344249612384767368507892158363956651143324700282691320602549521018433282045305934177259788654210446635567241436132152527066289867159025622531673679932928800428220902922797749698833880369459992660233491888685138411576752904626156616!
7879067415004412013981026431670302781024110519975053809251441552664436
69511960725461683923540509722083284499949640693261032175088071770752625715514447955826557712145788335413096605846094990518354192078468581049551182463356462320362254575228504832260154014711494047728231908988560471959432227430757037756982849761179510606564990760482550523650166051816257559911846873631604330683415998718783128129596202153860989135995808487768515967505016694762801648802643195317228190038723096817378214656316865358863028621101166843344133382307558191936382436293288042783143055605310850704624642119614021936697883455897446258843692364552938560002554151859669932279793361668525062215759746324370745332741416494216309012640576985343301010920693829503909820353838566004944067715341098119987942805455022906977753661010950067359395252301633449452356106825072997912177918436374814062844277968100041756635947420416768117482017672896904337064967352263106026535711007597395234736036298428984015565943223054875356151110866963436262165440575641171353835890872173070596039040597793849103!
6511919613427919440128566811728681598359611378946010125676443438082247070490023185084235213042639811189709583244714164597763501116307625848095008701493449513079965992954097684158860941655430946986067589018614170778141474686227232765295039894254082902355866495212825729079245813851076586228786055451349969723070416315974239445690393038118202725795105519781134231015178521890424346496650894181117354609245262021371137195676841189511521902507335137574217501870437614126135353094126084804313051200792975952140413789628883243769036385641778001621627161816525820367941544906943954307963287899647807608555063949916962195279711901809586155318819699397668051655460229580017948563694384203283404278464809965543259459238690367209746182768863741318400635630079422528479763675001256831936882203374586645618033291782515494021505175038624781840272794622469978853958785054623954499516285871397211907425386069707751534411059083177014806958625176529207667888992864932992747415768392548848256304173508923017!
7830021888627749768893011895126316581351718791541400833921363828193108
90513335161935706439544652866330201623448052563814824077603326355508933605604424600448291508530124857820023813927063948608641347736250592506112490756973272230629775646381758420315977437335059628471695549795691293756712366294481331175001245733081851145135484800803418840241315976099255095439620940223136901994965329154153506574920570848498252125571529713557668092811195646136559978088820601346208029112370733158792968907458126312371445670688267987605112516475243208816963302038466447547681765253439647124033397270561625609556398590434964457944954309646474894650904853218921957202959530304641111659297682838865324856282312400884964347702070305433879310515186656471481638169102808581722273535412432597450682370732570982895174461945607104365181581376611951264796184795738549839706427908331191819986570317688182995627653773550817877954776508879127635456487418504926881654920604279454152324325660886511272516911299135838141087303316226745988807478038370462171963678341308873095934247673904776115!
9889912257956712537595647561197354433250225864693454090286443805874146990816556768627372738684379408181795610034069923609651994674815469729086964659337397815197016592423352323564154660384433383919403959613788697368038784573559776597114425308399036097207900798630760580587154257630905313701170428256614614685495281159811585622164178827815096648530846108315102795050436008223704805906025671983361940627849132697314224063839734514532482481302835413107495583441838614276255684586088125480974234287749200900923284074396403427236378633963689208585828554168451881115424273277009906456900987457552472790655980562791378646347120152088573295392198298533974664284668582790870097525092718284268985718349096818631094055666564458004884410339465117943424316045651688649670572673975682203639929547305884760554514274877470659782796548949635930733229935544218041325793184982745371731074834945331895091180930044431802916317275150006421421578652674930429539362545414889852345180704736711109357279269686564151!
5247330372610962446862975284476055707383363737823065050122108263369719
88075108604688503412723447485126617770907992748947875452890090521769765393687424062077231822805410023094648908685623168235146372427299039603064435210032573328560886393955516681268213833016293297667977867565964355304845607780497372157920306742053032058019080270143713444189975051565950859669361820946330093699578877393829056171869595929946794361612158914912015600100023035628615125456556655337473041260908149580919711280867262633851226246606419701323482948035644775946957207936024921169383546469389460233454193806163301965877110182440156847825987227072673500423182611186926597876231387289642204868657309045382033085449935852043245724839112219513295051579100247794771004066377248790265571488884796979735704211650757608461878843133796216532312574315213889205811178555699279309188094293890977585613169470402538186573665695832169965958804520851819697274357615667119822924560390469525194933210759734703948101591482517589711265922302574128880726708177188375067355390953641551328142015248781177114!
3482990331360263760426058861193566517767074583372199895330390489523913206826537792242062700977980988946675597187245555567134378469026535534688117079585001850285212937013116146521110736943167979503565208282136148011268238016101664943567473417763748372880157186204006404472629160972382589276215336016567491331895068070627782410045744037363258443107542767652950697756362822852737849484420702113012432345131575749350253578676799853594478041923680272736593544826756955472856905736016664746212557525019317784966206511224147078776162061146449889902668216675721879137381376140174710282246788986725751196803158851173126704053258430387627782238672266925357722036920870135473056068115600903509392779176674839574431420714912447313501225546073126741017945058115343293737286543328733446257836194481924526163541788359009389973024080621051298705913903975571923951674795509616865435663577484529624923813220008329084430833030576850544208103795681435666922927348572003104884179234310446580688953858726743814!
8348731529650107764155019749250703132742148481239788685811391010900182
49090995469436312961812536547193471212283807549865686591583722868146190635410953485232670554975361905608239058159113371445525047330672730624227139457528834854889942185745744889206940232606243287927358564951701444186174018418373263764791714540013359674617405830826393727893490810786198922385870345812513982247649647007752907124326020348000239451017358846468257740913087584515962399002766468223959074529523926428306559428543409143648925344292182513570779144962898224806467709148384090911514589610749383485784880807121433829023487208362107992359895062741806069037687404644726404188015735924795375991112156943009608983444629680686011467029102364120958029018609226074551234104983912532015604860273460988245148648432254832675500529008148734538983631356592773131179328836211007211599961315219911725691681899960870825062191254946131125490776526063207036231897241830859958579387419378708348225070152390938499332559310652190513681059336064861089468207224555422430112657141050828612216101081004581777!
3370919374379062289989596224173452184331945051277917796601154892023168491463596096169598616821643671175696854206049100409006169078167430243904473349850147863726706105693676072525164576151294211311629974748183761004606695897909323902577614980463372076919222259328236178404969041379181515374344242733626249558454232062869892488352165799933907917509540768234595160387324388181683690007433113673985430304052495927693685462083670005908633301178630874993739635956858976116348960179166726796605516490861400434448117862153583360984970496289282786486924122320805439939104334341019564178399582455126220556116076741160779832290603975886951147801032161856144959140749293512916086914534660610841694647041611561923866814230934483566399542264717552387574974421387272230796502177946284221874524930330498681663359921010552038845486760571928329559123276961732134968947802118912623447421785908853884067148125440069829365065220130190616955378432426153891483828105402950511840637876877234184400942653059494517!
9243576698997332896583800866606233847759095576220205602276518653605385
23553843745107362962392805230202092309660311635156423561540681110235178761462251944120644292834726236559729720149026752335625188787684395265448115729660748799089328448221254102231043203399148743725714335765832853961835178566475440498860100869638052637605743373122008063627879684909029232858931089061916343578608313417391750989970302069749103922130012341608004119286343965424842059261793368728098393595453691033567327108159117557620000953888329412084429827988048791349759502464645602759589914538960491613793478214629274343372792133917392650045082328532754264581760884356541693444000522135818927662083734901101455655254750765419245440999083696076591953649097658590724853350795572415019126863241169128496172523992207344038102394497164800269534560712698912118761394346980783745339986140508718446774224385072467747565302711974864997611631470554269949908627401966447196716914845339410483869892524887982754042429506250673628968228718414012061643077451598342070312729529554773622909551294617485271!
2234470014798122561520813387618272137504842851029475431917164100613940641951102791657342571377187439004041141095840789878798904404461976851644290769907751869143999457553589516910142583398277606881010396872889388378260769769793060890147977212381036466122797042608402169383227626157999810338997824213392301933654985075929073513173812305155584841202841580458642632841086867168277333300019440504069264904728711496209116852468157311013195089076753088137512307361334691248783677854840988231753122788964752737176821877754718949497193617169886705177032952380218009812498186868213790758774024671611408948847092538737002437445958225680805931246626754027541259162015320305375204912011733941590613649484830595965803167267126053074489249890875699638549887899850358903093969653023981950673757471120916176006305690623248995700870967418389737506808808329433818457334450433221942723930304551062926217452448650653982326502276340391865248580344424358116859906204695990803209645128297362841941964585968544130!
9488312502263214185237739362983768356629188236700818550345229681440579
10047098507864200993344563020257790457845758293717024580615717476876401591151397438051069231986224003430839448955875342945868129917872545246355207150777620986599884562506237337541104647191226905227917802400306914591234147601051869680919909141140568196163848344923045010391625086270737671697804789678304961864630148339780726372578988568607479278695724854044722803394348865851127379606193227148499391806677636164279563128531387058407750328990470939939450412391322073326179589401732857593094201893206394596340091088212708404504300797764527687883773824063038805764402350867784334848511472475903060732759627614891605412730444105810575073185525275664624892874931737962874173675112765431418609346822051073979524499955719151894154865554439050152450094475833741223946324118462541236464307605473307541779081277193071033387043790903187741744143395480607897586541837691350184148086540593913804215428989992891319827528813044329141191656140211314743019101936539817432943601799002589472391794169311132744!
9747705505691672181532517877188177042759557452697996911060981267139005207396737152872504941955729725489977906325216105689021584618063743516593176512358588718013544481767994391403351578652399551236644537861429052451806318619549941029257734089366106014073084254735554492448990647790454560076578417149637561428933878232924300181018883187075417481890270266958914951360944423554404590916874507990411672185000108429363077474471068420797352625851285365136113550409211298297588518833048013864974048212629382441554086329834143041439425173959119737908828047230575692334557377155699464110327662203205163554998750976975166979530480061796012084157972090982327314624752091987009449682970034606797877844453437089871292262672756084787395295947937340933641177319173242887466831448233312906571493854553875804091553935301730388262230496279950082346765094942310811751447673952956877248348408805252741249283529896505149682687924376728425533190992108762990522601827922090289239622979026157696883648192870317514!
5314086183266916645372130046603087776005949156839867209518985438312000
49487514423524714689271847088143405604240557364907382090174607735584196817532368631124721481144986629551768078434606267570008018013324128127287296000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000!
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

regards, tom lane

Bruce Momjian wrote:

> I tried your \g test and the file size difference is the length of the
> dashed line in the file, not the number of digits display, which are
> both 4096. One has 12550 dashes, the other 19950 dashes.

So this is a psql bug? I can count the correct number of chars with
SPI:

alvherre=# create or replace function factorial_length(int) returns int language plphp
alvherre-# as $$ $r = spi_exec("select factorial($args[0])");
alvherre$# $row = spi_fetch_row($r);
alvherre$# return strlen($row['factorial']); $$;

alvherre=# select factorial_length(4000);
factorial_length
------------------
12674
(1 fila)

alvherre=# select factorial_length(6000);
factorial_length
------------------
20066
(1 fila)

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> So this is a psql bug?

Not here. Do you see it?

regards, tom lane

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > So this is a psql bug?
>
> Not here. Do you see it?

Actually, no. If I cut'n paste the number from psql to

cat > foo
<shift> <insert>

then only 4096 chars are copied. (Amusingly, I can't add a newline to
^D and close the file. I must delete one char to do that.)

However if I open vim and paste there, the whole 12000+delta chars are
copied.

So it must be a bug in Bruce's procedure I guess ...

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Please try Tom's \g test:

test=> select factorial(4000)
test-> \g /tmp/x
test=> \q
$ wc -c /tmp/x
20881 /tmp/x

Do you see a number greater than 20881, something like 30000?

---------------------------------------------------------------------------

Alvaro Herrera wrote:
> Tom Lane wrote:
> > Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > > So this is a psql bug?
> >
> > Not here. Do you see it?
>
> Actually, no. If I cut'n paste the number from psql to
>
> cat > foo
> <shift> <insert>
>
> then only 4096 chars are copied. (Amusingly, I can't add a newline to
> ^D and close the file. I must delete one char to do that.)
>
> However if I open vim and paste there, the whole 12000+delta chars are
> copied.
>
> So it must be a bug in Bruce's procedure I guess ...
>
> --
> Alvaro Herrera http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

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

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Actually, no. If I cut'n paste the number from psql to
> cat > foo
> <shift> <insert>
> then only 4096 chars are copied. (Amusingly, I can't add a newline to
> ^D and close the file. I must delete one char to do that.)

Hmm, cut buffer limitation in X or someplace? I definitely get the
right number of characters into the file written with \g, and what looks
like a reasonable number of screensful of plain psql output. If Bruce
is seeing the right number of dashes and the wrong number of data
characters in his \g output then *something* is pretty weird there.

regards, tom lane

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > Actually, no. If I cut'n paste the number from psql to
> > cat > foo
> > <shift> <insert>
> > then only 4096 chars are copied. (Amusingly, I can't add a newline to
> > ^D and close the file. I must delete one char to do that.)
>
> Hmm, cut buffer limitation in X or someplace? I definitely get the
> right number of characters into the file written with \g, and what looks
> like a reasonable number of screensful of plain psql output. If Bruce
> is seeing the right number of dashes and the wrong number of data
> characters in his \g output then *something* is pretty weird there.

Well, I just tried the \g test and it is correct (12675 digits or so).

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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

Alvaro Herrera wrote:
> Tom Lane wrote:
> > Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > > Actually, no. If I cut'n paste the number from psql to
> > > cat > foo
> > > <shift> <insert>
> > > then only 4096 chars are copied. (Amusingly, I can't add a newline to
> > > ^D and close the file. I must delete one char to do that.)
> >
> > Hmm, cut buffer limitation in X or someplace? I definitely get the
> > right number of characters into the file written with \g, and what looks
> > like a reasonable number of screensful of plain psql output. If Bruce
> > is seeing the right number of dashes and the wrong number of data
> > characters in his \g output then *something* is pretty weird there.
>
> Well, I just tried the \g test and it is correct (12675 digits or so).

I just tested from a standalone backend:

backend> select pow(10::numeric, 131071) + 1

and got 4095 zeros and no trailing '1' (wrong), so it isn't psql, it
must be something in the backend.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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 <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> I just tested from a standalone backend:
> backend> select pow(10::numeric, 131071) + 1
> and got 4095 zeros and no trailing '1' (wrong), so it isn't psql, it
> must be something in the backend.

If the backend is truncating the result length, I don't see why psql
would decide it needs 12K dashes for the header. There's something
awfully fishy going on in your machine.

Try something like

regression=# select length((pow(10::numeric, 131071))::text);
length
--------
131089
(1 row)

If that gives the right answer then the NUMERIC code is off the hook,
and what you've got is a strange limitation on output column length.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > I just tested from a standalone backend:
> > backend> select pow(10::numeric, 131071) + 1
> > and got 4095 zeros and no trailing '1' (wrong), so it isn't psql, it
> > must be something in the backend.
>
> If the backend is truncating the result length, I don't see why psql
> would decide it needs 12K dashes for the header. There's something
> awfully fishy going on in your machine.
>
> Try something like
>
> regression=# select length((pow(10::numeric, 131071))::text);
> length
> --------
> 131089
> (1 row)
>
> If that gives the right answer then the NUMERIC code is off the hook,
> and what you've got is a strange limitation on output column length.

test=> select length((pow(10::numeric, 131071))::text);
length
--------
131089
(1 row)

Looks good. From psql I just tried:

SELECT repeat('x', 4000);

and got 4k x's, and SELECT repeat('x', 8000) returns 8k x's, so that works.

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

On Fri, 2005-12-02 at 14:09 -0500, Bruce Momjian wrote:

> I ran your SELECT pow(10::numeric, 131071), and gain, 4096 0's are
> displayed on my screen. SELECT pow(10::numeric, 7000) and SELECT
> pow(10::numeric, 10000) generate identical displays on my screen.

Are you saying there is a bug with or without my patch?

Can we get the usual release levels/cvstip, ports, etc.

Are you connecting across network/ protocol differences...

[Just out of interest, has anybody ever used a number bigger than 10^20
in an application? My understanding is that the number of atoms in the
universe is around 10^80. Accuracy is needed during calculation, but
much less so during storage.]

Thanks,

Best Regards, Simon Riggs

Simon Riggs wrote:
> On Fri, 2005-12-02 at 14:09 -0500, Bruce Momjian wrote:
>
> > I ran your SELECT pow(10::numeric, 131071), and gain, 4096 0's are
> > displayed on my screen. SELECT pow(10::numeric, 7000) and SELECT
> > pow(10::numeric, 10000) generate identical displays on my screen.
>
> Are you saying there is a bug with or without my patch?

Without.

> Can we get the usual release levels/cvstip, ports, etc.

Sorry, BSD/OS 4.3.1, CVS HEAD, no patches.

> Are you connecting across network/ protocol differences...

psql and the backend are on the same machine.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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 <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Tom Lane wrote:
>> If that gives the right answer then the NUMERIC code is off the hook,
>> and what you've got is a strange limitation on output column length.

> test=> select length((pow(10::numeric, 131071))::text);
> length
> --------
> 131089
> (1 row)

> Looks good. From psql I just tried:

> SELECT repeat('x', 4000);

> and got 4k x's, and SELECT repeat('x', 8000) returns 8k x's, so that works.

Curiouser and curiouser. How about if you repeat 4k or 8k '1's? If the
behavior is different for letters and digits then I'd look at the column
justification logic in psql's printing code.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Tom Lane wrote:
> >> If that gives the right answer then the NUMERIC code is off the hook,
> >> and what you've got is a strange limitation on output column length.
>
> > test=> select length((pow(10::numeric, 131071))::text);
> > length
> > --------
> > 131089
> > (1 row)
>
> > Looks good. From psql I just tried:
>
> > SELECT repeat('x', 4000);
>
> > and got 4k x's, and SELECT repeat('x', 8000) returns 8k x's, so that works.
>
> Curiouser and curiouser. How about if you repeat 4k or 8k '1's? If the

1's print just fine too.

> behavior is different for letters and digits then I'd look at the column
> justification logic in psql's printing code.

Again, I checked on a stand-alone backend and saw the same failures, so
it isn't psql.

Wow, check this out:

test=> SELECT CAST (pow(10::numeric, 10000) + 1 AS TEXT)

It works fine! I have all the digits, and the trailing 1.0:

000001.0000000000000000

while SELECT pow(10::numeric, 10000) fails.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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 <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Wow, check this out:
> test=> SELECT CAST (pow(10::numeric, 10000) + 1 AS TEXT)
> It works fine! I have all the digits, and the trailing 1.0:
> 000001.0000000000000000
> while SELECT pow(10::numeric, 10000) fails.

That's just about as wacky as can be, because numeric_text() is
implemented on top of numeric_out() ... there's no way that numeric_out
can be delivering the wrong answer if the cast produces the right text.
So somewhere between numeric_out and the delivery to the client,
something's getting confused. I think it's time you got out your
debugger and started tracing through the backend ...

regards, tom lane

On Fri, Dec 02, 2005 at 04:30:54PM -0500, Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Wow, check this out:
> > test=> SELECT CAST (pow(10::numeric, 10000) + 1 AS TEXT)
> > It works fine! I have all the digits, and the trailing 1.0:
> > 000001.0000000000000000
> > while SELECT pow(10::numeric, 10000) fails.
>
> That's just about as wacky as can be, because numeric_text() is
> implemented on top of numeric_out() ... there's no way that numeric_out
> can be delivering the wrong answer if the cast produces the right text.
> So somewhere between numeric_out and the delivery to the client,
> something's getting confused. I think it's time you got out your
> debugger and started tracing through the backend ...

Bruce, have you run a process trace on the backend to see if write()
(or whatever) is writing the correct number of characters? What
exactly is your output device and how are you connected to the
machine that runs the backend (ssh to a remote box from an xterm,
sitting in front of the box's VT52 serial console, etc.)?

If you run the query that fails in a standalone backend, do you get
something like "(typeid = 1700, len = -1, typmod = -1, byval = f)"
at the end of the line, or is that part truncated too?

--
Michael Fuhr

Michael Fuhr wrote:
> On Fri, Dec 02, 2005 at 04:30:54PM -0500, Tom Lane wrote:
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > Wow, check this out:
> > > test=> SELECT CAST (pow(10::numeric, 10000) + 1 AS TEXT)
> > > It works fine! I have all the digits, and the trailing 1.0:
> > > 000001.0000000000000000
> > > while SELECT pow(10::numeric, 10000) fails.
> >
> > That's just about as wacky as can be, because numeric_text() is
> > implemented on top of numeric_out() ... there's no way that numeric_out
> > can be delivering the wrong answer if the cast produces the right text.
> > So somewhere between numeric_out and the delivery to the client,
> > something's getting confused. I think it's time you got out your
> > debugger and started tracing through the backend ...
>
> Bruce, have you run a process trace on the backend to see if write()
> (or whatever) is writing the correct number of characters? What
> exactly is your output device and how are you connected to the
> machine that runs the backend (ssh to a remote box from an xterm,
> sitting in front of the box's VT52 serial console, etc.)?
>
> If you run the query that fails in a standalone backend, do you get
> something like "(typeid = 1700, len = -1, typmod = -1, byval = f)"
> at the end of the line, or is that part truncated too?

I found the cause. I traced into printf then realized I was not in libc
but port/snprintf.c, and I see 4096 defined for those buffers. I will
work on a patch to make it dynamic. At the time I think there was
thought that 4096 was as large as it ever needed to be, but obviously
this was wrong. I think Win32 would see the same failure because it used
port/snprintf.c too.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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 <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> I found the cause. I traced into printf then realized I was not in libc
> but port/snprintf.c, and I see 4096 defined for those buffers.

Uh, how is control getting to snprintf? I don't see that used either
in numeric.c or in printtup.c.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > I found the cause. I traced into printf then realized I was not in libc
> > but port/snprintf.c, and I see 4096 defined for those buffers.
>
> Uh, how is control getting to snprintf? I don't see that used either
> in numeric.c or in printtup.c.

I am seeing it in the standalone backend here:

debugtup (slot=0x856e0b0, self=0x84306d0) at printtup.c:548
548 printatt((unsigned) i + 1, typeinfo->attrs[i], value);
(gdb) s
printatt (attributeId=1, attributeP=0x856efa4, value=0x857201c "1", '0' <repeats 199 times>...) at printtup.c:480
480 printf("\t%2d: %s%s%s%s\t(typeid = %u, len = %d, typmod = %d, byval = %c)\n",

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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 <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Tom Lane wrote:
>> Uh, how is control getting to snprintf? I don't see that used either
>> in numeric.c or in printtup.c.

> I am seeing it in the standalone backend here:

> debugtup (slot=0x856e0b0, self=0x84306d0) at printtup.c:548
> 548 printatt((unsigned) i + 1, typeinfo->attrs[i], value);

That would only affect a standalone backend, however, not normal
operation.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Tom Lane wrote:
> >> Uh, how is control getting to snprintf? I don't see that used either
> >> in numeric.c or in printtup.c.
>
> > I am seeing it in the standalone backend here:
>
> > debugtup (slot=0x856e0b0, self=0x84306d0) at printtup.c:548
> > 548 printatt((unsigned) i + 1, typeinfo->attrs[i], value);
>
> That would only affect a standalone backend, however, not normal
> operation.

Ah, psql itself is using the same buggy snprintf.c. That would explain
the long dash line, but short digits.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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 wrote:
> > If you run the query that fails in a standalone backend, do you get
> > something like "(typeid = 1700, len = -1, typmod = -1, byval = f)"
> > at the end of the line, or is that part truncated too?
>
> I found the cause. I traced into printf then realized I was not in libc
> but port/snprintf.c, and I see 4096 defined for those buffers. I will
> work on a patch to make it dynamic. At the time I think there was
> thought that 4096 was as large as it ever needed to be, but obviously
> this was wrong. I think Win32 would see the same failure because it used
> port/snprintf.c too.

My Win32 8.1 platform is fine so Win32 must not use port/snprintf.c.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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 <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Tom Lane wrote:
>> That would only affect a standalone backend, however, not normal
>> operation.

> Ah, psql itself is using the same buggy snprintf.c.

Doh. OK, we gotta fix it then. But what are you going to do when you
can't malloc enough memory? You can't ereport in a client environment,
and there's no API for printf to report failure.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Tom Lane wrote:
> >> That would only affect a standalone backend, however, not normal
> >> operation.
>
> > Ah, psql itself is using the same buggy snprintf.c.
>
> Doh. OK, we gotta fix it then. But what are you going to do when you
> can't malloc enough memory? You can't ereport in a client environment,
> and there's no API for printf to report failure.

Yep, I am digging through snprintf.c now to try find a solution.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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 <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Tom Lane wrote:
>> Doh. OK, we gotta fix it then. But what are you going to do when you
>> can't malloc enough memory? You can't ereport in a client environment,
>> and there's no API for printf to report failure.

> Yep, I am digging through snprintf.c now to try find a solution.

Well, there's always the option of going back to plan B, which is not
using printf for strings that might be long. AFAICS it would only be
a convenience for psql, since it's not doing any real conversions,
just inserting the right number of spaces.

regards, tom lane

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Yep, I am digging through snprintf.c now to try find a solution.

The cleanest solution is probably to fix things so that dopr_outch is
aware of whether it's working for sprintf or fprintf, and can dump the
buffer directly to the file when it gets full in the fprintf case.
Its existing API would need to be changed a bit ... maybe pass it a
struct containing what it needs, instead of having all the layers of
code know what to pass.

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

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Yep, I am digging through snprintf.c now to try find a solution.
>
> The cleanest solution is probably to fix things so that dopr_outch is
> aware of whether it's working for sprintf or fprintf, and can dump the
> buffer directly to the file when it gets full in the fprintf case.
> Its existing API would need to be changed a bit ... maybe pass it a
> struct containing what it needs, instead of having all the layers of
> code know what to pass.

OK, snprintf.c fixed. I added a 'stream' and outlen parameter to all
the calls, and cleaned up the switch() statement that was outputing
twice. When we were outputing just to a string, it didn't matter, but
now that we are also outputting to a stream, it does.

Passed regression and initdb tests, and factorial(4000) works!

(I could have done the struct but that seemed too invasive.)

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

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(at)svana(dot)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.

Martijn van Oosterhout <kleptog(at)svana(dot)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

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> (I could have done the struct but that seemed too invasive.)

I think it'd be a lot cleaner with the struct. Mind if I take another
pass at it?

regards, tom lane

On Sat, Dec 03, 2005 at 11:43:00 -0500,
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Martijn van Oosterhout <kleptog(at)svana(dot)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.

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > (I could have done the struct but that seemed too invasive.)
>
> I think it'd be a lot cleaner with the struct. Mind if I take another
> pass at it?

OK, you want my patch or want me to apply and then you can modify?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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 <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Tom Lane wrote:
>> I think it'd be a lot cleaner with the struct. Mind if I take another
>> pass at it?

> OK, you want my patch or want me to apply and then you can modify?

You sent out your patch already --- I've been working from that.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Tom Lane wrote:
> >> I think it'd be a lot cleaner with the struct. Mind if I take another
> >> pass at it?
>
> > OK, you want my patch or want me to apply and then you can modify?
>
> You sent out your patch already --- I've been working from that.

OK, thanks.
--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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 <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> OK, snprintf.c fixed. I added a 'stream' and outlen parameter to all
> the calls, and cleaned up the switch() statement that was outputing
> twice. When we were outputing just to a string, it didn't matter, but
> now that we are also outputting to a stream, it does.

I found a whole bunch more problems than this :-(. I've committed a
cleaned-up version that seems to work correctly in a simple standalone
testbed, but it'd be a good idea to exercise it inside PG as well.
Can you try regression tests and the factorial() problem on CVS tip?

The problems are sufficiently bad that it might be a good idea to
backport the fixes into 8.0 and before as well --- but I note that
the ABI is different (pg_snprintf vs snprintf, etc) so this requires
a bit of investigation rather than just committing the file as-is.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > OK, snprintf.c fixed. I added a 'stream' and outlen parameter to all
> > the calls, and cleaned up the switch() statement that was outputing
> > twice. When we were outputing just to a string, it didn't matter, but
> > now that we are also outputting to a stream, it does.
>
> I found a whole bunch more problems than this :-(. I've committed a
> cleaned-up version that seems to work correctly in a simple standalone
> testbed, but it'd be a good idea to exercise it inside PG as well.
> Can you try regression tests and the factorial() problem on CVS tip?

Thanks. Tested 8.1.1 and CVS tip and all compile, and regression pass.
I also tested the factorial test and the result looks perfect, thanks!

> The problems are sufficiently bad that it might be a good idea to
> backport the fixes into 8.0 and before as well --- but I note that
> the ABI is different (pg_snprintf vs snprintf, etc) so this requires
> a bit of investigation rather than just committing the file as-is.

Not as many 8.0.X platforms used *printf because we didn't test %$ for
its use on that release, so my bet is that very few platforms would be
using it.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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 <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Tom Lane wrote:
>> The problems are sufficiently bad that it might be a good idea to
>> backport the fixes into 8.0 and before as well --- but I note that
>> the ABI is different (pg_snprintf vs snprintf, etc) so this requires
>> a bit of investigation rather than just committing the file as-is.

> Not as many 8.0.X platforms used *printf because we didn't test %$ for
> its use on that release, so my bet is that very few platforms would be
> using it.

Hm. One of the main problems I found was incorrect results for
LONGLONG_MIN (-2^63). I'm rather tempted to add a test case for
that to the int8 regression test and see if any platforms fail ;-)

regards, tom lane

I wrote:
> Hm. One of the main problems I found was incorrect results for
> LONGLONG_MIN (-2^63). I'm rather tempted to add a test case for
> that to the int8 regression test and see if any platforms fail ;-)

Done ... let me know whether the back branches still pass regression
for you ;-)

regards, tom lane

Tom Lane wrote:
> I wrote:
> > Hm. One of the main problems I found was incorrect results for
> > LONGLONG_MIN (-2^63). I'm rather tempted to add a test case for
> > that to the int8 regression test and see if any platforms fail ;-)
>
> Done ... let me know whether the back branches still pass regression
> for you ;-)

I checked back to 7.3 and everything passed. I did a cvs update,
configure, gmake, and regression run for each branch.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)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 <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Tom Lane wrote:
>> Done ... let me know whether the back branches still pass regression
>> for you ;-)

> I checked back to 7.3 and everything passed. I did a cvs update,
> configure, gmake, and regression run for each branch.

[ digs a bit deeper... ] Actually, it appears that that bug didn't
exist before 8.1; it was introduced here:

2005-03-16 22:18 momjian

* src/port/snprintf.c: Factor duplicate snprintf code into
functions.

by an ill-considered removal of an unsigned local variable.

regards, tom lane

>> 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

[ 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 TABLE
test=> INSERT INTO test SELECT pow(10::numeric, 10000) + 1;
INSERT 0 1
test=> SELECT log(x) FROM test;
log
------------------------
10000.0000000000000000
(1 row)

test=> SELECT x % 10 FROM test;
?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 numeric format
test=> 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 format
test=> SELECT factorial(400);
ERROR: value overflows numeric 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(at)candle(dot)pha(dot)pa(dot)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 <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> The limit seems to be around 150k digits:

It's exactly 10^(128K), as I've mentioned more than once.

> 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,

Only if they declared their columns as numeric(N) and not just plain
unconstrained numeric. Not to mention the possibility that they're
doing the same thing you just did, ie computing values and returning
them to the client without ever storing them in a table. So I don't
think the above reasoning is defensible.

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

It looks like the limit would be about factorial(256).

The question remains, though, is this computational range good for
anything except demos?

regards, tom lane

On 12/5/05, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Not only does 4000! not work, but 400! doesn't even work. I just lost
> > demo "wow" factor points!
>
> It looks like the limit would be about factorial(256).
>
> The question remains, though, is this computational range good for
> anything except demos?

I've hesitated commenting, because I think it might be a silly reason,
but perhaps it's one other people share. ... I use PG as a
calculator for big numbers because it's the only user friendly thing
on my system that can do factorial(300) - factorial(280). I'd rather
use something like octave, but I've found its pretty easy to escape
its range. If the range for computation is changed, then I'll
probably keep an old copy around just for this, though I'm not quite
sure how much I'd be affected..

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > The limit seems to be around 150k digits:
>
> It's exactly 10^(128K), as I've mentioned more than once.
>
> > 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,
>
> Only if they declared their columns as numeric(N) and not just plain
> unconstrained numeric. Not to mention the possibility that they're
> doing the same thing you just did, ie computing values and returning
> them to the client without ever storing them in a table. So I don't
> think the above reasoning is defensible.
>
> > Not only does 4000! not work, but 400! doesn't even work. I just lost
> > demo "wow" factor points!
>
> It looks like the limit would be about factorial(256).
>
> The question remains, though, is this computational range good for
> anything except demos?

I can say that the extended range is good for finding *printf problems. ;-)

Let me also add that as far as saving disk space, this is the _big_
improvement on the TODO list:

* Merge xmin/xmax/cmin/cmax back into three header fields

Before subtransactions, there used to be only three fields needed to
store these four values. This was possible because only the current
transaction looks at the cmin/cmax values. If the current transaction
created and expired the row the fields stored where xmin (same as
xmax), cmin, cmax, and if the transaction was expiring a row from a
another transaction, the fields stored were xmin (cmin was not
needed), xmax, and cmax. Such a system worked because a transaction
could only see rows from another completed transaction. However,
subtransactions can see rows from outer transactions, and once the
subtransaction completes, the outer transaction continues, requiring
the storage of all four fields. With subtransactions, an outer
transaction can create a row, a subtransaction expire it, and when the
subtransaction completes, the outer transaction still has to have
proper visibility of the row's cmin, for example, for cursors.

One possible solution is to create a phantom cid which represents a
cmin/cmax pair and is stored in local memory. Another idea is to
store both cmin and cmax only in local memory.

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

On Mon, Dec 05, 2005 at 11:59:10PM -0500, Bruce Momjian wrote:
> Tom Lane wrote:
> > It looks like the limit would be about factorial(256).
> >
> > The question remains, though, is this computational range good for
> > anything except demos?
>
> I can say that the extended range is good for finding *printf problems. ;-)

Might anybody be calculating permutations or combinations with the
textbook functions that use factorials? Not a show-stopper since
those calculations can be optimized (at least the basic formulas I
know), but somebody might get bit by the change. Maybe the release
notes could mention the new upper limit of factorial().

--
Michael Fuhr

Michael Fuhr <mike(at)fuhr(dot)org> writes:
> On Mon, Dec 05, 2005 at 11:59:10PM -0500, Bruce Momjian wrote:
>> Tom Lane wrote:
>>> The question remains, though, is this computational range good for
>>> anything except demos?
>>
>> I can say that the extended range is good for finding *printf problems. ;-)

> Might anybody be calculating permutations or combinations with the
> textbook functions that use factorials?

Hm ... between that, the possible crypto connection, and John's personal
testimony that he actually uses PG for calculations in this range, I'm
starting to lean to the idea that we shouldn't cut the range.

We could get the same 2-byte savings (in fact 3 bytes on average,
considering alignment issues) by implementing a 2-byte length word
format for numeric. I had originally hoped to do both things to save
an average 5 bytes per numeric, which is starting to get to the point of
actually being interesting ;-). But maybe we should just do the part
that we can do without removing any user-visible functionality.

regards, tom lane

Tom Lane wrote:

> Hm ... between that, the possible crypto connection, and John's
> personal
> testimony that he actually uses PG for calculations in this range, I'm
> starting to lean to the idea that we shouldn't cut the range.

Just to be clear, this John has yet to use NUMERIC for any
calculations, let alone in that range. (I've only used NUMERIC for
importing real-valued data where I didn't want to lose precision with a
floating point representation, for instance, decimal latitude-longitude
values.)

There was this post, though:

Gregory Maxwell wrote:

> I've hesitated commenting, because I think it might be a silly reason,
> but perhaps it's one other people share. ... I use PG as a
> calculator for big numbers because it's the only user friendly thing
> on my system that can do factorial(300) - factorial(280). I'd rather
> use something like octave, but I've found its pretty easy to escape
> its range. If the range for computation is changed, then I'll
> probably keep an old copy around just for this, though I'm not quite
> sure how much I'd be affected..

- John D. Burger
MITRE

"John D. Burger" <john(at)mitre(dot)org> writes:
> Tom Lane wrote:
>> Hm ... between that, the possible crypto connection, and John's
>> personal testimony

> Just to be clear, this John has yet to use NUMERIC for any
> calculations, let alone in that range.

My mistake, got confused as to who had said what.

The point remains though: in discussing this proposed patch, we were
assuming that 10^508 would still be far beyond what people actually
needed. Even one or two reports from the list membership of actual
use of larger values casts a pretty big shadow on that assumption.

regards, tom lane

Tom Lane wrote:
> "John D. Burger" <john(at)mitre(dot)org> writes:
> > Tom Lane wrote:
> >> Hm ... between that, the possible crypto connection, and John's
> >> personal testimony
>
> > Just to be clear, this John has yet to use NUMERIC for any
> > calculations, let alone in that range.
>
> My mistake, got confused as to who had said what.
>
> The point remains though: in discussing this proposed patch, we were
> assuming that 10^508 would still be far beyond what people actually
> needed. Even one or two reports from the list membership of actual
> use of larger values casts a pretty big shadow on that assumption.

Agreed. I would like to see us hit the big savings first, like merging
cmin/cmax (4 bytes per row) and reducing the varlena header size (2-3
bytes for short values), before we start going after disk savings that
actually limit our capabilites.

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

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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461