Обсуждение: Clarification on when _PG_init() is invoked for extensions
Hello Hackers,
I’m working on a PostgreSQL extension that defines GUCs and initializes hooks inside the _PG_init() function,
and I wanted to confirm my understanding of when _PG_init() is called and whether the library remains loaded
across sessions.
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.
2/ When using LOAD 'extension': My understanding is that the library loads only in the backend executing the LOAD
command, and _PG_init() runs just for that backend. I’m unsure whether this affects existing and future backends for
the same database, or if it is strictly per-backend and non-persistent. I also believe this mechanism cannot be used if
the extension needs to 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. Also wanted to check if I need LOAD extname in my sql script?
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.
If someone could confirm whether my understanding is correct
Thanks in advance for your guidance.
Regards,
Ayush
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.
memory or register a background worker.
3/ When running
CREATE EXTENSION: Here the.sois loaded lazily when a function defined withMODULE_PATHNAMEis
first executed or defined. In this model, I’m unsure whether I need to explicitly runLOAD 'extension'to ensure_PG_init()runs for other existing and upcoming backends.
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.
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?
Thank you,