Re: Fetching timeline during recovery - Mailing list pgsql-hackers

At Fri, 20 Dec 2019 00:35:19 +0100, Jehan-Guillaume de Rorthais <jgdr@dalibo.com> wrote in 
> On Fri, 13 Dec 2019 16:12:55 +0900
> Michael Paquier <michael@paquier.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..


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

> It's easier to answer from a standby point of view as the lag between received
> and replayed might be interesting to report in various situations.


The third one;

> > And on top of that we have a third possibility: one SRF function
> > returning 5 rows with three attributes (mode, LSN, TLI), where mode
> > corresponds to one value in the set {write, insert, etc.}.
> 
> I prefer the second one. Just select the field(s) you need, no need WHERE
> clause, similar to some other stats function.
> 
> -1

It might be clean in a sense, but I don't come up with the case where
the format is useful..

Anyway as the same with the first one, the case names (write, insert,
flush, receive, replay) comes from two different machineries and
showing them in a row could be confusing.


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

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

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

> Fifth one is more consistent with what we already have.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center