On Wed, Mar 1, 2023 at 12:51 PM Melanie Plageman <melanieplage...@gmail.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