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

From: Robert Haas <[email-masked]>
Date: Fri, 8 Apr 2022 09:57:53 -0400

On Fri, Apr 8, 2022 at 9:31 AM Bharath Rupireddy
<[email-masked]> wrote:
> Fundamental question - should the pg_walfile_{name, name_offset} check
> whether the file with the computed WAL file name exists on the server
> right now or ever existed earlier? Right now, they don't do that, see
> [1].

I don't think that checking whether the file exists is the right
approach. However, I do think that it's important to be precise about
which TLI is going to be used. I think it would be reasonable to
redefine this function (on both the primary and the standby) so that
the TLI that is used is the one that was in effect at the time record
at the given LSN was either written or replayed. Then, you could
potentially use this function to figure out whether you still have the
WAL files that are needed to replay up to some previous point in the
WAL stream. However, what about the segments where we switched from
one TLI to the next in the middle of the segment? There, you probably
need both the old and the new segments, or maybe if you're trying to
stream them you only need the new one because we have some weird
special case that will send the segment from the new timeline when the
segment from the old timeline is requested. So you couldn't just call
this function on one LSN per segment and call it good, and it wouldn't
necessarily be the case that the filenames you got back were exactly
the ones you needed.

So I'm not entirely sure this proposal is good enough, but it at least
would have the advantage of meaning that the filename you get back is
one that existed at some point in time and somebody used it for
something.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

---

From: Bharath Rupireddy <[email-masked]>
Date: Sat, 9 Apr 2022 19:00:30 +0530

> one that existed at some point in time and somebody used it for

Using insert tli when not in recovery and using tli of the last WAL
replayed record in crash/archive/standby recovery, seems a reasonable
choice to me. I've also added a note in the docs.

Attaching v2 with the above change. Please review it further.

Regards,
Bharath Rupireddy.

---

From: Andrey Borodin <[email-masked]>
Date: Sat, 9 Apr 2022 21:25:01 +0500

> On 9 Apr 2022, at 18:30, Bharath Rupireddy <[email-masked]> wrote:
> 
> choice to me. 

Please excuse me if I'm not attentive enough. I've read this thread. And I could not find what is the problem that you are solving. What is the purpose of the WAL file name you want to obtain?

pg_walfile_name() - is a formatting function. With TLI as an hidden argument. If we want it to work on Standby we should just convert it to pure formatting function without access the the DB state, pass TLI as an argument.
Making implicit TLI computation with certain expectations is not a good idea IMV.

pg_walfile_name() could just read .history file, determine which TLI contains given LSN and format the name. And still there's a tricky segments during TLI switch.

Either way we can rename the function to pg_walfile_name_as_if_on_timeline_of_last_wal_replayed().

Thanks!

Best regards, Andrey Borodin.