Skip to content

Commit bc5ac36

Browse files
mhagandermhagander
committed
Add function pg_xlog_location_diff to help comparisons
Comparing two xlog locations are useful for example when calculating replication lag. Euler Taveira de Oliveira, reviewed by Fujii Masao, and some cleanups from me
1 parent 0e5e167 commit bc5ac36

File tree

5 files changed

+114
-2
lines changed

5 files changed

+114
-2
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 19 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -14475,11 +14475,15 @@ SELECT set_config('log_statement_stats', 'off', false);
1447514475
<indexterm>
1447614476
<primary>pg_xlogfile_name_offset</primary>
1447714477
</indexterm>
14478+
<indexterm>
14479+
<primary>pg_xlog_location_diff</primary>
14480+
</indexterm>
1447814481

1447914482
<para>
1448014483
The functions shown in <xref
1448114484
linkend="functions-admin-backup-table"> assist in making on-line backups.
14482-
These functions cannot be executed during recovery.
14485+
These functions cannot be executed during recovery (except
14486+
<function>pg_xlog_location_diff</function>).
1448314487
</para>
1448414488

1448514489
<table id="functions-admin-backup-table">
@@ -14547,6 +14551,13 @@ SELECT set_config('log_statement_stats', 'off', false);
1454714551
<entry><type>text</>, <type>integer</></entry>
1454814552
<entry>Convert transaction log location string to file name and decimal byte offset within file</entry>
1454914553
</row>
14554+
<row>
14555+
<entry>
14556+
<literal><function>pg_xlog_location_diff(<parameter>location</> <type>text</>, <parameter>location</> <type>text</>)</function></literal>
14557+
</entry>
14558+
<entry><type>numeric</></entry>
14559+
<entry>Calculate the difference between two transaction log locations</entry>
14560+
</row>
1455014561
</tbody>
1455114562
</tgroup>
1455214563
</table>
@@ -14639,6 +14650,13 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
1463914650
needs to be archived.
1464014651
</para>
1464114652

14653+
<para>
14654+
<function>pg_xlog_location_diff</> calculates the difference in bytes
14655+
between two transaction log locations. It can be used with
14656+
<structname>pg_stat_replication</structname> or some functions shown in
14657+
<xref linkend="functions-admin-backup-table"> to get the replication lag.
14658+
</para>
14659+
1464214660
<para>
1464314661
For details about proper usage of these functions, see
1464414662
<xref linkend="continuous-archiving">.

‎src/backend/access/transam/xlogfuncs.c

Lines changed: 90 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -26,6 +26,7 @@
2626
#include "replication/walreceiver.h"
2727
#include "storage/smgr.h"
2828
#include "utils/builtins.h"
29+
#include "utils/numeric.h"
2930
#include "utils/guc.h"
3031
#include "utils/timestamp.h"
3132

@@ -465,3 +466,92 @@ pg_is_in_recovery(PG_FUNCTION_ARGS)
465466
{
466467
PG_RETURN_BOOL(RecoveryInProgress());
467468
}
469+
470+
/*
471+
* Validate the text form of a transaction log location.
472+
* (Just using sscanf() input allows incorrect values such as
473+
* negatives, so we have to be a bit more careful about that).
474+
*/
475+
static void
476+
validate_xlog_location(char *str)
477+
{
478+
#define MAXLSNCOMPONENT 8
479+
480+
int len1,
481+
len2;
482+
483+
len1 = strspn(str, "0123456789abcdefABCDEF");
484+
if (len1 < 1 || len1 > MAXLSNCOMPONENT || str[len1] != '/')
485+
ereport(ERROR,
486+
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
487+
errmsg("invalid input syntax for transaction log location: \"%s\"", str)));
488+
489+
len2 = strspn(str + len1 + 1, "0123456789abcdefABCDEF");
490+
if (len2 < 1 || len2 > MAXLSNCOMPONENT || str[len1 + 1 + len2] != '\0')
491+
ereport(ERROR,
492+
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
493+
errmsg("invalid input syntax for transaction log location: \"%s\"", str)));
494+
}
495+
496+
/*
497+
* Compute the difference in bytes between two WAL locations.
498+
*/
499+
Datum
500+
pg_xlog_location_diff(PG_FUNCTION_ARGS)
501+
{
502+
text *location1 = PG_GETARG_TEXT_P(0);
503+
text *location2 = PG_GETARG_TEXT_P(1);
504+
char *str1,
505+
*str2;
506+
XLogRecPtr loc1,
507+
loc2;
508+
Numeric result;
509+
510+
/*
511+
* Read and parse input
512+
*/
513+
str1 = text_to_cstring(location1);
514+
str2 = text_to_cstring(location2);
515+
516+
validate_xlog_location(str1);
517+
validate_xlog_location(str2);
518+
519+
if (sscanf(str1, "%X/%X", &loc1.xlogid, &loc1.xrecoff) != 2)
520+
ereport(ERROR,
521+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
522+
errmsg("could not parse transaction log location \"%s\"", str1)));
523+
if (sscanf(str2, "%X/%X", &loc2.xlogid, &loc2.xrecoff) != 2)
524+
ereport(ERROR,
525+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
526+
errmsg("could not parse transaction log location \"%s\"", str2)));
527+
528+
/*
529+
* Sanity check
530+
*/
531+
if (loc1.xrecoff > XLogFileSize)
532+
ereport(ERROR,
533+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
534+
errmsg("xrecoff \"%X\" is out of valid range, 0..%X", loc1.xrecoff, XLogFileSize)));
535+
if (loc2.xrecoff > XLogFileSize)
536+
ereport(ERROR,
537+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
538+
errmsg("xrecoff \"%X\" is out of valid range, 0..%X", loc2.xrecoff, XLogFileSize)));
539+
540+
/*
541+
* result = XLogFileSize * (xlogid1 - xlogid2) + xrecoff1 - xrecoff2
542+
*/
543+
result = DatumGetNumeric(DirectFunctionCall2(numeric_sub,
544+
DirectFunctionCall1(int8_numeric, Int64GetDatum((int64) loc1.xlogid)),
545+
DirectFunctionCall1(int8_numeric, Int64GetDatum((int64) loc2.xlogid))));
546+
result = DatumGetNumeric(DirectFunctionCall2(numeric_mul,
547+
DirectFunctionCall1(int8_numeric, Int64GetDatum((int64) XLogFileSize)),
548+
NumericGetDatum(result)));
549+
result = DatumGetNumeric(DirectFunctionCall2(numeric_add,
550+
NumericGetDatum(result),
551+
DirectFunctionCall1(int8_numeric, Int64GetDatum((int64) loc1.xrecoff))));
552+
result = DatumGetNumeric(DirectFunctionCall2(numeric_sub,
553+
NumericGetDatum(result),
554+
DirectFunctionCall1(int8_numeric, Int64GetDatum((int64) loc2.xrecoff))));
555+
556+
PG_RETURN_NUMERIC(result);
557+
}

‎src/include/access/xlog_internal.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -281,5 +281,6 @@ extern Datum pg_is_in_recovery(PG_FUNCTION_ARGS);
281281
extern Datum pg_xlog_replay_pause(PG_FUNCTION_ARGS);
282282
extern Datum pg_xlog_replay_resume(PG_FUNCTION_ARGS);
283283
extern Datum pg_is_xlog_replay_paused(PG_FUNCTION_ARGS);
284+
extern Datum pg_xlog_location_diff(PG_FUNCTION_ARGS);
284285

285286
#endif /* XLOG_INTERNAL_H */

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 201203031
56+
#define CATALOG_VERSION_NO 201203041
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2936,6 +2936,9 @@ DESCR("xlog filename and byte offset, given an xlog location");
29362936
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_ ));
29372937
DESCR("xlog filename, given an xlog location");
29382938

2939+
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_ ));
2940+
DESCR("difference in bytes, given two xlog locations");
2941+
29392942
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_ ));
29402943
DESCR("export a snapshot");
29412944

0 commit comments

Comments
 (0)