On 23-12-2011 12:05, Tom Lane wrote: > I too think a datatype is overkill, if we're only planning on providing > one function. Just emit the values as numeric and have done. > Here it is. Output changed to numeric. While the output was int8 I could use pg_size_pretty but now I couldn't. I attached another patch that implements pg_size_pretty(numeric).
-- Euler Taveira de Oliveira - Timbira http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 48631cc..04bc24d 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -14378,6 +14378,9 @@ SELECT set_config('log_statement_stats', 'off', false); <indexterm> <primary>pg_xlogfile_name_offset</primary> </indexterm> + <indexterm> + <primary>pg_xlog_location_diff</primary> + </indexterm> <para> The functions shown in <xref @@ -14450,6 +14453,13 @@ SELECT set_config('log_statement_stats', 'off', false); <entry><type>text</>, <type>integer</></entry> <entry>Convert transaction log location string to file name and decimal byte offset within file</entry> </row> + <row> + <entry> + <literal><function>pg_xlog_location_diff(<parameter>location</> <type>text</>, <parameter>location</> <type>text</>)</function></literal> + </entry> + <entry><type>numeric</></entry> + <entry>Calculate the difference between two transaction log locations</entry> + </row> </tbody> </tgroup> </table> @@ -14543,6 +14553,13 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); </para> <para> + <function>pg_xlog_location_diff</> calculates the difference in bytes + between two transaction log locations. It can be used with + <structname>pg_stat_replication</structname> or some functions shown in + <xref linkend="functions-admin-backup-table"> to get the replication lag. + </para> + + <para> For details about proper usage of these functions, see <xref linkend="continuous-archiving">. </para> diff --git a/src/backend/access/transam/xlogfuncs.c b/src/backend/access/transam/xlogfuncs.c index 2e10d4d..e03c5e8 100644 --- a/src/backend/access/transam/xlogfuncs.c +++ b/src/backend/access/transam/xlogfuncs.c @@ -26,6 +26,7 @@ #include "replication/walreceiver.h" #include "storage/smgr.h" #include "utils/builtins.h" +#include "utils/numeric.h" #include "utils/guc.h" #include "utils/timestamp.h" @@ -465,3 +466,84 @@ pg_is_in_recovery(PG_FUNCTION_ARGS) { PG_RETURN_BOOL(RecoveryInProgress()); } + +static void +validate_xlog_location(char *str) +{ +#define MAXLSNCOMPONENT 8 + + int len1, len2; + + len1 = strspn(str, "0123456789abcdefABCDEF"); + if (len1 < 1 || len1 > MAXLSNCOMPONENT || str[len1] != '/') + ereport(ERROR, + (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), + errmsg("invalid input syntax for transaction log location: \"%s\"", str))); + len2 = strspn(str + len1 + 1, "0123456789abcdefABCDEF"); + if (len2 < 1 || len2 > MAXLSNCOMPONENT || str[len1 + 1 + len2] != '\0') + ereport(ERROR, + (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), + errmsg("invalid input syntax for transaction log location: \"%s\"", str))); +} + +/* + * Compute the difference in bytes between two WAL locations. + */ +Datum +pg_xlog_location_diff(PG_FUNCTION_ARGS) +{ + text *location1 = PG_GETARG_TEXT_P(0); + text *location2 = PG_GETARG_TEXT_P(1); + char *str1, *str2; + uint64 xlogid1, xrecoff1; + uint64 xlogid2, xrecoff2; + Numeric result; + + /* + * Read and parse input + */ + str1 = text_to_cstring(location1); + str2 = text_to_cstring(location2); + + validate_xlog_location(str1); + validate_xlog_location(str2); + + if (sscanf(str1, "%8lX/%8lX", &xlogid1, &xrecoff1) != 2) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not parse transaction log location \"%s\"", str1))); + if (sscanf(str2, "%8lX/%8lX", &xlogid2, &xrecoff2) != 2) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not parse transaction log location \"%s\"", str2))); + + /* + * Sanity check + */ + if (xrecoff1 > XLogFileSize) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("xrecoff \"%lX\" is out of valid range, 0..%X", xrecoff1, XLogFileSize))); + if (xrecoff2 > XLogFileSize) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("xrecoff \"%lX\" is out of valid range, 0..%X", xrecoff2, XLogFileSize))); + + /* + * result = XLogFileSize * (xlogid1 - xlogid2) + xrecoff1 - xrecoff2 + */ + result = DatumGetNumeric(DirectFunctionCall2(numeric_sub, + DirectFunctionCall1(int8_numeric, Int64GetDatum(xlogid1)), + DirectFunctionCall1(int8_numeric, Int64GetDatum(xlogid2)))); + result = DatumGetNumeric(DirectFunctionCall2(numeric_mul, + DirectFunctionCall1(int8_numeric, Int64GetDatum(XLogFileSize)), + NumericGetDatum(result))); + result = DatumGetNumeric(DirectFunctionCall2(numeric_add, + NumericGetDatum(result), + DirectFunctionCall1(int8_numeric, Int64GetDatum(xrecoff1)))); + result = DatumGetNumeric(DirectFunctionCall2(numeric_sub, + NumericGetDatum(result), + DirectFunctionCall1(int8_numeric, Int64GetDatum(xrecoff2)))); + + PG_RETURN_NUMERIC(result); +} diff --git a/src/include/access/xlog_internal.h b/src/include/access/xlog_internal.h index db6380f..fa45aa1 100644 --- a/src/include/access/xlog_internal.h +++ b/src/include/access/xlog_internal.h @@ -281,5 +281,6 @@ extern Datum pg_is_in_recovery(PG_FUNCTION_ARGS); extern Datum pg_xlog_replay_pause(PG_FUNCTION_ARGS); extern Datum pg_xlog_replay_resume(PG_FUNCTION_ARGS); extern Datum pg_is_xlog_replay_paused(PG_FUNCTION_ARGS); +extern Datum pg_xlog_location_diff(PG_FUNCTION_ARGS); #endif /* XLOG_INTERNAL_H */ diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index b6ac195..9940658 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -2902,6 +2902,8 @@ DATA(insert OID = 2850 ( pg_xlogfile_name_offset PGNSP PGUID 12 1 0 0 0 f f f t DESCR("xlog filename and byte offset, given an xlog location"); DATA(insert OID = 2851 ( pg_xlogfile_name PGNSP PGUID 12 1 0 0 0 f f f t f i 1 0 25 "25" _null_ _null_ _null_ _null_ pg_xlogfile_name _null_ _null_ _null_ )); DESCR("xlog filename, given an xlog location"); +DATA(insert OID = 3150 ( pg_xlog_location_diff PGNSP PGUID 12 1 0 0 0 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 = 3809 ( pg_export_snapshot PGNSP PGUID 12 1 0 0 0 f f f t f v 0 0 25 "" _null_ _null_ _null_ _null_ pg_export_snapshot _null_ _null_ _null_ )); DESCR("export a snapshot");
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 48631cc..eff2b0e 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -14881,6 +14881,13 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); </row> <row> <entry> + <literal><function>pg_size_pretty(<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> + </row> + <row> + <entry> <literal><function>pg_table_size(<type>regclass</type>)</function></literal> </entry> <entry><type>bigint</type></entry> diff --git a/src/backend/utils/adt/dbsize.c b/src/backend/utils/adt/dbsize.c index 26a8c01..494ce27 100644 --- a/src/backend/utils/adt/dbsize.c +++ b/src/backend/utils/adt/dbsize.c @@ -24,6 +24,7 @@ #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,6 +551,93 @@ pg_size_pretty(PG_FUNCTION_ARGS) PG_RETURN_TEXT_P(cstring_to_text(buf)); } +Datum +pg_size_pretty_num(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 = strcat(buf, " 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 = strcat(buf, " 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 = strcat(buf, " 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 = strcat(buf, " 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 = strcat(buf, " TB"); + } + } + } + } + + PG_RETURN_TEXT_P(cstring_to_text(result)); +} + /* * Get the filenode of a relation * diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index b6ac195..0d8d486 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -3364,6 +3364,7 @@ DESCR("disk space usage for the specified fork of a table or index"); DATA(insert OID = 2286 ( pg_total_relation_size PGNSP PGUID 12 1 0 0 0 f f f t f v 1 0 20 "2205" _null_ _null_ _null_ _null_ pg_total_relation_size _null_ _null_ _null_ )); DESCR("total disk space usage for the specified table and associated indexes"); DATA(insert OID = 2288 ( pg_size_pretty PGNSP PGUID 12 1 0 0 0 f f f t f v 1 0 25 "20" _null_ _null_ _null_ _null_ pg_size_pretty _null_ _null_ _null_ )); +DATA(insert OID = 3151 ( pg_size_pretty PGNSP PGUID 12 1 0 0 0 f f f t f v 1 0 25 "1700" _null_ _null_ _null_ _null_ pg_size_pretty_num _null_ _null_ _null_ )); DESCR("convert a long int to a human readable text using size units"); DATA(insert OID = 2997 ( pg_table_size PGNSP PGUID 12 1 0 0 0 f f f t f v 1 0 20 "2205" _null_ _null_ _null_ _null_ pg_table_size _null_ _null_ _null_ )); DESCR("disk space usage for the specified table, including TOAST, free space and visibility map"); diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h index 68179d5..3d07f29 100644 --- a/src/include/utils/builtins.h +++ b/src/include/utils/builtins.h @@ -453,6 +453,7 @@ extern Datum pg_database_size_name(PG_FUNCTION_ARGS); 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_num(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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers