Re: Add pg_walinspect function with block info columns

On Wed, Mar 1, 2023 at 12:51 PM Melanie Plageman
<melanieplageman(at)gmail(dot)com> wrote:
> When using pg_walinspect, and calling functions like
> pg_get_wal_records_info(), I often wish that the various information in
> the block_ref column was separated out into columns so that I could
> easily access them and pass them to various other functions to add
> information -- like getting the relname from pg_class like this:
>
> SELECT n.nspname, c.relname, wal_info.*
> FROM pg_get_wal_records_extended_info(:start_lsn, :end_lsn) wal_info
> JOIN pg_class c
> ON wal_info.relfilenode = pg_relation_filenode(c.oid) AND
> wal_info.reldatabase IN (0, (SELECT oid FROM pg_database
> WHERE datname = current_database()))
> JOIN pg_namespace n ON n.oid = c.relnamespace;
>
>
> This has been mentioned in [1] amongst other places.
>
> So, attached is a patch with pg_get_wal_records_extended_info(). I
> suspect the name is not very good. Also, it is nearly a direct copy of
> pg_get_wal_fpi_infos() except for the helper called to fill in the
> tuplestore, so it might be worth doing something about that.
>
> However, I am mainly looking for feedback about whether or not others
> would find this useful, and, if so, what columns they would like to see
> in the returned tuplestore.
>
> Note that I didn't include the cumulative fpi_len for all the pages
> since pg_get_wal_fpi_info() now exists. I noticed that
> pg_get_wal_fpi_info() doesn't list compression information (which is in
> the block_ref column of pg_get_wal_records_info()). I don't know if this
> is worth including in my proposed function
> pg_get_wal_records_extended_info().

Thinking about this more, it could make sense to have a function which
gives you this extended block information and has a parameter like
with_fpi which would include the information returned by
pg_get_wal_fpi_info(). It might be nice to have it still include the
information about the record itself as well.

I don't know if it would be instead of pg_get_wal_fpi_info(), though.

The way I would use this is when I want to see the record level
information but with some additional information aggregated across the
relevant blocks. For example, I could group by the record information
and relfilenode and using the query in my example above, see all the
information for the record along with the relname (when possible).

- Melanie