2015-12-01 11:12 GMT+01:00 Pavel Stehule <[email protected]>:
>
>
> 2015-12-01 11:02 GMT+01:00 Kyotaro HORIGUCHI <
> [email protected]>:
>
>> 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 <
>> [email protected]> 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
