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`.
Thanks for the report and the very clear diagnosis, I could
reproduce the issue and your analysis matches what I see.
The attached patch implements exactly what you suggested: a
LOAD running while creating_extension is true strips the simple
"$libdir/" prefix (so dynamic_library_path is consulted, like
the extension's own function loads do), while a user-issued
LOAD keeps the literal prefix and therefore preserves the
#18920 behaviour.
To avoid duplicating the existing prefix-stripping logic in
load_external_function(), I factored it out into a small
static helper and reused it from load_file(). Nested paths
(e.g. "$libdir/foo/bar") are still left untouched and continue
to be expanded by expand_dynamic_library_name() as before.
Verified locally that the PostGIS-style reproducer now succeeds
when the extension is installed via extension_control_path /
dynamic_library_path, that a plain user-issued
LOAD '$libdir/foo' still behaves as on HEAD, and that the
existing extension regression suites still pass.
Regards,
Ayush
