Subject: Fwd: Advice about preloaded libraries

From: Esteban Zimanyi <[email-masked]>
Date: Tue, 10 Oct 2023 10:58:57 +0200

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/[email-masked]
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.

---

From: Alvaro Herrera <[email-masked]>
Date: Tue, 10 Oct 2023 17:15:36 +0200

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