Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1um8NM-00ARIk-Th for pgsql-admin@arkaria.postgresql.org; Wed, 13 Aug 2025 10:06:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1um8NL-00EEuv-Ac for pgsql-admin@arkaria.postgresql.org; Wed, 13 Aug 2025 10:06:31 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1um8NK-00EEun-U6 for pgsql-admin@lists.postgresql.org; Wed, 13 Aug 2025 10:06:31 +0000 Received: from mail-oi1-x22f.google.com ([2607:f8b0:4864:20::22f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1um8NI-000Vx9-1s for pgsql-admin@lists.postgresql.org; Wed, 13 Aug 2025 10:06:30 +0000 Received: by mail-oi1-x22f.google.com with SMTP id 5614622812f47-43572103e1cso111550b6e.0 for ; Wed, 13 Aug 2025 03:06:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1755079586; x=1755684386; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=81O1P3stmN3fIxuhuaMgJD4HFPtmPs6X3kormKIeRAU=; b=BWTglluURnSF7/Ni+YC3QD3ASUH0Ep9rR6b2wASUXvZS5Rhfmq+tey7HAkXa0q8jpH 8VbQ9g0eueB9nmiBAoUQC7ye+rKMIohisqFucwn09eFi4mcCEjF+fRayOl0AVqaRXqZO cCxGML5stzMklodJPt6b2lK0JlTAmQrjw5q+lMJd65+mY6cPKW4uqLDIgwQipwBAXd1H mrv0AizAS3Fi9OloLbnXc3THnLN8s4lOCRcRuOUA6uePt3GiX1hQjSTBqoxs0h2+ueCl R3PEsr9xG8OTi0S7A+d+4bzkTnlCmS1dcKdBjyVLqC4VuVbVGX9g8U5FVuF4B+p8o+tK Ogag== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1755079586; x=1755684386; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=81O1P3stmN3fIxuhuaMgJD4HFPtmPs6X3kormKIeRAU=; b=i/UYtsHr2IUuFF9UWA30DNRdmYLBWH6npWWPIdqNGqc5fBs07JM9NureRiZIQ47KFq th6BUJLYk6FLV/8Rs5FC4ZsouZc0CG3Yi4zD19TX4ABAK84WCWXAepeDvS/LhMWHoZlV QHsVzs0F2IWGCX7YNWAEOW0zlEz20hxunP7Uf251mxWwl7Mna6sSWJDmr7qqWKMM+i40 bXjsbCS+B1UvY7AfPM9QzruHohi64Tz11lTNSUimfTDjqk19wwu7K2HteK4yjWpBRVA0 oiMPXQZ4FL/eww5YSZbY1Xfg3tsACHYChQFfzdHGvrRFB+s3/SlzwH6o4EKezsA8QFx2 MQsA== X-Gm-Message-State: AOJu0YzD8fVDH4n4nUfj75Xb7KcMw5D9jZd4NsENLhMVm4ZMAhwDUDfo qcawtsyD7l5zlZP7tWkioQ2IkjJMLWq28hsJeochxbEtMie2mmyqLkxEqxLOZLpkzAekwahDhXy LS9vnf1TYyEDa7LRxeUUhCeu3hnZZXL4= X-Gm-Gg: ASbGncvOCcqOarIdsyg/r27dg2Xtq1BPU1I2BH/ti2Fkc5sRM3LXz9Dj6oZBtNUsCHR 7N0QqpI5usNVjS++ilh0P55icaSPtY1Cd0uq3EuVzN8MsAhDT7WUBoJNN4przxPs91od5eznQ4X 8AlsB4ahBkbQ0z1rrwC8QzkYIFm3qapOofEh2V6qcvq/xfXLi8yPqdCBtEoG3rJtLnoob72kV5t a2uOw== X-Google-Smtp-Source: AGHT+IHdJvLLMtujgqEWTa7ErlirExq9ZNCVO8SOA/TFfFddbKGsy5M1rXTp7caxXtCqOCQCOGsjG2mNcHsMoqqQpYU= X-Received: by 2002:a05:6808:2223:b0:404:c263:9bdc with SMTP id 5614622812f47-435d425ef03mr632741b6e.7.1755079586122; Wed, 13 Aug 2025 03:06:26 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Fabrice Chapuis Date: Wed, 13 Aug 2025 12:06:15 +0200 X-Gm-Features: Ac12FXwQrgsHsdWHEqvuvMBpcqC3kgI5r8l1I0vc2tTqJCVfoOjT6ME08B_HFJA Message-ID: Subject: Re: pg_split_walfile_name To: Bruce Momjian Cc: pgsql-admin@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000004891b0063c3c4f6c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000004891b0063c3c4f6c Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable select * FROM pg_split_walfile_name(:'file_name') pd; +-[ RECORD 1 ]---+----------+ | segment_number | 12583339 | | timeline_id | 1 | +----------------+----------+ Yes, number of sgements 12583339 correspond to C001AB in hexdecimal thanks for answering Fabrice On Tue, Aug 12, 2025 at 7:34=E2=80=AFPM Bruce Momjian wr= ote: > 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=3D# \set file_name '000000010000000100C000AB' > > postgres=3D# \set offset 256 > > postgres=3D# 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 =3D '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 =3D 'wal_segment_size'; > lsn > -------------- > c001ab000100 > > > -- > Bruce Momjian https://momjian.us > EDB https://enterprisedb.com > > Do not let urgent matters crowd out time for investment in the future. > --0000000000004891b0063c3c4f6c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
select * FROM pg_split_walfile_name(:'file_name') pd;
+-[ RECORD 1 ]---+----------+
| segment_number | 125= 83339 |
| timeline_id= =C2=A0 =C2=A0 | 1=C2=A0 =C2=A0 =C2=A0 =C2=A0 |
+----------------+----------+
Yes, number of sgements 12583339 correspond to=C2=A0=C2=A0C001AB in= hexdecimal

thanks for answering
=

Fabrice

On Tue, = Aug 12, 2025 at 7:34=E2=80=AFPM Bruce Momjian <bruce@momjian.us> wrote:
On Thu, Jul 31, 2025 at 10:08:25AM +0200, Fabric= e Chapuis wrote:
> Hi,
>
> In the documentation,=C2=A0PostgreSQL: Documentation: 17: 9.28.=C2=A0S= ystem
> Administration Functions,=C2=A0I do not understand the position 100C i= n the wal
> filename and how the result could be C001?
>
> Any idea?
>
> Regards,
>
> Fabrice
>
> pg_split_walfile_name=C2=A0is useful to compute a=C2=A0LSN=C2=A0from a= file offset and WAL
> file name, for example:
>
> postgres=3D# \set file_name '000000010000000100C000AB'
> postgres=3D# \set offset 256
> postgres=3D# SELECT '0/0'::pg_lsn + pd.segment_number * ps.set= ting::int + :offset AS lsn
>=C2=A0 =C2=A0FROM pg_split_walfile_name(:'file_name') pd,
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 pg_show_all_settings() ps
>=C2=A0 =C2=A0WHERE ps.name =3D 'wal_segment_size';
>=C2=A0 =C2=A0 =C2=A0 =C2=A0lsn
> ---------------
>=C2=A0 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;=C2=A0 try=
this:

=C2=A0 =C2=A0 =C2=A0 =C2=A0 SELECT to_hex((pd.segment_number * ps.setting::= int + :offset)::bigint) AS lsn
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 FROM pg_split_walfile_name(:'file_na= me') pd,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0pg_show_all_settings= () ps
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 WHERE ps.name =3D 'wal_segment_size'; =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0lsn
=C2=A0 =C2=A0 =C2=A0 =C2=A0 --------------
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0c001ab000100


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

=C2=A0 Do not let urgent matters crowd out time for investment in the futur= e.
--0000000000004891b0063c3c4f6c--