[isf-wifidog] Incoming/outgoing wrap-around in database

[Benoit Grégoire]

On Sunday 20 February 2005 18:56, Mina Naguib wrote:
> I think the column types in the database need to be changed for input
> and output.
>
> Currently they're int4, but since they're signed that means the maximum
> value they will hold is exceeded after ~ 2.1 Gigs of transfer.
>
> Several records in the database have looped-around and now show negative
> values (select * from connections where incoming < 0 or outgoing < 0)

Yes, I've been meaning to fix  this by changing it to int8 (I first ran into 
it when writing the statistics).  Unfortunately, it's not possible to change 
the field size on the fly.  I'll have to put the auth server offline, dump 
all the data of that table, change the field size and restore the table.   
Finding the appropriate time to do this is getting kind of hard, unless I get 
up really early.

I was thinking I'd do it when I setup the new auth server, which will require 
downtime anyway.

> The data type in wifidog is "unsigned long long int" which will
> typically occupy 8 bytes of memory and hold the range of 0 to 2^64 (a
> ridiculously large number)
>
> The DB field needs to be expanded to match that if at all possible.  As
> far as I can tell, the integer types in postgresql are all signed, which
> means the biggest of them (bigint/int8) will hold the range -(2^63) to
> 2^63.  That is basically half the maximum wifidog could possibly report.
>
> If anyone knows PostgreSQL better maybe they can recommend a field type
> that will gracefully handle the 0 - 2^64 range, otherwise we might have
> to settle for a signed int8.  That's still plenty (maxes out at 16
> exabytes) and there's a good chance we will never, ever see it
> wrap-around unless our software is still in use several millennia from
> now :)

The alternative is to use a arbitrary precision numeric type, but the 
performance hit from that is not worth it at all.

-- 
Benoit Grégoire, http://benoitg.coeus.ca/
-------------- section suivante --------------
Une pièce jointe non texte a été nettoyée...
Nom: non disponible
Type: application/pgp-signature
Taille: 189 octets
Desc: non disponible
Url: http://philippeapril.com/pipermail/wifidog/attachments/20050220/829b461c/attachment.pgp