Re: first add newly loaded plugin to the list then invoke _PG_init

On Thu, Mar 11, 2021 at 12:29:49PM +0100, mickiewicz(at)syncad(dot)com wrote:
> Hi All,
> I'm working on C plugin for Postgres (ver. 10). One of the thing which I
> need is to automatically add some SQL functions from the plugin during its
> initialization ( inside _PG_init method ). It means that during loading
> libmyplugin.so _PG_init calls SPI_execute( "CREATE FUNCTION fun() RETURNING
> void() AS '$libdir/libmyplugin.so', 'fun' LANGUAGE C" ), what leads to
> recursive call of _PG_init.

You can't do that. This might appear to work in the common case, but you have
no guarantee that you'll have an active transaction when your module is loaded,
or even that you'll be able to perform write. For instance modules are also
loaded in parallel workers, or or hot standby servers.

> It seems that the problem is with adding a
> plugin to the list after the execution of _PG_init, not before: https://github.com/postgres/postgres/blob/2c0cefcd18161549e9e8b103f46c0f65fca84d99/src/backend/utils/fmgr/dfmgr.c#L287
> . What do You think about changing this and add a newly loaded plugin to the
> list and then execute its _PG_init ? This change will help to keep
> consistency between plugin expectation about database structure (about
> tables, functions, etc.) without delivery additional SQL scripts - plugin
> itself will be able to prepare all the required things.

I think you're doing things backwards. You can look at pg_stat_statements on
how to achieve ABI compatability for lib vs SQL definition mismatch.