From: Euler Taveira de Oliveira <euler@timbira.com>
To: Fujii Masao <masao.fujii@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Magnus Hagander <magnus@hagander.net>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Pgsql Hackers <pgsql-hackers@postgresql.org>
Subject: Re: xlog location arithmetic
Date: Thu, 09 Feb 2012 19:00:30 -0300
Message-ID: <4F3441FE.1020008@timbira.com> (raw)
In-Reply-To: <CAHGQGwEV==i8q50KpKBqpG+L6KMuE+XwUFc9FpuWb1+LMfUwDQ@mail.gmail.com>
References: <4EDD97E5.8080201@timbira.com>
<CABUevEzBfLk=8Q9NHAJyNMQPRRvSNAsE+dS2gBiFON8VG5cBCw@mail.gmail.com>
<CA+TgmoZRMNN0eVEsD-kxB9e-MvdmwoTi6guuJUvQP_8q2C5Cyg@mail.gmail.com>
<4EDE5853.3080808@timbira.com>
<CA+TgmoYfaSW8PWUX45Jo1ZSPEbMDPaDdjcGnKr76CGJ1jyZm5w@mail.gmail.com>
<CABUevEx4K5WkWwz4pHkPBpLHOND0aXxP-3oKUmTg9RYCzWzjHA@mail.gmail.com>
<4EF088E2.3090607@timbira.com>
<CABUevEyXUZ=-wONOw-vEaXsrhCS80v1Y4_hN7xWOPAJ8LfTFaA@mail.gmail.com>
<18871.1324652754@sss.pgh.pa.us>
<4F1AE435.50000@timbira.com>
<CAHGQGwGDv-moTqTZtCRb=q0QA4Cx4Ewy_kdD0u4gL4Fg1HPFZQ@mail.gmail.com>
<4F315F6C.8030700@timbira.com>
<CAHGQGwEV==i8q50KpKBqpG+L6KMuE+XwUFc9FpuWb1+LMfUwDQ@mail.gmail.com>
On 08-02-2012 09:35, Fujii Masao wrote:
Fujii, new patch attached. Thanks for your tests.
> 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.
> Why OID needs to be reassigned?
>
There isn't a compelling reason. It is just a way to say: "hey, it is another
function with the same old name".
I'll not attach another version for pg_size_pretty because it is a matter of
updating a duplicated OID.
--
Euler Taveira de Oliveira - Timbira http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
Attachments:xlogdiff3.diff (text/x-patch, 6.6K) downloaddiff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgmlindex 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.cindex 2e10d4d..be7d388 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, 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.hindex 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.hindex d926a88..f6b67b0 100644--- a/src/include/catalog/pg_proc.h+++ b/src/include/catalog/pg_proc.h@@ -2925,6 +2925,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 = 3165 ( 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");
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: euler@timbira.com, masao.fujii@gmail.com, tgl@sss.pgh.pa.us, magnus@hagander.net, robertmhaas@gmail.com
Subject: Re: xlog location arithmetic
In-Reply-To: <4F3441FE.1020008@timbira.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox