On Fri, Feb 10, 2012 at 09:32, Fujii Masao <masao.fu...@gmail.com> wrote:
> On Fri, Feb 10, 2012 at 7:00 AM, Euler Taveira de Oliveira
> <eu...@timbira.com> wrote:
>> On 08-02-2012 09:35, Fujii Masao wrote:
>>
>> Fujii, new patch attached. Thanks for your tests.
>
> Thanks for the new patch!
>
>>> But another problem happened. When I changed pg_proc.h so that the unused
>>> OID was assigned to pg_xlog_location_diff(), and executed the above again,
>>> I encountered the segmentation fault:
>>>
>> I reproduced the problems in my old 32-bit laptop. I fixed it casting to
>> int64. I also updated the duplicated OID.
>
> Yep, in the updated patch, I could confirm that the function works fine
> without
> any error in my machine. The patch looks fine to me except the following minor
> comments:
I started working on this one to commit it, and came up with a few things more.
Do we even *need* the validate_xlog_location() function? If we just
remove those calls, won't we still catch all the incorrectly formatted
ones in the errors of the sscanf() calls? Or am I too deep into
weekend-mode and missing something obvious?
I've also removed tabs in the documentation, fixed the merge confllict
in pg_proc.h that happened during the wait, and fixed some indentation
(updated patch with these changes attached).
But I'm going to hold off committing it until someone confirms I'm not
caught too deeply in weekend-mode and am missing something obvious in
the comment above about validate_xlog_location.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e8e637b..4ae76e2 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14454,11 +14454,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">
@@ -14526,6 +14530,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>
@@ -14619,6 +14630,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..b8f8152 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,87 @@ 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, Int64GetDatum((int64) loc1.xlogid)),
+ DirectFunctionCall1(int8_numeric, Int64GetDatum((int64) loc2.xlogid))));
+ result = DatumGetNumeric(DirectFunctionCall2(numeric_mul,
+ DirectFunctionCall1(int8_numeric, Int64GetDatum((int64) XLogFileSize)),
+ NumericGetDatum(result)));
+ result = DatumGetNumeric(DirectFunctionCall2(numeric_add,
+ NumericGetDatum(result),
+ DirectFunctionCall1(int8_numeric, Int64GetDatum((int64) loc1.xrecoff))));
+ result = DatumGetNumeric(DirectFunctionCall2(numeric_sub,
+ NumericGetDatum(result),
+ DirectFunctionCall1(int8_numeric, Int64GetDatum((int64) 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 8700d0d..b848a27 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2928,6 +2928,9 @@ 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 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 = 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 = 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");
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
