[PATCH] expand the units that pg_size_pretty supports on output

From 3bb6c7c388db49c78d1e027f86d77e7f744a1667 Mon Sep 17 00:00:00 2001
From: David Christensen <david.christensen@crunchydata.com>
Date: Wed, 28 Apr 2021 10:38:37 -0500
Subject: [PATCH] Expand the supported units in pg_size_bytes to cover all
 units

---
 src/backend/utils/adt/dbsize.c       |  62 +++++++++-----
 src/include/catalog/pg_proc.dat      |   2 +-
 src/test/regress/expected/dbsize.out | 117 ++++++++++++++++-----------
 src/test/regress/sql/dbsize.sql      |  14 ++--
 4 files changed, 121 insertions(+), 74 deletions(-)

diff --git a/src/backend/utils/adt/dbsize.c b/src/backend/utils/adt/dbsize.c
index 31ed49f8ed..d517a90483 100644
--- a/src/backend/utils/adt/dbsize.c
+++ b/src/backend/utils/adt/dbsize.c
@@ -760,8 +760,9 @@ pg_size_bytes(PG_FUNCTION_ARGS)
 		char	   *cp;
 
 		/*
-		 * Note we might one day support EB units, so if what follows 'E'
-		 * isn't a number, just treat it all as a unit to be parsed.
+		 * If what follows 'e' isn't a number, we just treat it all as a unit
+		 * to be parsed; this allows us to support both exponential notation
+		 * and EB units.
 		 */
 		exponent = strtol(endptr + 1, &cp, 10);
 		(void) exponent;		/* Silence -Wunused-result warnings */
@@ -791,7 +792,8 @@ pg_size_bytes(PG_FUNCTION_ARGS)
 	/* Handle possible unit */
 	if (*strptr != '\0')
 	{
-		int64		multiplier = 0;
+		int64		multiplier = 1;
+		int         i;
 
 		/* Trim any trailing whitespace */
 		endptr = str + VARSIZE_ANY_EXHDR(arg) - 1;
@@ -803,25 +805,39 @@ pg_size_bytes(PG_FUNCTION_ARGS)
 		*endptr = '\0';
 
 		/* Parse the unit case-insensitively */
-		if (pg_strcasecmp(strptr, "bytes") == 0)
-			multiplier = (int64) 1;
-		else if (pg_strcasecmp(strptr, "kb") == 0)
-			multiplier = (int64) 1024;
-		else if (pg_strcasecmp(strptr, "mb") == 0)
-			multiplier = ((int64) 1024) * 1024;
-
-		else if (pg_strcasecmp(strptr, "gb") == 0)
-			multiplier = ((int64) 1024) * 1024 * 1024;
-
-		else if (pg_strcasecmp(strptr, "tb") == 0)
-			multiplier = ((int64) 1024) * 1024 * 1024 * 1024;
+		const int unit_count = 9; /* sizeof units table */
+		const char *units[unit_count] = {
+			"bytes",
+			"kb",
+			"mb",
+			"gb",
+			"tb",
+			"pb",
+			"eb",
+			"zb",
+			"yb",
+		};
+
+		for (i = 0; i < unit_count; i++) {
+			printf("strptr: %s units: %s", strptr, units[i]);
+			if (pg_strcasecmp(strptr, units[i]) == 0)
+				break;
+			/* 
+			 * Note: int64 isn't large enough to store the full multiplier
+			 * going past ~ 9EB, but since this is a fixed value, we can apply
+			 * it twice, thus storing use 2 ** 5 = 32 here, but 2 ** 10 = 1024
+			 * on actual conversion to numeric.
+			 */
+			multiplier *= 32;
+		}
 
-		else
+		if (i == unit_count)
 			ereport(ERROR,
 					(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\", \"EB\", \"ZB\", and \"YB\".")));
 
 		if (multiplier > 1)
 		{
@@ -832,13 +848,19 @@ pg_size_bytes(PG_FUNCTION_ARGS)
 			num = DatumGetNumeric(DirectFunctionCall2(numeric_mul,
 													  NumericGetDatum(mul_num),
 													  NumericGetDatum(num)));
+
+			/* second application to get around int64 limitations in unit multipliers */
+			num = DatumGetNumeric(DirectFunctionCall2(numeric_mul,
+													  NumericGetDatum(mul_num),
+													  NumericGetDatum(num)));
 		}
 	}
 
-	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 91f0ea2212..8be7598a79 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7200,7 +7200,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 b93d40fb02..8993522904 100644
--- a/src/test/regress/expected/dbsize.out
+++ b/src/test/regress/expected/dbsize.out
@@ -64,53 +64,65 @@ SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM
 
 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'), ('5.1ZB'),
+            ('1.17 YB')) 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
+ 5.1ZB    |    6021017265658797647463
+ 1.17 YB  | 1414443208949116134406226
+(13 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'), ('5.1Zb'),
+            ('1.17 yb')) 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
+ 5.1Zb    |    6021017265658797647463
+ 1.17 yb  | 1414443208949116134406226
+(13 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);
-   size    |   pg_size_bytes   
------------+-------------------
- -1        |                -1
- -123bytes |              -123
- -1kb      |             -1024
- -1mb      |          -1048576
-  -1 Gb    |       -1073741824
- -1.5 gB   |       -1610612736
- -1tb      |    -1099511627776
- -3000 TB  | -3298534883328000
- -10e-1 MB |          -1048576
-(9 rows)
+            ('-1tb'), ('-3000 TB'), ('-10e-1 MB'), ('-19e-4eb'), ('-18YB')) 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
+ -10e-1 MB |                    -1048576
+ -19e-4eb  |           -2190550858753009
+ -18YB     | -21760664753063325144711168
+(11 rows)
 
 -- different cases with allowed points
 SELECT size, pg_size_bytes(size) FROM
@@ -128,29 +140,38 @@ 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"
 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", "EB", "ZB", and "YB".
 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", "EB", "ZB", and "YB".
 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
+HINT:  Valid units are "bytes", "kB", "MB", "GB", "TB", "PB", "EB", "ZB", and "YB".
 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", "EB", "ZB", and "YB".
 SELECT pg_size_bytes('');
 ERROR:  invalid size: ""
 SELECT pg_size_bytes('kb');
@@ -168,6 +189,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", "EB", "ZB", and "YB".
 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 a97dacbf7b..2a4abcfc65 100644
--- a/src/test/regress/sql/dbsize.sql
+++ b/src/test/regress/sql/dbsize.sql
@@ -30,29 +30,33 @@ SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM
 
 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'), ('5.1ZB'),
+            ('1.17 YB')) 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'), ('5.1Zb'),
+            ('1.17 yb')) 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'), ('-18YB')) x(size);
 
 -- different cases with allowed points
 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 2efd906f8f4ae21d76dfa1c9e2e37468dec68560 Mon Sep 17 00:00:00 2001
From: David Christensen <david.christensen@crunchydata.com>
Date: Wed, 28 Apr 2021 10:38:37 -0500
Subject: [PATCH 2/2] Expand the supported units in pg_size_bytes to cover all
 units

---
 src/backend/utils/adt/dbsize.c       |  63 ++++++++++-----
 src/include/catalog/pg_proc.dat      |   2 +-
 src/test/regress/expected/dbsize.out | 117 ++++++++++++++++-----------
 src/test/regress/sql/dbsize.sql      |  14 ++--
 4 files changed, 120 insertions(+), 76 deletions(-)

diff --git a/src/backend/utils/adt/dbsize.c b/src/backend/utils/adt/dbsize.c
index f25ec5c1af..ffa6488da8 100644
--- a/src/backend/utils/adt/dbsize.c
+++ b/src/backend/utils/adt/dbsize.c
@@ -708,7 +708,6 @@ pg_size_bytes(PG_FUNCTION_ARGS)
 			   *endptr;
 	char		saved_char;
 	Numeric		num;
-	int64		result;
 	bool		have_digits = false;
 
 	str = text_to_cstring(arg);
@@ -760,8 +759,9 @@ pg_size_bytes(PG_FUNCTION_ARGS)
 		char	   *cp;
 
 		/*
-		 * Note we might one day support EB units, so if what follows 'E'
-		 * isn't a number, just treat it all as a unit to be parsed.
+		 * If what follows 'e' isn't a number, we just treat it all as a unit
+		 * to be parsed; this allows us to support both exponential notation
+		 * and EB units.
 		 */
 		exponent = strtol(endptr + 1, &cp, 10);
 		(void) exponent;		/* Silence -Wunused-result warnings */
@@ -791,7 +791,20 @@ pg_size_bytes(PG_FUNCTION_ARGS)
 	/* Handle possible unit */
 	if (*strptr != '\0')
 	{
-		int64		multiplier = 0;
+		int64		multiplier = 1;
+		int         i;
+		int         unit_count = 9; /* sizeof units table */
+		char       *units[] = {
+			"bytes",
+			"kb",
+			"mb",
+			"gb",
+			"tb",
+			"pb",
+			"eb",
+			"zb",
+			"yb",
+		};
 
 		/* Trim any trailing whitespace */
 		endptr = str + VARSIZE_ANY_EXHDR(arg) - 1;
@@ -802,26 +815,26 @@ pg_size_bytes(PG_FUNCTION_ARGS)
 		endptr++;
 		*endptr = '\0';
 
-		/* Parse the unit case-insensitively */
-		if (pg_strcasecmp(strptr, "bytes") == 0)
-			multiplier = (int64) 1;
-		else if (pg_strcasecmp(strptr, "kb") == 0)
-			multiplier = (int64) 1024;
-		else if (pg_strcasecmp(strptr, "mb") == 0)
-			multiplier = ((int64) 1024) * 1024;
-
-		else if (pg_strcasecmp(strptr, "gb") == 0)
-			multiplier = ((int64) 1024) * 1024 * 1024;
-
-		else if (pg_strcasecmp(strptr, "tb") == 0)
-			multiplier = ((int64) 1024) * 1024 * 1024 * 1024;
+		for (i = 0; i < unit_count; i++) {
+			printf("strptr: %s units: %s", strptr, units[i]);
+			if (pg_strcasecmp(strptr, units[i]) == 0)
+				break;
+			/* 
+			 * Note: int64 isn't large enough to store the full multiplier
+			 * going past ~ 9EB, but since this is a fixed value, we can apply
+			 * it twice, thus storing use 2 ** 5 = 32 here, but 2 ** 10 = 1024
+			 * on actual conversion to numeric.
+			 */
+			multiplier *= 32;
+		}
 
-		else
+		if (i == unit_count)
 			ereport(ERROR,
 					(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\", \"EB\", \"ZB\", and \"YB\".")));
 
 		if (multiplier > 1)
 		{
@@ -832,13 +845,19 @@ pg_size_bytes(PG_FUNCTION_ARGS)
 			num = DatumGetNumeric(DirectFunctionCall2(numeric_mul,
 													  NumericGetDatum(mul_num),
 													  NumericGetDatum(num)));
+
+			/* second application to get around int64 limitations in unit multipliers */
+			num = DatumGetNumeric(DirectFunctionCall2(numeric_mul,
+													  NumericGetDatum(mul_num),
+													  NumericGetDatum(num)));
 		}
 	}
 
-	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 acbcae4607..628dcd69a8 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7192,7 +7192,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 b93d40fb02..8993522904 100644
--- a/src/test/regress/expected/dbsize.out
+++ b/src/test/regress/expected/dbsize.out
@@ -64,53 +64,65 @@ SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM
 
 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'), ('5.1ZB'),
+            ('1.17 YB')) 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
+ 5.1ZB    |    6021017265658797647463
+ 1.17 YB  | 1414443208949116134406226
+(13 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'), ('5.1Zb'),
+            ('1.17 yb')) 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
+ 5.1Zb    |    6021017265658797647463
+ 1.17 yb  | 1414443208949116134406226
+(13 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);
-   size    |   pg_size_bytes   
------------+-------------------
- -1        |                -1
- -123bytes |              -123
- -1kb      |             -1024
- -1mb      |          -1048576
-  -1 Gb    |       -1073741824
- -1.5 gB   |       -1610612736
- -1tb      |    -1099511627776
- -3000 TB  | -3298534883328000
- -10e-1 MB |          -1048576
-(9 rows)
+            ('-1tb'), ('-3000 TB'), ('-10e-1 MB'), ('-19e-4eb'), ('-18YB')) 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
+ -10e-1 MB |                    -1048576
+ -19e-4eb  |           -2190550858753009
+ -18YB     | -21760664753063325144711168
+(11 rows)
 
 -- different cases with allowed points
 SELECT size, pg_size_bytes(size) FROM
@@ -128,29 +140,38 @@ 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"
 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", "EB", "ZB", and "YB".
 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", "EB", "ZB", and "YB".
 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
+HINT:  Valid units are "bytes", "kB", "MB", "GB", "TB", "PB", "EB", "ZB", and "YB".
 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", "EB", "ZB", and "YB".
 SELECT pg_size_bytes('');
 ERROR:  invalid size: ""
 SELECT pg_size_bytes('kb');
@@ -168,6 +189,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", "EB", "ZB", and "YB".
 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 a97dacbf7b..2a4abcfc65 100644
--- a/src/test/regress/sql/dbsize.sql
+++ b/src/test/regress/sql/dbsize.sql
@@ -30,29 +30,33 @@ SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM
 
 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'), ('5.1ZB'),
+            ('1.17 YB')) 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'), ('5.1Zb'),
+            ('1.17 yb')) 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'), ('-18YB')) x(size);
 
 -- different cases with allowed points
 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 e1d250ba19b13fb6858125b68adbbd6a78ed4393 Mon Sep 17 00:00:00 2001
From: David Christensen <david.christensen@crunchydata.com>
Date: Fri, 2 Apr 2021 14:35:31 -0500
Subject: [PATCH 1/2] Expand the units that pg_size_pretty(numeric) knows about

Rework the logic slightly to allow a lookup table-based approach for
displaying units.  Add units up to yottabytes, with easy expansion if
this ever needs to grow.
---
 doc/src/sgml/func.sgml               |  2 +-
 src/backend/utils/adt/dbsize.c       | 43 ++++++++++--------
 src/test/regress/expected/dbsize.out | 67 +++++++++++++++++++---------
 src/test/regress/sql/dbsize.sql      | 27 ++++++++---
 4 files changed, 94 insertions(+), 45 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 08b07f561e..f6739f150c 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26371,7 +26371,7 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
        </para>
        <para>
         Converts a size in bytes into a more easily human-readable format with
-        size units (bytes, kB, MB, GB or TB as appropriate).  Note that the
+        size units (bytes, kB, MB, GB, TB, PB, EB, ZB, or YB as appropriate).  Note that the
         units are powers of 2 rather than powers of 10, so 1kB is 1024 bytes,
         1MB is 1024<superscript>2</superscript> = 1048576 bytes, and so on.
        </para></entry>
diff --git a/src/backend/utils/adt/dbsize.c b/src/backend/utils/adt/dbsize.c
index 3c70bb5943..f25ec5c1af 100644
--- a/src/backend/utils/adt/dbsize.c
+++ b/src/backend/utils/adt/dbsize.c
@@ -638,7 +638,7 @@ pg_size_pretty_numeric(PG_FUNCTION_ARGS)
 	Numeric		size = PG_GETARG_NUMERIC(0);
 	Numeric		limit,
 				limit2;
-	char	   *result;
+	char	   *result = NULL;
 
 	limit = int64_to_numeric(10 * 1024);
 	limit2 = int64_to_numeric(10 * 1024 * 2 - 1);
@@ -660,31 +660,36 @@ pg_size_pretty_numeric(PG_FUNCTION_ARGS)
 		}
 		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
-			{
+			int idx, max_iter = 6; /* highest index of table_below */
+			char *output_formats[] = {
+				"%s MB",
+				"%s GB",
+				"%s TB",
+				"%s PB",
+				"%s EB",
+				"%s ZB",
+				"%s YB"
+			};
+
+			for (idx = 0; idx < max_iter; idx++) {
 				/* 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));
+					result = psprintf(output_formats[idx], numeric_to_cstring(size));
+					break;
 				}
 			}
+
+			if (!result) {
+				/* this uses the last format in the table above for anything else */
+
+				/* size >>= 10 */
+				size = numeric_shift_right(size, 10);
+				size = numeric_half_rounded(size);
+				result = psprintf(output_formats[max_iter], numeric_to_cstring(size));
+			}
 		}
 	}
 
diff --git a/src/test/regress/expected/dbsize.out b/src/test/regress/expected/dbsize.out
index e901a2c92a..b93d40fb02 100644
--- a/src/test/regress/expected/dbsize.out
+++ b/src/test/regress/expected/dbsize.out
@@ -13,27 +13,54 @@ 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 | 847 ZB         | -847 ZB
+      1000000000000000000000000000 | 827 YB         | -827 YB
+   1000000000000000000000000000000 | 827181 YB      | -827181 YB
+                              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 | 847 ZB         | -847 ZB
+    1000000000000000000000000000.5 | 827 YB         | -827 YB
+ 1000000000000000000000000000000.5 | 827181 YB      | -827181 YB
+(22 rows)
 
 SELECT size, pg_size_bytes(size) FROM
     (VALUES ('1'), ('123bytes'), ('1kB'), ('1MB'), (' 1 GB'), ('1.5 GB '),
diff --git a/src/test/regress/sql/dbsize.sql b/src/test/regress/sql/dbsize.sql
index d10a4d7f68..a97dacbf7b 100644
--- a/src/test/regress/sql/dbsize.sql
+++ b/src/test/regress/sql/dbsize.sql
@@ -4,12 +4,29 @@ 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 '),
-- 
2.30.1 (Apple Git-130)

From ac30b06e3ddcb57eebb380560c2f4a47430dfd74 Mon Sep 17 00:00:00 2001
From: David Christensen <david.christensen@crunchydata.com>
Date: Tue, 29 Jun 2021 10:20:05 -0500
Subject: [PATCH 1/2] Refactor pg_size_pretty and pg_size_bytes to allow for
 supported unit expansion

---
 src/backend/utils/adt/dbsize.c       | 90 ++++++++++++++++------------
 src/include/catalog/pg_proc.dat      |  2 +-
 src/test/regress/expected/dbsize.out |  4 --
 src/test/regress/sql/dbsize.sql      |  2 -
 4 files changed, 53 insertions(+), 45 deletions(-)

diff --git a/src/backend/utils/adt/dbsize.c b/src/backend/utils/adt/dbsize.c
index 9c39e7d3b3..df08845932 100644
--- a/src/backend/utils/adt/dbsize.c
+++ b/src/backend/utils/adt/dbsize.c
@@ -638,7 +638,7 @@ pg_size_pretty_numeric(PG_FUNCTION_ARGS)
 	Numeric		size = PG_GETARG_NUMERIC(0);
 	Numeric		limit,
 				limit2;
-	char	   *result;
+	char	   *result = NULL;
 
 	limit = int64_to_numeric(10 * 1024);
 	limit2 = int64_to_numeric(10 * 1024 * 2 - 1);
@@ -660,31 +660,32 @@ pg_size_pretty_numeric(PG_FUNCTION_ARGS)
 		}
 		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
-			{
+			int idx, max_iter = 2; /* highest index of table_below */
+			char *output_formats[] = {
+				"%s MB",
+				"%s GB",
+				"%s TB"
+			};
+
+			for (idx = 0; idx < max_iter; idx++) {
 				/* 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));
+					result = psprintf(output_formats[idx], numeric_to_cstring(size));
+					break;
 				}
 			}
+
+			if (!result) {
+				/* this uses the last format in the table above for anything else */
+
+				/* size >>= 10 */
+				size = numeric_shift_right(size, 10);
+				size = numeric_half_rounded(size);
+				result = psprintf(output_formats[max_iter], numeric_to_cstring(size));
+			}
 		}
 	}
 
@@ -703,7 +704,6 @@ pg_size_bytes(PG_FUNCTION_ARGS)
 			   *endptr;
 	char		saved_char;
 	Numeric		num;
-	int64		result;
 	bool		have_digits = false;
 
 	str = text_to_cstring(arg);
@@ -786,7 +786,16 @@ pg_size_bytes(PG_FUNCTION_ARGS)
 	/* Handle possible unit */
 	if (*strptr != '\0')
 	{
-		int64		multiplier = 0;
+		int64		multiplier = 1;
+		int         i;
+		int         unit_count = 5; /* sizeof units table */
+		char       *units[] = {
+			"bytes",
+			"kb",
+			"mb",
+			"gb",
+			"tb",
+		};
 
 		/* Trim any trailing whitespace */
 		endptr = str + VARSIZE_ANY_EXHDR(arg) - 1;
@@ -797,21 +806,20 @@ pg_size_bytes(PG_FUNCTION_ARGS)
 		endptr++;
 		*endptr = '\0';
 
-		/* Parse the unit case-insensitively */
-		if (pg_strcasecmp(strptr, "bytes") == 0)
-			multiplier = (int64) 1;
-		else if (pg_strcasecmp(strptr, "kb") == 0)
-			multiplier = (int64) 1024;
-		else if (pg_strcasecmp(strptr, "mb") == 0)
-			multiplier = ((int64) 1024) * 1024;
-
-		else if (pg_strcasecmp(strptr, "gb") == 0)
-			multiplier = ((int64) 1024) * 1024 * 1024;
-
-		else if (pg_strcasecmp(strptr, "tb") == 0)
-			multiplier = ((int64) 1024) * 1024 * 1024 * 1024;
+		for (i = 0; i < unit_count; i++) {
+			printf("strptr: %s units: %s", strptr, units[i]);
+			if (pg_strcasecmp(strptr, units[i]) == 0)
+				break;
+			/* 
+			 * Note: int64 isn't large enough to store the full multiplier
+			 * going past ~ 9EB, but since this is a fixed value, we can apply
+			 * it twice, thus storing use 2 ** 5 = 32 here, but 2 ** 10 = 1024
+			 * on actual conversion to numeric.
+			 */
+			multiplier *= 32;
+		}
 
-		else
+		if (i == unit_count)
 			ereport(ERROR,
 					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 					 errmsg("invalid size: \"%s\"", text_to_cstring(arg)),
@@ -827,13 +835,19 @@ pg_size_bytes(PG_FUNCTION_ARGS)
 			num = DatumGetNumeric(DirectFunctionCall2(numeric_mul,
 													  NumericGetDatum(mul_num),
 													  NumericGetDatum(num)));
+
+			/* second application to get around int64 limitations in unit multipliers */
+			num = DatumGetNumeric(DirectFunctionCall2(numeric_mul,
+													  NumericGetDatum(mul_num),
+													  NumericGetDatum(num)));
 		}
 	}
 
-	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..624948ccd2 100644
--- a/src/test/regress/expected/dbsize.out
+++ b/src/test/regress/expected/dbsize.out
@@ -114,10 +114,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..37023a01c3 100644
--- a/src/test/regress/sql/dbsize.sql
+++ b/src/test/regress/sql/dbsize.sql
@@ -34,8 +34,6 @@ SELECT size, pg_size_bytes(size) FROM
 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 e3fc63d9be6af5725a0ed24e16513cb711631f4a Mon Sep 17 00:00:00 2001
From: David Christensen <david.christensen@crunchydata.com>
Date: Tue, 29 Jun 2021 10:02:50 -0500
Subject: [PATCH 2/2] Full expansion of all units

---
 doc/src/sgml/func.sgml               |   2 +-
 src/backend/utils/adt/dbsize.c       |  22 +++-
 src/test/regress/expected/dbsize.out | 180 +++++++++++++++++----------
 src/test/regress/sql/dbsize.sql      |  39 ++++--
 4 files changed, 164 insertions(+), 79 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 6388385edc..687d2a7ac8 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26354,7 +26354,7 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
        </para>
        <para>
         Converts a size in bytes into a more easily human-readable format with
-        size units (bytes, kB, MB, GB or TB as appropriate).  Note that the
+        size units (bytes, kB, MB, GB, TB, PB, EB, ZB, or YB as appropriate).  Note that the
         units are powers of 2 rather than powers of 10, so 1kB is 1024 bytes,
         1MB is 1024<superscript>2</superscript> = 1048576 bytes, and so on.
        </para></entry>
diff --git a/src/backend/utils/adt/dbsize.c b/src/backend/utils/adt/dbsize.c
index df08845932..5650bb9f2b 100644
--- a/src/backend/utils/adt/dbsize.c
+++ b/src/backend/utils/adt/dbsize.c
@@ -660,11 +660,15 @@ pg_size_pretty_numeric(PG_FUNCTION_ARGS)
 		}
 		else
 		{
-			int idx, max_iter = 2; /* highest index of table_below */
+			int idx, max_iter = 6; /* highest index of table_below */
 			char *output_formats[] = {
 				"%s MB",
 				"%s GB",
-				"%s TB"
+				"%s TB",
+				"%s PB",
+				"%s EB",
+				"%s ZB",
+				"%s YB"
 			};
 
 			for (idx = 0; idx < max_iter; idx++) {
@@ -755,8 +759,9 @@ pg_size_bytes(PG_FUNCTION_ARGS)
 		char	   *cp;
 
 		/*
-		 * Note we might one day support EB units, so if what follows 'E'
-		 * isn't a number, just treat it all as a unit to be parsed.
+		 * If what follows 'e' isn't a number, we just treat it all as a unit
+		 * to be parsed; this allows us to support both exponential notation
+		 * and EB units.
 		 */
 		exponent = strtol(endptr + 1, &cp, 10);
 		(void) exponent;		/* Silence -Wunused-result warnings */
@@ -788,13 +793,17 @@ pg_size_bytes(PG_FUNCTION_ARGS)
 	{
 		int64		multiplier = 1;
 		int         i;
-		int         unit_count = 5; /* sizeof units table */
+		int         unit_count = 9; /* sizeof units table */
 		char       *units[] = {
 			"bytes",
 			"kb",
 			"mb",
 			"gb",
 			"tb",
+			"pb",
+			"eb",
+			"zb",
+			"yb",
 		};
 
 		/* Trim any trailing whitespace */
@@ -824,7 +833,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\", \"EB\", \"ZB\", and \"YB\".")));
 
 		if (multiplier > 1)
 		{
diff --git a/src/test/regress/expected/dbsize.out b/src/test/regress/expected/dbsize.out
index 624948ccd2..8993522904 100644
--- a/src/test/regress/expected/dbsize.out
+++ b/src/test/regress/expected/dbsize.out
@@ -13,77 +13,116 @@ 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 | 847 ZB         | -847 ZB
+      1000000000000000000000000000 | 827 YB         | -827 YB
+   1000000000000000000000000000000 | 827181 YB      | -827181 YB
+                              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 | 847 ZB         | -847 ZB
+    1000000000000000000000000000.5 | 827 YB         | -827 YB
+ 1000000000000000000000000000000.5 | 827181 YB      | -827181 YB
+(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'), ('5.1ZB'),
+            ('1.17 YB')) 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
+ 5.1ZB    |    6021017265658797647463
+ 1.17 YB  | 1414443208949116134406226
+(13 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'), ('5.1Zb'),
+            ('1.17 yb')) 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
+ 5.1Zb    |    6021017265658797647463
+ 1.17 yb  | 1414443208949116134406226
+(13 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);
-   size    |   pg_size_bytes   
------------+-------------------
- -1        |                -1
- -123bytes |              -123
- -1kb      |             -1024
- -1mb      |          -1048576
-  -1 Gb    |       -1073741824
- -1.5 gB   |       -1610612736
- -1tb      |    -1099511627776
- -3000 TB  | -3298534883328000
- -10e-1 MB |          -1048576
-(9 rows)
+            ('-1tb'), ('-3000 TB'), ('-10e-1 MB'), ('-19e-4eb'), ('-18YB')) 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
+ -10e-1 MB |                    -1048576
+ -19e-4eb  |           -2190550858753009
+ -18YB     | -21760664753063325144711168
+(11 rows)
 
 -- different cases with allowed points
 SELECT size, pg_size_bytes(size) FROM
@@ -101,25 +140,38 @@ 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"
 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", "EB", "ZB", and "YB".
 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", "EB", "ZB", and "YB".
 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", "EB", "ZB", and "YB".
 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", "EB", "ZB", and "YB".
 SELECT pg_size_bytes('');
 ERROR:  invalid size: ""
 SELECT pg_size_bytes('kb');
@@ -137,6 +189,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", "EB", "ZB", and "YB".
 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 37023a01c3..2a4abcfc65 100644
--- a/src/test/regress/sql/dbsize.sql
+++ b/src/test/regress/sql/dbsize.sql
@@ -4,32 +4,55 @@ 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'), ('5.1ZB'),
+            ('1.17 YB')) 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'), ('5.1Zb'),
+            ('1.17 yb')) 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'), ('-18YB')) x(size);
 
 -- different cases with allowed points
 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');
-- 
2.30.1 (Apple Git-130)

diff --git a/src/backend/utils/adt/dbsize.c b/src/backend/utils/adt/dbsize.c
index 9c39e7d3b3..2fcfef76f9 100644
--- a/src/backend/utils/adt/dbsize.c
+++ b/src/backend/utils/adt/dbsize.c
@@ -34,6 +34,30 @@
 /* Divide by two and round towards positive infinity. */
 #define half_rounded(x)   (((x) + ((x) < 0 ? 0 : 1)) / 2)
 
+/*
+ * Units used in pg_size_pretty functions
+ */
+struct size_pretty_unit {
+	char	   *unit;			/* e.g bytes, kB, MB etc */
+	uint64		limit;			/* upper limit after reducing by shiftright
+								 * bits from previous units */
+	uint8		shiftRight;		/* number of bits to shift right by after
+								 * limit is exceeded */
+	uint8		multishift;		/* how many bits to shift "1" left by to make
+								 * one of these units. */
+	bool		doHalfRound;	/* perform halfround operation */
+};
+
+/* When adding units here also update the error message in pg_size_bytes */
+static const struct size_pretty_unit size_pretty_units[] = {
+	{"bytes", 10 * 1024, 9, 0, false}, /* keep one extra bit for rounding */
+	{"kB", 20 * 1024 - 1, 10, 10, true},
+	{"MB", 20 * 1024 - 1, 10, 20, true},
+	{"GB", 20 * 1024 - 1, 10, 30, true},
+	{"TB", 20 * 1024 - 1, 10, 40, true},
+	{NULL, 0, 0, 0, false}
+};
+
 /* Return physical size of directory contents, or 0 if dir doesn't exist */
 static int64
 db_dir_size(const char *path)
@@ -535,37 +559,30 @@ pg_size_pretty(PG_FUNCTION_ARGS)
 {
 	int64		size = PG_GETARG_INT64(0);
 	char		buf[64];
-	int64		limit = 10 * 1024;
-	int64		limit2 = limit * 2 - 1;
+	uint32		i;
 
-	if (Abs(size) < limit)
-		snprintf(buf, sizeof(buf), INT64_FORMAT " bytes", size);
-	else
+	for (i = 0; size_pretty_units[i].unit != NULL; i++)
 	{
-		size >>= 9;				/* keep one extra bit for rounding */
-		if (Abs(size) < limit2)
-			snprintf(buf, sizeof(buf), INT64_FORMAT " kB",
-					 half_rounded(size));
-		else
+		/*
+		 * Use this set of units if the number is under the limit or there
+		 * is no other unit to use.
+		 */
+		if (Abs(size) < size_pretty_units[i].limit ||
+			size_pretty_units[i + 1].unit == NULL)
 		{
-			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));
-				}
-			}
+			if (size_pretty_units[i].doHalfRound)
+				size = half_rounded(size);
+
+			snprintf(buf, sizeof(buf), INT64_FORMAT " %s", size,
+					 size_pretty_units[i].unit);
+			break;
 		}
+
+		/*
+		 * limit for the current unit has been exceeded.  Shift right by the
+		 * specified number of bits and try the next unit
+		 */
+		size >>= size_pretty_units[i].shiftRight;
 	}
 
 	PG_RETURN_TEXT_P(cstring_to_text(buf));
@@ -636,56 +653,34 @@ Datum
 pg_size_pretty_numeric(PG_FUNCTION_ARGS)
 {
 	Numeric		size = PG_GETARG_NUMERIC(0);
-	Numeric		limit,
-				limit2;
+	Numeric		limit;
 	char	   *result;
+	uint32		i;
 
-	limit = int64_to_numeric(10 * 1024);
-	limit2 = int64_to_numeric(10 * 1024 * 2 - 1);
-
-	if (numeric_is_less(numeric_absolute(size), limit))
+	for (i = 0; size_pretty_units[i].unit != NULL; i++)
 	{
-		result = psprintf("%s bytes", numeric_to_cstring(size));
-	}
-	else
-	{
-		/* keep one extra bit for rounding */
-		/* size >>= 9 */
-		size = numeric_shift_right(size, 9);
+		limit = int64_to_numeric(size_pretty_units[i].limit);
 
-		if (numeric_is_less(numeric_absolute(size), limit2))
-		{
-			size = numeric_half_rounded(size);
-			result = psprintf("%s kB", numeric_to_cstring(size));
-		}
-		else
+		/*
+		 * Use this set of units if the number is under the limit or there
+		 * is no other unit to use.
+		 */
+		if (numeric_is_less(numeric_absolute(size), limit) ||
+			size_pretty_units[i + 1].unit == NULL)
 		{
-			/* size >>= 10 */
-			size = numeric_shift_right(size, 10);
-			if (numeric_is_less(numeric_absolute(size), limit2))
-			{
+			if (size_pretty_units[i].doHalfRound)
 				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));
-				}
-			}
+
+			result = psprintf("%s %s", numeric_to_cstring(size),
+							  size_pretty_units[i].unit);
+			break;
 		}
+
+		/*
+		 * limit for the current unit has been exceeded.  Shift right by the
+		 * specified number of bits and try the next unit
+		 */
+		size = numeric_shift_right(size, size_pretty_units[i].shiftRight);
 	}
 
 	PG_RETURN_TEXT_P(cstring_to_text(result));
@@ -787,6 +782,7 @@ pg_size_bytes(PG_FUNCTION_ARGS)
 	if (*strptr != '\0')
 	{
 		int64		multiplier = 0;
+		uint32		i;
 
 		/* Trim any trailing whitespace */
 		endptr = str + VARSIZE_ANY_EXHDR(arg) - 1;
@@ -797,21 +793,18 @@ pg_size_bytes(PG_FUNCTION_ARGS)
 		endptr++;
 		*endptr = '\0';
 
-		/* Parse the unit case-insensitively */
-		if (pg_strcasecmp(strptr, "bytes") == 0)
-			multiplier = (int64) 1;
-		else if (pg_strcasecmp(strptr, "kb") == 0)
-			multiplier = (int64) 1024;
-		else if (pg_strcasecmp(strptr, "mb") == 0)
-			multiplier = ((int64) 1024) * 1024;
-
-		else if (pg_strcasecmp(strptr, "gb") == 0)
-			multiplier = ((int64) 1024) * 1024 * 1024;
-
-		else if (pg_strcasecmp(strptr, "tb") == 0)
-			multiplier = ((int64) 1024) * 1024 * 1024 * 1024;
-
-		else
+		for (i = 0; size_pretty_units[i].unit != NULL; i++)
+		{
+			/* Parse the unit case-insensitively */
+			if (pg_strcasecmp(strptr, size_pretty_units[i].unit) == 0)
+			{
+				multiplier = ((int64) 1) << size_pretty_units[i].multishift;
+				break;
+			}
+		}
+		
+		/* Verify we found a valid unit in the loop above */
+		if (size_pretty_units[i].unit == NULL)
 			ereport(ERROR,
 					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 					 errmsg("invalid size: \"%s\"", text_to_cstring(arg)),

From 8b387db81b93349968be3ae9d6cd7cd77d05d2e7 Mon Sep 17 00:00:00 2001
From: David Rowley <dgrowley@gmail.com>
Date: Mon, 5 Jul 2021 20:03:27 +1200
Subject: [PATCH v1] Use lookup table for units in pg_size_pretty and
 pg_size_bytes

We've grown 2 versions of pg_size_pretty over the years, one for BIGINT
and one for NUMERIC.  Both should output the same, but keeping them in
sync would be harder than needed due to neither function sharing a source
of truth about which units to use.

Here we add a static array that defines the units that we recognize and
have both pg_size_pretty and pg_size_pretty_numeric use it.  This will
make adding any units in the future a very simple task.

The table contains all information required to allow us to also modify
pg_size_bytes to also use it there too, so adjust that too.

There are no behavioral changes here.
---
 src/backend/utils/adt/dbsize.c | 153 +++++++++++++++------------------
 1 file changed, 68 insertions(+), 85 deletions(-)

diff --git a/src/backend/utils/adt/dbsize.c b/src/backend/utils/adt/dbsize.c
index 9c39e7d3b3..4b7331d85c 100644
--- a/src/backend/utils/adt/dbsize.c
+++ b/src/backend/utils/adt/dbsize.c
@@ -34,6 +34,29 @@
 /* Divide by two and round towards positive infinity. */
 #define half_rounded(x)   (((x) + ((x) < 0 ? 0 : 1)) / 2)
 
+/* Units used in pg_size_pretty functions.  All units must be powers of 2 */
+struct size_pretty_unit
+{
+	char	   *text;			/* bytes, kB, MB, GB etc */
+	uint32		limit;			/* upper limit after reducing by shiftRight
+								 * bits from previous unit */
+	uint8		shiftRight;		/* number of bits to shift right by after
+								 * limit is exceeded before considering the
+								 * next unit. Remaining shifts will be half
+								 * rounded until we get to unitBits */
+	uint8		unitBits;		/* 1 << unitBits make 1 of this unit */
+};
+
+/* When adding units here also update the error message in pg_size_bytes */
+static const struct size_pretty_unit size_pretty_units[] = {
+	{"bytes", 10 * 1024, 9, 0}, /* keep one extra bit for rounding */
+	{"kB", 20 * 1024 - 1, 10, 10},
+	{"MB", 20 * 1024 - 1, 10, 20},
+	{"GB", 20 * 1024 - 1, 10, 30},
+	{"TB", 20 * 1024 - 1, 10, 40},
+	{NULL, 0, 0, 0}
+};
+
 /* Return physical size of directory contents, or 0 if dir doesn't exist */
 static int64
 db_dir_size(const char *path)
@@ -535,37 +558,25 @@ pg_size_pretty(PG_FUNCTION_ARGS)
 {
 	int64		size = PG_GETARG_INT64(0);
 	char		buf[64];
-	int64		limit = 10 * 1024;
-	int64		limit2 = limit * 2 - 1;
+	uint8		rightshifts = 0;
+	const struct size_pretty_unit *unit;
 
-	if (Abs(size) < limit)
-		snprintf(buf, sizeof(buf), INT64_FORMAT " bytes", size);
-	else
+	for (unit = size_pretty_units; unit->text !=NULL; unit++)
 	{
-		size >>= 9;				/* keep one extra bit for rounding */
-		if (Abs(size) < limit2)
-			snprintf(buf, sizeof(buf), INT64_FORMAT " kB",
-					 half_rounded(size));
-		else
+		/* use this unit if below limit or there's no more units */
+		if (Abs(size) < unit->limit || unit[1].text == NULL)
 		{
-			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));
-				}
-			}
+			/* half-round until we get down to unitBits */
+			while (rightshifts++ < unit->unitBits)
+				size = half_rounded(size);
+
+			snprintf(buf, sizeof(buf), INT64_FORMAT " %s", size, unit->text);
+			break;
 		}
+
+		/* size too big for this unit, shift right and try the next unit */
+		size >>= unit->shiftRight;
+		rightshifts += unit->shiftRight;
 	}
 
 	PG_RETURN_TEXT_P(cstring_to_text(buf));
@@ -636,56 +647,30 @@ 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);
+	Numeric		limit;
+	char	   *result = NULL;
+	uint8		rightshifts = 0;
+	const struct size_pretty_unit *unit;
 
-	if (numeric_is_less(numeric_absolute(size), limit))
-	{
-		result = psprintf("%s bytes", numeric_to_cstring(size));
-	}
-	else
+	for (unit = size_pretty_units; unit->text !=NULL; unit++)
 	{
-		/* keep one extra bit for rounding */
-		/* size >>= 9 */
-		size = numeric_shift_right(size, 9);
+		limit = int64_to_numeric(unit->limit);
 
-		if (numeric_is_less(numeric_absolute(size), limit2))
-		{
-			size = numeric_half_rounded(size);
-			result = psprintf("%s kB", numeric_to_cstring(size));
-		}
-		else
+		/* use this unit if below limit or there's no more units */
+		if (numeric_is_less(numeric_absolute(size), limit) ||
+			unit[1].text == NULL)
 		{
-			/* size >>= 10 */
-			size = numeric_shift_right(size, 10);
-			if (numeric_is_less(numeric_absolute(size), limit2))
-			{
+			/* half-round until we get down to unitBits */
+			while (rightshifts++ < unit->unitBits)
 				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));
-				}
-			}
+
+			result = psprintf("%s %s", numeric_to_cstring(size), unit->text);
+			break;
 		}
+
+		/* size too big for this unit, shift right and try the next unit */
+		size = numeric_shift_right(size, unit->shiftRight);
+		rightshifts += unit->shiftRight;
 	}
 
 	PG_RETURN_TEXT_P(cstring_to_text(result));
@@ -786,6 +771,7 @@ pg_size_bytes(PG_FUNCTION_ARGS)
 	/* Handle possible unit */
 	if (*strptr != '\0')
 	{
+		const struct size_pretty_unit *unit;
 		int64		multiplier = 0;
 
 		/* Trim any trailing whitespace */
@@ -797,21 +783,18 @@ pg_size_bytes(PG_FUNCTION_ARGS)
 		endptr++;
 		*endptr = '\0';
 
-		/* Parse the unit case-insensitively */
-		if (pg_strcasecmp(strptr, "bytes") == 0)
-			multiplier = (int64) 1;
-		else if (pg_strcasecmp(strptr, "kb") == 0)
-			multiplier = (int64) 1024;
-		else if (pg_strcasecmp(strptr, "mb") == 0)
-			multiplier = ((int64) 1024) * 1024;
-
-		else if (pg_strcasecmp(strptr, "gb") == 0)
-			multiplier = ((int64) 1024) * 1024 * 1024;
-
-		else if (pg_strcasecmp(strptr, "tb") == 0)
-			multiplier = ((int64) 1024) * 1024 * 1024 * 1024;
+		for (unit = size_pretty_units; unit->text !=NULL; unit++)
+		{
+			/* Parse the unit case-insensitively */
+			if (pg_strcasecmp(strptr, unit->text) == 0)
+			{
+				multiplier = ((int64) 1) << unit->unitBits;
+				break;
+			}
+		}
 
-		else
+		/* Verify we found a valid unit in the loop above */
+		if (unit->text == NULL)
 			ereport(ERROR,
 					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 					 errmsg("invalid size: \"%s\"", text_to_cstring(arg)),
-- 
2.30.2

From ec3a5513066c0d37ccda6d4ebc122ff1a4d45148 Mon Sep 17 00:00:00 2001
From: David Rowley <dgrowley@gmail.com>
Date: Mon, 5 Jul 2021 20:03:27 +1200
Subject: [PATCH v2] Use lookup table for units in pg_size_pretty and
 pg_size_bytes

We've grown 2 versions of pg_size_pretty over the years, one for BIGINT
and one for NUMERIC.  Both should output the same, but keeping them in
sync would be harder than needed due to neither function sharing a source
of truth about which units to use.

Here we add a static array that defines the units that we recognize and
have both pg_size_pretty and pg_size_pretty_numeric use it.  This will
make adding any units in the future a very simple task.

The table contains all information required to allow us to also modify
pg_size_bytes to also use it there too, so adjust that too.

There are no behavioral changes here.
---
 src/backend/utils/adt/dbsize.c | 158 +++++++++++++++------------------
 1 file changed, 73 insertions(+), 85 deletions(-)

diff --git a/src/backend/utils/adt/dbsize.c b/src/backend/utils/adt/dbsize.c
index 9c39e7d3b3..9a15625e89 100644
--- a/src/backend/utils/adt/dbsize.c
+++ b/src/backend/utils/adt/dbsize.c
@@ -34,6 +34,27 @@
 /* Divide by two and round towards positive infinity. */
 #define half_rounded(x)   (((x) + ((x) < 0 ? 0 : 1)) / 2)
 
+/* Units used in pg_size_pretty functions.  All units must be powers of 2 */
+struct size_pretty_unit
+{
+	const char *name;			/* bytes, kB, MB, GB etc */
+	uint32		limit;			/* upper limit, prior to half rounding after
+								 * converting to this unit. */
+	bool		round;			/* do half rounding for this unit */
+	uint8		unitbits;		/* (1 << unitbits) bytes to make 1 of this
+								 * unit */
+};
+
+/* When adding units here also update the error message in pg_size_bytes */
+static const struct size_pretty_unit size_pretty_units[] = {
+	{"bytes", 10 * 1024, false, 0},
+	{"kB", 20 * 1024 - 1, true, 10},
+	{"MB", 20 * 1024 - 1, true, 20},
+	{"GB", 20 * 1024 - 1, true, 30},
+	{"TB", 20 * 1024 - 1, true, 40},
+	{NULL, 0, false, 0}
+};
+
 /* Return physical size of directory contents, or 0 if dir doesn't exist */
 static int64
 db_dir_size(const char *path)
@@ -535,37 +556,28 @@ pg_size_pretty(PG_FUNCTION_ARGS)
 {
 	int64		size = PG_GETARG_INT64(0);
 	char		buf[64];
-	int64		limit = 10 * 1024;
-	int64		limit2 = limit * 2 - 1;
+	const struct size_pretty_unit *unit;
 
-	if (Abs(size) < limit)
-		snprintf(buf, sizeof(buf), INT64_FORMAT " bytes", size);
-	else
+	for (unit = size_pretty_units; unit->name != NULL; unit++)
 	{
-		size >>= 9;				/* keep one extra bit for rounding */
-		if (Abs(size) < limit2)
-			snprintf(buf, sizeof(buf), INT64_FORMAT " kB",
-					 half_rounded(size));
-		else
+		/* use this unit there are no more units or we're below the limit */
+		if (unit[1].name == NULL || Abs(size) < unit->limit)
 		{
-			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));
-				}
-			}
+			if (unit->round)
+				size = half_rounded(size);
+
+			snprintf(buf, sizeof(buf), INT64_FORMAT " %s", size, unit->name);
+			break;
 		}
+
+		/*
+		 * We may need to shift 1 bit less than the difference between this
+		 * and the next unit if the next unit has half rounding.  Or we may
+		 * need to shift an extra bit if this unit has half rounding and the
+		 * next one does not.
+		 */
+		size >>= (unit[1].unitbits - unit->unitbits - (unit[1].round == true)
+				  + (unit->round == true));
 	}
 
 	PG_RETURN_TEXT_P(cstring_to_text(buf));
@@ -636,56 +648,34 @@ 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);
+	char	   *result = NULL;
+	const struct size_pretty_unit *unit;
 
-	if (numeric_is_less(numeric_absolute(size), limit))
+	for (unit = size_pretty_units; unit->name != NULL; unit++)
 	{
-		result = psprintf("%s bytes", numeric_to_cstring(size));
-	}
-	else
-	{
-		/* keep one extra bit for rounding */
-		/* size >>= 9 */
-		size = numeric_shift_right(size, 9);
+		unsigned int shiftby;
 
-		if (numeric_is_less(numeric_absolute(size), limit2))
-		{
-			size = numeric_half_rounded(size);
-			result = psprintf("%s kB", numeric_to_cstring(size));
-		}
-		else
+		/* use this unit there are no more units or we're below the limit */
+		if (unit[1].name == NULL ||
+			numeric_is_less(numeric_absolute(size),
+							int64_to_numeric(unit->limit)))
 		{
-			/* size >>= 10 */
-			size = numeric_shift_right(size, 10);
-			if (numeric_is_less(numeric_absolute(size), limit2))
-			{
+			if (unit->round)
 				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));
-				}
-			}
+
+			result = psprintf("%s %s", numeric_to_cstring(size), unit->name);
+			break;
 		}
+
+		/*
+		 * We may need to shift 1 bit less than the difference between this
+		 * and the next unit if the next unit has half rounding.  Or we may
+		 * need to shift an extra bit if this unit has half rounding and the
+		 * next one does not.
+		 */
+		shiftby = (unit[1].unitbits - unit->unitbits - (unit[1].round == true)
+				   + (unit->round == true));
+		size = numeric_shift_right(size, shiftby);
 	}
 
 	PG_RETURN_TEXT_P(cstring_to_text(result));
@@ -786,6 +776,7 @@ pg_size_bytes(PG_FUNCTION_ARGS)
 	/* Handle possible unit */
 	if (*strptr != '\0')
 	{
+		const struct size_pretty_unit *unit;
 		int64		multiplier = 0;
 
 		/* Trim any trailing whitespace */
@@ -797,21 +788,18 @@ pg_size_bytes(PG_FUNCTION_ARGS)
 		endptr++;
 		*endptr = '\0';
 
-		/* Parse the unit case-insensitively */
-		if (pg_strcasecmp(strptr, "bytes") == 0)
-			multiplier = (int64) 1;
-		else if (pg_strcasecmp(strptr, "kb") == 0)
-			multiplier = (int64) 1024;
-		else if (pg_strcasecmp(strptr, "mb") == 0)
-			multiplier = ((int64) 1024) * 1024;
-
-		else if (pg_strcasecmp(strptr, "gb") == 0)
-			multiplier = ((int64) 1024) * 1024 * 1024;
-
-		else if (pg_strcasecmp(strptr, "tb") == 0)
-			multiplier = ((int64) 1024) * 1024 * 1024 * 1024;
+		for (unit = size_pretty_units; unit->name != NULL; unit++)
+		{
+			/* Parse the unit case-insensitively */
+			if (pg_strcasecmp(strptr, unit->name) == 0)
+			{
+				multiplier = ((int64) 1) << unit->unitbits;
+				break;
+			}
+		}
 
-		else
+		/* Verify we found a valid unit in the loop above */
+		if (unit->name == NULL)
 			ereport(ERROR,
 					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 					 errmsg("invalid size: \"%s\"", text_to_cstring(arg)),
-- 
2.30.2

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)

diff --git a/src/backend/utils/adt/dbsize.c b/src/backend/utils/adt/dbsize.c
index 9c39e7d3b3..daf9149be3 100644
--- a/src/backend/utils/adt/dbsize.c
+++ b/src/backend/utils/adt/dbsize.c
@@ -31,8 +31,8 @@
 #include "utils/relmapper.h"
 #include "utils/syscache.h"
 
-/* Divide by two and round towards positive infinity. */
-#define half_rounded(x)   (((x) + ((x) < 0 ? 0 : 1)) / 2)
+/* Divide by two and round away from zero */
+#define half_rounded(x)   (((x) + ((x) < 0 ? -1 : 1)) / 2)
 
 /* Return physical size of directory contents, or 0 if dir doesn't exist */
 static int64
@@ -542,25 +542,29 @@ pg_size_pretty(PG_FUNCTION_ARGS)
 		snprintf(buf, sizeof(buf), INT64_FORMAT " bytes", size);
 	else
 	{
-		size >>= 9;				/* keep one extra bit for rounding */
+		/*
+		 * We use divide instead of bit shifting so that behavior matches for
+		 * both positive and negative size values.
+		 */
+		size /= (1 << 9);				/* keep one extra bit for rounding */
 		if (Abs(size) < limit2)
 			snprintf(buf, sizeof(buf), INT64_FORMAT " kB",
 					 half_rounded(size));
 		else
 		{
-			size >>= 10;
+			size /= (1 << 10);
 			if (Abs(size) < limit2)
 				snprintf(buf, sizeof(buf), INT64_FORMAT " MB",
 						 half_rounded(size));
 			else
 			{
-				size >>= 10;
+				size /= (1 << 10);
 				if (Abs(size) < limit2)
 					snprintf(buf, sizeof(buf), INT64_FORMAT " GB",
 							 half_rounded(size));
 				else
 				{
-					size >>= 10;
+					size /= (1 << 10);
 					snprintf(buf, sizeof(buf), INT64_FORMAT " TB",
 							 half_rounded(size));
 				}

diff --git a/src/backend/utils/adt/dbsize.c b/src/backend/utils/adt/dbsize.c
new file mode 100644
index 9c39e7d..d03e103
--- a/src/backend/utils/adt/dbsize.c
+++ b/src/backend/utils/adt/dbsize.c
@@ -31,8 +31,8 @@
 #include "utils/relmapper.h"
 #include "utils/syscache.h"
 
-/* Divide by two and round towards positive infinity. */
-#define half_rounded(x)   (((x) + ((x) < 0 ? 0 : 1)) / 2)
+/* Divide by two and round away from zero */
+#define half_rounded(x)   (((x) + ((x) < 0 ? -1 : 1)) / 2)
 
 /* Return physical size of directory contents, or 0 if dir doesn't exist */
 static int64
@@ -542,25 +542,29 @@ pg_size_pretty(PG_FUNCTION_ARGS)
 		snprintf(buf, sizeof(buf), INT64_FORMAT " bytes", size);
 	else
 	{
-		size >>= 9;				/* keep one extra bit for rounding */
+		/*
+		 * We use divide instead of bit shifting so that behavior matches for
+		 * both positive and negative size values.
+		 */
+		size /= (1 << 9);				/* keep one extra bit for rounding */
 		if (Abs(size) < limit2)
 			snprintf(buf, sizeof(buf), INT64_FORMAT " kB",
 					 half_rounded(size));
 		else
 		{
-			size >>= 10;
+			size /= (1 << 10);
 			if (Abs(size) < limit2)
 				snprintf(buf, sizeof(buf), INT64_FORMAT " MB",
 						 half_rounded(size));
 			else
 			{
-				size >>= 10;
+				size /= (1 << 10);
 				if (Abs(size) < limit2)
 					snprintf(buf, sizeof(buf), INT64_FORMAT " GB",
 							 half_rounded(size));
 				else
 				{
-					size >>= 10;
+					size /= (1 << 10);
 					snprintf(buf, sizeof(buf), INT64_FORMAT " TB",
 							 half_rounded(size));
 				}
@@ -621,13 +625,13 @@ numeric_half_rounded(Numeric n)
 }
 
 static Numeric
-numeric_shift_right(Numeric n, unsigned count)
+numeric_truncated_divide(Numeric n, int64 divisor)
 {
 	Datum		d = NumericGetDatum(n);
 	Datum		divisor_numeric;
 	Datum		result;
 
-	divisor_numeric = NumericGetDatum(int64_to_numeric(((int64) 1) << count));
+	divisor_numeric = NumericGetDatum(int64_to_numeric(divisor));
 	result = DirectFunctionCall2(numeric_div_trunc, d, divisor_numeric);
 	return DatumGetNumeric(result);
 }
@@ -650,8 +654,8 @@ pg_size_pretty_numeric(PG_FUNCTION_ARGS)
 	else
 	{
 		/* keep one extra bit for rounding */
-		/* size >>= 9 */
-		size = numeric_shift_right(size, 9);
+		/* size /= (1 << 9) */
+		size = numeric_truncated_divide(size, 1 << 9);
 
 		if (numeric_is_less(numeric_absolute(size), limit2))
 		{
@@ -660,8 +664,9 @@ pg_size_pretty_numeric(PG_FUNCTION_ARGS)
 		}
 		else
 		{
-			/* size >>= 10 */
-			size = numeric_shift_right(size, 10);
+			/* size /= (1 << 10) */
+			size = numeric_truncated_divide(size, 1 << 10);
+
 			if (numeric_is_less(numeric_absolute(size), limit2))
 			{
 				size = numeric_half_rounded(size);
@@ -669,8 +674,8 @@ pg_size_pretty_numeric(PG_FUNCTION_ARGS)
 			}
 			else
 			{
-				/* size >>= 10 */
-				size = numeric_shift_right(size, 10);
+				/* size /= (1 << 10) */
+				size = numeric_truncated_divide(size, 1 << 10);
 
 				if (numeric_is_less(numeric_absolute(size), limit2))
 				{
@@ -679,8 +684,8 @@ pg_size_pretty_numeric(PG_FUNCTION_ARGS)
 				}
 				else
 				{
-					/* size >>= 10 */
-					size = numeric_shift_right(size, 10);
+					/* size /= (1 << 10) */
+					size = numeric_truncated_divide(size, 1 << 10);
 					size = numeric_half_rounded(size);
 					result = psprintf("%s TB", numeric_to_cstring(size));
 				}
diff --git a/src/test/regress/expected/dbsize.out b/src/test/regress/expected/dbsize.out
new file mode 100644
index e901a2c..29804ae
--- a/src/test/regress/expected/dbsize.out
+++ b/src/test/regress/expected/dbsize.out
@@ -35,6 +35,48 @@ SELECT size, pg_size_pretty(size), pg_si
  1000000000000000.5 | 909 TB         | -909 TB
 (12 rows)
 
+-- test where units change up
+SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM
+    (VALUES (10239::bigint), (10240::bigint),
+            (10485247::bigint), (10485248::bigint),
+            (10736893951::bigint), (10736893952::bigint),
+            (10994579406847::bigint), (10994579406848::bigint),
+            (11258449312612351::bigint), (11258449312612352::bigint)) x(size);
+       size        | pg_size_pretty | pg_size_pretty 
+-------------------+----------------+----------------
+             10239 | 10239 bytes    | -10239 bytes
+             10240 | 10 kB          | -10 kB
+          10485247 | 10239 kB       | -10239 kB
+          10485248 | 10 MB          | -10 MB
+       10736893951 | 10239 MB       | -10239 MB
+       10736893952 | 10 GB          | -10 GB
+    10994579406847 | 10239 GB       | -10239 GB
+    10994579406848 | 10 TB          | -10 TB
+ 11258449312612351 | 10239 TB       | -10239 TB
+ 11258449312612352 | 10240 TB       | -10240 TB
+(10 rows)
+
+SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM
+    (VALUES (10239::numeric), (10240::numeric),
+            (10485247::numeric), (10485248::numeric),
+            (10736893951::numeric), (10736893952::numeric),
+            (10994579406847::numeric), (10994579406848::numeric),
+            (11258449312612351::numeric), (11258449312612352::numeric)) x(size);
+       size        | pg_size_pretty | pg_size_pretty 
+-------------------+----------------+----------------
+             10239 | 10239 bytes    | -10239 bytes
+             10240 | 10 kB          | -10 kB
+          10485247 | 10239 kB       | -10239 kB
+          10485248 | 10 MB          | -10 MB
+       10736893951 | 10239 MB       | -10239 MB
+       10736893952 | 10 GB          | -10 GB
+    10994579406847 | 10239 GB       | -10239 GB
+    10994579406848 | 10 TB          | -10 TB
+ 11258449312612351 | 10239 TB       | -10239 TB
+ 11258449312612352 | 10240 TB       | -10240 TB
+(10 rows)
+
+-- pg_size_bytes() tests
 SELECT size, pg_size_bytes(size) FROM
     (VALUES ('1'), ('123bytes'), ('1kB'), ('1MB'), (' 1 GB'), ('1.5 GB '),
             ('1TB'), ('3000 TB'), ('1e6 MB')) x(size);
diff --git a/src/test/regress/sql/dbsize.sql b/src/test/regress/sql/dbsize.sql
new file mode 100644
index d10a4d7..6a45c5e
--- a/src/test/regress/sql/dbsize.sql
+++ b/src/test/regress/sql/dbsize.sql
@@ -11,6 +11,22 @@ SELECT size, pg_size_pretty(size), pg_si
             (1000000000.5::numeric), (1000000000000.5::numeric),
             (1000000000000000.5::numeric)) x(size);
 
+-- test where units change up
+SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM
+    (VALUES (10239::bigint), (10240::bigint),
+            (10485247::bigint), (10485248::bigint),
+            (10736893951::bigint), (10736893952::bigint),
+            (10994579406847::bigint), (10994579406848::bigint),
+            (11258449312612351::bigint), (11258449312612352::bigint)) x(size);
+
+SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM
+    (VALUES (10239::numeric), (10240::numeric),
+            (10485247::numeric), (10485248::numeric),
+            (10736893951::numeric), (10736893952::numeric),
+            (10994579406847::numeric), (10994579406848::numeric),
+            (11258449312612351::numeric), (11258449312612352::numeric)) x(size);
+
+-- pg_size_bytes() tests
 SELECT size, pg_size_bytes(size) FROM
     (VALUES ('1'), ('123bytes'), ('1kB'), ('1MB'), (' 1 GB'), ('1.5 GB '),
             ('1TB'), ('3000 TB'), ('1e6 MB')) x(size);