In PostgreSQL 9.2, community has added a function pg_xlog_location_diff(), which is very useful for finding the difference between two xlog location in bytes and also useful for monitoring replication. Detail of this function is given in following link:
However this function is not available for users of PostgreSQL/PPAS 9.0/9.1 users. So, I thought to write same function plpgsql so, that users can take benefit of same in 9.0/9.1.
Before using formula and developing function, lets understand what is xlog and offset.
Let’s consider user has used function pg_current_xlog_location() function and he gets following information:
worktest=# select pg_current_xlog_location(); pg_current_xlog_location -------------------------- 1/D1012B80 (1 row)
In above, first field before forward slash is the hexadecimal value of logical xlog file and second field i.e. D1012B80 is hexadecimal offset inside the logical xlogfile.
So, whenerver user sees information for xlog location, he gets xlog information in following format:
(hexadecimal) xlog/ (hexadecimal) offset
To calculate the difference between two xlog location, user can use following formula, which gives difference in hexadecimal:
(FF000000 * xlog + offset) - (FF000000 * xlog + offset)
where FF000000 is max value of offset i.e In PostgreSQL/PPAS offset value can go from 00000000 to FF000000 and if we convert that in decimal, it gives maximum 4278190080 bytes (4080MB)
Based on above formula, following is plpgsql function which can be use to get the difference:
CREATE OR REPLACE FUNCTION pg_xlog_location_diff_sql( text, text) RETURNS numeric LANGUAGE plpgsql AS $function$ DECLARE offset1 text; offset2 text; xlog1 text; xlog2 text; SQL text; diff text; BEGIN /* Extract the Offset and xlog from input in offset and xlog variables */ offset1=split_part($1,'/',2); xlog1=split_part($1,'/',1); offset2=split_part($2,'/',2); xlog2=split_part($2,'/',1); /* Prepare SQL query for calculation based on following formula (FF000000 * xlog + offset) - (FF000000 * xlog + offset) which gives value in hexadecimal. Since, hexadecimal calculation is cumbersome so convert into decimal and then calculate the difference */ SQL='SELECT (x'''||'FF000000'||'''::bigint * x'''||xlog1||'''::bigint + x'''||offset1||'''::bigint)'||' - (x'''||'FF000000'||'''::bigint * x'''||xlog2||'''::bigint + x'''||offset2||'''::bigint)'; EXECUTE SQL into diff; /* Return the value in numeric by explicit casting */ RETURN diff::numeric; END; $function$;
Usage example is given below:
worktest=# select pg_xlog_location_diff_sql(pg_current_xlog_location(),'1/D009F578'); pg_xlog_location_diff_sql --------------------------- 16230472 (1 row)
I hope this will help PostgreSQL/PPAS 9.0/9.1 users.