public inbox for [email protected]
help / color / mirror / Atom feedpg_split_walfile_name
3+ messages / 2 participants
[nested] [flat]
* pg_split_walfile_name
@ 2025-07-31 08:08 Fabrice Chapuis <[email protected]>
2025-08-12 17:34 ` Re: pg_split_walfile_name Bruce Momjian <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Fabrice Chapuis @ 2025-07-31 08:08 UTC (permalink / raw)
To: [email protected]
Hi,
In the documentation, PostgreSQL: Documentation: 17: 9.28. System
Administration Functions
<https://www.postgresql.org/docs/current/functions-admin.html;, 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)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pg_split_walfile_name
2025-07-31 08:08 pg_split_walfile_name Fabrice Chapuis <[email protected]>
@ 2025-08-12 17:34 ` Bruce Momjian <[email protected]>
2025-08-13 10:06 ` Re: pg_split_walfile_name Fabrice Chapuis <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Bruce Momjian @ 2025-08-12 17:34 UTC (permalink / raw)
To: Fabrice Chapuis <[email protected]>; +Cc: [email protected]
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 <[email protected]> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pg_split_walfile_name
2025-07-31 08:08 pg_split_walfile_name Fabrice Chapuis <[email protected]>
2025-08-12 17:34 ` Re: pg_split_walfile_name Bruce Momjian <[email protected]>
@ 2025-08-13 10:06 ` Fabrice Chapuis <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Fabrice Chapuis @ 2025-08-13 10:06 UTC (permalink / raw)
To: Bruce Momjian <[email protected]>; +Cc: [email protected]
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 PM Bruce Momjian <[email protected]> wrote:
> 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 <[email protected]> https://momjian.us
> EDB https://enterprisedb.com
>
> Do not let urgent matters crowd out time for investment in the future.
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-08-13 10:06 UTC | newest]
Thread overview: 3+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-07-31 08:08 pg_split_walfile_name Fabrice Chapuis <[email protected]>
2025-08-12 17:34 ` Bruce Momjian <[email protected]>
2025-08-13 10:06 ` Fabrice Chapuis <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox