[ic] Incorrect treatment of sizing of bigint/int8 in IC w/ Postgres

[Peter]

On 18/03/10 03:47, Chris Keane wrote:
> 
> IC 5.7.2, Postgres 8.3
> 
> In Table::DBI.pm the MAX_FIELD_LENGTH for each element in a table in a
> Postgres database is discovered with
> 
> SELECT a.attnum,t.typname,a.attlen,a.atttypmod,a.attname FROM pg_class
> c,pg_attribute a,pg_type t WHERE c.relname='_TABLE_' AND a.attnum > 0
> AND a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY a.attnum;
> 
> at line 225. In the case of a field with type bigint or int8, the attlen
> returned is 8, which is quite correct. That indicates that the maximum
> value is 8 bytes of storage, a maximum value of 2^63, or
> 9223372036854775807. That's great!
> 
> On the other hand, down at the if statement at line 757, namely:
>                         if( $stype=~/numeric/i  or $stype=~/varbit/i ){
>                                 $scfg->{LENGTH} = $slenvar;
>                         }
>                         else {
>                                 $scfg->{LENGTH} = $len;
>                         }
> IC decides that the length of the bigint can't be more than 8 digits,
> which yields a maximum value of 99999999, which as you can see yields a
> much lower maximum than 9223372036854775807.
> 
> Trying to write a slice containing a bigint > 99999999 yields an error:
> # Vend::Table::DBI - Length Exception! - Data length: 9 Field length: 8
> # Vend::Table::DBI - Length Exception - Table: userdb, Field: <field
> name>. Action to take: truncate_log
> which then proceeds to lop the final digit off the bigint and stores it.
> For example, if you try to write 123456789, IC will produce this error
> and actually write 12345678 instead.
> See how that could be a problem?
> 
> Since this was a rather critical error for us, I applied the simplest
> possible hack just to stop the bleeding, namely to insert
>                         elsif ($stype =~/int8/i) {
>                                 $scfg->{LENGTH} = 19;
>                         }
> into the above the above if statement. I suppose a better approach would
> have to be do something like
>    elsif($ctype =~ /^int/i) {
>             $scfg->{LENGTH} = length(2 ** (($len * 8)-1));
>    }
> 
> But either way it's a significant problem and I just wanted to point it
> out so it can be fixed for future releases.


Agreed this should be fixed.  As a workaround you can comment out the
following line in userdb.pgsql:
Database  userdb  LENGTH_EXCEPTION_DEFAULT  truncate_log

That will tell IC to ignore the length exception and pass the data onto
the db as-is.  Of course if the data length really *is* too long then it
will die instead of truncating the data, but this may actually be
preferable to you anyways.


Peter