LSN external representation

user_7069611 0
Viewed 380

According to postgres documentation, LSN is a 64-bit integer, representing a byte position in the write-ahead log stream. It is printed as two hexadecimal numbers of up to 8 digits each, separated by a slash; for example, 16/B374D848. Does anyone know what is the meaning of these 2 hexadecimal numbers?

postgresql
3 Answers

Left pad the part right of the slash with zeros, then remove the slash, and you have the offset in bytes described by the documentation.

user_64643080

Quoting the documentation that you can find here :

In PostgreSQL 9.2 and earlier, XLogRecPtr was a struct consisting of two 32 bit integers:

typedef struct XLogRecPtr
{
     uint32     xlogid;         /* log file #, 0 based */
     uint32     xrecoff;        /* byte offset of location in log file */
} XLogRecPtr;

and

As of PostgreSQL 9.3, XLogRecPtr is an unsigned 64 bit integer, defined in include/access/xlogdefs.h:

typedef uint64 XLogRecPtr;

I don't know if there is a daily usage in the work of the database administrator : if I understand correctly you are looking for a mean to convert the external representation (pg_lsn) to the internal representation (64-bit integer) ? if this is it, and using your example : 16/B374D848 you are looking for this kind of answer :

# select x as bit_stream, bit_length(x), x::int8 as int8 from (select ('x' || lpad(split_part('16/B374D848', '/', 1), 8, '0') || lpad(split_part('16/B374D848', '/', 2), 8, '0'))::bit(64)) as t(x);

bit_stream|bit_length|int8 0000000000000000000000000001011000000000000000000000000000000000|64|94489280512 (1 row)

Looking into the code (see the pg_lsn_in_internal function) you have another mean using this kind of SQL :

# select ('x' || lpad(split_part('16/B374D848', '/', 1), 8, '0'))::bit(32)::bigint << 32 | ('x' || lpad(split_part('16/B374D848', '/', 2), 8, '0'))::bit(32)::bigint;

?column? 97500059720 (1 row)

As you can see in the code there is still 2 parts, the first part is the id and the second part is the offset into the logical WAL file name. This is what I understand of the code. The old version of the type is still used using bit function.

user_173283050

pg_lsn type supports - operation and returns byte offset, so here is the simplest way to convert from pg_lsn to bigint:

SELECT pg_current_wal_lsn() - '0/0'
user_16668440
Related