Postgres Pro
Downloads
Home   >   mailing lists

Thread: maxint reached?

maxint reached?

From
Daniel Kalchev
Date:
Has anyone seen this:

ERROR:  dtoi4: integer out of range


on 7.1.3

What worries me, is that at startup time, the log shows:

DEBUG:  database system was shut down at 2002-04-02 23:16:52 EEST
DEBUG:  CheckPoint record at (82, 1928435208)
DEBUG:  Redo record at (82, 1928435208); Undo record at (0, 0); Shutdown TRUE
DEBUG:  NextTransactionId: 517528628; NextOid: 2148849196
DEBUG:  database system is in production state

Note the NextOid, while i /usr/include/machine/limits.h defines INT_MAX as 
2147483647. Are oid really singed ints?

Daniel

PS: This database indeed has an increasing oid counter in that range. Grep 
from the log shows

DEBUG:  NextTransactionId: 386003914; NextOid: 1551075952
DEBUG:  NextTransactionId: 397667914; NextOid: 1643984428
DEBUG:  NextTransactionId: 444453748; NextOid: 1864857132
DEBUG:  NextTransactionId: 450233305; NextOid: 1888540204
DEBUG:  NextTransactionId: 454987662; NextOid: 1917687340
DEBUG:  NextTransactionId: 501775621; NextOid: 2078209580
DEBUG:  NextTransactionId: 517524499; NextOid: 2148849196
DEBUG:  NextTransactionId: 517528628; NextOid: 2148849196

this is from one month ago.

Re: maxint reached?

From
Daniel Kalchev
Date:
An followup to my previous post.

It turned out to be an query containing "oid = somenumber" called from perl script. Is it possible that the default
typeconversion functions do not work as expected?
 

Changing this to "oid = oid(somenumber)" worked as expected.

Daniel

Re: maxint reached?

From
Tom Lane
Date:
Daniel Kalchev <daniel@digsys.bg> writes:
> It turned out to be an query containing "oid = somenumber" called from perl script. Is it possible that the default
typeconversion functions do not work as expected?
 

No, but you do have to cast an oversize value to oid explicitly to
prevent it from being taken as int4, eg

regression=# select oid = 2444444444 from int4_tbl;
ERROR:  dtoi4: integer out of range
regression=# select oid = 2444444444::oid from int4_tbl;
<< works >>

(In releases before about 7.1 you'd have had to single-quote the
literal, too.)

This is one of a whole raft of cases involving undesirable assignment
of types to numeric constants; see past complaints about int4 being used
where int2 or int8 was wanted, numeric vs float8 constants, etc etc.
We're still looking for a promotion rule that does what you want every
time...
        regards, tom lane

Re: maxint reached?

From
Daniel Kalchev
Date:
>>>Tom Lane said:> This is one of a whole raft of cases involving undesirable assignment> of types to numeric
constants;see past complaints about int4 being used> where int2 or int8 was wanted, numeric vs float8 constants, etc
etc.>We're still looking for a promotion rule that does what you want every> time...
 

So in essence this means that my best bet is to again dump/reload the 
database... Even pgaccess has hit this problem as it uses oid=something in the 
queries.

Daniel

Re: maxint reached?

From
Tom Lane
Date:
Daniel Kalchev <daniel@digsys.bg> writes:
> So in essence this means that my best bet is to again dump/reload the 
> database...

Either that or fix your queries to cast the literals explicitly.
        regards, tom lane

Re: maxint reached?

From
Daniel Kalchev
Date:
>>>Tom Lane said:> Daniel Kalchev <daniel@digsys.bg> writes:> > So in essence this means that my best bet is to again
dump/reloadthe > > database...> > Either that or fix your queries to cast the literals explicitly.
 

There is more to it:

customer=# select max(oid) from croute;    max     
--------------2144025472
(1 row)

How to handle this?

Daniel

Re: maxint reached?

From
Daniel Kalchev
Date:
>>>Tom Lane said:> Daniel Kalchev <daniel@digsys.bg> writes:> > So in essence this means that my best bet is to again
dump/reloadthe > > database...> > Either that or fix your queries to cast the literals explicitly.
 

Sorry for the incomplete reply:

this does not work:

customer=# select max(oid) from croute;    max     
--------------2144025472
(1 row)

this does work:

customer=# select oid(max(oid)) from croute;   oid     
------------2150941824
(1 row)


weird, isn't it? I guess max should return the same type as it's arguments, no?

Daniel

Re: maxint reached?

From
Tom Lane
Date:
Daniel Kalchev <daniel@digsys.bg> writes:
> There is more to it:

> customer=# select max(oid) from croute;
>      max     
> -------------
>  -2144025472
> (1 row)

> How to handle this?

Use a more recent Postgres release.  max(oid) behaves as expected in
7.2.  Before that it was piggybacking on max(int4), which meant that
it chose the wrong value once you had any entries with the high bit
set...
        regards, tom lane