Fwd: Advice about preloaded libraries

Hi,

MobilityDB
https://github.com/MobilityDB/MobilityDB
is a PostgreSQL extension that depends on PosGIS.

Bradford Boyle who has been working on packaging MobilityDB
/messages/by-id/CAPqRbE716D3gpD0JDbaFAb72ELaJrPpG1LUZvobELNBgL3R0+g@mail.gmail.com
highlighted the issue of which of the GUC shared_preload_libraries vs local_preload_libraries vs session_preload_libraries should be used to load the postgis-3 library.

Our understanding of the information in the manual
https://www.postgresql.org/docs/16/runtime-config-client.html#GUC-SESSION-PRELOAD-LIBRARIES
does not give us a clear-cut answer for this question. We are looking for advice on which of the three options mentioned above should be used.

MobilityDB requires loading PostGIS before any MobilityDB query can be issued. For example, commenting out the following line
#shared_preload_libraries = 'postgis-3'
in the postgresql.conf shows the following

$ psql test
psql (15.3)
Type "help" for help.

test=# select tgeompoint 'Point(1 1)@2000-01-01';
2023-10-03 16:41:25.980 CEST [8683] ERROR: could not load library "/usr/local/pgsql/15/lib/libMobilityDB-1.1.so": /usr/local/pgsql/15/lib/libMobilityDB-1.1.so: undefined symbol: ST_Intersects at character 19
2023-10-03 16:41:25.980 CEST [8683] STATEMENT: select tgeompoint 'Point(1 1)@2000-01-01';
ERROR: could not load library "/usr/local/pgsql/15/lib/libMobilityDB-1.1.so": /usr/local/pgsql/15/lib/libMobilityDB-1.1.so: undefined symbol: ST_Intersects
LINE 1: select tgeompoint 'Point(1 1)@2000-01-01';
^
test=# select st_point(1,1);
st_point
--------------------------------------------
0101000000000000000000F03F000000000000F03F
(1 row)

test=# select tgeompoint 'Point(1 1)@2000-01-01';
tgeompoint
-------------------------------------------------------------------
0101000000000000000000F03F000000000000F03F@2000-01-01 00:00:00+01
(1 row)

test=#
------------------------------------------------------------

As can be seen above, it is not REALLY mandatory to have shared_preload_libraries = 'postgis-3' but then the user is responsible for issuing a query to load PostGIS (select st_point(1,1); above) and then she is able to execute MobilityDB queries.

Thanks for your advice.

I read the email several times but I'm still not sure I understand
what in particular you are asking.

From what I can tell the goal is to package a third-party project,
MobilityDB in this case. This project should have clear instructions
on how to install it. If not, consider reporting the issue to the
developers. If they can't provide good documentation maybe the project
is not mature enough to invest your time into it yet.

If you find the observed behavior of PostgreSQL confusing, that's
understandable, but this behavior is expected one. Typically
PostgreSQL loads an extension to the backend when needed. This is what
happens when a user calls `select st_point(1,1);`. You can load an
extension to the postmaster by using shared_preload_libraries. This is
typically used when an extension needs to acquire locks and shared
memory when DBMS starts. To my knowledge PostGIS doesn't use any of
this, although I'm not an expert in PostGIS.

All PostgreSQL GUCs are well documented. You can find more details here [1]https://www.postgresql.org/docs/current/runtime-config-client.html.

Hopefully I answered the right question. If not please be a bit more specific.

[1]: https://www.postgresql.org/docs/current/runtime-config-client.html

--
Best regards,
Aleksander Alekseev

On 2023-Oct-10, Esteban Zimanyi wrote:

As can be seen above, it is not REALLY mandatory to have
shared_preload_libraries = 'postgis-3' but then the user is responsible for
issuing a query to load PostGIS (select st_point(1,1); above) and then she
is able to execute MobilityDB queries.

Calling a function that exists in some library will cause the library to
be loaded. Alternatively, you can cause the library to be loaded
automatically at some point of the start sequence, by
shared_preload_libraries or the other configuration options. Or you can
use the LOAD statement.

If by whichever mechanism postgis has been loaded into your session,
then calling a function in MobilityDB will work fine, because the
postgis library will have been loaded. It doesn't matter exactly how
was postgis loaded.

The advantage of using shared_preload_libraries is performance of
connection establishment: the library is loaded by the postmaster, so
each new backend inherits it already loaded and doesn't have to load it
itself.

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"I love the Postgres community. It's all about doing things _properly_. :-)"
(David Garamond)