Re: Make unlogged table resets detectable

On Fri, Jun 4, 2021 at 8:41 PM Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> Stepping back, maybe unlogged tables are the wrong level to solve this
> problem. We could just have a "crash counter" in pg_control that would
> be incremented every time a crash happened (and all unlogged tables are
> reset). It might be a number or maybe the LSN of the startup checkpoint
> after the most recent crash.
>
> A SQL function could read the value. Perhaps we'd also have a SQL
> function to reset it, but I don't see a use case for it.
>
> Then, it's up to the client to check it against a stored value, and
> clear/repopulate unlogged tables as necessary.

I think this would be useful for a variety of purposes. Both being
able to know the last time that it happened and being able to know the
number of times that it happened could be useful, depending on the
scenario. For example, if one of my employer's customers began
complaining about a problem that started happening recently, it would
be useful to be able to establish whether there had also been a crash
recently, and a timestamp or LSN would help a lot. On the other hand,
if we had a counter, we'd probably find out some interesting things,
too. Maybe someone would report that the value of the counter was
surprisingly large. For example, if a customer's pg_control output
showed that the database cluster had performed crash recovery 162438
times, I might have some, err, followup questions.

This is not a vote for or against any specific proposal; it's just a
general statement that I support trying to do something in this area,
and that it feels like anything we do will likely have some value.

--
Robert Haas
EDB: http://www.enterprisedb.com