Re: 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
> https://www.postgresql.org/message-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(at)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].

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