Add pg_walinspect function with block info columns

From: Melanie Plageman <melanieplageman(at)gmail(dot)com>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Cc: Peter Geoghegan <pg(at)bowt(dot)ie>, "bharath(dot)rupireddyforpostgres(at)gmail(dot)com" <bharath(dot)rupireddyforpostgres(at)gmail(dot)com>
Subject: Add pg_walinspect function with block info columns
Date: 2023-03-01 17:51:16
Message-ID: CAAKRu_bORebdZmcV8V4cZBzU8M_C6tDDdbiPhCZ6i-iuSXW9TA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

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().

- Melanie

[1] https://www.postgresql.org/message-id/CAH2-Wz%3DacGKoP8cZ%2B6Af2inoai0N5cZKCY13DaqXCwQNupK8qg%40mail.gmail.com

Attachment Content-Type Size
v1-0001-Add-extended-block-info-function-to-pg_walinspect.patch text/x-patch 5.8 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Melih Mutlu 2023-03-01 18:09:44 Re: Allow logical replication to copy tables in binary format
Previous Message Jehan-Guillaume de Rorthais 2023-03-01 17:48:40 Re: Memory leak from ExecutorState context?