Re: Show various offset arrays for heap WAL records - Mailing list pgsql-hackers

From Peter Geoghegan
Subject Re: Show various offset arrays for heap WAL records
Date
Msg-id CAH2-Wz=acGKoP8cZ+6Af2inoai0N5cZKCY13DaqXCwQNupK8qg@mail.gmail.com
Whole thread Raw
In response to Re: Show various offset arrays for heap WAL records  (Peter Geoghegan <pg@bowt.ie>)
Responses Re: Show various offset arrays for heap WAL records
List pgsql-hackers
On Tue, Jan 31, 2023 at 1:52 PM Peter Geoghegan <pg@bowt.ie> wrote:
> > I would also like to see functions like XLogRecGetBlockRefInfo() pass
> > something more useful than a stringinfo buffer so that we could easily
> > extract out the relfilenode in pgwalinspect.
>
> That does seem particularly important. It's a pain to do this from
> SQL. In general I'm okay with focussing on pg_walinspect over
> pg_waldump, since it'll become more important over time. Obviously
> pg_waldump needs to still work, but I think it's okay to care less
> about pg_waldump usability.

I just realized why you mentioned XLogRecGetBlockRefInfo() -- it
probably shouldn't even be used by pg_walinspect at all (just by
pg_waldump). Using something like XLogRecGetBlockRefInfo() within
pg_walinspect misses out on the opportunity to output information in a
more descriptive tuple format, with real data types. It's not just the
relfilenode, either -- it's the block numbers themselves. And the fork
number.

In other words, I suspect that this is out of scope for this patch,
strictly speaking. We simply shouldn't be using
XLogRecGetBlockRefInfo() in pg_walinspect in the first place. Rather,
pg_walinspect should be calling some other function that ultimately
allows the user to work with (say) an array of int8 from SQL for the
block numbers. There is no great reason not to, AFAICT, since this
information is completely generic -- it's not like the rmgr-specific
output from GetRmgr(), where fine grained type information is just a
nice-to-have, with usability issues of its own (on account of the
details being record type specific).

I've been managing this problem within my own custom pg_walinspect
queries by using my own custom ICU collation. I use ICU's natural sort
order to order based on block_ref, or based on a substring()
expression that extracts something interesting from block_ref, such as
relfilenode. You can create a custom collation for this like so, per
the docs:

CREATE COLLATION IF NOT EXISTS numeric (provider = icu, locale =
'en-u-kn-true');

Obviously this hack of mine works, but hardly anybody else would be
willing to take the time to figure something like this out. Plus it's
error prone when it doesn't really have to be. And it suggests that
the block_ref field isn't record type generic -- that's sort of
misleading IMV.

-- 
Peter Geoghegan



pgsql-hackers by date:

Previous
From: Andres Freund
Date:
Subject: Re: BUG: Postgres 14 + vacuum_defer_cleanup_age + FOR UPDATE + UPDATE
Next
From: Peter Smith
Date:
Subject: Re: Logical replication timeout problem