Re: RFC: Additional Directory for Extensions

Hi Craig,

On Thu, 22 Aug 2024 at 01:07, Craig Ringer <craig(dot)ringer(at)enterprisedb(dot)com>
wrote:

> It's also very relevant for local development and testing.
>

Yep, which is the original goal of Christoph IIRC.

> It may be possible to weaken this restriction somewhat thanks to the
> upcoming
> https://kubernetes.io/blog/2024/08/16/kubernetes-1-31-image-volume-source/
> feature that permits additional OCI images to be mounted as read-only
> volumes on a workload. This would still only permit mounting at
> Pod-creation time, not runtime mounting and unmonuting, but means the
> base postgres image could be supplemented by mounting additional
> images for extensions.
>

I'm really excited about that feature, but it's still in the alpha stage.
However, I don't anticipate any issues for the future general availability
(GA) release. Regardless, we may need to consider a temporary solution that
is compatible with existing Kubernetes and possibly Postgres versions (but
that's beyond the purpose of this thread).

For example, one might mount image "postgis-vX.Y.Z" image onto base
> image "postgresql-16" if support for PostGIS is desired, without then
> having to bake every possible extension anyone might ever want into
> the base image. This solves all sorts of messy issues with upgrades
> and new version releases.
>

Yep.

> But for it to work, it must be possible to tell postgres to look in
> _multiple places_ for extension .sql scripts and control files. This
> is presently possible for modules (dynamic libraries, .so / .dylib /
> .dll) but without a way to also configure the path for extensions it's
> of very limited utility.
>

Agree.

> So IMO this should be a _path_ to search for extension control files
> and SQL scripts.
>

I like this. I also prefer the name `extension_search_path`.

For safety, it might make sense to impose the restriction that if an
> extension control file is found in a given directory, SQL scripts will
> also only be looked for in that same directory. That way there's no
> chance of accidentally mixing and matching SQL scripts from different
> versions of an extension if it appears twice on the extension search
> path in different places. The rule for loading SQL scripts would be:
>
> * locate first directory on path contianing matching extension control file
> * use this directory as the extension directory for all subsequent SQL
> script loading and running actions
>

It could work, but it requires some prototyping and exploration. I'm
willing to participate and use CloudNativePG as a test bed.

Cheers,
Gabriele
--
Gabriele Bartolini
Vice President, Cloud Native at EDB
enterprisedb.com