Clarification on when _PG_init() is invoked for extensions

Hi,

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?

Thank you,
Rahila Syed

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 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.

*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

You probably can do the LOAD in the GUC callback functions and then set
that GUC only for specific databases using ALTER DATABASE SET
<my-extension-loading-guc>=true;

On Thu, Nov 20, 2025 at 6:53 PM Ayush Vatsa <ayushvatsa1810@gmail.com>
wrote:

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

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 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.

You are right, if the create extension creates a C function it triggers the
load of the library, which
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