Extensions, patch v16

Hi,

Please find attached revision 16 of the extension patch, with the
following additions over the previous one:

- added documentation as proposed by David Wheeler, you can browse it
online if reading SGML ain't your thing

http://pgsql.tapoueh.org/extensions/doc/html/extend-extension.html

- renamed pg_extensions system view to pg_available_extensions

- add ALTER EXTENSION SET SCHEMA command

so that I'm going to mark the patch for that as rejected, where it's
in fact deprecated: it turned out we already need it as part of the
main patch here

- add support for 'relocatable' boolean property in the control file,
as discussed on list

this controls what happens at create extension time, by doing a
relocation of the extension objects when the extension is relocatable
and the asked schema isn't the first of the search_path (you can't
relocate an object to a schema where it already is)

when the extension is not relocatable, the mechanism used is the
@extschema@ replacement in the script so that the user still has a
say, but at create time only

- adapt all contrib

the SET search_path TO public, which became TO @extschema@, is now
removed

2 contribs are not relocatable: adminpack installs its functions
directly into pg_catalog and earthdistance depends on cube.

As we said we won't support extension dependencies in the first cut,
there's no guard in the dependency recursion that would allow us not
to relocate cube objects when relocating earthdistance. The easiest
solution seemed to me to mark the earthdistance extension as not
relocatable.

- nothing is done for the psql commands \dx and \dx+, here's an idea:

\dx lists only installed extensions
\dx+ <extension> lists the objects, calling pg_extension_objects()
\dX lists available extensions (and installed too)

- we still depend on extension authors providing a control file. Do we
want to spend some efforts on trying to get rid of this file? I know
David desperately want to, but that's at the cost of making it much
harder to manage more than one extension in a single directory, for
once, and the Makefile mechanisms to make than happen (include a rule
depending on the presence of some variables, keep track of it for the
cleaning, etc) doesn't seem to me to worth it.

- this patch still includes the current version of pg_execute_from_file
patch, and the next thing I'm going to do is have it change
underneath us to remove some SQL visible functions that shouldn't
exists. Please don't focus on that part of the changes.

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

On Dec 9, 2010, at 12:34 PM, Dimitri Fontaine wrote:

> - add support for 'relocatable' boolean property in the control file,
> as discussed on list
>
> this controls what happens at create extension time, by doing a
> relocation of the extension objects when the extension is relocatable
> and the asked schema isn't the first of the search_path (you can't
> relocate an object to a schema where it already is)
>
> when the extension is not relocatable, the mechanism used is the
> @extschema@ replacement in the script so that the user still has a
> say, but at create time only

This still isn't ideal, but I think it's a big improvement. Thanks.

> - nothing is done for the psql commands \dx and \dx+, here's an idea:
>
> \dx lists only installed extensions
> \dx+ <extension> lists the objects, calling pg_extension_objects()
> \dX lists available extensions (and installed too)

+1 I think that's much more like existing psql commands.

> - we still depend on extension authors providing a control file. Do we
> want to spend some efforts on trying to get rid of this file? I know
> David desperately want to, but that's at the cost of making it much
> harder to manage more than one extension in a single directory, for
> once, and the Makefile mechanisms to make than happen (include a rule
> depending on the presence of some variables, keep track of it for the
> cleaning, etc) doesn't seem to me to worth it.

I don't think it makes it any harder to manage multiple extension in a single directory because one can create the control file explicitly (or perhaps rely on .control.in for that), just as they do now. Everyone else can do less work.

So:

* If $extension.control.in exists, use that
* If it doesn't, generate $extension.control from the Makefile variables
* Always remove $extension.control in the `clean` targets

Best,

David

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> On Dec 9, 2010, at 12:34 PM, Dimitri Fontaine wrote:
>> - add support for 'relocatable' boolean property in the control file,
>> as discussed on list
>
> This still isn't ideal, but I think it's a big improvement. Thanks.

Glad you like it :) If you see any way to manage that better, please do
tell. Just be sure to review the past 18 months of on-list discussion
about the topic before to go thinking extension vs search_path is easy
to solve, or even possible to solve.

>> \dx lists only installed extensions
>> \dx+ <extension> lists the objects, calling pg_extension_objects()
>> \dX lists available extensions (and installed too)
>
> +1 I think that's much more like existing psql commands.

Good, I'll have that in the next patch version, waiting for until your
review of the new one :)

> So:
>
> * If $extension.control.in exists, use that
> * If it doesn't, generate $extension.control from the Makefile variables

What if $extension.control exists? Is it a byproduct of the .in file
from previous `make` run or a user file? What if we have both the .in
and the make variable because people are confused? Or both the make
variables and a .control and not .control.in? Etc...

> * Always remove $extension.control in the `clean` targets

Hell no, as you can bypass the .in mechanism and provide directly the
.control file.

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

Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
> "David E. Wheeler" <david(at)kineticode(dot)com> writes:
>>> What if $extension.control exists? Is it a byproduct of the .in file
>>> from previous `make` run or a user file? What if we have both the .in
>>> and the make variable because people are confused? Or both the make
>>> variables and a .control and not .control.in? Etc...

>> * Always remove $extension.control in the `clean` targets

> Hell no, as you can bypass the .in mechanism and provide directly the
> .control file.

Are there any actual remaining use-cases for that sed step? It's
certainly vestigial as far as the contrib modules are concerned:
it would be simpler and more readable to replace MODULE_PATHNAME with
$libdir in the sources. Unless somebody can point to a real-world
use-case, I'd just as soon get rid of the .in files altogether while
we're having this flag day.

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> Are there any actual remaining use-cases for that sed step?

The goal here is to allow extension authors to maintain their version
number in the Makefile rather than in the Makefile and in the control
file separately. Having the same version number in more than one place
never eases maintenance.

Oh and in PostgreSQL sources cases, that would add like 36 spots where
to manually maintain our major version string. I'm not eager to do that.

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

Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> Are there any actual remaining use-cases for that sed step?

> The goal here is to allow extension authors to maintain their version
> number in the Makefile rather than in the Makefile and in the control
> file separately. Having the same version number in more than one place
> never eases maintenance.

Why is it in the makefile at all? If the makefile does need to know it,
why don't we have it scrape the number out of the control file? Or even
more to the point, since when do we need version numbers in extensions?

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> Why is it in the makefile at all? If the makefile does need to know it,
> why don't we have it scrape the number out of the control file? Or even
> more to the point, since when do we need version numbers in extensions?

It's in the Makefile so that you find it in the control file later, then
in the extension catalog. We need the version number just because I'm
not able to name a single software that's not letting you know about its
version number once installed.

Well in fact I know about one, and I wish the situation would be quite
different there.

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

Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> Why is it in the makefile at all? If the makefile does need to know it,
>> why don't we have it scrape the number out of the control file? Or even
>> more to the point, since when do we need version numbers in extensions?

> It's in the Makefile so that you find it in the control file later, then
> in the extension catalog.

This doesn't answer my question of why it couldn't be done the other
way. Why does the makefile need to know it? If it does need to know
it, couldn't it get it out of the control file instead of vice versa?

> We need the version number just because I'm
> not able to name a single software that's not letting you know about its
> version number once installed.

I'm not convinced that this is actually a requirement, or that doing it
this specific way is a good solution. In particular, keeping the
version number in the system catalogs seems pretty dubious. The common
method for upgrading an already-installed contrib module just involves
dropping in a new .so --- that's not going to change the system
catalogs. It would likely be better to keep the version ID inside the
.so file.

regards, tom lane

On Dec 10, 2010, at 12:26 AM, Dimitri Fontaine wrote:

> What if $extension.control exists? Is it a byproduct of the .in file
> from previous `make` run or a user file? What if we have both the .in
> and the make variable because people are confused? Or both the make
> variables and a .control and not .control.in? Etc...

There are ways to deal with those issue, I'm sure.

>> * Always remove $extension.control in the `clean` targets
>
> Hell no, as you can bypass the .in mechanism and provide directly the
> .control file.

I'm saying disallow the .control file, only allow the control.in file.

David

On Dec 10, 2010, at 7:32 AM, Tom Lane wrote:

> Are there any actual remaining use-cases for that sed step? It's
> certainly vestigial as far as the contrib modules are concerned:
> it would be simpler and more readable to replace MODULE_PATHNAME with
> $libdir in the sources. Unless somebody can point to a real-world
> use-case, I'd just as soon get rid of the .in files altogether while
> we're having this flag day.

I've made extensive use of them in pgTAP, but they don't depend on PGXS's doing its bit. So no. Unless we require control.in and not .control.

Best,

David

On Fri, Dec 10, 2010 at 12:30 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I'm not convinced that this is actually a requirement, or that doing it
> this specific way is a good solution.  In particular, keeping the
> version number in the system catalogs seems pretty dubious.  The common
> method for upgrading an already-installed contrib module just involves
> dropping in a new .so --- that's not going to change the system
> catalogs.  It would likely be better to keep the version ID inside the
> .so file.

This is an interesting point. There are really two things here: the
.so version, and the version of the system catalog entries. For
example, imagine that an extension provides a single function, called
foo(). So we load up the .so and CREATE FUNCTION statement to match.
Later, the extension is so successful that the author writes a second
function, bar(). The new .so can (at least possibly) be used with the
old schema definitions, but the new schema definitions aren't
compatible with the old .so. The logical upgrade process is to swap
out the .so first, and then add update the catalog definitions.

On the other hand, if you were dropping a deprecated function, you'd
need to do the steps in reverse order.

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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Fri, Dec 10, 2010 at 12:30 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> ... In particular, keeping the
>> version number in the system catalogs seems pretty dubious. The common
>> method for upgrading an already-installed contrib module just involves
>> dropping in a new .so --- that's not going to change the system
>> catalogs. It would likely be better to keep the version ID inside the
>> .so file.

> This is an interesting point. There are really two things here: the
> .so version, and the version of the system catalog entries.

True. Consider a situation like an RPM upgrade: it's going to drop in a
new .so version, *and nothing else*. It's pure fantasy to imagine that
the RPM script is going to find all your databases and execute some SQL
commands against them. Since a large number of bug-fix cases do require
only a .so update, not being able to track the .so version seems like
it's missing most of the argument for having version tracking at all.

(In the RPM case, the RPM infrastructure would be able to tell you
which version you had installed, so I'm not sold that PG needs to
duplicate that.)

regards, tom lane

On Dec 10, 2010, at 10:20 AM, Tom Lane wrote:

> True. Consider a situation like an RPM upgrade: it's going to drop in a
> new .so version, *and nothing else*. It's pure fantasy to imagine that
> the RPM script is going to find all your databases and execute some SQL
> commands against them. Since a large number of bug-fix cases do require
> only a .so update, not being able to track the .so version seems like
> it's missing most of the argument for having version tracking at all.

Sometimes there will be changes to the SQL, too. How does that work with CREATE EXTENSION? Do I install the upgrade, then run CREATE EXTENSION to get the latest SQL script to run? But then all the objects already exist…

Best,

David

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> This doesn't answer my question of why it couldn't be done the other
> way. Why does the makefile need to know it? If it does need to know
> it, couldn't it get it out of the control file instead of vice versa?

Well the Makefile support is just a facility to fill in the control file
automatically for you, on the grounds that you're probably already
maintaining your version number in the Makefile. Or that it's easy to
get it there, as in:

EXTVERSION = $(shell dpkg-parsechangelog | awk -F '[:-]' '/^Version:/ { print substr($$2, 2) }')

That comes from a real world example that's yet to be adapted to being
an extension in 9.1, but still:

https://github.com/dimitri/pgfincore/blob/debian/Makefile

> I'm not convinced that this is actually a requirement, or that doing it
> this specific way is a good solution. In particular, keeping the
> version number in the system catalogs seems pretty dubious. The common
> method for upgrading an already-installed contrib module just involves
> dropping in a new .so --- that's not going to change the system
> catalogs. It would likely be better to keep the version ID inside the
> .so file.

Upgrade are left for a future patch, did we decide. Still, it seems to
me that we will support some upgrade scripts so that author can decide
what to do knowing current and next version, and yes, knowing that the
module has already been taken care of by the OS-level packaging.

That means some extensions upgrades will break the database between the
OS-level package upgrade and the sql upgrade (support to come), but in
my experience that's seldom the case. And not by chance.

So in the case that only the module (.so) needs upgrading, we would
still provide for an upgrade path in the script / sql support so that
the version number has a chance of being upgraded too. As you say in
another mail, of course, the OS packaging system will not forcibly be
willing to care for that all by itself. I can imagine debian offering
the choice to the users and acting accordingly, though.

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

On Dec 10, 2010, at 11:28 AM, Dimitri Fontaine wrote:

> Well the Makefile support is just a facility to fill in the control file
> automatically for you, on the grounds that you're probably already
> maintaining your version number in the Makefile. Or that it's easy to
> get it there, as in:
>
> EXTVERSION = $(shell dpkg-parsechangelog | awk -F '[:-]' '/^Version:/ { print substr($$2, 2) }')
>
> That comes from a real world example that's yet to be adapted to being
> an extension in 9.1, but still:
>
> https://github.com/dimitri/pgfincore/blob/debian/Makefile

I use that in pgTAP, too (line 23):

https://github.com/theory/pgtap/blob/master/Makefile

But I don't need core to support that. Frankly, if we're not going to generate the control file from Makefile variables, then I'd rather not have any control file Makefile variables at all.

> Upgrade are left for a future patch, did we decide. Still, it seems to
> me that we will support some upgrade scripts so that author can decide
> what to do knowing current and next version, and yes, knowing that the
> module has already been taken care of by the OS-level packaging.

Yeah, this will be needed ASAP.

Best,

David

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> On Dec 10, 2010, at 11:28 AM, Dimitri Fontaine wrote:
>> Upgrade are left for a future patch, did we decide. Still, it seems to
>> me that we will support some upgrade scripts so that author can decide
>> what to do knowing current and next version, and yes, knowing that the
>> module has already been taken care of by the OS-level packaging.

> Yeah, this will be needed ASAP.

I don't mind if we don't have an implementation of upgrade cases in
hand. But we had better have a design in hand, to make sure what we're
doing now doesn't foreclose upgrade cases.

regards, tom lane

Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> This doesn't answer my question of why it couldn't be done the other
>> way. Why does the makefile need to know it? If it does need to know
>> it, couldn't it get it out of the control file instead of vice versa?

> Well the Makefile support is just a facility to fill in the control file
> automatically for you, on the grounds that you're probably already
> maintaining your version number in the Makefile.

Why would you choose to maintain it in the Makefile? In most cases
makefiles are the least likely thing to be changing during a minor
update. I would think that the right place for it is in the C code
(if we're trying to version .so files) or the .sql file, if we're trying
to version the SQL objects. In particular, if the only reason the
makefile needs to know it is to inject it into the control file, it
seems completely silly to not just maintain it in the control file
instead.

regards, tom lane

On Dec 10, 2010, at 11:47 AM, Tom Lane wrote:

> Why would you choose to maintain it in the Makefile? In most cases
> makefiles are the least likely thing to be changing during a minor
> update. I would think that the right place for it is in the C code
> (if we're trying to version .so files) or the .sql file, if we're trying
> to version the SQL objects. In particular, if the only reason the
> makefile needs to know it is to inject it into the control file, it
> seems completely silly to not just maintain it in the control file
> instead.

+1

David

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> Why would you choose to maintain it in the Makefile? In most cases
> makefiles are the least likely thing to be changing during a minor
> update.

I must have a packager skewed view of things here, but ok, point noted.

> I would think that the right place for it is in the C code
> (if we're trying to version .so files) or the .sql file, if we're trying
> to version the SQL objects. In particular, if the only reason the
> makefile needs to know it is to inject it into the control file, it
> seems completely silly to not just maintain it in the control file
> instead.

If we are after the a bare feature set, sure, the Makefile facility is
only about trying to be nice to the user. I accept that you think it's
counter productive rather than helping.

In the next patch, I will rename the control files to be straight
.control files, remove the implicit rule in the pgxs.mk file, remove the
note in the documentation and apply the following:

sed -i 's/EXTVERSION/9.1devel/' contrib/*/*.control

Or do we want contrib's specific version numbers that are not all the
same as the current PostgreSQL version number?

On to your question about the upgrade design, in order not to paint
ourselves into a corner. What I now have in mind is the following:

When there's an extension upgrade the user will have to install the new
files (.so, .sql, .control) and run an upgrade command in his databases:

ALTER EXTENSION pair UPGRADE;

The version we upgrade from is known from the catalog, the version we
upgrade to is read in the control file. So we are able to call the sql
script and offer a way for it to know about the versions. The simplest
way seems to be a new pair of functions:

pg_extension_upgrade() returns bool
pg_extension_versions() returns table(current text, next text)

Those are to be run only from the extension's script.

The first returns false when the user did CREATE EXTENSION and true when
the user did ALTER EXTENSION UPGRADE, which are the only two commands
that will run the script.

The second will return the versions we detailed above, and the
extension's author is free to compare them however he wants to and
decide what to do now. It's cool that we have DO blocks here, and
pg_execute_sql_file() to offer the same facility as \i for psql scripts.

Of course if calling the script succeeds, then the version number in the
pg_extension catalog is changed to the "next" one.

Now, it would be better if it were easy to compare version numbers, for
example with a -core datatype that handles that. Do we already want to
open this can of worms?

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

On 12/10/10 12:17 PM, Dimitri Fontaine wrote:
> Or do we want contrib's specific version numbers that are not all the
> same as the current PostgreSQL version number?

I think that each contrib needs its own version numbers. The reason
being that most minor updates don't touch contrib.

Also, once extensions and pgxn are operating full swing, I see contrib
going away anyway ...

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

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> On 12/10/10 12:17 PM, Dimitri Fontaine wrote:
>> Or do we want contrib's specific version numbers that are not all the
>> same as the current PostgreSQL version number?

> I think that each contrib needs its own version numbers. The reason
> being that most minor updates don't touch contrib.

Certainly extensions that aren't part of contrib would need separate
version numbers.

regards, tom lane

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> I think that each contrib needs its own version numbers. The reason
> being that most minor updates don't touch contrib.

Fair enough. What are the version numbers of each current contribs?

> Also, once extensions and pgxn are operating full swing, I see contrib
> going away anyway ...

No, not all of them. Most of them are in the tree as show cases or for
core developers to easily check they just didn't break an important part
of the system from an external viewpoint, or to give examples on how to
upgrade external extension code between major releases.

The part that will drop in interest is the one where customers are not
trusting the extension mechanism and third-party software enough to
grant them landing into their production environments. Maybe. Given some
years and a good track record.

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

On 12/10/2010 03:24 PM, Josh Berkus wrote:
>
> Also, once extensions and pgxn are operating full swing, I see contrib
> going away anyway ...

We've heard this before, but I'm still quite skeptical about it. Quite
apart from anything else we should keep enough extensions in core to
test the extension mechanism, as well as to provide examples as part of
the base distribution. Some (e.g. hstore and citext) should probably
move into core. Others like pgcrypto are probably in just the right
place as they are.

cheers

andrew

On Fri, 2010-12-10 at 15:42 -0500, Andrew Dunstan wrote:
>
> On 12/10/2010 03:24 PM, Josh Berkus wrote:
> >
> > Also, once extensions and pgxn are operating full swing, I see contrib
> > going away anyway ...
>
> We've heard this before, but I'm still quite skeptical about it. Quite
> apart from anything else we should keep enough extensions in core to
> test the extension mechanism, as well as to provide examples as part of
> the base distribution. Some (e.g. hstore and citext) should probably
> move into core. Others like pgcrypto are probably in just the right
> place as they are.

I hope that contrib goes away. I agree with your assertion that things
like hstore and citext shoudl be in core but it is my hope that with
extensions and pgxn, there will be no reason for contrib to exist at
all.

JD

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
> On to your question about the upgrade design, in order not to paint
> ourselves into a corner. What I now have in mind is the following:

> When there's an extension upgrade the user will have to install the new
> files (.so, .sql, .control) and run an upgrade command in his databases:

> ALTER EXTENSION pair UPGRADE;

> The version we upgrade from is known from the catalog, the version we
> upgrade to is read in the control file. So we are able to call the sql
> script and offer a way for it to know about the versions. The simplest
> way seems to be a new pair of functions:

> pg_extension_upgrade() returns bool
> pg_extension_versions() returns table(current text, next text)

> Those are to be run only from the extension's script.

> The first returns false when the user did CREATE EXTENSION and true when
> the user did ALTER EXTENSION UPGRADE, which are the only two commands
> that will run the script.

> The second will return the versions we detailed above, and the
> extension's author is free to compare them however he wants to and
> decide what to do now. It's cool that we have DO blocks here, and
> pg_execute_sql_file() to offer the same facility as \i for psql scripts.

Hmm ...

I don't believe that extension SQL scripts should rely on DO blocks.
There is no requirement that plpgsql be installed, and we're not going
to create one as part of this feature. What this means is that the
design you offer above doesn't work at all, since it fundamentally
assumes that the SQL script can do conditional logic. What's more,
it fundamentally assumes that the script WILL do conditional logic
and support (in one lump) every possible combination of versions.
That's going to turn into buggy spaghetti-code very quickly.

I think that something that could work is more along the lines of the
extension containing different upgrade scripts for whatever set of cases
the author feels like supporting; for example the foo extension might
provide both
foo_upgrade.11.13.sql
foo_upgrade.12.13.sql
if the author is willing to support one-step upgrades from two preceding
versions to version 13. It would then be the responsibility of the
ALTER EXTENSION code to select and execute the correct upgrade script.
A missing script would be reported as an upgrade failure by ALTER
EXTENSION.

(Actually, we could probably assume that the target version is
implicitly "the current version", as identified from the control file,
and omit that from the script file names. That would avoid ambiguity
if version numbers can have more than one part.)

regards, tom lane

"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> On Fri, 2010-12-10 at 15:42 -0500, Andrew Dunstan wrote:
>> On 12/10/2010 03:24 PM, Josh Berkus wrote:
>>> Also, once extensions and pgxn are operating full swing, I see contrib
>>> going away anyway ...

>> We've heard this before, but I'm still quite skeptical about it. Quite
>> apart from anything else we should keep enough extensions in core to
>> test the extension mechanism, as well as to provide examples as part of
>> the base distribution. Some (e.g. hstore and citext) should probably
>> move into core. Others like pgcrypto are probably in just the right
>> place as they are.

> I hope that contrib goes away. I agree with your assertion that things
> like hstore and citext shoudl be in core but it is my hope that with
> extensions and pgxn, there will be no reason for contrib to exist at
> all.

I agree with Andrew --- we're going to need a collection of "standard
extensions" if only for testing purposes. It may someday not be called
contrib, but it'll still be there.

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> I don't believe that extension SQL scripts should rely on DO blocks.
> There is no requirement that plpgsql be installed, and we're not going
> to create one as part of this feature. What this means is that the
> design you offer above doesn't work at all, since it fundamentally
> assumes that the SQL script can do conditional logic. What's more,
> it fundamentally assumes that the script WILL do conditional logic
> and support (in one lump) every possible combination of versions.
> That's going to turn into buggy spaghetti-code very quickly.

Yeah, I was picturing a main script that calls other ones by means of
PERFORM pg_execute_from_file('upgrade_script.sql');

Of course if plpgsql is not to be a requirement, the DO blocks hosting
the CASE logic won't fly and all that blows away.

> I think that something that could work is more along the lines of the
> extension containing different upgrade scripts for whatever set of cases
> the author feels like supporting; for example the foo extension might
> provide both
> foo_upgrade.11.13.sql
> foo_upgrade.12.13.sql
> if the author is willing to support one-step upgrades from two preceding
> versions to version 13. It would then be the responsibility of the
> ALTER EXTENSION code to select and execute the correct upgrade script.
> A missing script would be reported as an upgrade failure by ALTER
> EXTENSION.
>
> (Actually, we could probably assume that the target version is
> implicitly "the current version", as identified from the control file,
> and omit that from the script file names. That would avoid ambiguity
> if version numbers can have more than one part.)

I don't think we can safely design around one part version numbers here,
because I'm yet to see that happening in any extension I've had my hands
on, which means a few already, as you can imagine.

Now, what about having the control file host an 'upgrade' property where
to put the script name? We would have to support a way for this filename
to depend on the already installed version, I'm thinking that %v might
be the easiest here (read: I want to avoid depending on any version
scheme).

version = '13'
script = 'foo.sql'
upgrade = 'foo_upgrade.%v.13.sql'

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

On 12/10/10 12:34 PM, Dimitri Fontaine wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> I think that each contrib needs its own version numbers. The reason
>> being that most minor updates don't touch contrib.
>
> Fair enough. What are the version numbers of each current contribs?

I'd say that for anything in /contrib, it gets a new version with each
major version of postgresql, but not with each minor version. Thus,
say, dblink when 9.1.0 is release would be dblink 9.1-1. If in 9.1.4 we
fix a bug in dblink, then it becomes dblink 9.1-2.

This is confusing from a version number perpsective, but it prevents
admins from having to run extension upgrades when nothing has changed.

The alternative would be to match postgresql minor version numbering
exactly, and then come up with some way to have a "no-op" upgrade in the
frequent cases where the contrib module isn't changed during a minor
release. This would also require some kind of "upgrade all" command for
contrib.

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

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> The alternative would be to match postgresql minor version numbering
> exactly, and then come up with some way to have a "no-op" upgrade in the
> frequent cases where the contrib module isn't changed during a minor
> release. This would also require some kind of "upgrade all" command for
> contrib.

That's as easy as having non-continuous version numbering. In your
example, we get from dblink version 9.1.0 to 9.1.4, but the 3 releases
before that it remains dblink 9.1.0.

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

On Fri, Dec 10, 2010 at 4:50 PM, Dimitri Fontaine
<dimitri(at)2ndquadrant(dot)fr> wrote:

> Now, what about having the control file host an 'upgrade' property where
> to put the script name? We would have to support a way for this filename
> to depend on the already installed version, I'm thinking that %v might
> be the easiest here (read: I want to avoid depending on any version
> scheme).
>
>  version = '13'
>  script  = 'foo.sql'
>  upgrade = 'foo_upgrade.%v.13.sql'

If I was linking of putting bundling my "utiliites" up as an extension
(yes, I would that from a packaging/DB management perspective), I
think I'ld like a control like that, but with a bit of a "wildcard"
version matching, something like:
version = '3.12'
upgrade-1. = 'utils-upgrade-1.0.sql'
upgrade-2. = 'utils-upgrade-2..0.sql
upgrade-3. = 'nothing'

I'm thinking of a scheme where the upgrade-$VERSION uses a prefix
match, so 1.1, 1.2, 1.3 would all be matched by "1.". The 3.=nothing
is some way of specifing you don't need to do anything, becuase my n.X
release are all compatible sql->so wise. They would only be "bug
fixes" if I did something wrong in my stuff.. Anything not compatible
woudl bump the first number.

If it's a "prefix" type match, then the PG versionins woudl work too,
for intsance:
upgrade-9.0.=...
would match any pg 9.0.*

I guess you could use SQL like if that' more "consitent"...

a.

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

On Dec 10, 2010, at 1:55 PM, Josh Berkus wrote:

> I'd say that for anything in /contrib, it gets a new version with each
> major version of postgresql, but not with each minor version. Thus,
> say, dblink when 9.1.0 is release would be dblink 9.1-1. If in 9.1.4 we
> fix a bug in dblink, then it becomes dblink 9.1-2.

Please don't add "-" to version numbers.

> This is confusing from a version number perpsective, but it prevents
> admins from having to run extension upgrades when nothing has changed.
>
> The alternative would be to match postgresql minor version numbering
> exactly, and then come up with some way to have a "no-op" upgrade in the
> frequent cases where the contrib module isn't changed during a minor
> release. This would also require some kind of "upgrade all" command for
> contrib.

+1

David

On Dec 10, 2010, at 1:50 PM, Dimitri Fontaine wrote:

>> (Actually, we could probably assume that the target version is
>> implicitly "the current version", as identified from the control file,
>> and omit that from the script file names. That would avoid ambiguity
>> if version numbers can have more than one part.)
>
> I don't think we can safely design around one part version numbers here,
> because I'm yet to see that happening in any extension I've had my hands
> on, which means a few already, as you can imagine.

Why not? Simplest thing, to my mind, is to have

upgrade/foo-1.12.sql
upgrade/foo-1.13.sql
upgrade/foo-1.15.sql

Since you know the existing version number, you just run all that come after. For example, if the current version is 1.12, then you know to run foo-1.13.sql and foo-1.15.sql.

> Now, what about having the control file host an 'upgrade' property where
> to put the script name? We would have to support a way for this filename
> to depend on the already installed version, I'm thinking that %v might
> be the easiest here (read: I want to avoid depending on any version
> scheme).
>
> version = '13'
> script = 'foo.sql'
> upgrade = 'foo_upgrade.%v.13.sql'

I think that's way more complicated than necessary.

Best,

David

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> On Dec 10, 2010, at 1:50 PM, Dimitri Fontaine wrote:
>> I don't think we can safely design around one part version numbers here,
>> because I'm yet to see that happening in any extension I've had my hands
>> on, which means a few already, as you can imagine.
>
> Why not? Simplest thing, to my mind, is to have
>
> upgrade/foo-1.12.sql
> upgrade/foo-1.13.sql
> upgrade/foo-1.15.sql

Since when is 1.12 a one part version number? :)

> Since you know the existing version number, you just run all that come
> after. For example, if the current version is 1.12, then you know to
> run foo-1.13.sql and foo-1.15.sql.

I don't think imposing what version numbers must look like and what the
separators in the file names should be is a good idea.

>> version = '13'
>> script = 'foo.sql'
>> upgrade = 'foo_upgrade.%v.13.sql'
>
> I think that's way more complicated than necessary.

It's just moving the complexity from the rules for the user to obey to
having them explain us by which rules they're playing. I personally very
much prefer the later, as you can imagine.

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

On Dec 10, 2010, at 2:32 PM, Dimitri Fontaine wrote:

> "David E. Wheeler" <david(at)kineticode(dot)com> writes:
>> On Dec 10, 2010, at 1:50 PM, Dimitri Fontaine wrote:
>>> I don't think we can safely design around one part version numbers here,
>>> because I'm yet to see that happening in any extension I've had my hands
>>> on, which means a few already, as you can imagine.
>>
>> Why not? Simplest thing, to my mind, is to have
>>
>> upgrade/foo-1.12.sql
>> upgrade/foo-1.13.sql
>> upgrade/foo-1.15.sql
>
> Since when is 1.12 a one part version number? :)

What difference does it make how many parts there are? If it's a naming convention, you just match /$extension-(.+?)\.sql$/. Simple.

>> Since you know the existing version number, you just run all that come
>> after. For example, if the current version is 1.12, then you know to
>> run foo-1.13.sql and foo-1.15.sql.
>
> I don't think imposing what version numbers must look like and what the
> separators in the file names should be is a good idea.

The version numbers can be anything, so long as there *are* version numbers. And the rest of the file name should be just like the extension.

> It's just moving the complexity from the rules for the user to obey to
> having them explain us by which rules they're playing. I personally very
> much prefer the later, as you can imagine.

You keep making extension authors have to do more work. I keep trying to make it so they can do less. We want the barrier to be as low as possible, which means a lot of DRY. Make it *possible* to do more complicated things, but don't *require* it.

Best,

David

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> On Dec 10, 2010, at 1:50 PM, Dimitri Fontaine wrote:
> (Actually, we could probably assume that the target version is
> implicitly "the current version", as identified from the control file,
> and omit that from the script file names. That would avoid ambiguity
> if version numbers can have more than one part.)
>>
>> I don't think we can safely design around one part version numbers here,
>> because I'm yet to see that happening in any extension I've had my hands
>> on, which means a few already, as you can imagine.

> Why not? Simplest thing, to my mind, is to have

> upgrade/foo-1.12.sql
> upgrade/foo-1.13.sql
> upgrade/foo-1.15.sql

> Since you know the existing version number, you just run all that come after. For example, if the current version is 1.12, then you know to run foo-1.13.sql and foo-1.15.sql.

If we assume the target is the current version, then we only need the
old-version number in the file name, so it doesn't matter how many
parts it has.

regards, tom lane

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> I'd say that for anything in /contrib, it gets a new version with each
> major version of postgresql, but not with each minor version. Thus,
> say, dblink when 9.1.0 is release would be dblink 9.1-1. If in 9.1.4 we
> fix a bug in dblink, then it becomes dblink 9.1-2.
> ...
> The alternative would be to match postgresql minor version numbering
> exactly, and then come up with some way to have a "no-op" upgrade in the
> frequent cases where the contrib module isn't changed during a minor
> release. This would also require some kind of "upgrade all" command for
> contrib.

99% of the time, "fix a bug" just means some C code changes. We should
not force DBAs to go through special upgrade commands unless there is
some change in the SQL objects created by the extension --- and just as
we discourage changes in the SQL objects created by the core during
minor releases, we should discourage such changes in minor extension
updates. So the case where ALTER EXTENSION UPGRADE is needed will be
the exception not the rule.

regards, tom lane

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> You keep making extension authors have to do more work. I keep trying
> to make it so they can do less. We want the barrier to be as low as
> possible, which means a lot of DRY. Make it *possible* to do more
> complicated things, but don't *require* it.

Sorry, imposing that - ain't part of the version number string won't
make any impression on me as far as getting simple is concerned. Go find
a single debian package not having - in its version number, and that's a
native software (developed to build debian).

For details, see the following, then explain me how RPM is so
differently simple, and then why I should care.

http://www.debian.org/doc/debian-policy/ch-controlfields.html#s-f-Version

So really, using %v to say "put the current version number here" does
not seem like a problem for me, it allows me not to have to think about
*any* files naming rules nor version numbering scheme.

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

On Dec 10, 2010, at 2:40 PM, Tom Lane wrote:

>> Since you know the existing version number, you just run all that come after. For example, if the current version is 1.12, then you know to run foo-1.13.sql and foo-1.15.sql.
>
> If we assume the target is the current version, then we only need the
> old-version number in the file name, so it doesn't matter how many
> parts it has.

Exactly.

Best,

David

On Dec 10, 2010, at 2:43 PM, Dimitri Fontaine wrote:

> "David E. Wheeler" <david(at)kineticode(dot)com> writes:
>> You keep making extension authors have to do more work. I keep trying
>> to make it so they can do less. We want the barrier to be as low as
>> possible, which means a lot of DRY. Make it *possible* to do more
>> complicated things, but don't *require* it.
>
> Sorry, imposing that - ain't part of the version number string won't
> make any impression on me as far as getting simple is concerned. Go find
> a single debian package not having - in its version number, and that's a
> native software (developed to build debian).

I'm making no such imposition. I'd rather it not be in contrib version numbers, because they should adhere to PostgreSQL-standard version numbering IMHO. YOu can use any characters you want in the version string. The upgrade file names simply start with "$extension-", so the format is "$extension-$version.sql". That's it.

> For details, see the following, then explain me how RPM is so
> differently simple, and then why I should care.
>
> http://www.debian.org/doc/debian-policy/ch-controlfields.html#s-f-Version
>
> So really, using %v to say "put the current version number here" does
> not seem like a problem for me, it allows me not to have to think about
> *any* files naming rules nor version numbering scheme.

It's just not necessary.

Best,

David

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> If we assume the target is the current version, then we only need the
> old-version number in the file name, so it doesn't matter how many
> parts it has.

IIUC, that puts even more work on the shoulders of the extension
authors, because the file named foo-1.12.sql is the one used to upgrade
from 1.12. That means that at each release, it's a different file
content, it's there to upgrade to a newer release.

Well it works too, of course, and we don't care how many dashes we find
in the filename, it's extension-version.sql. I'd be ok with that too.

So, we have a sound proposal for the ALTER EXTENSION UPGRADE command,
which comes later. So we keep version numbers in the CREATE EXTENSION
patch and the control files, and remove the facility to get this number
from the Makefile. Is that right?

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

Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
> "David E. Wheeler" <david(at)kineticode(dot)com> writes:
>> You keep making extension authors have to do more work. I keep trying
>> to make it so they can do less. We want the barrier to be as low as
>> possible, which means a lot of DRY. Make it *possible* to do more
>> complicated things, but don't *require* it.

> So really, using %v to say "put the current version number here" does
> not seem like a problem for me, it allows me not to have to think about
> *any* files naming rules nor version numbering scheme.

Maybe I misread David's meaning, but I thought he was saying that
there's no value in inventing all those control file entries in the
first place. Just hard-wire in ALTER EXTENSION UPGRADE the convention
that the name of an upgrade script to upgrade from prior version VVV is
EXTNAME-upgrade.VVV.sql (or any variant spelling of that you care for).
What is the point of letting/making extension authors invent their own
naming schemes? That has no benefit that I can perceive, and the
disadvantage that lack of uniformity will confuse users.

As for the question of what characters should be expected in version
numbers, +1 for digits and dots only. There's no good reason for
something else. Even the Debian document you quote points out that
hyphens in upstream version numbers give them problems, and Red Hat
style packaging rules flat out disallow hyphens. (hyphen-something is
for the packager to use, not the upstream software.)

regards, tom lane

On Dec 10, 2010, at 2:55 PM, Dimitri Fontaine wrote:

> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> If we assume the target is the current version, then we only need the
>> old-version number in the file name, so it doesn't matter how many
>> parts it has.
>
> IIUC, that puts even more work on the shoulders of the extension
> authors, because the file named foo-1.12.sql is the one used to upgrade
> from 1.12. That means that at each release, it's a different file
> content, it's there to upgrade to a newer release.

Yeah, it should be *to* 1.12. FWIW, this is how Bricolage upgrade scripts are handled: version-string-named directories with the appropriate scripts to upgrade *to* the named version number.

> So, we have a sound proposal for the ALTER EXTENSION UPGRADE command,
> which comes later. So we keep version numbers in the CREATE EXTENSION
> patch and the control files, and remove the facility to get this number
> from the Makefile. Is that right?

Yes. No new variables in Makefile at all IIUC.

Best,

David

On Dec 10, 2010, at 2:58 PM, Tom Lane wrote:

> Maybe I misread David's meaning, but I thought he was saying that
> there's no value in inventing all those control file entries in the
> first place. Just hard-wire in ALTER EXTENSION UPGRADE the convention
> that the name of an upgrade script to upgrade from prior version VVV is
> EXTNAME-upgrade.VVV.sql (or any variant spelling of that you care for).
> What is the point of letting/making extension authors invent their own
> naming schemes? That has no benefit that I can perceive, and the
> disadvantage that lack of uniformity will confuse users.

Yes, except that the version number in the file name should be the version it upgrades *to*, not *from*.

> As for the question of what characters should be expected in version
> numbers, +1 for digits and dots only. There's no good reason for
> something else. Even the Debian document you quote points out that
> hyphens in upstream version numbers give them problems, and Red Hat
> style packaging rules flat out disallow hyphens. (hyphen-something is
> for the packager to use, not the upstream software.)

I've mandated semantic versions for PGXN, mainly because it's simple and because it's close enough to the version numbers used in core.

http://semver.org/

If we're going to be comparing version strings in file names, we'll need *something* to use to compare what's higher than another number.

Best,

David

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> Yeah, it should be *to* 1.12. FWIW, this is how Bricolage upgrade scripts are handled: version-string-named directories with the appropriate scripts to upgrade *to* the named version number.

But you still have to know what you're upgrading *from*.

If we use subdirectories then it'd work to put one number in the subdir
name and the other in the file name.

regards, tom lane

On Dec 10, 2010, at 3:03 PM, Tom Lane wrote:

>> Yeah, it should be *to* 1.12. FWIW, this is how Bricolage upgrade scripts are handled: version-string-named directories with the appropriate scripts to upgrade *to* the named version number.
>
> But you still have to know what you're upgrading *from*.

Huh? It's in the pg_extension catalog.

Best,

David

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> On Dec 10, 2010, at 3:03 PM, Tom Lane wrote:
>>> Yeah, it should be *to* 1.12. FWIW, this is how Bricolage upgrade scripts are handled: version-string-named directories with the appropriate scripts to upgrade *to* the named version number.

>> But you still have to know what you're upgrading *from*.

> Huh? It's in the pg_extension catalog.

How do you select which upgrade script to apply?

regards, tom lane

On Dec 10, 2010, at 4:15 PM, Tom Lane wrote:

>> Huh? It's in the pg_extension catalog.
>
> How do you select which upgrade script to apply?

You run all those that contain version numbers higher than the currently-installed one.

This of course assumes that one can correctly tell that one version number is higher than another.

Best,

David

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> On Dec 10, 2010, at 4:15 PM, Tom Lane wrote:
>> How do you select which upgrade script to apply?

> You run all those that contain version numbers higher than the currently-installed one.

> This of course assumes that one can correctly tell that one version number is higher than another.

This idea is not exactly free of disadvantages.

1. It assumes that the underlying .so supports not only the current
version, but every intermediate version of the SQL objects. For
example, say the previously installed version was 1.10, and we are
trying to go to 1.12. With your proposal we must pass through the
catalog state applicable to 1.11. What if that includes some SQL
function whose underlying C function is no longer there? The
CREATE FUNCTION command will fail, that's what, even though the
next update file would have deleted it or more likely replaced it
with a reference to some other underlying function.

2. It can't tell whether a missing update file means "no work is
required" or "no upgrade is possible"; in fact, without quite a lot of
assumptions about version numbers, it can't even tell that an
intermediate version update file is missing at all. I assume you expect
that the backend would treat a missing file as "no work is required",
but that carries a lot of risk of winding up in a bad state if a file
fails to get installed or fails to get read for some reason.

I'd much rather expect the extension author to explicitly support each
pair of (from, to) version numbers that he's prepared to deal with.
If he can build those update scripts as simple concatenations of
single-step scripts, great; but let's not hard-wire the assumption that
that approach MUST work.

regards, tom lane

On Dec 10, 2010, at 4:39 PM, Tom Lane wrote:

> This idea is not exactly free of disadvantages.
>
> 1. It assumes that the underlying .so supports not only the current
> version, but every intermediate version of the SQL objects. For
> example, say the previously installed version was 1.10, and we are
> trying to go to 1.12. With your proposal we must pass through the
> catalog state applicable to 1.11. What if that includes some SQL
> function whose underlying C function is no longer there? The
> CREATE FUNCTION command will fail, that's what, even though the
> next update file would have deleted it or more likely replaced it
> with a reference to some other underlying function.

Yes, I always forget about shared objects, since most of the stuff I do isn't C.

> 2. It can't tell whether a missing update file means "no work is
> required" or "no upgrade is possible"; in fact, without quite a lot of
> assumptions about version numbers, it can't even tell that an
> intermediate version update file is missing at all. I assume you expect
> that the backend would treat a missing file as "no work is required",
> but that carries a lot of risk of winding up in a bad state if a file
> fails to get installed or fails to get read for some reason.

That seems relatively low-risk to me.

> I'd much rather expect the extension author to explicitly support each
> pair of (from, to) version numbers that he's prepared to deal with.
> If he can build those update scripts as simple concatenations of
> single-step scripts, great; but let's not hard-wire the assumption that
> that approach MUST work.

This does eliminate the need for the core to mandate a version number scheme, but it could create a *lot* more maintenance work for a rapidly-evolving extension. I doubt I would ever have got very far with pgTAP if I'd had to do something like this.

Best,

David

Tom,

> I'd much rather expect the extension author to explicitly support each
> pair of (from, to) version numbers that he's prepared to deal with.
> If he can build those update scripts as simple concatenations of
> single-step scripts, great; but let's not hard-wire the assumption that
> that approach MUST work.

That's an n^2 problem.

However, I don't see any obvious way to avoid it.

We would want to support some wildcarding, though, just to avoid having
1,000 version-to-version files in every extension when a lot of the
upgrade actions might be generic. Of course, in order to do
wildcarding, we need to mandate a version numbering system ...

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

Tom,

> I don't believe that extension SQL scripts should rely on DO blocks.
> There is no requirement that plpgsql be installed, and we're not going
> to create one as part of this feature. What this means is that the
> design you offer above doesn't work at all, since it fundamentally
> assumes that the SQL script can do conditional logic. What's more,
> it fundamentally assumes that the script WILL do conditional logic
> and support (in one lump) every possible combination of versions.
> That's going to turn into buggy spaghetti-code very quickly.

I just noticed this response, and don't think it can stand as-is.

While I agree that it's not reasonable to have a single script which
supports every combination of versions, I also assert that it's
completely unreasonable to expect extension authors to write upgrade
scripts with no conditional logic. Your view would essentially be
requiring authors to write a completely seperate SQL script for every
single possible combination of two versions.

For an extension which has had 10 releases with SQL modifications, this
would be 45 separate upgrade files. That's a ridiculous thing to expect
of any contributor.

I, for one, have no problem whatsoever with requiring that users have
plpgsql installed in order to use extensions. It's installed by default.
If they need to uninstall plpgsql for some security reason, then fine;
they can write their own upgrade scripts. You are pushing making things
easy for 0.5% of our users at the expense of everyone else.

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

On Fri, Dec 10, 2010 at 8:14 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> I don't believe that extension SQL scripts should rely on DO blocks.
>> There is no requirement that plpgsql be installed, and we're not going
>> to create one as part of this feature.  What this means is that the
>> design you offer above doesn't work at all, since it fundamentally
>> assumes that the SQL script can do conditional logic.  What's more,
>> it fundamentally assumes that the script WILL do conditional logic
>> and support (in one lump) every possible combination of versions.
>> That's going to turn into buggy spaghetti-code very quickly.
>
> I just noticed this response, and don't think it can stand as-is.
>
> While I agree that it's not reasonable to have a single script which
> supports every combination of versions, I also assert that it's
> completely unreasonable to expect extension authors to write upgrade
> scripts with no conditional logic.  Your view would essentially be
> requiring authors to write a completely seperate SQL script for every
> single possible combination of two versions.
>
> For an extension which has had 10 releases with SQL modifications, this
> would be 45 separate upgrade files.  That's a ridiculous thing to expect
> of any contributor.
>
> I, for one, have no problem whatsoever with requiring that users have
> plpgsql installed in order to use extensions. It's installed by default.
> If they need to uninstall plpgsql for some security reason, then fine;
> they can write their own upgrade scripts.  You are pushing making things
> easy for 0.5% of our users at the expense of everyone else.

Yea, verily. I share Tom's concern about depending on a procedural
language that isn't absolutely guaranteed to be there, but crippling
the extension mechanism is a bad solution. Conditional logic is
important, and we need to have it. If we're really bent on making
this watertight, we can either somehow nail down PL/pgsql so that it's
always present, or add conditional logic to straight SQL, or some
other magic I'm not thinking of. Or we can just suck up the fact that
people who uninstall PL/pgsql are not going to be able to install
extensions that depend on PL/pgsql, which isn't great, but I think it
beats the alternative.

In my not-inconsiderable experience writing upgrade scripts, most of
the time, you just add new objects. So if CREATE OR REPLACE or CREATE
IF NOT EXISTS is available, you only need one upgrade script to
upgrade from ANY prior version. And most of what people create with
these scripts are functions, which have CREATE OR REPLACE. However,
every once in a while you want to change the definition of an existing
object, at which point you enter what I like to call dependency hell.
If the object has no dependencies, you can just drop and recreate it,
but if it does, go directly to unspeakable agony. A further problem
with extensions is that we haven't got either COR or CINE for things
like types, operator classes, operator class members, etc. If we
decline to add that, then people are going to have to work around it
by writing the logic in PL/pgsql.... or else go with Tom's suggestion
of having a separate script for every to/from combination.

But I don't think that's really the right way to go. I think what
will quickly happen is that the conditional logic will move out of the
SQL script itself and into complicated Makefile hackery which will
generate a whole bunch of similar but not quite identical upgrade
scripts. Blech.

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

Hi,

I've been reading through the entire thread and it seems like this is
the best mail to choose to answer.

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> Maybe I misread David's meaning, but I thought he was saying that
> there's no value in inventing all those control file entries in the
> first place. Just hard-wire in ALTER EXTENSION UPGRADE the convention
> that the name of an upgrade script to upgrade from prior version VVV is
> EXTNAME-upgrade.VVV.sql (or any variant spelling of that you care for).

Yeah that works, as soon as VVV is the version we upgrade from.

That said, we need to find a way to lighten the process for extensions
where it's easy to have a single script to support upgrade from more
than once past release.

What about having the following keys supported in the control file:

upgrade_<version> = 'script.version.sql'
upgrade_all = 'script.sql'

Where the version here is the version you're upgrading *from* (to is
known and static when you distribute the files after all), and where
upgrade_all is applied last no matter what got applied before.

Also, do we want a subdirectory per extension to host all those files?

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

On Fri, Dec 10, 2010 at 11:24:27AM -0500, Tom Lane wrote:
> Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
> > Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> >> Are there any actual remaining use-cases for that sed step?
>
> > The goal here is to allow extension authors to maintain their version
> > number in the Makefile rather than in the Makefile and in the control
> > file separately. Having the same version number in more than one place
> > never eases maintenance.
>
> Why is it in the makefile at all? If the makefile does need to know it,
> why don't we have it scrape the number out of the control file? Or even
> more to the point, since when do we need version numbers in extensions?

We *absolutely* need version numbers in extensions. People will want
to have a certain version, or a certain minimum version, etc., etc.,
etc., just as they do for any other software.

Seriously, are you OK?

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 Dec 11, 2010, at 1:09 PM, David Fetter wrote:

>> Why is it in the makefile at all? If the makefile does need to know it,
>> why don't we have it scrape the number out of the control file? Or even
>> more to the point, since when do we need version numbers in extensions?
>
> We *absolutely* need version numbers in extensions. People will want
> to have a certain version, or a certain minimum version, etc., etc.,
> etc., just as they do for any other software.
>
> Seriously, are you OK?

One of the biggest mistakes in the creation of CPAN was allowing modules without extensions. It makes figuring out what to upgrade extremely difficult. Learning from that, PGXN requires version numbers for all extensions.

Best,

David

On Dec 11, 2010, at 12:09 PM, Dimitri Fontaine wrote:

> Yeah that works, as soon as VVV is the version we upgrade from.
>
> That said, we need to find a way to lighten the process for extensions
> where it's easy to have a single script to support upgrade from more
> than once past release.
>
> What about having the following keys supported in the control file:
>
> upgrade_<version> = 'script.version.sql'
> upgrade_all = 'script.sql'

Why not just use an upgrade script naming convention? Think: Convention over configuration.

> Where the version here is the version you're upgrading *from* (to is
> known and static when you distribute the files after all), and where
> upgrade_all is applied last no matter what got applied before.
>
> Also, do we want a subdirectory per extension to host all those files?

How are things currently arranged?

Best,

David

Hi there,

it's clear we need versions, probably, major.minor would be enough. The problem
I see is how to keep .so in sync with .sql ? Should we store .sql in database ?

Also, we need permissions for extension, since we have open/closed
extensions.

Oleg

On Sat, 11 Dec 2010, David E. Wheeler wrote:

> On Dec 11, 2010, at 1:09 PM, David Fetter wrote:
>
>>> Why is it in the makefile at all? If the makefile does need to know it,
>>> why don't we have it scrape the number out of the control file? Or even
>>> more to the point, since when do we need version numbers in extensions?
>>
>> We *absolutely* need version numbers in extensions. People will want
>> to have a certain version, or a certain minimum version, etc., etc.,
>> etc., just as they do for any other software.
>>
>> Seriously, are you OK?
>
> One of the biggest mistakes in the creation of CPAN was allowing modules without extensions. It makes figuring out what to upgrade extremely difficult. Learning from that, PGXN requires version numbers for all extensions.
>
> Best,
>
> David
>
>
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

On Sat, Dec 11, 2010 at 4:35 PM, David E. Wheeler <david(at)kineticode(dot)com> wrote:

>> What about having the following keys supported in the control file:
>>
>>  upgrade_<version> = 'script.version.sql'
>>  upgrade_all = 'script.sql'
>
> Why not just use an upgrade script naming convention? Think: Convention over configuration.

Mainly, because of the situation where I have may versions that can
all be upgraded from the same script. I'ld much rather distribution
just 3 scripts (install + 2 upgrades), and a control file with
something like this (pretend I'm on version 2.6)
upgragde-1.0 = $EXT-upgrade-1.sql
upgragde-1.1 = $EXT-upgrade-1.sql
upgragde-1.1.1 = $EXT-upgrade-1.sql
upgragde-1.1.2 = $EXT-upgrade-1.sql
upgragde-1.2 = $EXT-upgrade-1.sql
upgragde-1.3 = $EXT-upgrade-1.sql
upgragde-1.4 = $EXT-upgrade-1.sql
upgragde-1.4.1 = $EXT-upgrade-1.sql
upgrade-2.0 = $EXT-upgrade-2.sql
upgrade-2.1 = $EXT-upgrade-2.sql
upgrade-2.2 = $EXT-upgrade-2.sql
upgrade-2.2.1 = $EXT-upgrade-2.sql
upgrade-2.3 = $EXT-upgrade-2.sql
upgrade-2.4 = $EXT-upgrade-2.sql
upgrade-2.5 = $EXT-upgrade-2.sql

Forcing convention on me to maitain/install an upgrade script for
every single version is way more than asking me to just specify an
upgrade script for versions.

Again, I'ld love for the "version" to support some sort of prefix or
wildcard matching, so I could do:
upgrade-1.* = $EXT-upgrade-1.sql
upgrade-2.* = $EXT-upgrade-2.sql

a.

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

Aidan Van Dyk <aidan(at)highrise(dot)ca> writes:
> Mainly, because of the situation where I have may versions that can
> all be upgraded from the same script. I'ld much rather distribution
> just 3 scripts (install + 2 upgrades), and a control file with
> something like this (pretend I'm on version 2.6)
> upgragde-1.0 = $EXT-upgrade-1.sql
[...]
> upgrade-2.5 = $EXT-upgrade-2.sql

Thanks for the example.

> Again, I'ld love for the "version" to support some sort of prefix or
> wildcard matching, so I could do:
> upgrade-1.* = $EXT-upgrade-1.sql
> upgrade-2.* = $EXT-upgrade-2.sql

Problem is: what to do if a single upgrade matches more than one line?
The only safe answer is to error out and refuse to upgrade but that
ain't nice to the user. How much is that a problem here?

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

On Mon, Dec 13, 2010 at 9:55 AM, Dimitri Fontaine
<dimitri(at)2ndquadrant(dot)fr> wrote:

>> Again, I'ld love for the "version" to support some sort of prefix or
>> wildcard matching, so I could do:
>>     upgrade-1.* =  $EXT-upgrade-1.sql
>>     upgrade-2.* =  $EXT-upgrade-2.sql
>
> Problem is: what to do if a single upgrade matches more than one line?
> The only safe answer is to error out and refuse to upgrade but that
> ain't nice to the user. How much is that a problem here?

To get a wildcard match (or a prefix match) for version upgrades, I'ld
be willing to have that error if I give a bad set of version matches.
If only have those 2 lines to manage, it's a lot more likely I won't
mess them up than if I have to manage 30 almost identical lines and
not miss/duplicate a version.

;-)

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

Aidan Van Dyk <aidan(at)highrise(dot)ca> writes:
> On Sat, Dec 11, 2010 at 4:35 PM, David E. Wheeler <david(at)kineticode(dot)com> wrote:
>> Why not just use an upgrade script naming convention?

> Mainly, because of the situation where I have may versions that can
> all be upgraded from the same script. I'ld much rather distribution
> just 3 scripts (install + 2 upgrades), and a control file with
> something like this (pretend I'm on version 2.6)
> upgragde-1.0 = $EXT-upgrade-1.sql
> upgragde-1.1 = $EXT-upgrade-1.sql
> upgragde-1.1.1 = $EXT-upgrade-1.sql
> upgragde-1.1.2 = $EXT-upgrade-1.sql
> upgragde-1.2 = $EXT-upgrade-1.sql
> upgragde-1.3 = $EXT-upgrade-1.sql
> upgragde-1.4 = $EXT-upgrade-1.sql
> upgragde-1.4.1 = $EXT-upgrade-1.sql
> upgrade-2.0 = $EXT-upgrade-2.sql
> upgrade-2.1 = $EXT-upgrade-2.sql
> upgrade-2.2 = $EXT-upgrade-2.sql
> upgrade-2.2.1 = $EXT-upgrade-2.sql
> upgrade-2.3 = $EXT-upgrade-2.sql
> upgrade-2.4 = $EXT-upgrade-2.sql
> upgrade-2.5 = $EXT-upgrade-2.sql

I see no advantage of this over a script per version combination, so
long as you allow scripts to \include each other.

regards, tom lane

Excerpts from Tom Lane's message of lun dic 13 12:50:43 -0300 2010:

> I see no advantage of this over a script per version combination, so
> long as you allow scripts to \include each other.

Hmm, are the upgrade scripts going to be run via SPI?

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> I see no advantage of this over a script per version combination, so
> long as you allow scripts to \include each other.

I guess the following should do:

SELECT pg_execute_sql_file('upgrade-1.sql');

But I rather prefer the 2-liner control file, myself:

upgrade-1.* = 'upgrade-1.sql'
upgrade-2.* = 'upgrade-2.sql'

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

Oleg Bartunov wrote:
> Hi there,
>
> it's clear we need versions, probably, major.minor would be enough. The problem
> I see is how to keep .so in sync with .sql ? Should we store .sql in database ?
>
> Also, we need permissions for extension, since we have open/closed
> extensions.
>

Don't people normally define the version number in the Makefile and pass
the version string into the C code and perhaps a psql variable?

---------------------------------------------------------------------------

>
> Oleg
>
> On Sat, 11 Dec 2010, David E. Wheeler wrote:
>
> > On Dec 11, 2010, at 1:09 PM, David Fetter wrote:
> >
> >>> Why is it in the makefile at all? If the makefile does need to know it,
> >>> why don't we have it scrape the number out of the control file? Or even
> >>> more to the point, since when do we need version numbers in extensions?
> >>
> >> We *absolutely* need version numbers in extensions. People will want
> >> to have a certain version, or a certain minimum version, etc., etc.,
> >> etc., just as they do for any other software.
> >>
> >> Seriously, are you OK?
> >
> > One of the biggest mistakes in the creation of CPAN was allowing modules without extensions. It makes figuring out what to upgrade extremely difficult. Learning from that, PGXN requires version numbers for all extensions.
> >
> > Best,
> >
> > David
> >
> >
> >
>
> Regards,
> Oleg
> _____________________________________________________________
> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
> Sternberg Astronomical Institute, Moscow University, Russia
> Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
> phone: +007(495)939-16-83, +007(495)939-23-83
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Dec 29, 2010, at 12:00 PM, Bruce Momjian wrote:

> Don't people normally define the version number in the Makefile and pass
> the version string into the C code and perhaps a psql variable?

There is no standard pattern AFAIK. A best practice would be welcome here.

David

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Oleg Bartunov wrote:
>> it's clear we need versions, probably, major.minor would be enough. The problem
>> I see is how to keep .so in sync with .sql ? Should we store .sql in database ?

> Don't people normally define the version number in the Makefile and pass
> the version string into the C code and perhaps a psql variable?

We had a long discussion upthread of what version numbers to keep where.
IMHO the Makefile is about the *least* useful place to put a version
number; the more so if you want more than one. What we seem to need is
a version number in the .sql file itself (so that we can tell whether we
need to take action to update the extension's catalog entries). I'm not
convinced yet whether there needs to be another version number embedded
in the .so file --- it may well be that the PG major version number
embedded with PG_MODULE_MAGIC is sufficient.

Personally I'd forget the notion of major.minor numbers here; all that
will accomplish is to complicate storage and comparison of the numbers.
We just need a simple integer that gets bumped whenever the extension's
SQL script changes.

regards, tom lane

On Dec 29, 2010, at 12:23 PM, Tom Lane wrote:

> We had a long discussion upthread of what version numbers to keep where.
> IMHO the Makefile is about the *least* useful place to put a version
> number; the more so if you want more than one. What we seem to need is
> a version number in the .sql file itself (so that we can tell whether we
> need to take action to update the extension's catalog entries). I'm not
> convinced yet whether there needs to be another version number embedded
> in the .so file --- it may well be that the PG major version number
> embedded with PG_MODULE_MAGIC is sufficient.

For contrib maybe, but not 3rd-party extensions.

> Personally I'd forget the notion of major.minor numbers here; all that
> will accomplish is to complicate storage and comparison of the numbers.
> We just need a simple integer that gets bumped whenever the extension's
> SQL script changes.

That won't be very flexible for third-party extensions. FWIW, for PGXN I mandated symantic version numbers (http://semver.org/), mainly because they're quite close to Pg core version numbers. I also created a basic data type for them:

https://github.com/theory/pgxn-manager/blob/master/sql/02-types.sql#L70

Best,

David

On Wed, Dec 29, 2010 at 3:23 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
>> Oleg Bartunov wrote:
>>> it's clear we need versions, probably, major.minor would be enough. The problem
>>> I see is how to keep .so in sync with .sql ? Should we store .sql in database ?
>
>> Don't people normally define the version number in the Makefile and pass
>> the version string into the C code and perhaps a psql variable?
>
> We had a long discussion upthread of what version numbers to keep where.
> IMHO the Makefile is about the *least* useful place to put a version
> number; the more so if you want more than one.  What we seem to need is
> a version number in the .sql file itself (so that we can tell whether we
> need to take action to update the extension's catalog entries).  I'm not
> convinced yet whether there needs to be another version number embedded
> in the .so file --- it may well be that the PG major version number
> embedded with PG_MODULE_MAGIC is sufficient.
>
> Personally I'd forget the notion of major.minor numbers here; all that
> will accomplish is to complicate storage and comparison of the numbers.
> We just need a simple integer that gets bumped whenever the extension's
> SQL script changes.

I think there are really two tasks here:

1. Identify whether a newer set of SQL definitions than the one
installed is available. If so, the extension is a candidate for an
upgrade.

2. Identify whether the installed version of the SQL definitions is
compatible with the installed shared object. If it's not, we'd like
the shared library load (or at a minimum, any use of the shared
library) to fail when attempted, rather than attempting to plunge
blindly onward and then crashing.

As to point #2, what an extension author would typically do (I hope)
is publish a new shared object is make it backward-compatible with
some number of previous versions of the SQL definitions, but not
necessarily all the way to the beginning of time. So the typical
upgrade sequence would be to install the new .so, and then upgrade the
SQL definitions. You'd want an interlock, though, in case someone
tried to use a set of SQL definitions that were either too new or too
old for the corresponding shared library. The "too new" case could
occur if someone installed a new version of the shared library,
upgraded the SQL definitions, and then put the old shared library file
back. The "too old" case could occur if the extension were upgraded
through many releases in a single step, such that whatever
backward-compatibility support existed in the shared library didn't
reach back far enough to cater to the ancient SQL definitions. In
either case, you want to chunk an error when someone tries to use the
module, rather than soldiering on blindly and crashing.

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

On Dec 29, 2010, at 1:27 PM, Robert Haas wrote:

> I think there are really two tasks here:
>
> 1. Identify whether a newer set of SQL definitions than the one
> installed is available. If so, the extension is a candidate for an
> upgrade.
>
> 2. Identify whether the installed version of the SQL definitions is
> compatible with the installed shared object. If it's not, we'd like
> the shared library load (or at a minimum, any use of the shared
> library) to fail when attempted, rather than attempting to plunge
> blindly onward and then crashing.

3. Check dependencies for one extension on other extensions.

> As to point #2, what an extension author would typically do (I hope)
> is publish a new shared object is make it backward-compatible with
> some number of previous versions of the SQL definitions, but not
> necessarily all the way to the beginning of time. So the typical
> upgrade sequence would be to install the new .so, and then upgrade the
> SQL definitions. You'd want an interlock, though, in case someone
> tried to use a set of SQL definitions that were either too new or too
> old for the corresponding shared library. The "too new" case could
> occur if someone installed a new version of the shared library,
> upgraded the SQL definitions, and then put the old shared library file
> back. The "too old" case could occur if the extension were upgraded
> through many releases in a single step, such that whatever
> backward-compatibility support existed in the shared library didn't
> reach back far enough to cater to the ancient SQL definitions. In
> either case, you want to chunk an error when someone tries to use the
> module, rather than soldiering on blindly and crashing.

Yeah, makes sense.

Best,

David

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Wed, Dec 29, 2010 at 3:23 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> We had a long discussion upthread of what version numbers to keep where.
>> IMHO the Makefile is about the *least* useful place to put a version
>> number; the more so if you want more than one.  What we seem to need is
>> a version number in the .sql file itself (so that we can tell whether we
>> need to take action to update the extension's catalog entries).  I'm not
>> convinced yet whether there needs to be another version number embedded
>> in the .so file --- it may well be that the PG major version number
>> embedded with PG_MODULE_MAGIC is sufficient.

In the .sql file? You mean something like:

ALTER EXTENSION ... SET VERSION '...';

It's currently managed in the .control file of the extension, which
allows us to list available extensions and their version number without
having to parse the .sql script from the C code...

>> Personally I'd forget the notion of major.minor numbers here; all that
>> will accomplish is to complicate storage and comparison of the numbers.
>> We just need a simple integer that gets bumped whenever the extension's
>> SQL script changes.

For contrib, as you wish. Now for third-party extensions, I don't see
us having any authority on what people will use internally in their
companies, etc.

> 1. Identify whether a newer set of SQL definitions than the one
> installed is available. If so, the extension is a candidate for an
> upgrade.

Well, it's currently (WIP in the upgrade branch of my repo) easier than
that, really. You have the control file on the file system and you have
the extension's entry in the catalogs.

http://git.postgresql.org/gitweb?p=postgresql-extension.git;a=shortlog;h=refs/heads/upgrade

What upgrade means here is running a given SQL script, that you choose
depending on the current and next version strings, following a scheme
that has been extensively discussed in another thread, and is currently
implemented like this:

# lo
comment = 'managing Large Objects'
version = '9.1devel'
relocatable = true
upgrade_from_null = 'null => lo.upgrade.sql'

Here, any property that begins with 'upgrade_from_' is considered as an
upgrade setup and the part after the prefix is not considered. The
value is meant to have two parts separated by '=>', first is either null
or a regexp matched against currently installed version number, second
part is the upgrade script name to use at ALTER EXTENSION ... UPGRADE.

We support 'null' version number to be able to "upgrade" from existing
code which is not organized as an extension yet. The aim is to be able
to:

CREATE EMPTY EXTENSION lo; -- version is null here
ALTER EXTENSION lo UPGRADE;

And run a script containing lines that will look like this:

alter domain @extschema(at)(dot)lo set extension lo;
alter function @extschema(at)(dot)lo_oid(lo) set extension lo;
alter function @extschema(at)(dot)lo_manage() set extension lo;

Note that we always need to support the placeholder here, because of
course following dependencies at this point isn't possible.

> 2. Identify whether the installed version of the SQL definitions is
> compatible with the installed shared object. If it's not, we'd like
> the shared library load (or at a minimum, any use of the shared
> library) to fail when attempted, rather than attempting to plunge
> blindly onward and then crashing.

Well, the way I see things, it's already too late and there's nothing we
can easily do to prevent that. What I mean is that the user will
typically upgrade the OS-level package first, then apply the upgrade on
the database(s).

$ apt-get install postgresql-9.1-prefix
$ psql -U postgres -c 'alter extension prefix upgrade' somedb

At the time you tell PostgreSQL about the new extension, the shared
object file has been in place for some time already, and the upgrade SQL
script has not been ran yet.

What I hope extension authors will do is document whether any upgrade
requires a restart or will otherwise be responsible for instability in
the server for backend started with the newer .so before the upgrade
script has been run. So that users/DBA will know whether the upgrade
calls for a maintenance window.

I could see us trying to shoehorn such information into the control file
too, but would ERRORing out on LOAD be any better than taking the
compatibility chance? Knowing that the compatibility in most cases
depends a lot on the actual call paths?

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

On Dec 29, 2010, at 2:01 PM, Dimitri Fontaine wrote:

> # lo
> comment = 'managing Large Objects'
> version = '9.1devel'
> relocatable = true
> upgrade_from_null = 'null => lo.upgrade.sql'
>
> Here, any property that begins with 'upgrade_from_' is considered as an
> upgrade setup and the part after the prefix is not considered. The
> value is meant to have two parts separated by '=>', first is either null
> or a regexp matched against currently installed version number, second
> part is the upgrade script name to use at ALTER EXTENSION ... UPGRADE.

I thought we were going to try to avoid having entries for upgrades in the control file.

> We support 'null' version number to be able to "upgrade" from existing
> code which is not organized as an extension yet. The aim is to be able
> to:
>
> CREATE EMPTY EXTENSION lo; -- version is null here
> ALTER EXTENSION lo UPGRADE;
>
> And run a script containing lines that will look like this:
>
> alter domain @extschema(at)(dot)lo set extension lo;
> alter function @extschema(at)(dot)lo_oid(lo) set extension lo;
> alter function @extschema(at)(dot)lo_manage() set extension lo;
>
> Note that we always need to support the placeholder here, because of
> course following dependencies at this point isn't possible.

I thought placeholders were going away, too. Did I lose track?

> Well, the way I see things, it's already too late and there's nothing we
> can easily do to prevent that. What I mean is that the user will
> typically upgrade the OS-level package first, then apply the upgrade on
> the database(s).
>
> $ apt-get install postgresql-9.1-prefix
> $ psql -U postgres -c 'alter extension prefix upgrade' somedb
>
> At the time you tell PostgreSQL about the new extension, the shared
> object file has been in place for some time already, and the upgrade SQL
> script has not been ran yet.

That sounds dangerous.

> What I hope extension authors will do is document whether any upgrade
> requires a restart or will otherwise be responsible for instability in
> the server for backend started with the newer .so before the upgrade
> script has been run. So that users/DBA will know whether the upgrade
> calls for a maintenance window.

But if a new connection comes in, the .so will be loaded into the new child, no? Very dangerous.

> I could see us trying to shoehorn such information into the control file
> too, but would ERRORing out on LOAD be any better than taking the
> compatibility chance? Knowing that the compatibility in most cases
> depends a lot on the actual call paths?

The new .so should not be installed until the upgrade is been run.

Best,

David

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> On Dec 29, 2010, at 2:01 PM, Dimitri Fontaine wrote:
>> At the time you tell PostgreSQL about the new extension, the shared
>> object file has been in place for some time already, and the upgrade SQL
>> script has not been ran yet.

> That sounds dangerous.

It is, but I don't see any alternative. As Dimitri said, the .so will
typically be installed by a packaging system, so we don't have any
opportunity to run SQL code beforehand. In any case ...

> The new .so should not be installed until the upgrade is been run.

... that flat out doesn't work. If the upgrade script tries to add
functions that didn't exist in the old .so, it'll fail.

regards, tom lane

On Jan 3, 2011, at 11:42 AM, Tom Lane wrote:

> It is, but I don't see any alternative. As Dimitri said, the .so will
> typically be installed by a packaging system, so we don't have any
> opportunity to run SQL code beforehand. In any case ...
>
>> The new .so should not be installed until the upgrade is been run.
>
> ... that flat out doesn't work. If the upgrade script tries to add
> functions that didn't exist in the old .so, it'll fail.

Right, what I'm saying is that `ALTER EXTENSION foo UPGRADE;` should install the .so, too, just before it runs the upgrade scripts.

Best,

David

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> I thought we were going to try to avoid having entries for upgrades in
> the control file.

Not what I have understood.

http://archives.postgresql.org/pgsql-hackers/2010-12/msg01014.php
http://archives.postgresql.org/pgsql-hackers/2010-12/msg01045.php

AS there was no answer, the meaning for me is that it was ok to
proceed. On this list people agreeing often remain silent.

>> Note that we always need to support the placeholder here, because of
>> course following dependencies at this point isn't possible.
>
> I thought placeholders were going away, too. Did I lose track?

Oh, dear, yes :) See the documentation for the relocatable parameter.
We know handle two kinds of extensions, some of them you can't offer
better than placeholders to allow users to define the schema where they
will land. Also, at upgrade time, I don't see any other way to solve
the problem. Do you?

http://pgsql.tapoueh.org/extensions/doc/html/extend-extension.html

>> At the time you tell PostgreSQL about the new extension, the shared
>> object file has been in place for some time already, and the upgrade SQL
>> script has not been ran yet.
>
> That sounds dangerous.

Been doing that countless times. Yet to see a case where the new .so is
not compatible at all with the previous .sql and the author don't give
you any warning about the situation. In theory that's possible, in
practice we value upgrades high enough around here.

Other than that, I don't have another idea to make it work reliably.
I'm still reading, though. Meanwhile I've done what seems like a good
compromise and to follow practical use cases.

>> What I hope extension authors will do is document whether any upgrade
>> requires a restart or will otherwise be responsible for instability in
>> the server for backend started with the newer .so before the upgrade
>> script has been run. So that users/DBA will know whether the upgrade
>> calls for a maintenance window.
>
> But if a new connection comes in, the .so will be loaded into the new child, no? Very dangerous.

Yeah. Before extension existed, it has always been working like that,
our users already depend on such a behavior, nothing new here. I just
don't see how extension could solve that is all I'm saying.

> The new .so should not be installed until the upgrade is been run.

Nice statement. How do you make that happen?

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

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> On Jan 3, 2011, at 11:42 AM, Tom Lane wrote:
>> ... that flat out doesn't work. If the upgrade script tries to add
>> functions that didn't exist in the old .so, it'll fail.

> Right, what I'm saying is that `ALTER EXTENSION foo UPGRADE;` should install the .so, too, just before it runs the upgrade scripts.

1. Doesn't work if you're upgrading an installation that has more than
one database using the extension. There's only one library directory.

2. Not possible from a permissions standpoint. Even if you think it'd
be smart to have the postgres daemon privileged enough to overwrite its
own executables, there is 0 chance of getting that past any distro.

regards, tom lane

On Jan 3, 2011, at 11:51 AM, Tom Lane wrote:

> 1. Doesn't work if you're upgrading an installation that has more than
> one database using the extension. There's only one library directory.
>
> 2. Not possible from a permissions standpoint. Even if you think it'd
> be smart to have the postgres daemon privileged enough to overwrite its
> own executables, there is 0 chance of getting that past any distro.

Okay, got it.

Best,

David

On Jan 3, 2011, at 11:46 AM, Dimitri Fontaine wrote:

> Not what I have understood.
>
> http://archives.postgresql.org/pgsql-hackers/2010-12/msg01014.php
> http://archives.postgresql.org/pgsql-hackers/2010-12/msg01045.php
>
> AS there was no answer, the meaning for me is that it was ok to
> proceed. On this list people agreeing often remain silent.

There were several of us who were not silent.

http://archives.postgresql.org/pgsql-hackers/2010-12/msg00804.php
http://archives.postgresql.org/pgsql-hackers/2010-12/msg00796.php

The fact that the last two messages in the thread say something else does not mean that they represent the consensus.

>>> Note that we always need to support the placeholder here, because of
>>> course following dependencies at this point isn't possible.
>>
>> I thought placeholders were going away, too. Did I lose track?
>
> Oh, dear, yes :) See the documentation for the relocatable parameter.
> We know handle two kinds of extensions, some of them you can't offer
> better than placeholders to allow users to define the schema where they
> will land. Also, at upgrade time, I don't see any other way to solve
> the problem. Do you?
>
> http://pgsql.tapoueh.org/extensions/doc/html/extend-extension.html

Right, I forgot about the relocatable parameter. I kind of expect that most extensions *would* be relocatable, though. Maybe it should be expected to be true if it's not present? Or perhaps require non-relocatable extensions to have a "fixed_schema" control key or something? Either will work, just trying to find the likely convention to avoid configuration in most cases. Maybe I'm wrong, though, and most extensions wouldn't be relocatable?

> Yeah. Before extension existed, it has always been working like that,
> our users already depend on such a behavior, nothing new here. I just
> don't see how extension could solve that is all I'm saying.

Fair enough.

>> The new .so should not be installed until the upgrade is been run.
>
> Nice statement. How do you make that happen?

Nope.

David

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> The fact that the last two messages in the thread say something else
> does not mean that they represent the consensus.

Yeah, but as I'm the one writing the code, I gave myself more than one
vote. And did consider the alternatives but didn't like them so much.

> Right, I forgot about the relocatable parameter. I kind of expect that most extensions *would* be relocatable, though. Maybe it should be expected to be true if it's not present? Or perhaps require non-relocatable extensions to have a "fixed_schema" control key or something? Either will work, just trying to find the likely convention to avoid configuration in most cases. Maybe I'm wrong, though, and most extensions wouldn't be relocatable?

Most are, but it's not for granted. See adminpack. Or earthdistance
that I had to make not-relocatable for lack of dependency support, as it
depends on cube and ALTER EXTENSION earthdistance SET SCHEMA foo would
have relocated cube too. We said dependency can wait until v2.

I don't see the benefit of having the 'relocatable' property optional in
the control file, but I see a huge drawback. Requiring it will force
extension authors to at least have a glance at the docs to understand
how to set it. It's important not to overlook it.

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

On Jan 3, 2011, at 12:23 PM, Dimitri Fontaine wrote:

> "David E. Wheeler" <david(at)kineticode(dot)com> writes:
>> The fact that the last two messages in the thread say something else
>> does not mean that they represent the consensus.
>
> Yeah, but as I'm the one writing the code, I gave myself more than one
> vote. And did consider the alternatives but didn't like them so much.

Just so long as you're aware that you might get more challenges on this going forward.

> Most are, but it's not for granted. See adminpack. Or earthdistance
> that I had to make not-relocatable for lack of dependency support, as it
> depends on cube and ALTER EXTENSION earthdistance SET SCHEMA foo would
> have relocated cube too. We said dependency can wait until v2.
>
> I don't see the benefit of having the 'relocatable' property optional in
> the control file, but I see a huge drawback. Requiring it will force
> extension authors to at least have a glance at the docs to understand
> how to set it. It's important not to overlook it.

I guess. I'll have to think about how to support it in PGXN, though. And the upgrade keys if they stay in.

David

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> Just so long as you're aware that you might get more challenges on this going forward.

Sure, thanks for the reminder. That said I also remember the reaction
when I used to scan the SHARE/contrib directory to find the extension
control file having the right name property, and I don't see scanning
the same directory in order to find out which upgrade file to consider
depending on several parts of its name as so different.

Current code allows you to use the same upgrade script for more than one
source version, and does so in a way that it's easy to determine which
upgrade file to seek for.

>> I don't see the benefit of having the 'relocatable' property optional in
>> the control file, but I see a huge drawback. Requiring it will force
>> extension authors to at least have a glance at the docs to understand
>> how to set it. It's important not to overlook it.
>
> I guess. I'll have to think about how to support it in PGXN, though. And the upgrade keys if they stay in.

Disclaimer: the following is based on my understanding of how you want
to bundle things, from several discussions we had together at pubs or
on IRC, please don't read further if you're changed your mind about
generating the control file from your PGXN YAML specification.

Well, I think you're having a dependency inversion problem here. PGXN
depends on extensions, not the other way round. Also, I really expect
the extension facility to be mainly used for internal proprietary code,
mainly procedure collections, and only occasionaly for publishing Open
Source components.

So you should be considering the control file as an input to your
processes, a source file, not something that your service will hide for
extension authors: there's no benefit that I can see in doing so.

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

On Jan 4, 2011, at 12:46 AM, Dimitri Fontaine wrote:

> "David E. Wheeler" <david(at)kineticode(dot)com> writes:
>> Just so long as you're aware that you might get more challenges on this going forward.
>
> Sure, thanks for the reminder. That said I also remember the reaction
> when I used to scan the SHARE/contrib directory to find the extension
> control file having the right name property, and I don't see scanning
> the same directory in order to find out which upgrade file to consider
> depending on several parts of its name as so different.

Silly programmer! You don't have to do that yourself! You can teach the computer to do it for you. It's very good at that sort of thing!

> Current code allows you to use the same upgrade script for more than one
> source version, and does so in a way that it's easy to determine which
> upgrade file to seek for.

As Tom pointed out, you can do the same with naming conventions by having scripts \i each other as appropriate.

>> I guess. I'll have to think about how to support it in PGXN, though. And the upgrade keys if they stay in.
>
> Disclaimer: the following is based on my understanding of how you want
> to bundle things, from several discussions we had together at pubs or
> on IRC, please don't read further if you're changed your mind about
> generating the control file from your PGXN YAML specification.

s/YAML/JSON/, and okay. :-)

> Well, I think you're having a dependency inversion problem here. PGXN
> depends on extensions, not the other way round.

What? That makes no sense, so I must be misunderstanding what you're trying to say.

> Also, I really expect
> the extension facility to be mainly used for internal proprietary code,
> mainly procedure collections, and only occasionaly for publishing Open
> Source components.

This is because you're not a Perl programmer. See CPAN.

> So you should be considering the control file as an input to your
> processes, a source file, not something that your service will hide for
> extension authors: there's no benefit that I can see in doing so.

I know, but then you're not a CPAN guy. You're a Debian package guy. It's hardly surprising that we'll have inverted views of this sort of thing. Frankly, I think that you might find StackBuilder a better fit with your world view.

http://pgfoundry.org/projects/stackbuilder/

Best,

David

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> As Tom pointed out, you can do the same with naming conventions by having scripts \i each other as appropriate.

This is a deprecated idea, though. We're talking about the
pg_execute_from_file() patch that has been applied, but without the
pg_execute_sql_file() function. So that part is internal to the backend
extension code and not available from SQL anymore.

There's no consensus to publish a bakend \i like function. So there's
no support for this upgrade script organizing you're promoting. Unless
the consensus changes again (but a commit has been done).

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

On Jan 4, 2011, at 11:48 AM, Dimitri Fontaine wrote:

>> As Tom pointed out, you can do the same with naming conventions by having scripts \i each other as appropriate.
>
> This is a deprecated idea, though. We're talking about the
> pg_execute_from_file() patch that has been applied, but without the
> pg_execute_sql_file() function. So that part is internal to the backend
> extension code and not available from SQL anymore.
>
> There's no consensus to publish a bakend \i like function. So there's
> no support for this upgrade script organizing you're promoting. Unless
> the consensus changes again (but a commit has been done).

To be clear, consensus was not reached, by my reading. It may be that it makes sense to restore pg_execute_sql_file(), perhaps to run only in the context of ALTER EXTENSION.

Just to be clear where I'm coming from, as an extension developer, I would like PostgreSQL extensions to:

* Prefer convention over configuration
* Not make me do more work that the computer can do

Best,

David

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> * Prefer convention over configuration

The previous idea about the convention is not flying well with the very
recent proposal of ALTER EXTENSION ... UPGRADE TO VERSION ..., because
it would certainly require that the extension's name include its major
version number, like debian is doing for a number of packages.

Also, how are PostGIS 1.4 and 1.5 (and 2.0) packaged nowadays?

> * Not make me do more work that the computer can do

No computer will guess reliably which upgrade file to apply given the
currently installed version and the newer one, as soon as the same file
can get used for more than a single combination of those two strings.

I much prefer to avoid shipping that many files, and thinks that even in
the worst case where you have to add a setup line per supported upgrade
setup, the control file support for that is better.

Now I perfectly understand that there's more to this world than my eyes
can see, that's why we're talking about alternatives.

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

On Jan 4, 2011, at 12:05 PM, Dimitri Fontaine wrote:

> "David E. Wheeler" <david(at)kineticode(dot)com> writes:
>> * Prefer convention over configuration
>
> The previous idea about the convention is not flying well with the very
> recent proposal of ALTER EXTENSION ... UPGRADE TO VERSION ..., because
> it would certainly require that the extension's name include its major
> version number, like debian is doing for a number of packages.

No, just the file.

> Also, how are PostGIS 1.4 and 1.5 (and 2.0) packaged nowadays?

Tarballs.

>> * Not make me do more work that the computer can do
>
> No computer will guess reliably which upgrade file to apply given the
> currently installed version and the newer one, as soon as the same file
> can get used for more than a single combination of those two strings.

Why not? Version numbers would have to be part of the file names. The only wrinkle is being able to properly order version numbers, and we could address that by requiring a specific version format. Tom suggested integers; I suggested semantic versions.

> I much prefer to avoid shipping that many files, and thinks that even in
> the worst case where you have to add a setup line per supported upgrade
> setup, the control file support for that is better.

Well, for a version that requires no upgrade script, there just wouldn't be one.

It's a matter of taste.

> Now I perfectly understand that there's more to this world than my eyes
> can see, that's why we're talking about alternatives.

You are?

Best,

David

On Tue, Jan 4, 2011 at 2:48 PM, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr> wrote:
> "David E. Wheeler" <david(at)kineticode(dot)com> writes:
>> As Tom pointed out, you can do the same with naming conventions by having scripts \i each other as appropriate.
>
> This is a deprecated idea, though.  We're talking about the
> pg_execute_from_file() patch that has been applied, but without the
> pg_execute_sql_file() function.  So that part is internal to the backend
> extension code and not available from SQL anymore.
>
> There's no consensus to publish a bakend \i like function.  So there's
> no support for this upgrade script organizing you're promoting.  Unless
> the consensus changes again (but a commit has been done).

My understanding of the consensus is that it wasn't felt necessary for
the purpose for which it was proposed. I think it could be
re-proposed with a different argument and very possibly accepted.

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

On Jan 5, 2011, at 10:05 AM, Robert Haas wrote:

>> There's no consensus to publish a bakend \i like function. So there's
>> no support for this upgrade script organizing you're promoting. Unless
>> the consensus changes again (but a commit has been done).
>
> My understanding of the consensus is that it wasn't felt necessary for
> the purpose for which it was proposed. I think it could be
> re-proposed with a different argument and very possibly accepted.

+1 Yes, exactly.

Best,

David

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> My understanding of the consensus is that it wasn't felt necessary for
> the purpose for which it was proposed. I think it could be
> re-proposed with a different argument and very possibly accepted.

Sure. I'd still prefer us to adopt the solution I've been promoting,
obviously, which I think has more merits. Namely no directory scanning,
easy to support extension names such as postgis-1.5, and easy to support
for a single upgrade file supporting upgrades from more than a single
version, and bypassing entirely the need to know what version numbering
scheme is in use: you just don't need to know how to compute previous or
next version number.

Now it's all about tradeoffs, and I'm just trying to explain what the
one I'm doing here seems to me to have lot of sense.

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