Re: Combine pg_walinspect till_end_of_wal functions with others

On Mon, Mar 6, 2023 at 8:52 PM Matthias van de Meent
<boekewurm+postgres(at)gmail(dot)com> wrote:
>
> On Mon, 6 Mar 2023 at 16:06, Bharath Rupireddy
> <bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
> > If we try to make these functions figure out the oldest WAl file and
> > start from there, then it'll unnecessarily complicate the APIs and
> > functions. If we still think we need a better function for the users
> > to figure out the oldest WAL file, perhaps, add a SQL-only
> > view/function to pg_walinspect that returns "select name from
> > pg_ls_waldir() order by name limit 1;", but honestly, that's so
> > trivial.
>
> That "order by name limit 1" has subtle bugs when you're working on a
> system that has experienced timeline switches. It is entirely possible
> that the first file (as sorted by the default collation) is not the
> first record you can inspect, or even in your timeline's history.

Hm. Note that pg_walinspect currently searches WAL on insertion
timeline; it doesn't care about the older timelines. The idea of
making it look at WAL on an older timeline was discussed, but for the
sake of simplicity we kept the functions simple. If needed, I can try
adding the timeline as input parameters to all the functions (with
default -1 meaning current insertion timeline; if specified, look for
WAL on that timeline).

Are you saying that a pg_walinspect function that traverses the pg_wal
directory and figures out the old valid WAL on a given timeline is
still useful? Or make the functions look for older WAL if start_lsn is
given as NULL or invalid?

--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com