Contents
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
- PostgreSQL 14
+(pg_lsn,numeric)and-(pg_lsn,numeric)operators added (commit 9bae7e4c)
- PostgreSQL 13
- support for
MIN()andMAX()aggregates added (commit 313f87a1)
- support for
- PostgreSQL 9.4
- added (commit 7d03a83f)
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;
References
- PostgreSQL documentation: pg_lsn