Re: Support to define custom wait events for extensions

On Thu, Jun 15, 2023 at 03:06:01PM +0900, Masahiro Ikeda wrote:
> Currently, only one PG_WAIT_EXTENSION event can be used as a
> wait event for extensions. Therefore, in environments with multiple
> extensions are installed, it could take time to identify which
> extension is the bottleneck.

Thanks for taking the time to implement a patch to do that.

> I want to know your feedbacks. Please feel free to comment.

I think that's been cruelly missed.

> In the core, the requested wait events are dynamically registered in shared
> memory. The extension then obtains the wait event information with
> GetNamedExtensionWaitEventTranche() and uses the value to notify the core
> that it is waiting.
>
> When a string representing of the wait event is requested,
> it returns the name defined by calling
> RequestNamedExtensionWaitEventTranche().

So this implements the equivalent of RequestNamedLWLockTranche()
followed by GetNamedLWLockTranche() to get the wait event number,
which can be used only during postmaster startup. Do you think that
we could focus on implementing something more flexible instead, that
can be used dynamically as well as statically? That would be similar
to LWLockNewTrancheId() and LWLockRegisterTranche(), actually, where
we would get one or more tranche IDs, then do initialization actions
in shmem based on the tranche ID(s).

> 4. check the custom wait event
> You can see the following results of psql-1.
>
> query | wait_event_type | wait_event
> -----------------------------+-----------------+-------------------
> SELECT inject_wait_event(); | Extension | custom_wait_event #
> requested wait event by the extension!
> (1 row)
>
> (..snip..)

A problem with this approach is that it is expensive as a test. Do we
really need one? There are three places that set PG_WAIT_EXTENSION in
src/test/modules/, more in /contrib, and there are modules like
pg_stat_statements that could gain from events for I/O operations, for
example.

> # TODOs
>
> * tests on windows (since I tested on Ubuntu 20.04 only)
> * add custom wait events for existing contrib modules (ex. postgres_fdw)
> * add regression code (but, it seems to be difficult)
> * others? (Please let me know)

Hmm. You would need to maintain a state in a rather stable manner,
and SQL queries can make that difficult in the TAP tests as the wait
event information is reset each time a query finishes. One area where
I think this gets easier is with a background worker loaded with
shared_preload_libraries that has a configurable naptime. Looking at
what's available in the tree, the TAP tests of pg_prewarm could use
one test on pg_stat_activity with a custom wait event name
(pg_prewarm.autoprewarm_interval is 0 hence the bgworker waits
infinitely). Note that in this case, you would need to be careful of
the case where the wait event is loaded dynamically, but like LWLocks
this should be able to work as well?
--
Michael