Re: pg_walinspect - a new extension to get raw WAL data and WAL stats

On Wed, Mar 16, 2022 at 8:49 PM Ashutosh Sharma <ashu.coek88@gmail.com> wrote:
>
> I can see that the pg_get_wal_records_info function shows the details
> of the WAL record whose existence is beyond the user specified
> stop/end lsn pointer. See below:
>
> ashu@postgres=# select * from pg_get_wal_records_info('0/01000028',
> '0/01000029');
> -[ RECORD 1
]----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> start_lsn        | 0/1000028
> end_lsn          | 0/100009F
> prev_lsn         | 0/0
> xid              | 0
> resource_manager | XLOG
> record_length    | 114
> fpi_length       | 0
> description      | CHECKPOINT_SHUTDOWN redo 0/1000028; tli 1; prev tli
> 1; fpw true; xid 0:3; oid 10000; multi 1; offset 0; oldest xid 3 in DB
> 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0;
> oldest running xid 0; shutdown
> block_ref        |
> data_length      | 88
> data             |
>
\x28000001000000000100000001000000010000000000000003000000000000001027000001000000000000000300000001000000010000000100000072550000a5c4316200000000000000000000000000000000ff7f0000
>
> In this case, the end lsn pointer specified by the user is
> '0/01000029'. There is only one WAL record which starts before this
> specified end lsn pointer whose start pointer is at 01000028, but that
> WAL record ends at 0/100009F which is way beyond the specified end
> lsn. So, how come we are able to display the complete WAL record info?
> AFAIU, end lsn is the lsn pointer where you need to stop reading the
> WAL data. If that is true, then there exists no valid WAL record
> between the start and end lsn in this particular case.

Thanks Ashutosh, it's an edge case and I don't think we would want to
show a WAL record that ends at LSN after the user specified end-lsn
which doesn't look good. I fixed it in the v11 patch set. Now, the
pg_get_wal_records_info will show records only upto user specified
end_lsn, it doesn't show the last record which starts at LSN < end_lsn
but ends at LSN > end_lsn, see [1].

Please review the v11 patch set further.

[1]
postgres=# select start_lsn, end_lsn, prev_lsn from
pg_get_wal_records_info('0/01000028', '0/01000029');
 start_lsn | end_lsn | prev_lsn
-----------+---------+----------
(0 rows)

postgres=# select start_lsn, end_lsn, prev_lsn from
pg_get_wal_records_info('0/01000028', '0/100009F');
 start_lsn |  end_lsn  | prev_lsn
-----------+-----------+----------
 0/1000028 | 0/100009F | 0/0
(1 row)

postgres=# select start_lsn, end_lsn, prev_lsn from
pg_get_wal_records_info('0/01000028', '0/10000A0');
 start_lsn |  end_lsn  | prev_lsn
-----------+-----------+----------
 0/1000028 | 0/100009F | 0/0
(1 row)

postgres=# select start_lsn, end_lsn, prev_lsn from
pg_get_wal_records_info('0/01000028', '0/0100009E');
 start_lsn | end_lsn | prev_lsn
-----------+---------+----------
(0 rows)

Regards,
Bharath Rupireddy.