Re: pg_xlogfile_name_offset() et al and recovery

On 8 July 2016 at 00:43, Michael Paquier <michael(dot)paquier(at)gmail(dot)com> wrote:

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

But its been like that for some time; by agreement we don't try to change
the past, just improve the future. I wish it had not been done that way,
but it was. Some things are backpatchable, others not. If you get others to
agree, I'd be fine with it, I'm just trying to explain what I understand to
be the limits of what can be changed.

What I do agree with is the need to fix something in 9.6 here. Releasing a
new feature with an obvious flaw helps nobody, so we must have a function
of some kind that allows you to find the filename for the start and stop
points of backup from a standby.

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

Agreed. I prefer your name for that function.

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

Cool

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

I'm happy to do that.

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