Search code examples
postgresqlbigint

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 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.

Solution

  • 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.