Re: Fetching timeline during recovery

On Fri, 20 Dec 2019 13:41:25 +0900 (JST)
Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com> wrote:

> At Fri, 20 Dec 2019 00:35:19 +0100, Jehan-Guillaume de Rorthais
> <jgdr(at)dalibo(dot)com> wrote in
> > On Fri, 13 Dec 2019 16:12:55 +0900
> > Michael Paquier <michael(at)paquier(dot)xyz> wrote:
>
> The first one;
>
> > > I mentioned a SRF function which takes an input argument, but that
> > > makes no sense. What I would prefer having is just having one
> > > function, returning one row (LSN, TLI), using in input one argument to
> > > extract the WAL information the caller wants with five possible cases
> > > (write, insert, flush, receive, replay).
> >
> > It looks odd when we look at other five existing functions of the same
> > family but without the tli. And this user interaction with admin function
> > is quite different of what we are used to with other admin funcs. But
> > mostly, when I think of such function, I keep thinking this parameter
> > should be a WHERE clause after a SRF function.
> >
> > -1
>
> It is realted to the third one, it may be annoying that the case names
> cannot have an aid of psql-completion..

indeed.

> The second one;
>
> > > Then, what you are referring to is one function which returns all
> > > (LSN,TLI) for the five cases (write, insert, etc.), so it would return
> > > one row with 10 columns, with NULL mapping to the values which have no
> > > meaning (like replay on a primary).
> >
> > This would looks like some other pg_stat_* functions, eg.
> > pg_stat_get_archiver. I'm OK with this. This could even be turned as a
> > catalog view.
> >
> > However, what's the point of gathering all the values eg from a production
> > cluster? Is it really useful to compare current/insert/flush LSN from wal
> > writer?
>
> There is a period where pg_controldata shows the previous TLI after
> promotion. It's useful if we can read the up-to-date TLI from live
> standby. I thought that this project is for that case..

I was not asking about the usefulness of LSN+TLI itself.
I was wondering about the usecase of gathering all 6 cols current+tli,
insert+tli and flush+tli from a production/primary cluster.

[...]
> > As a fourth possibility, as I badly explained my last implementation
> > details, I still hope we can keep it in the loop here. Just overload
> > existing functions with ones that takes a boolean as parameter and add the
> > TLI as a second field, eg.:
> >
> > Name | Result type | Argument data types
> > -------------------+--------------+-------------------------------------------
> > pg_current_wal_lsn | pg_lsn |
> > pg_current_wal_lsn | SETOF record | with_tli bool, OUT lsn pg_lsn, OUT tli
> > int
>
> I prefer this one, in the sense of similarity with existing functions.

thanks

> > And the fifth one, implementing brand new functions:
> >
> > pg_current_wal_lsn_tli
> > pg_current_wal_insert_lsn_tli
> > pg_current_wal_flush_lsn_tli
> > pg_last_wal_receive_lsn_tli
> > pg_last_wal_replay_lsn_tli
>
> Mmmmm.... We should remove exiting ones instead? (Of couse we don't,
> though.)

Yes, that would be great but sadly, it would introduce a regression on various
tools relying on them. At least, the one doing "select *" or most
probably "select func()".

But anyway, adding 5 funcs is not a big deal neither. Too bad they are so close
to existing ones though.

> > > I actually prefer the first one, and you mentioned the second. But
> > > there could be a point in doing the third one. An advantage of the
> > > second and third ones is that you may be able to get a consistent view
> > > of all the data, but it means holding locks to look at the values a
> > > bit longer. Let's see what others think.
> >
> > I like the fourth one, but I was not able to return only one field if given
> > parameter is false or NULL. Giving false as argument to these funcs has no
> > meaning compared to the original one without arg. I end up with this
> > solution because I was worried about adding five more funcs really close to
> > some existing one.
>
> Right. It is a restriction of polymorphic functions. It is in the same
> relation with pg_stop_backup() and pg_stop_backup(true).

indeed.