Re: why pg_walfile_name() cannot be executed during recovery?

On Fri, Apr 2, 2021 at 5:52 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Fri, Apr 2, 2021 at 4:23 AM SATYANARAYANA NARLAPURAM
> <satyanarlapuram(at)gmail(dot)com> wrote:
> > Why pg_walfile_name() can't be executed under recovery?
>
> I believe the issue is that the backend executing the function might
> not have an accurate idea about which TLI to use. But I don't
> understand why we can't find some solution to that problem.
>
> > What is the best way for me to get the current timeline and/or the file being recovering on the standby using a postgres query? I know I can get it via process title but don't want to go that route.
>
> pg_stat_wal_receiver has LSN and TLI information, but probably won't
> help except when WAL receiver is actually active.
> pg_last_wal_receive_lsn() and pg_last_wal_replay_lsn() will give the
> LSN at any point during recovery, but not the TLI. We might have some
> gaps in this area...

I spent some time today to allow pg_walfile_{name, name_offset} run in
recovery. Timeline ID is computed while in recovery as follows - WAL
receiver's last received and flushed WAL record's TLI if it's
streaming, otherwise the last replayed WAL record's TLI. This way,
these functions can be used on standby or PITR server or even in crash
recovery if the server opens up for read-only connections.

Please have a look at the attached patch.

If the approach looks okay, I can add notes in the documentation.

Regards,
Bharath Rupireddy.