Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 0209E17A222 for ; Thu, 9 Feb 2012 18:03:11 -0400 (AST) Received: from oproxy8.bluehost.com ([2605:dc00:100:2::a8] helo=oproxy8-pub.bluehost.com) by magus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1Rvc55-0006bG-Au for pgsql-hackers@postgresql.org; Thu, 09 Feb 2012 22:03:11 +0000 Received: (qmail 21038 invoked by uid 0); 9 Feb 2012 22:02:50 -0000 Received: from unknown (HELO host159.hostmonster.com) (74.220.207.159) by oproxy8.bluehost.com with SMTP; 9 Feb 2012 22:02:50 -0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=timbira.com; s=default; h=Content-Type:In-Reply-To:References:Subject:CC:To:MIME-Version:From:Date:Message-ID; bh=Atuznt8MvyRe+65nGOHapEL7bjbXspyBNiuEjkjVrhs=; b=ONuvJUx1xSObBnlRo3vUGivfstI/tYB5Hv8ocAljjP0J1i1slxajyNIAw7VfMhHbagWqdwfS37fIr2x/H6eWUae7lZPR8tl2WQTXShaFk46l6jUvwn0/wvE+OQEMeLyj; Received: from [187.53.33.165] (helo=[10.3.2.4]) by host159.hostmonster.com with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.76) (envelope-from ) id 1Rvc4n-000625-Ly; Thu, 09 Feb 2012 15:02:50 -0700 Message-ID: <4F3441FE.1020008@timbira.com> Date: Thu, 09 Feb 2012 19:00:30 -0300 From: Euler Taveira de Oliveira User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.24) Gecko/20111114 Icedove/3.1.16 MIME-Version: 1.0 To: Fujii Masao CC: Tom Lane , Magnus Hagander , Robert Haas , Pgsql Hackers Subject: Re: xlog location arithmetic References: <4EDD97E5.8080201@timbira.com> <4EDE5853.3080808@timbira.com> <4EF088E2.3090607@timbira.com> <18871.1324652754@sss.pgh.pa.us> <4F1AE435.50000@timbira.com> <4F315F6C.8030700@timbira.com> In-Reply-To: X-Enigmail-Version: 1.1.2 Content-Type: multipart/mixed; boundary="------------050107030400050807020205" X-Identified-User: {1651:host159.hostmonster.com:timbirac:timbira.com} {sentby:smtp auth 187.53.33.165 authed with euler@timbira.com} X-Pg-Spam-Score: -2.0 (--) X-Archive-Number: 201202/422 X-Sequence-Number: 202787 This is a multi-part message in MIME format. --------------050107030400050807020205 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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 --------------050107030400050807020205 Content-Type: text/x-patch; name="xlogdiff3.diff" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="xlogdiff3.diff" 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); pg_xlogfile_name_offset + + pg_xlog_location_diff + The functions shown in assist in making on-line backups. - These functions cannot be executed during recovery. + These functions cannot be executed during recovery (except + pg_xlog_location_diff). @@ -14518,6 +14522,13 @@ SELECT set_config('log_statement_stats', 'off', false); text, integerConvert transaction log location string to file name and decimal byte offset within file + + + pg_xlog_location_diff(location text, location text) + + numeric + Calculate the difference between two transaction log locations +
@@ -14611,6 +14622,13 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); + pg_xlog_location_diff calculates the difference in bytes + between two transaction log locations. It can be used with + pg_stat_replication or some functions shown in + to get the replication lag. + + + For details about proper usage of these functions, see . diff --git a/src/backend/access/transam/xlogfuncs.c b/src/backend/access/transam/xlogfuncs.c index 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.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 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"); --------------050107030400050807020205--