The following bug has been logged on the website:
Bug reference: 19506
Logged by: Gabriele Bartolini
Email address: gabriele.bartolini@gmail.com
PostgreSQL version: 18.4
Operating system: Linux (reproduced under CloudNativePG/Kubernetes)
Description:
When an extension is installed in a location reached via
`extension_control_path` / `dynamic_library_path` (rather than the
compiled-in package library directory), a LOAD '$libdir/foo' hardcoded
inside an extension's SQL script fails to find the library. PostGIS does
this in its upgrade scripts, so a PostGIS upgrade fails:
```
app=# SELECT postgis_extensions_upgrade();
NOTICE: Updating extension postgis 3.6.1
ERROR: could not access file "$libdir/postgis-3": No such file or directory
CONTEXT: SQL statement "LOAD '$libdir/postgis-3'"
extension script file "postgis--ANY--3.6.3.sql", near line 1530
```
This is a side effect of the fix for bug #18920 (commit f777d773878). Commit
4f7f7b03758 (`extension_control_path`) made the feature work by stripping
the '$libdir/' prefix so that dynamic_library_path is consulted. #18920 then
restricted that stripping to the function-load path so that a user-issued
`LOAD` keeps the literal '$libdir/' prefix. As a result, a `LOAD` inside an
extension script now also keeps the literal prefix, so
`dynamic_library_path` is never consulted, and the library cannot be found.
A `LOAD` running inside an extension script should behave like the
extension's function loads (strip '$libdir/'), while a LOAD issued directly
by a user should keep it (the #18920 behaviour). The two can be
distinguished by `creating_extension`.
Reproduced with the CloudNativePG operator on Kubernetes, but it applies to
any setup using `extension_control_path` / `dynamic_library_path`.
