extension_control_path

Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:

Please find attached to this email a patch implementing a new GUC that
allows users to setup a list of path where PostgreSQL will search for
the extension control files at CREATE EXTENSION time.

Why is that a good idea? It's certainly not going to simplify DBAs'
lives, more the reverse. ("This dump won't reload." "Uh, where did
you get that extension from?" "Ummm...")

Assuming that there is some need for loading extensions from nonstandard
places, would it be better to just allow a filename specification in
CREATE EXTENSION? (I don't know the answer, since the use-case isn't
apparent to me in the first place, but it seems worth asking.)

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Tom Lane <tgl@sss.pgh.pa.us> writes:

Why is that a good idea? It's certainly not going to simplify DBAs'
lives, more the reverse. ("This dump won't reload." "Uh, where did
you get that extension from?" "Ummm...")

The latest users for the feature are the Red Hat team working on Open
Shift where they want to have co-existing per-user PostgreSQL clusters
on a machine, each with its own set of extensions.

Having extension_control_path also allows to install extension files in
a place not owned by root.

Lastly, as a developer, you might enjoy being able to have your own
non-system-global place to install extensions, as Andres did explain on
this list not too long ago.

Assuming that there is some need for loading extensions from nonstandard
places, would it be better to just allow a filename specification in
CREATE EXTENSION? (I don't know the answer, since the use-case isn't
apparent to me in the first place, but it seems worth asking.)

In the extension_control_path idea, we still are adressing needs where
the people managing the OS and the database are distinct sets. The GUC
allows the system admins to setup PostgreSQL the way they want, then the
database guy doesn't need to know anything about that at CREATE
EXTENSION time.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:

Please find attached to this email a patch implementing a new GUC that
allows users to setup a list of path where PostgreSQL will search for
the extension control files at CREATE EXTENSION time.

Why is that a good idea? It's certainly not going to simplify DBAs'
lives, more the reverse. ("This dump won't reload." "Uh, where did
you get that extension from?" "Ummm...")

We *already* have that problem. I don't think this makes it
particularly worse- you still need to go back to the old box and look at
what came from where. Sure, you *might* be lucky enough to find the
right extension by guessing at what packages were installed or searching
for one that looks like the correct one, but then, you might discover
that the version available isn't the right version for the database
you're trying to restore anyway. Indeed, this might allow folks who
don't particularly care for package systems to build consistent dumps
without having to worry quite as much about what the package system is
doing.

Assuming that there is some need for loading extensions from nonstandard
places, would it be better to just allow a filename specification in
CREATE EXTENSION? (I don't know the answer, since the use-case isn't
apparent to me in the first place, but it seems worth asking.)

For my 2c, I could absolutely see it as being worthwhile to have an
independent directory to install not-from-package extensions. That
would keep things which are "managed by the package system" and things
which are installed independent separate, which is absolutely a good
thing, imv.

Thanks,

Stephen

Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:

Tom Lane <tgl@sss.pgh.pa.us> writes:

Why is that a good idea? It's certainly not going to simplify DBAs'
lives, more the reverse. ("This dump won't reload." "Uh, where did
you get that extension from?" "Ummm...")

The latest users for the feature are the Red Hat team working on Open
Shift where they want to have co-existing per-user PostgreSQL clusters
on a machine, each with its own set of extensions.

Um ... "own set of installed extensions" doesn't need to mean "own set of
available extensions", any more than those clusters need to have their
own Postgres executables. If the clusters *do* have their own
executables, eg because they're different PG versions, then they can
certainly also have their own $SHAREDIR trees too. So this example
is totally without value for your case.

Having extension_control_path also allows to install extension files in
a place not owned by root.

As far as the control files go, there's nothing saying that
$SHAREDIR/extension has to be root-owned. If there are .so's involved,
I do not believe the Red Hat crew is asking you to support loading .so's
from non-root-owned dirs, because that'd be against their own corporate
security policies. (But in any case, where we find the control and SQL
files need not have anything to do with where the .so's are.)

Lastly, as a developer, you might enjoy being able to have your own
non-system-global place to install extensions, as Andres did explain on
this list not too long ago.

And again, if you're working on a development version, $SHAREDIR/extension
is probably owned by you anyway.

I don't see that any of these scenarios create a need to install extension
files anywhere but $SHAREDIR/extension.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Tom Lane <tgl@sss.pgh.pa.us> writes:

Dimitri Fontaine <dimitri@2ndQuadrant.fr> writes:

Tom Lane <tgl@sss.pgh.pa.us> writes:

Why is that a good idea? It's certainly not going to simplify DBAs'
lives, more the reverse. ("This dump won't reload." "Uh, where did
you get that extension from?" "Ummm...")

The latest users for the feature are the Red Hat team working on Open
Shift where they want to have co-existing per-user PostgreSQL clusters
on a machine, each with its own set of extensions.

Um ... "own set of installed extensions" doesn't need to mean "own set of
available extensions", any more than those clusters need to have their
own Postgres executables. If the clusters *do* have their own
executables, eg because they're different PG versions, then they can
certainly also have their own $SHAREDIR trees too. So this example
is totally without value for your case.

They have several clusters as in `initdb` running standard packaged
binaries, each user having its own set of processes running with only
his privileges.

So when applying your idea (well, my understanding of it), they would be
happy with a $SHAREDIR per initdb.

Having extension_control_path also allows to install extension files in
a place not owned by root.

As far as the control files go, there's nothing saying that
$SHAREDIR/extension has to be root-owned. If there are .so's involved,
I do not believe the Red Hat crew is asking you to support loading .so's
from non-root-owned dirs, because that'd be against their own corporate
security policies. (But in any case, where we find the control and SQL
files need not have anything to do with where the .so's are.)

But you can have a single $SHAREDIR per set of executables, right?

Please read the following email to know what they asked for and how they
do operate OpenShift:

/messages/by-id/341087492.2585530.1376776393038.JavaMail.root@redhat.com

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

But you can have a single $SHAREDIR per set of executables, right?

Please read the following email to know what they asked for and how they
do operate OpenShift:

/messages/by-id/341087492.2585530.1376776393038.JavaMail.root@redhat.com

FWIW, I'm talking with Amazon later this week and checking how they're
handling their tenant-loadable extensions. I'd like to come up with one
solution here which covers all cloud providers.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 15 January 2014 03:07, Stephen Frost <sfrost@snowman.net> wrote:

For my 2c, I could absolutely see it as being worthwhile to have an
independent directory to install not-from-package extensions. That
would keep things which are "managed by the package system" and things
which are installed independent separate, which is absolutely a good
thing, imv.

Another use case previously mentioned is that it makes
user-installable extensions for developer-targeted bundles like
Postgres.app possible. Postgres.app ships with contrib and a few other
extensions by default, but if you want to install more, you have to
chuck them into extensions dir inside the app bundle itself, so minor
updates which replace the bundle will then lose your installed
extensions. A nicer approach would be to allow it to also look for
extensions under ~/Library/ as well as in the bundled distribution,
but that's not possible if postgres only looks in one place, short of
hand hacking fs links in the extension dir.

Cheers

Tom

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hi.

I can't apply the patch.

$ git apply --stat ~/Downloads/extension_control_path.v0.patch
fatal: unrecognized input

2014/1/14 Dimitri Fontaine <dimitri@2ndquadrant.fr>

Hi,

Please find attached to this email a patch implementing a new GUC that
allows users to setup a list of path where PostgreSQL will search for
the extension control files at CREATE EXTENSION time.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--
Best regards,
Sergey Muraviov

Sergey Muraviov <sergey.k.muraviov@gmail.com> writes:

I can't apply the patch.

Did you try using the `patch`(1) command?

The PostgreSQL project policy is to not use the git format when sending
patches to the mailing list, prefering the context diff format. So you
need to resort to using the basic patch commands rather than the modern
git tooling. See also:

http://wiki.postgresql.org/wiki/Submitting_a_Patch

Patches must be in a format which provides context (eg: Context
Diff); 'normal' or 'plain' diff formats are not acceptable.

The following email might be useful for you:

/messages/by-id/CAOR=d=0q0DaL0BNZTsDdNWPgM5EjkXUykj7m+QsQbR728EOKCA@mail.gmail.com

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Jan 24, 2014 at 6:57 AM, Dimitri Fontaine <dimitri@2ndquadrant.fr>
wrote:

Sergey Muraviov <sergey.k.muraviov@gmail.com> writes:

I can't apply the patch.

Did you try using the `patch`(1) command?

The PostgreSQL project policy is to not use the git format when sending
patches to the mailing list, prefering the context diff format. So you
need to resort to using the basic patch commands rather than the modern
git tooling. See also:

http://wiki.postgresql.org/wiki/Submitting_a_Patch

Patches must be in a format which provides context (eg: Context
Diff); 'normal' or 'plain' diff formats are not acceptable.

Would be nice if we can use "git apply" command...

:-)

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

Hi

Now patch applies cleanly and works. :-)

But I have some notes:

1. There is an odd underscore character in functions
find_in_extension_control_path and list_extension_control_paths:
\"extension_control__path\""

2. If we have several versions of one extension in different directories
(which are listed in extension_control_path parameter) then we
get strange output from pg_available_extensions and
pg_available_extension_versions views (Information about extension, whose
path is at the beginning of the list, is duplicated). And only one version
of the extension can be created.

See examples:
/extensions/
├── postgis-2.0.4
│ ├── postgis--2.0.4.sql
│ └── postgis.control
└── postgis-2.1.1
├── postgis--2.1.1.sql
└── postgis.control

=================================================

postgresql.conf:
extension_control_path =
'/extensions/postgis-2.0.4:/extensions/postgis-2.1.1'

postgres=# table pg_catalog.pg_available_extensions;
name | default_version | installed_version |
comment
---------+-----------------+-------------------+---------------------------------------------------------------------
postgis | 2.0.4 | | PostGIS geometry,
geography, and raster spatial types and functions
postgis | 2.0.4 | | PostGIS geometry,
geography, and raster spatial types and functions
(2 rows)

postgres=# table pg_catalog.pg_available_extension_versions;
name | version | installed | superuser | relocatable | schema |
requires | comment

---------+---------+-----------+-----------+-------------+--------+----------+---------------------------------------------------------------------
postgis | 2.0.4 | f | t | t | |
| PostGIS geometry, geography, and raster spatial types and functions
postgis | 2.0.4 | f | t | t | |
| PostGIS geometry, geography, and raster spatial types and functions
(2 rows)

=================================================

postgresql.conf:
extension_control_path =
'/extensions/postgis-2.1.1:/extensions/postgis-2.0.4'

postgres=# table pg_catalog.pg_available_extensions;
name | default_version | installed_version |
comment
---------+-----------------+-------------------+---------------------------------------------------------------------
postgis | 2.1.1 | | PostGIS geometry,
geography, and raster spatial types and functions
postgis | 2.1.1 | | PostGIS geometry,
geography, and raster spatial types and functions
(2 rows)

postgres=# create extension postgis;
CREATE EXTENSION

postgres=# SELECT PostGIS_version();
postgis_version
---------------------------------------
2.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)

postgres=# table pg_catalog.pg_available_extensions;
name | default_version | installed_version |
comment
---------+-----------------+-------------------+---------------------------------------------------------------------
postgis | 2.1.1 | 2.1.1 | PostGIS geometry,
geography, and raster spatial types and functions
postgis | 2.1.1 | 2.1.1 | PostGIS geometry,
geography, and raster spatial types and functions
(2 rows)

3. It would be fine to see an extension control path
in pg_available_extensions and pg_available_extension_versions views (in
separate column or within of extension name).

4. Perhaps the CREATE EXTENSION command should be improved to allow
creation of the required version of the extension.
So we can use different versions of extensions in different databases.

PS
Sorry for my English.

2014/1/24 Fabrízio de Royes Mello <fabriziomello@gmail.com>

On Fri, Jan 24, 2014 at 6:57 AM, Dimitri Fontaine <dimitri@2ndquadrant.fr>
wrote:

Sergey Muraviov <sergey.k.muraviov@gmail.com> writes:

I can't apply the patch.

Did you try using the `patch`(1) command?

The PostgreSQL project policy is to not use the git format when sending
patches to the mailing list, prefering the context diff format. So you
need to resort to using the basic patch commands rather than the modern
git tooling. See also:

http://wiki.postgresql.org/wiki/Submitting_a_Patch

Patches must be in a format which provides context (eg: Context
Diff); 'normal' or 'plain' diff formats are not acceptable.

Would be nice if we can use "git apply" command...

:-)

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

Timbira: http://www.timbira.com.br
Blog sobre TI: http://fabriziomello.blogspot.com
Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello

--
Best regards,
Sergey Muraviov

On Fri, Jan 24, 2014 at 6:57 AM, Fabrízio de Royes Mello
<fabriziomello@gmail.com> wrote:

Would be nice if we can use "git apply" command...

"git apply" seems to have raised pedantry to an art form. Not only
won't it apply patches in any format other than the one it likes,
it'll fail to apply any part of the patch if there are any failing
hunks; I don't think it tolerates fuzz, either. You can override some
of these behaviors but not all of them. It seems like somebody
designed this tool more with the idea of preventing people from
applying patches than actually doing it.

"patch", on the other hand, makes the very reasonable assumption that
if you didn't want to apply the patch, you wouldn't have run the
"patch" command in the first place. It does its best to make sense of
whatever you feed it, and if it can't apply the whole thing, it still
applies as much as it can. I find this much more desirable behavior.
It may be the policy of other projects to reject patches for trivial
formatting mistakes or minor fuzz, but it's not the policy here, and I
think that's a good thing. We typically bounce things for rebasing if
there are actual rejects, but not otherwise.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

I haven't actually looked at the patch itself, but I noted this from the
other review:

On Fri, Jan 24, 2014 at 6:43 PM, Sergey Muraviov <
sergey.k.muraviov@gmail.com> wrote:

=================================================

postgresql.conf:
extension_control_path =
'/extensions/postgis-2.0.4:/extensions/postgis-2.1.1'

Using colon as the path separator is going to break on windows. The patch
notices this and uses semicolon on Windows instead. Do we really want to go
down that path - that means that everybody who writes any sorts of
installation instructions including this will have to make them separate
for different platforms. Shouldn't we just use semicolon on all platforms,
for consistency?

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

Magnus Hagander <magnus@hagander.net> writes:

Using colon as the path separator is going to break on windows. The patch
notices this and uses semicolon on Windows instead. Do we really want to go
down that path - that means that everybody who writes any sorts of
installation instructions including this will have to make them separate
for different platforms. Shouldn't we just use semicolon on all platforms,
for consistency?

Semicolon, being a valid filename character on most platforms (dunno
about Windows), isn't a terribly good choice either.

Since I disagree with the goal of this patch in the first place, I'm
disinclined to spend brain cells on inventing a more robust format for
a list of path names. I'm sure there is one though, if you're giving
up on being consistent with traditional PATH format.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Magnus Hagander <magnus@hagander.net> writes:

Using colon as the path separator is going to break on windows. The patch
notices this and uses semicolon on Windows instead. Do we really want to go
down that path - that means that everybody who writes any sorts of
installation instructions including this will have to make them separate
for different platforms. Shouldn't we just use semicolon on all platforms,
for consistency?

Well, I've been considering that what I found already in the backend to
solve the same problem was a valid model to build against.

Pick any reasonnable choice you want to, fix dynamic_library_path along
the new lines or maybe ask me to, and then let's apply the same design
to the new GUC doing about exactly the same thing?

Tom Lane <tgl@sss.pgh.pa.us> writes:

Since I disagree with the goal of this patch in the first place, I'm

Should we remove dynamic_library_path? If not, why do we keep it?

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sat, Jan 25, 2014 at 6:07 PM, Dimitri Fontaine <dimitri@2ndquadrant.fr>wrote:

Magnus Hagander <magnus@hagander.net> writes:

Using colon as the path separator is going to break on windows. The patch
notices this and uses semicolon on Windows instead. Do we really want to

go

down that path - that means that everybody who writes any sorts of
installation instructions including this will have to make them separate
for different platforms. Shouldn't we just use semicolon on all

platforms,

for consistency?

Well, I've been considering that what I found already in the backend to
solve the same problem was a valid model to build against.

Pick any reasonnable choice you want to, fix dynamic_library_path along
the new lines or maybe ask me to, and then let's apply the same design
to the new GUC doing about exactly the same thing?

Ha, I didn't realize dynamic_library_paty had the same problem. In fact, I

have to admit I didn't realize I could put more than one path in there - I
don't think I've ever used that :D

So based on the previous behaviour there, I withdraw my comment - being
consistent with the existing behaviour of that parameter makes perfect
sense.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

Hi,

Sergey Muraviov <sergey.k.muraviov@gmail.com> writes:

Now patch applies cleanly and works. :-)

Cool ;-)

But I have some notes:

1. There is an odd underscore character in functions
find_in_extension_control_path and list_extension_control_paths:
\"extension_control__path\""

Fixed in the new version of the patch, attached.

2. If we have several versions of one extension in different directories
(which are listed in extension_control_path parameter) then we
get strange output from pg_available_extensions and
pg_available_extension_versions views (Information about extension, whose
path is at the beginning of the list, is duplicated). And only one version
of the extension can be created.

Fixed.

3. It would be fine to see an extension control path
in pg_available_extensions and pg_available_extension_versions views (in
separate column or within of extension name).

I think the on-disk location is an implementation detail and decided in
the attached version not to change those system view definitions.

4. Perhaps the CREATE EXTENSION command should be improved to allow
creation of the required version of the extension.
So we can use different versions of extensions in different databases.

Fixed in the attached.

I also fixed ALTER EXTENSION UPDATE to search for udpate scripts in the
same directory where the main control file is found, but I suspect this
part requires more thinking.

When we ALTER EXTENSION UPDATE we might now have several places where we
find extname.control files, with possibly differents default_version
properties.

In the attached, we select the directory containing the control file
where default_version matches the already installed extension version.
That matches with a model where the new version of the extension changes
the default_version in an auxiliary file.

We might want to instead match on the default_version in the control
file to match with the new version we are asked to upgrade to.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

Hi.

Now it looks fine for me.

2014-01-28 Dimitri Fontaine <dimitri@2ndquadrant.fr>:

Hi,

Sergey Muraviov <sergey.k.muraviov@gmail.com> writes:

Now patch applies cleanly and works. :-)

Cool ;-)

But I have some notes:

1. There is an odd underscore character in functions
find_in_extension_control_path and list_extension_control_paths:
\"extension_control__path\""

Fixed in the new version of the patch, attached.

2. If we have several versions of one extension in different directories
(which are listed in extension_control_path parameter) then we
get strange output from pg_available_extensions and
pg_available_extension_versions views (Information about extension, whose
path is at the beginning of the list, is duplicated). And only one

version

of the extension can be created.

Fixed.

3. It would be fine to see an extension control path
in pg_available_extensions and pg_available_extension_versions views (in
separate column or within of extension name).

I think the on-disk location is an implementation detail and decided in
the attached version not to change those system view definitions.

4. Perhaps the CREATE EXTENSION command should be improved to allow
creation of the required version of the extension.
So we can use different versions of extensions in different databases.

Fixed in the attached.

I also fixed ALTER EXTENSION UPDATE to search for udpate scripts in the
same directory where the main control file is found, but I suspect this
part requires more thinking.

When we ALTER EXTENSION UPDATE we might now have several places where we
find extname.control files, with possibly differents default_version
properties.

In the attached, we select the directory containing the control file
where default_version matches the already installed extension version.
That matches with a model where the new version of the extension changes
the default_version in an auxiliary file.

We might want to instead match on the default_version in the control
file to match with the new version we are asked to upgrade to.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

--
Best regards,
Sergey Muraviov

On Jan 30, 2014, at 10:06 AM, Sergey Muraviov <sergey.k.muraviov@gmail.com> wrote:

Now it looks fine for me.

Just as another data point, I recently submitted pgTAP to the Homebrew project This is the build-from-source system for OS X, used by a lot of web developers. In my build script, I originally had

depends_on :postgresql

Which means, “require any version of PostgreSQL.” But then tests failed on OS X Server, which includes a system-distributed PostgreSQL. Homebrew installs everything in /usr/local, and not only does it disallow installing anything outside of that directory, it doesn’t have any permissions to do so. The install failed, of course, because extensions want to install in $PGROOT/share/extensions. For now, I had to change it to

depends_on 'postgresql'

A subtle difference that means, “require the latest version of the Homebrew-built PostgreSQL in /usr/local.”

However, if extension_control_path was supported, I could change it back to requiring any Postgres and install pgTAP somewhere under /usr/local, as required for Homebrew. Then all the user would have to do to use it with their preferred Postgres would be to set extension_control_path.

In other words, I am strongly in favor of this patch, as it gives distribution systems a lot more flexibility (for better and for worse) in determining where extensions should be installed.

My $0.02.

Best,

David

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers