Date: Tue, 12 Aug 2025 13:34:56 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Fabrice Chapuis <fabrice636861@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: pg_split_walfile_name
Message-ID: <aJt7QFz2g1VUbZFm@momjian.us>
References: 
 <CAA5-nLCE4PyT7Dh5ogNwBc=dwdA2T58Kc7uWHN4fW1GmKPAG4w@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAA5-nLCE4PyT7Dh5ogNwBc=dwdA2T58Kc7uWHN4fW1GmKPAG4w@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/aJt7QFz2g1VUbZFm%40momjian.us>
Precedence: bulk

On Thu, Jul 31, 2025 at 10:08:25AM +0200, Fabrice Chapuis wrote:
> Hi,
> 
> In the documentation, PostgreSQL: Documentation: 17: 9.28. System
> Administration Functions, I do not understand the position 100C in the wal
> filename and how the result could be C001?
> 
> Any idea?
> 
> Regards,
> 
> Fabrice
> 
> pg_split_walfile_name is useful to compute a LSN from a file offset and WAL
> file name, for example:
> 
> postgres=# \set file_name '000000010000000100C000AB'
> postgres=# \set offset 256
> postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset AS lsn
>   FROM pg_split_walfile_name(:'file_name') pd,
>        pg_show_all_settings() ps
>   WHERE ps.name = 'wal_segment_size';
>       lsn
> ---------------
>  C001/AB000100
> (1 row)

I think your problem is that you are converting the WAL segment/offset
to a number of bytes by multiplying by wal_segment_size, and then you
are casting it to an LSN, which assumes time line/segment number;  try
this:

	SELECT to_hex((pd.segment_number * ps.setting::int + :offset)::bigint) AS lsn
	  FROM pg_split_walfile_name(:'file_name') pd,
	       pg_show_all_settings() ps
	  WHERE ps.name = 'wal_segment_size';
	     lsn
	--------------
	 c001ab000100


-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Do not let urgent matters crowd out time for investment in the future.