Обсуждение: 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,
CREATE extension does not automatically load or ensure that _PG_init() is run.
It mainly runs the .sql script in your extension.
_PG_init() did run when I executed CREATE EXTENSION. I suspect this might be happening because the SQL script defines C functions using
MODULE_PATHNAME, which triggers the library load. In a new session,
_PG_init() seems to run again when any of those C functions are executed.databases. I want the hooks initialized by this extension to apply only to those specific
databases, while all other databases should continue with the default PostgreSQL behavior.
Ayush
Hi,CREATE extension does not automatically load or ensure that _PG_init() is run.
It mainly runs the .sql script in your extension.Thanks for the clarification. However, in my testing,_PG_init()did run when I executedCREATE EXTENSION. I suspect this might be happening because the SQL script defines
C functions usingMODULE_PATHNAME, which triggers the library load.
In a new session,_PG_init()seems to run again when any of those C functions are executed.My use case:My use case is that I’m building an experimental extension that will be installed only in a few
databases. I want the hooks initialized by this extension to apply only to those specific
databases, while all other databases should continue with the default PostgreSQL behavior.Thank you,
Ayush
Ayush Vatsa <ayushvatsa1810@gmail.com> writes:
> My use case is that I’m building an experimental extension that will be
> installed only in a few
> databases. I want the hooks initialized by this extension to apply only to
> those specific
> databases, while all other databases should continue with the default
> PostgreSQL behavior.
If you don't name the extension in shared_preload_libraries, it
will not be loaded into the postmaster, but only into individual
sessions. The ways the latter can happen are
* session_preload_libraries or local_preload_libraries GUC
* explicit LOAD command
* creation of a C function that references that library
* calling of a C function that references that library
Extensions per se don't have anything to do with this,
except to the extent that an extension's creation script
might do some of the above actions.
You might be able to get what you want by doing
ALTER DATABASE db SET session_preload_libraries = 'your-library'
for each DB you want it to be active in. If it exposes any SQL
objects you'd also need to issue CREATE EXTENSION in each DB.
regards, tom lane
CREATE extension does not automatically load or ensure that _PG_init() is run.
It mainly runs the .sql script in your extension.Thanks for the clarification. However, in my testing,_PG_init()did run when I executedCREATE EXTENSION. I suspect this might be happening because the SQL script defines
C functions usingMODULE_PATHNAME, which triggers the library load.
In a new session,_PG_init()seems to run again when any of those C functions are executed.
in turn causes _PG_init() to execute.
Sorry for the confusion.
FWIW, the call stack looks like the following:
_PG_init ()
internal_load_library()
load_external_function ()
fmgr_c_validator ()
FunctionCall1Coll ()
OidFunctionCall1Coll ()
ProcedureCreate ()
CreateFunction ()
ProcessUtilitySlow ()
standard_ProcessUtility ()
ProcessUtility ()
execute_sql_string ()
execute_extension_script ()
CreateExtensionInternal ()
CreateExtension ()
Thank you,
Rahila Syed