64-Bit Values and DBI

tadman has asked for the wisdom of the Perl Monks concerning the following question:

MySQL has a "BIGINT" column type, which is 64-bit. This, I have found, is useful in AUTO_INCREMENT columns with high churn, such that you will burn through all 2³²-1 possibilities in a relatively short amount of time. Although I don't expect to use much more than say 34-35 bits, I do worry that Perl is going to blow a gasket on large numbers which could be introduced somehow into that. How do you handle these oversize numbers safely using DBI?

split 64 bit number inspired this question, because I had taken for granted Perl's ability to cope with "large" numbers, such as integers bigger than 32 bits. wog pointed out that support for 64-bit math can be compiled in, but is optional. Is this going to be a standard any time soon, at least in the "enabled if availble" capacity?

Comment on 64-Bit Values and DBI

Replies are listed 'Best First'.
Re: 64-Bit Values and DBI by dga (Hermit) on Aug 11, 2001 at 09:43 UTC
On 64 bit archetectures it is the default to set up for 64 bit native numbers. On 32 bit platforms it is a compile time directive to add in the support because it is slower to use 64 bit numbers on these machines. However 32 bit numbers on those machines aren't affected AFAIK. The bigger issue is that perl represents numbers in double precision floating point (64 bit) but the mantissa, the part that matters for integer work, is only 54 bits, with 10 for the exponent. So if you are only going to use 34 or 35 bits then perl compiled default on a 32 bit machine should work just fine since there is no problem until after 2^54. If you overflow this the problems are subtle so be sure you will stay < 2^54. On 64 bit machines you could use the integer pragma to force 64 bit integers to get really big ints but on a 32 bit native this would decrease the range from 2^54 to 2^32. I do not know what use integer does with a 32 bit machine compiled to allow long number support. `use integer; #will represent all numbers as int type disabling floatin +g numbers` [download] As for DBI if i converts int types into native int types to send them along to the database that would be a problem though I don't know what effect using the long int compile option has on DBI. I will look into that Monday if nothing reported here before then.	[reply] [d/l]
Re: 64-Bit Values and DBI by acid06 (Friar) on Aug 11, 2001 at 06:02 UTC
Don't know if I'm pointing you to the right direction, but maybe Math::BigInt might help you with your problem.	[reply]
Re^2: 64-Bit Values and DBI by tadman (Prior) on Aug 11, 2001 at 23:39 UTC
That's one way, as has been pointed out, but how would this work in the context of a DBI call? Would this interoperate with a `bind_param` call, for example? What about something like `selectall_arrayref`?	[reply] [d/l] [select]
Re: 64-Bit Values and DBI by mattr (Curate) on Aug 12, 2001 at 16:07 UTC
I have another potential strategy.. and maybe Mysql isn't the best choice for a site with such a huge number of inserts going on. If you use PostgreSql you might be able to produce your own trigger code that increments a number represented using say base 62 (0..9,a..z,A..Z) or maybe just hexadecimal (0..9,a..f). You should be able to hit some pretty high numbers with that (of course you need more than 8 bytes since bitz iz bitz), and no worries about DBI internal code as you store in the database as a string. PostreSql.org has some info about writing triggers in PL/SQL or PL/Perl, don't know how fast this is but looks interesting. They do recommend writing triggers in C if you are worried about performance..	[reply]