Hi
new update:
1. unit searching is case insensitive
2. initial support for binary byte prefixes - KiB, MiB, .. (IEC standard),
change behave for SI units
Second point is much more complex then it is looking - if pg_size_bytes
should be consistent with pg_size_pretty.
The current pg_size_pretty and transformations in guc.c are based on JEDEC
standard. Using this standard for GUC has sense - using it for object sizes
is probably unhappy.
I tried to fix (and enhance) pg_size_pretty - now reports correct units,
and via second parameter it allows to specify base: 2 (binary, IEC -
default) or 10 (SI).
I think it is good to have it. These standards are generic and wide used,
but should to be pretty explained in documentation if we will use JEDEC for
configuration. Probably better to leave JEDEC and prefer SI and IEC.
Plan B is fix Postgres on JEDEC only - it is trivial, simple - but it can
look like archaic in next years.
Comments, notices?
Regards
Pavel
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index 60b9a09..d9a4f34
*** 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
*** 17678,17699 ****
</row>
<row>
<entry>
! <literal><function>pg_size_pretty(<type>bigint</type>)</function></literal>
</entry>
<entry><type>text</type></entry>
<entry>
Converts a size in bytes expressed as a 64-bit integer into a
! human-readable format with size units
</entry>
</row>
<row>
<entry>
! <literal><function>pg_size_pretty(<type>numeric</type>)</function></literal>
</entry>
<entry><type>text</type></entry>
<entry>
Converts a size in bytes expressed as a numeric value into a
! human-readable format with size units
</entry>
</row>
<row>
--- 17681,17715 ----
</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. The parameter is case insensitive string. Following
! units are supported: B, kB, MB, GB, TB, PB, KiB, MiB, TiB, PiB.
! </entry>
! </row>
! <row>
! <entry>
! <literal><function>pg_size_pretty(<type>bigint</type> <optional>, <type>int</type></optional>)</function></literal>
</entry>
<entry><type>text</type></entry>
<entry>
Converts a size in bytes expressed as a 64-bit integer into a
! human-readable format with size units. Second parameter allows to
! specify the base (2 or 10). The binary base is default.
</entry>
</row>
<row>
<entry>
! <literal><function>pg_size_pretty(<type>numeric</type> <optional>, <type>int</type></optional>)</function></literal>
</entry>
<entry><type>text</type></entry>
<entry>
Converts a size in bytes expressed as a numeric value into a
! human-readable format with size units. Second parameter allows to
! specify the base (2 or 10). The binary base is default.
</entry>
</row>
<row>
diff --git a/src/backend/utils/adt/dbsize.c b/src/backend/utils/adt/dbsize.c
new file mode 100644
index 5ee59d0..819da35
*** a/src/backend/utils/adt/dbsize.c
--- b/src/backend/utils/adt/dbsize.c
***************
*** 31,36 ****
--- 31,64 ----
#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[] =
+ {
+ {"B", 1L},
+ {"kiB", 1024L},
+ {"MiB", 1024L * 1024},
+ {"GiB", 1024L * 1024 * 1024},
+ {"TiB", 1024L * 1024 * 1024 * 1024},
+ {"PiB", 1024L * 1024 * 1024 * 1024 * 1024},
+ {"B", 1L},
+ {"kB", 1000L},
+ {"MB", 1000L * 1000},
+ {"GB", 1000L * 1000 * 1000},
+ {"TB", 1000L * 1000 * 1000 * 1000},
+ {"PB", 1000L * 1000 * 1000 * 1000 * 1000},
+
+ {""} /* end of table marker */
+ };
+
+
/* Divide by two and round towards positive infinity. */
#define half_rounded(x) (((x) + ((x) < 0 ? 0 : 1)) / 2)
*************** calculate_table_size(Relation rel)
*** 409,415 ****
* Calculate total on-disk size of all indexes attached to the given table.
*
* Can be applied safely to an index, but you'll just get zero.
! */
static int64
calculate_indexes_size(Relation rel)
{
--- 437,443 ----
* Calculate total on-disk size of all indexes attached to the given table.
*
* Can be applied safely to an index, but you'll just get zero.
! */
static int64
calculate_indexes_size(Relation rel)
{
*************** pg_total_relation_size(PG_FUNCTION_ARGS)
*** 526,574 ****
}
/*
! * formatting with size units
*/
Datum
pg_size_pretty(PG_FUNCTION_ARGS)
{
int64 size = PG_GETARG_INT64(0);
char buf[64];
- int64 limit = 10 * 1024;
- int64 limit2 = limit * 2 - 1;
! if (Abs(size) < limit)
! snprintf(buf, sizeof(buf), INT64_FORMAT " bytes", size);
! else
{
! size >>= 9; /* keep one extra bit for rounding */
! if (Abs(size) < limit2)
! snprintf(buf, sizeof(buf), INT64_FORMAT " kB",
! half_rounded(size));
else
{
! size >>= 10;
if (Abs(size) < limit2)
! snprintf(buf, sizeof(buf), INT64_FORMAT " MB",
half_rounded(size));
else
{
size >>= 10;
if (Abs(size) < limit2)
! snprintf(buf, sizeof(buf), INT64_FORMAT " GB",
half_rounded(size));
else
{
size >>= 10;
! snprintf(buf, sizeof(buf), INT64_FORMAT " TB",
half_rounded(size));
}
}
}
}
PG_RETURN_TEXT_P(cstring_to_text(buf));
}
static char *
numeric_to_cstring(Numeric n)
{
--- 554,673 ----
}
/*
! * formatting with size units - it is wrapper for pg_size_with_base_pretty
*/
Datum
pg_size_pretty(PG_FUNCTION_ARGS)
{
+ PG_RETURN_DATUM(DirectFunctionCall2(pg_size_with_base_pretty,
+ PG_GETARG_DATUM(0),
+ Int32GetDatum(2)));
+ }
+
+ /*
+ * formatting with size units with specified base
+ */
+ Datum
+ pg_size_with_base_pretty(PG_FUNCTION_ARGS)
+ {
int64 size = PG_GETARG_INT64(0);
+ int32 base = PG_GETARG_INT32(1);
char buf[64];
! if (base == 2)
{
! int64 limit = 10 * 1024;
! int64 limit2 = limit * 2 - 1;
!
! if (Abs(size) < limit)
! snprintf(buf, sizeof(buf), INT64_FORMAT " bytes", size);
else
{
! size >>= 9; /* keep one extra bit for rounding */
if (Abs(size) < limit2)
! snprintf(buf, sizeof(buf), INT64_FORMAT " KiB",
half_rounded(size));
else
{
size >>= 10;
if (Abs(size) < limit2)
! snprintf(buf, sizeof(buf), INT64_FORMAT " MiB",
half_rounded(size));
else
{
size >>= 10;
! if (Abs(size) < limit2)
! snprintf(buf, sizeof(buf), INT64_FORMAT " GiB",
! half_rounded(size));
! else
! {
! size >>= 10;
! if (Abs(size) < limit2)
! snprintf(buf, sizeof(buf), INT64_FORMAT " TiB",
! half_rounded(size));
! else
! {
! size >>= 10;
! snprintf(buf, sizeof(buf), INT64_FORMAT " PiB",
! half_rounded(size));
! }
! }
! }
! }
! }
! }
! else if (base == 10)
! {
! char buf[64];
! int64 limit = 10 * 1000;
! int64 limit2 = limit * 10 - 1;
!
! if (Abs(size) < limit)
! snprintf(buf, sizeof(buf), INT64_FORMAT " bytes", size);
! else
! {
! size /= 500;
! if (Abs(size) < limit2)
! snprintf(buf, sizeof(buf), INT64_FORMAT " kB",
! half_rounded(size));
! else
! {
! size /= 1000;
! if (Abs(size) < limit2)
! snprintf(buf, sizeof(buf), INT64_FORMAT " MB",
half_rounded(size));
+ else
+ {
+ size /= 1000;
+ if (Abs(size) < limit2)
+ snprintf(buf, sizeof(buf), INT64_FORMAT " GB",
+ half_rounded(size));
+ else
+ {
+ size /= 1000;
+ if (Abs(size) < limit2)
+ snprintf(buf, sizeof(buf), INT64_FORMAT " TB",
+ half_rounded(size));
+ else
+ {
+ size /= 1000;
+ snprintf(buf, sizeof(buf), INT64_FORMAT " PB",
+ half_rounded(size));
+ }
+ }
}
}
}
}
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("unsupported base, only 2 or 10 is allowed")));
PG_RETURN_TEXT_P(cstring_to_text(buf));
}
+
static char *
numeric_to_cstring(Numeric n)
{
*************** numeric_shift_right(Numeric n, unsigned
*** 640,705 ****
return DatumGetNumeric(result);
}
Datum
pg_size_pretty_numeric(PG_FUNCTION_ARGS)
{
Numeric size = PG_GETARG_NUMERIC(0);
Numeric limit,
limit2;
char *result;
! limit = int64_to_numeric(10 * 1024);
! limit2 = int64_to_numeric(10 * 1024 * 2 - 1);
!
! if (numeric_is_less(numeric_absolute(size), limit))
! {
! result = psprintf("%s bytes", numeric_to_cstring(size));
! }
! else
{
! /* keep one extra bit for rounding */
! /* size >>= 9 */
! size = numeric_shift_right(size, 9);
! if (numeric_is_less(numeric_absolute(size), limit2))
{
! size = numeric_half_rounded(size);
! result = psprintf("%s kB", numeric_to_cstring(size));
}
else
{
! /* size >>= 10 */
! size = numeric_shift_right(size, 10);
if (numeric_is_less(numeric_absolute(size), limit2))
{
size = numeric_half_rounded(size);
! result = psprintf("%s MB", numeric_to_cstring(size));
}
else
{
/* size >>= 10 */
size = numeric_shift_right(size, 10);
-
if (numeric_is_less(numeric_absolute(size), limit2))
{
size = numeric_half_rounded(size);
! result = psprintf("%s GB", numeric_to_cstring(size));
}
else
{
/* size >>= 10 */
size = numeric_shift_right(size, 10);
size = numeric_half_rounded(size);
! result = psprintf("%s TB", numeric_to_cstring(size));
}
}
}
}
PG_RETURN_TEXT_P(cstring_to_text(result));
}
/*
* Get the filenode of a relation
*
* This is expected to be used in queries like
--- 739,1007 ----
return DatumGetNumeric(result);
}
+ static Numeric
+ numeric_div_int32(Numeric n, int32 divisor)
+ {
+ Datum d = NumericGetDatum(n);
+ Datum divisor_numeric;
+ Datum result;
+
+ divisor_numeric = DirectFunctionCall1(int4_numeric, divisor);
+ result = DirectFunctionCall2(numeric_div_trunc, d, divisor_numeric);
+ return DatumGetNumeric(result);
+ }
+
Datum
pg_size_pretty_numeric(PG_FUNCTION_ARGS)
{
+ PG_RETURN_DATUM(DirectFunctionCall2(pg_size_with_base_pretty_numeric,
+ PG_GETARG_DATUM(0),
+ Int32GetDatum(2)));
+ }
+
+ Datum
+ pg_size_with_base_pretty_numeric(PG_FUNCTION_ARGS)
+ {
Numeric size = PG_GETARG_NUMERIC(0);
+ int32 base = PG_GETARG_INT32(1);
Numeric limit,
limit2;
char *result;
! if (base == 2)
{
! limit = int64_to_numeric(10 * 1024);
! limit2 = int64_to_numeric(10 * 1024 * 2 - 1);
! if (numeric_is_less(numeric_absolute(size), limit))
{
! result = psprintf("%s bytes", numeric_to_cstring(size));
}
else
{
! /* keep one extra bit for rounding */
! /* size >>= 9 */
! size = numeric_shift_right(size, 9);
!
if (numeric_is_less(numeric_absolute(size), limit2))
{
size = numeric_half_rounded(size);
! result = psprintf("%s KiB", numeric_to_cstring(size));
}
else
{
/* size >>= 10 */
size = numeric_shift_right(size, 10);
if (numeric_is_less(numeric_absolute(size), limit2))
{
size = numeric_half_rounded(size);
! result = psprintf("%s MiB", numeric_to_cstring(size));
}
else
{
/* size >>= 10 */
size = numeric_shift_right(size, 10);
+
+ if (numeric_is_less(numeric_absolute(size), limit2))
+ {
+ size = numeric_half_rounded(size);
+ result = psprintf("%s GiB", numeric_to_cstring(size));
+ }
+ else
+ {
+ /* size >>= 10 */
+ size = numeric_shift_right(size, 10);
+
+ if (numeric_is_less(numeric_absolute(size), limit2))
+ {
+ size = numeric_half_rounded(size);
+ result = psprintf("%s TiB", numeric_to_cstring(size));
+ }
+ else
+ {
+ /* size >>= 10 */
+ size = numeric_shift_right(size, 10);
+ size = numeric_half_rounded(size);
+ result = psprintf("%s PiB", numeric_to_cstring(size));
+ }
+ }
+ }
+ }
+ }
+ }
+ else if (base == 10)
+ {
+ limit = int64_to_numeric(10 * 1000);
+ limit2 = int64_to_numeric(100 * 1000 - 1);
+
+ if (numeric_is_less(numeric_absolute(size), limit))
+ {
+ result = psprintf("%s bytes", numeric_to_cstring(size));
+ }
+ else
+ {
+ /* keep one extra bit for rounding */
+ /* size /= 500 */
+ size = numeric_div_int32(size, 500);
+
+ if (numeric_is_less(numeric_absolute(size), limit2))
+ {
+ size = numeric_half_rounded(size);
+ result = psprintf("%s kB", numeric_to_cstring(size));
+ }
+ else
+ {
+ /* size /= 1000 */
+ size = numeric_div_int32(size, 1000);
+ if (numeric_is_less(numeric_absolute(size), limit2))
+ {
size = numeric_half_rounded(size);
! result = psprintf("%s MB", numeric_to_cstring(size));
! }
! else
! {
! /* size /= 1000 */
! size = numeric_div_int32(size, 10);
!
! if (numeric_is_less(numeric_absolute(size), limit2))
! {
! size = numeric_half_rounded(size);
! result = psprintf("%s GB", numeric_to_cstring(size));
! }
! else
! {
! /* size /= 1000 */
! size = numeric_div_int32(size, 1000);
!
! if (numeric_is_less(numeric_absolute(size), limit2))
! {
! size = numeric_half_rounded(size);
! result = psprintf("%s TB", numeric_to_cstring(size));
! }
! else
! {
! /* size /= 1000 */
! size = numeric_div_int32(size, 1000);
! size = numeric_half_rounded(size);
! result = psprintf("%s PB", numeric_to_cstring(size));
! }
! }
}
}
}
}
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("unsupported base, only 2 or 10 is allowed")));
PG_RETURN_TEXT_P(cstring_to_text(result));
}
/*
+ * 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 (strcasecmp(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..da6f2f6
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
*************** DESCR("disk space usage for the specifie
*** 3661,3669 ****
DATA(insert OID = 2286 ( pg_total_relation_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_total_relation_size _null_ _null_ _null_ ));
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 = 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_ ));
DESCR("disk space usage for the specified table, including TOAST, free space and visibility map");
DATA(insert OID = 2998 ( pg_indexes_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_indexes_size _null_ _null_ _null_ ));
--- 3661,3675 ----
DATA(insert OID = 2286 ( pg_total_relation_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_total_relation_size _null_ _null_ _null_ ));
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 binary units");
! DATA(insert OID = 3317 ( pg_size_pretty PGNSP PGUID 12 1 0 0 0 f f f f t f v s 2 0 25 "20 23" _null_ _null_ _null_ _null_ _null_ pg_size_with_base_pretty _null_ _null_ _null_ ));
! DESCR("convert a long int to a human readable text using size SI or binary units");
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 = 3318 ( pg_size_pretty PGNSP PGUID 12 1 0 0 0 f f f f t f v s 2 0 25 "1700 23" _null_ _null_ _null_ _null_ _null_ pg_size_with_base_pretty_numeric _null_ _null_ _null_ ));
+ DESCR("convert a long int to a human readable text using size SI or binary units");
+ DATA(insert OID = 3319 ( 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 = 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_ ));
DESCR("disk space usage for the specified table, including TOAST, free space and visibility map");
DATA(insert OID = 2998 ( pg_indexes_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_indexes_size _null_ _null_ _null_ ));
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
new file mode 100644
index e610bf3..a3b803a
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
*************** extern Datum pg_relation_size(PG_FUNCTIO
*** 462,467 ****
--- 462,470 ----
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_with_base_pretty(PG_FUNCTION_ARGS);
+ extern Datum pg_size_with_base_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..6c5fce3
*** 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,121 ----
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 KiB | -977 KiB
! 1000000000 | 954 MiB | -954 MiB
! 1000000000000 | 931 GiB | -931 GiB
! 1000000000000000 | 909 TiB | -909 TiB
! 1000000000000000000 | 888 PiB | -888 PiB
! (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 KiB | -977 KiB
! 1000000000 | 954 MiB | -954 MiB
! 1000000000000 | 931 GiB | -931 GiB
! 1000000000000000 | 909 TiB | -909 TiB
! 1000000000000000000 | 888 PiB | -888 PiB
! 10.5 | 10.5 bytes | -10.5 bytes
! 1000.5 | 1000.5 bytes | -1000.5 bytes
! 1000000.5 | 977 KiB | -977 KiB
! 1000000000.5 | 954 MiB | -954 MiB
! 1000000000000.5 | 931 GiB | -931 GiB
! 1000000000000000.5 | 909 TiB | -909 TiB
! 1000000000000000000.5 | 888 PiB | -888 PiB
! (14 rows)
!
! SELECT size, pg_size_pretty(size, 10), pg_size_pretty(-1 * size, 10) 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 | 1000 kB | -1000 kB
! 1000000000 | 1000 MB | -1000 MB
! 1000000000000 | 1000 GB | -1000 GB
! 1000000000000000 | 1000 TB | -1000 TB
! 1000000000000000000 | 1000 PB | -1000 PB
! (7 rows)
!
! SELECT size, pg_size_pretty(size, 10), pg_size_pretty(-1 * size, 10) 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 | 1000 kB | -1000 kB
! 1000000000 | 1000 MB | -1000 MB
! 1000000000000 | 100 TB | -100 TB
! 1000000000000000 | 100 PB | -100 PB
! 1000000000000000000 | 100000 PB | -100000 PB
! 10.5 | 10.5 bytes | -10.5 bytes
! 1000.5 | 1000.5 bytes | -1000.5 bytes
! 1000000.5 | 1000 kB | -1000 kB
! 1000000000.5 | 1000 MB | -1000 MB
! 1000000000000.5 | 100 TB | -100 TB
! 1000000000000000.5 | 100 PB | -100 PB
! 1000000000000000000.5 | 100000 PB | -100000 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
! 1000
! 1000000
! 1000000000
! 1500000000
! 1000000000000
! 1000000000000000
! (8 rows)
!
! SELECT pg_size_bytes(size) FROM
! (VALUES('1'), ('1B'), ('1kiB'), ('1MiB'), (' 1 GiB'), ('1.5 GiB '),
! ('1TiB'),('1PiB')) 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..f7e2e0f
*** 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,44 ----
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 size, pg_size_pretty(size, 10), pg_size_pretty(-1 * size, 10) FROM
! (VALUES (10::bigint), (1000::bigint), (1000000::bigint),
! (1000000000::bigint), (1000000000000::bigint),
! (1000000000000000::bigint),
! (1000000000000000000::bigint)) x(size);
!
! SELECT size, pg_size_pretty(size, 10), pg_size_pretty(-1 * size, 10) 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);
!
! SELECT pg_size_bytes(size) FROM
! (VALUES('1'), ('1B'), ('1kiB'), ('1MiB'), (' 1 GiB'), ('1.5 GiB '),
! ('1TiB'),('1PiB')) x(size);
!
! --should fail
! SELECT pg_size_bytes('1 AB');
! SELECT pg_size_bytes('1 AB A');
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
