Re: Extension Templates S03E11

Hi,

Please find attached to this email the latest and greatest version of
in-line SQL only extensions support, known as "Extension Templates" and
which could be renamed "In-Catalog Extension Templates".

I've included a high-level description of the patch in a style that
targets the detailed commit messages for features of that source code
impact level.

The attached patch is known to address all points raised in the previous
reviews and to implement the best design we could come up with, thanks
to immense helping from Tom, Heikki and Markus. Of course, bugs are all
my precious.

I'm going to register that patch to the next commitfest. It's not the
only patch I intend to register for september though, as I want to get
to a usable situation with Event Triggers, so you can expect a series of
patches for that, covering what couldn't make it previously.

As I think this WIP is about as ready-for-committer as it will ever be,
it would be fantastic if we could do a single committer review before
CF2013-09 so that I know that it's going to be accepted… or not. Well at
least it's in the queue already, we'll see what can be done.

Regards,

---
Implement in-catalog Extension Template facility.

Previously, the only way to CREATE EXTENSION involved installing file
system templates in a place generally owned by root: creation scripts,
upgrade scripts, main control file and auxilliary control files. This
patch implements a way to upload all those resources into the catalogs,
so that a PostgreSQL connection is all you need to make an extension
available.

By design and for security concerns the current Extension Template
facility is not able to deal with extensions that need to load a DSO
module into the backend. Using any other PL is supported though.

An extension created from a template depends on it, and the templates
are part of any backup script taken with pg_dump. So that at pg_restore
time, when CREATE EXTENSION is executed the templates are already in
place.

To be able to do that, though, we need a difference in behavior in
between the classic file system level templates and the catalog
templates: there's no dependency tracking happening at all with file
system templates and those can be changed at will even if an extension
has been already instanciated from the templates, or even removed.

Apart from the dependency tracking, the only other difference between
file system templates and catalog templates for extensions is that the
later are managed per-database. The file system level templates being
managed per major version of PostgreSQL is considered a drawback of that
method and not to be immitated by the in-catalog system, more flexible
by design.

At CREATE EXTENSION time, the file system templates are always prefered
to the catalog templates. Also, it's prohibited to make available an
extension in the catalogs if an extension of the same name is already
available from file system templates. That said, some "race conditions"
make it still possible to have the same extension name available as a
file system template and a catalog template. Even if only the former
will ever get installed, it's been deemed prudent to restrict the
in-catalog templates for extensions to superusers only.

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

On 1 August 2013 18:01, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr> wrote:

> Hi,
>
> Please find attached to this email the latest and greatest version of
> in-line SQL only extensions support, known as "Extension Templates" and
> which could be renamed "In-Catalog Extension Templates".
>
> I've included a high-level description of the patch in a style that
> targets the detailed commit messages for features of that source code
> impact level.
>
> The attached patch is known to address all points raised in the previous
> reviews and to implement the best design we could come up with, thanks
> to immense helping from Tom, Heikki and Markus. Of course, bugs are all
> my precious.
>
> I'm going to register that patch to the next commitfest. It's not the
> only patch I intend to register for september though, as I want to get
> to a usable situation with Event Triggers, so you can expect a series of
> patches for that, covering what couldn't make it previously.
>
> As I think this WIP is about as ready-for-committer as it will ever be,
> it would be fantastic if we could do a single committer review before
> CF2013-09 so that I know that it's going to be accepted… or not. Well at
> least it's in the queue already, we'll see what can be done.
>
> Regards,
>
> ---
> Implement in-catalog Extension Template facility.
>
> Previously, the only way to CREATE EXTENSION involved installing file
> system templates in a place generally owned by root: creation scripts,
> upgrade scripts, main control file and auxilliary control files. This
> patch implements a way to upload all those resources into the catalogs,
> so that a PostgreSQL connection is all you need to make an extension
> available.
>
> By design and for security concerns the current Extension Template
> facility is not able to deal with extensions that need to load a DSO
> module into the backend. Using any other PL is supported though.
>
> An extension created from a template depends on it, and the templates
> are part of any backup script taken with pg_dump. So that at pg_restore
> time, when CREATE EXTENSION is executed the templates are already in
> place.
>
> To be able to do that, though, we need a difference in behavior in
> between the classic file system level templates and the catalog
> templates: there's no dependency tracking happening at all with file
> system templates and those can be changed at will even if an extension
> has been already instanciated from the templates, or even removed.
>
> Apart from the dependency tracking, the only other difference between
> file system templates and catalog templates for extensions is that the
> later are managed per-database. The file system level templates being
> managed per major version of PostgreSQL is considered a drawback of that
> method and not to be immitated by the in-catalog system, more flexible
> by design.
>
> At CREATE EXTENSION time, the file system templates are always prefered
> to the catalog templates. Also, it's prohibited to make available an
> extension in the catalogs if an extension of the same name is already
> available from file system templates. That said, some "race conditions"
> make it still possible to have the same extension name available as a
> file system template and a catalog template. Even if only the former
> will ever get installed, it's been deemed prudent to restrict the
> in-catalog templates for extensions to superusers only.
>

Could you please resubmit this without using SnapshotNow as it's no longer
supported?

Thanks

Thom

Thom Brown <thom(at)linux(dot)com> writes:
> Could you please resubmit this without using SnapshotNow as it's no longer
> supported?

Sure, sorry that I missed that, please find v12 attached.

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

Hi,

2013-08-04 15:20 keltezéssel, Dimitri Fontaine írta:
> Thom Brown <thom(at)linux(dot)com> writes:
>> Could you please resubmit this without using SnapshotNow as it's no longer
>> supported?
> Sure, sorry that I missed that, please find v12 attached.

Here's a review for this patch.

* Is the patch in a patch format which has context? (eg: context diff format)

Yes.

* Does it apply cleanly to the current git master?

No, it has one reject in src/bin/pg_dump/pg_backup_archiver.c.
It was obvious to fix, version 12a is attached.

* Does it include reasonable tests, necessary doc patches, etc?

It has extended the SQL reference nicely but the reference to
<xref linkend="extend-extensions"> was not obvious enough
regarding the list of control parameters.

The SQL syntax has them in allcaps, while the referenced section
has them in lower case. It's easy to miss them while just browsing
for e.g. RELOCATABLE. I had to go back twice to find the proper
part of the text.

I would like to see the control parameters documented in allcaps
in CREATE EXTENSION TEMPLATE. Then ALTER EXTENSION
TEMPLATE should reference the CREATE instead of the longer
text in <xref linkend="extend-extensions">. This xref can still
be there for reference in all three of CREATE/ALTER/DROP
EXTENSION TEMPLATE.

* Does the patch actually implement what it's supposed to do?

Yes.

* Do we want that?

Yes.

* Do we already have it?

No.

* Does it follow SQL spec, or the community-agreed behavior?

There's no such provision in the spec.
As far as I can tell, it follows the community-agreed behavior.

* Does it include pg_dump support (if applicable)?

Yes.

But the version check is already wrong in src/bin/pg_dump/pg_dump.c
since this patch missed 9.3.

+ /*
+ * Before 9.3, there are no extension templates.
+ */
+ if (fout->remoteVersion < 90300)
+ {
+ *numExtensionTemplates = 0;
+ return NULL;
+ }
+

* Are there dangers?

I don't think so.

* Have all the bases been covered?

It seems so.

* Does the feature work as advertised?

Yes.

* Are there corner cases the author has failed to consider?

I don't know.

* Are there any assertion failures or crashes?

No.

* Does the patch slow down simple tests?

No.

* If it claims to improve performance, does it?

n/a

* Does it slow down other things?

No.

* Does it follow the project coding guidelines?

Yes.

Nitpicking. This chunk has an extra unnecessary space:

diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile
index c4d3f3c..689dc37 100644
--- a/src/backend/catalog/Makefile
+++ b/src/backend/catalog/Makefile
@@ -38,6 +38,7 @@ POSTGRES_BKI_SRCS = $(addprefix $(top_srcdir)/src/include/catalog/,\
pg_authid.h pg_auth_members.h pg_shdepend.h pg_shdescription.h \
pg_ts_config.h pg_ts_config_map.h pg_ts_dict.h \
pg_ts_parser.h pg_ts_template.h pg_extension.h \
+ pg_extension_control.h pg_extension_template.h pg_extension_uptmpl.h \
pg_foreign_data_wrapper.h pg_foreign_server.h pg_user_mapping.h \
pg_foreign_table.h \
pg_default_acl.h pg_seclabel.h pg_shseclabel.h pg_collation.h pg_range.h \

* Are there portability issues?

No.

* Will it work on Windows/BSD etc?

It should.

* Are the comments sufficient and accurate?

Yes.

* Does it do what it says, correctly?

According to the added regression tests, yes.

* Does it produce compiler warnings?

No.

* Can you make it crash?

No.

* Is everything done in a way that fits together coherently with other features/modules?

I think so.

* Are there interdependencies that can cause problems?

I don't know.

Best regards,
Zoltán Böszörményi

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
http://www.postgresql.at/

Hi,

Boszormenyi Zoltan <zb(at)cybertec(dot)at> writes:
> Here's a review for this patch.

Thanks for that review Zoltan!

> No, it has one reject in src/bin/pg_dump/pg_backup_archiver.c.
> It was obvious to fix, version 12a is attached.

Included in the new version of the patch (v13), attached.

> It has extended the SQL reference nicely but the reference to
> <xref linkend="extend-extensions"> was not obvious enough
> regarding the list of control parameters.

Fixed in the attached.

> I would like to see the control parameters documented in allcaps
> in CREATE EXTENSION TEMPLATE. Then ALTER EXTENSION
> TEMPLATE should reference the CREATE instead of the longer
> text in <xref linkend="extend-extensions">. This xref can still
> be there for reference in all three of CREATE/ALTER/DROP
> EXTENSION TEMPLATE.

I didn't follow exactly your proposal here because I didn't want to have
to maintain the control parameter description list in two different
places. I've still added a detailed list with references and details and
more importantly with coverage of e.g. "NORELOCATABLE" which is not
covered in the referenced material.

> But the version check is already wrong in src/bin/pg_dump/pg_dump.c
> since this patch missed 9.3.
>
> + if (fout->remoteVersion < 90300)

Fixed.

> Nitpicking. This chunk has an extra unnecessary space:

Fixed.

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

2013-08-27 18:09 keltezéssel, Dimitri Fontaine írta:
> Hi,
>
> Boszormenyi Zoltan <zb(at)cybertec(dot)at> writes:
>> Here's a review for this patch.
> Thanks for that review Zoltan!

You're welcome.

>> I would like to see the control parameters documented in allcaps
>> in CREATE EXTENSION TEMPLATE. Then ALTER EXTENSION
>> TEMPLATE should reference the CREATE instead of the longer
>> text in <xref linkend="extend-extensions">. This xref can still
>> be there for reference in all three of CREATE/ALTER/DROP
>> EXTENSION TEMPLATE.
> I didn't follow exactly your proposal here because I didn't want to have
> to maintain the control parameter description list in two different
> places. I've still added a detailed list with references and details and
> more importantly with coverage of e.g. "NORELOCATABLE" which is not
> covered in the referenced material.

It looks better. Now that the lowercase keywords are expected
by the eye in the referenced text, they will be much easier to find.
Thanks.

Best regards,
Zoltán Böszörményi

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
http://www.postgresql.at/

This doesn't build:

make -C pg_upgrade_support all
make[2]: Entering directory `/var/lib/jenkins/jobs/postgresql_commitfest_world/workspace/contrib/pg_upgrade_support'
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -fpic -I. -I. -I../../src/include -D_GNU_SOURCE -I/usr/include/libxml2 -c -o pg_upgrade_support.o pg_upgrade_support.c -MMD -MP -MF .deps/pg_upgrade_support.Po
pg_upgrade_support.c: In function ‘create_empty_extension’:
pg_upgrade_support.c:193:8: error: too few arguments to function ‘InsertExtensionTuple’
In file included from pg_upgrade_support.c:16:0:
../../src/include/commands/extension.h:60:12: note: declared here
make[2]: *** [pg_upgrade_support.o] Error 1

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> make -C pg_upgrade_support all

Do we have something automated to easily test pg_upgrade?

My memories of how pg_upgrade works with extensions makes me believe
that I don't have anything special to do when those extensions have been
made available through a template: the scripts are not used. That said,
we want to retain some new dependencies…

The contrib/pg_upgrade/IMPLEMENTATION file is silent about upgrading
extensions…

I fixed the pg_upgrade_support compiling in my branch here, please find
the patch-on-patch attached. I also checked that the whole of contribs
now build fine and didn't find any other problem.

Regards,
--
Dimitri Fontaine 06 63 07 10 78
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

Dimitri Fontaine wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> > make -C pg_upgrade_support all
>
> Do we have something automated to easily test pg_upgrade?

"make check" in contrib/pg_upgrade should do the trick.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
> "make check" in contrib/pg_upgrade should do the trick.

PASSED

Even after I added extension to the serial_schedule. I don't know if I
need to do anything specific on that area, will wait about some feedback
on that before sending a new version of the patch. Meanwhile my branch
is updated, and I sent the patch-on-patch too.

Regards,
--
Dimitri Fontaine 06 63 07 10 78
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
> Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
>> "make check" in contrib/pg_upgrade should do the trick.
>
> PASSED
>
> Even after I added extension to the serial_schedule. I don't know if I
> need to do anything specific on that area, will wait about some feedback
> on that before sending a new version of the patch. Meanwhile my branch
> is updated, and I sent the patch-on-patch too.

Here's v14 of the patch with pg_upgrade support fixed, so that the
automated setup that Peter built is able to have at it!

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

On Thu, 2013-08-29 at 12:16 +0200, Dimitri Fontaine wrote:
> Here's v14 of the patch with pg_upgrade support fixed, so that the
> automated setup that Peter built is able to have at it!

Fails cpluspluscheck:

In file included from /tmp/cpluspluscheck.5g2uWw/test.cpp:3:0:
./src/include/commands/template.h:47:8: error: ‘ExtensionControl’ does not name a type
./src/include/commands/template.h:51:8: error: ‘ExtensionControl’ does not name a type

I think this actually just means the header does not include all it needs by itself.

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Fails cpluspluscheck:

Turns out I'm discovering that particular check, thanks! I could
reproduce and fix the error locally after being led to the command
./src/tools/pginclude/cpluspluscheck.

So please find v15 of the patch attached to this email, that passes all
previously done checks and this one too now.

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

On Mon, Sep 02, 2013 at 02:32:16AM -0400, Peter Eisentraut wrote:
> On Thu, 2013-08-29 at 12:16 +0200, Dimitri Fontaine wrote:
> > Here's v14 of the patch with pg_upgrade support fixed, so that the
> > automated setup that Peter built is able to have at it!
>
> Fails cpluspluscheck:
>
> In file included from /tmp/cpluspluscheck.5g2uWw/test.cpp:3:0:
> ./src/include/commands/template.h:47:8: error: ‘ExtensionControl’ does not name a type
> ./src/include/commands/template.h:51:8: error: ‘ExtensionControl’ does not name a type
>
> I think this actually just means the header does not include all it needs by itself.

Is there some standard set of checks you run on new patches, and are
the results showing up on, say, the buildfarm or some other CI
dashboard?

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Tue, Sep 3, 2013 at 4:20 AM, David Fetter <david(at)fetter(dot)org> wrote:
> On Mon, Sep 02, 2013 at 02:32:16AM -0400, Peter Eisentraut wrote:
>> On Thu, 2013-08-29 at 12:16 +0200, Dimitri Fontaine wrote:
>> > Here's v14 of the patch with pg_upgrade support fixed, so that the
>> > automated setup that Peter built is able to have at it!
>>
>> Fails cpluspluscheck:
>>
>> In file included from /tmp/cpluspluscheck.5g2uWw/test.cpp:3:0:
>> ./src/include/commands/template.h:47:8: error: ‘ExtensionControl’ does not name a type
>> ./src/include/commands/template.h:51:8: error: ‘ExtensionControl’ does not name a type
>>
>> I think this actually just means the header does not include all it needs by itself.
>
> Is there some standard set of checks you run on new patches, and are
> the results showing up on, say, the buildfarm or some other CI
> dashboard?
I believe that Peter does all those checks using his own Jenkins environment:
http://pgci.eisentraut.org/jenkins/
For the commit fest patches here you go:
http://pgci.eisentraut.org/jenkins/view/All/job/postgresql_commitfest_world/
--
Michael

Dimitri,

* Dimitri Fontaine (dimitri(at)2ndQuadrant(dot)fr) wrote:
> So please find v15 of the patch attached to this email, that passes all
> previously done checks and this one too now.

Looks like there's been a bit of unfortunate bitrot due to Tom's change
to disable fancy output:

patching file src/test/regress/expected/sanity_check.out
Hunk #1 FAILED at 104.
Hunk #2 FAILED at 166.
2 out of 2 hunks FAILED -- saving rejects to file src/test/regress/expected/sanity_check.out.rej

Are there any other changes you have pending for this..? Would be nice
to see the latest version which you've tested and which patches cleanly
against master... ;)

I'll still go ahead and start looking through this, per our discussion.

Thanks,

Stephen

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> Are there any other changes you have pending for this..? Would be nice
> to see the latest version which you've tested and which patches cleanly
> against master... ;)

I just rebased now, please see attached. I had to pick new OIDs in some
places too, but that's about it.

> I'll still go ahead and start looking through this, per our discussion.

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

Dimitri Fontaine wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > Are there any other changes you have pending for this..? Would be nice
> > to see the latest version which you've tested and which patches cleanly
> > against master... ;)
>
> I just rebased now, please see attached. I had to pick new OIDs in some
> places too, but that's about it.

I think you're missing support for getObjectIdentity and
getObjectTypeDescription for the new object classes.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Mon, 2013-11-04 at 08:43 -0500, Stephen Frost wrote:
> I'll still go ahead and start looking through this, per our discussion.

In the CF app, this is marked "Ready for Committer". That's a bit vague
here, considering Dimitri, you, Peter, and Alvaro are all committers.
Who is this patch waiting on? Is the discussion concluding, or does it
need another round of review?

Regards,
Jeff Davis

Hi,

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> In the CF app, this is marked "Ready for Committer". That's a bit vague
> here, considering Dimitri, you, Peter, and Alvaro are all committers.
> Who is this patch waiting on? Is the discussion concluding, or does it
> need another round of review?

Thanks for the confusion I guess, but I'm no committer here ;-)

This patch has received extensive review in July and I think it now
properly implements the design proposed by Tom and Heikki in 9.3/CF4.

As the path didn't make it already, yes it needs another (final) round
of review. The main difficulty in reviewing is understanding the design
and the relation in between our current model of extensions and what
this patch offers.

You might find the discussions we had with Markus Wanner quite useful in
this light. The current situation is that I believe the patch to
implement the same “template” model as the on-disk extensions, down to
dependency tracking.

IIRC I left only one differing behavior, which is that you're not
allowed to DROP an Extension Template when it's needed for a dump and
restore cycle, where you could be doing that at the file system level of
course (and pg_restore on a new system would depend on other files).

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

Dimitri,

* Dimitri Fontaine (dimitri(at)2ndQuadrant(dot)fr) wrote:
> As the path didn't make it already, yes it needs another (final) round
> of review. The main difficulty in reviewing is understanding the design
> and the relation in between our current model of extensions and what
> this patch offers.

I'm afraid this really needs more work, at least of the more mundane
kind. I started working through this patch, but when I hit on
"get_template_oid", I was reminded of the discussion we had back in
January around using just 'template' everywhere.

http://www.postgresql.org/message-id/20130118182156.GF16126@tamriel.snowman.net

We already have other 'template' objects in the system and I'm not
excited about the confusion. This also applies to 'CreateTemplate',
'CreateTemplateTupleDesc', right down to 'template.h' and 'template.c'.

Attached is a patch against v16 which fixes up a few documentation
issues (I'm pretty sure extension templates and aggregates are
unrelated..), and points out that there is zero documentation on these
new catalog tables (look for 'XXX' in the patch) along with a few
other areas which could use improvement.

Thanks,

Stephen

On 24.11.2013 00:19, Dimitri Fontaine wrote:
> Jeff Davis <pgsql(at)j-davis(dot)com> writes:
>> In the CF app, this is marked "Ready for Committer". That's a bit vague
>> here, considering Dimitri, you, Peter, and Alvaro are all committers.
>> Who is this patch waiting on? Is the discussion concluding, or does it
>> need another round of review?
>
> Thanks for the confusion I guess, but I'm no committer here ;-)
>
> This patch has received extensive review in July and I think it now
> properly implements the design proposed by Tom and Heikki in 9.3/CF4.

Ok, since my name has been mentioned, I'll bite..

I still don't like this. What I suggested back in December was to have a
simple mechanism to upload an extension zip file to the server via libpq
(http://www.postgresql.org/message-id/50BF80A6.20500@vmware.com). The
idea developed from that into the concept of extension templates, but
the original idea was lost somewhere along the way.

Back in December, when I agreed that "upload zip file via libpq" might
be useful, Tom suggested that we call control+sql file a "template", and
the installed entity an "extension". So far so good. Now comes the
patch, and the extension template no longer means a control+sql file. It
means an entity that's installed in the database that contains the same
information as a control+sql file, but in a new format. In fact, what
*do* you call the control+sql file that lies on the filesystem? Not a
template, apparently.

I want to be able to download extension.zip from pgxn.org, and then
install it on a server. I want to be able to install it the traditional
way, by unzipping it to the filesystem, or via libpq by using this new
feature. I do *not* want to rewrite the extension using a new CREATE
TEMPLATE FOR EXTENSION syntax to do the latter. I want to be able to
install the *same* zip file using either method.

- Heikki

* Heikki Linnakangas (hlinnakangas(at)vmware(dot)com) wrote:
> On 24.11.2013 00:19, Dimitri Fontaine wrote:
> >This patch has received extensive review in July and I think it now
> >properly implements the design proposed by Tom and Heikki in 9.3/CF4.
>
> Ok, since my name has been mentioned, I'll bite..
>
> I still don't like this. What I suggested back in December was to
> have a simple mechanism to upload an extension zip file to the
> server via libpq
> (http://www.postgresql.org/message-id/50BF80A6.20500@vmware.com).
> The idea developed from that into the concept of extension
> templates, but the original idea was lost somewhere along the way.

I hate to admit it, but I kind of agree.. While reviewing the patch and
thinking about it, the whole thing really did start to strike me as a
little too 'meta'. We want a way to package and ship extensions which
can then be loaded via the libpq protocol. I'm not sure that there's
really any need to track all of this in catalog tables.

Also as part of the patch review, I went back and looked through some of
the older threads around this and noticed the understandable concern,
given the current patch, of non-superusers being able to create
extension templates. I'm not sure that an approach which allows a zip
file to be uploaded would be something we could allow non-superusers to
do, but I really feel like we need a way for non-superusers to create
extensions (when they don't have untrusted-language components).

Now, given these two trains of thought, I start to see that we may want
to avoid non-superusers being able to create arbitrary files on disk,
even in a controlled area. We've managed that for various other objects
(tables, et al), I'm sure we could work out a solution to that issue...

Thanks,

Stephen

On Tue, 2013-11-26 at 01:37 +0200, Heikki Linnakangas wrote:
> Back in December, when I agreed that "upload zip file via libpq" might
> be useful, Tom suggested that we call control+sql file a "template", and
> the installed entity an "extension".

Simply uploading "safe" extension files (i.e. not native libraries)
using the protocol seems narrower in scope and less controversial.

However, I like the idea of putting extensions in the catalogs (aside
from DSOs of course), too. Setting aside compatibility problems with
existing extensions, do you think that's a reasonable goal to work
toward, or do you think that's a dead end?

Regards,
Jeff Davis

Hi,

Heikki Linnakangas <hlinnakangas(at)vmware(dot)com> writes:
> I still don't like this. What I suggested back in December was to have a
> simple mechanism to upload an extension zip file to the server via libpq
> (http://www.postgresql.org/message-id/50BF80A6.20500@vmware.com). The idea
> developed from that into the concept of extension templates, but the
> original idea was lost somewhere along the way.

And I will quote Andres' answer to your same proposal:

http://www.postgresql.org/message-id/20121205172747.GC27424@awork2.anarazel.de

So having a mode for pg_dump that actually makes dumps that are usable
for recovering after a disaster seems sensible to me. Otherwise you
need to redeploy from the VCS or whatever, which isn't really what you
want when restoring a database backup.

Comparing the situation to the one where you have extensions provided
by the packaging system or by /contrib or whatever doesn't seem to be
all that valid to me.

If you continue reading the thread from back then, the conclusion was to
drop the patch I was then proposing and instead work on the one we are
currently reviewing.

> Back in December, when I agreed that "upload zip file via libpq" might be
> useful, Tom suggested that we call control+sql file a "template", and the
> installed entity an "extension". So far so good. Now comes the patch, and
> the extension template no longer means a control+sql file. It means an
> entity that's installed in the database that contains the same information
> as a control+sql file, but in a new format. In fact, what *do* you call the
> control+sql file that lies on the filesystem? Not a template, apparently.

It's historical. To make it possible to start with some extension patch
in the 9.0 development cycle, it's been decided to only target the
contrib style extensions. Thanks to that we add something in 9.1.

In practice, the patch currently under review makes it so that both the
file system based model and the catalog based model behave the same (as
much as possible and sensible, and thanks to lots of reviewing efforts
from Markus Wanner), so we could be refering to the file system based
model as “template”.

> I want to be able to download extension.zip from pgxn.org, and then install
> it on a server. I want to be able to install it the traditional way, by
> unzipping it to the filesystem, or via libpq by using this new feature. I do
> *not* want to rewrite the extension using a new CREATE TEMPLATE FOR
> EXTENSION syntax to do the latter. I want to be able to install the *same*
> zip file using either method.

I would like to be able to support that, and the theory is attractive.
In practice, it's not that simple.

PGXN implements a source based distribution model, and most extensions
over there are following the same model, where the needed SQL files are
derived from sources at build time, using make(1).

See the following examples, the first one includes a C source file and
the second one is all PL stuff:

http://pgxn.org/dist/first_last_agg/
http://api.pgxn.org/src/first_last_agg/first_last_agg-0.1.2/

sql/$(EXTENSION)--$(EXTVERSION).sql: sql/$(EXTENSION).sql
cp $< $@

http://pgxn.org/dist/mimeo/1.0.1/
http://api.pgxn.org/src/mimeo/mimeo-1.0.1/

sql/$(EXTENSION)--$(EXTVERSION).sql: sql/tables/*.sql sql/functions/*.sql
cat $^ > $@

So, to support uploading PGXN zip files directly within the backend, now
the backend must be in a position to unpack the archive and build the
extension, then it must know where the build artefacts are going to be
found or it needs to `make install` in a known prefix and follow our
current conventions to find the files.

As I said to David Wheeler when he did build PGXN, I don't think that a
source level distribution is going to help us dealing with production
deployments.

So, while I understand where you're coming from, please tell me what are
your answers for those two design questions about the Extension template
idea:

- what should happen at pg_restore time?

- do you really want the extension build infrastructure in core?

My current thinking is to build the missing infrastructure as a
contrib module that will know how to divert CREATE EXTENSION with an
Event Trigger and apply the necessary magics at that time, and fill
in the Extension Templates for you.

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

Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
> So, to support uploading PGXN zip files directly within the backend, now
> the backend must be in a position to unpack the archive and build the
> extension, then it must know where the build artefacts are going to be
> found or it needs to `make install` in a known prefix and follow our
> current conventions to find the files.

As I've said multiple times before, this is an absolute nonstarter.
It's insane from a security standpoint, and it requires a whole lot
of infrastructure that wouldn't be there on a production box
(starting with the Postgres header files, but even a C compiler
wouldn't necessarily be there).

What you are looking for is a software distribution channel. A postgres
daemon isn't that and we shouldn't try to make it that.

regards, tom lane

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
> > So, to support uploading PGXN zip files directly within the backend, now
> > the backend must be in a position to unpack the archive and build the
> > extension, then it must know where the build artefacts are going to be
> > found or it needs to `make install` in a known prefix and follow our
> > current conventions to find the files.
>
> As I've said multiple times before, this is an absolute nonstarter.
> It's insane from a security standpoint, and it requires a whole lot
> of infrastructure that wouldn't be there on a production box
> (starting with the Postgres header files, but even a C compiler
> wouldn't necessarily be there).
>
> What you are looking for is a software distribution channel. A postgres
> daemon isn't that and we shouldn't try to make it that.

I tend to agree and I've been trying to hash this out with Dimitri on
IRC this morning. One proposal that I personally like is the notion of
having an external-to-PG client which works with pgxn and simply
installs the various objects into a schema based on some naming schema,
with a table in that schema which handles the control information. This
external utility would handle upgrades by looking at the 'control' table
and then figuring out which script from the pgxn package needs to be
run.

These wouldn't be PG "extensions" really though, which it seems folks
are pretty hung up on. There would also be no support for binary or
untrusted components, which is a bit frustrating, as you'd like to be
able to support those if you're a superuser. Trying to build both into
one "extension template" structure, or what-have-you, seems to be
causing us to fail to make any progress on either use-case.

Thanks,

Stephen

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> As I've said multiple times before, this is an absolute nonstarter.

FWIW, I was explaining the model that I didn't want to follow.

Thanks for approving, even if that's not a surprise as the model I did
follow is the one we agreed on a year ago.

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

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> These wouldn't be PG "extensions" really though, which it seems folks
> are pretty hung up on. There would also be no support for binary or
> untrusted components, which is a bit frustrating, as you'd like to be
> able to support those if you're a superuser. Trying to build both into
> one "extension template" structure, or what-have-you, seems to be
> causing us to fail to make any progress on either use-case.

So, plenty of points to address separately here:

- why do we want extensions to manage PL user code?

- what about managing extensions with modules from the protocol
directly, if we have Extension Templates ?

- is it possible to open the feature to non-superusers ?

Let's have at it.

# Why do we want extensions to manage PL user code?

Extensions allows to manage any number of database objects as a single
entity with a very clear and simple enough life cycle management
tooling:

create extension …
alter extension … update to …
drop extension … [cascade]

\dx
\dx+
pg_available_extensions()
pg_available_extension_versions()

Plus, you can handle inter-extension dependencies, albeit currently in a
very limited way, but that's still something.

Now that we have those tools, I want to be able to use them when dealing
with a bunch of PL code because I didn't find a better option in
PostgreSQL for dealing with such code.

It could well be that the “package” thing that I couldn't find tonight
in the SQL standard documents are better suited to manage user code, but
after having had to write management code and use extensions, I know I
want to deal with PL user code the same way I deal with extensions.

Of course, it's already possible to do so, packaging the user code into
a set of files that you then need to install at the right place on the
filesystem (a place owned by root in most cases), so I already did have
the pleasure to use extensions for user code. And I also had to write
all the packaging myself, then ship it to several nodes, etc.

I think that opening Extensions to be easy to use without requiring root
level access to the database server's filesystem would be a great idea,
even if it would take, I don't know, say, 3 years of development to get
there.

# Extension Templates and Binary Modules

Then as soon as we are able to CREATE EXTENSION mystuff; without ever
pre-installing files on the file system as root, then we would like to
be able to do just that even with binary modules.

The current documentation of the dynamic_library_path GUC makes me
believe it's possible already without patching the backend. We will need
to patch the extension's CREATE FUNCTION statements not to hardcode
$libdir in there of course. That could be done automatically by some
separate distribution packaging software.

Some cases would still need more work before being supported within that
frame:

- Hot Standby
- Modules that depend on other libs being installed.

# Extension Templates and Superusers

The problem found here is that if a non privileged user installs an
extension template named “pgcyrpto” then the superuser installs what he
believes is the extension “pgcrypto”, the malicious unprivileged user
now is running his own code (extension install script) as a superuser.

The current patch will prioritize file-based templates when an extension
is provided both from the file system and the catalogs, so for the
situation to happen you need to have forgotten to install the proper
system package. Still.

I've been asked (if memory serves) to then limit the Extension Templates
feature to superuser, which is a very bad thing™.

What I now think we should do is only grant superusers the privileges to
install an extension from a template they own or is owned by another
superuser.

The general rule being that only the role who did install the template
would be allowed to install an extension with it, so that you can't have
a privilege escalation happening, IIUC.

As a superuser, use SET ROLE first.

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

On 11/26/2013 10:07 PM, Dimitri Fontaine wrote:
> What I now think we should do is only grant superusers the privileges to
> install an extension from a template they own or is owned by another
> superuser.

Say what? Superusers bypass all privileges by definition.

--
Vik

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> We already have other 'template' objects in the system and I'm not
> excited about the confusion. This also applies to 'CreateTemplate',
> 'CreateTemplateTupleDesc', right down to 'template.h' and 'template.c'.

The current code is made to expose the notion of a template and only
implements one kind of template, for extensions. Still the
ProcessUtility hooking has been made so as to make it easy adding new
templates.

Given that I haven't seen any other template idea in the years I've been
contributing to PostgreSQL other than the FTS template, and that I'm
seeing no other candidate, I took the arbitrary decision not to open the
sources more than that to the idea of other kinds of templates.

We could of course have a 50 lines templates.c file that calls into an
extension_template.c file for the meat of the implementation if that's
prefered by the project.

Just tell me about that, it's not in the attached patch.

> Attached is a patch against v16 which fixes up a few documentation
> issues (I'm pretty sure extension templates and aggregates are
> unrelated..), and points out that there is zero documentation on these
> new catalog tables (look for 'XXX' in the patch) along with a few
> other areas which could use improvement.

I merged your patch in, rebased against master, fixed some more typos I
found, and filled in the gaps you found in the docs. Version 17 of the
patch is attached to that email, passes `make check`.

ENOTIME for building docs, will do tomorrow, I though you might
appreciate an update meanwhile (and with some luck docs still build
fine).

The other main point that will change the current code is dealing with
superusers and security concerns. Baring objections, I'm going to
implement a variation of what I did propose in the thread:

When a superuser CREATE EXTENSION against a template that has been
provided by a non-privileged user, automatically SET ROLE to that user
before doing so, avoiding escalation privileges.

If that's not what the superuser intended, then it still is possible for
him to ALTER TEMPLATE FOR EXTENSION … OWNER TO himself beforehand.

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

On Wed, 2013-11-27 at 18:34 +0100, Dimitri Fontaine wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > We already have other 'template' objects in the system and I'm not
> > excited about the confusion. This also applies to 'CreateTemplate',
> > 'CreateTemplateTupleDesc', right down to 'template.h' and 'template.c'.
>
...
> We could of course have a 50 lines templates.c file that calls into an
> extension_template.c file for the meat of the implementation if that's
> prefered by the project.

I think that Stephen was just talking about the naming. I would have
expected the names to be something like "xtmpl" (which is the shortest
abbreviation that came to my mind) rather than "tpl", for instance. Use
of "template" is a bit ambiguous.

> I merged your patch in, rebased against master, fixed some more typos I
> found, and filled in the gaps you found in the docs. Version 17 of the
> patch is attached to that email, passes `make check`.
>
> ENOTIME for building docs, will do tomorrow, I though you might
> appreciate an update meanwhile (and with some luck docs still build
> fine).

Yes, they build fine.

However, I find the "full version" quite awkward still. I don't think
it's purely a documentation issue -- the "default full version" concept
itself is a bit awkward.

If I understand correctly, it goes something like this:

When a user does:
CREATE EXTENSION foo VERSION '3.0';

and there are templates for 1.0, 2.0, and 2.1, and upgrades from
1.0->2.0, 2.0->2.1, and 2.1->3.0, then there are three possible actions
that might be taken:

1. Install 1.0 and run three upgrades
2. Install 2.0 and run two upgrade
3. Install 2.1 and run one upgrade

There are two ways to disambiguate:

1. Specify the initial version using "FROM 2.0" in CREATE EXTENSION
command (though the documentation seems to say that the FROM clause is
only useful for unpackaged, for some reason)
2. If not specified there, it falls through to DEFAULT FULL VERSION
for the extension template

But that's a little strange. Many extensions are likely to have all
relevant versions available in full, and DEFAULT FULL won't kick in.
Those that do make use of upgrade-only paths are likely to have a few
full versions and a few upgrade scripts based off each. If you pick a
default full version of 2.0, then it won't be useful for installing
version 1.0, nor version 23.7. In each of those cases, you'll want to
specify the FROM clause in CREATE EXTENSION.

I suppose the idea is to avoid the need for specifying "FROM" in the
common case where you want to install the latest version. But it seems
like you could just as well keep a full copy of the extension's latest
version, and just prune them out when they become old.

Unless I'm missing something, I'd be inclined to just get rid of the
concept of DEFAULT FULL VERSION just to keep the documentation simpler
without losing any real functionality.

In passing, I'll also make a quick complaint about the code:

/*
* If we have a full script for the target version (or a create
template),
* we don't need to care about unpackaged or default_major_version, nor
* about upgrade sequences.
*/

...

* If the user did
* ask for a target version that happens to be the same as the
* default_full_version, just install that one directly.

The second one (in an "else" branch) shouldn't happen, assuming
default_full_version points at a proper full version, right?

Regards,
Jeff Davis

On Sat, 2013-11-30 at 01:05 -0800, Jeff Davis wrote:
> Unless I'm missing something, I'd be inclined to just get rid of the
> concept of DEFAULT FULL VERSION just to keep the documentation simpler
> without losing any real functionality.

I found some explanation of the original reasoning in these threads:

http://www.postgresql.org/message-id/CA
+Tgmoae3Qs4QbQfxOUzZFxRSxA0zy8ibSOYSuuTzDUMPeAkAg(at)mail(dot)gmail(dot)com
http://www.postgresql.org/message-id/m2622fy43s.fsf@2ndQuadrant.fr
http://www.postgresql.org/message-id/m2k44m9oyo.fsf@2ndQuadrant.fr

It seems like it's for pg_dump, so it can avoid outputting the extension
templates and just say "VERSION 'x.y'" without worrying about which
version it needs to start from.

That seems like a legitimate purpose, but I think we can come up with
something that's a little easier on users and easier to document (and
name). Perhaps just find the shortest upgrade path to the version
requested (using some arbitrary but deterministic tiebreaker)?

Regards,
Jeff Davis

On Wed, 2013-11-27 at 18:34 +0100, Dimitri Fontaine wrote:
> The other main point that will change the current code is dealing with
> superusers and security concerns. Baring objections, I'm going to
> implement a variation of what I did propose in the thread:
>
> When a superuser CREATE EXTENSION against a template that has been
> provided by a non-privileged user, automatically SET ROLE to that user
> before doing so, avoiding escalation privileges.

That proposal is worded like a special case for superusers, and I don't
see why. If the security model is that an extension script is run with
as the template owner, then we should just do that universally. If not,
making a special case for superusers undermines the security of
powerful-but-not-superuser roles.

I haven't looked in detail at the security issues here... is this the
result of a consensus or are there still differing opinions?

> If that's not what the superuser intended, then it still is possible for
> him to ALTER TEMPLATE FOR EXTENSION … OWNER TO himself beforehand.

Doesn't sound like a clean workaround.

We already have a model for executing functions, and those are black
boxes of code as well. If we deviate too much from that, I think we're
inviting problems.

Heikki expressed some concerns here:

http://www.postgresql.org/message-id/5152FF3D.4000401@vmware.com

followed by Robert. The concerns seem mostly about the namespace,
because it's hard to be sure that "CREATE EXTENSION foo" installs the
"foo" you expect. Those are very valid practical concerns; but I agree
with Dimitri that this is not a fundamental problem, and we might find a
compromise here that can work.

One idea: if a non-superuser role creates an extension template, then it
is only visible to that exact same role.

The first version might only allow superusers to create extension
templates, but it would be nice to have a plan for non-superusers soon.

Aside: why do file-based templates shadow catalog-based templates?
Shouldn't we just throw an error if both are available at CREATE
EXTENSION time?

Also, I notice that the extension templates are not in shared catalogs;
was that discussed?

(Some of these issues seem underdocumented, as well.)

Regards,
Jeff Davis

On Tue, 2013-11-26 at 01:37 +0200, Heikki Linnakangas wrote:
> I want to be able to download extension.zip from pgxn.org, and then
> install it on a server. I want to be able to install it the traditional
> way, by unzipping it to the filesystem, or via libpq by using this new
> feature.

I see where you're coming from, but after some thought, and looking at
the patch, I think we really do want a catalog representation for (at
least some) extensions.

Dealing with files is a conceptual mismatch that will never be as easy
and coherent as something that database manages and understands.
Replication, backup, and Postgres-as-a-service providers are clear
examples, and I just don't see a file-based approach solving those
problems.

One example is tablespaces, which are a constant source of
administrative pain. That's inherent to tablespaces, just like it's
inherent to native shared libraries, and we can't do much about them.

But bringing more of an extension into the catalog can be done, and I
think we'll see big benefits from that.

Imagine something like (this comes from an in-person conversation with
Dimitri a while ago; hopefully I'm not misrepresenting his vision):

=# select pgxn_install_template('myextension');

or even:

=# select pgxn_update_all_templates();

That is much closer to what modern language environments do -- ruby,
python, go, and haskell all have a language-managed extension service
independent of the OS packaging system and don't require more privileges
or access than running the language.

That being said, there some things about in-catalog templates that need
some more thought:

1. If someone does want their OS to install extensions for them (e.g.
the contrib package), how should that be done? This usually works fine
with the aforementioned languages, because installation is still just
dropping files in the right place. Postgres is different, because to put
something in the catalog, we need a running server, which is awkward for
a packaging system to do.

2. When 9.4 gets released, we need some solid advice for extension
authors. If they have a native shared library, I assume we just tell
them to keep using the file-based templates. But if they have a SQL-only
extension, do we tell them to port to the in-catalog templates? What if
they port to in-catalog templates, and then decide they just want to
optimize one function by writing it in native code? Do they have to port
back? What should the authors of SQL-only extensions distribute on PGXN?
Should there be a migration period where they offer both kinds of
templates until they drop support for 9.3?
a. Some extensions have quite a few .sql files. It seems awkward to
just cat them all into one giant SQL query. Not a rational problem, but
it would bother me a little to tell people to squash their
otherwise-organized functions into a giant blob.

3. What do we do about native shared libraries? Ultimately, I imagine
that we should handle these similarly to tablespaces: have a real
database object with an OID that extensions or functions can depend on,
and create a symlink (with the OID as the link name) that points to the
real file on disk. We could also export some new symbols like the shared
library name and version for better error checking.

4. Do we live with both file-based and catalog-based templates
forever? I guess probably so, because the file-based templates probably
are a little better for contrib itself (because the complaints about
relying on OS packaging don't apply as strongly, if at all).

Regards,
Jeff Davis

On Wed, 2013-11-27 at 18:34 +0100, Dimitri Fontaine wrote:
> I merged your patch in, rebased against master, fixed some more typos I
> found, and filled in the gaps you found in the docs. Version 17 of the
> patch is attached to that email, passes `make check`.

A couple more questions:

1. During the initial development of extensions, there was discussion
about ordered version numbers and dependencies on the version (e.g.
requires foo >= 2.1). Outside the scope of this patch, of course, but is
that something that we can still do later? Or are we building
infrastructure that will make that nearly impossible in a release or
two?

2. People will want to start using this feature to control and version
their schema. Do you have comments about that? Should we eventually try
to get extensions to support that use case (if they don't already), or
should that be a completely different feature?

Regards,
Jeff Davis

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> I think that Stephen was just talking about the naming. I would have
> expected the names to be something like "xtmpl" (which is the shortest
> abbreviation that came to my mind) rather than "tpl", for instance. Use
> of "template" is a bit ambiguous.

To be honest I'm not following along with the complaint. What object
would you liked to be named xtmpl rather than what it is called now?

If you're refering to the catalog column names such as tplname and
tplowner and the like, the tradition AFAICS is to find a trigram prefix
for those entries… and that's what I did.

> However, I find the "full version" quite awkward still. I don't think
> it's purely a documentation issue -- the "default full version" concept
> itself is a bit awkward.

Yes. I coulnd't find a better name, and I would quite like that we do.

There are two ideas behind that feature:

- When we released hstore 1.1 we had a choice of either providing
still hstore--1.0.sql and hstore--1.1.sql or only the latter,
deprecating the 1.0 version for the next release.

The default_major_version feature allows to only ship hstore-1.0.sql
and still offer full support for hstore 1.0 and 1.1.

Note that it's problematic given the current implementation of
pg_upgrade, where when migrating from 9.1 to 9.2 (IIRC that's when
hstore got to 1.1) you can result in either still having hstore 1.0
in your database if you used pg_upgrade or have hstore 1.1 already
if you used pg_dump and pg_restore.

Note also that if you install 9.2 then by a technically constrained
choice of policy in the project, then you cannot continue using
hstore 1.0.

So the goal is to simplify extension authors management of sql
install and upgrade scripts while giving more options to the users
of extension wrt the version they want to be using.

- With Extension Templates, the extension author can be providing
scripts foo--1.0.sql and foo--1.0--1.1.sql and run the upgrade with

ALTER EXTENSION foo UPDATE TO '1.1';

Now what happens at pg_restore time? We only have the 1.0 and
1.0--1.1 scripts, yet we want to be installing foo version 1.1.

So we need the "default_major_version" capabilities, whatever the
name we choose. Hence my inclusion of that feature in the Extension
Template patch.

> If I understand correctly, it goes something like this:
>
> When a user does:
> CREATE EXTENSION foo VERSION '3.0';
>
> and there are templates for 1.0, 2.0, and 2.1, and upgrades from
> 1.0->2.0, 2.0->2.1, and 2.1->3.0, then there are three possible actions
> that might be taken:
>
> 1. Install 1.0 and run three upgrades
> 2. Install 2.0 and run two upgrade
> 3. Install 2.1 and run one upgrade

With PostgreSQL versions 9.1, 9.2 and 9.3, given the scripts you're
listing, the command you propose will just fail. Full stop. ERROR.

> The second one (in an "else" branch) shouldn't happen, assuming
> default_full_version points at a proper full version, right?

Will review, thanks.

> It seems like it's for pg_dump, so it can avoid outputting the extension
> templates and just say "VERSION 'x.y'" without worrying about which
> version it needs to start from.

Exactly. We need pg_dump to be smart enough for handling the case as
soon as we have Extension Templates, and we already have said smarts in
the backend code. They were just not applied at CREATE EXTENSION time
before this patch.

> That seems like a legitimate purpose, but I think we can come up with
> something that's a little easier on users and easier to document (and
> name). Perhaps just find the shortest upgrade path to the version
> requested (using some arbitrary but deterministic tiebreaker)?

The danger is that the shorter path could well include a downgrade, and
nobody tests for downgrading paths IME. So I keep thinking we should ask
the extension author to give us a starting point. Now, if you have a
better name for it than “default_full_version”, I'm all ears!

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

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
>> When a superuser CREATE EXTENSION against a template that has been
>> provided by a non-privileged user, automatically SET ROLE to that user
>> before doing so, avoiding escalation privileges.
>
> That proposal is worded like a special case for superusers, and I don't
> see why. If the security model is that an extension script is run with
> as the template owner, then we should just do that universally. If not,
> making a special case for superusers undermines the security of
> powerful-but-not-superuser roles.

I like that idea yes.

> I haven't looked in detail at the security issues here... is this the
> result of a consensus or are there still differing opinions?

AFAIK past reviewers came up with the privilege escalation use case and
said we'd better have that feature a superuser only one. It's playing
safe, but I wish we could find another solution.

> We already have a model for executing functions, and those are black
> boxes of code as well. If we deviate too much from that, I think we're
> inviting problems.

So maybe we should have “SECURITY DEFINER” and “SECURITY INVOKER”
extension templates, the default being “SECURITY DEFINER”?

> Aside: why do file-based templates shadow catalog-based templates?
> Shouldn't we just throw an error if both are available at CREATE
> EXTENSION time?

That sounds good too. We need to ERROR out at UPDATE time too of course.

> Also, I notice that the extension templates are not in shared catalogs;
> was that discussed?

Yes it was. The current model for extensions is to be per-database, but
it's limited by the way we deal with modules (.so), for security reasons
that encompass the per-database model.

Also consider multi-tenancy installations. Certainly, you don't want any
database owner to be able to review PL code from any other database
owner in the same cluster when each database owner is another customer.

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

On Saturday, November 30, 2013, Dimitri Fontaine wrote:
>
> Also consider multi-tenancy installations. Certainly, you don't want any
> database owner to be able to review PL code from any other database
> owner in the same cluster when each database owner is another customer
>

I'm planning to do a more comprehensive response, but the only use case
that I see for extension templates is to be in a shared catalog and I don't
see that as being a particularly compelling case.

Without that, all of the information about a given extension is already in
the database in our dependency system. As you pointed out, there was
previously a notion of "inline" templates. I'm not sure that particular
patch is exactly where we want to go, but I absolutely do not like this
idea that we have a "template" on a per-database level which does nothing
but duplicate most of the information we *already have*, since you have to
assume that if the extension template (which is per-database) exists then
the extension has also been created in the database.

Having a versioning notion (and whatever other meta data we, or an
extension author, feels is useful) for what are otherwise simple containers
(aka the schematic we already have..) makes sense and it would be great to
provide support around that, but not this duplication of
object definitions.

Thanks,

Stephen

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> 1. During the initial development of extensions, there was discussion
> about ordered version numbers and dependencies on the version (e.g.
> requires foo >= 2.1). Outside the scope of this patch, of course, but is
> that something that we can still do later? Or are we building
> infrastructure that will make that nearly impossible in a release or
> two?

The best answer I can think of as of today has been proposed on list
already in a patch names "finer extension dependencies", where I failed
miserably to explain my ideas correctly to -hackers. Here are the
archive links to the opening of the thread and the latest known version
of the patch:

http://www.postgresql.org/message-id/m2hb0y2bh3.fsf@hi-media.com
http://www.postgresql.org/message-id/871uoa4dal.fsf@hi-media-techno.com

Meanwhile, at pgcon 2013 unconference about pg_upgrade, with Bruce and
Peter we had a chat about this problem, coming from a different parallel
that might allow for a better explaning of what is it all about:

Distribution binary packages nowadays are tracking symbol level
dependencies in between binaries (shared objects and programs). To do
so they use some dynamic capabilities (objdump and the like, IIUC).

In the PostgreSQL we just can't do that automatically, mainly because of
EXECUTE support in PL functions and DO blocks. We are not even able to
track inter-function dependencies because of that, so that you're
allowed to DROP a function that you actually depend on.

The “features” proposal where an extension “provides” a set of at least
one feature (its own name) is a way for the extension author to list a
subset of the symbols provided in his extension so that other extensions
are able to depend on some of them. Then this set of “features” can be
edited from a version to the next, adding new “features” and removing
existing ones.

With “feature” dependency tracking, we are able to prevent extension
updates when the new version deprecates features that we know are still
in the dependency graph:

# ALTER EXTENSION foo UPDATE TO '2.0'
ERROR: extension bar depends on feature "baz" provided by "foo"
version '1.1'

My plan is to be able to someday explain what I was trying to do in a
way that allows for the patch to be considered again, and I have some
faith in that plan because I remember Peter suddenly understanding what
this was all about when in Ottawa earlier this year.

It should be quite trivial to update the patch I had then to also
support Extension Templates, basically adding a new column in the
catalogs, grammar support in the control properties, and making sure
that the value actually goes from the template into the extension
catalogs at CREATE or UPDATE time.

Mind you, if the Extension Templates patch fate is solvable this Commit
Fest, and provided that the “extension features” appears to be a good
idea for someone else than just me, I would happily be proposing an
updated version of it for next Commit Fest, in time fro 9.4. It was a
very simple patch.

Of course, given Extension Templates, I would be adding some regression
tests to said patch ;-)

> 2. People will want to start using this feature to control and version
> their schema. Do you have comments about that? Should we eventually try
> to get extensions to support that use case (if they don't already), or
> should that be a completely different feature?

The tension between extensions and database schema is pretty simple: an
Extension content is not part of pg_dump.

With the idea of Extension Templates, you now have two kinds of
templates: file system based templates, excluded from the dumps and
managed separately (typically managed as an OS package), and catalog
templates, parts of the dumps, fully managed by PostgreSQL.

Both ways, at pg_restore time the extension is built again from the
templates, wherever we find those. The term “extension” covers for a
single set of behaviours.

So my current thinking is that Extension Templates as currently
developped will not be a solution to version controling your database
schema. I have not been thinking about how to make that happen other
than the following two points:

- yes I would like PostgreSQL to offer database versioning
capabilities;

- no I don't think Extensions can be made into providing support for
that set of features.

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

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> Without that, all of the information about a given extension is already in
> the database in our dependency system. As you pointed out, there was

That's not entirely true. We would still be missing some information
from the extension control file.

> previously a notion of "inline" templates. I'm not sure that particular
> patch is exactly where we want to go, but I absolutely do not like this
> idea that we have a "template" on a per-database level which does nothing
> but duplicate most of the information we *already have*, since you have to
> assume that if the extension template (which is per-database) exists then
> the extension has also been created in the database.

That's a classic bootstrap problem. If you consider that the extension
is already installed, then you don't need to know how to install it.

The main feature that the patch provides is installation path for an
extension that doesn't involve the server's file system.

> Having a versioning notion (and whatever other meta data we, or an
> extension author, feels is useful) for what are otherwise simple containers
> (aka the schematic we already have..) makes sense and it would be great to
> provide support around that, but not this duplication of
> object definitions.

I don't like duplication either, we've just been failing to find any
alternative with pg_restore support for the last 3 years.

If you want the simplest possible patch that would enable you bypassing
the file system, here's what I would be proposing: have a special flag
allowing CREATE EXTENSION to just prepare pg_extension catalog entries.

Then create your objects as usual, and use ALTER EXTENSION … ADD … to
register them against the existing extension.

That would work beautifully, and of course you would have to do that
again manually at pg_restore time after CREATE DATABASE and before
pg_restore, or you would need to change the fact that extensions objects
are not part of your pg_dump scripts, or you would have to name your new
thing something else than an extension.

Also, please note that I did propose that design when working on the
first patch series for extension (8.4 and 9.0 eras), or at least some
variant where the control properties came in from some command rather
than from a file, and it was rejected because the CREATE EXTENSION
bootstrapping was then judged too complex, and it was not clear how
extension authors were going to maintain their scripts.

The current extension model is simple enough to reason about. A script
must be provided in a template and is executed at CREATE EXTENSION time
or at ALTER EXTENSION UPDATE time, and pg_dump only contains the CREATE
EXTENSION command, so that pg_restore has to find the template again.

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

Dimitri,

On Saturday, November 30, 2013, Dimitri Fontaine wrote:
>
> The current extension model is simple enough to reason about. A script
> must be provided in a template and is executed at CREATE EXTENSION time
> or at ALTER EXTENSION UPDATE time, and pg_dump only contains the CREATE
> EXTENSION command, so that pg_restore has to find the template again.
>

I understand that folks have complained about pg_dump/restore knowing "too
much" about extensions, but I do not find that a compelling argument- it's
being made from the perspective that an extension is defined by files on
the file system. Perhaps that means that what we're talking about aren't
extensions, but in that case, such arguments do not make much sense.

Thanks,

Stephen

On Sat, 2013-11-30 at 22:55 +0100, Dimitri Fontaine wrote:
> So we need the "default_major_version" capabilities, whatever the
> name we choose. Hence my inclusion of that feature in the Extension
> Template patch.

What we need is a means to install versions for which we don't have full
SQL, but for which there exists some upgrade path.
"default_major_version" is one possible approach to that, but it seems
pretty awkward to me.

> > That seems like a legitimate purpose, but I think we can come up with
> > something that's a little easier on users and easier to document (and
> > name). Perhaps just find the shortest upgrade path to the version
> > requested (using some arbitrary but deterministic tiebreaker)?
>
> The danger is that the shorter path could well include a downgrade, and
> nobody tests for downgrading paths IME. So I keep thinking we should ask
> the extension author to give us a starting point. Now, if you have a
> better name for it than “default_full_version”, I'm all ears!

The problem with asking for a starting point is that:
(a) It's one more thing to get wrong;
(b) what we really care about is the ending point; and
(c) we have to find a path to the right endpoint, and that path might
surprise the extension author and/or user anyway.

It seems like in any real use case, the author would use a convention
that kept the upgrades/downgrades somewhat sane. A few examples might
be:

* Offer only earliest version and upgrades
* Offer only latest version and downgrades
* Offer full SQL script on major versions and upgrades to the point
releases

I don't see why we are trying to accommodate a case where the author
doesn't offer enough full SQL scripts and offers broken downgrade
scripts; or why that case is different from offering broken upgrade
scripts.

Regards,
Jeff Davis

On Sat, 2013-11-30 at 23:03 +0100, Dimitri Fontaine wrote:
> Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> >> When a superuser CREATE EXTENSION against a template that has been
> >> provided by a non-privileged user, automatically SET ROLE to that user
> >> before doing so, avoiding escalation privileges.
> >
> > That proposal is worded like a special case for superusers, and I don't
> > see why. If the security model is that an extension script is run with
> > as the template owner, then we should just do that universally. If not,
> > making a special case for superusers undermines the security of
> > powerful-but-not-superuser roles.
>
> I like that idea yes.

To clarify, I wasn't proposing that, I'd just like some consistent
security model.

> So maybe we should have “SECURITY DEFINER” and “SECURITY INVOKER”
> extension templates, the default being “SECURITY DEFINER”?

That doesn't seem to answer Heikki's stated concern, because a malicious
non-superuser would just declare the trojan extension to be SECURITY
INVOKER.

As I see it, the problem is more about namespacing than anything else.
It's analogous to a shell which includes the current directory in the
$PATH -- a malicious user can just name an executable "ls" and trick
root into executing it. The solution for a shell has nothing to do with
setuid; so I'm reluctant to base our solution on SECURITY DEFINER.

I prefer a solution that prevents the kind of name collisions that would
trick a privileged user. My strawman idea was to just say that an
extension template created by a non-superuser could only be instantiated
by that same user.

> Also consider multi-tenancy installations. Certainly, you don't want any
> database owner to be able to review PL code from any other database
> owner in the same cluster when each database owner is another customer.

That could be solved by permissions, as well, right?

Regards,
Jeff Davis

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> I don't see why we are trying to accommodate a case where the author
> doesn't offer enough full SQL scripts and offers broken downgrade
> scripts; or why that case is different from offering broken upgrade
> scripts.

That's fair enough I guess. I will work on automating the choice of the
first full script to use then, for next patch version.

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

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
>> So maybe we should have “SECURITY DEFINER” and “SECURITY INVOKER”
>> extension templates, the default being “SECURITY DEFINER”?
>
> That doesn't seem to answer Heikki's stated concern, because a malicious
> non-superuser would just declare the trojan extension to be SECURITY
> INVOKER.

It does answer if only superusers are allowed to install SECURITY
INVOKER templates, which I forgot to add in the previous email. Or at
least my understanding is that it could work that way.

> As I see it, the problem is more about namespacing than anything else.
> It's analogous to a shell which includes the current directory in the
> $PATH -- a malicious user can just name an executable "ls" and trick
> root into executing it. The solution for a shell has nothing to do with
> setuid; so I'm reluctant to base our solution on SECURITY DEFINER.
>
> I prefer a solution that prevents the kind of name collisions that would
> trick a privileged user. My strawman idea was to just say that an
> extension template created by a non-superuser could only be instantiated
> by that same user.

Yes that's a simpler model. And simpler is better when talking security.

The only drawback of that is to forbid the superuser from executing a
command. That would be new in PostgreSQL I think. We can work around
that with automating the SET ROLE to the template owner when a superuser
is creating the extension. That's what led me to the SECURITY DEFINER
proposition.

Either of those solution are fine to me, with or without the automated
SET ROLE when a superuser is installing an extension from a template
owned by a non-superuser.

Tell me your preference, I'll work on the code.

>> Also consider multi-tenancy installations. Certainly, you don't want any
>> database owner to be able to review PL code from any other database
>> owner in the same cluster when each database owner is another customer.
>
> That could be solved by permissions, as well, right?

I still think about extensions as being a per-database thing, and that
the current security policy makes if a per-major-version thing when the
extension contains a module (.so).

Also, the dynamic_library_path already allows us to make binary
extensions a per-database object again, baring incompatibilities that
would manifest themselves as run-time errors…

So I strongly vote against making the Extension Templates a set of
shared catalogs.

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

On Sun, 2013-12-01 at 15:58 +0100, Dimitri Fontaine wrote:
> Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> Either of those solution are fine to me, with or without the automated
> SET ROLE when a superuser is installing an extension from a template
> owned by a non-superuser.
>
> Tell me your preference, I'll work on the code.

This version (for this 'fest) should be superuser-only, because we don't
have enough consensus about the security model.

That being said, we don't want to prevent a change to allow
non-superusers in the future. So let's collect a few ideas, and leave
room to implement one of them later.

To throw another idea out, also based on the premise that it's a
namespace problem: if a non-superuser creates an extension template,
then we force a prefix of that user's username. So a superuser can
create an "base" extension template with no prefix, but if I create an
extension template it would be called something like "jdavis"."foo". To
be more consistent, we could have a reserved prefix that's always
assumed, similar to pg_catalog.

> I still think about extensions as being a per-database thing, and that
> the current security policy makes if a per-major-version thing when the
> extension contains a module (.so).
>
> Also, the dynamic_library_path already allows us to make binary
> extensions a per-database object again, baring incompatibilities that
> would manifest themselves as run-time errors…
>
> So I strongly vote against making the Extension Templates a set of
> shared catalogs.

I don't have much of an opinion on this point, but I also don't
understand your point. Can you clarify? What's the use case for
DB-specific extension templates?

I generally think of extension templates as universal, in that
"myExtension version 1.2.3" is exactly the same everywhere, and
immutable, so why not share it?

I understand why extensions (not templates) are per-DB, because you
might want to control which objects are available, and also control
which namespace they go in.

Regards,
Jeff Davis

* Jeff Davis (pgsql(at)j-davis(dot)com) wrote:
> To throw another idea out, also based on the premise that it's a
> namespace problem: if a non-superuser creates an extension template,
> then we force a prefix of that user's username. So a superuser can
> create an "base" extension template with no prefix, but if I create an
> extension template it would be called something like "jdavis"."foo". To
> be more consistent, we could have a reserved prefix that's always
> assumed, similar to pg_catalog.

Ugh, no. We have schemas already (which, as we all know, are even in a
'pg_namespace' catalog..), with permissions associated with them to
allow only certain users to create objects in them, etc.

> > I still think about extensions as being a per-database thing, and that
> > the current security policy makes if a per-major-version thing when the
> > extension contains a module (.so).
> >
> > Also, the dynamic_library_path already allows us to make binary
> > extensions a per-database object again, baring incompatibilities that
> > would manifest themselves as run-time errors…
> >
> > So I strongly vote against making the Extension Templates a set of
> > shared catalogs.
>
> I don't have much of an opinion on this point, but I also don't
> understand your point. Can you clarify? What's the use case for
> DB-specific extension templates?

This is one of my major issues with this approach in general- we're
building this very complicated structure for a use-case which doesn't
exist. There isn't actually any need for per-DB extension templates,
except as a way to create an extension that doesn't require sticking
files on the filesystem, but we only have that requirement because it's
what we've defined extensions *to be*, which is why these end up not
really being extensions but another thing entirely.

I'm completely behind the idea of having packages which install
functions/tables/whatever in some schema, can be installed by a regular
user, and even that the DB could provide some help in tracking metadata
(version information, etc) about those packages, but I don't see
building that on top of extensions in this way.

> I generally think of extension templates as universal, in that
> "myExtension version 1.2.3" is exactly the same everywhere, and
> immutable, so why not share it?

The concern, at least as I've understood it from talking to Dimitri, is
that you don't want everyone to see or have available every extension
template which exists; perhaps there is sensetive code, blah, blah.
Another requirement which doesn't really match up with how extensions
exist today.

Thanks,

Stephen

* Dimitri Fontaine (dimitri(at)2ndQuadrant(dot)fr) wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > Without that, all of the information about a given extension is already in
> > the database in our dependency system. As you pointed out, there was
>
> That's not entirely true. We would still be missing some information
> from the extension control file.

Fine, so we need an extra side-table, which needn't even be a catalog
table; though, as I've mentioned before, having PG help with managing
these extensions by tracking such information seems reasonable. What
isn't reasonable is having two nearly complete copies of every extension
installed into a given database.

> > previously a notion of "inline" templates. I'm not sure that particular
> > patch is exactly where we want to go, but I absolutely do not like this
> > idea that we have a "template" on a per-database level which does nothing
> > but duplicate most of the information we *already have*, since you have to
> > assume that if the extension template (which is per-database) exists then
> > the extension has also been created in the database.
>
> That's a classic bootstrap problem. If you consider that the extension
> is already installed, then you don't need to know how to install it.

I don't see how it's a bootstrap problem at all- we just need a way to
install the extension which doesn't involve the filesystem. That's
completely possible to do *without* storing two complete copies of the
extension in the PG catalogs.

> The main feature that the patch provides is installation path for an
> extension that doesn't involve the server's file system.

No, that isn't what this patch does, which is why I'm so frustrated by
it because what you describe is *exactly what I want*. This patch
doesn't do that though- it tries to provide a way to *mimic* the
filesystem part of PG extensions through the catalog- on a per-database
level. There's a number of problems with that approach: the filesystem
*sucks* when it comes to storing the information about an extension- we
do it for extensions today because we're working with the OS packaging
systems; we have *way* better information and understanding about an
extension once it's actually installed into the PG catalogs, why ignore
that instead of use it?; we're duplicating the filesystem-style (which
is next to useless to us..) definition of an extension into every
database we want to install it into for no good purpose; by having it
mimic the filesystem-style approach, it makes it very difficult to
reason about security, if non-superusers should be allowed to install
these things (or the extensions themselves...), etc.

> > Having a versioning notion (and whatever other meta data we, or an
> > extension author, feels is useful) for what are otherwise simple containers
> > (aka the schematic we already have..) makes sense and it would be great to
> > provide support around that, but not this duplication of
> > object definitions.
>
> I don't like duplication either, we've just been failing to find any
> alternative with pg_restore support for the last 3 years.

*That doesn't make this approach the right one*. If anything, I'm
afraid we've ended up building ourselves a rube goldberg machine because
of this constant struggle to fit a square peg into a round hole.

> If you want the simplest possible patch that would enable you bypassing
> the file system, here's what I would be proposing: have a special flag
> allowing CREATE EXTENSION to just prepare pg_extension catalog entries.
>
> Then create your objects as usual, and use ALTER EXTENSION … ADD … to
> register them against the existing extension.

That's basically what we already do with schemas today and hence is
pretty darn close to what I'm proposing. Perhaps it'd be a way to
simply version schemas themselves- heck, with that, we could even
provide that oft-asked-for schema delta tool in-core by being able to
deduce the differences between schema at version X and schema at
version Y.

> That would work beautifully, and of course you would have to do that
> again manually at pg_restore time after CREATE DATABASE and before
> pg_restore, or you would need to change the fact that extensions objects
> are not part of your pg_dump scripts, or you would have to name your new
> thing something else than an extension.

We would need a way to dump and restore this, of course.

> Also, please note that I did propose that design when working on the
> first patch series for extension (8.4 and 9.0 eras), or at least some
> variant where the control properties came in from some command rather
> than from a file, and it was rejected because the CREATE EXTENSION
> bootstrapping was then judged too complex, and it was not clear how
> extension authors were going to maintain their scripts.

This just makes me feel like the problem was trying to shoehorn this new
concept into the existing extension system. I don't see how this would
really change things for extension authors having to maintain their
scripts one way or the other.

> The current extension model is simple enough to reason about. A script
> must be provided in a template and is executed at CREATE EXTENSION time
> or at ALTER EXTENSION UPDATE time, and pg_dump only contains the CREATE
> EXTENSION command, so that pg_restore has to find the template again.

The current extension system is simple. This addition of extension
templates muddies things *significantly*.

Thanks,

Stephen

Jeff,

* Jeff Davis (pgsql(at)j-davis(dot)com) wrote:
> I see where you're coming from, but after some thought, and looking at
> the patch, I think we really do want a catalog representation for (at
> least some) extensions.

Perhaps I'm missing something- but we already *have* a catalog
representation for every extension that's ever installed into a given
database. A representation that's a heck of a lot better than a big
text blob.

> Dealing with files is a conceptual mismatch that will never be as easy
> and coherent as something that database manages and understands.
> Replication, backup, and Postgres-as-a-service providers are clear
> examples, and I just don't see a file-based approach solving those
> problems.

Agreed.

> But bringing more of an extension into the catalog can be done, and I
> think we'll see big benefits from that.

I'm not following here- what's 'missing'?

> Imagine something like (this comes from an in-person conversation with
> Dimitri a while ago; hopefully I'm not misrepresenting his vision):
>
> =# select pgxn_install_template('myextension');
>
> or even:
>
> =# select pgxn_update_all_templates();
>
> That is much closer to what modern language environments do -- ruby,
> python, go, and haskell all have a language-managed extension service
> independent of the OS packaging system and don't require more privileges
> or access than running the language.

I like the general idea, but I don't particularly see the need for the
backend PG process to be making connections to these external
repositories and pulling down files to execute. That could be done just
as simply by another process which works with the PG backend- ala how
dpkg and aptitude work together.

> That being said, there some things about in-catalog templates that need
> some more thought:
>
> 1. If someone does want their OS to install extensions for them (e.g.
> the contrib package), how should that be done? This usually works fine
> with the aforementioned languages, because installation is still just
> dropping files in the right place. Postgres is different, because to put
> something in the catalog, we need a running server, which is awkward for
> a packaging system to do.

You need a running PG for the *extension* to be installed, but with the
filesystem-based extension approach we have today, the "template" (which
are the files on the filesystem) don't need PG running, and if we had an
external tool which could work with the PG backend to install extensions
via libpq, just like the backend works with the files on the filesystem,
we wouldn't have this issue of bootstrapping the 'extension template'
into the catalog.

> 2. When 9.4 gets released, we need some solid advice for extension
> authors. If they have a native shared library, I assume we just tell
> them to keep using the file-based templates. But if they have a SQL-only
> extension, do we tell them to port to the in-catalog templates? What if
> they port to in-catalog templates, and then decide they just want to
> optimize one function by writing it in native code? Do they have to port
> back? What should the authors of SQL-only extensions distribute on PGXN?
> Should there be a migration period where they offer both kinds of
> templates until they drop support for 9.3?

This is one of the main things that I think Heikki was trying to drive
at with his comment- we really don't *want* to make extension authors
have to do anything different than what they do today. With an external
tool, they wouldn't need to and it would just be two different ways for
an extension to be installed into a given database. In the end though,
if we're telling people to 'port' their extensions, then I think we've
already lost.

> a. Some extensions have quite a few .sql files. It seems awkward to
> just cat them all into one giant SQL query. Not a rational problem, but
> it would bother me a little to tell people to squash their
> otherwise-organized functions into a giant blob.

'awkward' isn't the word I'd use, it's downright horrible.

> 3. What do we do about native shared libraries? Ultimately, I imagine
> that we should handle these similarly to tablespaces: have a real
> database object with an OID that extensions or functions can depend on,
> and create a symlink (with the OID as the link name) that points to the
> real file on disk. We could also export some new symbols like the shared
> library name and version for better error checking.

I'm sorry, but I do not see shared libraries working through this
system, at all. I know that goes against what Dimitri and some others
want, but I've talked with a few folks (such as Paul Ramsey of PostGIS)
about this notion and, from that perspective, it's almost laughable to
think we could ship shared libraries in this way. Even if we could
convince ourselves that there's some way for us to track the files on
the filesystem and work out all the per-database and whatever issues are
associated with that, it'd only work for the simplest shared libraries
which don't have any dependencies on other libraries on the system
(excepting, perhaps, libc6) and that narrows the use-case down
significantly, to the point where I don't feel it's worth all that
effort.

> 4. Do we live with both file-based and catalog-based templates
> forever? I guess probably so, because the file-based templates probably
> are a little better for contrib itself (because the complaints about
> relying on OS packaging don't apply as strongly, if at all).

We need the file-based extension "templates" because they deal with
shared libraries and because we've got them already. I don't think we
actually need to keep track of the specific commands used to define a
catalog-bassed extension inside the catalog, so I'm advocating for there
simply not being any "catalog-based template" system for extensions.
Extensions themselves are already "catalog-based".

Thanks,

Stephen

* Dimitri Fontaine (dimitri(at)2ndQuadrant(dot)fr) wrote:
> # Why do we want extensions to manage PL user code?

Having a management system for sets of objects is a *great* idea- and
one which we already have through schemas. What we don't have is any
kind of versioning system built-in or other metadata about it, nor do we
have good tooling which leverages such a versioning or similar system.
Extensions provide some of that metadata around schemas and object
definitions, but they are also currently defined to be built from SQL
scripts on the filesystem in a specific way and can "include" shared
libraries (though the PG extension, which is in the catalog, doesn't
really include the shared libraries, when you think about it...).

> # Extension Templates and Binary Modules
>
> Then as soon as we are able to CREATE EXTENSION mystuff; without ever
> pre-installing files on the file system as root, then we would like to
> be able to do just that even with binary modules.

I really just don't see this as being either particularly useful nor
feasible within a reasonable amount of effort. Shared libraries are
really the perview of the OS packaging system. If you want to build
some tool which is external to PG but helps facilitate the building and
installing of shared libraries, but doesn't use the OS packaging system
(and, instead, attempts to duplicate it) then go for it, but don't
expect to ship or install that through the PG backend.

> # Extension Templates and Superusers
>
> The problem found here is that if a non privileged user installs an
> extension template named “pgcyrpto” then the superuser installs what he
> believes is the extension “pgcrypto”, the malicious unprivileged user
> now is running his own code (extension install script) as a superuser.

For my part, the problem here is this notion of extension templates in
the PG catalog and this is just one symptom of how that's really not a
good approach.

Thanks,

Stephen

On 12/02/2013 05:34 AM, Stephen Frost wrote:
> * Jeff Davis (pgsql(at)j-davis(dot)com) wrote:
>> I see where you're coming from, but after some thought, and looking at
>> the patch, I think we really do want a catalog representation for (at
>> least some) extensions.
>
> Perhaps I'm missing something- but we already *have* a catalog
> representation for every extension that's ever installed into a given
> database. A representation that's a heck of a lot better than a big
> text blob.

Right. I think Jeff was thinking of a catalog representation for
extensions that haven't been installed yet, but are available in the
system and could be installed with CREATE EXTENSION foo. I wouldn't mind
having a catalog like that. Even without any of this extension template
stuff, it would be handy to have a view that lists all the extensions
available in the filesystem.

>> 2. When 9.4 gets released, we need some solid advice for extension
>> authors. If they have a native shared library, I assume we just tell
>> them to keep using the file-based templates. But if they have a SQL-only
>> extension, do we tell them to port to the in-catalog templates? What if
>> they port to in-catalog templates, and then decide they just want to
>> optimize one function by writing it in native code? Do they have to port
>> back? What should the authors of SQL-only extensions distribute on PGXN?
>> Should there be a migration period where they offer both kinds of
>> templates until they drop support for 9.3?
>
> This is one of the main things that I think Heikki was trying to drive
> at with his comment- we really don't *want* to make extension authors
> have to do anything different than what they do today. With an external
> tool, they wouldn't need to and it would just be two different ways for
> an extension to be installed into a given database. In the end though,
> if we're telling people to 'port' their extensions, then I think we've
> already lost.

Exactly.

There should be no difference between file-based extensions and
catalog-based extensions. It's just two different ways to install the
same extension. The extension author doesn't need to care about that,
it's the DBA that decides which method to use to install it.

I'm going to object loudly to any proposal that doesn't meet that criteria.

- Heikki

On 2013-12-02 11:07:28 +0200, Heikki Linnakangas wrote:
> >Perhaps I'm missing something- but we already *have* a catalog
> >representation for every extension that's ever installed into a given
> >database. A representation that's a heck of a lot better than a big
> >text blob.
>
> Right. I think Jeff was thinking of a catalog representation for extensions
> that haven't been installed yet, but are available in the system and could
> be installed with CREATE EXTENSION foo. I wouldn't mind having a catalog
> like that. Even without any of this extension template stuff, it would be
> handy to have a view that lists all the extensions available in the
> filesystem.

Luckily that's already there: SELECT * FROM pg_available_extensions;

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Heikki Linnakangas <hlinnakangas(at)vmware(dot)com> writes:
> Right. I think Jeff was thinking of a catalog representation for extensions
> that haven't been installed yet, but are available in the system and could
> be installed with CREATE EXTENSION foo. I wouldn't mind having a catalog
> like that. Even without any of this extension template stuff, it would be
> handy to have a view that lists all the extensions available in the
> filesystem.

http://www.postgresql.org/docs/9.1/static/view-pg-available-extensions.html
http://www.postgresql.org/docs/9.1/static/view-pg-available-extension-versions.html

> There should be no difference between file-based extensions and
> catalog-based extensions. It's just two different ways to install the same
> extension. The extension author doesn't need to care about that, it's the
> DBA that decides which method to use to install it.

Agreed.

> I'm going to object loudly to any proposal that doesn't meet that criteria.

Please be kind enough to poin me where my current patch is drifting away
from that criteria. What you're proposing here is what I think I have
been implementing.

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

Stephen Frost <sfrost(at)snowman(dot)net> writes:
>> > Having a versioning notion (and whatever other meta data we, or an
>> > extension author, feels is useful) for what are otherwise simple containers
>> > (aka the schematic we already have..) makes sense and it would be great to
>> > provide support around that, but not this duplication of
>> > object definitions.
>>
>> I don't like duplication either, we've just been failing to find any
>> alternative with pg_restore support for the last 3 years.
>
> *That doesn't make this approach the right one*. If anything, I'm
> afraid we've ended up building ourselves a rube goldberg machine because
> of this constant struggle to fit a square peg into a round hole.

This duplication you're talking about only applies to CREATE EXTENSION.

I don't know of any ways to implement ALTER EXTENSION … UPDATE …
behaviour without a separate set of scripts to apply in a certain order
depending on the current and target versions of the extension.

If you know how to enable a DBA to update a set of objects in a database
only with information already found in the database, and in a way that
this information is actually *not* an SQL script, I'm all ears.

> That's basically what we already do with schemas today and hence is
> pretty darn close to what I'm proposing. Perhaps it'd be a way to
> simply version schemas themselves- heck, with that, we could even
> provide that oft-asked-for schema delta tool in-core by being able to
> deduce the differences between schema at version X and schema at
> version Y.

Given that at any moment you have a single version of the schema
installed, I don't know how you're supposed to be able to do that?

Maybe you mean by tracking the changes at update time? Well that at
least would be a good incentive to have Command String access in event
triggers, I guess.

>> That would work beautifully, and of course you would have to do that
>> again manually at pg_restore time after CREATE DATABASE and before
>> pg_restore, or you would need to change the fact that extensions objects
>> are not part of your pg_dump scripts, or you would have to name your new
>> thing something else than an extension.
>
> We would need a way to dump and restore this, of course.

Which is available in the current patch, of course.

> Having a management system for sets of objects is a *great* idea- and
> one which we already have through schemas. What we don't have is any
> kind of versioning system built-in or other metadata about it, nor do we
> have good tooling which leverages such a versioning or similar system.

Exactly.

How can we implement ALTER <OBJECT> … UPDATE TO <VERSION> without having
access to some SQL scripts?

The current patch offers a way to manage those scripts and apply them,
with the idea that the people managing the scripts (extension authors)
and the people applying them (DBAs) are not going to be the same people,
and that it's then possible to have to apply more than a single script
for a single UPDATE command.

> I really just don't see this as being either particularly useful nor
> feasible within a reasonable amount of effort. Shared libraries are
> really the perview of the OS packaging system. If you want to build
> some tool which is external to PG but helps facilitate the building and
> installing of shared libraries, but doesn't use the OS packaging system
> (and, instead, attempts to duplicate it) then go for it, but don't
> expect to ship or install that through the PG backend.

I'll give you that implementing Event Triggers just to be able to build
what you're talking about on top of it and out of core might not be
called “a reasonable amount of effort.”

>> The problem found here is that if a non privileged user installs an
>> extension template named “pgcyrpto” then the superuser installs what he
>> believes is the extension “pgcrypto”, the malicious unprivileged user
>> now is running his own code (extension install script) as a superuser.
>
> For my part, the problem here is this notion of extension templates in
> the PG catalog and this is just one symptom of how that's really not a
> good approach.

The only reason for that being the case is that you suppose that root on
the file system is more trustworthy as an entity than postgres on the
file system or any superuser in the PostgreSQL service.

As soon as you question that, then you might come to realise the only
difference in between file-system templates and catalog templates is our
ability to deal with the problem, rather than the problem itself.

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

* Heikki Linnakangas (hlinnakangas(at)vmware(dot)com) wrote:
> Right. I think Jeff was thinking of a catalog representation for
> extensions that haven't been installed yet, but are available in the
> system and could be installed with CREATE EXTENSION foo.

I really don't particularly see value in this unless it hooks into PGXN
or similar somehow (ala how an apt repository works). I just don't see
the point if users have to install templates to then get a list of what
extensions they have available to install. The whole 'extension
template' piece of this just ends up being overhead and gets in the way.

> I wouldn't
> mind having a catalog like that. Even without any of this extension
> template stuff, it would be handy to have a view that lists all the
> extensions available in the filesystem.

As mentioned, that's available for the filesystem-based extensions.

> There should be no difference between file-based extensions and
> catalog-based extensions. It's just two different ways to install
> the same extension. The extension author doesn't need to care about
> that, it's the DBA that decides which method to use to install it.
>
> I'm going to object loudly to any proposal that doesn't meet that criteria.

Right, which is why I think this is going to *have* to exist outside of
the backend as an independent tool which can simply install an extension
through normal libpq/PG object creation method- very similar to how
extension creation already happens, except that we're being fed from a
PG connection instead of reading in an SQL file from the filesystem.

Thanks,

Stephen

* Dimitri Fontaine (dimitri(at)2ndQuadrant(dot)fr) wrote:
> > There should be no difference between file-based extensions and
> > catalog-based extensions. It's just two different ways to install the same
> > extension. The extension author doesn't need to care about that, it's the
> > DBA that decides which method to use to install it.
>
> Agreed.
>
> > I'm going to object loudly to any proposal that doesn't meet that criteria.
>
> Please be kind enough to poin me where my current patch is drifting away
> from that criteria. What you're proposing here is what I think I have
> been implementing.

Perhaps you're seeing something down the road that I'm not, but I don't
see how what you're proposing with extension templates actually moves us
closer to this goal.

What is the next step to allow an extension pulled down from pgxn to be
installed, unchanged, into a given database?

Thanks,

Stephen

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> What is the next step to allow an extension pulled down from pgxn to be
> installed, unchanged, into a given database?

An extension packaging system.

Unchanged is not a goal, and not possible even today.

PGXN is a *source based* packaging system. You can't just install what's
in PGXN on the server's file system then CREATE EXTENSION, you have this
extra step called the “build”.

Whether you're targetting a file system template or a catalog template,
PGXN is not a complete solution, you still need to build the extension.

As I already mentionned in this thread, that's even true for SQL only
extensions today, have a look at this example:

http://api.pgxn.org/src/mimeo/mimeo-1.0.1/
http://api.pgxn.org/src/mimeo/mimeo-1.0.1/Makefile

So even as of today, given file based extension templates and PGXN,
there's something missing. You can find different client tools to help
you there, such as pgxn_client and pex:

http://pgxnclient.projects.pgfoundry.org/
https://github.com/petere/pex

What I want to build is an “extension distribution” software that knows
how to prepare anything from PGXN (and other places) so that it's fully
ready for being used in the database. Then the main client would run as
a CREATE EXTENSION "ddl_command_start" Event Trigger and would fetch the
prepared extension for you and make it available, then leaving the main
command operate as intended.

Which is what I think the pex extension is doing, and that's not
coincidental, but it runs the build step on the PostgreSQL server itself
and needs to have a non-trivial set of file-system privileges to be
doing so, and even needs to get root privileges with sudo for some of
its operations.

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

* Dimitri Fontaine (dimitri(at)2ndQuadrant(dot)fr) wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > *That doesn't make this approach the right one*. If anything, I'm
> > afraid we've ended up building ourselves a rube goldberg machine because
> > of this constant struggle to fit a square peg into a round hole.
>
> This duplication you're talking about only applies to CREATE EXTENSION.
>
> I don't know of any ways to implement ALTER EXTENSION … UPDATE …
> behaviour without a separate set of scripts to apply in a certain order
> depending on the current and target versions of the extension.

We've already got it in the form of how filesystem extensions work
today..

> If you know how to enable a DBA to update a set of objects in a database
> only with information already found in the database, and in a way that
> this information is actually *not* an SQL script, I'm all ears.

Clearly we need the information from the extension package (the scripts
which are on the PG server's filesystem today, but need not be in the
future) but that doesn't mean we need to keep those text blobs in the
catalog.

> > That's basically what we already do with schemas today and hence is
> > pretty darn close to what I'm proposing. Perhaps it'd be a way to
> > simply version schemas themselves- heck, with that, we could even
> > provide that oft-asked-for schema delta tool in-core by being able to
> > deduce the differences between schema at version X and schema at
> > version Y.
>
> Given that at any moment you have a single version of the schema
> installed, I don't know how you're supposed to be able to do that?

*I am not trying to rebuild the entire extension package from the PG
catalog*. I do not see the need to do so either. Perhaps that's
short-sighted of me, but I don't think so; to go back to my dpkg
example, we don't store the source package in dpkg's database nor do
people generally feel the need to rebuild .deb's from the files which
are out on the filesystem (a non-trivial task though I suppose it might
be possible to do- but not for *every version* of the package..).

> Maybe you mean by tracking the changes at update time? Well that at
> least would be a good incentive to have Command String access in event
> triggers, I guess.

I don't see the need to track the changes at all. We don't actually
track them in the database anywhere today... We happen to have scripts
available on the filesystem which allow us to move between versions, but
they're entirely outside the catalog and that's where they belong.

> > Having a management system for sets of objects is a *great* idea- and
> > one which we already have through schemas. What we don't have is any
> > kind of versioning system built-in or other metadata about it, nor do we
> > have good tooling which leverages such a versioning or similar system.
>
> Exactly.
>
> How can we implement ALTER <OBJECT> … UPDATE TO <VERSION> without having
> access to some SQL scripts?
>
> The current patch offers a way to manage those scripts and apply them,
> with the idea that the people managing the scripts (extension authors)
> and the people applying them (DBAs) are not going to be the same people,
> and that it's then possible to have to apply more than a single script
> for a single UPDATE command.

Extension authors are not going to be issuing updates to everyone's
catalogs directly to update their templates.. That's still going to be
the DBA, or some tool the DBA runs, job. I'm argueing that such a tool
could actually do a lot more and work outside of the PG backend but
communicate through libpq. As I see it, you're trying to build that
tool *into* the backend and while 'extension templates' might end up
there, I don't think you're going to get your wish when it comes to
having a PG backend reach out over the internet at the request of a
normal user, ever.

> As soon as you question that, then you might come to realise the only
> difference in between file-system templates and catalog templates is our
> ability to deal with the problem, rather than the problem itself.

I really think there's a good deal more to my concerns than that. :)

Thanks,

Stephen

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> Clearly we need the information from the extension package (the scripts
> which are on the PG server's filesystem today, but need not be in the
> future) but that doesn't mean we need to keep those text blobs in the
> catalog.

So, I guess it would have been good to hear about that about a year ago:

http://www.postgresql.org/message-id/13481.1354743758@sss.pgh.pa.us
http://www.postgresql.org/message-id/6466.1354817682@sss.pgh.pa.us

We could have CREATE TEMPLATE FOR EXTENSION store the scripts into some
files in PGDATA instead of the catalogs, but really I don't see the
point.

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

On 12/02/2013 04:14 PM, Dimitri Fontaine wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
>> What is the next step to allow an extension pulled down from pgxn to be
>> installed, unchanged, into a given database?
>
> An extension packaging system.
>
> Unchanged is not a goal, and not possible even today.
>
> PGXN is a *source based* packaging system. You can't just install what's
> in PGXN on the server's file system then CREATE EXTENSION, you have this
> extra step called the “build”.
>
> Whether you're targetting a file system template or a catalog template,
> PGXN is not a complete solution, you still need to build the extension.

So? Just "make; make install" and you're done. Or "apt-get install foo".

> What I want to build is an “extension distribution” software that knows
> how to prepare anything from PGXN (and other places) so that it's fully
> ready for being used in the database.

You mean, something to replace "make install" if it's not installed on
the server? Fair enough. You could probably write a little perl script
to parse simple Makefiles that only copy a few static files in place. Or
add a flag to the control file indicating that the extension follows a
standard layout, and doesn't need a "make" step.

I fear we're wandering off the point again. So let me repeat: It must be
possible to install the same extension the way you do today, and using
the new mechanism.

- Heikki

* Dimitri Fontaine (dimitri(at)2ndQuadrant(dot)fr) wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > What is the next step to allow an extension pulled down from pgxn to be
> > installed, unchanged, into a given database?
>
> An extension packaging system.
>
> Unchanged is not a goal, and not possible even today.

I'm not convinced of that, actually, but you do raise a good point.

> PGXN is a *source based* packaging system. You can't just install what's
> in PGXN on the server's file system then CREATE EXTENSION, you have this
> extra step called the “build”.

Fine- so we need a step that goes from 'source' to 'built'. I don't see
that step being done in or by a PG backend process. Adding a new option
which can take a pgxn source and build a script from it which can be run
against PG via libpq is what I'd be going for- but that script *just
installs (or perhaps upgrades) the extension.* There's no need for that
script, or various upgrade/downgrade/whatever scripts, to be sucked
wholesale into the PG catalog.

> What I want to build is an “extension distribution” software that knows
> how to prepare anything from PGXN (and other places) so that it's fully
> ready for being used in the database. Then the main client would run as
> a CREATE EXTENSION "ddl_command_start" Event Trigger and would fetch the
> prepared extension for you and make it available, then leaving the main
> command operate as intended.

I really don't think that's a good approach.

> Which is what I think the pex extension is doing, and that's not
> coincidental, but it runs the build step on the PostgreSQL server itself
> and needs to have a non-trivial set of file-system privileges to be
> doing so, and even needs to get root privileges with sudo for some of
> its operations.

pex is an interesting beginning to this, but we'd need *some* backend
support for being able to install the extension via libpq (or pex would
need to be modified to not actually use our extension framework at
all for 'trusted' extensions...).

Thanks,

Stephen

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> Fine- so we need a step that goes from 'source' to 'built'. I don't see
> that step being done in or by a PG backend process. Adding a new option
> which can take a pgxn source and build a script from it which can be run
> against PG via libpq is what I'd be going for- but that script *just
> installs (or perhaps upgrades) the extension.* There's no need for that
> script, or various upgrade/downgrade/whatever scripts, to be sucked
> wholesale into the PG catalog.

As you said previously, we can't ask extension authors to control what
version of their extension is installed on which database, so we need a
way to cooperate with the backend in order to know how to operate the
update.

We can't just pull data out of the backend to do that, not until we've
been pushing the list of available versions and update scripts that we
have to be able to run the update.

That's were I though about pushing the whole thing down to the catalogs
and have the backend take control from there.

>> What I want to build is an “extension distribution” software that knows
>> how to prepare anything from PGXN (and other places) so that it's fully
>> ready for being used in the database. Then the main client would run as
>> a CREATE EXTENSION "ddl_command_start" Event Trigger and would fetch the
>> prepared extension for you and make it available, then leaving the main
>> command operate as intended.
>
> I really don't think that's a good approach.

What's your alternative? Goals are:

- using the update abilities of the extension mechanism
- no access to the server's file system needed
- pg_restore does the right thing

I went for the whole set of extension abilities in my patch, you're
pushing hard for me to reduce that goal so I only included the ability
to manage version upgrades here.

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

Heikki Linnakangas <hlinnakangas(at)vmware(dot)com> writes:
> I fear we're wandering off the point again. So let me repeat: It must be
> possible to install the same extension the way you do today, and using the
> new mechanism.

The way you do today is running make install or apt-get install or
something else to write files in the right place on the file system,
usually with root privileges.

The new mechanism tries to avoid using the file system *completely*.

Sorry. I don't understand what you mean other that “I don't want this
patch because I don't understand what it is about”.

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

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> * Dimitri Fontaine (dimitri(at)2ndQuadrant(dot)fr) wrote:
>> Then as soon as we are able to CREATE EXTENSION mystuff; without ever
>> pre-installing files on the file system as root, then we would like to
>> be able to do just that even with binary modules.

> I really just don't see this as being either particularly useful nor
> feasible within a reasonable amount of effort. Shared libraries are
> really the perview of the OS packaging system.

Yes, exactly. What's more, you're going to face huge push-back from
vendors who are concerned about security (which is most of them).
If there were such a feature, it would end up disabled, one way or
another, in a large fraction of installations. That would make it
impractical to use anyway for most extension authors. I don't think
it's good project policy to fragment the user base that way.

I'm on board with the notion of an all-in-the-database extension
mechanism for extensions that consist solely of SQL objects. But
not for ones that need a .so somewhere.

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
>> * Dimitri Fontaine (dimitri(at)2ndQuadrant(dot)fr) wrote:
>>> Then as soon as we are able to CREATE EXTENSION mystuff; without ever
>>> pre-installing files on the file system as root, then we would like to
>>> be able to do just that even with binary modules.
>
>> I really just don't see this as being either particularly useful nor
>> feasible within a reasonable amount of effort. Shared libraries are
>> really the perview of the OS packaging system.
>
> Yes, exactly. What's more, you're going to face huge push-back from
> vendors who are concerned about security (which is most of them).

Last time I talked with vendors, they were working in the Open Shift
team at Red Hat, and they actually asked me to offer them the ability
you're refusing, to let them enable a better security model.

The way they use cgroups and SELinux means that they want to be able to
load shared binaries from system user places.

> If there were such a feature, it would end up disabled, one way or
> another, in a large fraction of installations. That would make it
> impractical to use anyway for most extension authors. I don't think
> it's good project policy to fragment the user base that way.

That point about fragmentation is a concern I share.

> I'm on board with the notion of an all-in-the-database extension
> mechanism for extensions that consist solely of SQL objects. But
> not for ones that need a .so somewhere.

Thanks for restating your position.

The current patch offers a feature that only works with SQL objects,
it's currently completely useless as soon as there's a .so involved.

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

* Dimitri Fontaine (dimitri(at)2ndQuadrant(dot)fr) wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > Clearly we need the information from the extension package (the scripts
> > which are on the PG server's filesystem today, but need not be in the
> > future) but that doesn't mean we need to keep those text blobs in the
> > catalog.
>
> So, I guess it would have been good to hear about that about a year ago:
>
> http://www.postgresql.org/message-id/13481.1354743758@sss.pgh.pa.us
> http://www.postgresql.org/message-id/6466.1354817682@sss.pgh.pa.us
>
> We could have CREATE TEMPLATE FOR EXTENSION store the scripts into some
> files in PGDATA instead of the catalogs, but really I don't see the
> point.

Yeah, I don't particularly like that idea either, but especially if it's
going to be per-database again. I can kinda, sorta see the point if
this was done cluster-wide but you don't like that idea and I'm not a
big fan of pushing these files out onto the filesystem anyway.

What I don't entirely follow is the argument against having
non-file-backed extensions be dump'd through pg_dump/restore. Even in
that thread, Tom appears to agree that they'd have to be dumped out in
some fashion, even if they're stored as files under PGDATA, because
otherwise you're not going to be able to restore the DB..

On the other hand, I can appreciate the concern that we don't really
want a dump/restore to include the extension definition when it's
already on the filesystem. That said, it amazes me that we don't
include the version # of the extension in pg_dump's 'CREATE EXTENSION'
command.. How is that not a problem?

Thanks,

Stephen

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> On the other hand, I can appreciate the concern that we don't really
> want a dump/restore to include the extension definition when it's
> already on the filesystem. That said, it amazes me that we don't
> include the version # of the extension in pg_dump's 'CREATE EXTENSION'
> command.. How is that not a problem?

Including the version number would be a problem.

When you install PostgreSQL 9.1, you only have hstore 1.0.
When you install PostgreSQL 9.2, you only have hstore 1.1.
When you install PostgreSQL 9.3, you only have hstore 1.2.

http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/hstore/hstore.control;hb=refs/heads/REL9_1_STABLE
http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/hstore/hstore.control;hb=refs/heads/REL9_2_STABLE
http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=contrib/hstore/hstore.control;hb=refs/heads/REL9_3_STABLE

We should maybe add the extension's version number in our documentation
pages, such as the following:

http://www.postgresql.org/docs/9.3/interactive/hstore.html

So when you pg_dump | pg_restore from 9.1 into 9.3, if pg_dump were to
be nitpicky about the version of hstore with the command

CREATE EXTENSION hstore VERSION '1.0';

What would happen is that pg_restore would fail.

That's just the way we maintain contribs.

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

* Dimitri Fontaine (dimitri(at)2ndQuadrant(dot)fr) wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > Fine- so we need a step that goes from 'source' to 'built'. I don't see
> > that step being done in or by a PG backend process. Adding a new option
> > which can take a pgxn source and build a script from it which can be run
> > against PG via libpq is what I'd be going for- but that script *just
> > installs (or perhaps upgrades) the extension.* There's no need for that
> > script, or various upgrade/downgrade/whatever scripts, to be sucked
> > wholesale into the PG catalog.
>
> As you said previously, we can't ask extension authors to control what
> version of their extension is installed on which database, so we need a
> way to cooperate with the backend in order to know how to operate the
> update.

Sure, that sounds reasonable..

> We can't just pull data out of the backend to do that, not until we've
> been pushing the list of available versions and update scripts that we
> have to be able to run the update.

I'm not following this, nor why we need this master list of every
extension which exists in the world to be in every PG catalog in every
database out there.

> That's were I though about pushing the whole thing down to the catalogs
> and have the backend take control from there.

I can appreciate the desire to do that but this particular piece really
feels like it could be done better external to the backend. To go back
to my OS example, I feel Debian is better off with apt-get/aptitude
being independent from dpkg itself.

> What's your alternative? Goals are:
>
> - using the update abilities of the extension mechanism
> - no access to the server's file system needed
> - pg_restore does the right thing
>
> I went for the whole set of extension abilities in my patch, you're
> pushing hard for me to reduce that goal so I only included the ability
> to manage version upgrades here.

I'd like to see these goals met, I just don't see it being all done in C
in the PG backend. I've tried to outline my thoughts about how we
should keep the actual extension creation scripts, upgrade scripts, etc,
out of the backend catalogs (and not on the filesystem either..) and let
those be managed externally, but that does then require that when we
actually dump the extension's objects instead of just
'CREATE EXTENSION blah;'. I understand there have been objections
raised to that, but I wonder if that isn't mainly because we're calling
these new things "extensions" which have this built-in notion that
they're coming from an OS packaging system which installs files
somewhere.. We certainly have none of these qualms about dumping and
restoring all the objects in a given schema.

Thanks,

Stephen

* Dimitri Fontaine (dimitri(at)2ndQuadrant(dot)fr) wrote:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> > Yes, exactly. What's more, you're going to face huge push-back from
> > vendors who are concerned about security (which is most of them).
>
> Last time I talked with vendors, they were working in the Open Shift
> team at Red Hat, and they actually asked me to offer them the ability
> you're refusing, to let them enable a better security model.
>
> The way they use cgroups and SELinux means that they want to be able to
> load shared binaries from system user places.

As I've pointed out before, I'd really like to hear exactly how these
individuals are using SELinux and why they feel this is an acceptable
approach. The only use-case that this model fits is where you don't
have *any* access control in the database itself and everyone might as
well be a superuser. Then, sure, SELinux can prevent your personal PG
environment from destroying the others on the system in much the same
way that a chroot can help there, but most folks who are looking at MAC
would view *any* database as an independent object system which needs to
*hook into* an SELinux or similar.

In other words, I really don't think we should be encouraging this
approach and certainly not without more understanding of what they're
doing here. Perhaps they have a use-case for it, but it might be better
done through 'adminpack' or something similar than what we support in
core.

Thanks,

Stephen

* Dimitri Fontaine (dimitri(at)2ndQuadrant(dot)fr) wrote:
> So when you pg_dump | pg_restore from 9.1 into 9.3, if pg_dump were to
> be nitpicky about the version of hstore with the command
>
> CREATE EXTENSION hstore VERSION '1.0';
>
> What would happen is that pg_restore would fail.
>
> That's just the way we maintain contribs.

I'd much rather get an error that says "that version of the extension is
unavailable" than a runtime error when my plpgsql code tries to use a
function whose definition changed between 1.0 and 1.1..

Perhaps we're not ready to go there because of how contrib is built and
shipped, and I can understand that, but that doesn't make it a good
solution. I'm not sure that such an issue should preclude us from
including in-catalog-only extensions from being dump'd out as a set of
objects (ala a schema) and then restored that way (preserving the
version of the extension it was installed at..).

I don't like the idea of having a pg_dump/restore mechanism that
intentionally tries to go out and install the latest version of whatever
extension was installed in the old DB by downloading it from PGXN,
building it, and then installing it... Is that what people are
expecting here?

Thanks,

Stephen

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> I don't like the idea of having a pg_dump/restore mechanism that
> intentionally tries to go out and install the latest version of whatever
> extension was installed in the old DB by downloading it from PGXN,
> building it, and then installing it... Is that what people are
> expecting here?

The whole idea of having Extension Templates in catalogs is exactly to
prevent what you're describing here from happening.

Whatever the templates you downloaded to get to the version you now have
in your database for extension “foo” are going to used again by
pg_restore at CREATE EXTENSION time. The extension depending on its
in-catalog templates ensures that model of operations.

You can copy/paste some extension examples from the regression tests and
pg_dump -Fc | pg_restore -l to see the details, or something.

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

On Dec 2, 2013, at 6:14 AM, Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> wrote:

> Whether you're targetting a file system template or a catalog template,
> PGXN is not a complete solution, you still need to build the extension.

This is true today, but only because PostgreSQL provides the infrastructure for building and installing extensions that entails `make && make install`. If Postgres provided some other method of building and installing extensions, you could start using it right away on PGXN. The *only* requirement for PGXN distributions, really, is a META.json file describing the extension.

Best,

David

On Mon, Dec 2, 2013 at 10:13 AM, Dimitri Fontaine
<dimitri(at)2ndquadrant(dot)fr> wrote:
> Heikki Linnakangas <hlinnakangas(at)vmware(dot)com> writes:
>> I fear we're wandering off the point again. So let me repeat: It must be
>> possible to install the same extension the way you do today, and using the
>> new mechanism.
>
> The way you do today is running make install or apt-get install or
> something else to write files in the right place on the file system,
> usually with root privileges.
>
> The new mechanism tries to avoid using the file system *completely*.
>
> Sorry. I don't understand what you mean other that “I don't want this
> patch because I don't understand what it is about”.

OK, I'll bite. I've been trying to stay out of this thread, but I
really *don't* understand what this patch is about. Extensions, as
they exist today, are installed from the filesystem and their contents
are not dumped. You're trying to create a new kind of extension which
is installed from the system catalogs (instead of the file system) and
is dumped. Why should anyone want that?

It seems that part of the answer is that people would like to be able
to install extensions via libpq. You could almost write a client-side
tool for that today just by using adminpack to write the files to the
server, but you'd trip over the fact that files written by adminpack
must be in either the data directory or the log directory. But we
could fix that easily enough. Here's a design sketch: (1) Add a new
GUC that specifies an alternate location from which extensions can be
installed. (2) Allow adminpack to write to that location just as it
writes to the existing locations. (3) Write a tool that copies files
from wherever to the appropriate server directory using adminpack.
Problem solved! The only downside is that the use of this facility
would have to be restricted to superusers, but the current consensus
on this thread is that we should restrict *this* facility to
superusers also, so we're not really losing anything. And, for a
further plus, it'd even work for extensions that contain shared
libraries. Win.

Now, if we could make this mechanism work for non-superusers, then I
think it gets more interesting, because now you have a more
significant gain in functionality: someone can potentially download an
extension from PGXN and install it just for themselves without needing
superuser access, provided the extension doesn't require a .so or any
permissions that they don't have. That's kind of nice, but as Stephen
said elsewhere on the thread, this seems like a lot of mechanism for
that narrow goal. As you (I think) said somewhere on this thread, you
could just create the extension with a bunch of CREATE and ALTER
EXTENSION .. ADD statements and set a flag on it that causes it to be
dumped the same way. (We might need to add a CREATE EXTENSION foo
WITH NO CONTENTS statement to really make it work, so that the libpq
connection can create it as completely empty and then add objects to
it one at a time, but we shouldn't need too much more than that.) The
whole idea of the extension template as such goes away.

So I'm having a hard time understanding what this patch actually gains
us that can't be done more simply by some other means.

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

On Sun, 2013-12-01 at 22:34 -0500, Stephen Frost wrote:
> Perhaps I'm missing something- but we already *have* a catalog
> representation for every extension that's ever installed into a given
> database. A representation that's a heck of a lot better than a big
> text blob.

I meant "extension template" represented in the catalog.

> > But bringing more of an extension into the catalog can be done, and I
> > think we'll see big benefits from that.
>
> I'm not following here- what's 'missing'?

It seems that you are making the assumption that installing an extension
template or creating an extension are major operations, and anyone
encountering an error is a superuser with admin access to the server and
can easily correct it.

If the admin messes up and the extension template isn't there (e.g.
after a failover), the person to encounter the error at CREATE EXTENSION
time might not have admin access or there might be a process required to
deploy the new files. But if the extension templates were carried along
with replication and backup naturally, then they'd be there.

And it would be nice if there was some hope for non-superusers to create
extension templates, but that will never happen as long as they are
files.

> > That being said, there some things about in-catalog templates that need
> > some more thought:
> >
> > 1. If someone does want their OS to install extensions for them (e.g.
> > the contrib package), how should that be done? This usually works fine
> > with the aforementioned languages, because installation is still just
> > dropping files in the right place. Postgres is different, because to put
> > something in the catalog, we need a running server, which is awkward for
> > a packaging system to do.
>
> You need a running PG for the *extension* to be installed, but with the
> filesystem-based extension approach we have today, the "template" (which
> are the files on the filesystem) don't need PG running

I think you misread -- this is a list of issues if we move templates
into the catalog. File-based templates obviously don't have this
problem.

> > 3. What do we do about native shared libraries? Ultimately, I imagine
> > that we should handle these similarly to tablespaces: have a real
> > database object with an OID that extensions or functions can depend on,
> > and create a symlink (with the OID as the link name) that points to the
> > real file on disk. We could also export some new symbols like the shared
> > library name and version for better error checking.
>
> I'm sorry, but I do not see shared libraries working through this
> system, at all. I know that goes against what Dimitri and some others
> want, but I've talked with a few folks (such as Paul Ramsey of PostGIS)
> about this notion and, from that perspective, it's almost laughable to
> think we could ship shared libraries in this way. Even if we could
> convince ourselves that there's some way for us to track the files on
> the filesystem and work out all the per-database and whatever issues are
> associated with that, it'd only work for the simplest shared libraries
> which don't have any dependencies on other libraries on the system
> (excepting, perhaps, libc6) and that narrows the use-case down
> significantly, to the point where I don't feel it's worth all that
> effort.

I was just suggesting that a little more information in the catalog
could improve dependency tracking and error handling. I'm not suggesting
we "ship" any shared libraries anywhere -- that's still up to extension
authors and PGXN. I'm also not suggesting that the error handling will
be perfect or catch subtle mismatches.

Regards,
Jeff Davis

On Mon, Dec 2, 2013 at 6:30 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> OK, I'll bite. I've been trying to stay out of this thread, but I
> really *don't* understand what this patch is about. Extensions, as
> they exist today, are installed from the filesystem and their contents
> are not dumped. You're trying to create a new kind of extension which
> is installed from the system catalogs (instead of the file system) and
> is dumped. Why should anyone want that?
>
> It seems that part of the answer is that people would like to be able
> to install extensions via libpq. You could almost write a client-side
> tool for that today just by using adminpack to write the files to the
> server, but you'd trip over the fact that files written by adminpack
> must be in either the data directory or the log directory. But we
> could fix that easily enough.

Just tossing an idea out there. What if you could install an extension
by specifying not a local file name but a URL. Obviously there's a
security issue but for example we could allow only https URLs with
verified domain names that are in a list of approved domain names
specified by a GUC.

--
greg

On Mon, Dec 2, 2013 at 2:33 PM, Greg Stark <stark(at)mit(dot)edu> wrote:
> On Mon, Dec 2, 2013 at 6:30 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> OK, I'll bite. I've been trying to stay out of this thread, but I
>> really *don't* understand what this patch is about. Extensions, as
>> they exist today, are installed from the filesystem and their contents
>> are not dumped. You're trying to create a new kind of extension which
>> is installed from the system catalogs (instead of the file system) and
>> is dumped. Why should anyone want that?
>>
>> It seems that part of the answer is that people would like to be able
>> to install extensions via libpq. You could almost write a client-side
>> tool for that today just by using adminpack to write the files to the
>> server, but you'd trip over the fact that files written by adminpack
>> must be in either the data directory or the log directory. But we
>> could fix that easily enough.
>
> Just tossing an idea out there. What if you could install an extension
> by specifying not a local file name but a URL. Obviously there's a
> security issue but for example we could allow only https URLs with
> verified domain names that are in a list of approved domain names
> specified by a GUC.

That's a different feature, but I don't see anything preventing
someone from implementing that as an extension, today, without any
core support at all. It would only be usable in cases where the share
directory is writable by the database server (i.e. low-security
installations) and you'd have to make it a function call rather than
piggybacking on CREATE EXTENSION, but neither of those things sound
bad to me. (And if they are bad, they could be addressed by providing
hooks or event triggers, leaving the rest of the functionality in the
extension module.)

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

Greg Stark <stark(at)mit(dot)edu> writes:
> On Mon, Dec 2, 2013 at 6:30 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> OK, I'll bite. I've been trying to stay out of this thread, but I
>> really *don't* understand what this patch is about. Extensions, as

Thanks!

>> they exist today, are installed from the filesystem and their contents
>> are not dumped. You're trying to create a new kind of extension which
>> is installed from the system catalogs (instead of the file system) and
>> is dumped. Why should anyone want that?

To benefit from ALTER EXTENSION … UPDATE … and \dx.

And technically the extension is not dumped, its templates are.

>> It seems that part of the answer is that people would like to be able
>> to install extensions via libpq. You could almost write a client-side
>> tool for that today just by using adminpack to write the files to the
>> server, but you'd trip over the fact that files written by adminpack
>> must be in either the data directory or the log directory. But we
>> could fix that easily enough.

Trick question: when you've implemented said client and used it for a
couple of (in-house) extensions, what do you think should happen at
pg_restore time?

Hint: in a properly designed ops model, pg_restore happens each and
every day when the unattended cron job “rebases” the QA or testing
environments from the production PITR backups, of course.

> Just tossing an idea out there. What if you could install an extension
> by specifying not a local file name but a URL. Obviously there's a
> security issue but for example we could allow only https URLs with
> verified domain names that are in a list of approved domain names
> specified by a GUC.

That's something I want to build. This time, not in core.

The model I've been thinking about involves an EVENT TRIGGER that is
fired at "ddl_command_start" for CREATE EXTENSION and prepares an
EXTENSION TEMPLATE before the command has a chance to check what's
available and install the current default version of it.

Also runs at ALTER EXTENSION … UPDATE …, of course, providing the
upgrade scripts on the fly.

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

* Dimitri Fontaine (dimitri(at)2ndQuadrant(dot)fr) wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > I don't like the idea of having a pg_dump/restore mechanism that
> > intentionally tries to go out and install the latest version of whatever
> > extension was installed in the old DB by downloading it from PGXN,
> > building it, and then installing it... Is that what people are
> > expecting here?
>
> The whole idea of having Extension Templates in catalogs is exactly to
> prevent what you're describing here from happening.

eh...? I'm not sure how that follows.

> Whatever the templates you downloaded to get to the version you now have
> in your database for extension “foo” are going to used again by
> pg_restore at CREATE EXTENSION time. The extension depending on its
> in-catalog templates ensures that model of operations.

What I've been trying to point out is that there's absolutely zero need
for the 'extension template' part of this to make a pg_restore work for
an entirely-in-the-catalog extension. I realize that's how you've done
it with this patch set but that doesn't make it necessary.

Thanks,

Stephen

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> * David E. Wheeler (david(at)justatheory(dot)com) wrote:
>> This is true today, but only because PostgreSQL provides the
>> infrastructure for building and installing extensions that entails `make
>> && make install`. If Postgres provided some other method of building and
>> installing extensions, you could start using it right away on PGXN. The
>> *only* requirement for PGXN distributions, really, is a META.json file
>> describing the extension.
>
> Thanks, that's a pretty interesting point.. I like the idea that we
> could provide a new make target which could build an 'inline extension'
> (or what-have-you) which could then be distributed and used by users
> either directly or with some client-side tool.

+1

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

David,

* David E. Wheeler (david(at)justatheory(dot)com) wrote:
> On Dec 2, 2013, at 6:14 AM, Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> wrote:
>
> > Whether you're targetting a file system template or a catalog template,
> > PGXN is not a complete solution, you still need to build the extension.
>
> This is true today, but only because PostgreSQL provides the infrastructure for building and installing extensions that entails `make && make install`. If Postgres provided some other method of building and installing extensions, you could start using it right away on PGXN. The *only* requirement for PGXN distributions, really, is a META.json file describing the extension.

Thanks, that's a pretty interesting point.. I like the idea that we
could provide a new make target which could build an 'inline extension'
(or what-have-you) which could then be distributed and used by users
either directly or with some client-side tool.

Thanks,

Stephen

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> What I've been trying to point out is that there's absolutely zero need
> for the 'extension template' part of this to make a pg_restore work for
> an entirely-in-the-catalog extension. I realize that's how you've done
> it with this patch set but that doesn't make it necessary.

If it's an extension, it's filtered out of pg_dump, so it's not part of
your pg_restore. Full Stop. This point has been debated and there has
been a very clear conclusion a year ago.

What am I missing here?
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

Robert,

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> Problem solved! The only downside is that the use of this facility
> would have to be restricted to superusers, but the current consensus
> on this thread is that we should restrict *this* facility to
> superusers also, so we're not really losing anything. And, for a
> further plus, it'd even work for extensions that contain shared
> libraries. Win.

This is more-or-less what I've been argueing for when it comes to
addressing this for people who are superusers and want to install
.so's via libpq- do it outside of PG core, thank you very much.

> Now, if we could make this mechanism work for non-superusers, then I
> think it gets more interesting, because now you have a more
> significant gain in functionality: someone can potentially download an
> extension from PGXN and install it just for themselves without needing
> superuser access, provided the extension doesn't require a .so or any
> permissions that they don't have.

This is exactly what I want to see happen, with perhaps some external
tool which handles the "download from PGXN and install it" part- or
maybe we add that to psql, but I feel like a dedicated tool would be
better.

> That's kind of nice, but as Stephen
> said elsewhere on the thread, this seems like a lot of mechanism for
> that narrow goal. As you (I think) said somewhere on this thread, you
> could just create the extension with a bunch of CREATE and ALTER
> EXTENSION .. ADD statements and set a flag on it that causes it to be
> dumped the same way. (We might need to add a CREATE EXTENSION foo
> WITH NO CONTENTS statement to really make it work, so that the libpq
> connection can create it as completely empty and then add objects to
> it one at a time, but we shouldn't need too much more than that.) The
> whole idea of the extension template as such goes away.

Exactly.

Thanks,

Stephen

On Mon, Dec 2, 2013 at 3:02 PM, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr> wrote:
> Greg Stark <stark(at)mit(dot)edu> writes:
>> On Mon, Dec 2, 2013 at 6:30 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>> OK, I'll bite. I've been trying to stay out of this thread, but I
>>> really *don't* understand what this patch is about. Extensions, as
>>> they exist today, are installed from the filesystem and their contents
>>> are not dumped. You're trying to create a new kind of extension which
>>> is installed from the system catalogs (instead of the file system) and
>>> is dumped. Why should anyone want that?
>
> To benefit from ALTER EXTENSION … UPDATE … and \dx.

I don't see what benefit we get as far as ALTER EXTENSION .. UPDATE is
concerned. You've got to do some kind of DDL to load the update
script into the database, and then you've got to run the UPDATE
itself. That's pretty complicated. You could have done the same
thing by just ALTERing the extension, without actually needing the
update script uploaded into the database.

> And technically the extension is not dumped, its templates are.

But you *could* dump the extension, and in fact it would be simpler
than what you're actually doing.

>>> It seems that part of the answer is that people would like to be able
>>> to install extensions via libpq. You could almost write a client-side
>>> tool for that today just by using adminpack to write the files to the
>>> server, but you'd trip over the fact that files written by adminpack
>>> must be in either the data directory or the log directory. But we
>>> could fix that easily enough.
>
> Trick question: when you've implemented said client and used it for a
> couple of (in-house) extensions, what do you think should happen at
> pg_restore time?
>
> Hint: in a properly designed ops model, pg_restore happens each and
> every day when the unattended cron job “rebases” the QA or testing
> environments from the production PITR backups, of course.

/me shrugs. You could just as well ask what happens when you've
installed postgresql but not postgresql-contrib and try to restore a
database that needs hstore. There's no getting around the fact that
if the extension isn't available, installation will fail. Nor is that
a bug. Still, I agree that there are use cases where that's not what
you want, and discussed some of them in the rest of the email from
which you took that quote.

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

* Jeff Davis (pgsql(at)j-davis(dot)com) wrote:
> On Sun, 2013-12-01 at 22:34 -0500, Stephen Frost wrote:
> > Perhaps I'm missing something- but we already *have* a catalog
> > representation for every extension that's ever installed into a given
> > database. A representation that's a heck of a lot better than a big
> > text blob.
>
> I meant "extension template" represented in the catalog.

Ok.

> > > But bringing more of an extension into the catalog can be done, and I
> > > think we'll see big benefits from that.
> >
> > I'm not following here- what's 'missing'?

The above was in reference to an extension, not an extension template.

> It seems that you are making the assumption that installing an extension
> template or creating an extension are major operations, and anyone
> encountering an error is a superuser with admin access to the server and
> can easily correct it.

I'm not following this train of thought at all.. Unless you're
referring to the case where it involves a .so, in which case then I
*would* expect a superuser with admin access to the server to be
involved. For the non-.so-including case, I'm hopeful we can provide
a mechanism which doesn't require a superuser or admin at all.

> If the admin messes up and the extension template isn't there (e.g.
> after a failover), the person to encounter the error at CREATE EXTENSION
> time might not have admin access or there might be a process required to
> deploy the new files. But if the extension templates were carried along
> with replication and backup naturally, then they'd be there.

The *extension* is already brought along with replication and binary
backup naturally, for non-.so-based extensions. For logical backups, we
depend on the filesystem to provide the extension definition- I don't
particularly like that and it *isn't necessary* for non-.so-based
extensions. In many ways, we do it the way we do specifically due to
how we distribute -contrib, as Dimitri has pointed out. What I'd like
to see is support for dumping the extension's objects during a logical
dump, perhaps only for specific types of extensions (ones installed that
way), and maybe with some additional caveats on it (should we have
multiple extension namespaces? or come up with some other name for
these objects?).

For .so-based extensions, yes, an admin is going to need to be involved
and I am not impressed by these notions that such a requirement is
unreasonable or overly draconian.

> And it would be nice if there was some hope for non-superusers to create
> extension templates, but that will never happen as long as they are
> files.

Agreed and as a follow-on to that, I'm pretty sure .so's will always be
files...

> > You need a running PG for the *extension* to be installed, but with the
> > filesystem-based extension approach we have today, the "template" (which
> > are the files on the filesystem) don't need PG running
>
> I think you misread -- this is a list of issues if we move templates
> into the catalog. File-based templates obviously don't have this
> problem.

I don't see us being able to completely get rid of the existing
extension system which uses the file-based approach. Certainly not for
a very long time, so I'm not sure that there's much discussion to be
had along these lines.

> I was just suggesting that a little more information in the catalog
> could improve dependency tracking and error handling. I'm not suggesting
> we "ship" any shared libraries anywhere -- that's still up to extension
> authors and PGXN. I'm also not suggesting that the error handling will
> be perfect or catch subtle mismatches.

Having more information about an extension in the catalog seems
reasonable to me- along the same lines of how we have dependency
information in dpkg for .deb's. What I don't like is the notion of
upgrade scripts which are installed into the catalog as *scripts*.
Ditto for initial extension creation. We already know how to drop and
recreate every object in the database, let's make use of that mechanism
instead. That doesn't allow pg_dump/restore to *invisibly upgrade an
extension* (which is what happens now..), but I find that to be an
entirely good thing...

Thanks,

Stephen

* Dimitri Fontaine (dimitri(at)2ndQuadrant(dot)fr) wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > What I've been trying to point out is that there's absolutely zero need
> > for the 'extension template' part of this to make a pg_restore work for
> > an entirely-in-the-catalog extension. I realize that's how you've done
> > it with this patch set but that doesn't make it necessary.
>
> If it's an extension, it's filtered out of pg_dump, so it's not part of
> your pg_restore. Full Stop. This point has been debated and there has
> been a very clear conclusion a year ago.
>
> What am I missing here?

I don't buy it. I understand that you heard that from Tom with regard
to extensions- but that was also when he was argueing that what you're
building here shouldn't be called extensions. I have a huge problem
with the idea that we can't dump out the definition of an
in-catalog-only extension, but it's okay to dump out some big blob of
text which is supposed to represent the same thing but in a much, much
more kludgey and inexact way. How are we going to handle new keywords
being added in new major versions? A pg_dump of the extension template
script is then going to be loaded into the new major version but will
not actually be able to be run because it'll error out...

I'm 100% confident that's not the only problem with this approach which
sticks script text blobs into the catalog as representations of
database objects.

Thanks,

Stephen

On Mon, Dec 2, 2013 at 3:17 PM, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr> wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
>> What I've been trying to point out is that there's absolutely zero need
>> for the 'extension template' part of this to make a pg_restore work for
>> an entirely-in-the-catalog extension. I realize that's how you've done
>> it with this patch set but that doesn't make it necessary.
>
> If it's an extension, it's filtered out of pg_dump, so it's not part of
> your pg_restore. Full Stop. This point has been debated and there has
> been a very clear conclusion a year ago.
>
> What am I missing here?

This is a fair point. For those that may not remember the previous
proposal, see here:

http://www.postgresql.org/message-id/m2390abpc1.fsf@2ndQuadrant.fr

See also ensuing discussion.

Speaking only for myself, I think the thing I most disliked about that
proposal was the syntax. I'd rather see each extension member dumped
separately, and then later dump the extension itself as CREATE
EXTENSION ... WITH NO CONTENTS or similar followed by ALTER EXTENSION
... ADD <item> for each member. That would provide a way of handling
dependency loops, which Dimitri's proposed syntax did not, and just in
general seems more elegant. But it's not perfect: for example,
there's no clean way to handle the situation where the extension is
present in the filesystem on the old database but not the new, or
visca versa, and I don't think anyone's proposed *any* really clean
way of handling that yet.

Fundamentally, I think this is a pretty hard problem. The OS-level
equivalent of extensions is something like RPMs or .deb files, and I
can't help but observe that those are only used for system-wide
installations, not per-user installs. I think the reason we're having
a hard time coming up with a satisfactory way of making this work is
that an extension as installed from SQL using libpq is a pretty
different beast from an extension as installed via the filesystem, and
bending the existing mechanism to make that work is somewhat painful
no matter how you do it. The argument was made then, and with some
validity, that we just shouldn't make the same mechanism serve both
purposes. What I now understand (that I think I probably didn't fully
understand back then) is that part of the point here is to enable
installation of extensions without requiring local filesystem access;
using a completely different mechanism would defeat that goal. But
I'm still not altogether happy with where that's landed us.

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

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> Speaking only for myself, I think the thing I most disliked about that
> proposal was the syntax. I'd rather see each extension member dumped
> separately, and then later dump the extension itself as CREATE
> EXTENSION ... WITH NO CONTENTS or similar followed by ALTER EXTENSION
> ... ADD <item> for each member. That would provide a way of handling
> dependency loops, which Dimitri's proposed syntax did not, and just in
> general seems more elegant. But it's not perfect: for example,
> there's no clean way to handle the situation where the extension is
> present in the filesystem on the old database but not the new, or
> visca versa, and I don't think anyone's proposed *any* really clean
> way of handling that yet.

I'm not convinced we really need to solve that problem, but one way to
solve it 'cleanly' would be to seperate the two types of extensions,
perhaps by calling them by different names or inventing a namespace for
extensions.

> Fundamentally, I think this is a pretty hard problem. The OS-level
> equivalent of extensions is something like RPMs or .deb files, and I
> can't help but observe that those are only used for system-wide
> installations, not per-user installs. I think the reason we're having
> a hard time coming up with a satisfactory way of making this work is
> that an extension as installed from SQL using libpq is a pretty
> different beast from an extension as installed via the filesystem, and
> bending the existing mechanism to make that work is somewhat painful
> no matter how you do it. The argument was made then, and with some
> validity, that we just shouldn't make the same mechanism serve both
> purposes. What I now understand (that I think I probably didn't fully
> understand back then) is that part of the point here is to enable
> installation of extensions without requiring local filesystem access;
> using a completely different mechanism would defeat that goal. But
> I'm still not altogether happy with where that's landed us.

I think we're falling into the trap of thinking that whatever this
user-installable-collection-of-objects thing is, it must be considered
PG 'extensions'. While I agree that some of the things we do for
extensions should also be done with these collections of objects (eg:
having versions and other meta-data for them), I'm starting to think
that's the "small" side of this whole equation and duplicating that
meta-data store for these collections would be easier than trying to
shoehorn them into the existing notion of 'extensions'.

Thanks,

Stephen

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> Speaking only for myself, I think the thing I most disliked about that
> proposal was the syntax. I'd rather see each extension member dumped
> separately, and then later dump the extension itself as CREATE
> EXTENSION ... WITH NO CONTENTS or similar followed by ALTER EXTENSION
> ... ADD <item> for each member. That would provide a way of handling
> dependency loops, which Dimitri's proposed syntax did not, and just in
> general seems more elegant. But it's not perfect: for example,

I could have fixed the syntax quite easily, within 9.3 time frame.

> there's no clean way to handle the situation where the extension is
> present in the filesystem on the old database but not the new, or
> visca versa, and I don't think anyone's proposed *any* really clean
> way of handling that yet.

Well, my memories is that I did propose thinking about upgrade paths
mixing template sources, and Tom objected quite strongly to doing that.

IIRC the line of thoughs is that it's indeed a very complex problem to
solve, and renaming the extension when you switch your distribution
model might be all you need. Same with incompatible major versions, when
there's no integrated upgrade path possible.

> Fundamentally, I think this is a pretty hard problem. The OS-level
> equivalent of extensions is something like RPMs or .deb files, and I
> can't help but observe that those are only used for system-wide
> installations, not per-user installs. I think the reason we're having

If you want to dive into system level unprivileged package management,
have a look at that:

http://www.gnu.org/software/guix/

> a hard time coming up with a satisfactory way of making this work is
> that an extension as installed from SQL using libpq is a pretty
> different beast from an extension as installed via the filesystem, and
> bending the existing mechanism to make that work is somewhat painful
> no matter how you do it. The argument was made then, and with some
> validity, that we just shouldn't make the same mechanism serve both
> purposes. What I now understand (that I think I probably didn't fully
> understand back then) is that part of the point here is to enable
> installation of extensions without requiring local filesystem access;
> using a completely different mechanism would defeat that goal. But
> I'm still not altogether happy with where that's landed us.

I'd like to better understanding what is so wrong about the current
design in terms that I'm not feeling like we did address a year ago.

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

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> I'm not convinced we really need to solve that problem, but one way to
> solve it 'cleanly' would be to seperate the two types of extensions,
> perhaps by calling them by different names or inventing a namespace for
> extensions.

My understanding is that this line of thoughts gave us Extension
Templates which are templates, not extensions.

> I think we're falling into the trap of thinking that whatever this
> user-installable-collection-of-objects thing is, it must be considered
> PG 'extensions'. While I agree that some of the things we do for
> extensions should also be done with these collections of objects (eg:
> having versions and other meta-data for them), I'm starting to think
> that's the "small" side of this whole equation and duplicating that
> meta-data store for these collections would be easier than trying to
> shoehorn them into the existing notion of 'extensions'.

My main question when thinking that way is:

- how to update from a version to another one?

The point about extensions is that we separate the author who maintains
the upgrade scripts from the DBA who operates the upgrades. It seems to
me it's important to keep that property.

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

* Dimitri Fontaine (dimitri(at)2ndQuadrant(dot)fr) wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > I'm not convinced we really need to solve that problem, but one way to
> > solve it 'cleanly' would be to seperate the two types of extensions,
> > perhaps by calling them by different names or inventing a namespace for
> > extensions.
>
> My understanding is that this line of thoughts gave us Extension
> Templates which are templates, not extensions.

Errr. "Extension Templates" aren't collections of objects in the
database which can be used directly by users. That's what extensions
are, except that they have to currently be installed from files on the
filesystem.

> > I think we're falling into the trap of thinking that whatever this
> > user-installable-collection-of-objects thing is, it must be considered
> > PG 'extensions'. While I agree that some of the things we do for
> > extensions should also be done with these collections of objects (eg:
> > having versions and other meta-data for them), I'm starting to think
> > that's the "small" side of this whole equation and duplicating that
> > meta-data store for these collections would be easier than trying to
> > shoehorn them into the existing notion of 'extensions'.
>
> My main question when thinking that way is:
>
> - how to update from a version to another one?

You pull down the new "collection-of-objects" pgxn module (or whatever)
and find the script that says "go from X.Y to Y.Z" and then run it, and
it runs the necessary add-to-collection/remove-from-collection magic
through libpq. Note that there would be a tool to do this, of course..
But my point is that it'd be a *client-side* tool, not something which
the backend does by reaching out to the internet and downloading files.

> The point about extensions is that we separate the author who maintains
> the upgrade scripts from the DBA who operates the upgrades. It seems to
> me it's important to keep that property.

I agree.

Thanks,

Stephen

On 3 December 2013 02:02, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr> wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
>> On the other hand, I can appreciate the concern that we don't really
>> want a dump/restore to include the extension definition when it's
>> already on the filesystem. That said, it amazes me that we don't
>> include the version # of the extension in pg_dump's 'CREATE EXTENSION'
>> command.. How is that not a problem?
>
> Including the version number would be a problem.
>
> When you install PostgreSQL 9.1, you only have hstore 1.0.
> When you install PostgreSQL 9.2, you only have hstore 1.1.
> When you install PostgreSQL 9.3, you only have hstore 1.2.

ISTM that the real solution to this particular problem is to decouple
the extensions that are currently in contrib from a specific postgres
version. We have an extension mechanism now, and a distribution
mechanism (which people may or may not like, personally I'd still
rather install rpms) so why do we still need to ship these things as a
blessed bundle which is tied to a specific release?

If things were split out, it would be much easier for extension
authors to maintain branches targeting different major versions of
pgsql. Users could then upgrade those separately from upgrading the
major db version.

If this were considered seriously, we could still package up a contrib
tarball with the same set as we have now, and for testing we could
either teach the buildfarm client to pull the contrib modules from
their respective homes, or replace the contrib dirs with git submodule
refs.

I suppose there's a case to be made that there will always be some
extensions which are inseparable - plpgsql is the obvious case - but
we do go to a fair bit of effort to keep backward compatibility in
that case. So the version number issue isn't as much of a deal. Most
extensions we'd expect to be more fluid, and we'd expect users to
upgrade at (perhaps) a different rate to the main db. Is there a case
for a small number of "built-in" extensions being versionless, but
everything else requiring a version number AND dumping out with the
version number?

Cheers

Tom

Tom Dunstan <pgsql(at)tomd(dot)cc> writes:
> On 3 December 2013 02:02, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr> wrote:
>> Stephen Frost <sfrost(at)snowman(dot)net> writes:
>>> On the other hand, I can appreciate the concern that we don't really
>>> want a dump/restore to include the extension definition when it's
>>> already on the filesystem. That said, it amazes me that we don't
>>> include the version # of the extension in pg_dump's 'CREATE EXTENSION'
>>> command.. How is that not a problem?

>> Including the version number would be a problem.
>>
>> When you install PostgreSQL 9.1, you only have hstore 1.0.
>> When you install PostgreSQL 9.2, you only have hstore 1.1.
>> When you install PostgreSQL 9.3, you only have hstore 1.2.

> ISTM that the real solution to this particular problem is to decouple
> the extensions that are currently in contrib from a specific postgres
> version.

"Problem"? It's not a bug that you get hstore 1.2 when you dump from 9.2
and reload into 9.3; that's a feature. You wanted an upgrade, presumably,
or you'd not have been going to 9.3 in the first place. The entire reason
why the extension mechanism works like it does is to allow that sort of
reasonably-transparent upgrade. It would not be a step forward to break
that by having pg_dump prevent it (which it would fail to do anyway,
likely, since the receiving installation might not have 1.1 available
to install).

regards, tom lane

On 3 December 2013 12:12, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Problem"? It's not a bug that you get hstore 1.2 when you dump from 9.2
> and reload into 9.3; that's a feature. You wanted an upgrade, presumably,
> or you'd not have been going to 9.3 in the first place. The entire reason
> why the extension mechanism works like it does is to allow that sort of
> reasonably-transparent upgrade. It would not be a step forward to break
> that by having pg_dump prevent it (which it would fail to do anyway,
> likely, since the receiving installation might not have 1.1 available
> to install).

Well, I was suggesting further down that extensions which we'd like to
consider built-in because they make guarantees about backwards
compatibility should simply be versionless, since we expect a
transparent upgrade, and as you point out the old version isn't likely
to be there. So maybe hstore wasn't a good example. Those should
continue to be dumped without versions.

(The counter argument is that if hstore 1.2 has awesome new features,
why should people wait until the next major version to enjoy them,
unless they depend on something changing in core?)

Extensions in contrib live in a weird place. Totally builtin stuff
should obviously be dumped without versions, and stuff which is
completely separate and follows its own release schedule should
obviously be versioned. I guess we consider all modules in contrib to
offer the same transparent upgrade guarantees as builtins, so they
shouldn't be versioned, but it feels like some of them should be, if
only because some aren't particularly tied in to the backend all that
tightly. But I guess that's a bogus metric, the true metric is whether
we want people to treat them as basically built-in, with the upgrade
guarantees that go along with that.

Cheers

Tom

On Sun, 2013-12-01 at 15:48 +0100, Dimitri Fontaine wrote:
> Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> > I don't see why we are trying to accommodate a case where the author
> > doesn't offer enough full SQL scripts and offers broken downgrade
> > scripts; or why that case is different from offering broken upgrade
> > scripts.
>
> That's fair enough I guess. I will work on automating the choice of the
> first full script to use then, for next patch version.

Can we separate this feature out? It's an issue with extensions today,
and I'm eager to make some progress after the explosion of differing
opinions today.

Robert, do you think this is an acceptable approach to solve your pet
peeve here:

http://www.postgresql.org/message-id/CA
+Tgmoae3Qs4QbQfxOUzZFxRSxA0zy8ibSOYSuuTzDUMPeAkAg(at)mail(dot)gmail(dot)com

As an aside, I'm still not entirely sure why pg_dump omits the version
of the extension. It seems to introduce unnecessary ways to fail during
restore -- e.g. you've updated to (and depend on) 1.1, but restore will
try to restore using an old default of 1.0. Is it so that extension
authors can drop support for old SQL scripts and the restore will just
find the newer one?

If the reason the extension version is left out is because we can't find
the upgrade path to the requested version, this feature should fix that.

Regards,
Jeff Davis

On Mon, 2013-12-02 at 11:07 +0200, Heikki Linnakangas wrote:
> On 12/02/2013 05:34 AM, Stephen Frost wrote:
> >> 2. When 9.4 gets released, we need some solid advice for extension
> >> authors. If they have a native shared library, I assume we just tell
> >> them to keep using the file-based templates. But if they have a SQL-only
> >> extension, do we tell them to port to the in-catalog templates? What if
> >> they port to in-catalog templates, and then decide they just want to
> >> optimize one function by writing it in native code? Do they have to port
> >> back? What should the authors of SQL-only extensions distribute on PGXN?
> >> Should there be a migration period where they offer both kinds of
> >> templates until they drop support for 9.3?
> >
> > This is one of the main things that I think Heikki was trying to drive
> > at with his comment- we really don't *want* to make extension authors
> > have to do anything different than what they do today. With an external
> > tool, they wouldn't need to and it would just be two different ways for
> > an extension to be installed into a given database. In the end though,
> > if we're telling people to 'port' their extensions, then I think we've
> > already lost.
>
> Exactly.
>
> There should be no difference between file-based extensions and
> catalog-based extensions. It's just two different ways to install the
> same extension. The extension author doesn't need to care about that,
> it's the DBA that decides which method to use to install it.
>
> I'm going to object loudly to any proposal that doesn't meet that criteria.

But we're arguably already in this position today. For a SQL-only
extension, the author can choose between:

1. Using a schema/namespace
a. installable over libpq
b. installable by non-superusers
c. no special handling when it comes to administration

2. Using an extension
a. convenient metadata (e.g. "requires")
b. upgrade scripts
c. omitted from pg_dump so can be managed separately
d. relocatable
e. not tied to one schema

And if the author decides to change, it requires porting the extension
to the other form.

Note: I'm using "extension" loosely here. We might call the SQL-only,
user-installable variety something else.

So how do we get to the point where we have clear advice to the author
of a SQL-only extension? And how do we do that without asking them to
port anything?

Stephen mentioned using external tools and/or metadata, but to me that
sounds like it would require porting the extension away from what's on
PGXN today.

Regards,
Jeff Davis

On 12/03/2013 09:25 AM, Jeff Davis wrote:
> On Mon, 2013-12-02 at 11:07 +0200, Heikki Linnakangas wrote:
>> There should be no difference between file-based extensions and
>> catalog-based extensions. It's just two different ways to install the
>> same extension. The extension author doesn't need to care about that,
>> it's the DBA that decides which method to use to install it.
>>
>> I'm going to object loudly to any proposal that doesn't meet that criteria.
>
> But we're arguably already in this position today. For a SQL-only
> extension, the author can choose between:
>
> 1. Using a schema/namespace
> a. installable over libpq
> b. installable by non-superusers
> c. no special handling when it comes to administration
>
> 2. Using an extension
> a. convenient metadata (e.g. "requires")
> b. upgrade scripts
> c. omitted from pg_dump so can be managed separately
> d. relocatable
> e. not tied to one schema
>
> And if the author decides to change, it requires porting the extension
> to the other form.
>
> Note: I'm using "extension" loosely here. We might call the SQL-only,
> user-installable variety something else.

Good point. It's not too hard to install an "extension" written as an
extension as plain schema objects, though. You can just run the .sql
script through psql. That's what you used to do before extensions were
invented. (the scripts in contrib contain an explicit check against
that, but I don't think that's common outside contrib)

Another perspective is that that's already a situation we'd rather not
have. Let's not make it worse by introducing a third way to install an
extension, which again requires the extension author to package the
extension differently.

> So how do we get to the point where we have clear advice to the author
> of a SQL-only extension? And how do we do that without asking them to
> port anything?

Yeah, that's the crucial question of this whole thread.

> Stephen mentioned using external tools and/or metadata, but to me that
> sounds like it would require porting the extension away from what's on
> PGXN today.

Why? The external tool can pick the extension in its current form from
PGXN, and install it via libpq. The tool might have to jump through some
hoops to do it, and we might need some new backend functionality to
support it, but I don't see why the extension author needs to do anything.

That said, it might make the tool easier to write if we place some new
requirements for extension authors. Like, stipulate that the .sql file
is in the top-level directory of the extension tarball. But the same
extension would still be installable with "make; make install".

- Heikki

On Tue, Dec 3, 2013 at 1:31 AM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> On Sun, 2013-12-01 at 15:48 +0100, Dimitri Fontaine wrote:
>> Jeff Davis <pgsql(at)j-davis(dot)com> writes:
>> > I don't see why we are trying to accommodate a case where the author
>> > doesn't offer enough full SQL scripts and offers broken downgrade
>> > scripts; or why that case is different from offering broken upgrade
>> > scripts.
>>
>> That's fair enough I guess. I will work on automating the choice of the
>> first full script to use then, for next patch version.
>
> Can we separate this feature out? It's an issue with extensions today,
> and I'm eager to make some progress after the explosion of differing
> opinions today.

+1 for separating that part out. I thought it was separated, at some point.

> Robert, do you think this is an acceptable approach to solve your pet
> peeve here:
>
> http://www.postgresql.org/message-id/CA
> +Tgmoae3Qs4QbQfxOUzZFxRSxA0zy8ibSOYSuuTzDUMPeAkAg(at)mail(dot)gmail(dot)com

I'd need to look exactly what's being proposed in more detail.

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

On Mon, Dec 2, 2013 at 7:46 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> Just tossing an idea out there. What if you could install an extension
>> by specifying not a local file name but a URL. Obviously there's a
>> security issue but for example we could allow only https URLs with
>> verified domain names that are in a list of approved domain names
>> specified by a GUC.
>
> That's a different feature, but I don't see anything preventing
> someone from implementing that as an extension, today, without any
> core support at all. It would only be usable in cases where the share
> directory is writable by the database server (i.e. low-security
> installations) and you'd have to make it a function call rather than
> piggybacking on CREATE EXTENSION, but neither of those things sound
> bad to me. (And if they are bad, they could be addressed by providing
> hooks or event triggers, leaving the rest of the functionality in the
> extension module.)

Well none of this isn't implementable as an extension if you have
write access to the database server's share directory.

This is all about UI. CREATE EXTENSION is about having the core do the
bookkeeping about which files belong to which version of which
extension.

I thought the fundamental problem the "in-catalog" extensions were
trying to solve were the issue with not having access to the
filesystem. If that's the case then being able to say create extension
from http://... would solve that.

If the fundamental problem is that you want multi-tenant databases to
be able to have different .so files visible depending on which
database is opened then that's a bit trickier.

--
greg

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> Can we separate this feature out? It's an issue with extensions today,
>> and I'm eager to make some progress after the explosion of differing
>> opinions today.
>
> +1 for separating that part out. I thought it was separated, at some point.

http://www.postgresql.org/message-id/CALtqXTetVi-eXhdBSpUey3TrthuG51esWUOd8cUR2t+RxtgEKg@mail.gmail.com
http://www.postgresql.org/message-id/m2r4k8jpfl.fsf@2ndQuadrant.fr

The only way for to bugfix all the reported problems had been to have
regression testing… and it's become a necessary dependency of the
extension templates patch, so I just included it in.

My interdependent git branches development fu seems to have totally
disappeared after the main extension patch that needed 7 of thoses…

> I'd need to look exactly what's being proposed in more detail.

What I did propose is a new GUC default_full_version:

+ <term><varname>default_full_version</varname> (<type>string</type>)</term>
+ <listitem>
+ <para>
+ This option allows an extension author to avoid shiping all versions
+ of all scripts when shipping an extension. When a version is requested
+ and the matching script does not exist on disk,
+ set <replaceable>default_full_version</replaceable> to the first
+ script you still ship and PostgreSQL will apply the intermediate
+ upgrade script as per the <command>ALTER EXTENSION UPDATE</command>
+ command.
+ </para>
+ <para>
+ For example, say you did provide the extension <literal>pair</literal>
+ version <literal>1.0</literal> and are now providing the
+ version <literal>1.1</literal>. If you want both current and new users
+ to be able to install the new version, you can provide both the
+ scripts <literal>pair--1.0--1.1.sql</literal>
+ and <literal>pair--1.1.sql</literal>, adding to the already
+ existing <literal>pair--1.0.sql</literal>.
+ </para>
+ <para>
+ When specifying <literal>default_version</literal>
+ and <literal>default_full_version = 1.0</literal> you can instead only
+ provide only the scripts <literal>pair--1.0.sql</literal>
+ and <literal>pair-1.0--1.1.sql</literal>. The <command>CREATE
+ EXTENSION pair;</command> will then automatically use the afore
+ mentionned scripts to install version 1.0 then update it to 1.1.
+ </para>
+ </listitem>

What Jeff is proposing is to simplify that down and have PostgreSQL auto
discover the upgrade cycle when the version asked for isn't directly
available with a creation script.

We would keep the behavior depicted here, just in a fully automated way.

Working on a separate patch for that, then.

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

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> > On 3 December 2013 02:02, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr> wrote:
> > ISTM that the real solution to this particular problem is to decouple
> > the extensions that are currently in contrib from a specific postgres
> > version.
>
> "Problem"? It's not a bug that you get hstore 1.2 when you dump from 9.2
> and reload into 9.3; that's a feature. You wanted an upgrade, presumably,

I don't buy this argument at *all* and it's not going to fly when we've
got multiple versions of an extension available concurrently. I'm
willing to accept that we have limitations when it comes from a
packaging perspective (today) around extensions but the notion that my
backup utility should intentionally omit information which is required
to restore the database to the same state it was in is ridiculous.

> or you'd not have been going to 9.3 in the first place.

This notion that a given major version of PG only ever has one version
of an extension associated with it is *also* wrong and only makes any
sense for contrib extensions- which are the exception rather than the
rule today.

> The entire reason
> why the extension mechanism works like it does is to allow that sort of
> reasonably-transparent upgrade. It would not be a step forward to break
> that by having pg_dump prevent it (which it would fail to do anyway,
> likely, since the receiving installation might not have 1.1 available
> to install).

I agree that there should be a way to *allow* such an upgrade to happen
transparently and perhaps we keep it the way it is for contrib
extensions as a historical artifact, but other extensions are
independent of the PG major version and multiple versions will be
available concurrently for them and having pg_dump willfully ignore the
extension version is a receipe for broken backups.

Step back and consider a user who is just trying to restore his backup
of his 9.2 database into a new server, also with 9.2, as quickly as he
can to get his system online again. Having four different versions of
extension X installed and available for 9.2, no clue or information
about which version was installed into which databases and getting
mysterious failures and errors because they're not all compatible is not
what anyone is going to want to deal with in that situation.

I certainly don't see extensions (outside of contrib) in the general
sense as being either tied to specific PG versions or being required to
maintain the same API that they started with on day 1.

Thanks,

Stephen

* Tom Dunstan (pgsql(at)tomd(dot)cc) wrote:
> Extensions in contrib live in a weird place. Totally builtin stuff
> should obviously be dumped without versions, and stuff which is
> completely separate and follows its own release schedule should
> obviously be versioned. I guess we consider all modules in contrib to
> offer the same transparent upgrade guarantees as builtins, so they
> shouldn't be versioned, but it feels like some of them should be, if
> only because some aren't particularly tied in to the backend all that
> tightly. But I guess that's a bogus metric, the true metric is whether
> we want people to treat them as basically built-in, with the upgrade
> guarantees that go along with that.

Note that we don't actually make guarantees about either builtins or
contrib modules when it comes to major version upgrades. The current
way we package contrib and how we tie contrib releases to PG releases
means that we can get away with omitting the version and saying "well,
if you restore to the same PG major version then you'll get the same
contrib extension version, and if you restore into a different version
then obviously you want the version of contrib with that major
version" but that *only* works for contrib. We need to accept that
other extensions exist and that they aren't tied to PG major versions
nor to our release schedule. There's a lot more extensions out there
today which are *not* in contrib than there are ones which *are*.

Thanks,

Stephen

* Jeff Davis (pgsql(at)j-davis(dot)com) wrote:
> Stephen mentioned using external tools and/or metadata, but to me that
> sounds like it would require porting the extension away from what's on
> PGXN today.

Not at all- and that'd be the point. An external tool could take the
PGXN extension, run 'make', then 'make install' (into a userland
directory), extract out the script and then, with a little help from PG,
run that script in "extension creation mode" via libpq.

Another option, which I generally like better, is to have a new package
format for PGXN that contains the results of "make install",
more-or-less, synonymous to Debian source vs. .deb packages.

Perhaps we could even have psql understand that format and be able to
install the extension via a backslash command instead of having an
external tool, but I think an external tool for dependency tracking and
downloading of necessary dependencies ala Debian would be better than
teaching psql to do that.

Thanks,

Stephen

* Greg Stark (stark(at)mit(dot)edu) wrote:
> I thought the fundamental problem the "in-catalog" extensions were
> trying to solve were the issue with not having access to the
> filesystem. If that's the case then being able to say create extension
> from http://... would solve that.

That's not really 'solved' unless you feel we can depend on that "create
extension from URL" to work at pg_restore time... I wouldn't have
guessed that people would accept that, but I've already been wrong about
such things in this thread once.

Thanks,

Stephen

On Tue, Dec 3, 2013 at 8:44 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>> > On 3 December 2013 02:02, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr> wrote:
>> > ISTM that the real solution to this particular problem is to decouple
>> > the extensions that are currently in contrib from a specific postgres
>> > version.
>>
>> "Problem"? It's not a bug that you get hstore 1.2 when you dump from 9.2
>> and reload into 9.3; that's a feature. You wanted an upgrade, presumably,
>
> I don't buy this argument at *all* and it's not going to fly when we've
> got multiple versions of an extension available concurrently. I'm
> willing to accept that we have limitations when it comes from a
> packaging perspective (today) around extensions but the notion that my
> backup utility should intentionally omit information which is required
> to restore the database to the same state it was in is ridiculous.
>
>> or you'd not have been going to 9.3 in the first place.
>
> This notion that a given major version of PG only ever has one version
> of an extension associated with it is *also* wrong and only makes any
> sense for contrib extensions- which are the exception rather than the
> rule today.
>
>> The entire reason
>> why the extension mechanism works like it does is to allow that sort of
>> reasonably-transparent upgrade. It would not be a step forward to break
>> that by having pg_dump prevent it (which it would fail to do anyway,
>> likely, since the receiving installation might not have 1.1 available
>> to install).
>
> I agree that there should be a way to *allow* such an upgrade to happen
> transparently and perhaps we keep it the way it is for contrib
> extensions as a historical artifact, but other extensions are
> independent of the PG major version and multiple versions will be
> available concurrently for them and having pg_dump willfully ignore the
> extension version is a receipe for broken backups.
>
> Step back and consider a user who is just trying to restore his backup
> of his 9.2 database into a new server, also with 9.2, as quickly as he
> can to get his system online again. Having four different versions of
> extension X installed and available for 9.2, no clue or information
> about which version was installed into which databases and getting
> mysterious failures and errors because they're not all compatible is not
> what anyone is going to want to deal with in that situation.

I think Tom's original idea here was that new versions of extensions
*shouldn't ever* be backward-incompatible, and therefore if this
problem arises it's the extension author's fault. It isn't however
clear that this dream is likely to be realized in practice. For
example, the only difference between hstore 1.0 and hstore 1.1 is that
we dropped the => operator, for the very good reason that we have been
slowly working towards deprecating => as an operator name so that we
can eventually use it for the purpose that the SQL standard specifies.
Given that we've done it in core, we can hardly say that no one will
ever do this anywhere else.

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

On Tue, Dec 3, 2013 at 8:43 AM, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr> wrote:
> What Jeff is proposing is to simplify that down and have PostgreSQL auto
> discover the upgrade cycle when the version asked for isn't directly
> available with a creation script.
>
> We would keep the behavior depicted here, just in a fully automated way.
>
> Working on a separate patch for that, then.

I like the idea of making it automatic, but it won't work in all
cases. For example, suppose someone ships 1.0, 1.0--1.2, 1.1, and
1.1--1.2. Since versions aren't intrinsically ordered, the system has
no way of knowing whether it's preferable to run 1.0 and then 1.0--1.2
or instead run 1.1 and then 1.1--1.2. So I think we will need either
to introduce a way of ordering version numbers (which Tom has
previously opposed) or some concept like your default_full_version.

In more normal cases, however, the system can (and probably should)
figure out what was intended by choosing the *shortest* path to get to
the intended version. For example, if someone ships 1.0, 1.0--1.1,
1.1, and 1.1--1.2, the system should choose to run 1.1 and then
1.1--1.2, not 1.0 and then 1.0--1.1 and then 1.1--1.2. But that can
be automatic: only if there are two paths of equal length (as in the
example in the previous paragraph) do we need help from the user to
figure out what to do.

We should also consider the possibility of a user trying to
deliberately install and older release. For example, if the user has
1.0, 1.0--1.1, 1.1, 1.1--1.2, and 1.2--1.0 (a downgrade script) with
default_full_version = 1.2, an attempt to install 1.0 should run just
the 1.0 script, NOT 1.2 and then 1.2--1.0.

Putting all that together, I'm inclined to suggest that what we really
need is a LIST of version numbers, rather than just one. If there one
path to the version we're installing is shorter than any other, we
choose that, period. If there are multiple paths of equal length, we
break the tie by choosing which version number appears first in the
aforementioned list. If that still doesn't break the tie, either
because none of the starting points are mentioned in that list or
because there are multiple equal-length paths starting in the same
place, we give up and emit an error.

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

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> That's not really 'solved' unless you feel we can depend on that "create
> extension from URL" to work at pg_restore time... I wouldn't have
> guessed that people would accept that, but I've already been wrong about
> such things in this thread once.

Basically, with the extra software I want to build out-of-core, what you
have is an externally maintained repository and the scripts are
downloaded at CREATE EXTENSION time.

With the Extension Template, you then have a solid cache you can rely on
at pg_restore time.

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

* Dimitri Fontaine (dimitri(at)2ndQuadrant(dot)fr) wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > That's not really 'solved' unless you feel we can depend on that "create
> > extension from URL" to work at pg_restore time... I wouldn't have
> > guessed that people would accept that, but I've already been wrong about
> > such things in this thread once.
>
> Basically, with the extra software I want to build out-of-core, what you
> have is an externally maintained repository and the scripts are
> downloaded at CREATE EXTENSION time.