Print pg_lsn as a number?

Hi,

On 2018-06-12 09:31:49 -0700, Scott Stroupe 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?

What do you want to do with the LSN?

Greetings,

Andres Freund

On Tue, Jun 12, 2018 at 6:31 PM, Scott Stroupe <sstroupe@kofile.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.

On Jun 12, 2018, at 10:39 AM, Andres Freund <andres@anarazel.de> wrote:

Hi,

On 2018-06-12 09:31:49 -0700, Scott Stroupe 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?

What do you want to do with the LSN?

Greetings,

Andres Freund

I want to put pg_lsns into a tool that only understands common numeric representations, to do simple math and comparisons.

Regards,
Scott

On Tue, Jun 12, 2018 at 10:39:43AM -0700, Andres Freund wrote:

On 2018-06-12 09:31:49 -0700, Scott Stroupe 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?

What do you want to do with the LSN?

While fixing the slot advance stuff, I would have liked to get an
automatic test able to reproduce the crash where the slot is first
advanced at a page boundary, and then again moved forward. However it
happens that it is not that easy to do so, so I would be rather
supportive to at least the following operations:
pg_lsn + numeric = pg_lsn.
pg_lsn % numeric = numeric.

We map pg_wal_lsn_diff result to a numeric, so that could make sense to
use numeric as well here, which is not the greatest choice by the way as
that's an int64 internally, but that's more portable for any
(unlikely-to-happen) future changes.

Using the segment size value in pg_settings, you could also advance the
LSN worth a full segment for example...
--
Michael

Hi,

On 2018-06-13 09:18:21 +0900, Michael Paquier wrote:

We map pg_wal_lsn_diff result to a numeric, so that could make sense to
use numeric as well here, which is not the greatest choice by the way as
that's an int64 internally, but that's more portable for any
(unlikely-to-happen) future changes.

The reason to not use int64 is that it's signed. lsns are
unsigned. Therefore you couldn't represent all LSNs without wrapping
into negative.

Greetings,

Andres Freund