Re: [PATCH] expand the units that pg_size_pretty supports on output - Mailing list pgsql-hackers
| From | David Christensen |
|---|---|
| Subject | Re: [PATCH] expand the units that pg_size_pretty supports on output |
| Date | |
| Msg-id | lzeecauig8.fsf@veeddrois.attlocal.net Whole thread Raw |
| In response to | Re: [PATCH] expand the units that pg_size_pretty supports on output (David Rowley <dgrowleyml@gmail.com>) |
| Responses |
Re: [PATCH] expand the units that pg_size_pretty supports on output
(Tom Lane <tgl@sss.pgh.pa.us>)
Re: [PATCH] expand the units that pg_size_pretty supports on output (David Rowley <dgrowleyml@gmail.com>) |
| List | pgsql-hackers |
David Rowley writes:
> On Wed, 7 Jul 2021 at 02:46, David Christensen
> <david.christensen@crunchydata.com> wrote:
>> if we do decide to expand the units table there will be a
>> few additional changes (most significantly, the return value of `pg_size_bytes()` will need to switch
>> to `numeric`).
>
> I wonder if it's worth changing pg_size_bytes() to return NUMERIC
> regardless of if we add any additional units or not.
>
> Would you like to create 2 patches, one to change the return type and
> another to add the new units, both based on top of the v2 patch I sent
> earlier?
>
> David
Enclosed is the patch to change the return type to numeric, as well as one for expanding units to
add PB and EB.
If we decide to expand further, the current implementation will need to change, as
ZB and YB have 70 and 80 bits needing to be shifted accordingly, so int64 isn't enough to hold
it. (I fixed this particular issue in the original version of this patch, so there is at least a
blueprint of how to fix.)
I figured that PB and EB are probably good enough additions at this point, so we can debate whether
to add the others.
Best,
David
From 57bd2eafff5404313426a10f63b0b7098314998a Mon Sep 17 00:00:00 2001
From: David Christensen <david.christensen@crunchydata.com>
Date: Wed, 7 Jul 2021 11:46:09 -0500
Subject: [PATCH] Make pg_size_bytes() return numeric instead of bigint
---
src/backend/utils/adt/dbsize.c | 7 ++++---
src/include/catalog/pg_proc.dat | 2 +-
src/test/regress/expected/dbsize.out | 17 +++++++++++++----
src/test/regress/sql/dbsize.sql | 6 ++++--
4 files changed, 22 insertions(+), 10 deletions(-)
diff --git a/src/backend/utils/adt/dbsize.c b/src/backend/utils/adt/dbsize.c
index 4b7331d85c..a5a3ebe34e 100644
--- a/src/backend/utils/adt/dbsize.c
+++ b/src/backend/utils/adt/dbsize.c
@@ -813,10 +813,11 @@ pg_size_bytes(PG_FUNCTION_ARGS)
}
}
- result = DatumGetInt64(DirectFunctionCall1(numeric_int8,
- NumericGetDatum(num)));
+ /* now finally truncate, since this is always in integer-like units */
+ num = DatumGetNumeric(DirectFunctionCall1(numeric_ceil,
+ NumericGetDatum(num)));
- PG_RETURN_INT64(result);
+ PG_RETURN_NUMERIC(num);
}
/*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index fde251fa4f..73326e3618 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7187,7 +7187,7 @@
prosrc => 'pg_size_pretty_numeric' },
{ oid => '3334',
descr => 'convert a size in human-readable format with size units into bytes',
- proname => 'pg_size_bytes', prorettype => 'int8', proargtypes => 'text',
+ proname => 'pg_size_bytes', prorettype => 'numeric', proargtypes => 'text',
prosrc => 'pg_size_bytes' },
{ oid => '2997',
descr => 'disk space usage for the specified table, including TOAST, free space and visibility map',
diff --git a/src/test/regress/expected/dbsize.out b/src/test/regress/expected/dbsize.out
index e901a2c92a..2950e017d0 100644
--- a/src/test/regress/expected/dbsize.out
+++ b/src/test/regress/expected/dbsize.out
@@ -101,6 +101,19 @@ SELECT size, pg_size_bytes(size) FROM
-.0 gb | 0
(8 rows)
+-- valid inputs outside bigint range (previous errors)
+SELECT pg_size_bytes('9223372036854775807.9');
+ pg_size_bytes
+---------------------
+ 9223372036854775808
+(1 row)
+
+SELECT pg_size_bytes('1e100');
+ pg_size_bytes
+-------------------------------------------------------------------------------------------------------
+ 10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
+(1 row)
+
-- invalid inputs
SELECT pg_size_bytes('1 AB');
ERROR: invalid size: "1 AB"
@@ -114,10 +127,6 @@ SELECT pg_size_bytes('1 AB A ');
ERROR: invalid size: "1 AB A "
DETAIL: Invalid size unit: "AB A".
HINT: Valid units are "bytes", "kB", "MB", "GB", and "TB".
-SELECT pg_size_bytes('9223372036854775807.9');
-ERROR: bigint out of range
-SELECT pg_size_bytes('1e100');
-ERROR: bigint out of range
SELECT pg_size_bytes('1e1000000000000000000');
ERROR: value overflows numeric format
SELECT pg_size_bytes('1 byte'); -- the singular "byte" is not supported
diff --git a/src/test/regress/sql/dbsize.sql b/src/test/regress/sql/dbsize.sql
index d10a4d7f68..e88184e9c9 100644
--- a/src/test/regress/sql/dbsize.sql
+++ b/src/test/regress/sql/dbsize.sql
@@ -30,12 +30,14 @@ SELECT size, pg_size_bytes(size) FROM
(VALUES ('-1.'), ('-1.kb'), ('-1. kb'), ('-0. gb'),
('-.1'), ('-.1kb'), ('-.1 kb'), ('-.0 gb')) x(size);
+-- valid inputs outside bigint range (previous errors)
+SELECT pg_size_bytes('9223372036854775807.9');
+SELECT pg_size_bytes('1e100');
+
-- invalid inputs
SELECT pg_size_bytes('1 AB');
SELECT pg_size_bytes('1 AB A');
SELECT pg_size_bytes('1 AB A ');
-SELECT pg_size_bytes('9223372036854775807.9');
-SELECT pg_size_bytes('1e100');
SELECT pg_size_bytes('1e1000000000000000000');
SELECT pg_size_bytes('1 byte'); -- the singular "byte" is not supported
SELECT pg_size_bytes('');
--
2.30.1 (Apple Git-130)
From 8d6972abf01c01d2c8713d94bbb46ae44682195e Mon Sep 17 00:00:00 2001
From: David Christensen <david.christensen@crunchydata.com>
Date: Wed, 7 Jul 2021 12:37:41 -0500
Subject: [PATCH] Expand units for pg_size_*() up through EB
---
src/backend/utils/adt/dbsize.c | 5 +-
src/test/regress/expected/dbsize.out | 138 +++++++++++++++++----------
src/test/regress/sql/dbsize.sql | 33 +++++--
3 files changed, 114 insertions(+), 62 deletions(-)
diff --git a/src/backend/utils/adt/dbsize.c b/src/backend/utils/adt/dbsize.c
index a5a3ebe34e..cd68ff10c8 100644
--- a/src/backend/utils/adt/dbsize.c
+++ b/src/backend/utils/adt/dbsize.c
@@ -54,6 +54,8 @@ static const struct size_pretty_unit size_pretty_units[] = {
{"MB", 20 * 1024 - 1, 10, 20},
{"GB", 20 * 1024 - 1, 10, 30},
{"TB", 20 * 1024 - 1, 10, 40},
+ {"PB", 20 * 1024 - 1, 10, 50},
+ {"EB", 20 * 1024 - 1, 10, 60},
{NULL, 0, 0, 0}
};
@@ -799,7 +801,8 @@ pg_size_bytes(PG_FUNCTION_ARGS)
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("invalid size: \"%s\"", text_to_cstring(arg)),
errdetail("Invalid size unit: \"%s\".", strptr),
- errhint("Valid units are \"bytes\", \"kB\", \"MB\", \"GB\", and \"TB\".")));
+ errhint("Valid units are \"bytes\", \"kB\", \"MB\", \"GB\", \"TB\", "
+ "\"PB\", and \"EB\".")));
if (multiplier > 1)
{
diff --git a/src/test/regress/expected/dbsize.out b/src/test/regress/expected/dbsize.out
index 2950e017d0..cb260f69a3 100644
--- a/src/test/regress/expected/dbsize.out
+++ b/src/test/regress/expected/dbsize.out
@@ -13,65 +13,96 @@ SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM
(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),
+ (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)
+ (1000000000000000000::numeric),
+ (1000000000000000000000::numeric),
+ (1000000000000000000000000::numeric),
+ (1000000000000000000000000000::numeric),
+ (1000000000000000000000000000000::numeric),
+ (10.5::numeric),
+ (1000.5::numeric),
+ (1000000.5::numeric),
+ (1000000000.5::numeric),
+ (1000000000000.5::numeric),
+ (1000000000000000.5::numeric),
+ (1000000000000000000.5::numeric),
+ (1000000000000000000000.5::numeric),
+ (1000000000000000000000000.5::numeric),
+ (1000000000000000000000000000.5::numeric),
+ (1000000000000000000000000000000.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
+ 1000000000000000000000 | 867 EB | -867 EB
+ 1000000000000000000000000 | 867362 EB | -867362 EB
+ 1000000000000000000000000000 | 867361738 EB | -867361738 EB
+ 1000000000000000000000000000000 | 867361737988 EB | -867361737988 EB
+ 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
+ 1000000000000000000000.5 | 867 EB | -867 EB
+ 1000000000000000000000000.5 | 867362 EB | -867362 EB
+ 1000000000000000000000000000.5 | 867361738 EB | -867361738 EB
+ 1000000000000000000000000000000.5 | 867361737988 EB | -867361737988 EB
+(22 rows)
SELECT size, pg_size_bytes(size) FROM
(VALUES ('1'), ('123bytes'), ('1kB'), ('1MB'), (' 1 GB'), ('1.5 GB '),
- ('1TB'), ('3000 TB'), ('1e6 MB')) x(size);
- size | pg_size_bytes
-----------+------------------
- 1 | 1
- 123bytes | 123
- 1kB | 1024
- 1MB | 1048576
- 1 GB | 1073741824
- 1.5 GB | 1610612736
- 1TB | 1099511627776
- 3000 TB | 3298534883328000
- 1e6 MB | 1048576000000
-(9 rows)
+ ('1TB'), ('3000 TB'), ('1e6 MB'), ('99 PB'), ('45 EB')) x(size);
+ size | pg_size_bytes
+----------+----------------------
+ 1 | 1
+ 123bytes | 123
+ 1kB | 1024
+ 1MB | 1048576
+ 1 GB | 1073741824
+ 1.5 GB | 1610612736
+ 1TB | 1099511627776
+ 3000 TB | 3298534883328000
+ 1e6 MB | 1048576000000
+ 99 PB | 111464090777419776
+ 45 EB | 51881467707308113920
+(11 rows)
-- case-insensitive units are supported
SELECT size, pg_size_bytes(size) FROM
(VALUES ('1'), ('123bYteS'), ('1kb'), ('1mb'), (' 1 Gb'), ('1.5 gB '),
- ('1tb'), ('3000 tb'), ('1e6 mb')) x(size);
- size | pg_size_bytes
-----------+------------------
- 1 | 1
- 123bYteS | 123
- 1kb | 1024
- 1mb | 1048576
- 1 Gb | 1073741824
- 1.5 gB | 1610612736
- 1tb | 1099511627776
- 3000 tb | 3298534883328000
- 1e6 mb | 1048576000000
-(9 rows)
+ ('1tb'), ('3000 tb'), ('1e6 mb'), ('99 pb'), ('45 eB')) x(size);
+ size | pg_size_bytes
+----------+----------------------
+ 1 | 1
+ 123bYteS | 123
+ 1kb | 1024
+ 1mb | 1048576
+ 1 Gb | 1073741824
+ 1.5 gB | 1610612736
+ 1tb | 1099511627776
+ 3000 tb | 3298534883328000
+ 1e6 mb | 1048576000000
+ 99 pb | 111464090777419776
+ 45 eB | 51881467707308113920
+(11 rows)
-- negative numbers are supported
SELECT size, pg_size_bytes(size) FROM
(VALUES ('-1'), ('-123bytes'), ('-1kb'), ('-1mb'), (' -1 Gb'), ('-1.5 gB '),
- ('-1tb'), ('-3000 TB'), ('-10e-1 MB')) x(size);
+ ('-1tb'), ('-3000 TB'), ('-10e-1 MB'), ('-19e-4eb')) x(size);
size | pg_size_bytes
-----------+-------------------
-1 | -1
@@ -83,7 +114,8 @@ SELECT size, pg_size_bytes(size) FROM
-1tb | -1099511627776
-3000 TB | -3298534883328000
-10e-1 MB | -1048576
-(9 rows)
+ -19e-4eb | -2190550858753009
+(10 rows)
-- different cases with allowed points
SELECT size, pg_size_bytes(size) FROM
@@ -118,21 +150,21 @@ SELECT pg_size_bytes('1e100');
SELECT pg_size_bytes('1 AB');
ERROR: invalid size: "1 AB"
DETAIL: Invalid size unit: "AB".
-HINT: Valid units are "bytes", "kB", "MB", "GB", and "TB".
+HINT: Valid units are "bytes", "kB", "MB", "GB", "TB", "PB", and "EB".
SELECT pg_size_bytes('1 AB A');
ERROR: invalid size: "1 AB A"
DETAIL: Invalid size unit: "AB A".
-HINT: Valid units are "bytes", "kB", "MB", "GB", and "TB".
+HINT: Valid units are "bytes", "kB", "MB", "GB", "TB", "PB", and "EB".
SELECT pg_size_bytes('1 AB A ');
ERROR: invalid size: "1 AB A "
DETAIL: Invalid size unit: "AB A".
-HINT: Valid units are "bytes", "kB", "MB", "GB", and "TB".
+HINT: Valid units are "bytes", "kB", "MB", "GB", "TB", "PB", and "EB".
SELECT pg_size_bytes('1e1000000000000000000');
ERROR: value overflows numeric format
SELECT pg_size_bytes('1 byte'); -- the singular "byte" is not supported
ERROR: invalid size: "1 byte"
DETAIL: Invalid size unit: "byte".
-HINT: Valid units are "bytes", "kB", "MB", "GB", and "TB".
+HINT: Valid units are "bytes", "kB", "MB", "GB", "TB", "PB", and "EB".
SELECT pg_size_bytes('');
ERROR: invalid size: ""
SELECT pg_size_bytes('kb');
@@ -150,6 +182,6 @@ ERROR: invalid size: ".+912"
SELECT pg_size_bytes('+912+ kB');
ERROR: invalid size: "+912+ kB"
DETAIL: Invalid size unit: "+ kB".
-HINT: Valid units are "bytes", "kB", "MB", "GB", and "TB".
+HINT: Valid units are "bytes", "kB", "MB", "GB", "TB", "PB", and "EB".
SELECT pg_size_bytes('++123 kB');
ERROR: invalid size: "++123 kB"
diff --git a/src/test/regress/sql/dbsize.sql b/src/test/regress/sql/dbsize.sql
index e88184e9c9..cfa993f63f 100644
--- a/src/test/regress/sql/dbsize.sql
+++ b/src/test/regress/sql/dbsize.sql
@@ -4,26 +4,43 @@ SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM
(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),
+ (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);
+ (1000000000000000000::numeric),
+ (1000000000000000000000::numeric),
+ (1000000000000000000000000::numeric),
+ (1000000000000000000000000000::numeric),
+ (1000000000000000000000000000000::numeric),
+ (10.5::numeric),
+ (1000.5::numeric),
+ (1000000.5::numeric),
+ (1000000000.5::numeric),
+ (1000000000000.5::numeric),
+ (1000000000000000.5::numeric),
+ (1000000000000000000.5::numeric),
+ (1000000000000000000000.5::numeric),
+ (1000000000000000000000000.5::numeric),
+ (1000000000000000000000000000.5::numeric),
+ (1000000000000000000000000000000.5::numeric)
+ ) x(size);
SELECT size, pg_size_bytes(size) FROM
(VALUES ('1'), ('123bytes'), ('1kB'), ('1MB'), (' 1 GB'), ('1.5 GB '),
- ('1TB'), ('3000 TB'), ('1e6 MB')) x(size);
+ ('1TB'), ('3000 TB'), ('1e6 MB'), ('99 PB'), ('45 EB')) x(size);
-- case-insensitive units are supported
SELECT size, pg_size_bytes(size) FROM
(VALUES ('1'), ('123bYteS'), ('1kb'), ('1mb'), (' 1 Gb'), ('1.5 gB '),
- ('1tb'), ('3000 tb'), ('1e6 mb')) x(size);
+ ('1tb'), ('3000 tb'), ('1e6 mb'), ('99 pb'), ('45 eB')) x(size);
-- negative numbers are supported
SELECT size, pg_size_bytes(size) FROM
(VALUES ('-1'), ('-123bytes'), ('-1kb'), ('-1mb'), (' -1 Gb'), ('-1.5 gB '),
- ('-1tb'), ('-3000 TB'), ('-10e-1 MB')) x(size);
+ ('-1tb'), ('-3000 TB'), ('-10e-1 MB'), ('-19e-4eb')) x(size);
-- different cases with allowed points
SELECT size, pg_size_bytes(size) FROM
--
2.30.1 (Apple Git-130)
pgsql-hackers by date: