Thread: Extension Packaging

Hackers,

I finally got round to updating a couple of my extensions to support 9.1 extensions. Unlike the contrib extensions,
theseneeded to target older versions of PostgreSQL, as well. Case in point, the semver data type; you might find the
Makefileof particular interest: 
 https://github.com/theory/pg-semver/blob/master/Makefile

Andrew Dunstan helped me figure out how to get this working, but I have to say, I'm less than thrilled at the
contortionsnecessary to support both 9.1 migration scripts and traditional installation scripts. No need to go into
detailon it, really; you can see it in that Makefile or read about it on the PGXN blog. 
 http://blog.pgxn.org/post/4783001135/extension-makefiles

I'm really thrilled with the extensions stuff. It makes it about as easy as possible for users to add them to their
database.And I think it's entirely appropriate that the complexity of managing extension upgrades between versions has
beenmoved from users/DBAs to extension developers. That said, there are a couple of things that would substantially
easethe the load for developers: 

* I would love to be able to maintain a single file for the default version of an extension. So rather than
distributingsql/semver--0.2.2.sql or, as I've done in the Makefile, copy sql/semver.sql to sql/semver--0.1.2.sql, if a
filename with no version in it was considered the same as the default version, then the Makefile could go back to being
muchsimpler (almost; see next point). That is, I'd install semver.sql on >= 9.1 and on < 9.1. I wouldn't have to check
whatversion I was installing against in the Makefile and do something different, which, frankly, is ugly and
error-prone.

* For the special unpackaged script, I'd like to be able to do something similar. At first I thought I could just
maintainand distribute a sql/semver--unpackaged--0.1.2.sql file and, beyond that, regular migration scrips would handle
things.But then, if someone installed 0.1.3 against 9.0, then upgraded to 9.1 and then issued `CREATE EXTENSION FROM
unpackaged`,then everything that was in 0.1.2 would be added to the extension package, but anything added in 0.1.3
wouldnot. 

So what I've done instead is maintain a file, sql/semver--unpackaged.sql, and I copy it to a file named for the current
versionin the Makefile. So this will just be kept up-to-date with the latest version, and will always be installed as
semver--unpackaged--$defaul_version.sql.But I sure wish I didn't have to do that. 

What if, instead, I could just install semver--unpackaged.sql, and the extension system knew that this one was for
addingexisting objects to an extension package? I realize this means that the term "unpackaged" would have to be
reservedand treated specially, and that this is, really, a temporary issue (for perhaps five years), but still, it
wouldease things in the short term, and I'm not sure how likely it is anyone would want to use "unpackaged" for a
versionnumber, anyway. 

* Another, minor point: If I release a new version with no changes to the code (as I've done today, just changing the
makestuff and documentation), it's kind of annoying that I'd need to have a migration script from the old version to
thenew version that's…empty. But I dunno, maybe not such a big deal. It's useful to have it there with a comment in it:
“Nochanges.” 

Anyway, those are just my thoughts. Comments?

Best,

David

On Thu, Apr 21, 2011 at 1:14 AM, David E. Wheeler <david@kineticode.com> wrote:

> I finally got round to updating a couple of my extensions to support 9.1 extensions. Unlike the contrib extensions,
theseneeded to target older versions of PostgreSQL, as well. Case in point, the semver data type; you might find the
Makefileof particular interest: 
>
>  https://github.com/theory/pg-semver/blob/master/Makefile

Hi David, thanks for sharing.

I've recently packaged an extension for PG 8.4-9.1 and had to wrestle
the Makefile too. You may take a look at it and check if there is any
solution useful for your extension too:
<https://github.com/dvarrazzo/pgmp/blob/master/Makefile>.

Specifically, I parse the version from the control file using:
   PGMP_VERSION=$(shell grep default_version pgmp.control | sed -e
"s/default_version = '\(.*\)'/\1/")

so the Makefile doesn't have to be maintained for it. To tell apart <
9.1 and >= 9.1 I've used instead:
   PG91 = $(shell $(PG_CONFIG) --version | grep -qE " 8\.| 9\.0" &&
echo pre91 || echo 91)   ifeq ($(PG91),91)   ...   else   ...

For my extension I'm less concerned by having the install sql named in
different ways or by the upgrade sql as all these files are generated
by scripts. You may find useful this one
<https://github.com/dvarrazzo/pgmp/blob/master/tools/sql2extension.py>
to generate the upgrade sql from the install sql. For my extension I
require Python and have all the sql files generated by the Makefile at
install time; if you don't want this dependency you may generate the
sql before packaging and ship the result instead.

Cheers,

-- Daniele

"David E. Wheeler" <david@kineticode.com> writes:
> * I would love to be able to maintain a single file for the default
> version of an extension.

Basically, this wasn't and isn't on the list of considerations.  There
has never been any expectation that a contrib module could use the exact
same SQL script for every Postgres version, and indeed one big thrust of
the extensions design has been to make it easy to have different ones.
I'm not interested in kluging things up after the fact to try to somehow
reverse that mindset and make pre-extension-world and post-extension-world
scripts compatible.  That looks like long-term pain in return for very
small short-term gain to me.

> * For the special unpackaged script, I'd like to be able to do something similar. At first I thought I could just
maintainand distribute a sql/semver--unpackaged--0.1.2.sql file and, beyond that, regular migration scrips would handle
things.But then, if someone installed 0.1.3 against 9.0, then upgraded to 9.1 and then issued `CREATE EXTENSION FROM
unpackaged`,then everything that was in 0.1.2 would be added to the extension package, but anything added in 0.1.3
wouldnot.
 

If you have multiple old versions that you want to support direct
upgrades from, you should *not* use the unvarnished "unpackaged" naming
convention for those upgrade scripts.  Use the real version names
instead, and instruct the users that they'd better get it right when
specifying the FROM version.  (Or if possible, set up the scripts to
intentionally fail should they be invoked with the wrong previous
version in place --- eg, it's not bad if they fail when trying to
replace an object that's not there.)

Or to put it more succinctly: there is nothing special about the name
"unpackaged".

> * Another, minor point: If I release a new version with no changes to the code (as I've done today, just changing the
makestuff and documentation), it's kind of annoying that I'd need to have a migration script from the old version to
thenew version that's�empty. But I dunno, maybe not such a big deal. It's useful to have it there with a comment in it:
�Nochanges.�
 

If you did not actually change the contents of the install script, you
should not change its version number either.
        regards, tom lane

On Apr 20, 2011, at 8:16 PM, Tom Lane wrote:

> I'm not interested in kluging things up after the fact to try to somehow
> reverse that mindset and make pre-extension-world and post-extension-world
> scripts compatible.  That looks like long-term pain in return for very
> small short-term gain to me.

Okay. What about building something into PGXS that could handle these kinds of things? I just can't help but wonder if
thereisn't some better way to do the kinds of things that Daniele and I have resorted to to use a PostgreSQL version in
aconditional in the Makefile. I know *this* much about make, and so am pretty sure that there must be a better way to
doit than the way I am. 

> If you have multiple old versions that you want to support direct
> upgrades from, you should *not* use the unvarnished "unpackaged" naming
> convention for those upgrade scripts.  Use the real version names
> instead, and instruct the users that they'd better get it right when
> specifying the FROM version.  (Or if possible, set up the scripts to
> intentionally fail should they be invoked with the wrong previous
> version in place --- eg, it's not bad if they fail when trying to
> replace an object that's not there.)

Yeah, I was thinking about that, too. It would require a lot of duplication for an extension that doesn't often change,
butin a few years it could be dumped. 

> If you did not actually change the contents of the install script, you
> should not change its version number either.

You know what? Duh! I should have thought of that. Glad I made the decision to allow an extension/version combination
toappear in more than one distribution on PGXN. Was kind of a PITA to add, but clearly was the right choice. 

Best,

David

On Apr 20, 2011, at 8:04 PM, Daniele Varrazzo wrote:

> Specifically, I parse the version from the control file using:
>
>    PGMP_VERSION=$(shell grep default_version pgmp.control | sed -e
> "s/default_version = '\(.*\)'/\1/")

Oh, that's not bad. Thanks.

> so the Makefile doesn't have to be maintained for it. To tell apart <
> 9.1 and >= 9.1 I've used instead:
>
>    PG91 = $(shell $(PG_CONFIG) --version | grep -qE " 8\.| 9\.0" &&
> echo pre91 || echo 91)
>    ifeq ($(PG91),91)
>    ...
>    else
>    ...

Oh, definitely better and more future-proof than what I had. Mind if I poach these examples and document them for PGXN
distribution?

> For my extension I'm less concerned by having the install sql named in
> different ways or by the upgrade sql as all these files are generated
> by scripts. You may find useful this one
> <https://github.com/dvarrazzo/pgmp/blob/master/tools/sql2extension.py>
> to generate the upgrade sql from the install sql. For my extension I
> require Python and have all the sql files generated by the Makefile at
> install time; if you don't want this dependency you ma

Yeah, I recently started using Perl to break up pgTAP into multiple files. I struggled for a while over whether or not
torequire it or to build the files at distribution time. I finally settled for having it run at install time, but it's
notrequired: you just wouldn't get the broken-up files without it, only the great big one. I guess one could do the
samewith your script: If you don't have python, you don't get the unpackaged migration scripts. 

Thanks again,

David

On Apr 20, 2011, at 8:25 PM, David E. Wheeler wrote:

> Okay. What about building something into PGXS that could handle these kinds of things? I just can't help but wonder
ifthere isn't some better way to do the kinds of things that Daniele and I have resorted to to use a PostgreSQL version
ina conditional in the Makefile. I know *this* much about make, and so am pretty sure that there must be a better way
todo it than the way I am. 

Okay, thanks to the feedback from Daniele, and based on your advice, Tom, to maintain explicit migration scripts (at
leastfor now, might use Daniele's script later), I've got the Makefile down to just 25 lines, and other than setting
thename of the extension on the first line, it should just work for any other extension I care to distribute using the
samefile layout. 
 https://github.com/theory/pg-semver/blob/master/Makefile

Thanks again for the feedback. Will be polishing this stuff for my PGCon talk on packaging extensions for PGXN.

Best,

David

Daniele Varrazzo <daniele.varrazzo@gmail.com> writes:
> For my extension I'm less concerned by having the install sql named in
> different ways or by the upgrade sql as all these files are generated
> by scripts. You may find useful this one

You can also generate that reliably in SQL.  You install your extension
with CREATE EXTENSION then run the query over pg_depend and you have it
all.  Then you can test this upgrade script you just got in SQL.  Tom
also has a version that does the necessary string replacements using sed
from a bash script rather than the SQL replace() function.
 http://archives.postgresql.org/pgsql-hackers/2011-02/msg01208.php
http://archives.postgresql.org/pgsql-hackers/2011-02/msg01438.php

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr

On Thu, Apr 21, 2011 at 4:16 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "David E. Wheeler" <david@kineticode.com> writes:

>> * Another, minor point: If I release a new version with no changes to the code (as I've done today, just changing
themake stuff and documentation), it's kind of annoying that I'd need to have a migration script from the old version
tothe new version that's…empty. But I dunno, maybe not such a big deal. It's useful to have it there with a comment in
it:“No changes.” 
>
> If you did not actually change the contents of the install script, you
> should not change its version number either.

Sorry, I'm not entirely convinced. If I release an extension 1.0, then
find a bug in the C code and fix it in 1.0.1, arguably "make install"
will put the .so in the right place and the 1.0.1 code will be picked
up by new sessions. But pg_extension still knows 1.0 as extension
version, and ALTER EXTENSION ... UPGRADE fails because no update path
is knows.

There is also a dangerous asymmetry: If I'm not mistaken the library
.so has no version number, so there can be only one version in the
system: an update changing code and sql requires ALTER EXTENSION to be
run as soon as possible, or some sql function from the old extension
may try to call non-existing functions in the library - or worse the
wrong ones or with wrong parameters. OTOH library-only changes don't
strictly require ALTER EXTENSION - and trying to issue the command
would fail if no path to the default version is available (leaving the
admin puzzled about whether he installed the upgrade properly).

Is an empty upgrade file the only way to get the extension metadata
right? I wouldn't find it particularly bad, but better have it that
have library-metadata mismatches or inconsistencies in the upgrade
procedures I think.

-- Daniele

On Apr 23, 2011, at 1:03 PM, Dimitri Fontaine wrote:

> Daniele Varrazzo <daniele.varrazzo@gmail.com> writes:
>> For my extension I'm less concerned by having the install sql named in
>> different ways or by the upgrade sql as all these files are generated
>> by scripts. You may find useful this one
> 
> You can also generate that reliably in SQL.  You install your extension
> with CREATE EXTENSION then run the query over pg_depend and you have it
> all.  Then you can test this upgrade script you just got in SQL.  Tom
> also has a version that does the necessary string replacements using sed
> from a bash script rather than the SQL replace() function.
> 
>  http://archives.postgresql.org/pgsql-hackers/2011-02/msg01208.php
>  http://archives.postgresql.org/pgsql-hackers/2011-02/msg01438.php

Nice. Did you and Tom ever work out the difference in results?
 http://archives.postgresql.org/pgsql-hackers/2011-02/msg01572.php

I'd like to see this documented somewhere, perhaps in 
 http://developer.postgresql.org/pgdocs/postgres/extend-extensions.html

Thanks,

David

Daniele Varrazzo <daniele.varrazzo@gmail.com> writes:
> On Thu, Apr 21, 2011 at 4:16 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> If you did not actually change the contents of the install script, you
>> should not change its version number either.

> Sorry, I'm not entirely convinced. If I release an extension 1.0, then
> find a bug in the C code and fix it in 1.0.1, arguably "make install"
> will put the .so in the right place and the 1.0.1 code will be picked
> up by new sessions. But pg_extension still knows 1.0 as extension
> version, and ALTER EXTENSION ... UPGRADE fails because no update path
> is knows.

If you didn't change the install script then it's not necessary to
execute ALTER EXTENSION ... UPGRADE.  You seem to be assuming that the
pg_extensions catalog has to reflect the bug fix level of an extension,
but that is *not* the intention.  If it did reflect that, you'd need
N times as many upgrade scripts, most of them identical, to deal with
updating from different bug fix levels of the prior version.

IMO it'd be better if the bug fix level was tracked outside the
database, for instance via an RPM package version/release number.
I'm not sure whether PGXN has anything for that at the moment.

For a comparative data point, there is no catalog-related way to tell
which minor release number you are running within a Postgres major
release.  There is the version() function, so one way to deal with this
would be to provide a version()-like function for your extension.

> There is also a dangerous asymmetry: If I'm not mistaken the library
> .so has no version number, so there can be only one version in the
> system: an update changing code and sql requires ALTER EXTENSION to be
> run as soon as possible, or some sql function from the old extension
> may try to call non-existing functions in the library - or worse the
> wrong ones or with wrong parameters.

We've been over that in the previous discussions, please see the
archives.  I believe the conclusion was that breaking ABI compatibility
within an update is just not a good idea.
        regards, tom lane

On Apr 24, 2011, at 2:46 PM, Tom Lane wrote:

> IMO it'd be better if the bug fix level was tracked outside the
> database, for instance via an RPM package version/release number.
> I'm not sure whether PGXN has anything for that at the moment.

Distributions may have their own versions independent of the versions of the extensions they contain. Is that
sufficient?

Best,

Davdi

"David E. Wheeler" <david@kineticode.com> writes:
> On Apr 24, 2011, at 2:46 PM, Tom Lane wrote:
>> IMO it'd be better if the bug fix level was tracked outside the
>> database, for instance via an RPM package version/release number.
>> I'm not sure whether PGXN has anything for that at the moment.

> Distributions may have their own versions independent of the versions of the extensions they contain. Is that
sufficient?

Hmm ... it's sufficient, but I think people are going to be confused as
to proper usage if you call two different things the "version".  In RPM
terminology there's a clear difference between "version" and "release";
maybe some similar wording should be adopted here?  Or use "major
version" versus "minor version"?
        regards, tom lane

On Apr 24, 2011, at 2:55 PM, Tom Lane wrote:

> Hmm ... it's sufficient, but I think people are going to be confused as
> to proper usage if you call two different things the "version".  In RPM
> terminology there's a clear difference between "version" and "release";
> maybe some similar wording should be adopted here?  Or use "major
> version" versus "minor version"?

I could "distribution version" =~ s/version/release/; Frankly, the way the terminology is now it's halfway-there
already.

So distribution semver release 1.1.0 might contain extension semver version 1.0.0.

Hrm, Still rather confusing.

Best,

David

On Sun, Apr 24, 2011 at 10:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Daniele Varrazzo <daniele.varrazzo@gmail.com> writes:
>> On Thu, Apr 21, 2011 at 4:16 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> If you did not actually change the contents of the install script, you
>>> should not change its version number either.
>
>> Sorry, I'm not entirely convinced. If I release an extension 1.0, then
>> find a bug in the C code and fix it in 1.0.1, arguably "make install"
>> will put the .so in the right place and the 1.0.1 code will be picked
>> up by new sessions. But pg_extension still knows 1.0 as extension
>> version, and ALTER EXTENSION ... UPGRADE fails because no update path
>> is knows.
>
> If you didn't change the install script then it's not necessary to
> execute ALTER EXTENSION ... UPGRADE.  You seem to be assuming that the
> pg_extensions catalog has to reflect the bug fix level of an extension,
> but that is *not* the intention.  If it did reflect that, you'd need
> N times as many upgrade scripts, most of them identical, to deal with
> updating from different bug fix levels of the prior version.

Yes, I was assuming that the pg_extension catalog should have included
the bug fix level, and I noticed the explosion of upgrade paths
required.


> IMO it'd be better if the bug fix level was tracked outside the
> database, for instance via an RPM package version/release number.
> I'm not sure whether PGXN has anything for that at the moment.

PGXN requires a version for the extension, possibly including the
patchlevel (well, actually forcing a patchlevel, as per semver spec),
and I/David/probably everybody else were thinking that such version
ought to be the same specified in the .control file. I see that a
better guideline would be to have '1.0' specified in the control and
'1.0.X' in the metadata submitted on PGXN, which I think is not
currently the case - see for example
http://api.pgxn.org/src/pair/pair-0.1.2/pair.control


-- Daniele

"David E. Wheeler" <david@kineticode.com> writes:
> On Apr 24, 2011, at 2:55 PM, Tom Lane wrote:
>> Hmm ... it's sufficient, but I think people are going to be confused as
>> to proper usage if you call two different things the "version".  In RPM
>> terminology there's a clear difference between "version" and "release";
>> maybe some similar wording should be adopted here?  Or use "major
>> version" versus "minor version"?

> I could "distribution version" =~ s/version/release/; Frankly, the way the terminology is now it's halfway-there
already.

> So distribution semver release 1.1.0 might contain extension semver version 1.0.0.

> Hrm, Still rather confusing.

Yeah.  It seems like a bad idea if the distribution "name" doesn't
include sufficient information to tell which version it contains.
I had in mind a convention like "distribution version x.y.z always
contains extension version x.y".  Seems like minor version versus
major version would be the way to explain that.
        regards, tom lane

On Apr 24, 2011, at 3:03 PM, Tom Lane wrote:

> Yeah.  It seems like a bad idea if the distribution "name" doesn't
> include sufficient information to tell which version it contains.
> I had in mind a convention like "distribution version x.y.z always
> contains extension version x.y".  Seems like minor version versus
> major version would be the way to explain that.

Does that apply to PostgreSQL itself? I guess release 9.0.4 contains 9.0. But it's a convention.

David

On Sun, Apr 24, 2011 at 6:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "David E. Wheeler" <david@kineticode.com> writes:
>> On Apr 24, 2011, at 2:55 PM, Tom Lane wrote:
>>> Hmm ... it's sufficient, but I think people are going to be confused as
>>> to proper usage if you call two different things the "version".  In RPM
>>> terminology there's a clear difference between "version" and "release";
>>> maybe some similar wording should be adopted here?  Or use "major
>>> version" versus "minor version"?
>
>> I could "distribution version" =~ s/version/release/; Frankly, the way the terminology is now it's halfway-there
already.
>
>> So distribution semver release 1.1.0 might contain extension semver version 1.0.0.
>
>> Hrm, Still rather confusing.
>
> Yeah.  It seems like a bad idea if the distribution "name" doesn't
> include sufficient information to tell which version it contains.
> I had in mind a convention like "distribution version x.y.z always
> contains extension version x.y".  Seems like minor version versus
> major version would be the way to explain that.

I think it's a bit awkward that we have to do it this way, though.
The installed version of the extension at the SQL level won't match
what the user thinks they've installed.  Granted, it'll be in the
ballpark (1.0 vs 1.0.3, for example) but that's not quite the same
thing.  I also note that we've moved PDQ from thinking that versions
are opaque strings to having pretty specific ideas about how they are
going to have to be assigned and managed to avoid maintainer insanity.That suggests to me that at a minimum we need
somemore documentation 
here.

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

On Apr 25, 2011, at 5:49 AM, Robert Haas wrote:

> I think it's a bit awkward that we have to do it this way, though.
> The installed version of the extension at the SQL level won't match
> what the user thinks they've installed.  Granted, it'll be in the
> ballpark (1.0 vs 1.0.3, for example) but that's not quite the same
> thing.  I also note that we've moved PDQ from thinking that versions
> are opaque strings to having pretty specific ideas about how they are
> going to have to be assigned and managed to avoid maintainer insanity.
> That suggests to me that at a minimum we need some more documentation
> here.

These are really great points. I knew I wasn't thrilled about this suggest, but wasn't sure why. Frankly, I think it
willbe really confusing to users who think they have FooBar 1.2.2 installed but see only 1.2 in the database. I don't
thinkI would do that, personally. I'm much more inclined to have the same extension version everywhere I can. 

If the core wants to build some infrastructure around the meaning of versions, then it will make sense (especially if
there'sa way to see *both* versions). But if not, I frankly don't see the point. 

Best,

David

On Mon, Apr 25, 2011 at 12:00 PM, David E. Wheeler <david@kineticode.com> wrote:

> These are really great points. I knew I wasn't thrilled about this suggest, but wasn't sure why. Frankly, I think it
willbe really confusing to users who think they have FooBar 1.2.2 installed but see only 1.2 in the database. I don't
thinkI would do that, personally. I'm much more inclined to have the same extension version everywhere I can. 

Really, that means you just a sql function to your extension,
somethign similary to uname -a, or rpm -qi, which includes something
that is *forced* to change the postgresql catalog view of your
extension every time you ship a new version (major, or patch), and
then you get the exact version (and whatever else you include) for
free every time you update ;-)

The thing to remember is that the postgresql "extensions" are managing
the *postgresql catalogs* view of things, even though the shared
object used by postgresql to provide the particular catalog's
requirements can be "fixed".

If your extension is almost exclusively a shared object, and the only
catalog things are a couple of functions defined to point into the C
code, there really isn't anything catalog-wise that you need to
"manage" for upgrades.

--
Aidan Van Dyk                                             Create like a god,
aidan@highrise.ca                                       command like a king,
http://www.highrise.ca/                                   work like a slave.

On Apr 25, 2011, at 9:14 AM, Aidan Van Dyk wrote:

> Really, that means you just a sql function to your extension,
> somethign similary to uname -a, or rpm -qi, which includes something
> that is *forced* to change the postgresql catalog view of your
> extension every time you ship a new version (major, or patch), and
> then you get the exact version (and whatever else you include) for
> free every time you update ;-)

I think it's silly for every extension to have its own function that does this. Every one would have a different name
and,perhaps, signature. 

> The thing to remember is that the postgresql "extensions" are managing
> the *postgresql catalogs* view of things, even though the shared
> object used by postgresql to provide the particular catalog's
> requirements can be "fixed".
>
> If your extension is almost exclusively a shared object, and the only
> catalog things are a couple of functions defined to point into the C
> code, there really isn't anything catalog-wise that you need to
> "manage" for upgrades.

Most of my extensions will not be written in C (e.g., pgTAP, explanation).

Best,

David

On Mon, Apr 25, 2011 at 12:17 PM, David E. Wheeler <david@kineticode.com> wrote:
> On Apr 25, 2011, at 9:14 AM, Aidan Van Dyk wrote:
>
>> Really, that means you just a sql function to your extension,
>> somethign similary to uname -a, or rpm -qi, which includes something
>> that is *forced* to change the postgresql catalog view of your
>> extension every time you ship a new version (major, or patch), and
>> then you get the exact version (and whatever else you include) for
>> free every time you update ;-)
>
> I think it's silly for every extension to have its own function that does this. Every one would have a different name
and,perhaps, signature.
 

+1.

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

Tom Lane <tgl@sss.pgh.pa.us> writes:
> If you didn't change the install script then it's not necessary to
> execute ALTER EXTENSION ... UPGRADE.  You seem to be assuming that the
> pg_extensions catalog has to reflect the bug fix level of an extension,
> but that is *not* the intention.  If it did reflect that, you'd need
> N times as many upgrade scripts, most of them identical, to deal with
> updating from different bug fix levels of the prior version.

+1 — but this discussion shows we're not exactly finished here.

> IMO it'd be better if the bug fix level was tracked outside the
> database, for instance via an RPM package version/release number.
> I'm not sure whether PGXN has anything for that at the moment.

-0.5

What I think would be useful here is to have both version and revision
in the control file and pg_extension catalog.  Then an extension can
easily be at version 1.2 and revision 1.2.3.

Now, that means that ALTER EXTENSION UPGRADE should accept to upgrade
the revision in the control file when nothing else changes.

> We've been over that in the previous discussions, please see the
> archives.  I believe the conclusion was that breaking ABI compatibility
> within an update is just not a good idea.

IOW, ABI should be tied to version, not to revision, I think.

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

On Wed, Apr 27, 2011 at 1:48 PM, Dimitri Fontaine
<dimitri@2ndquadrant.fr> wrote:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> If you didn't change the install script then it's not necessary to
>> execute ALTER EXTENSION ... UPGRADE.  You seem to be assuming that the
>> pg_extensions catalog has to reflect the bug fix level of an extension,
>> but that is *not* the intention.  If it did reflect that, you'd need
>> N times as many upgrade scripts, most of them identical, to deal with
>> updating from different bug fix levels of the prior version.
>
> +1 — but this discussion shows we're not exactly finished here.

Probably what is needed is only a clarification that the version
number is only about schema object, not revision, patch level, release
status or whatever else semantically meaningful. I've attached a patch
for the docs about the point.


>> IMO it'd be better if the bug fix level was tracked outside the
>> database, for instance via an RPM package version/release number.
>> I'm not sure whether PGXN has anything for that at the moment.
>
> -0.5
>
> What I think would be useful here is to have both version and revision
> in the control file and pg_extension catalog.  Then an extension can
> easily be at version 1.2 and revision 1.2.3.
>
> Now, that means that ALTER EXTENSION UPGRADE should accept to upgrade
> the revision in the control file when nothing else changes.

A less invasive change would be to just update the extension comment
on ALTER EXTENSION UPGRADE. This means that the revision would be just
informative and not metadata available to eventual depending code but
it's on purpose. I think that, if an extension requires its patchlevel
to be known, e.g. because depending code has to take different actions
based on the revision, it should really provide an inspection
function, such as foo_revision(), so that pre-9.1 code can work with
it as well.


-- Daniele

On Thu, Apr 28, 2011 at 4:07 PM, Daniele Varrazzo
<daniele.varrazzo@gmail.com> wrote:
> On Wed, Apr 27, 2011 at 1:48 PM, Dimitri Fontaine
> <dimitri@2ndquadrant.fr> wrote:
>> Tom Lane <tgl@sss.pgh.pa.us> writes:
>>> If you didn't change the install script then it's not necessary to
>>> execute ALTER EXTENSION ... UPGRADE.  You seem to be assuming that the
>>> pg_extensions catalog has to reflect the bug fix level of an extension,
>>> but that is *not* the intention.  If it did reflect that, you'd need
>>> N times as many upgrade scripts, most of them identical, to deal with
>>> updating from different bug fix levels of the prior version.
>>
>> +1 — but this discussion shows we're not exactly finished here.
>
> Probably what is needed is only a clarification that the version
> number is only about schema object, not revision, patch level, release
> status or whatever else semantically meaningful. I've attached a patch
> for the docs about the point.

How about each .so containing a version callback?

Thus you can show what is the version of underlying implementation
without needing to mess with catalogs just to keep track of patchlevel
of C code.

--
marko

On Thu, Apr 28, 2011 at 2:21 PM, Marko Kreen <markokr@gmail.com> wrote:
> On Thu, Apr 28, 2011 at 4:07 PM, Daniele Varrazzo
> <daniele.varrazzo@gmail.com> wrote:
>> On Wed, Apr 27, 2011 at 1:48 PM, Dimitri Fontaine
>> <dimitri@2ndquadrant.fr> wrote:
>>> Tom Lane <tgl@sss.pgh.pa.us> writes:
>>>> If you didn't change the install script then it's not necessary to
>>>> execute ALTER EXTENSION ... UPGRADE.  You seem to be assuming that the
>>>> pg_extensions catalog has to reflect the bug fix level of an extension,
>>>> but that is *not* the intention.  If it did reflect that, you'd need
>>>> N times as many upgrade scripts, most of them identical, to deal with
>>>> updating from different bug fix levels of the prior version.
>>>
>>> +1 — but this discussion shows we're not exactly finished here.
>>
>> Probably what is needed is only a clarification that the version
>> number is only about schema object, not revision, patch level, release
>> status or whatever else semantically meaningful. I've attached a patch
>> for the docs about the point.
>
> How about each .so containing a version callback?
>
> Thus you can show what is the version of underlying implementation
> without needing to mess with catalogs just to keep track of patchlevel
> of C code.

On this line, it would be easier to add a parameter "revision" to the
control file and have a function pg_revision(ext) to return it,
eventually showing in the \dx output. But this still assumes the
revision as being just a string, and if it has a semantic meaning then
it requires parsing to extract meaning for it (whereas foo_revision()
may return everything the author of foo thinks is important for code
depending on it to know, e.g. it may return an integer 90102 or a
record (major, minor, patch, status, svn-rev,
name-of-my-last-daughter). I don't think we want to force any
convention, such as the revision being a semver number - even if PGXN
restrict the extension to this strings subset.

-- Daniele

On Thu, Apr 28, 2011 at 4:40 PM, Daniele Varrazzo
<daniele.varrazzo@gmail.com> wrote:
> On Thu, Apr 28, 2011 at 2:21 PM, Marko Kreen <markokr@gmail.com> wrote:
>> On Thu, Apr 28, 2011 at 4:07 PM, Daniele Varrazzo
>> <daniele.varrazzo@gmail.com> wrote:
>>> On Wed, Apr 27, 2011 at 1:48 PM, Dimitri Fontaine
>>> <dimitri@2ndquadrant.fr> wrote:
>>>> Tom Lane <tgl@sss.pgh.pa.us> writes:
>>>>> If you didn't change the install script then it's not necessary to
>>>>> execute ALTER EXTENSION ... UPGRADE.  You seem to be assuming that the
>>>>> pg_extensions catalog has to reflect the bug fix level of an extension,
>>>>> but that is *not* the intention.  If it did reflect that, you'd need
>>>>> N times as many upgrade scripts, most of them identical, to deal with
>>>>> updating from different bug fix levels of the prior version.
>>>>
>>>> +1 — but this discussion shows we're not exactly finished here.
>>>
>>> Probably what is needed is only a clarification that the version
>>> number is only about schema object, not revision, patch level, release
>>> status or whatever else semantically meaningful. I've attached a patch
>>> for the docs about the point.
>>
>> How about each .so containing a version callback?
>>
>> Thus you can show what is the version of underlying implementation
>> without needing to mess with catalogs just to keep track of patchlevel
>> of C code.
>
> On this line, it would be easier to add a parameter "revision" to the
> control file and have a function pg_revision(ext) to return it,
> eventually showing in the \dx output. But this still assumes the
> revision as being just a string, and if it has a semantic meaning then
> it requires parsing to extract meaning for it (whereas foo_revision()
> may return everything the author of foo thinks is important for code
> depending on it to know, e.g. it may return an integer 90102 or a
> record (major, minor, patch, status, svn-rev,
> name-of-my-last-daughter). I don't think we want to force any
> convention, such as the revision being a semver number - even if PGXN
> restrict the extension to this strings subset.

Yeah, I was thinking about such convertionless patchlevel,
just for information.  Authors would use it for patchlevel,
but packages could put their version numbers there too.

Main idea would be to see the noise versions also in db,
otherwise you still need to go to OS to see whats actually
installed.

Reading it from control file seems even better solution for that,
although there is minor problem of running backend
using older .so-s than installed.  But that does not seem serious
enough to warrant a workaround.

--
marko

Daniele Varrazzo <daniele.varrazzo@gmail.com> writes:
> On Thu, Apr 28, 2011 at 2:21 PM, Marko Kreen <markokr@gmail.com> wrote:
>> How about each .so containing a version callback?
>> 
>> Thus you can show what is the version of underlying implementation
>> without needing to mess with catalogs just to keep track of patchlevel
>> of C code.

> On this line, it would be easier to add a parameter "revision" to the
> control file and have a function pg_revision(ext) to return it,
> eventually showing in the \dx output.

I think what we're discussing here is bug-fix revisions that don't
affect the SQL declarations for the extension.  Presumably, that means a
change in the C code, so the shared library is the right place to keep
the revision number.  A version number in the control file seems to
carry a nontrivial risk of being out of sync with the actual code in the
shared library.

What's not clear to me is whether to just suggest that extension authors
who care about this should provide a foo_version() function, or to try
to standardize it a bit more than that.

One point worth thinking about is that not all extensions will have
a shared library at all --- SQL-only extensions have been mentioned
several times as an important use case.  For those, there's no such
thing as an update that doesn't change the script file, and we shouldn't
try to impose a requirement of providing a lower-level revision number.
        regards, tom lane

On Thu, Apr 28, 2011 at 3:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Daniele Varrazzo <daniele.varrazzo@gmail.com> writes:
>> On Thu, Apr 28, 2011 at 2:21 PM, Marko Kreen <markokr@gmail.com> wrote:
>>> How about each .so containing a version callback?
>>>
>>> Thus you can show what is the version of underlying implementation
>>> without needing to mess with catalogs just to keep track of patchlevel
>>> of C code.
>
>> On this line, it would be easier to add a parameter "revision" to the
>> control file and have a function pg_revision(ext) to return it,
>> eventually showing in the \dx output.
>
> I think what we're discussing here is bug-fix revisions that don't
> affect the SQL declarations for the extension.  Presumably, that means a
> change in the C code, so the shared library is the right place to keep
> the revision number.  A version number in the control file seems to
> carry a nontrivial risk of being out of sync with the actual code in the
> shared library.

There is also the case of extensions whose data file matter: for
instance I've packaged the Italian text search dictionary as an
extension (http://pgxn.org/dist/italian_fts/): it contains no .so but
it may happen for the dictionary files to be changed. Its version is
1.2 and will stay so as long as the sql doesn't change, but its
revision is currently 1.2.1 and may bump to 1.2.2 should the dict
content change. For this extension, just spotting the 1.2.1 in the \dx
output would be more than enough, I don't see any use for the revision
number returned in an api call.

As long as the extension is installed via "make install" the .control
shouldn't drift away from the extension files it represents.

-- Daniele

On Apr 28, 2011, at 7:04 AM, Tom Lane wrote:

> I think what we're discussing here is bug-fix revisions that don't
> affect the SQL declarations for the extension.  Presumably, that means a
> change in the C code, so the shared library is the right place to keep
> the revision number.  A version number in the control file seems to
> carry a nontrivial risk of being out of sync with the actual code in the
> shared library.

But that's exactly where it is stored right now.

> What's not clear to me is whether to just suggest that extension authors
> who care about this should provide a foo_version() function, or to try
> to standardize it a bit more than that.

Please, if those are the choices, go with the latter. If you leave it to extension authors, they'll all have different
namesand different return types, and will thus be worthless to most folks wanting a generalized way to see what
versionsof extensions they have installed. Hell, I already regret that pgtap_version() returns NUMERIC. Which reminds
me,I might change it in a future version. Then it's *really* inconsistent, isn't it? 

> One point worth thinking about is that not all extensions will have
> a shared library at all --- SQL-only extensions have been mentioned
> several times as an important use case.  For those, there's no such
> thing as an update that doesn't change the script file, and we shouldn't
> try to impose a requirement of providing a lower-level revision number.

No, but there are new releases without code changes. I've been making releases that tweak documentation and the
Makefile(for 9.1 support) but not the code. Should the extension in this case get a new version or not? 

Look, I read this thread this morning carefully, but I have to say I don't really understand it. Considering that there
wasconsensus on not requiring any format, meaning, or mandated sort ordering of versions, there's suddenly quite a lot
ofdiscussion of the meaning and format, if not sort ordering. 

So maybe it's half-assed. Maybe the version can be anything but the revision must be an integer. Maybe there's a
`pg_extension_version($extension_name)`function that returns ARRAY[$version, $revision], and the revision is set in the
controlfile but not included in the version or in the upgrade file names. I think I can live with that. But, hell,
you'rehalfway to mandating the meaning by doing this. Will we have to go the rest of the way in the future? 

Best,

David

Hey folks,

I'd kind of like to get this issue nailed down soon so I can update the PGXN HOWTO and illustrate a generally
agreed-uponbest practice for extension developers. How *do* we want people to use versions in their extension? 

Thanks,

David

On Apr 28, 2011, at 2:16 PM, David E. Wheeler wrote:

> On Apr 28, 2011, at 7:04 AM, Tom Lane wrote:
>
>> I think what we're discussing here is bug-fix revisions that don't
>> affect the SQL declarations for the extension.  Presumably, that means a
>> change in the C code, so the shared library is the right place to keep
>> the revision number.  A version number in the control file seems to
>> carry a nontrivial risk of being out of sync with the actual code in the
>> shared library.
>
> But that's exactly where it is stored right now.
>
>> What's not clear to me is whether to just suggest that extension authors
>> who care about this should provide a foo_version() function, or to try
>> to standardize it a bit more than that.
>
> Please, if those are the choices, go with the latter. If you leave it to extension authors, they'll all have
differentnames and different return types, and will thus be worthless to most folks wanting a generalized way to see
whatversions of extensions they have installed. Hell, I already regret that pgtap_version() returns NUMERIC. Which
remindsme, I might change it in a future version. Then it's *really* inconsistent, isn't it? 
>
>> One point worth thinking about is that not all extensions will have
>> a shared library at all --- SQL-only extensions have been mentioned
>> several times as an important use case.  For those, there's no such
>> thing as an update that doesn't change the script file, and we shouldn't
>> try to impose a requirement of providing a lower-level revision number.
>
> No, but there are new releases without code changes. I've been making releases that tweak documentation and the
Makefile(for 9.1 support) but not the code. Should the extension in this case get a new version or not? 
>
> Look, I read this thread this morning carefully, but I have to say I don't really understand it. Considering that
therewas consensus on not requiring any format, meaning, or mandated sort ordering of versions, there's suddenly quite
alot of discussion of the meaning and format, if not sort ordering. 
>
> So maybe it's half-assed. Maybe the version can be anything but the revision must be an integer. Maybe there's a
`pg_extension_version($extension_name)`function that returns ARRAY[$version, $revision], and the revision is set in the
controlfile but not included in the version or in the upgrade file names. I think I can live with that. But, hell,
you'rehalfway to mandating the meaning by doing this. Will we have to go the rest of the way in the future? 
>
> Best,
>
> David
>
>
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

On Apr 28, 2011, at 2:16 PM, David E. Wheeler wrote:

> So maybe it's half-assed. Maybe the version can be anything but the revision must be an integer. Maybe there's a
`pg_extension_version($extension_name)`function that returns ARRAY[$version, $revision], and the revision is set in the
controlfile but not included in the version or in the upgrade file names. I think I can live with that. But, hell,
you'rehalfway to mandating the meaning by doing this. Will we have to go the rest of the way in the future? 

Okay, how we add a "revision" key to the control file and extrevision to the pg_extension catalog. Its type can be
"TEXT"and is optional for use by extensions. 

This would allow extension authors to identify the base version of an extension but also the revision. And the core
doesn'thave to care how it works or if it's used, but it would allow users to know exactly what they have installed. 

Thoughts?

Best,

David

On Wed, May 11, 2011 at 5:06 PM, David E. Wheeler <david@kineticode.com> wrote:
> On Apr 28, 2011, at 2:16 PM, David E. Wheeler wrote:
>
>> So maybe it's half-assed. Maybe the version can be anything but the revision must be an integer. Maybe there's a
`pg_extension_version($extension_name)`function that returns ARRAY[$version, $revision], and the revision is set in the
controlfile but not included in the version or in the upgrade file names. I think I can live with that. But, hell,
you'rehalfway to mandating the meaning by doing this. Will we have to go the rest of the way in the future? 
>
> Okay, how we add a "revision" key to the control file and extrevision to the pg_extension catalog. Its type can be
"TEXT"and is optional for use by extensions. 
>
> This would allow extension authors to identify the base version of an extension but also the revision. And the core
doesn'thave to care how it works or if it's used, but it would allow users to know exactly what they have installed. 
>
> Thoughts?

How would pg_extension.extrevision be kept up to date?  AFAICS, the
whole point is that you might swap out the shared libraries without
doing anything at the SQL level.

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

On May 11, 2011, at 2:47 PM, Robert Haas wrote:

>> Okay, how we add a "revision" key to the control file and extrevision to the pg_extension catalog. Its type can be
"TEXT"and is optional for use by extensions. 
>>
>> This would allow extension authors to identify the base version of an extension but also the revision. And the core
doesn'thave to care how it works or if it's used, but it would allow users to know exactly what they have installed. 
>>
>> Thoughts?
>
> How would pg_extension.extrevision be kept up to date?  AFAICS, the
> whole point is that you might swap out the shared libraries without
> doing anything at the SQL level.

Bah! Okay, I give up. I'll not worry about it right now, as I have only one C extension outside of core and it won't
changemuch in the code. And I'll just keep using the full version string (x.y.z) for the upgrade scripts. What I won't
dois change that version with every release, unless there is a code change to demand it. The distribution version can
incrementindependently. 

Best,

David

On Wed, May 11, 2011 at 10:48 PM, David E. Wheeler <david@kineticode.com> wrote:
> On May 11, 2011, at 2:47 PM, Robert Haas wrote:
>
>>> Okay, how we add a "revision" key to the control file and extrevision to the pg_extension catalog. Its type can be
"TEXT"and is optional for use by extensions. 
>>>
>>> This would allow extension authors to identify the base version of an extension but also the revision. And the core
doesn'thave to care how it works or if it's used, but it would allow users to know exactly what they have installed. 
>>>
>>> Thoughts?
>>
>> How would pg_extension.extrevision be kept up to date?  AFAICS, the
>> whole point is that you might swap out the shared libraries without
>> doing anything at the SQL level.
>
> Bah! Okay, I give up. I'll not worry about it right now, as I have only one C extension outside of core and it won't
changemuch in the code. And I'll just keep using the full version string (x.y.z) for the upgrade scripts. What I won't
dois change that version with every release, unless there is a code change to demand it. The distribution version can
incrementindependently. 

What might work is to have the view call some function
pg_get_the_revision_from_the_control_file_or_some_other_place_in_the_filesystem('extension-name').

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

Robert Haas <robertmhaas@gmail.com> writes:
>> Okay, how we add a "revision" key to the control file and extrevision to
>> the pg_extension catalog. Its type can be "TEXT" and is optional for use
>> by extensions.
>
> How would pg_extension.extrevision be kept up to date?  AFAICS, the
> whole point is that you might swap out the shared libraries without
> doing anything at the SQL level.

Well, ALTER EXTENSION UPDATE could be taught to check for control file
changes only when there's no upgrade script.  I think it already did
that for some fields, like require and comment, but it's no longer the
case.

Still, I would think that it should be possible to update some metadata
of the extension without running an SQL upgrade script.

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

On Thu, May 12, 2011 at 3:29 AM, Dimitri Fontaine
<dimitri@2ndquadrant.fr> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>>> Okay, how we add a "revision" key to the control file and extrevision to
>>> the pg_extension catalog. Its type can be "TEXT" and is optional for use
>>> by extensions.
>>
>> How would pg_extension.extrevision be kept up to date?  AFAICS, the
>> whole point is that you might swap out the shared libraries without
>> doing anything at the SQL level.
>
> Well, ALTER EXTENSION UPDATE could be taught to check for control file
> changes only when there's no upgrade script.  I think it already did
> that for some fields, like require and comment, but it's no longer the
> case.
>
> Still, I would think that it should be possible to update some metadata
> of the extension without running an SQL upgrade script.

The point is that something like RPM is not going to run *any* SQL
command.  It's just going to replace the files in the filesystem.  If
the view can pull that info from some file on the fly, then it can be
pretty much guaranteed to be up-to-date and accurate.  Anything else
seems hit or miss.

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

On May 17, 2011, at 9:44 AM, Peter van Hardenberg wrote:

> My apologies for wading in out of the blue here as a first time poster with big demands, but allow me to briefly
statemy hopes without trying to be too proscriptive about particular mechanisms.  

You are not alone, I assure you. :-)

> My hope here is that the extension model should eventually enable me to offer the ability for non-superuser databases
tospecify by some mechanism the extensions that they require in a reproducible fashion, enabling my users to recreate
theirlocal development conditions on a production cluster. 

Yeah. Right now I believe this can only be done for extension that don't require a super user. And IIRC, all C-based
extensionsrequire a super user. 

> My particular worry, and I apologize if I have misunderstood the thrust of this thread, is that "extension version"
mightnot be tied to the "extension revision", and so I will not be able to determine whether or not all existing
extensionsare already at a specific version. 

Well, nothing has happened in that regard. It's too late for 9.1, and there wasn't consensus, anyway. So right now, the
installedextension version is the installed extension version. There is, however, no indication of any meaning or order
toversions. They're just strings of text. 

> The precision of this process is very important to me. My intended use case for this feature is to allow users to
specifythe versions of extensions that they need in some kind of a control file or in a database migration script such
thatthey can then install those extensions on various new systems in a reliable and reproducible way. 

This is do-able.

> David, if you do what you propose, haven't I already lost?

No. I was suggesting that there be some sort of function, pg_extension_version($ext_name), that would return the
versionand the revision. Combined they would equal the version you're interested in. I'm not thrilled with this
approach,though, and it's not there, so for now we have the wild west of versions.  

So for now, what you want (modulo permissions issues) is what's there, IIUC.

Best,

David