pg_lsn

A data type representing a LSN

pg_lsn is a data type representing a LSN (log sequence number).

pg_lsn was added in PostgreSQL 9.4.

Usage

pg_lsn supports values between 0/0 and FFFFFFFF/FFFFFFFF.

Standard comparison operators such as = and > are supported.

From PostgreSQL 14 it is possible to add and subtract byte values.

Change history

Examples

Basic usage of pg_lsn:

postgres=# SELECT '0/0'::pg_lsn;
 pg_lsn 
--------
 0/0
(1 row)

Determining the distance in bytes between two LSNs:

postgres=# SELECT '7/A25801C8'::pg_lsn - '7/A2000000'::pg_lsn;
 ?column? 
----------
  5767624
(1 row)

Adding and subtracting byte values (PostgreSQL 14 and later):

postgres=# SELECT '7/A25801C8'::pg_lsn + 128;
  ?column?  
------------
 7/A2580248
(1 row)

postgres=# SELECT '7/A25801C8'::pg_lsn - 65536;
  ?column?  
------------
 7/A25701C8
(1 row)

Attempting to cast an invalid LSN:

postgres=# SELECT '-1/-1'::pg_lsn;
ERROR:  invalid input syntax for type pg_lsn: "-1/-1"
LINE 1: SELECT '-1/-1'::pg_lsn;

It is not possible to add LSNs:

postgres=# SELECT '7/A25801C8'::pg_lsn + '7/A2000000'::pg_lsn;
ERROR:  operator does not exist: pg_lsn + pg_lsn
LINE 1: SELECT '7/A25801C8'::pg_lsn + '7/A2000000'::pg_lsn;
  • PostgreSQL documentation: pg_lsn

Categories

Data type, PostgreSQL internals

See also

LSN, pg_wal_lsn_diff()