RFC: extension_search_path to supplement dynamic_library_path

Hi all

We have a dynamic_library_path setting to allow postgres to search
additional locations for loadable libraries (since 8.1, commit
761a0bb69b). This permits libraries to be located in alternate
locations for loading by shared_preload_libraries, LOAD commands,
implicit loading via function calls etc. This searches '$libdir',
PG_INSTALL_DIR/lib, by default, but can be modified by configuration.

When extensions were introduced in 9.1 (d9572c4e3b) no search path
support for extension control files and SQL scripts was added to match
dynamic_library_path. The only place extension scripts and control
files can be placed is PG_INSTALL_DIR/share/extension .

Practically all loadable modules now come with extension control files
and usually extension SQL scripts. In practice this means extensions
are not relocatable.

Reasons to care are much the same reasons dynamic_library_path was
added. Specific use cases include:

1. Running "make check" against a temporary instance of a postgres
install (from packages or source) without needing permission to modify
the installation's lib/ and share/extension directories or install the
extension before testing it.
2. Letting postgres builds installed in non-default paths load
extensions installed from public repositories like yum.postgresql.org,
so long as the custom build can promise to be ABI-compatible with the
mainline builds.

------

Details:

(1) is useful when building and testing extensions against a postgres
that was installed from packages, or when you want to build and check
a new or updated extension before installing it in the local postgres.

Right now the extension must "make install" into the active postgres
install first; we have to install the built code before testing it,
which is backwards. And the extension tests need have write privileges
to lib/ and share/extension which is undesirable.

PGXS doesn't support a "temp_install" mode, so we can't "make check"
the same way we do for in-tree builds, and it doesn't IMO make a lot
of sense to do so. It'd be much better to just allow pg_regress,
pg_isolation_regress and TAP tests to specify an extension_search_path
when they initdb and start a new temp instance.

(2) is useful when for an alternative PostgreSQL build that's
binary-compatible with normal builds in a non-default path, while
continuing to use extensions packaged from public repos. For example,
to hot-fix a production issue with a patched build until the next
postgres release includes the fix without overwriting packaged files.
Or to trial a proposed upgrade before committing to it. Right now if
you want to install to a non-default path you have to recompile all
your extensions or manually copy files around. Not fun if you use
(say) PostGIS.

I'm mainly interested in the first use case.