Re: Comments on Custom RMGRs

On 27/5/2024 20:20, Michael Paquier wrote:
> Please note that I've been studying ways to have pg_stat_statements
> being plugged in directly with the shared pgstat APIs to get it backed
> by a dshash to give more flexibility and scaling, giving a way for
> extensions to register their own stats kind.  In this case, the flush
> of the stats would be controlled with a callback in the stats
> registered by the extensions, conflicting with what's proposed here.
> pg_stat_statements is all about stats, at the end.  I don't want this
> argument to act as a barrier if a checkpoint hook is an accepted
> consensus here,  but a checkpoint hook used for this code path is not
> the most intuitive solution I can think of in the long-term.Let me continue this thread.
I wait for any kind of checkpoint cut-in machinery for extensions.

Typically, when collecting knowledge about the instance state, we store 
it in an extension's owned database table, incurring the costs 
associated with transactional mechanics, tuple format overhead, and so 
on. Usually, we don't need MVCC or rollback; we have fixed-length data, 
and it would be better to store data in hash tables. These hash tables 
should survive instances' restarts and crashes - that's the only feature 
needed.

The pg_stat_statements dumps its data to a file, but it is not reliable 
enough when we need consistent information, such as replication status 
or when logging update conflicts (see the Spock extension [1]). When we 
learn about query executions, we can't dump the hash table on each 
ExecutorEnd due to overhead, but we are okay with adding one more WAL 
record containing the hash table entry data - it may be done by the 
backend or by a separate background worker.

So, the primary reason for us is to have a moment to store the 
extension's state on disk, keeping in mind that we have registered RMGR, 
which allows us to restore the full state using this disk file and WAL 
records.

For me, the ideal place for such a hook is CheckPointGuts, right between 
the CheckPointBuffers call and fsyncs. I think that to demonstrate how 
this hook can work, the pg_stat_statements storage may need to be 
redesigned slightly.

[1] https://github.com/pgEdge/spock

-- 
regards, Andrei Lepikhov,
pgEdge