Subject: Re: RFC: Additional Directory for Extensions

From: "David E. Wheeler" <[email-masked]>
Date: Mon, 26 Aug 2024 10:06:59 -0400

Hi Hackers,

Apologies for the delay in reply; I’ve been at the XOXO Festival and almost completely unplugged for the first time in ages. Happy to see this thread coming alive, though. Thank you Gabriele, Craig, and Jelte!

On Aug 21, 2024, at 19:07, Craig Ringer <[email-masked]> wrote:

> So IMO this should be a _path_ to search for extension control files
> and SQL scripts.
> 
> If the current built-in default extension dir was exposed as a var
> $extdir like we do for $libdir, this might look something like this
> for local development and testing while working with a packaged
> postgres build:
> 
>    SET extension_search_path = $extsdir, /opt/myapp/extensions,
> /usr/local/postgres/my-custom-extension/extensions;
>    SET dynamic_library_path = $libdir, /opt/myapp/lib,
> /usr/local/postgres/my-custom-extension/lib

I would very much like something like this, but I’m not sure how feasible it is for a few reasons. The first, and most important, is that extensions are not limited to just a control file and SQL file. They also very often include:

* one or more shared library files
* documentation files
* binary files

And maybe more? How many of these directories might an extension install files into:

✦ ❯ pg_config | grep DIR | awk '{print $1}'
BINDIR
DOCDIR
HTMLDIR
INCLUDEDIR
PKGINCLUDEDIR
INCLUDEDIR-SERVER
LIBDIR
PKGLIBDIR
LOCALEDIR
MANDIR
SHAREDIR
SYSCONFDIR

I would assume BINDIR, DOCDIR, HTMLDIR, PKGLIBDIR, MANDIR, SHAREDIR, and perhaps LOCALEDIR.

But even if it’s just one or two, the only proper way an extension directory would work, IME, is to define a directory-based structure for extensions, where every file for an extension is in a directory named for the extension, and subdirectories are defined for each of the above requisite file types. Something like:

extension_name
├── control.ini
├── bin
├── doc
├── html
├── lib
├── local
├── man
└── share

This would allow multiple paths to work and keep all the files for an extension bundled together. It could also potentially allow for multiple versions of an extension to be installed at once, if we required the version to be part of the directory name.

I think this would be a much nicer layout for packaging, installing, and managing extensions versus the current method of strewing files around to a slew of different directories. But it would come at some cost, in terms of backward with the existing layout (or migration to it), significant modification of the server to use the new layout (and extension_search_path), and other annoyances like PATH and MANPATH management.

Long term I think it would be worthwhile, but the current patch feels like a decent interim step we could live with, solving most of the integration problems (immutable servers, packaging testing, etc.) at the cost of a slightly unexpected directory layout. What I mean by that is that the current patch is pretty much just using extension_destdir as a prefix to all of those directories from pg_config, so they never have to change, but it does mean that you end up installing extensions in something like

/mnt/extensions/pg16/usr/share/postgresql/16
/mnt/extensions/pg16/usr/include/postgresql

etc.

Best,

David

---

From: Craig Ringer <[email-masked]>
Date: Tue, 27 Aug 2024 09:35:43 +1200

> But even if it’s just one or two, the only proper way an extension directory would work, IME, is to define a directory-based structure for extensions, where every file for an extension is in a directory named for the extension, and subdirectories are defined for each of the above requisite file types.
> [...]
> I think this would be a much nicer layout for packaging, installing, and managing extensions versus the current method of strewing files around to a slew of different directories.

This looks like a good suggestion to me, it would make the packaging,
distribution and integration of 3rd party extensions significantly
easier without any obvious large or long term cost.

> But it would come at some cost, in terms of backward with the existing layout (or migration to it), significant modification of the server to use the new layout (and extension_search_path), and other annoyances like PATH and MANPATH management.

Also PGXS, the windows extension build support, and 3rd party cmake
builds etc. But not by the looks a drastic change.

> Long term I think it would be worthwhile, but the current patch feels like a decent interim step we could live with, solving most of the integration problems (immutable servers, packaging testing, etc.) at the cost of a slightly unexpected directory layout. What I mean by that is that the current patch is pretty much just using extension_destdir as a prefix to all of those directories from pg_config, so they never have to change, but it does mean that you end up installing extensions in something like:
>

My only real concern with the current patch is that it limits
searching for extensions to one additional configurable location,
which is inconsistent with how things like the dynamic_library_path
works. Once in, it'll be difficult to change or extend for BC, and if
someone wants to add a search path capability it'll break existing
configurations.

Would it be feasible to define its configuration syntax as accepting a
list of paths, but only implement the semantics for single-entry lists
and ERROR on multiple paths? That way it could be extended w/o
breaking existing configurations later.

With that said, I'm not the one doing the work at the moment, and the
functionality would definitely be helpful. If there's agreement on
supporting a search-path or recursing into subdirectories I'd be
willing to have a go at it, but I'm a bit stale on Pg's codebase now
so I'd want to be fairly confident the work wouldn't just be thrown
out.

--
Craig Ringer

---

From: Gabriele Bartolini <[email-masked]>
Date: Tue, 27 Aug 2024 10:56:44 +0200

Hi David,

Thanks for your email.

On Mon, 26 Aug 2024 at 16:07, David E. Wheeler <[email-masked]>
wrote:

> But even if it’s just one or two, the only proper way an extension
> directory would work, IME, is to define a directory-based structure for
> extensions, where every file for an extension is in a directory named for
> the extension, and subdirectories are defined for each of the above
> requisite file types. Something like:
>
> extension_name
> ├── control.ini
> ├── bin
> ├── doc
> ├── html
> ├── lib
> ├── local
> ├── man
> └── share
>

I'm really glad you proposed this publicly. I reached the same conclusion
the other day when digging deeper into the problem with a few folks from
CloudNativePG. Setting aside multi-arch images for now, if we could
reorganize the content of a single image (identified by OS distro,
PostgreSQL major version, and extension version) with a top-level directory
structure as you described, we could easily mount each image as a separate
volume.

The extension image could follow a naming convention like this (order can
be adjusted): `<extension name>-<pg major>-<extension
version>-<distro>(-<seq>)`. For example, `pgvector-16-0.7.4-bookworm-1`
would represent the first image built in a repository for pgvector 0.7.4
for PostgreSQL 16 on Debian Bookworm. If multi-arch images aren't desired,
we could incorporate the architecture somewhere in the naming convention.

This would allow multiple paths to work and keep all the files for an
> extension bundled together. It could also potentially allow for multiple
> versions of an extension to be installed at once, if we required the
> version to be part of the directory name.
>

If we wanted to install multiple versions of an extension, we could mount
them in different directories, with the version included in the folder
name—for example, `pgvector-0.7.4` instead of just `pgvector`. However, I'm
a bit rusty with the extensions framework, so I'll need to check if this
approach is feasible and makes sense.

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

---

From: "David E. Wheeler" <[email-masked]>
Date: Tue, 27 Aug 2024 11:19:48 -0400

On Aug 27, 2024, at 04:56, Gabriele Bartolini <[email-masked]> wrote:

> The extension image could follow a naming convention like this (order can be adjusted): `<extension name>-<pg major>-<extension version>-<distro>(-<seq>)`. For example, `pgvector-16-0.7.4-bookworm-1` would represent the first image built in a repository for pgvector 0.7.4 for PostgreSQL 16 on Debian Bookworm. If multi-arch images aren't desired, we could incorporate the architecture somewhere in the naming convention.

Well now you’re just describing the binary distribution format RFC[1] (POC[2]) and multi-platform OCI distribution POC[3] :-)

> If we wanted to install multiple versions of an extension, we could mount them in different directories, with the version included in the folder name—for example, `pgvector-0.7.4` instead of just `pgvector`. However, I'm a bit rusty with the extensions framework, so I'll need to check if this approach is feasible and makes sense.

Right, if we decided to adopt this proposal, it might make sense to include the “default version” as part of the directory name. But there’s quite a lot of work between here and there.

Best,

David

[1]: https://github.com/pgxn/rfcs/pull/2
[2]: https://justatheory.com/2024/06/trunk-poc/
[3]: https://justatheory.com/2024/06/trunk-oci-poc/

---

From: "David E. Wheeler" <[email-masked]>
Date: Tue, 27 Aug 2024 11:26:15 -0400

> easier without any obvious large or long term cost.

Yes!

> builds etc. But not by the looks a drastic change.

Right. ISTM it could complicate PGXS quite a bit. If we set, say, 

SET extension_search_path = $extsdir, /mnt/extensions/pg16, /mnt/extensions/pg16/gosuperfast/extensions;

What should be the output of `pg_config --sharedir`?

> someone wants to add a search path capability it'll break existing

Agreed.

> breaking existing configurations later.

I imagine it’s a simple matter of programming :-) But that leaves the issue of directory organization. The current patch is just a prefix for various PGXS/pg_config directories; the longer-term proposal I’ve made here is not a prefix for sharedir, mandir, etc., but a directory that contains directories named for extensions. So even if we were to take this approach, the directory structure would vary.

I suspect we’d have to name it differently and support both long-term. That, too me, is the main issue with this patch.

OTOH, we have this patch now, and this other stuff is just a proposal. Actual code trumps ideas in my mind.

> so I'd want to be fairly confident the work wouldn't just be thrown

I think we should get some clarity on the proposal, and then consensus, as you say. I say “get some clarity” because my proposal doesn’t require recursing, and I’m not sure why it’d be needed.

Best,

David

---

From: Craig Ringer <[email-masked]>
Date: Wed, 28 Aug 2024 14:24:45 +1200

> What should be the output of `pg_config --sharedir`?

`pg_config` only cares about compile-time settings, so I would not
expect its output to change.

I suspect we'd have to add PGXS extension-path awareness if going for
per-extension subdir support. I'm not sure it makes sense to teach
`pg_config` about this, since it'd need to have a different mode like

    pg_config --extension myextname --extension-sharedir

since the extension's "sharedir" is
$basedir/extensions/myextension/share or whatever.

Supporting this looks to be a bit intrusive in the makefiles,
requiring them to differentiate between "share dir for extensions" and
"share dir for !extensions", etc. I'm not immediately sure if it can
be done in a way that transparently converts unmodified extension PGXS
makefiles to target the new paths; it might require an additional
define, or use of new variables and an ifdef block to add
backwards-compat to the extension makefile for building on older
postgres.

> But that leaves the issue of directory organization. The current patch is just a prefix for various PGXS/pg_config directories; the longer-term proposal I’ve made here is not a prefix for sharedir, mandir, etc., but a directory that contains directories named for extensions. So even if we were to take this approach, the directory structure would vary.

Right. The proposed structure is rather a bigger change than I was
thinking when I suggested supporting an extension search path not just
a single extra path. But it's also a cleaner proposal; the
per-extension directory would make it easier to ensure that the
extension control file, sql scripts, and dynamic library all match the
same extension and version if multiple ones are on the path. Which is
desirable when doing things like testing a new version of an in-core
extension.

> OTOH, we have this patch now, and this other stuff is just a proposal. Actual code trumps ideas in my mind.

Right. And I've been on the receiving end of having a small, focused
patch derailed by others jumping in and scope-exploding it into
something completely different to solve a much wider but related
problem.

I'm definitely not trying to stand in the way of progress with this; I
just want to make sure that it doesn't paint us into a corner that
prevents a more general solution from being adopted later. That's why
I'm suggesting making the config a multi-value string (list of paths)
and raising a runtime "ERROR: searching multiple paths for extensions
not yet supported" or something if >1 path configured.

If that doesn't work, no problem.

>From what you and Gabriele are discussing (which I agree with), it wouldn't.

---

From: "David E. Wheeler" <[email-masked]>
Date: Thu, 29 Aug 2024 11:55:27 -0400

> expect its output to change.

Right, of course that’s its original purpose, but extensions depend on it to determine where to install extensions. Not just PGXS, but also pgrx and various Makefile customizations I’ve seen in the wild.

> $basedir/extensions/myextension/share or whatever.

Right. PGXS would just need to know where to put the directory for an extension. There should be a default for the project, and then it can be overridden with something like DESTDIR (but without full paths under that prefix).

> backwards-compat to the extension makefile for building on older

Yeah, might just have to be an entirely new thing, though it sure would be nice for existing PGXS-using Makefiles to do the right thing. Maybe for the new version of the server with the proposed new pattern it would dispatch to the new thing somehow without modifying all the rest of its logic.

> desirable when doing things like testing a new version of an in-core

💯

> something completely different to solve a much wider but related

I’m not complaining, I would definitely prefer to see consensus on a cleaner proposal along the lines we’ve discussed and a commitment to time from parties able to get it done in time for v18. I’m willing to help where I can with my baby C! Failing that, we can fall back on the destdir patch.

> If that doesn't work, no problem.

I think the logic would have to be different, so they’d be different GUCs with their own semantics. But if the core team and committers are on board with the general idea of search paths and per-extension directory organization, it would be best to avoid getting stuck with maintaining the current patch’s GUC.

OTOH, we could get it committed now and revert it later if we get the better thing done and committed.

> From what you and Gabriele are discussing (which I agree with), it wouldn’t.

Ah, great.

I’ll try to put some thought into a more formal proposal in a new thread next week. Unless your Gabriele beats me to it 😂.

Best,

David

---

From: Ebru Aydin Gol <[email-masked]>
Date: Thu, 10 Oct 2024 14:20:14 +0300

Thanks for your efforts, a secondary directory for extensions is a very
useful feature.

Is there any updates on the patch?

-Ebru

On Thu, Aug 29, 2024 at 6:55 PM David E. Wheeler <[email-masked]>
wrote:

> Right, of course that’s its original purpose, but extensions depend on it
> to determine where to install extensions. Not just PGXS, but also pgrx and
> various Makefile customizations I’ve seen in the wild.
>
> Right. PGXS would just need to know where to put the directory for an
> extension. There should be a default for the project, and then it can be
> overridden with something like DESTDIR (but without full paths under that
> prefix).
>
> Yeah, might just have to be an entirely new thing, though it sure would be
> nice for existing PGXS-using Makefiles to do the right thing. Maybe for the
> new version of the server with the proposed new pattern it would dispatch
> to the new thing somehow without modifying all the rest of its logic.
>
> I’m not complaining, I would definitely prefer to see consensus on a
> cleaner proposal along the lines we’ve discussed and a commitment to time
> from parties able to get it done in time for v18. I’m willing to help where
> I can with my baby C! Failing that, we can fall back on the destdir patch.
>
> I think the logic would have to be different, so they’d be different GUCs
> with their own semantics. But if the core team and committers are on board
> with the general idea of search paths and per-extension directory
> organization, it would be best to avoid getting stuck with maintaining the
> current patch’s GUC.
>
> >> I think we should get some clarity on the proposal, and then consensus,
> as you say. I say “get some clarity” because my proposal doesn’t require
> recursing, and I’m not sure why it’d be needed.
> >
> I’ll try to put some thought into a more formal proposal in a new thread
> next week. Unless your Gabriele beats me to it 😂.
>
> Best,
>
> David
>
>
>
>

---

From: "David E. Wheeler" <[email-masked]>
Date: Thu, 10 Oct 2024 22:35:42 +0200

On Oct 10, 2024, at 13:20, Ebru Aydin Gol <[email-masked]> wrote:

> Thanks for your efforts, a secondary directory for extensions is a very useful feature.
>  

There haven't been, no, but your reply has chastened me! I’ve now started a separate thread[1] proposing directory-based extension packaging, as promised up-thread.

Best,

David

[1]: https://www.postgresql.org/message-id/2CAD6FA7-DC25-48FC-80F2-8F203DECAE6A%40justatheory.com

---

From: Ebru Aydin Gol <[email-masked]>
Date: Fri, 11 Oct 2024 11:27:18 +0300

❤️

Ebru Aydin Gol reacted via Gmail
<https://www.google.com/gmail/about/?utm_source=gmail-in-product&utm_medium=et&utm_campaign=emojireactionemail#app>

On Thu, Oct 10, 2024 at 11:35 PM David E. Wheeler <[email-masked]>
wrote:

> There haven't been, no, but your reply has chastened me! I’ve now started
> a separate thread[1] proposing directory-based extension packaging, as
> promised up-thread.
>
> Best,
>
> David
>
> [1]:
> https://www.postgresql.org/message-id/2CAD6FA7-DC25-48FC-80F2-8F203DECAE6A%40justatheory.com
>
>

---

From: Peter Eisentraut <[email-masked]>
Date: Mon, 11 Nov 2024 08:16:05 +0100

I implemented a patch along the lines Craig had suggested.  It's a new 
GUC variable that is a path for extension control files.  It's called 
extension_control_path, and it works exactly the same way as 
dynamic_library_path.  Except that the magic token is called $system 
instead of $libdir.  In fact, most of the patch is refactoring the 
routines in dfmgr.c to not hardcode dynamic_library_path but allow 
searching for any file in any path.  Once a control file is found, the 
other extension support files (script files and auxiliary control files) 
are looked for in the same directory.

This works pretty much fine for the use cases that have been presented 
here, including installing extensions outside of the core installation 
tree (for CNPG and Postgres.app) and for testing uninstalled extensions 
(for Debian).

There are some TODOs in the patch.  Some of those are for documentation 
that needs to be completed.  Others are for functions like 
pg_available_extensions() that need to be rewritten to be aware of the 
path.  I think this would be pretty straightforward.

Some open problems or discussion points:

- You can install extensions into alternative directories using PGXS like

     make install datadir=/else/where/share pkglibdir=/else/where/lib

This works.  I was hoping it would work to use

     make install prefix=/else/where

but that doesn't because of some details in Makefile.global.  I think we 
can tweak that a little bit to make that work too.

- With the current patch, if you install into datadir=/else/where/share, 
then you need to set extension_control_path=/else/where/share/extension. 
  This is a bit confusing.  Maybe we want to make the "extension" part 
implicit.

- The biggest problem is that many extensions set in their control file

     module_pathname = '$libdir/foo'

This disables the use of dynamic_library_path, so this whole idea of 
installing an extension elsewhere won't work that way.  The obvious 
solution is that extensions change this to just 'foo'.  But this will 
require a lot updating work for many extensions, or a lot of patching by 
packagers.

Maybe we could devise some sort of rule that if the extension control 
file is found via the path outside of $system, then the leading $libdir 
is ignored.
From 08620bae551bb6b2e3a44aa3df4c8751ad06f804 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <[email-masked]>
Date: Mon, 11 Nov 2024 07:29:47 +0100
Subject: [PATCH v0] extension_control_path

The new GUC extension_control_path specifies a path to look for
extension control files.  The default value is $system, which looks in
the compiled-in location, as before.

The path search uses the same code and works in the same way as
dynamic_library_path.

Discussion: https://www.postgresql.org/message-id/flat/[email-masked]
---
 doc/src/sgml/config.sgml                      | 13 ++++
 doc/src/sgml/extend.sgml                      |  1 +
 doc/src/sgml/ref/create_extension.sgml        |  1 +
 src/backend/commands/extension.c              | 56 ++++++++++++++--
 src/backend/utils/fmgr/dfmgr.c                | 64 +++++++++++--------
 src/backend/utils/misc/guc_tables.c           | 12 ++++
 src/backend/utils/misc/postgresql.conf.sample |  1 +
 src/include/commands/extension.h              |  2 +
 src/include/fmgr.h                            |  2 +
 9 files changed, 118 insertions(+), 34 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index d54f9049569..c4790de7102 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -10434,6 +10434,19 @@ <title>Other Defaults</title>
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-extension-control-path" xreflabel="extension_control_path">
+      <term><varname>extension_control_path</varname> (<type>string</type>)
+      <indexterm>
+       <primary><varname>extension_control_path</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        TODO
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="guc-gin-fuzzy-search-limit" xreflabel="gin_fuzzy_search_limit">
       <term><varname>gin_fuzzy_search_limit</varname> (<type>integer</type>)
       <indexterm>
diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml
index 218940ee5ce..77a517d5167 100644
--- a/doc/src/sgml/extend.sgml
+++ b/doc/src/sgml/extend.sgml
@@ -635,6 +635,7 @@ <title>Extension Files</title>
     <primary>control file</primary>
    </indexterm>
 
+    <!-- TODO -->
     <para>
      The <command>CREATE EXTENSION</command> command relies on a control
      file for each extension, which must be named the same as the extension
diff --git a/doc/src/sgml/ref/create_extension.sgml b/doc/src/sgml/ref/create_extension.sgml
index ca2b80d669c..c26218f3adf 100644
--- a/doc/src/sgml/ref/create_extension.sgml
+++ b/doc/src/sgml/ref/create_extension.sgml
@@ -92,6 +92,7 @@ <title>Parameters</title>
         installed. <productname>PostgreSQL</productname> will create the
         extension using details from the file
         <literal>SHAREDIR/extension/</literal><replaceable class="parameter">extension_name</replaceable><literal>.control</literal>.
+        <!-- TODO -->
        </para>
       </listitem>
      </varlistentry>
diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index af6bd8ff426..b328e8e28f4 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -69,6 +69,9 @@
 #include "utils/varlena.h"

+/* GUC */
+char	   *Extension_control_path;
+
 /* Globally visible state variables */
 bool		creating_extension = false;
 Oid			CurrentExtensionObject = InvalidOid;
@@ -79,6 +82,7 @@ Oid			CurrentExtensionObject = InvalidOid;
 typedef struct ExtensionControlFile
 {
 	char	   *name;			/* name of the extension */
+	char	   *control_dir;	/* directory where control file was found */
 	char	   *directory;		/* directory for script files */
 	char	   *default_version;	/* default install target version, if any */
 	char	   *module_pathname;	/* string to substitute for
@@ -328,6 +332,9 @@ is_extension_script_filename(const char *filename)
 	return (extension != NULL) && (strcmp(extension, ".sql") == 0);
 }
 
+// TODO
+// This is now only for finding/listing available extensions.  Rewrite to use
+// path.  See further TODOs below.
 static char *
 get_extension_control_directory(void)
 {
@@ -341,16 +348,36 @@ get_extension_control_directory(void)
 	return result;
 }
 
+/*
+ * Find control file for extension with name in control->name, looking in the
+ * path.  Return the full file name, or NULL if not found.  If found, the
+ * directory is recorded in control->control_dir.
+ */
 static char *
-get_extension_control_filename(const char *extname)
+find_extension_control_filename(ExtensionControlFile *control)
 {
 	char		sharepath[MAXPGPATH];
+	char	   *system_dir;
+	char	   *basename;
 	char	   *result;
 
+	Assert(control->name);
+
 	get_share_path(my_exec_path, sharepath);
-	result = (char *) palloc(MAXPGPATH);
-	snprintf(result, MAXPGPATH, "%s/extension/%s.control",
-			 sharepath, extname);
+	system_dir = psprintf("%s/extension", sharepath);
+
+	basename = psprintf("%s.control", control->name);
+
+	result = find_in_path(basename, Extension_control_path, "extension_control_path", "$system", system_dir);
+
+	if (result)
+	{
+		const char *p;
+
+		p = strrchr(result, '/');
+		Assert(p);
+		control->control_dir = pnstrdup(result, p - result);
+	}
 
 	return result;
 }
@@ -366,7 +393,7 @@ get_extension_script_directory(ExtensionControlFile *control)
 	 * installation's share directory.
 	 */
 	if (!control->directory)
-		return get_extension_control_directory();
+		return pstrdup(control->control_dir);
 
 	if (is_absolute_path(control->directory))
 		return pstrdup(control->directory);
@@ -444,7 +471,15 @@ parse_extension_control_file(ExtensionControlFile *control,
 	if (version)
 		filename = get_extension_aux_control_filename(control, version);
 	else
-		filename = get_extension_control_filename(control->name);
+		filename = find_extension_control_filename(control);
+
+	if (!filename)
+	{
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED), // XXX?
+				 errmsg("extension \"%s\" is not available", control->name),
+				 errhint("The extension must first be installed on the system where PostgreSQL is running.")));
+	}
 
 	if ((file = AllocateFile(filename, "r")) == NULL)
 	{
@@ -457,6 +492,7 @@ parse_extension_control_file(ExtensionControlFile *control,
 				return;
 			}
 
+			// TODO: this check is obsolete?
 			/* missing control file indicates extension is not installed */
 			ereport(ERROR,
 					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -2114,6 +2150,8 @@ RemoveExtensionById(Oid extId)
  * The system view pg_available_extensions provides a user interface to this
  * SRF, adding information about whether the extensions are installed in the
  * current DB.
+ *
+ * TODO
  */
 Datum
 pg_available_extensions(PG_FUNCTION_ARGS)
@@ -2194,6 +2232,8 @@ pg_available_extensions(PG_FUNCTION_ARGS)
  * The system view pg_available_extension_versions provides a user interface
  * to this SRF, adding information about which versions are installed in the
  * current DB.
+ *
+ * TODO
  */
 Datum
 pg_available_extension_versions(PG_FUNCTION_ARGS)
@@ -2366,6 +2406,8 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol,
  * directory.  That's not a bulletproof check, since the file might be
  * invalid, but this is only used for hints so it doesn't have to be 100%
  * right.
+ *
+ * TODO
  */
 bool
 extension_file_exists(const char *extensionName)
@@ -2445,6 +2487,8 @@ convert_requires_to_datum(List *requires)
 /*
  * This function reports the version update paths that exist for the
  * specified extension.
+ *
+ * TODO
  */
 Datum
 pg_extension_update_paths(PG_FUNCTION_ARGS)
diff --git a/src/backend/utils/fmgr/dfmgr.c b/src/backend/utils/fmgr/dfmgr.c
index c7aa789b51b..d348d30d9e4 100644
--- a/src/backend/utils/fmgr/dfmgr.c
+++ b/src/backend/utils/fmgr/dfmgr.c
@@ -81,8 +81,7 @@ static void incompatible_module_error(const char *libname,
 									  const Pg_magic_struct *module_magic_data) pg_attribute_noreturn();
 static char *expand_dynamic_library_name(const char *name);
 static void check_restricted_library_name(const char *name);
-static char *substitute_libpath_macro(const char *name);
-static char *find_in_dynamic_libpath(const char *basename);
+static char *substitute_path_macro(const char *str, const char *macro, const char *value);
 
 /* Magic structure that module needs to match to be accepted */
 static const Pg_magic_struct magic_data = PG_MODULE_MAGIC_DATA;
@@ -408,7 +407,7 @@ incompatible_module_error(const char *libname,
 /*
  * If name contains a slash, check if the file exists, if so return
  * the name.  Else (no slash) try to expand using search path (see
- * find_in_dynamic_libpath below); if that works, return the fully
+ * find_in_path below); if that works, return the fully
  * expanded file name.  If the previous failed, append DLSUFFIX and
  * try again.  If all fails, just return the original name.
  *
@@ -427,13 +426,13 @@ expand_dynamic_library_name(const char *name)
 
 	if (!have_slash)
 	{
-		full = find_in_dynamic_libpath(name);
+		full = find_in_path(name, Dynamic_library_path, "dynamic_library_path", "$libdir", pkglib_path);
 		if (full)
 			return full;
 	}
 	else
 	{
-		full = substitute_libpath_macro(name);
+		full = substitute_path_macro(name, "$libdir", pkglib_path);
 		if (pg_file_exists(full))
 			return full;
 		pfree(full);
@@ -443,14 +442,14 @@ expand_dynamic_library_name(const char *name)
 
 	if (!have_slash)
 	{
-		full = find_in_dynamic_libpath(new);
+		full = find_in_path(new, Dynamic_library_path, "dynamic_library_path", "$libdir", pkglib_path);
 		pfree(new);
 		if (full)
 			return full;
 	}
 	else
 	{
-		full = substitute_libpath_macro(new);
+		full = substitute_path_macro(new, "$libdir", pkglib_path);
 		pfree(new);
 		if (pg_file_exists(full))
 			return full;
@@ -485,47 +484,56 @@ check_restricted_library_name(const char *name)
  * Result is always freshly palloc'd.
  */
 static char *
-substitute_libpath_macro(const char *name)
+substitute_path_macro(const char *str, const char *macro, const char *value)
 {
 	const char *sep_ptr;
 
-	Assert(name != NULL);
+	Assert(str != NULL);
+	Assert(macro[0] == '$');
 
-	/* Currently, we only recognize $libdir at the start of the string */
-	if (name[0] != '$')
-		return pstrdup(name);
+	/* Currently, we only recognize $macro at the start of the string */
+	if (str[0] != '$')
+		return pstrdup(str);
 
-	if ((sep_ptr = first_dir_separator(name)) == NULL)
-		sep_ptr = name + strlen(name);
+	if ((sep_ptr = first_dir_separator(str)) == NULL)
+		sep_ptr = str + strlen(str);
 
-	if (strlen("$libdir") != sep_ptr - name ||
-		strncmp(name, "$libdir", strlen("$libdir")) != 0)
+	if (strlen(macro) != sep_ptr - str ||
+		strncmp(str, macro, strlen(macro)) != 0)
 		ereport(ERROR,
 				(errcode(ERRCODE_INVALID_NAME),
-				 errmsg("invalid macro name in dynamic library path: %s",
-						name)));
+				 errmsg("invalid macro name in path: %s",
+						str)));
 
-	return psprintf("%s%s", pkglib_path, sep_ptr);
+	return psprintf("%s%s", value, sep_ptr);
 }

 /*
  * Search for a file called 'basename' in the colon-separated search
- * path Dynamic_library_path.  If the file is found, the full file name
+ * path given.  If the file is found, the full file name
  * is returned in freshly palloc'd memory.  If the file is not found,
  * return NULL.
+ *
+ * path_param is the name of the parameter that path came from, for error
+ * messages.
+ *
+ * macro and macro_val allow substituting a macro; see
+ * substitute_path_macro().
  */
-static char *
-find_in_dynamic_libpath(const char *basename)
+char *
+find_in_path(const char *basename, const char *path, const char *path_param,
+			 const char *macro, const char *macro_val)
 {
 	const char *p;
 	size_t		baselen;
 
 	Assert(basename != NULL);
 	Assert(first_dir_separator(basename) == NULL);
-	Assert(Dynamic_library_path != NULL);
+	Assert(path != NULL);
+	Assert(path_param != NULL);
 
-	p = Dynamic_library_path;
+	p = path;
 	if (strlen(p) == 0)
 		return NULL;
 
@@ -542,7 +550,7 @@ find_in_dynamic_libpath(const char *basename)
 		if (piece == p)
 			ereport(ERROR,
 					(errcode(ERRCODE_INVALID_NAME),
-					 errmsg("zero-length component in parameter \"dynamic_library_path\"")));
+					 errmsg("zero-length component in parameter \"%s\"", path_param)));
 
 		if (piece == NULL)
 			len = strlen(p);
@@ -552,7 +560,7 @@ find_in_dynamic_libpath(const char *basename)
 		piece = palloc(len + 1);
 		strlcpy(piece, p, len + 1);
 
-		mangled = substitute_libpath_macro(piece);
+		mangled = substitute_path_macro(piece, macro, macro_val);
 		pfree(piece);
 
 		canonicalize_path(mangled);
@@ -561,13 +569,13 @@ find_in_dynamic_libpath(const char *basename)
 		if (!is_absolute_path(mangled))
 			ereport(ERROR,
 					(errcode(ERRCODE_INVALID_NAME),
-					 errmsg("component in parameter \"dynamic_library_path\" is not an absolute path")));
+					 errmsg("component in parameter \"%s\" is not an absolute path", path_param)));
 
 		full = palloc(strlen(mangled) + 1 + baselen + 1);
 		sprintf(full, "%s/%s", mangled, basename);
 		pfree(mangled);
 
-		elog(DEBUG3, "find_in_dynamic_libpath: trying \"%s\"", full);
+		elog(DEBUG3, "%s: trying \"%s\"", __func__, full);
 
 		if (pg_file_exists(full))
 			return full;
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 8a67f01200c..5a00b99daba 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -39,6 +39,7 @@
 #include "catalog/namespace.h"
 #include "catalog/storage.h"
 #include "commands/async.h"
+#include "commands/extension.h"
 #include "commands/event_trigger.h"
 #include "commands/tablespace.h"
 #include "commands/trigger.h"
@@ -4233,6 +4234,17 @@ struct config_string ConfigureNamesString[] =
 		NULL, NULL, NULL
 	},
 
+	{
+		{"extension_control_path", PGC_SUSET, CLIENT_CONN_OTHER,
+			gettext_noop("Sets the path for extension control files."),
+			gettext_noop("TODO"),
+			GUC_SUPERUSER_ONLY
+		},
+		&Extension_control_path,
+		"$system",
+		NULL, NULL, NULL
+	},
+
 	{
 		{"krb_server_keyfile", PGC_SIGHUP, CONN_AUTH_AUTH,
 			gettext_noop("Sets the location of the Kerberos server key file."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 39a3ac23127..518c4f40719 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -772,6 +772,7 @@
 # - Other Defaults -
 
 #dynamic_library_path = '$libdir'
+#extension_control_path = '$system'
 #gin_fuzzy_search_limit = 0

diff --git a/src/include/commands/extension.h b/src/include/commands/extension.h
index c6f3f867eb7..fe8a97570ea 100644
--- a/src/include/commands/extension.h
+++ b/src/include/commands/extension.h
@@ -17,6 +17,8 @@
 #include "catalog/objectaddress.h"
 #include "parser/parse_node.h"
 
+/* GUC */
+extern PGDLLIMPORT char *Extension_control_path;
 
 /*
  * creating_extension is only true while running a CREATE EXTENSION or ALTER
diff --git a/src/include/fmgr.h b/src/include/fmgr.h
index 1e3795de4a8..2930b61cee5 100644
--- a/src/include/fmgr.h
+++ b/src/include/fmgr.h
@@ -740,6 +740,8 @@ extern bool CheckFunctionValidatorAccess(Oid validatorOid, Oid functionOid);
  */
 extern PGDLLIMPORT char *Dynamic_library_path;
 
+extern char *find_in_path(const char *basename, const char *path, const char *path_param,
+						  const char *macro, const char *macro_val);
 extern void *load_external_function(const char *filename, const char *funcname,
 									bool signalNotFound, void **filehandle);
 extern void *lookup_external_function(void *filehandle, const char *funcname);

base-commit: e7a9496de90657e2161f68b3a5a9b2d9b0b7bb07
-- 
2.47.0

---

From: "David E. Wheeler" <[email-masked]>
Date: Mon, 11 Nov 2024 13:15:53 -0500

On Nov 11, 2024, at 02:16, Peter Eisentraut <[email-masked]> wrote:

> I implemented a patch along the lines Craig had suggested.

Oh, nice, thank you.

> It's a new GUC variable that is a path for extension control files.  It's called extension_control_path, and it works exactly the same way as dynamic_library_path.  Except that the magic token is called $system instead of $libdir.

I assume we can bikeshed these names later. :-)

> In fact, most of the patch is refactoring the routines in dfmgr.c to not hardcode dynamic_library_path but allow searching for any file in any path.  Once a control file is found, the other extension support files (script files and auxiliary control files) are looked for in the same directory.

What about shared libraries files?

> This works pretty much fine for the use cases that have been presented here, including installing extensions outside of the core installation tree (for CNPG and Postgres.app) and for testing uninstalled extensions (for Debian).

If I understand correctly, shared modules still lie in dynamic_library_path, yes? That makes things a bit more complicated, as the CNPG use case has to set up multiple persistent volumes to persist files put into various directories, notably sharedir and pkglibdir.

> but that doesn't because of some details in Makefile.global.  I think we can tweak that a little bit to make that work too.

In the broader extension organization RFC I’ve been working up[1], I propose a new Makefile prefix for the destination directory for an extension. It hinges on the idea that an extension has all of its files organized in a directory with the extension name, rather than separate params for data, pkglib, bin, etc.

> - With the current patch, if you install into datadir=/else/where/share, then you need to set extension_control_path=/else/where/share/extension.  This is a bit confusing.  Maybe we want to make the "extension" part implicit.

+1

> This disables the use of dynamic_library_path, so this whole idea of installing an extension elsewhere won't work that way.  The obvious solution is that extensions change this to just 'foo'.  But this will require a lot updating work for many extensions, or a lot of patching by packagers.

Since that’s set at build/install time, couldn’t the definition of `$libdir` here be changed to mean “the directory into which it’s being installed right now?”. Doesn’t seem necessary to search a path if the specific location is set at install time.

> Maybe we could devise some sort of rule that if the extension control file is found via the path outside of $system, then the leading $libdir is ignored.
> <v0-0001-extension_control_path.patch>

This is what I propose,[1] yes. If we redefine the organization of extension files to live in a single directory named for an extension, then once you’ve found the control files all the other files are there, too. But `$libdir` is presumably still meaningful, since the first time you call an extension function in a new connection, it just tries to load that location, it doesn’t go through the control file search process, AFAIK.

Perhaps I misunderstand, but I would like to talk through the implications of a more radical rethinking of extension file location along the lines of the other thread[2] and the RFC I’m working up based on them both[1], especially since there are a few other use cases that inform it.

A notable one is the idea Gabriele shared with me in Athens to be able to add an extension to a running CNPG pod by simply mounting a read-only volume with all the files it requires.

Best,

David

[1]: https://github.com/theory/justatheory/pull/7/files

---

From: Peter Eisentraut <[email-masked]>
Date: Thu, 5 Dec 2024 12:07:31 +0100

> path.  I think this would be pretty straightforward.

I've made a bit of progress on this patch, filled in some documentation 

> can tweak that a little bit to make that work too.

This works now.

> require a lot updating work for many extensions, or a lot of patching by 

I have solved this by just stripping off "$libdir/" from the front of 
the filename.  This works for now.  We can think about other ways to 
tweak this, perhaps, but I don't see any drawback to this in practice.

This patch is now complete enough for testing, I think.  As I mentioned 
earlier, I haven't updated pg_available_extensions() etc. to support the 
path, but that shouldn't prevent some testing.
From fdf1bd8abbb9aaff3747c57d653ef19f8be31fe8 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <[email-masked]>
Date: Thu, 5 Dec 2024 11:49:05 +0100
Subject: [PATCH v1] extension_control_path

TODO: Some utility functions such as pg_available_extensions() are not
adjusted to be aware of the path yet.
---
 doc/src/sgml/config.sgml                      | 68 +++++++++++++++
 doc/src/sgml/extend.sgml                      | 19 ++--
 doc/src/sgml/ref/create_extension.sgml        |  6 +-
 src/Makefile.global.in                        | 19 ++--
 src/backend/commands/extension.c              | 87 ++++++++++++++-----
 src/backend/utils/fmgr/dfmgr.c                | 76 ++++++++++------
 src/backend/utils/misc/guc_tables.c           | 13 +++
 src/backend/utils/misc/postgresql.conf.sample |  1 +
 src/include/commands/extension.h              |  2 +
 src/include/fmgr.h                            |  2 +
 10 files changed, 230 insertions(+), 63 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index e0c8325a39c..94ef9fb44d7 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -10476,6 +10476,74 @@ <title>Other Defaults</title>
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-extension-control-path" xreflabel="extension_control_path">
+      <term><varname>extension_control_path</varname> (<type>string</type>)
+      <indexterm>
+       <primary><varname>extension_control_path</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        A path to search for extensions, specifically extension control files
+        (<filename><replaceable>name</replaceable>.control</filename>).  The
+        remaining extension script and secondary control files are then loaded
+        from the same directory where the primary control file was found.
+        See <xref linkend="extend-extensions-files"/> for details.
+       </para>
+
+       <para>
+        The value for <varname>extension_control_path</varname> must be a
+        list of absolute directory paths separated by colons (or semi-colons
+        on Windows).  If a list element starts
+        with the special string <literal>$system</literal>, the
+        compiled-in <productname>PostgreSQL</productname> extension
+        directory is substituted for <literal>$system</literal>; this
+        is where the extensions provided by the standard
+        <productname>PostgreSQL</productname> distribution are installed.
+        (Use <literal>pg_config --sharedir</literal> to find out the name of
+        this directory.) For example:
+<programlisting>
+extension_control_path = '/usr/local/share/postgresql/extension:/home/my_project/share/extension:$system'
+</programlisting>
+        or, in a Windows environment:
+<programlisting>
+extension_control_path = 'C:\tools\postgresql\extension;H:\my_project\share\extension;$system'
+</programlisting>
+        Note that the path elements should typically end in
+        <literal>extension</literal> if the normal installation layouts are
+        followed.  (The value for <literal>$system</literal> already includes
+        the <literal>extension</literal> suffix.)
+       </para>
+
+       <para>
+        The default value for this parameter is
+        <literal>'$system'</literal>. If the value is set to an empty
+        string, the default <literal>'$system'</literal> is also assumed.
+       </para>
+
+       <para>
+        This parameter can be changed at run time by superusers and users
+        with the appropriate <literal>SET</literal> privilege, but a
+        setting done that way will only persist until the end of the
+        client connection, so this method should be reserved for
+        development purposes. The recommended way to set this parameter
+        is in the <filename>postgresql.conf</filename> configuration
+        file.
+       </para>
+
+       <para>
+        Note that if you set this parameter to be able to load extensions from
+        nonstandard locations, you will most likely also need to set <xref
+        linkend="guc-dynamic-library-path"/> to a correspondent location, for
+        example,
+<programlisting>
+extension_control_path = '/usr/local/share/postgresql/extension:$system'
+dynamic_library_path = '/usr/local/lib/postgresql:$libdir'
+</programlisting>
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry id="guc-gin-fuzzy-search-limit" xreflabel="gin_fuzzy_search_limit">
       <term><varname>gin_fuzzy_search_limit</varname> (<type>integer</type>)
       <indexterm>
diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml
index 218940ee5ce..0a20d06abfb 100644
--- a/doc/src/sgml/extend.sgml
+++ b/doc/src/sgml/extend.sgml
@@ -649,6 +649,11 @@ <title>Extension Files</title>
      control file can specify a different directory for the script file(s).
     </para>
 
+    <para>
+     Additional locations for extension control files can be configured using
+     the parameter <xref linkend="guc-extension-control-path"/>.
+    </para>
+
     <para>
      The file format for an extension control file is the same as for the
      <filename>postgresql.conf</filename> file, namely a list of
@@ -669,9 +674,9 @@ <title>Extension Files</title>
        <para>
         The directory containing the extension's <acronym>SQL</acronym> script
         file(s).  Unless an absolute path is given, the name is relative to
-        the installation's <literal>SHAREDIR</literal> directory.  The
-        default behavior is equivalent to specifying
-        <literal>directory = 'extension'</literal>.
+        the installation's <literal>SHAREDIR</literal> directory.  By default,
+        the script files are looked for in the same directory where the
+        control file was found.
        </para>
       </listitem>
      </varlistentry>
@@ -719,8 +724,8 @@ <title>Extension Files</title>
        <para>
         The value of this parameter will be substituted for each occurrence
         of <literal>MODULE_PATHNAME</literal> in the script file(s).  If it is not
-        set, no substitution is made.  Typically, this is set to
-        <literal>$libdir/<replaceable>shared_library_name</replaceable></literal> and
+        set, no substitution is made.  Typically, this is set to just
+        <literal><replaceable>shared_library_name</replaceable></literal> and
         then <literal>MODULE_PATHNAME</literal> is used in <command>CREATE
         FUNCTION</command> commands for C-language functions, so that the script
         files do not need to hard-wire the name of the shared library.
@@ -1808,6 +1813,10 @@ <title>Extension Building Infrastructure</title>
     setting <varname>PG_CONFIG</varname> to point to its
     <command>pg_config</command> program, either within the makefile
     or on the <literal>make</literal> command line.
+    You can also select a separate installation directory for your extension
+    by setting the <literal>make</literal> variable <varname>prefix</varname>
+    on the <literal>make</literal> command line.  (But this will then require
+    additional setup to get the server to find the extension there.)
    </para>
 
    <para>
diff --git a/doc/src/sgml/ref/create_extension.sgml b/doc/src/sgml/ref/create_extension.sgml
index ca2b80d669c..713abd9c494 100644
--- a/doc/src/sgml/ref/create_extension.sgml
+++ b/doc/src/sgml/ref/create_extension.sgml
@@ -90,8 +90,10 @@ <title>Parameters</title>
        <para>
         The name of the extension to be
         installed. <productname>PostgreSQL</productname> will create the
-        extension using details from the file
-        <literal>SHAREDIR/extension/</literal><replaceable class="parameter">extension_name</replaceable><literal>.control</literal>.
+        extension using details from the file <filename><replaceable
+        class="parameter">extension_name</replaceable>.control</filename>,
+        found via the server's extension control path (set by <xref
+        linkend="guc-extension-control-path"/>.)
        </para>
       </listitem>
      </varlistentry>
diff --git a/src/Makefile.global.in b/src/Makefile.global.in
index eac3d001211..7c1bffbea07 100644
--- a/src/Makefile.global.in
+++ b/src/Makefile.global.in
@@ -87,9 +87,19 @@ endif # not PGXS
 #
 # In a PGXS build, we cannot use the values inserted into Makefile.global
 # by configure, since the installation tree may have been relocated.
-# Instead get the path values from pg_config.
+# Instead get the path values from pg_config.  But users can specify
+# prefix explicitly, if they want to select their own installation
+# location.
 
-ifndef PGXS
+ifdef PGXS
+# Extension makefiles should set PG_CONFIG, but older ones might not
+ifndef PG_CONFIG
+PG_CONFIG = pg_config
+endif
+endif
+
+# This means: if ((not PGXS) or prefix)
+ifneq (,$(if $(PGXS),,1)$(prefix))
 
 # Note that prefix, exec_prefix, and datarootdir aren't defined in a PGXS build;
 # makefiles may only use the derived variables such as bindir.
@@ -147,11 +157,6 @@ localedir := @localedir@
 
 else # PGXS case
 
-# Extension makefiles should set PG_CONFIG, but older ones might not
-ifndef PG_CONFIG
-PG_CONFIG = pg_config
-endif
-
 bindir := $(shell $(PG_CONFIG) --bindir)
 datadir := $(shell $(PG_CONFIG) --sharedir)
 sysconfdir := $(shell $(PG_CONFIG) --sysconfdir)
diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index af6bd8ff426..9115a48d25f 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -69,6 +69,9 @@
 #include "utils/varlena.h"

+/* GUC */
@@ -328,6 +332,12 @@ is_extension_script_filename(const char *filename)
 
+/*
+ * TODO
+ *
+ * This is now only for finding/listing available extensions.  Rewrite to use
+ * path.  See further TODOs below.
+ */
 static char *
 get_extension_control_directory(void)
 {
@@ -341,16 +351,45 @@ get_extension_control_directory(void)
 	return result;
 }
 
+/*
+	char	   *ecp;
 	char	   *result;
 
+	Assert(control->name);
+
 	get_share_path(my_exec_path, sharepath);
-	result = (char *) palloc(MAXPGPATH);
-	snprintf(result, MAXPGPATH, "%s/extension/%s.control",
-			 sharepath, extname);
+	system_dir = psprintf("%s/extension", sharepath);
+
+	basename = psprintf("%s.control", control->name);
+
+	/*
+	 * find_in_path() does nothing if the path value is empty.  This is the
+	 * historical behavior for dynamic_library_path, but it makes no sense for
+	 * extensions.  So in that case, substitute a default value.
+	 */
+	ecp = Extension_control_path;
+	if (strlen(ecp) == 0)
+		ecp = "$system";
+	result = find_in_path(basename, Extension_control_path, "extension_control_path", "$system", system_dir);
+
+	if (result)
+	{
+		const char *p;
+
+		p = strrchr(result, '/');
+		Assert(p);
+		control->control_dir = pnstrdup(result, p - result);
+	}
 
 	return result;
 }
@@ -366,7 +405,7 @@ get_extension_script_directory(ExtensionControlFile *control)
 	 * installation's share directory.
 	 */
 	if (!control->directory)
-		return get_extension_control_directory();
+		return pstrdup(control->control_dir);
 
 	if (is_absolute_path(control->directory))
 		return pstrdup(control->directory);
@@ -444,27 +483,25 @@ parse_extension_control_file(ExtensionControlFile *control,
 	if (version)
 		filename = get_extension_aux_control_filename(control, version);
 	else
-		filename = get_extension_control_filename(control->name);
+		filename = find_extension_control_filename(control);
+
+	if (!filename)
+	{
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("extension \"%s\" is not available", control->name),
+				 errhint("The extension must first be installed on the system where PostgreSQL is running.")));
+	}
 
 	if ((file = AllocateFile(filename, "r")) == NULL)
 	{
-		if (errno == ENOENT)
+		/* no complaint for missing auxiliary file */
+		if (errno == ENOENT && version)
 		{
-			/* no complaint for missing auxiliary file */
-			if (version)
-			{
-				pfree(filename);
-				return;
-			}
-
-			/* missing control file indicates extension is not installed */
-			ereport(ERROR,
-					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-					 errmsg("extension \"%s\" is not available", control->name),
-					 errdetail("Could not open extension control file \"%s\": %m.",
-							   filename),
-					 errhint("The extension must first be installed on the system where PostgreSQL is running.")));
+			pfree(filename);
+			return;
 		}
+
 		ereport(ERROR,
 				(errcode_for_file_access(),
 				 errmsg("could not open extension control file \"%s\": %m",
@@ -2114,6 +2151,8 @@ RemoveExtensionById(Oid extId)
  * The system view pg_available_extensions provides a user interface to this
  * SRF, adding information about whether the extensions are installed in the
  * current DB.
+ *
+ * TODO: make aware of path
  */
 Datum
 pg_available_extensions(PG_FUNCTION_ARGS)
@@ -2194,6 +2233,8 @@ pg_available_extensions(PG_FUNCTION_ARGS)
  * The system view pg_available_extension_versions provides a user interface
  * to this SRF, adding information about which versions are installed in the
  * current DB.
+ *
+ * TODO: make aware of path
  */
 Datum
 pg_available_extension_versions(PG_FUNCTION_ARGS)
@@ -2366,6 +2407,8 @@ get_available_versions_for_extension(ExtensionControlFile *pcontrol,
  * directory.  That's not a bulletproof check, since the file might be
  * invalid, but this is only used for hints so it doesn't have to be 100%
  * right.
+ *
+ * TODO: make aware of path
  */
 bool
 extension_file_exists(const char *extensionName)
@@ -2445,6 +2488,8 @@ convert_requires_to_datum(List *requires)
 /*
  * This function reports the version update paths that exist for the
  * specified extension.
+ *
+ * TODO: make aware of path
  */
 Datum
 pg_extension_update_paths(PG_FUNCTION_ARGS)
diff --git a/src/backend/utils/fmgr/dfmgr.c b/src/backend/utils/fmgr/dfmgr.c
index 8e81ecc7491..2372d0944c0 100644
--- a/src/backend/utils/fmgr/dfmgr.c
+++ b/src/backend/utils/fmgr/dfmgr.c
@@ -71,8 +71,7 @@ static void incompatible_module_error(const char *libname,
 									  const Pg_magic_struct *module_magic_data) pg_attribute_noreturn();
 static char *expand_dynamic_library_name(const char *name);
 static void check_restricted_library_name(const char *name);
-static char *substitute_libpath_macro(const char *name);
-static char *find_in_dynamic_libpath(const char *basename);
+static char *substitute_path_macro(const char *str, const char *macro, const char *value);
 
@@ -398,7 +397,7 @@ incompatible_module_error(const char *libname,
@@ -413,17 +412,25 @@ expand_dynamic_library_name(const char *name)
 
 	Assert(name);
 
+	/*
+	 * If the value starts with "$libdir/", strip that.  This is because many
+	 * extensions have hardcoded '$libdir/foo' as their library name, which
+	 * prevents using the path.
+	 */
+	if (strncmp(name, "$libdir/", 8) == 0)
+		name += 8;
+
 	have_slash = (first_dir_separator(name) != NULL);
 
 	if (!have_slash)
 	{
@@ -433,14 +440,14 @@ expand_dynamic_library_name(const char *name)
 
 	if (!have_slash)
 	{
@@ -475,47 +482,60 @@ check_restricted_library_name(const char *name)
 
-	Assert(name != NULL);
+	Assert(str != NULL);

 

 /*

-	p = Dynamic_library_path;
+	/*
+	 * If the path variable is empty, don't do a path search.
+	 */
 	if (strlen(p) == 0)
 		return NULL;
 
@@ -532,7 +552,7 @@ find_in_dynamic_libpath(const char *basename)
 		if (piece == p)
 			ereport(ERROR,
 					(errcode(ERRCODE_INVALID_NAME),
-					 errmsg("zero-length component in parameter \"dynamic_library_path\"")));
+					 errmsg("zero-length component in parameter \"%s\"", path_param)));
 
 		if (piece == NULL)
 			len = strlen(p);
@@ -542,7 +562,7 @@ find_in_dynamic_libpath(const char *basename)
 		piece = palloc(len + 1);
 		strlcpy(piece, p, len + 1);

@@ -551,13 +571,13 @@ find_in_dynamic_libpath(const char *basename)

 
index 8cf1afbad20..5a280f62b77 100644
@@ -4252,6 +4253,18 @@ struct config_string ConfigureNamesString[] =
 		NULL, NULL, NULL
 	},
 
+	{
+		{"extension_control_path", PGC_SUSET, CLIENT_CONN_OTHER,
+			gettext_noop("Sets the path for extension control files."),
+			gettext_noop("The remaining extension script and secondary control files are then loaded "
+						 "from the same directory where the primary control file was found."),
+			GUC_SUPERUSER_ONLY
+		},
+		&Extension_control_path,
+		"$system",
+		NULL, NULL, NULL
+	},
+
 	{
 		{"krb_server_keyfile", PGC_SIGHUP, CONN_AUTH_AUTH,
 			gettext_noop("Sets the location of the Kerberos server key file."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index a2ac7575ca7..83e72b33fb7 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -774,6 +774,7 @@
 # - Other Defaults -

 
+/* GUC */
 
 /*

base-commit: 71cb352904c1833fe067d6f191269710fe2ca06f
-- 
2.47.1

---

From: Matheus Alcantara <[email-masked]>
Date: Mon, 24 Feb 2025 10:33:34 -0300

Hi,

> path, but that shouldn't prevent some testing.

To help with this patch I'm attaching a new version with the remaining TODOs
fixed and also with a new TAP test.

Thoughts?

-- 
Matheus Alcantara

---

From: Andrew Dunstan <[email-masked]>
Date: Tue, 25 Feb 2025 07:45:10 -0500

> fixed and also with a new TAP test.

I think your additions generally look good. We should be able to 
simplify this:

+    system_dir = psprintf("%s/extension", sharepath);
+    ecp = system_dir;
+
+    if (strlen(Extension_control_path) == 0)
+    {
+        paths = lappend(paths, ecp);
+    }

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

---

From: Matheus Alcantara <[email-masked]>
Date: Tue, 25 Feb 2025 17:29:29 -0300

Thanks for reviewing!

> +        paths = lappend(paths, ecp);
Fixed on the attached v3.

-- 
Matheus Alcantara

---

From: Matheus Alcantara <[email-masked]>
Date: Fri, 28 Feb 2025 12:35:51 -0300

Hi

> Fixed on the attached v3.
After I've sent the v3 patch I noticed that the tests were failing on windows.
The problem was on TAP test that was using ":" as a separator on
extension_control_path and also the path was not being escaped correctly
resulting in a wrong path configuration.

Attached v4 with all these fixes.

-- 
Matheus Alcantara

---

From: Gabriele Bartolini <[email-masked]>
Date: Sat, 1 Mar 2025 10:15:37 +0100

Hi everyone,

I have finally been able to test the patch in a Kubernetes environment with
CloudNativePG, thanks to Niccolò Fei and Marco Nenciarini, who created a
pilot patch for CloudNativePG (
https://github.com/cloudnative-pg/cloudnative-pg/pull/6546).

In the meantime, Kubernetes is likely adding the ImageVolume feature
starting from the upcoming version 1.33. I will write a blog post soon
about how CloudNativePG will benefit from this feature. See
https://github.com/kubernetes/enhancements/issues/4639.

Although the steps are not easily reproducible by everyone, I can confirm
that I successfully mounted a volume in the Postgres pod using a container
image that includes only pgvector (with a size of 1.6MB - see
https://github.com/EnterpriseDB/pgvector/blob/dev/5645/Dockerfile.cnpg).

By setting:

dynamic_library_path = '$libdir:/extensions/pgvector/lib'
extension_control_path = '$system:/extensions/pgvector/share'

I was able to run the following queries:

postgres=# SELECT * FROM pg_available_extensions WHERE name = 'vector';
-[ RECORD 1 ]-----+-----------------------------------------------------
name              | vector
default_version   | 0.8.0
installed_version |
comment           | vector data type and ivfflat and hnsw access methods

postgres=# SELECT * FROM pg_available_extensions WHERE name = 'vector';
-[ RECORD 1 ]-----+-----------------------------------------------------
name              | vector
default_version   | 0.8.0
installed_version |
comment           | vector data type and ivfflat and hnsw access methods

I also successfully ran the following:

postgres=# SELECT * FROM pg_extension_update_paths('vector');

By emptying the content of `extension_control_path`, the vector extension
disappeared from the list.

postgres=# SHOW extension_control_path ;
 extension_control_path
------------------------
 $system
(1 row)

postgres=# SELECT * FROM pg_available_extensions WHERE name = 'vector';
 name | default_version | installed_version | comment
------+-----------------+-------------------+---------
(0 rows)

postgres=# SELECT * FROM pg_available_extension_versions WHERE name =
'vector';
 name | version | installed | superuser | trusted | relocatable | schema |
requires | comment
------+---------+-----------+-----------+---------+-------------+--------+----------+---------
(0 rows)

In my opinion, the patch already helps a lot and does what I can reasonably
expect from a first iteration of improvements in enabling immutable
container images that ship a self-contained extension to be dynamically
loaded and unloaded from a Postgres cluster in Kubernetes. From here, it is
all about learning how to improve things with an exploratory mindset with
future versions of Postgres and Kubernetes. It's a long journey but this is
a fundamental step in the right direction.

Let me know if there's more testing for me to do. The documentation looks
clear to me.

Thank you to everyone who contributed to this patch, from the initial
discussions to the development phase. I sincerely hope this is included in
Postgres 18.

Ciao,
Gabriele

-- 
Gabriele Bartolini
VP, Chief Architect, Kubernetes
enterprisedb.com

---

From: Gabriele Bartolini <[email-masked]>
Date: Mon, 3 Mar 2025 14:39:47 +0100

As promised, here is a blog article that provides more context and
information about what this feature will mean in Kubernetes with
CloudNativePG:
https://www.gabrielebartolini.it/articles/2025/03/the-immutable-future-of-postgresql-extensions-in-kubernetes-with-cloudnativepg/

Thanks,
Gabriele

On Sat, 1 Mar 2025 at 10:15, Gabriele Bartolini <
[email-masked]> wrote:

> Hi everyone,
>
> I have finally been able to test the patch in a Kubernetes environment
> with CloudNativePG, thanks to Niccolò Fei and Marco Nenciarini, who created
> a pilot patch for CloudNativePG (
> In my opinion, the patch already helps a lot and does what I can
> reasonably expect from a first iteration of improvements in enabling
> immutable container images that ship a self-contained extension to be
> dynamically loaded and unloaded from a Postgres cluster in Kubernetes. From
> here, it is all about learning how to improve things with an exploratory
> mindset with future versions of Postgres and Kubernetes. It's a long
> journey but this is a fundamental step in the right direction.
>

---

From: "David E. Wheeler" <[email-masked]>
Date: Mon, 3 Mar 2025 10:05:18 -0500

On Mar 3, 2025, at 08:39, Gabriele Bartolini <[email-masked]> wrote:

> As promised, here is a blog article that provides more context and information about what this feature will mean in Kubernetes with CloudNativePG: https://www.gabrielebartolini.it/articles/2025/03/the-immutable-future-of-postgresql-extensions-in-kubernetes-with-cloudnativepg/

Very nice writeup, thank you. Makes me wish for the bandwidth to get back to and start refining the PGXN OCI RFC to specify tags and/or metadata for the distribution of different contents, including:

* Full extension distribution (as currently written)
* Distribution with only pkglibdir and sharedir contents
* Distribution with only pkglibdir contents
* Distribution with only sharedir contents

I think we could come up with a standard that would work very nicely for a variety of use cases.

Will have to see if I can scrounge up the time for it.

Best,

David

---

From: "David E. Wheeler" <[email-masked]>
Date: Mon, 3 Mar 2025 10:10:44 -0500

On Mar 3, 2025, at 10:05, David E. Wheeler <[email-masked]> wrote:

> Very nice writeup, thank you. Makes me wish for the bandwidth to get back to and start refining the PGXN OCI RFC

Forgot to link to the POC[1]. The RFC[2] is not OCI-specific, but the POC demonstrates the “full content” version. Will likely want to modify the binary distribution RFC to adopt a standard layout that allows for volume mounting to “install” an extension.

Best,

David

1: https://justatheory.com/2024/06/trunk-oci-poc/
2: https://github.com/pgxn/rfcs/pull/2

---

From: Matheus Alcantara <[email-masked]>
Date: Mon, 3 Mar 2025 15:45:59 -0300

Hi, attached a new v5 with some minor improvements on TAP tests:

- Add a proper test name for all test cases
- Add CREATE EXTENSION command execution
- Change the assert on pg_available_extensions and
  pg_available_extension_versions to validate the row content

Also rebased with master

-- 
Matheus Alcantara

---

From: Peter Eisentraut <[email-masked]>
Date: Thu, 6 Mar 2025 14:46:08 +0100

>    pg_available_extension_versions to validate the row content

This looks very good to me.  I have one issue to point out:  The logic 
in get_extension_control_directories() needs to be a little bit more 
careful to align with the rules in find_in_path().  For example, it 
should use first_path_var_separator() to get the platform-specific path 
separator, and probably also substitute_path_macro() and 
canonicalize_path() etc., to keep everything consistent.  (Maybe it 
would be ok to move the function to dfmgr.c to avoid having to export 
too many things from there.)

Independent of that, attached is a small patch that suggests to use the 
newer foreach_ptr() macro in some places.

From 35c2106558095e74359cec58b9631fa19ed937b3 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <[email-masked]>
Date: Thu, 6 Mar 2025 14:28:08 +0100
Subject: [PATCH] Use foreach_ptr

---
 src/backend/commands/extension.c | 20 ++++++--------------
 1 file changed, 6 insertions(+), 14 deletions(-)

diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
index 7e8a28e4064..4bdb20aaf54 100644
--- a/src/backend/commands/extension.c
+++ b/src/backend/commands/extension.c
@@ -51,10 +51,10 @@
 #include "commands/defrem.h"
 #include "commands/extension.h"
 #include "commands/schemacmds.h"
-#include "nodes/pg_list.h"
 #include "funcapi.h"
 #include "mb/pg_wchar.h"
 #include "miscadmin.h"
+#include "nodes/pg_list.h"
 #include "nodes/queryjumble.h"
 #include "storage/fd.h"
 #include "tcop/utility.h"
@@ -2183,20 +2183,17 @@ Datum
 pg_available_extensions(PG_FUNCTION_ARGS)
 {
 	ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+	List	   *locations;
 	DIR		   *dir;
 	struct dirent *de;
-	List	   *locations;
-	ListCell   *cell;
 
 	/* Build tuplestore to hold the result rows */
 	InitMaterializedSRF(fcinfo, 0);
 
 	locations = get_extension_control_directories();
 
-	foreach(cell, locations)
+	foreach_ptr(char, location, locations)
 	{
-		char	   *location = (char *) lfirst(cell);
-
 		dir = AllocateDir(location);
 
 		/*
@@ -2271,7 +2268,6 @@ pg_available_extension_versions(PG_FUNCTION_ARGS)
 {
 	ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
 	List	   *locations;
-	ListCell   *cell;
 	DIR		   *dir;
 	struct dirent *de;
 
@@ -2279,10 +2275,9 @@ pg_available_extension_versions(PG_FUNCTION_ARGS)
 	InitMaterializedSRF(fcinfo, 0);
 
 	locations = get_extension_control_directories();
-	foreach(cell, locations)
-	{
-		char	   *location = (char *) lfirst(cell);
 
+	foreach_ptr(char, location, locations)
+	{
 		dir = AllocateDir(location);
 
 		/*
@@ -2449,16 +2444,13 @@ extension_file_exists(const char *extensionName)
 {
 	bool		result = false;
 	List	   *locations;
-	ListCell   *cell;
 	DIR		   *dir;
 	struct dirent *de;
 
 	locations = get_extension_control_directories();
 
-	foreach(cell, locations)
+	foreach_ptr(char, location, locations)
 	{
-		char	   *location = (char *) lfirst(cell);
-
 		dir = AllocateDir(location);
 
 		/*
-- 
2.48.1

---

From: Matheus Alcantara <[email-masked]>
Date: Mon, 10 Mar 2025 17:25:40 -0300

Hi,

Thanks for reviewing and suggestions!

On Thu, Mar 6, 2025 at 10:46 AM Peter Eisentraut <[email-masked]> wrote:
> canonicalize_path() etc., to keep everything consistent.
>
I fixed this hardcoded path separator issue on the TAP test and forgot
to fix it also on code, sorry, fixed on this new version.

I also spent some time investigating why the tests on Windows were still
passing even using a wrong path separator.

Consider extension_control_path = '$system;C:\custom\path'

When running on Windows, the get_extension_control_directories was
returning [$system;C:, \custom\path] and for somehow the \custom\path
was successfully being read and since the tests was only referencing the
extension on this custom path everything was passing, but querying for
an extension that is only on $system was resulting in an empty query
result. In the attached patch I also included a new test case to query
on pg_available_extensions for an extension that is installed on the
$system, so we can ensure that extensions in both paths can be used
correctly.

> (Maybe it would be ok to move the function to dfmgr.c to avoid having
> to export too many things from there.)
>
I've exported substitute_path_macro because adding a new function on
dfmgr would require #include nodes/pg_list.h and I'm not sure what
approach would be better, please let me know what you think.

-- 
Matheus Alcantara

---

From: Peter Eisentraut <[email-masked]>
Date: Tue, 11 Mar 2025 16:58:55 +0100

> approach would be better, please let me know what you think.

Yes, that structure looks ok.  But you can remove one level of block in 
get_extension_control_directories().

I found a bug that was already present in my earlier patch versions:

@@ -423,7 +424,7 @@ find_extension_control_filename(ExtensionControlFile 
*control)
     ecp = Extension_control_path;
     if (strlen(ecp) == 0)
         ecp = "$system";
-   result = find_in_path(basename, Extension_control_path, 
"extension_control_path", "$system", system_dir);
+   result = find_in_path(basename, ecp, "extension_control_path", 
"$system", system_dir);

Without this, it won't work if you set extension_control_path empty. 
(Maybe add a test for that?)

I think this all works now, but I think the way 
pg_available_extensions() works is a bit strange and inefficient.  After 
it finds a candidate control file, it calls 
read_extension_control_file() with the extension name, that calls 
parse_extension_control_file(), that calls 
find_extension_control_filename(), and that calls find_in_path(), which 
searches that path again!

There should be a simpler way into this.  Maybe 
pg_available_extensions() should fill out the ExtensionControlFile 
structure itself, set ->control_dir with where it found it, then call 
directly to parse_extension_control_file(), and that should skip the 
finding if the directory is already set.  Or something similar.

---

From: Matheus Alcantara <[email-masked]>
Date: Wed, 12 Mar 2025 10:17:19 -0300

> get_extension_control_directories().
Sorry, missed during debugging. Fixed

> (Maybe add a test for that?)
Fixed, and also added a new test case for this.

> finding if the directory is already set.  Or something similar.
Good catch. I fixed this by creating a new function to construct the
ExtensionControlFile and changed the pg_available_extensions to set the
control_dir. The read_extension_control_file was also changed to just
call this new function constructor. I implemented the logic to check if
the control_dir is already set on parse_extension_control_file because
it seems to me that make more sense to not call
find_extension_control_filename instead of putting this logic there
since we already set the control_dir when we find the control file, and
having the logic to set the control_dir or skip the find_in_path seems
more confusing on this function instead of on
parse_extension_control_file. Please let me know what you think.

-- 
Matheus Alcantara

---

From: Peter Eisentraut <[email-masked]>
Date: Wed, 19 Mar 2025 07:42:44 +0100

> parse_extension_control_file. Please let me know what you think.

Committed that, thanks.

A small tweak I made was to replace palloc+snprintf by psprintf.  Maybe 
you were not aware that that function exists.

I also simplified the error handling in parse_extension_control_file() a 
bit.  If we pass in a control directory (which is the new code we're 
adding), then we can assume that we already found the file earlier, and 
then if we now don't find it, then we should just report the file system 
error instead of the "you should install this extension first" error. 
It's kind of a "can't happen" error anyway, so the different is small.

---

From: Gabriele Bartolini <[email-masked]>
Date: Wed, 19 Mar 2025 08:03:08 +0100

Thanks everyone for making this happen.

Ciao,
Gabriele

---

From: Christoph Berg <[email-masked]>
Date: Wed, 19 Mar 2025 15:58:59 +0100

Re: Peter Eisentraut
> Committed that, thanks.

Awesome, thanks!

It works perfectly for the Debian "test extension packages at build
time" use case, replacing our old extension_destdir patch.

PKGARGS="--pgoption extension_control_path=$PWD/debian/$PACKAGE/usr/share/postgresql/$v/extension:\$system --pgoption dynamic_library_path=$PWD/debian/$PACKAGE/usr/lib/postgresql/$v/lib:/usr/lib/postgresql/$v/lib"

https://salsa.debian.org/postgresql/postgresql-common/-/commit/3792eea42e4dcef39b5c8d99f63deb8091ef9c46

Christoph

---

From: "David E. Wheeler" <[email-masked]>
Date: Wed, 19 Mar 2025 13:29:29 -0400

> Committed that, thanks.

🎉

I’ve been meaning to test the patch again, so here goes.

First thing I notice is that prefix= uses the magic to insert “postgresql” into the path if it’s not already there:

``` console
❯ make PG_CONFIG=~/dev/c/postgres/pgsql-devel/bin/pg_config prefix=/Users/david/Downloads install
/opt/homebrew/bin/gmkdir -p '/Users/david/Downloads/share/postgresql/extension'
/opt/homebrew/bin/gmkdir -p '/Users/david/Downloads/share/postgresql/extension'
/opt/homebrew/bin/gmkdir -p '/Users/david/Downloads/share/doc//postgresql/extension'
/opt/homebrew/bin/ginstall -c -m 644 .//pair.control '/Users/david/Downloads/share/postgresql/extension/'
/opt/homebrew/bin/ginstall -c -m 644 .//sql/pair--0.1.2.sql .//sql/pair--unpackaged--0.1.2.sql  '/Users/david/Downloads/share/postgresql/extension/'
/opt/homebrew/bin/ginstall -c -m 644 .//doc/pair.md '/Users/david/Downloads/share/doc//postgresql/extension/‘
```

I think this should at least be documented, but generally feels unexpected to me. I’ve attached a patch that fleshes out the docs, along with an example of setting `extension_control_path` and `dynamic_library_path` to use the locations. It might not have the information right about the need for “postgresql” or “pgsql” in the path. Back in 2003[1] it was just “postgres”, but I couldn’t find the logic for it just now.

Everything else works very nicely except for extensions that use the Makefile `MODULEDIR` variable to install all of the share files except the control file into a particular directory, and the `directory` in the control file so that the files can be found. Here’s semver[2], which has both:

```console
❯ make PG_CONFIG=~/dev/c/postgres/pgsql-devel/bin/pg_config prefix=/Users/david/Downloads/postgresql install
/opt/homebrew/bin/gmkdir -p '/Users/david/Downloads/postgresql/share/extension'
/opt/homebrew/bin/gmkdir -p '/Users/david/Downloads/postgresql/share/semver'
/opt/homebrew/bin/gmkdir -p '/Users/david/Downloads/postgresql/lib'
/opt/homebrew/bin/gmkdir -p '/Users/david/Downloads/postgresql/share/doc//semver'
/opt/homebrew/bin/ginstall -c -m 644 .//semver.control '/Users/david/Downloads/postgresql/share/extension/'
/opt/homebrew/bin/ginstall -c -m 644 .//sql/semver--0.10.0--0.11.0.sql .//sql/semver--0.11.0--0.12.0.sql .//sql/semver--0.12.0--0.13.0.sql .//sql/semver--0.13.0--0.15.0.sql .//sql/semver--0.15.0--0.16.0.sql .//sql/semver--0.16.0--0.17.0.sql .//sql/semver--0.17.0--0.20.0.sql .//sql/semver--0.2.1--0.2.4.sql .//sql/semver--0.2.4--0.3.0.sql .//sql/semver--0.20.0--0.21.0.sql .//sql/semver--0.21.0--0.22.0.sql .//sql/semver--0.22.0--0.30.0.sql .//sql/semver--0.3.0--0.4.0.sql .//sql/semver--0.30.0--0.31.0.sql .//sql/semver--0.31.0--0.31.1.sql .//sql/semver--0.31.1--0.31.2.sql .//sql/semver--0.31.2--0.32.0.sql .//sql/semver--0.32.0--0.32.1.sql .//sql/semver--0.32.1--0.40.0.sql .//sql/semver--0.32.1.sql .//sql/semver--0.40.0.sql .//sql/semver--0.5.0--0.10.0.sql .//sql/semver--unpackaged--0.2.1.sql .//sql/semver.sql  '/Users/david/Downloads/postgresql/share/semver/'
/opt/homebrew/bin/ginstall -c -m 755  src/semver.dylib '/Users/david/Downloads/postgresql/lib/'
/opt/homebrew/bin/gmkdir -p '/Users/david/Downloads/postgresql/lib/bitcode/src/semver'
/opt/homebrew/bin/gmkdir -p '/Users/david/Downloads/postgresql/lib/bitcode'/src/semver/src/
/opt/homebrew/bin/ginstall -c -m 644 src/semver.bc '/Users/david/Downloads/postgresql/lib/bitcode'/src/semver/src/
cd '/Users/david/Downloads/postgresql/lib/bitcode' && /opt/homebrew/Cellar/llvm/19.1.7_1/bin/llvm-lto -thinlto -thinlto-action=thinlink -o src/semver.index.bc src/semver/src/semver.bc
/opt/homebrew/bin/ginstall -c -m 644 .//doc/semver.mmd '/Users/david/Downloads/postgresql/share/doc//semver/‘
```

Following `MODULEDIR=semver`, it puts the SQL files into `share/semver/` instead of `share/extension/`, as expected, but then, even though the control file has `directory=semver`, it can’t load them:

```pgsql
david=# create extension semver;
ERROR:  could not open directory "/Users/david/dev/c/postgres/pgsql-devel/share/semver": No such file or directory
```

Looks like it’s only looking in the `semver` subdirectory under $libdir and not the whole path.

But given that the `directory` variable in the control file can be a full path, I don’t see that there’s much of a way to generalize a solution. I guess there are three options:

1. If directory is a full path, try to load the files there. It probably already works that way, though I haven’t tired it.

2. If the directory is not a full path, check for it under each directory in `extension_control_path`? But no, that points to `share/extension`, not `share`, so it can’t really searched unless it also lops off `extension` from the end of each path.

3. Drop support for MODULEDIR and directory.

I think I’d opt for #3, personally, just to simplify things.

Anyway, I then built envvar, a C extension with no `directory` configuration, and it worked perfectly.

I will say, though, that I will kind of miss being able to run `make install` without first running `make`, as the `prefix` variable does not work with `make`.

Best,

David

[1]: https://postgr.es/m/[email-masked]
[2]: https://github.com/theory/pg-semver/
[3]: https://github.com/theory/pg-envvar

---

From: Tom Lane <[email-masked]>
Date: Wed, 19 Mar 2025 14:55:58 -0400

> Committed that, thanks.

Buildfarm member snakefly doesn't like this too much.  Since no other
animals have failed, I guess it must be about local conditions on
that machine, but the report is pretty opaque:

# +++ tap check in src/test/modules/test_extensions +++

#   Failed test '$system extension is installed correctly on pg_available_extensions'
#   at t/001_extension_control_path.pl line 69.
#          got: 'f'
#     expected: 't'

#   Failed test '$system extension is installed correctly on pg_available_extensions with empty extension_control_path'
#   at t/001_extension_control_path.pl line 76.
#          got: 'f'
#     expected: 't'
# Looks like you failed 2 tests of 5.
[06:43:53] t/001_extension_control_path.pl .. 
Dubious, test returned 2 (wstat 512, 0x200)
Failed 2/5 subtests

Looking at the test, it presupposes that "amcheck" must be an
available extension.  I do not see anything that guarantees
that that's so, though.  It'd fail if contrib hasn't been
installed.  Is there a reason to use "amcheck" rather than
something more certainly available, like "plpgsql"?

			regards, tom lane

---

From: Matheus Alcantara <[email-masked]>
Date: Wed, 19 Mar 2025 16:25:54 -0300

> something more certainly available, like "plpgsql"?

There is no specific reason to use "amcheck" instead of "plpgsql". Attached a
patch with this change, sorry about that.

(Not sure if we should also improve the message to make the test failure less
opaque?)

-- 
Matheus Alcantara

---

From: Tom Lane <[email-masked]>
Date: Wed, 19 Mar 2025 15:42:46 -0400

> (Not sure if we should also improve the message to make the test failure less

Yeah, I was wondering how to do that.  The earlier tests in that
script show the whole row from pg_available_extensions, not just a
bool ... but that doesn't help if the problem is we don't find a row.

			regards, tom lane

---

From: Peter Eisentraut <[email-masked]>
Date: Thu, 20 Mar 2025 11:01:37 +0100

> patch with this change, sorry about that.

Committed.

I was able to reproduce the problem from scratch using:

./configure ...
make  # no contrib
make -C src/test/modules/test_extensions check

So it depended on in which order you build the various components.

---

From: Tom Lane <[email-masked]>
Date: Thu, 20 Mar 2025 10:35:51 -0400

Peter Eisentraut <[email-masked]> writes:
>>> Buildfarm member snakefly doesn't like this too much.

> So it depended on in which order you build the various components.

That makes sense, but I wonder how snakefly hit it while other BF
animals did not.  It's running a reasonably up-to-date BF client
version and there's nothing odd-looking about its configuration.

Anyway, I see snakefly is green now so that tweak did fix it.

			regards, tom lane

---

From: Andrew Dunstan <[email-masked]>
Date: Thu, 20 Mar 2025 10:53:17 -0400

On 2025-03-19 We 2:55 PM, Tom Lane wrote:
> Peter Eisentraut<[email-masked]> writes:
>> Committed that, thanks.

I think something else must be going on. The failure in question came 
after the step "install-contrib" succeeded, and the log file for that shows:

make[1]: Entering directory `/opt/postgres/build-farm-18/HEAD/pgsql.build/contrib/amcheck'
/usr/bin/mkdir -p '/opt/postgres/build-farm-18/HEAD/pgsql.build/tmp_install/opt/postgres/build-farm-18/HEAD/inst/lib'
/usr/bin/mkdir -p '/opt/postgres/build-farm-18/HEAD/pgsql.build/tmp_install/opt/postgres/build-farm-18/HEAD/inst/share/extension'
/usr/bin/mkdir -p '/opt/postgres/build-farm-18/HEAD/pgsql.build/tmp_install/opt/postgres/build-farm-18/HEAD/inst/share/extension'
/usr/bin/install -c -m 755  amcheck.so '/opt/postgres/build-farm-18/HEAD/pgsql.build/tmp_install/opt/postgres/build-farm-18/HEAD/inst/lib/amcheck.so'
/usr/bin/install -c -m 644 ./amcheck.control '/opt/postgres/build-farm-18/HEAD/pgsql.build/tmp_install/opt/postgres/build-farm-18/HEAD/inst/share/extension/'
/usr/bin/install -c -m 644 ./amcheck--1.3--1.4.sql ./amcheck--1.2--1.3.sql ./amcheck--1.1--1.2.sql ./amcheck--1.0--1.1.sql ./amcheck--1.0.sql  '/opt/postgres/build-farm-18/HEAD/pgsql.build/tmp_install/opt/postgres/build-farm-18/HEAD/inst/share/extension/'
make[1]: Leaving directory `/opt/postgres/build-farm-18/HEAD/pgsql.build/contrib/amcheck'

(wondering if this another of these cases where the "path includes postgres" thing bites us, and we're looking in the wrong place)

cheers

andrew

--
Andrew Dunstan
EDB:https://www.enterprisedb.com

---

From: Andrew Dunstan <[email-masked]>
Date: Thu, 20 Mar 2025 18:38:10 -0400

> (wondering if this another of these cases where the "path includes postgres" thing bites us, and we're looking in the wrong place)

Nope, testing shows it's not that, so I am rather confused about what 
was going on.

cheers

andrew

---

From: Matheus Alcantara <[email-masked]>
Date: Fri, 21 Mar 2025 12:52:55 -0300

On Thu, Mar 20, 2025 at 7:38 PM Andrew Dunstan <[email-masked]> wrote:
>> I think something else must be going on. The failure in question came after the step "install-contrib" succeeded, and the log file for that shows:
>>
>>
> Nope, testing shows it's not that, so I am rather confused about what was going on.
>

I'm not sure if I'm checking on the right place [1] but it seems that the
Contrib and ContribInstall is executed after Check step which causes this test
failure?

'steps_completed' => [
                      'SCM-checkout',
                      'Configure',
                      'Build',
                      'Check',
                      'Contrib',
                      'TestModules',
                      'Install',
                      'ContribInstall',
                      'TestModulesInstall',
                      'MiscCheck',
                      ...
                      ]

[1] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=snakefly&dt=2025-03-20%2009%3A46%3A05
-- 
Matheus Alcantara

---

From: Andrew Dunstan <[email-masked]>
Date: Fri, 21 Mar 2025 12:38:58 -0400

> [1] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=snakefly&dt=2025-03-20%2009%3A46%3A05

No. In the buildfarm, the Check step only runs the core regression 
tests, not any TAP tests. The above shows fairly clearly that the 
failure occurred after the ContribInstall step, which is what's puzzling me.

cheers

andrew

---

From: Tom Lane <[email-masked]>
Date: Fri, 21 Mar 2025 12:42:28 -0400

> Contrib and ContribInstall is executed after Check step which causes this test

No, this is not failing in Check.

I did just notice a clue though: on snakefly, the failing step's
log [1] includes

make[1]: Leaving directory `/opt/postgres/build-farm-18/HEAD/pgsql.build/src/backend'
rm -rf '/opt/postgres/build-farm-18/HEAD/pgsql.build'/tmp_install
/usr/bin/mkdir -p '/opt/postgres/build-farm-18/HEAD/pgsql.build'/tmp_install/log
make -C '../../../..' DESTDIR='/opt/postgres/build-farm-18/HEAD/pgsql.build'/tmp_install install >'/opt/postgres/build-farm-18/HEAD/pgsql.build'/tmp_install/log/install.log 2>&1
make -j1  checkprep >>'/opt/postgres/build-farm-18/HEAD/pgsql.build'/tmp_install/log/install.log 2>&1
PATH="/opt/postgres/build-farm-18/HEAD/pgsql.build/tmp_install/opt/postgres/build-farm-18/HEAD/inst/bin:/opt/postgres/build-farm-18/HEAD/pgsql.build/src/test/modules/test_extensions:$PATH" LD_LIBRARY_PATH="/opt/postgres/build-farm-18/HEAD/pgsql.build/tmp_install/opt/postgres/build-farm-18/HEAD/inst/lib:$LD_LIBRARY_PATH" INITDB_TEMPLATE='/opt/postgres/build-farm-18/HEAD/pgsql.build'/tmp_install/initdb-template  initdb --auth trust --no-sync --no-instructions --lc-messages=C --no-clean '/opt/postgres/build-farm-18/HEAD/pgsql.build'/tmp_install/initdb-template >>'/opt/postgres/build-farm-18/HEAD/pgsql.build'/tmp_install/log/initdb-template.log 2>&1
echo "# +++ regress check in src/test/modules/test_extensions +++" && PATH="/opt/postgres/build-farm-18/HEAD/pgsql.build/tmp_install/opt/postgres/build-farm-18/HEAD/inst/bin:/opt/postgres/build-farm-18/HEAD/pgsql.build/src/test/modules/test_extensions:$PATH" LD_LIBRARY_PATH="/opt/postgres/build-farm-18/HEAD/pgsql.build/tmp_install/opt/postgres/build-farm-18/HEAD/inst/lib:$LD_LIBRARY_PATH" INITDB_TEMPLATE='/opt/postgres/build-farm-18/HEAD/pgsql.build'/tmp_install/initdb-template  ../../../../src/test/regress/pg_regress --temp-instance=./tmp_check --inputdir=. --bindir=  --temp-config=/opt/postgres/build-farm-18/tmp/buildfarm-C9Iy3s/bfextra.conf  --no-locale --port=5678 --dbname=contrib_regression test_extensions test_extdepend
# +++ regress check in src/test/modules/test_extensions +++
# initializing database system by running initdb

showing that the step made its own tmp_install, and that only the core
"install" process was executed, so the lack of amcheck in that install
tree is not surprising.  But concurrent runs on other animals, eg [2],
don't show a tmp_install rebuild happening.  So those are using an
installation tree that *does* include contrib modules.

So what this comes down to is "why is snakefly doing a fresh install
here?".  I don't know the buildfarm client well enough to identify
probable causes.  I do note that Makefile.global.in conditionalizes
tmp_install rebuild on several variables:

temp-install: | submake-generated-headers
ifndef NO_TEMP_INSTALL
ifneq ($(abs_top_builddir),)
ifeq ($(MAKELEVEL),0)
	rm -rf '$(abs_top_builddir)'/tmp_install
	$(MKDIR_P) '$(abs_top_builddir)'/tmp_install/log
	$(MAKE) -C '$(top_builddir)' DESTDIR='$(abs_top_builddir)'/tmp_install install >'$(abs_top_builddir)'/tmp_install/log/install.log 2>&1
	$(MAKE) -j1 $(if $(CHECKPREP_TOP),-C $(CHECKPREP_TOP),) checkprep >>'$(abs_top_builddir)'/tmp_install/log/install.log 2>&1

I think we've had trouble before with that MAKELEVEL test...

			regards, tom lane

[1] https://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=snakefly&dt=2025-03-20%2009%3A46%3A05&stg=module-test_extensions-check
[2] https://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=alligator&dt=2025-03-19%2006%3A10%3A38&stg=module-test_extensions-check

---

From: Matheus Alcantara <[email-masked]>
Date: Fri, 21 Mar 2025 18:52:20 -0300

Hi David, thanks for testing!

On Wed, Mar 19, 2025 at 2:29 PM David E. Wheeler <[email-masked]> wrote:
>
> First thing I notice is that prefix= uses the magic to insert
> “postgresql” into the path if it’s not already there:
>
> ``` console
> I think this should at least be documented, but generally feels
> unexpected to me. I’ve attached a patch that fleshes out the docs,
> along with an example of setting `extension_control_path` and
> `dynamic_library_path` to use the locations. It might not have the
> information right about the need for “postgresql” or “pgsql” in the
> path.

Did you miss to attach the patch?

> Everything else works very nicely except for extensions that use the
> Makefile `MODULEDIR` variable to install all of the share files except
> the control file into a particular directory, and the `directory` in
> the control file so that the files can be found. Here’s semver[2],
> which has both:
>
> ```console
> Following `MODULEDIR=semver`, it puts the SQL files into
> `share/semver/` instead of `share/extension/`, as expected, but then,
> even though the control file has `directory=semver`, it can’t load
> them:
>
> ```pgsql

I've managed to reproduce the issue. The problem is on get_ext_ver_list
which calls get_extension_script_directory that try to search for .sql
files only on $sharedir.

> But given that the `directory` variable in the control file can be a
> full path, I don’t see that there’s much of a way to generalize a
> solution. I guess there are three options:
>
> 1. If directory is a full path, try to load the files there. It
> probably already works that way, though I haven’t tired it.
>
Yes, if the directory is a full path it try to load the files from
there. It is implemented on get_extension_script_directory.

> 2. If the directory is not a full path, check for it under each
> directory in `extension_control_path`? But no, that points to
> `share/extension`, not `share`, so it can’t really searched unless it
> also lops off `extension` from the end of each path.

Maybe we could make the "extension" part of the extension control path
explicitly, like Peter has mentioned in his first patch version [1]?.
If "directory" is not set we could use "extension" otherwise use the
"directory" as a path suffix when searching on extension_control_path?

[1] https://www.postgresql.org/message-id/0d384836-7e6e-4932-af3b-8dad1f6fee43%40eisentraut.org

-- 
Matheus Alcantara

---

From: "David E. Wheeler" <[email-masked]>
Date: Fri, 21 Mar 2025 18:05:34 -0400

> Did you miss to attach the patch?

No. You can see it in the archive[1]. Direct link[2].

> "directory" as a path suffix when searching on extension_control_path?

So, omit “extension” from the path options, append it to search for control files, and then append the directory value (if not absolute) if it exists to look for files, and otherwise append “extensions” to find them, too. I think that makes sense.

Essentially it becomes a SHAREDIR search path.

Best,

David

[1]: https://postgr.es/m/[email-masked]
[2]: https://www.postgresql.org/message-id/attachment/174397/v1-0001-Flesh-out-docs-for-the-prefix-make-variable.patch

---

From: Andrew Dunstan <[email-masked]>
Date: Sat, 22 Mar 2025 09:28:41 -0400

On 2025-03-21 Fr 12:42 PM, Tom Lane wrote:
> Matheus Alcantara<[email-masked]> writes:
>> On Thu, Mar 20, 2025 at 7:38 PM Andrew Dunstan<[email-masked]> wrote:

Good catch. This is happening because the owner hasn't updated the 
animal to REL_19_1. In 19 and 19.1 we fixed detection of exiting 
installs to take account of the 'Is there postgres or pgsql in the 
prefix' issue. So it was looking in the wrong place.

cheers

andrew