Thread: 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
Attachment
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 controlfile 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@kineticode.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@2ndQuadrant.fr> writes:
> "David E. Wheeler" <david@kineticode.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@sss.pgh.pa.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@2ndQuadrant.fr> writes:
> Tom Lane <tgl@sss.pgh.pa.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@sss.pgh.pa.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@2ndQuadrant.fr> writes:
> Tom Lane <tgl@sss.pgh.pa.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.inand not .control. Best, David
On Fri, Dec 10, 2010 at 12:30 PM, Tom Lane <tgl@sss.pgh.pa.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@gmail.com> writes:
> On Fri, Dec 10, 2010 at 12:30 PM, Tom Lane <tgl@sss.pgh.pa.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, thenrun CREATE EXTENSION to get the latest SQL script to run? But then all the objects already exist… Best, David
Tom Lane <tgl@sss.pgh.pa.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@kineticode.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@2ndQuadrant.fr> writes:
> Tom Lane <tgl@sss.pgh.pa.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@sss.pgh.pa.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@agliodbs.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@agliodbs.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@2ndQuadrant.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 bothfoo_upgrade.11.13.sqlfoo_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@commandprompt.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@sss.pgh.pa.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@agliodbs.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@agliodbs.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@2ndquadrant.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@highrise.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@kineticode.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@kineticode.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 justlike 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 barrierto 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@kineticode.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
"David E. Wheeler" <david@kineticode.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
Josh Berkus <josh@agliodbs.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
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@kineticode.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-standardversion numbering IMHO. YOu can use any characters you want in the version string. The upgrade file namessimply 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@sss.pgh.pa.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@2ndQuadrant.fr> writes:
> "David E. Wheeler" <david@kineticode.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@sss.pgh.pa.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 withthe 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 numbersused 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 thananother number. Best, David
"David E. Wheeler" <david@kineticode.com> writes:
> Yeah, it should be *to* 1.12. FWIW, this is how Bricolage upgrade scripts are handled: version-string-named
directorieswith 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 directorieswith 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@kineticode.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
directorieswith 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@kineticode.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 maintenancework for a rapidly-evolving extension. I doubt I would ever have got very far with pgTAP if I'd had to do somethinglike 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@agliodbs.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@sss.pgh.pa.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@2ndQuadrant.fr> writes: > > Tom Lane <tgl@sss.pgh.pa.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@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.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 toupgrade 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
whatto 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@sai.msu.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@kineticode.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@highrise.ca command like a king, http://www.highrise.ca/ work like a slave.
Aidan Van Dyk <aidan@highrise.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@2ndquadrant.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@highrise.ca command like a king, http://www.highrise.ca/ work like a slave.
Aidan Van Dyk <aidan@highrise.ca> writes:
> On Sat, Dec 11, 2010 at 4:35 PM, David E. Wheeler <david@kineticode.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@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Tom Lane <tgl@sss.pgh.pa.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 whatto 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@sai.msu.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian <bruce@momjian.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@momjian.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 forthem: 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@sss.pgh.pa.us> wrote: > Bruce Momjian <bruce@momjian.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@gmail.com> writes: > On Wed, Dec 29, 2010 at 3:23 PM, Tom Lane <tgl@sss.pgh.pa.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@.lo set extension lo; alter function @extschema@.lo_oid(lo) set extension lo; alter function@extschema@.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@.lo set extension lo; > alter function @extschema@.lo_oid(lo) set extension lo; > alter function @extschema@.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@kineticode.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 upgradescripts. Best, David
"David E. Wheeler" <david@kineticode.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@kineticode.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
upgradescripts.
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. Maybeit 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 configurationin 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@kineticode.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 havea "fixed_schema" control key or something? Either will work, just trying to find the likely convention to avoid configurationin 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@kineticode.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@kineticode.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 hadtogether at pubs or on IRC, please don't read further if you're changed your mind about generating the control file fromyour 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@kineticode.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 thatsort 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 viewsof 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@kineticode.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(), perhapsto 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@kineticode.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@kineticode.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 versionnumbers, and we could address that by requiring a specific version format. Tom suggested integers; I suggested semanticversions. > 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@2ndquadrant.fr> wrote: > "David E. Wheeler" <david@kineticode.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@gmail.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