maxint reached?

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 type conversion functions do not work as expected?

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

Daniel

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

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

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

Tom Lane said:

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.

There is more to it:

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

How to handle this?

Daniel

Tom Lane said:

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.

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

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