why does pg_size_pretty return negative value?

I was playing with pg_size_pretty() and I've discovered that when I pass it a great value it starts to return negative开发者_JAVA百科 value. This is my test:

select pg_size_pretty(9223370000000000000); -- "8388606 TB"
select pg_size_pretty(9223371000000000000); -- "8388607 TB"
select pg_size_pretty(9223372000000000000); -- "-8388607 TB"

Can you explain me why? Thanks.

The highest signed 64 bit int is 922337203685477587. That's but a tiny bit more than the number in question. Surely there's an overflow somewhere in pg_size_pretty.

Based on the code mentioned in a comment, pg_size_pretty is attempting to round the number and does so using an intermediary value that's larger than the max signed 64-bit int. 9223372000000000000 + 1024*1024*1024*1024/2 = 9223372549755813888, which is larger than 922337203685477587.

Update: Added second paragraph and clarified that the overflow isn't in the caller.

You are not overflowing, pg_size_pretty is overflowing. The pg_size_pretty function is supposed to take a bigint:

pg_size_pretty(bigint)
text
Converts a size in bytes into a human-readable format with size units

And 9223372000000000000 < 9223372036854775808 so 9223372000000000000 is a perfectly valid bigint and pg_size_pretty should do The Right Thing with it. You should report a bug to the PostgreSQL people and win kudos.

UPDATE: Examining the PostgreSQL source code (thanks to Jeremiah Peschka for providing the link) shows us where the bug is:

491                 else
492                 {
493                     mult *= 1024;
494                     snprintf(buf, sizeof(buf), INT64_FORMAT " TB",
495                              (size + mult / 2) / mult); /* OVERFLOW! */
496                 }

If size is close to the limit of int64, then adding mult/2 to it will overflow before the following division by mult can bring it back into range.