2015-12-30 17:33 GMT+01:00 Robert Haas <robertmh...@gmail.com>:
> On Mon, Dec 28, 2015 at 8:45 AM, Shulgin, Oleksandr
> <oleksandr.shul...@zalando.de> wrote:
> > I didn't check out earlier versions of this patch, but the latest one
> still
> > changes pg_size_pretty() to emit PB suffix.
> >
> > I don't think it is worth it to throw a number of changes together like
> > that. We should focus on adding pg_size_bytes() first and make it
> > compatible with both pg_size_pretty() and existing GUC units: that is
> > support suffixes up to TB and make sure they have the meaning of powers
> of
> > 2^10, not 10^3. Re-using the table present in guc.c would be a plus.
> >
> > Next, we could think about adding handling of PB suffix on input and
> output,
> > but I don't see a big problem if that is emitted as 1024TB or the user
> has
> > to specify it as 1024TB in a GUC or argument to pg_size_bytes(): an minor
> > inconvenience only.
>
> +1 to everything in this email.
>
so I removed support for PB and SI units. Now the
memory_unit_conversion_table is shared.
Regards
Pavel
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index 8ef9fce..6b921ae
*** 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,17696 ----
</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. The parameter is case insensitive string. Following
+ units are supported: kB, MB, GB, TB.
+ </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..3ec6d0c
*** a/src/backend/utils/adt/dbsize.c
--- b/src/backend/utils/adt/dbsize.c
***************
*** 25,30 ****
--- 25,31 ----
#include "storage/fd.h"
#include "utils/acl.h"
#include "utils/builtins.h"
+ #include "utils/guc.h"
#include "utils/numeric.h"
#include "utils/rel.h"
#include "utils/relfilenodemap.h"
*************** pg_size_pretty_numeric(PG_FUNCTION_ARGS)
*** 699,704 ****
--- 700,807 ----
PG_RETURN_TEXT_P(cstring_to_text(result));
}
+ #define MAX_DIGITS 20
+ #define MAX_UNIT_LEN 3
+
+ /*
+ * Convert human readable size to long int.
+ *
+ * Due suppor decimal value and case insensitivity of units
+ * a function parse_intcannot be used.
+ */
+ 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 multiplier;
+ Numeric mul_num;
+ const char *hintmsg;
+
+ 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")));
+
+ if (!parse_memory_unit(unit, &multiplier, &hintmsg))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("invalid unit \"%s\"", unit),
+ errhint("%s", _(hintmsg))));
+
+ /*
+ * Now, the multiplier is in KB unit. It should be multiplied by 1024
+ * before usage
+ */
+ mul_num = DatumGetNumeric(DirectFunctionCall1(int8_numeric,
+ Int64GetDatum(multiplier * 1024L)));
+
+ 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
*
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
new file mode 100644
index a185749..7f8871a
*** a/src/backend/utils/misc/guc.c
--- b/src/backend/utils/misc/guc.c
*************** convert_from_base_unit(int64 base_value,
*** 5238,5243 ****
--- 5238,5272 ----
/*
+ * Parse value as some known memory unit to their size in bytes.
+ * Used in pg_size_bytes function. Against convert_to_base_unit, a string
+ * comparation is case insensitive.
+ */
+ bool
+ parse_memory_unit(const char *unit, int *multiplier,
+ const char **hintmsg)
+ {
+ int i;
+
+ for (i = 0; *memory_unit_conversion_table[i].unit; i++)
+ {
+ const unit_conversion *conv = &memory_unit_conversion_table[i];
+
+ if ( conv->base_unit == GUC_UNIT_KB &&
+ strcasecmp(unit, conv->unit) == 0)
+ {
+ *multiplier = conv->multiplier;
+ return true;
+ }
+ }
+
+ *hintmsg = memory_units_hint;
+
+ return false;
+ }
+
+
+ /*
* Try to parse value as an integer. The accepted formats are the
* usual decimal, octal, or hexadecimal formats, optionally followed by
* a unit name if "flags" indicates a unit is allowed.
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/include/utils/guc.h b/src/include/utils/guc.h
new file mode 100644
index dc167f9..007d9ab
*** a/src/include/utils/guc.h
--- b/src/include/utils/guc.h
*************** extern int NewGUCNestLevel(void);
*** 357,362 ****
--- 357,364 ----
extern void AtEOXact_GUC(bool isCommit, int nestLevel);
extern void BeginReportingGUCOptions(void);
extern void ParseLongOption(const char *string, char **name, char **value);
+ extern bool parse_memory_unit(const char *unit, int *multiplier,
+ const char **hintmsg);
extern bool parse_int(const char *value, int *result, int flags,
const char **hintmsg);
extern bool parse_real(const char *value, double *result);
diff --git a/src/test/regress/expected/dbsize.out b/src/test/regress/expected/dbsize.out
new file mode 100644
index aa513e7..7b3a764
*** 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,77 ----
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 | 909495 TB | -909495 TB
! (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 | 909495 TB | -909495 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
! 1000000000000000000.5 | 909495 TB | -909495 TB
! (14 rows)
!
! SELECT pg_size_bytes(size) FROM
! (VALUES('1'), ('1kB'), ('1MB'), (' 1 GB'), ('1.5 GB '),
! ('1TB'), ('3000 TB')) x(size);
! pg_size_bytes
! ------------------
! 1
! 1024
! 1048576
! 1073741824
! 1610612736
! 1099511627776
! 3298534883328000
! (7 rows)
+ -- case insensitive units are supported
+ SELECT pg_size_bytes(size) FROM
+ (VALUES('1'), ('1kb'), ('1mb'), (' 1 Gb'), ('1.5 gB '),
+ ('1tb')) x(size);
+ pg_size_bytes
+ ---------------
+ 1
+ 1024
+ 1048576
+ 1073741824
+ 1610612736
+ 1099511627776
+ (6 rows)
+
+ --should fail
+ SELECT pg_size_bytes('1 AB');
+ ERROR: invalid unit "AB"
+ HINT: Valid units for this parameter are "kB", "MB", "GB", and "TB".
+ 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..7f5cfc1
*** 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,28 ----
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'), ('1kB'), ('1MB'), (' 1 GB'), ('1.5 GB '),
! ('1TB'), ('3000 TB')) x(size);
!
! -- case insensitive units are supported
! SELECT pg_size_bytes(size) FROM
! (VALUES('1'), ('1kb'), ('1mb'), (' 1 Gb'), ('1.5 gB '),
! ('1tb')) 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
