On 26-01-2012 06:19, Fujii Masao wrote: Thanks for your review. Comments below.
> When I compiled the source with xlogdiff.patch, I got the following warnings. > > xlogfuncs.c:511:2: warning: format '%lX' expects argument of type > 'long unsigned int *', but argument 3 has type 'uint64 *' [-Wformat] > What is your compiler? I'm using gcc 4.6.2. I refactored the patch so I'm now using XLogRecPtr and %X. > postgres=# SELECT pg_xlog_location_diff('0/2000074', '0/2000074'); > ERROR: xrecoff "2000074" is out of valid range, 0..A4A534C > Ugh? I can't reproduce that. It seems to be related to long int used by the prior version. > Since pg_xlog_location_diff() can be executed during recovery, > the above needs to be updated. > Fixed. >> 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). > I realized that it collides with the pg_size_pretty(int8) if we don't specify a type. Hence, I decided to drop the pg_size_pretty(int8) in favor of pg_size_pretty(numeric). It is slower than the former but it is not a performance critical function. > According to the above source code comment in pg_proc.h, ISTM > pg_size_pretty() for numeric also needs to have its own DESCR(). > Fixed. > According to "man strcat", the dest string must have enough space for > the result. > "buf" has enough space? > Ops. Fixed. -- 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 236a60a..511a918 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -14942,7 +14942,7 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); </row> <row> <entry> - <literal><function>pg_size_pretty(<type>bigint</type>)</function></literal> + <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> diff --git a/src/backend/utils/adt/dbsize.c b/src/backend/utils/adt/dbsize.c index 26a8c01..d4a142b 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" @@ -506,48 +507,101 @@ pg_total_relation_size(PG_FUNCTION_ARGS) PG_RETURN_INT64(size); } -/* - * formatting with size units - */ Datum pg_size_pretty(PG_FUNCTION_ARGS) { - int64 size = PG_GETARG_INT64(0); - char buf[64]; - int64 limit = 10 * 1024; - int64 limit2 = limit * 2 - 1; + Numeric size = PG_GETARG_NUMERIC(0); + Numeric limit, limit2; + + char *buf, *result; - if (size < limit) - snprintf(buf, sizeof(buf), INT64_FORMAT " bytes", size); + 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 { - size >>= 9; /* keep one extra bit for rounding */ - if (size < limit2) - snprintf(buf, sizeof(buf), INT64_FORMAT " kB", - (size + 1) / 2); + 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 { - size >>= 10; - if (size < limit2) - snprintf(buf, sizeof(buf), INT64_FORMAT " MB", - (size + 1) / 2); + 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; - if (size < limit2) - snprintf(buf, sizeof(buf), INT64_FORMAT " GB", - (size + 1) / 2); + /* 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 >>= 10; - snprintf(buf, sizeof(buf), INT64_FORMAT " TB", - (size + 1) / 2); + /* 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(buf)); + PG_RETURN_TEXT_P(cstring_to_text(result)); } /* diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 8fc4ddb..d150584 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -3376,8 +3376,8 @@ DATA(insert OID = 2332 ( pg_relation_size PGNSP PGUID 12 1 0 0 0 f f f t f v 2 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_ )); -DESCR("convert a long int to a human readable text using size units"); +DATA(insert OID = 3158 ( 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 _null_ _null_ _null_ )); +DESCR("convert a numeric 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"); DATA(insert OID = 2998 ( pg_indexes_size PGNSP PGUID 12 1 0 0 0 f f f t f v 1 0 20 "2205" _null_ _null_ _null_ _null_ pg_indexes_size _null_ _null_ _null_ ));
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 236a60a..826f002 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -14446,11 +14446,15 @@ 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 linkend="functions-admin-backup-table"> assist in making on-line backups. - These functions cannot be executed during recovery. + These functions cannot be executed during recovery (except + <function>pg_xlog_location_diff</function>). </para> <table id="functions-admin-backup-table"> @@ -14518,6 +14522,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> @@ -14611,6 +14622,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..2a6bbcf 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,83 @@ 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; + XLogRecPtr loc1, loc2; + 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, "%X/%X", &loc1.xlogid, &loc1.xrecoff) != 2) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not parse transaction log location \"%s\"", str1))); + if (sscanf(str2, "%X/%X", &loc2.xlogid, &loc2.xrecoff) != 2) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not parse transaction log location \"%s\"", str2))); + + /* + * Sanity check + */ + if (loc1.xrecoff > XLogFileSize) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("xrecoff \"%X\" is out of valid range, 0..%X", loc1.xrecoff, XLogFileSize))); + if (loc2.xrecoff > XLogFileSize) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("xrecoff \"%X\" is out of valid range, 0..%X", loc2.xrecoff, XLogFileSize))); + + /* + * result = XLogFileSize * (xlogid1 - xlogid2) + xrecoff1 - xrecoff2 + */ + result = DatumGetNumeric(DirectFunctionCall2(numeric_sub, + DirectFunctionCall1(int8_numeric, UInt32GetDatum(loc1.xlogid)), + DirectFunctionCall1(int8_numeric, UInt32GetDatum(loc2.xlogid)))); + result = DatumGetNumeric(DirectFunctionCall2(numeric_mul, + DirectFunctionCall1(int8_numeric, UInt32GetDatum(XLogFileSize)), + NumericGetDatum(result))); + result = DatumGetNumeric(DirectFunctionCall2(numeric_add, + NumericGetDatum(result), + DirectFunctionCall1(int8_numeric, UInt32GetDatum(loc1.xrecoff)))); + result = DatumGetNumeric(DirectFunctionCall2(numeric_sub, + NumericGetDatum(result), + DirectFunctionCall1(int8_numeric, UInt32GetDatum(loc2.xrecoff)))); + + PG_RETURN_NUMERIC(result); +} diff --git a/src/include/access/xlog_internal.h b/src/include/access/xlog_internal.h index b81c156..c079a9a 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 8fc4ddb..4064a8a 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -2915,6 +2915,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 = 3157 ( 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");
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers