Thread: Print pg_lsn as a number?
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-hexrepresentation? Thanks, Scott [1] https://www.postgresql.org/docs/current/static/datatype-pg-lsn.html
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-hexrepresentation? ... > [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
Attachment
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