Re: custom function for converting human readable sizes to bytes - Mailing list pgsql-hackers

On 2/15/16, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
>> On 12/02/16 10:19, Dean Rasheed wrote:
>>> This seems like a reasonable first patch for me as a committer, so
>>> I'll take it unless anyone else was planning to do so.
>>
>
> So looking at this, it seems that for the most part pg_size_bytes()
> will parse any output produced by pg_size_pretty(). The exception is
> that there are 2 versions of pg_size_pretty(), one that takes bigint
> and one that takes numeric, whereas pg_size_bytes() returns bigint, so
> it can't handle all inputs. Is there any reason not to make
> pg_size_bytes() return numeric?

I guess the only reason is a comparison purpose. The example from the
first letter[1]:

SELECT * FROM pg_class WHERE pg_table_size(oid) > pg_human_size('2GB');

... but not for something like:
WITH sel AS ( SELECT pg_size_pretty(oid) AS _size, * FROM pg_class WHERE ...
)
SELECT pg_size_bytes(_size), * FROM sel;


So the use case of the "pg_size_bytes" is getting a value to compare
with a result from pg_table_size, ..., even with pg_database_size,
_each_ of whom returns bigint (or just int), but not numeric:

postgres=# \df pg_*_size                                  List of functions  Schema   |          Name          | Result
datatype | Argument
 
data types |  Type
------------+------------------------+------------------+---------------------+--------pg_catalog | pg_column_size
  | integer          | "any"      | normalpg_catalog | pg_database_size       | bigint           | name      |
normalpg_catalog| pg_database_size       | bigint           | oid      | normalpg_catalog | pg_indexes_size        |
bigint          | regclass      | normalpg_catalog | pg_relation_size       | bigint           | regclass      |
normalpg_catalog| pg_relation_size       | bigint           | regclass,
 
text      | normalpg_catalog | pg_table_size          | bigint           | regclass      | normalpg_catalog |
pg_tablespace_size    | bigint           | name      | normalpg_catalog | pg_tablespace_size     | bigint           |
oid     | normalpg_catalog | pg_total_relation_size | bigint           | regclass      | normal
 
(10 rows)

The commit message for "pg_size_pretty(numeric)" explains[2] it was
introduced for using with "pg_xlog_location_diff"[3] only.
Since "pg_size_bytes" supports up to (4 EiB-1B)[4] I hope nobody will
want to send a query like "tell me where difference between two
transaction log locations is bigger or equal than 4EiB"...

But comparison between int8 and numeric is OK, so usage of rational
input will not break queries:

postgres=# select '10e200'::numeric > 10::int8 as check;check
-------t
(1 row)


P.S.: "bytes" size unit was added just for consistency: each group
should have a name, even with an exponent of 1.

> It would still be compatible with the example use cases, but it would
> be a better inverse of both variants of pg_size_pretty() and would be
> more future-proof. It already works internally using numeric, so it's
> a trivial change to make now, but impossible to change in the future
> without introducing a new function with a different name, which is
> messy.
>
> Thoughts?
>
> Regards,
> Dean

[1]http://www.postgresql.org/message-id/CAFj8pRD-tGoDKnxdYgECzA4On01_uRqPrwF-8LdkSE-6bDHp0w@mail.gmail.com
[2]http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=4a2d7ad76f5f275ef2d6a57e1a61d5bf756349e8
[3]http://www.postgresql.org/docs/devel/static/functions-admin.html#FUNCTIONS-ADMIN-BACKUP-TABLE
[4]https://en.wikipedia.org/wiki/Binary_prefix#Adoption_by_IEC.2C_NIST_and_ISO

-- 
Best regards,
Vitaly Burovoy