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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
