Re: Print pg_lsn as a number?

On Tue, Jun 12, 2018 at 6:31 PM, Scott Stroupe <sstroupe(at)kofile(dot)net> wrote:
> According to the documentation[1], pg_lsn is a 64-bit integer that's printed as two hex numbers separated by a slash, e.g. 68/1225BB70. Is there a way to get the 64-bit integer in a common numeric representation instead of the peculiar hex-slash-hex representation?
...
> [1] https://www.postgresql.org/docs/current/static/datatype-pg-lsn.html

Quoting your own [1] ref :"Two LSNs can be subtracted using the -
operator; the result is the number of bytes separating those
write-ahead log locations."

You can try substraction from an arbitrary origin ( pg_lsn('0/0')
seems nice, as arbitrary as Greenwich meridian ), and it worked for me
in

select
pg_lsn('68/1225BB70')
, pg_lsn('0/0')
, pg_lsn('68/1225BB70') - pg_lsn('0/0')
, to_hex((pg_lsn('68/1225BB70') - pg_lsn('0/0'))::bigint)
;

( http://sqlfiddle.com/#!17/9eecb/16272 )

Reconstructing via simple addition does not work, but you can do
division, modulus, double to_hex, join with '/', cast to pg_lsn if you
like.

Francisco Olarte.