Based on documentation and experimentation, I believe the behavior works as follows, but I would appreciate confirmation or corrections:
1/ When the extension is listed in shared_preload_libraries: _PG_init() is executed before the server starts accepting connections, and hooks are initialized for every backend process. The downside is that this enables hooks globally for all databases in the instance.
That sounds correct. shared_preload_libraries are typically used when your module needs to access shared memory or register a background worker.
3/ When running CREATE EXTENSION: Here the .so is loaded lazily when a function defined with MODULE_PATHNAME is first executed or defined. In this model, I’m unsure whether I need to explicitly run LOAD 'extension' to ensure _PG_init() runs for other existing and upcoming backends.
CREATE extension does not automatically load or ensure that _PG_init() is run. It mainly runs the .sql script in your extension.
My use case: I would like the extension to initialize hooks automatically, but only for all (existing and upcoming) backends of the specific database where the extension is installed - not globally across all databases.
You might need to add a check in your hook to verify the current database. However, depending on where the hook runs, performing catalog lookups to determine the connected database could be costly.
Could you share more details about the problem you’re trying to solve, or explain why you want to restrict access to hooks?
