Re: pg_xlogfile_name_offset() et al and recovery

On Thu, Jul 7, 2016 at 6:26 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> The behaviour of that function is defined in backbranches, so I suggest we
> should not alter that now.

Hm..

> 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

This has clearly value, I have wanted to override the timeline number
a couple of times now. I have always done so with a custom function
and saw many people doing it at application level, but it is a weird
design to have the 1-argument version fail for a node in recovery, and
the 2-argument version succeed. I'd rather have everything consistent
to be honest, with the same function name, and the behavior clearly
documented.

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

It would be good to have an equivalent pg_current_xlog_timeline as
well that can run on nodes not in recovery. I agree that having a
separate function makes more sense as its equivalents for WAL
positions.

> 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.

This would be useful as well, that's less custom-parsing logic for
applications based on the segment name in backup_label.

Note that I am not personally planning to write a patch for all that,
but any people are welcome to pick up this task!
--
Michael