Re: Add pg_walinspect function with block info columns

On Tue, Mar 14, 2023 at 06:50:15PM -0700, Peter Geoghegan wrote:
> On Tue, Mar 14, 2023 at 5:34 PM Melanie Plageman
> <melanieplageman(at)gmail(dot)com> wrote:
>> Well, I think if you only care about the WAL record-level information
>> and not the block-level information, having the WAL record information
>> denormalized like that with all the block information would be a
>> nuisance.
>
> I generally care about both. When I want to look at things at the
> pg_get_wal_records_info() level (as opposed to a summary), the
> block_ref information is *always* of primary importance. I don't want
> to have to write my own bug-prone parser for block_ref, but why should
> the only alternative be joining against pg_get_wal_block_info()? The
> information that I'm interested in is "close at hand" to
> pg_get_wal_records_info() already.
>

I am not sure to get the concern here. As long as one is smart enough
with SQL, there is no need to perform a double scan of the contents of
pg_wal with a large scan on the start LSN. If one wishes to only
extract some block for a given record type, or for a filter of your
choice, it is possible to use a LATERAL on pg_get_wal_block_info(),
say:
SELECT r.start_lsn, b.blockid
FROM pg_get_wal_records_info('0/01000028', '0/1911AA8') AS r,
LATERAL pg_get_wal_block_info(start_lsn, end_lsn) as b
WHERE r.resource_manager = 'Heap2';

This will extract the block information that you'd want for a given
record type.

> I understand that in the general case there might be quite a few
> blocks associated with a WAL record. For complicated cases,
> pg_get_wal_block_info() does make sense. However, the vast majority of
> individual WAL records (and possibly most WAL record types) are
> related to one block only. One block that is generally from the
> relation's main fork.

Sure, though there may be more complicated scenarios, like custom
RMGRs. At the end it comes to how much normalization should be
applied to the data extracted. FWIW, I think that the current
interface is a pretty good balance in usability.
--
Michael