Re: Fetching timeline during recovery

On Fri, 13 Dec 2019 16:12:55 +0900
Michael Paquier <michael@paquier.xyz> wrote:

> On Wed, Dec 11, 2019 at 10:45:25AM -0500, Stephen Frost wrote:
> > I'm confused- wouldn't the above approach be a function that's returning
> > only one row, if you had a bunch of columns and then had NULL values for
> > those cases that didn't apply..?  Or, if you were thinking about the SRF
> > approach that you suggested, you could use a WHERE clause to make it
> > only one row...  Though I can see how it's nicer to just have one row in
> > some cases which is why I was suggesting the "bunch of columns"
> > approach.  
> 
> Oh, sorry.  I see the confusion now and that's my fault.  In
> https://www.postgresql.org/message-id/20191211052002.GK72921@paquier.xyz
> 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

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

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.

> 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


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


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

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

Fifth one is more consistent with what we already have.

Thanks again.

Regards,