Re: pg_xlogfile_name_offset() et al and recovery

On 7 July 2016 at 08:26, Michael Paquier <michael(dot)paquier(at)gmail(dot)com> wrote:

> On Thu, Jul 7, 2016 at 3:59 PM, Amit Langote
> <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> > While reading the thread "BUG #14230: Wrong timeline returned by
> > pg_stop_backup on a standby", I came to know that ThisTimelineId is
> > invalid on standby. And because pg_xlogfile_name_offset() uses the same
> > to compute its result, it makes sense to prevent it from being used on a
> > standby.
>
> To be honest, I have always found that this restriction was hard to
> justify on a function that basically performs a static calculation.

I know its annoying behaviour, but this is not an immutable function i.e.
not a static calculation.
The timeline is not so much invalid as variable over time.

The behaviour of that function is defined in backbranches, so I suggest we
should not alter that now.

What we can do is have another function that makes it clearer that the
answer is variable.
pg_xlogfile_name_offset(offset, timelineid)
always succeeds on standby but needs to be told what timelineid to use

then we have another function
pg_last_xact_replay_timeline() that allows you to find out the current
timeline

The actual problem here is somewhat more convoluted, because we would like
to know the timeline of the basebackup at start and stop. That information
is not easily available from the backup label data returned by
pg_stop_backup().
We would like to do something like this...

select pg_xlogfile_name_offset(l.lsn, l.tli) from pg_stop_backup(false) l;

So I suggest we add another column to the output of pg_stop_backup() to
return the tli value directly.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services