Re: Add pg_walinspect function with block info columns

On Tue, Mar 14, 2023 at 5:34 PM Melanie Plageman
<melanieplageman(at)gmail(dot)com> wrote:
> On Tue, Mar 14, 2023 at 6:57 PM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> > Why doesn't it already work like this? Why do we need a separate
> > pg_get_wal_block_info() function at all?
>
> 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 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.

> But, perhaps you are suggesting a parameter to pg_get_wal_records_info()
> like "with_block_info" or something, which produces the full
> denormalized block + record output?

I was thinking of something like that, yes -- though it wouldn't
necessarily have to be the *full* denormalized block_ref info, the FPI
itself, etc. Just the more useful stuff.

It occurs to me that my concern about the information that
pg_get_wal_records_info() lacks could be restated as a concern about
what pg_get_wal_block_info() lacks: pg_get_wal_block_info() fails to
show basic information about the WAL record whose blocks it reports
on, even though it could easily show all of the
pg_get_wal_records_info() info once per block (barring block_ref). So
addressing my concern by adjusting pg_get_wal_block_info() might be
the best approach. I'd probably be happy with that -- I'd likely just
stop using pg_get_wal_records_info() completely under this scheme.

Overall, I'm concerned that we may have missed the opportunity to make
simple things easier. Again, wanting to see (say) all of the PRUNE
records and VACUUM records with an "order by relfilenode,
block_number, lsn" seems likely to be a very common requirement to me.
It's exactly the kind of thing that you'd expect an SQL interface to
make easy.

--
Peter Geoghegan