Horton

Re: Add pg_walinspect function with block info columns

Peter Geoghegan <pg@bowt.ie>

View thread View on postgr.es
From: Peter Geoghegan <pg@bowt.ie>
To: Melanie Plageman <melanieplageman@gmail.com>
Cc: Matthias van de Meent <boekewurm+postgres@gmail.com>, Michael Paquier <michael@paquier.xyz>, Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>, Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2023-03-14T22:56:50Z
Lists: pgsql-hackers 
On Tue, Mar 14, 2023 at 3:34 PM Melanie Plageman
<melanieplageman@gmail.com> wrote:
> After patching master to add in the columns from
> pg_get_wal_records_info() which are not returned by
> pg_get_wal_block_info() (except block_ref column of course), this query:
>
>   SELECT COUNT(*) FROM pg_get_wal_block_info(:start_lsn, :end_lsn);
>
> took 467 ms.
>
> Perhaps this difference isn't important, but I found it noticeable.

This seems weird to me too. It's not so much the performance overhead
that bothers me (though that's not great either). It seems *illogical*
to me. The query you end up writing must do two passes over the WAL
records, but its structure almost suggests that it's necessary to do
two separate passes over distinct "streams".

Why doesn't it already work like this? Why do we need a separate
pg_get_wal_block_info() function at all?

-- 
Peter Geoghegan

Commits

  1. Add show_data option to pg_get_wal_block_info.

  2. Show record information in pg_get_wal_block_info.

  3. Fix recent pg_walinspect fpi_length bug.

  4. Improve a few things in pg_walinspect

  5. Rework design of functions in pg_walinspect

  6. pg_walinspect: pg_get_wal_fpi_info() -> pg_get_wal_block_info()