On Sat, Mar 10, 2012 at 3:23 AM, Robert Haas <[email protected]> wrote:
> On Fri, Mar 9, 2012 at 12:38 PM, Magnus Hagander <[email protected]> wrote:
>> On Fri, Mar 9, 2012 at 18:18, Tom Lane <[email protected]> wrote:
>>> Magnus Hagander <[email protected]> writes:
>>>> On Fri, Mar 9, 2012 at 15:37, Tom Lane <[email protected]> wrote:
>>>>> Why would it be useful to use pg_size_pretty on xlog locations?
>>>>> -1 because of the large expense of bigint->numeric->whatever conversion
>>>>> that would be added to existing uses.
>>>
>>>> Given the expense, perhaps we need to different (overloaded) functions
>>>> instead?
Agreed. Attached patch introduces the overloaded funtion
pg_size_pretty(numeric).
>>> That would be a workable solution, but I continue to not believe that
>>> this is useful enough to be worth the trouble.
>>
>> There's certainly some use to being able to prettify it. Wouldn't a
>> pg_size_pretty(numeric) also be useful if you want to pg_size_() a
>> sum() of something? Used on files it doesn't make too much sense,
>> given how big those files have to be, but it can be used on other
>> things as well...
>>
>> I can see a usecase for having a pg_size_pretty(numeric) as an option.
>> Not necessarily a very big one, but a >0 one.
>
> +1.
+1, too.
Regards,
--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 14989,14995 **** postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
</row>
<row>
<entry>
! <literal><function>pg_size_pretty(<type>bigint</type>)</function></literal>
</entry>
<entry><type>text</type></entry>
<entry>Converts a size in bytes into a human-readable format with size units</entry>
--- 14989,14995 ----
</row>
<row>
<entry>
! <literal><function>pg_size_pretty(<type>bigint</type> or <type>numeric</type>)</function></literal>
</entry>
<entry><type>text</type></entry>
<entry>Converts a size in bytes into a human-readable format with size units</entry>
*** a/src/backend/utils/adt/dbsize.c
--- b/src/backend/utils/adt/dbsize.c
***************
*** 24,29 ****
--- 24,30 ----
#include "storage/fd.h"
#include "utils/acl.h"
#include "utils/builtins.h"
+ #include "utils/numeric.h"
#include "utils/rel.h"
#include "utils/relmapper.h"
#include "utils/syscache.h"
***************
*** 550,555 **** pg_size_pretty(PG_FUNCTION_ARGS)
--- 551,652 ----
PG_RETURN_TEXT_P(cstring_to_text(buf));
}
+ Datum
+ pg_size_pretty_numeric(PG_FUNCTION_ARGS)
+ {
+ Numeric size = PG_GETARG_NUMERIC(0);
+ Numeric limit, limit2;
+ char *buf, *result;
+
+ limit = DatumGetNumeric(DirectFunctionCall1(int8_numeric, Int64GetDatum((int64) (10 * 1024))));
+ limit2 = DatumGetNumeric(DirectFunctionCall1(int8_numeric, Int64GetDatum((int64) (10 * 1024 * 2 - 1))));
+
+ if (DatumGetBool(DirectFunctionCall2(numeric_lt, NumericGetDatum(size), NumericGetDatum(limit))))
+ {
+ buf = DatumGetCString(DirectFunctionCall1(numeric_out, NumericGetDatum(size)));
+ result = palloc(strlen(buf) + 7);
+ strcpy(result, buf);
+ strcat(result, " bytes");
+ }
+ else
+ {
+ Numeric arg2;
+
+ /* keep one extra bit for rounding */
+ /* size >>= 9 */
+ arg2 = DatumGetNumeric(DirectFunctionCall1(int8_numeric, Int64GetDatum((int64) pow(2, 9))));
+ size = DatumGetNumeric(DirectFunctionCall2(numeric_div_trunc, NumericGetDatum(size), NumericGetDatum(arg2)));
+
+ if (DatumGetBool(DirectFunctionCall2(numeric_lt, NumericGetDatum(size), NumericGetDatum(limit2))))
+ {
+ /* size = (size + 1) / 2 */
+ size = DatumGetNumeric(DirectFunctionCall2(numeric_add, NumericGetDatum(size),
+ DirectFunctionCall1(int8_numeric, Int64GetDatum(1))));
+ size = DatumGetNumeric(DirectFunctionCall2(numeric_div_trunc, NumericGetDatum(size),
+ DirectFunctionCall1(int8_numeric, Int64GetDatum(2))));
+ buf = DatumGetCString(DirectFunctionCall1(numeric_out, NumericGetDatum(size)));
+ result = palloc(strlen(buf) + 4);
+ strcpy(result, buf);
+ strcat(result, " kB");
+ }
+ else
+ {
+ Numeric arg3;
+
+ /* size >>= 10 */
+ arg3 = DatumGetNumeric(DirectFunctionCall1(int8_numeric, Int64GetDatum((int64) pow(2, 10))));
+ size = DatumGetNumeric(DirectFunctionCall2(numeric_div_trunc, NumericGetDatum(size), NumericGetDatum(arg3)));
+
+ if (DatumGetBool(DirectFunctionCall2(numeric_lt, NumericGetDatum(size), NumericGetDatum(limit2))))
+ {
+ /* size = (size + 1) / 2 */
+ size = DatumGetNumeric(DirectFunctionCall2(numeric_add, NumericGetDatum(size),
+ DirectFunctionCall1(int8_numeric, Int64GetDatum(1))));
+ size = DatumGetNumeric(DirectFunctionCall2(numeric_div_trunc, NumericGetDatum(size),
+ DirectFunctionCall1(int8_numeric, Int64GetDatum(2))));
+ buf = DatumGetCString(DirectFunctionCall1(numeric_out, NumericGetDatum(size)));
+ result = palloc(strlen(buf) + 4);
+ strcpy(result, buf);
+ strcat(result, " MB");
+ }
+ else
+ {
+ /* size >>= 10 */
+ size = DatumGetNumeric(DirectFunctionCall2(numeric_div_trunc, NumericGetDatum(size), NumericGetDatum(arg3)));
+
+ if (DatumGetBool(DirectFunctionCall2(numeric_lt, NumericGetDatum(size), NumericGetDatum(limit2))))
+ {
+ /* size = (size + 1) / 2 */
+ size = DatumGetNumeric(DirectFunctionCall2(numeric_add, NumericGetDatum(size),
+ DirectFunctionCall1(int8_numeric, Int64GetDatum(1))));
+ size = DatumGetNumeric(DirectFunctionCall2(numeric_div_trunc, NumericGetDatum(size),
+ DirectFunctionCall1(int8_numeric, Int64GetDatum(2))));
+ buf = DatumGetCString(DirectFunctionCall1(numeric_out, NumericGetDatum(size)));
+ result = palloc(strlen(buf) + 4);
+ strcpy(result, buf);
+ strcat(result, " GB");
+ }
+ else
+ {
+ /* size = (size + 1) / 2 */
+ size = DatumGetNumeric(DirectFunctionCall2(numeric_div_trunc, NumericGetDatum(size), NumericGetDatum(arg3)));
+ size = DatumGetNumeric(DirectFunctionCall2(numeric_add, NumericGetDatum(size),
+ DirectFunctionCall1(int8_numeric, Int64GetDatum(1))));
+ size = DatumGetNumeric(DirectFunctionCall2(numeric_div_trunc, NumericGetDatum(size),
+ DirectFunctionCall1(int8_numeric, Int64GetDatum(2))));
+
+ buf = DatumGetCString(DirectFunctionCall1(numeric_out, NumericGetDatum(size)));
+ result = palloc(strlen(buf) + 4);
+ strcpy(result, buf);
+ strcat(result, " TB");
+ }
+ }
+ }
+ }
+
+ PG_RETURN_TEXT_P(cstring_to_text(result));
+ }
+
/*
* Get the filenode of a relation
*
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 2938,2943 **** DESCR("xlog filename, given an xlog location");
--- 2938,2945 ----
DATA(insert OID = 3165 ( pg_xlog_location_diff PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 1700 "25 25" _null_ _null_ _null_ _null_ pg_xlog_location_diff _null_ _null_ _null_ ));
DESCR("difference in bytes, given two xlog locations");
+ DATA(insert OID = 3166 ( pg_size_pretty PGNSP PGUID 12 1 0 0 0 f f f f t f v 1 0 25 "1700" _null_ _null_ _null_ _null_ pg_size_pretty_numeric _null_ _null_ _null_ ));
+ DESCR("convert a numeric to a human readable text using size units");
DATA(insert OID = 3809 ( pg_export_snapshot PGNSP PGUID 12 1 0 0 0 f f f f t f v 0 0 25 "" _null_ _null_ _null_ _null_ pg_export_snapshot _null_ _null_ _null_ ));
DESCR("export a snapshot");
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
***************
*** 453,458 **** extern Datum pg_database_size_name(PG_FUNCTION_ARGS);
--- 453,459 ----
extern Datum pg_relation_size(PG_FUNCTION_ARGS);
extern Datum pg_total_relation_size(PG_FUNCTION_ARGS);
extern Datum pg_size_pretty(PG_FUNCTION_ARGS);
+ extern Datum pg_size_pretty_numeric(PG_FUNCTION_ARGS);
extern Datum pg_table_size(PG_FUNCTION_ARGS);
extern Datum pg_indexes_size(PG_FUNCTION_ARGS);
extern Datum pg_relation_filenode(PG_FUNCTION_ARGS);
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
