2015-12-01 11:12 GMT+01:00 Pavel Stehule <pavel.steh...@gmail.com>: > > > 2015-12-01 11:02 GMT+01:00 Kyotaro HORIGUCHI < > horiguchi.kyot...@lab.ntt.co.jp>: > >> Hello, The meaning of "be orange" (I couldn't find it) interests >> me but putting it aside.. >> >> I have some random comments. >> >> At Mon, 30 Nov 2015 18:30:18 +0100, Pavel Stehule < >> pavel.steh...@gmail.com> wrote in < >> cafj8prcd6we3tqmr0vbcn98wf0p3o3h6nau4btaoswcj443...@mail.gmail.com> >> > Hi >> > >> > >> > > 2. using independent implementation - there is some redundant code, >> but we >> > > can support duble insted int, and we can support some additional >> units. >> > > >> > >> > new patch is based on own implementation - use numeric/bigint >> calculations >> > >> > + regress tests and doc >> >> 1. What do you think about binary byte prefixes? (kiB, MiB...) >> > > I don't know this mechanics - can you write it? It can be good idea/ > > >> >> I couldn't read what Robert wrote upthread but I also would like >> to have 2-base byte unit prifixes. (Sorry I couldn't put it >> aside..) >> >> >> 2. Doesn't it allow units in lower case? >> > > The parser is consistent with a behave used in configure file processing. > We can change it, but then there will be divergence between this function > and GUC parser. > > >> >> I think '1gb' and so should be acceptable as an input. >> >> >> 3. Why are you allow positive sign prefixing digits? I suppose >> that positive sign also shouldn't be allowed if it rejects >> prifixing negative sign. >> > > I have not strong opinion about it. '-' is exactly wrong, but '+' can be > acceptable. But it can be changed. > > >> >> 4. Useless includes >> >> dbsize.c is modified to include guc.h but it is useless. >> > > I'll fix it. > > >> >> 5. Error message >> >> + if (ndigits == 0) >> + ereport(ERROR, >> + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), >> + errmsg("string is empty"))); >> >> If pg_size_bytes allows prefixing positive sign or spaces, >> ndigits == 0 here doesn't mean that the whole string is empty. >> >> > I'll fix it. > > >> >> 6. Symmetry with pg_size_pretty >> >> pg_size_pretty doesn't have units larger than TB. Just adding PB >> to it breaks backward compatibility, but leaving as it is breaks >> symmetry with this function. Some possible solution for this >> that I can guess for now are.. >> > > I prefer a warning about possible compatibility issue in pg_size_pretty > and support PB directly there. > > >> >> - Leave it as is. >> >> - New GUC to allow PB for pg_size_pretty(). >> >> - Expanded function such like pg_size_pretty2 (oops..) >> >> - New polymorphic (sibling?) function with additional >> parameters to instruct how they work. (The following >> involving 2-base representations) >> >> pg_size_pretty(n, <2base>, <allow_PB or such..>); >> pg_size_bytes(n, <2base>, <allow_PB or such..>); >> >> 7. Docs >> >> + Converts a size in human-readable format with size units >> + to bytes >> >> The descriptions for the functions around use 'into' instead of >> 'to' for the preposition. >> >> >> 8. Regression >> >> The regression in the patch looks good enough as is (except that >> it forgets the unit 'B' or prifixing spaces or sings), but they >> are necessarily have individual tests. The following SQL >> statement will do them at once. >> >> SELECT s, pg_size_bytes(s) FROM (VALUES ('1'), ('1B')...) as t(s); >> >> > I'll fix it. >
here is updated patch Regards Pavel > > Thank you for your ideas > > Regards > > Pavel > > >> regards, >> >> -- >> Kyotaro Horiguchi >> NTT Open Source Software Center >> >> >> >
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml new file mode 100644 index 60b9a09..55e7d35 *** a/doc/src/sgml/func.sgml --- b/doc/src/sgml/func.sgml *************** postgres=# SELECT * FROM pg_xlogfile_nam *** 17607,17612 **** --- 17607,17615 ---- <primary>pg_relation_size</primary> </indexterm> <indexterm> + <primary>pg_size_bytes</primary> + </indexterm> + <indexterm> <primary>pg_size_pretty</primary> </indexterm> <indexterm> *************** postgres=# SELECT * FROM pg_xlogfile_nam *** 17677,17682 **** --- 17680,17695 ---- </entry> </row> <row> + <entry> + <literal><function>pg_size_bytes(<type>text</type>)</function></literal> + </entry> + <entry><type>bigint</type></entry> + <entry> + Converts a size in human-readable format with size units + into bytes. + </entry> + </row> + <row> <entry> <literal><function>pg_size_pretty(<type>bigint</type>)</function></literal> </entry> diff --git a/src/backend/utils/adt/dbsize.c b/src/backend/utils/adt/dbsize.c new file mode 100644 index 5ee59d0..0e7399c *** a/src/backend/utils/adt/dbsize.c --- b/src/backend/utils/adt/dbsize.c *************** *** 31,36 **** --- 31,58 ---- #include "utils/relmapper.h" #include "utils/syscache.h" + #define MAX_UNIT_LEN 3 + #define MAX_DIGITS 20 + + typedef struct + { + char unit[MAX_UNIT_LEN + 1]; + long int multiplier; + } unit_multiplier; + + static const unit_multiplier unit_multiplier_table[] = + { + {"PB", 1024L * 1024 * 1024 * 1024 * 1024}, + {"TB", 1024L * 1024 * 1024 * 1024}, + {"GB", 1024L * 1024 * 1024}, + {"MB", 1024L * 1024}, + {"kB", 1024L}, + {"B", 1L}, + + {""} /* end of table marker */ + }; + + /* Divide by two and round towards positive infinity. */ #define half_rounded(x) (((x) + ((x) < 0 ? 0 : 1)) / 2) *************** pg_size_pretty(PG_FUNCTION_ARGS) *** 559,566 **** else { size >>= 10; ! snprintf(buf, sizeof(buf), INT64_FORMAT " TB", ! half_rounded(size)); } } } --- 581,595 ---- else { size >>= 10; ! if (Abs(size) < limit2) ! snprintf(buf, sizeof(buf), INT64_FORMAT " TB", ! half_rounded(size)); ! else ! { ! size >>= 10; ! snprintf(buf, sizeof(buf), INT64_FORMAT " PB", ! half_rounded(size)); ! } } } } *************** pg_size_pretty_numeric(PG_FUNCTION_ARGS) *** 689,696 **** { /* size >>= 10 */ size = numeric_shift_right(size, 10); ! size = numeric_half_rounded(size); ! result = psprintf("%s TB", numeric_to_cstring(size)); } } } --- 718,736 ---- { /* size >>= 10 */ size = numeric_shift_right(size, 10); ! ! if (numeric_is_less(numeric_absolute(size), limit2)) ! { ! size = numeric_half_rounded(size); ! result = psprintf("%s TB", numeric_to_cstring(size)); ! } ! else ! { ! /* size >>= 10 */ ! size = numeric_shift_right(size, 10); ! size = numeric_half_rounded(size); ! result = psprintf("%s PB", numeric_to_cstring(size)); ! } } } } *************** pg_size_pretty_numeric(PG_FUNCTION_ARGS) *** 700,705 **** --- 740,848 ---- } /* + * Convert human readable size to long int. + * + * cannot to use parse_int due too low limits there + */ + Datum + pg_size_bytes(PG_FUNCTION_ARGS) + { + text *arg = PG_GETARG_TEXT_PP(0); + const char *cp = text_to_cstring(arg); + char digits[MAX_DIGITS + 1]; + int ndigits = 0; + Numeric num; + int64 result; + + /* Skip leading spaces */ + while (isspace((unsigned char) *cp)) + cp++; + + switch (*cp) + { + /* ignore plus symbol */ + case '+': + cp++; + break; + case '-': + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("size cannot be negative"))); + } + + while (*cp) + { + if ((isdigit(*cp) || *cp == '.') && ndigits < MAX_DIGITS) + digits[ndigits++] = *cp++; + else + break; + } + + if (ndigits == 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid format"))); + + digits[ndigits] = '\0'; + num = DatumGetNumeric(DirectFunctionCall3(numeric_in, + CStringGetDatum(digits), 0, -1)); + + /* allow whitespace between integer and unit */ + while (isspace((unsigned char) *cp)) + cp++; + + /* Handle possible unit */ + if (*cp != '\0') + { + char unit[MAX_UNIT_LEN + 1]; + int unitlen = 0; + int i; + long int multiplier; + bool found = false; + Numeric mul_num; + + + while (*cp && !isspace(*cp) && unitlen < MAX_UNIT_LEN) + unit[unitlen++] = *cp++; + + unit[unitlen] = '\0'; + + /* allow whitespace after unit */ + while (isspace((unsigned char) *cp)) + cp++; + + if (*cp != '\0') + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid format"))); + + for (i = 0; *unit_multiplier_table[i].unit; i++) + { + if (strcmp(unit, unit_multiplier_table[i].unit) == 0) + { + multiplier = unit_multiplier_table[i].multiplier; + found = true; + break; + } + } + + if (!found) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("unknown unit \"%s\"", unit))); + + mul_num = DatumGetNumeric(DirectFunctionCall1(int8_numeric, Int64GetDatum(multiplier))); + num = DatumGetNumeric(DirectFunctionCall2(numeric_mul, + NumericGetDatum(mul_num), + NumericGetDatum(num))); + } + + result = DatumGetInt64(DirectFunctionCall1(numeric_int8, NumericGetDatum(num))); + + PG_RETURN_INT64(result); + } + + /* * Get the filenode of a relation * * This is expected to be used in queries like diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h new file mode 100644 index d8640db..b68c8fa *** a/src/include/catalog/pg_proc.h --- b/src/include/catalog/pg_proc.h *************** DATA(insert OID = 2286 ( pg_total_relati *** 3662,3667 **** --- 3662,3669 ---- DESCR("total disk space usage for the specified table and associated indexes"); DATA(insert OID = 2288 ( pg_size_pretty PGNSP PGUID 12 1 0 0 0 f f f f t f v s 1 0 25 "20" _null_ _null_ _null_ _null_ _null_ pg_size_pretty _null_ _null_ _null_ )); DESCR("convert a long int to a human readable text using size units"); + DATA(insert OID = 3317 ( pg_size_bytes PGNSP PGUID 12 1 0 0 0 f f f f t f v s 1 0 20 "25" _null_ _null_ _null_ _null_ _null_ pg_size_bytes _null_ _null_ _null_ )); + DESCR("convert a human readable text with size units to long int bytes"); DATA(insert OID = 3166 ( pg_size_pretty PGNSP PGUID 12 1 0 0 0 f f f f t f v s 1 0 25 "1700" _null_ _null_ _null_ _null_ _null_ pg_size_pretty_numeric _null_ _null_ _null_ )); DESCR("convert a numeric to a human readable text using size units"); DATA(insert OID = 2997 ( pg_table_size PGNSP PGUID 12 1 0 0 0 f f f f t f v s 1 0 20 "2205" _null_ _null_ _null_ _null_ _null_ pg_table_size _null_ _null_ _null_ )); diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h new file mode 100644 index e610bf3..227e5f5 *** a/src/include/utils/builtins.h --- b/src/include/utils/builtins.h *************** extern Datum pg_relation_size(PG_FUNCTIO *** 462,467 **** --- 462,468 ---- extern Datum pg_total_relation_size(PG_FUNCTION_ARGS); extern Datum pg_size_pretty(PG_FUNCTION_ARGS); extern Datum pg_size_pretty_numeric(PG_FUNCTION_ARGS); + extern Datum pg_size_bytes(PG_FUNCTION_ARGS); extern Datum pg_table_size(PG_FUNCTION_ARGS); extern Datum pg_indexes_size(PG_FUNCTION_ARGS); extern Datum pg_relation_filenode(PG_FUNCTION_ARGS); diff --git a/src/test/regress/expected/dbsize.out b/src/test/regress/expected/dbsize.out new file mode 100644 index aa513e7..4ed9190 *** a/src/test/regress/expected/dbsize.out --- b/src/test/regress/expected/dbsize.out *************** *** 1,37 **** SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM (VALUES (10::bigint), (1000::bigint), (1000000::bigint), (1000000000::bigint), (1000000000000::bigint), ! (1000000000000000::bigint)) x(size); ! size | pg_size_pretty | pg_size_pretty ! ------------------+----------------+---------------- ! 10 | 10 bytes | -10 bytes ! 1000 | 1000 bytes | -1000 bytes ! 1000000 | 977 kB | -977 kB ! 1000000000 | 954 MB | -954 MB ! 1000000000000 | 931 GB | -931 GB ! 1000000000000000 | 909 TB | -909 TB ! (6 rows) SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM (VALUES (10::numeric), (1000::numeric), (1000000::numeric), (1000000000::numeric), (1000000000000::numeric), (1000000000000000::numeric), (10.5::numeric), (1000.5::numeric), (1000000.5::numeric), (1000000000.5::numeric), (1000000000000.5::numeric), ! (1000000000000000.5::numeric)) x(size); ! size | pg_size_pretty | pg_size_pretty ! --------------------+----------------+---------------- ! 10 | 10 bytes | -10 bytes ! 1000 | 1000 bytes | -1000 bytes ! 1000000 | 977 kB | -977 kB ! 1000000000 | 954 MB | -954 MB ! 1000000000000 | 931 GB | -931 GB ! 1000000000000000 | 909 TB | -909 TB ! 10.5 | 10.5 bytes | -10.5 bytes ! 1000.5 | 1000.5 bytes | -1000.5 bytes ! 1000000.5 | 977 kB | -977 kB ! 1000000000.5 | 954 MB | -954 MB ! 1000000000000.5 | 931 GB | -931 GB ! 1000000000000000.5 | 909 TB | -909 TB ! (12 rows) --- 1,63 ---- SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM (VALUES (10::bigint), (1000::bigint), (1000000::bigint), (1000000000::bigint), (1000000000000::bigint), ! (1000000000000000::bigint), ! (1000000000000000000::bigint)) x(size); ! size | pg_size_pretty | pg_size_pretty ! ---------------------+----------------+---------------- ! 10 | 10 bytes | -10 bytes ! 1000 | 1000 bytes | -1000 bytes ! 1000000 | 977 kB | -977 kB ! 1000000000 | 954 MB | -954 MB ! 1000000000000 | 931 GB | -931 GB ! 1000000000000000 | 909 TB | -909 TB ! 1000000000000000000 | 888 PB | -888 PB ! (7 rows) SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM (VALUES (10::numeric), (1000::numeric), (1000000::numeric), (1000000000::numeric), (1000000000000::numeric), (1000000000000000::numeric), + (1000000000000000000::numeric), (10.5::numeric), (1000.5::numeric), (1000000.5::numeric), (1000000000.5::numeric), (1000000000000.5::numeric), ! (1000000000000000.5::numeric), ! (1000000000000000000.5::numeric)) x(size); ! size | pg_size_pretty | pg_size_pretty ! -----------------------+----------------+---------------- ! 10 | 10 bytes | -10 bytes ! 1000 | 1000 bytes | -1000 bytes ! 1000000 | 977 kB | -977 kB ! 1000000000 | 954 MB | -954 MB ! 1000000000000 | 931 GB | -931 GB ! 1000000000000000 | 909 TB | -909 TB ! 1000000000000000000 | 888 PB | -888 PB ! 10.5 | 10.5 bytes | -10.5 bytes ! 1000.5 | 1000.5 bytes | -1000.5 bytes ! 1000000.5 | 977 kB | -977 kB ! 1000000000.5 | 954 MB | -954 MB ! 1000000000000.5 | 931 GB | -931 GB ! 1000000000000000.5 | 909 TB | -909 TB ! 1000000000000000000.5 | 888 PB | -888 PB ! (14 rows) + SELECT pg_size_bytes(size) FROM + (VALUES('1'), ('1B'), ('1kB'), ('1MB'), (' 1 GB'), ('1.5 GB '), + ('1TB'),('1PB')) x(size); + pg_size_bytes + ------------------ + 1 + 1 + 1024 + 1048576 + 1073741824 + 1610612736 + 1099511627776 + 1125899906842624 + (8 rows) + + --should fail + SELECT pg_size_bytes('1 AB'); + ERROR: unknown unit "AB" + SELECT pg_size_bytes('1 AB A'); + ERROR: invalid format diff --git a/src/test/regress/sql/dbsize.sql b/src/test/regress/sql/dbsize.sql new file mode 100644 index c118090..6cf833c *** a/src/test/regress/sql/dbsize.sql --- b/src/test/regress/sql/dbsize.sql *************** *** 1,12 **** SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM (VALUES (10::bigint), (1000::bigint), (1000000::bigint), (1000000000::bigint), (1000000000000::bigint), ! (1000000000000000::bigint)) x(size); SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM (VALUES (10::numeric), (1000::numeric), (1000000::numeric), (1000000000::numeric), (1000000000000::numeric), (1000000000000000::numeric), (10.5::numeric), (1000.5::numeric), (1000000.5::numeric), (1000000000.5::numeric), (1000000000000.5::numeric), ! (1000000000000000.5::numeric)) x(size); --- 1,23 ---- SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM (VALUES (10::bigint), (1000::bigint), (1000000::bigint), (1000000000::bigint), (1000000000000::bigint), ! (1000000000000000::bigint), ! (1000000000000000000::bigint)) x(size); SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM (VALUES (10::numeric), (1000::numeric), (1000000::numeric), (1000000000::numeric), (1000000000000::numeric), (1000000000000000::numeric), + (1000000000000000000::numeric), (10.5::numeric), (1000.5::numeric), (1000000.5::numeric), (1000000000.5::numeric), (1000000000000.5::numeric), ! (1000000000000000.5::numeric), ! (1000000000000000000.5::numeric)) x(size); ! ! SELECT pg_size_bytes(size) FROM ! (VALUES('1'), ('1B'), ('1kB'), ('1MB'), (' 1 GB'), ('1.5 GB '), ! ('1TB'),('1PB')) x(size); ! ! --should fail ! SELECT pg_size_bytes('1 AB'); ! SELECT pg_size_bytes('1 AB A'); \ No newline at end of file
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers