Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit53874c5

Browse files
committed
Add pg_size_bytes() to parse human-readable size strings.
This will parse strings in the format produced by pg_size_pretty() andreturn sizes in bytes. This allows queries to be written with clauseslike "pg_total_relation_size(oid) > pg_size_bytes('10 GB')".Author: Pavel Stehule with various improvements by Vitaly BurovoyDiscussion:http://www.postgresql.org/message-id/CAFj8pRD-tGoDKnxdYgECzA4On01_uRqPrwF-8LdkSE-6bDHp0w@mail.gmail.comReviewed-by: Vitaly Burovoy, Oleksandr Shulgin, Kyotaro Horiguchi, Michael Paquier and Robert Haas
1 parent091b605 commit53874c5

File tree

7 files changed

+331
-3
lines changed

7 files changed

+331
-3
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 30 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -17766,6 +17766,9 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
1776617766
<indexterm>
1776717767
<primary>pg_relation_size</primary>
1776817768
</indexterm>
17769+
<indexterm>
17770+
<primary>pg_size_bytes</primary>
17771+
</indexterm>
1776917772
<indexterm>
1777017773
<primary>pg_size_pretty</primary>
1777117774
</indexterm>
@@ -17836,6 +17839,15 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
1783617839
Shorthand for <literal>pg_relation_size(..., 'main')</literal>
1783717840
</entry>
1783817841
</row>
17842+
<row>
17843+
<entry>
17844+
<literal><function>pg_size_bytes(<type>text</type>)</function></literal>
17845+
</entry>
17846+
<entry><type>bigint</type></entry>
17847+
<entry>
17848+
Converts a size in human-readable format with size units into bytes
17849+
</entry>
17850+
</row>
1783917851
<row>
1784017852
<entry>
1784117853
<literal><function>pg_size_pretty(<type>bigint</type>)</function></literal>
@@ -17968,10 +17980,26 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
1796817980

1796917981
<para>
1797017982
<function>pg_size_pretty</> can be used to format the result of one of
17971-
the other functions in a human-readable way, using kB, MB, GB or TB as
17972-
appropriate.
17983+
the other functions in a human-readable way, usingbytes,kB, MB, GB or TB
17984+
asappropriate.
1797317985
</para>
1797417986

17987+
<para>
17988+
<function>pg_size_bytes</> can be used to get the size in bytes from a
17989+
string in human-readable format. The input may have units of bytes, kB,
17990+
MB, GB or TB, and is parsed case-insensitively. If no units are specified,
17991+
bytes are assumed.
17992+
</para>
17993+
17994+
<note>
17995+
<para>
17996+
The units kB, MB, GB and TB used by the functions
17997+
<function>pg_size_pretty</> and <function>pg_size_bytes</> are defined
17998+
using powers of 2 rather than powers of 10, so 1kB is 1024 bytes, 1MB is
17999+
1024<superscript>2</> = 1048576 bytes, and so on.
18000+
</para>
18001+
</note>
18002+
1797518003
<para>
1797618004
The functions above that operate on tables or indexes accept a
1797718005
<type>regclass</> argument, which is simply the OID of the table or index

‎src/backend/utils/adt/dbsize.c

Lines changed: 149 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -699,6 +699,155 @@ pg_size_pretty_numeric(PG_FUNCTION_ARGS)
699699
PG_RETURN_TEXT_P(cstring_to_text(result));
700700
}
701701

702+
/*
703+
* Convert a human-readable size to a size in bytes
704+
*/
705+
Datum
706+
pg_size_bytes(PG_FUNCTION_ARGS)
707+
{
708+
text*arg=PG_GETARG_TEXT_PP(0);
709+
char*str,
710+
*strptr,
711+
*endptr;
712+
charsaved_char;
713+
Numericnum;
714+
int64result;
715+
boolhave_digits= false;
716+
717+
str=text_to_cstring(arg);
718+
719+
/* Skip leading whitespace */
720+
strptr=str;
721+
while (isspace((unsignedchar)*strptr))
722+
strptr++;
723+
724+
/* Check that we have a valid number and determine where it ends */
725+
endptr=strptr;
726+
727+
/* Part (1): sign */
728+
if (*endptr=='-'||*endptr=='+')
729+
endptr++;
730+
731+
/* Part (2): main digit string */
732+
if (isdigit((unsignedchar)*endptr))
733+
{
734+
have_digits= true;
735+
do
736+
endptr++;
737+
while (isdigit((unsignedchar)*endptr));
738+
}
739+
740+
/* Part (3): optional decimal point and fractional digits */
741+
if (*endptr=='.')
742+
{
743+
endptr++;
744+
if (isdigit((unsignedchar)*endptr))
745+
{
746+
have_digits= true;
747+
do
748+
endptr++;
749+
while (isdigit((unsignedchar)*endptr));
750+
}
751+
}
752+
753+
/* Complain if we don't have a valid number at this point */
754+
if (!have_digits)
755+
ereport(ERROR,
756+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
757+
errmsg("invalid size: \"%s\"",str)));
758+
759+
/* Part (4): optional exponent */
760+
if (*endptr=='e'||*endptr=='E')
761+
{
762+
longexponent;
763+
char*cp;
764+
765+
/*
766+
* Note we might one day support EB units, so if what follows isn't a
767+
* number, just treat it all as a unit to be parsed.
768+
*/
769+
exponent=strtol(endptr+1,&cp,10);
770+
if (cp>endptr+1)
771+
{
772+
if (exponent>NUMERIC_MAX_PRECISION||
773+
exponent<-NUMERIC_MAX_PRECISION)
774+
ereport(ERROR,
775+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
776+
errmsg("invalid size: \"%s\"",str)));
777+
endptr=cp;
778+
}
779+
}
780+
781+
/*
782+
* Parse the number, saving the next character, which may be the first
783+
* character of the unit string.
784+
*/
785+
saved_char=*endptr;
786+
*endptr='\0';
787+
788+
num=DatumGetNumeric(DirectFunctionCall3(numeric_in,
789+
CStringGetDatum(strptr),
790+
ObjectIdGetDatum(InvalidOid),
791+
Int32GetDatum(-1)));
792+
793+
*endptr=saved_char;
794+
795+
/* Skip whitespace between number and unit */
796+
strptr=endptr;
797+
while (isspace((unsignedchar)*strptr))
798+
strptr++;
799+
800+
/* Handle possible unit */
801+
if (*strptr!='\0')
802+
{
803+
int64multiplier=0;
804+
805+
/* Trim any trailing whitespace */
806+
endptr=str+VARSIZE_ANY_EXHDR(arg)-1;
807+
808+
while (isspace((unsignedchar)*endptr))
809+
endptr--;
810+
811+
endptr++;
812+
*endptr='\0';
813+
814+
/* Parse the unit case-insensitively */
815+
if (pg_strcasecmp(strptr,"bytes")==0)
816+
multiplier=1;
817+
elseif (pg_strcasecmp(strptr,"kb")==0)
818+
multiplier=1024;
819+
elseif (pg_strcasecmp(strptr,"mb")==0)
820+
multiplier=1024*1024;
821+
elseif (pg_strcasecmp(strptr,"gb")==0)
822+
multiplier=1024*1024*1024;
823+
elseif (pg_strcasecmp(strptr,"tb")==0)
824+
multiplier=1024*1024*1024*1024L;
825+
else
826+
ereport(ERROR,
827+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
828+
errmsg("invalid size: \"%s\"",text_to_cstring(arg)),
829+
errdetail("Invalid size unit: \"%s\".",strptr),
830+
errhint("Valid units are \"bytes\", \"kB\", \"MB\", \"GB\", and \"TB\".")));
831+
832+
if (multiplier>1)
833+
{
834+
Numericmul_num;
835+
836+
mul_num=DatumGetNumeric(DirectFunctionCall1(int8_numeric,
837+
Int64GetDatum(multiplier)));
838+
839+
num=DatumGetNumeric(DirectFunctionCall2(numeric_mul,
840+
NumericGetDatum(mul_num),
841+
NumericGetDatum(num)));
842+
}
843+
}
844+
845+
result=DatumGetInt64(DirectFunctionCall1(numeric_int8,
846+
NumericGetDatum(num)));
847+
848+
PG_RETURN_INT64(result);
849+
}
850+
702851
/*
703852
* Get the filenode of a relation
704853
*

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO201602171
56+
#defineCATALOG_VERSION_NO201602201
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3601,6 +3601,8 @@ DATA(insert OID = 2288 ( pg_size_prettyPGNSP PGUID 12 1 0 0 0 f f f f t f i s
36013601
DESCR("convert a long int to a human readable text using size units");
36023602
DATA(insert OID = 3166 ( pg_size_prettyPGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 25 "1700" _null_ _null_ _null_ _null_ _null_ pg_size_pretty_numeric _null_ _null_ _null_ ));
36033603
DESCR("convert a numeric to a human readable text using size units");
3604+
DATA(insert OID = 3334 ( pg_size_bytesPGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 20 "25" _null_ _null_ _null_ _null_ _null_ pg_size_bytes _null_ _null_ _null_ ));
3605+
DESCR("convert a size in human-readable format with size units into bytes");
36043606
DATA(insert OID = 2997 ( pg_table_sizePGNSP PGUID 12 1 0 0 0 f f f f t f v s 1 0 20 "2205" _null_ _null_ _null_ _null_ _null_ pg_table_size _null_ _null_ _null_ ));
36053607
DESCR("disk space usage for the specified table, including TOAST, free space and visibility map");
36063608
DATA(insert OID = 2998 ( pg_indexes_sizePGNSP PGUID 12 1 0 0 0 f f f f t f v s 1 0 20 "2205" _null_ _null_ _null_ _null_ _null_ pg_indexes_size _null_ _null_ _null_ ));

‎src/include/utils/builtins.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -473,6 +473,7 @@ extern Datum pg_relation_size(PG_FUNCTION_ARGS);
473473
externDatumpg_total_relation_size(PG_FUNCTION_ARGS);
474474
externDatumpg_size_pretty(PG_FUNCTION_ARGS);
475475
externDatumpg_size_pretty_numeric(PG_FUNCTION_ARGS);
476+
externDatumpg_size_bytes(PG_FUNCTION_ARGS);
476477
externDatumpg_table_size(PG_FUNCTION_ARGS);
477478
externDatumpg_indexes_size(PG_FUNCTION_ARGS);
478479
externDatumpg_relation_filenode(PG_FUNCTION_ARGS);

‎src/test/regress/expected/dbsize.out

Lines changed: 109 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -35,3 +35,112 @@ SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM
3535
1000000000000000.5 | 909 TB | -909 TB
3636
(12 rows)
3737

38+
SELECT size, pg_size_bytes(size) FROM
39+
(VALUES ('1'), ('123bytes'), ('1kB'), ('1MB'), (' 1 GB'), ('1.5 GB '),
40+
('1TB'), ('3000 TB'), ('1e6 MB')) x(size);
41+
size | pg_size_bytes
42+
----------+------------------
43+
1 | 1
44+
123bytes | 123
45+
1kB | 1024
46+
1MB | 1048576
47+
1 GB | 1073741824
48+
1.5 GB | 1610612736
49+
1TB | 1099511627776
50+
3000 TB | 3298534883328000
51+
1e6 MB | 1048576000000
52+
(9 rows)
53+
54+
-- case-insensitive units are supported
55+
SELECT size, pg_size_bytes(size) FROM
56+
(VALUES ('1'), ('123bYteS'), ('1kb'), ('1mb'), (' 1 Gb'), ('1.5 gB '),
57+
('1tb'), ('3000 tb'), ('1e6 mb')) x(size);
58+
size | pg_size_bytes
59+
----------+------------------
60+
1 | 1
61+
123bYteS | 123
62+
1kb | 1024
63+
1mb | 1048576
64+
1 Gb | 1073741824
65+
1.5 gB | 1610612736
66+
1tb | 1099511627776
67+
3000 tb | 3298534883328000
68+
1e6 mb | 1048576000000
69+
(9 rows)
70+
71+
-- negative numbers are supported
72+
SELECT size, pg_size_bytes(size) FROM
73+
(VALUES ('-1'), ('-123bytes'), ('-1kb'), ('-1mb'), (' -1 Gb'), ('-1.5 gB '),
74+
('-1tb'), ('-3000 TB'), ('-10e-1 MB')) x(size);
75+
size | pg_size_bytes
76+
-----------+-------------------
77+
-1 | -1
78+
-123bytes | -123
79+
-1kb | -1024
80+
-1mb | -1048576
81+
-1 Gb | -1073741824
82+
-1.5 gB | -1610612736
83+
-1tb | -1099511627776
84+
-3000 TB | -3298534883328000
85+
-10e-1 MB | -1048576
86+
(9 rows)
87+
88+
-- different cases with allowed points
89+
SELECT size, pg_size_bytes(size) FROM
90+
(VALUES ('-1.'), ('-1.kb'), ('-1. kb'), ('-0. gb'),
91+
('-.1'), ('-.1kb'), ('-.1 kb'), ('-.0 gb')) x(size);
92+
size | pg_size_bytes
93+
--------+---------------
94+
-1. | -1
95+
-1.kb | -1024
96+
-1. kb | -1024
97+
-0. gb | 0
98+
-.1 | 0
99+
-.1kb | -102
100+
-.1 kb | -102
101+
-.0 gb | 0
102+
(8 rows)
103+
104+
-- invalid inputs
105+
SELECT pg_size_bytes('1 AB');
106+
ERROR: invalid size: "1 AB"
107+
DETAIL: Invalid size unit: "AB".
108+
HINT: Valid units are "bytes", "kB", "MB", "GB", and "TB".
109+
SELECT pg_size_bytes('1 AB A');
110+
ERROR: invalid size: "1 AB A"
111+
DETAIL: Invalid size unit: "AB A".
112+
HINT: Valid units are "bytes", "kB", "MB", "GB", and "TB".
113+
SELECT pg_size_bytes('1 AB A ');
114+
ERROR: invalid size: "1 AB A "
115+
DETAIL: Invalid size unit: "AB A".
116+
HINT: Valid units are "bytes", "kB", "MB", "GB", and "TB".
117+
SELECT pg_size_bytes('9223372036854775807.9');
118+
ERROR: bigint out of range
119+
SELECT pg_size_bytes('1e100');
120+
ERROR: bigint out of range
121+
SELECT pg_size_bytes('1e1000000000000000000');
122+
ERROR: invalid size: "1e1000000000000000000"
123+
SELECT pg_size_bytes('1 byte'); -- the singular "byte" is not supported
124+
ERROR: invalid size: "1 byte"
125+
DETAIL: Invalid size unit: "byte".
126+
HINT: Valid units are "bytes", "kB", "MB", "GB", and "TB".
127+
SELECT pg_size_bytes('');
128+
ERROR: invalid size: ""
129+
SELECT pg_size_bytes('kb');
130+
ERROR: invalid size: "kb"
131+
SELECT pg_size_bytes('..');
132+
ERROR: invalid size: ".."
133+
SELECT pg_size_bytes('-.');
134+
ERROR: invalid size: "-."
135+
SELECT pg_size_bytes('-.kb');
136+
ERROR: invalid size: "-.kb"
137+
SELECT pg_size_bytes('-. kb');
138+
ERROR: invalid size: "-. kb"
139+
SELECT pg_size_bytes('.+912');
140+
ERROR: invalid size: ".+912"
141+
SELECT pg_size_bytes('+912+ kB');
142+
ERROR: invalid size: "+912+ kB"
143+
DETAIL: Invalid size unit: "+ kB".
144+
HINT: Valid units are "bytes", "kB", "MB", "GB", and "TB".
145+
SELECT pg_size_bytes('++123 kB');
146+
ERROR: invalid size: "++123 kB"

‎src/test/regress/sql/dbsize.sql

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10,3 +10,42 @@ SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM
1010
(10.5::numeric), (1000.5::numeric), (1000000.5::numeric),
1111
(1000000000.5::numeric), (1000000000000.5::numeric),
1212
(1000000000000000.5::numeric)) x(size);
13+
14+
SELECT size, pg_size_bytes(size)FROM
15+
(VALUES ('1'), ('123bytes'), ('1kB'), ('1MB'), (' 1 GB'), ('1.5 GB'),
16+
('1TB'), ('3000 TB'), ('1e6 MB')) x(size);
17+
18+
-- case-insensitive units are supported
19+
SELECT size, pg_size_bytes(size)FROM
20+
(VALUES ('1'), ('123bYteS'), ('1kb'), ('1mb'), (' 1 Gb'), ('1.5 gB'),
21+
('1tb'), ('3000 tb'), ('1e6 mb')) x(size);
22+
23+
-- negative numbers are supported
24+
SELECT size, pg_size_bytes(size)FROM
25+
(VALUES ('-1'), ('-123bytes'), ('-1kb'), ('-1mb'), (' -1 Gb'), ('-1.5 gB'),
26+
('-1tb'), ('-3000 TB'), ('-10e-1 MB')) x(size);
27+
28+
-- different cases with allowed points
29+
SELECT size, pg_size_bytes(size)FROM
30+
(VALUES ('-1.'), ('-1.kb'), ('-1. kb'), ('-0. gb'),
31+
('-.1'), ('-.1kb'), ('-.1 kb'), ('-.0 gb')) x(size);
32+
33+
-- invalid inputs
34+
SELECT pg_size_bytes('1 AB');
35+
SELECT pg_size_bytes('1 AB A');
36+
SELECT pg_size_bytes('1 AB A');
37+
SELECT pg_size_bytes('9223372036854775807.9');
38+
SELECT pg_size_bytes('1e100');
39+
SELECT pg_size_bytes('1e1000000000000000000');
40+
SELECT pg_size_bytes('1 byte');-- the singular "byte" is not supported
41+
SELECT pg_size_bytes('');
42+
43+
SELECT pg_size_bytes('kb');
44+
SELECT pg_size_bytes('..');
45+
SELECT pg_size_bytes('-.');
46+
SELECT pg_size_bytes('-.kb');
47+
SELECT pg_size_bytes('-. kb');
48+
49+
SELECT pg_size_bytes('.+912');
50+
SELECT pg_size_bytes('+912+ kB');
51+
SELECT pg_size_bytes('++123 kB');

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp