Re: ALTER EXTENSION UPGRADE, v3

Hi,

PFA version 3 of the ALTER EXTENSION PATCH, cleaned and merged against
recent HEAD and extension's branch from which I just produced the v30
patch.

It includes a way to upgrade "from null", that is from pre-9.1, and the
specific upgrade files to achieve that for all contribs. That goes like
this:

dim=# \i ~/pgsql/exts/share/contrib/lo.sql
CREATE DOMAIN
CREATE FUNCTION
CREATE FUNCTION
dim=# create wrapper extension lo;
CREATE EXTENSION
dim=# alter extension lo upgrade;
ALTER EXTENSION
dim=# alter extension lo set schema utils;
ALTER EXTENSION
dim=# \dx lo
Objects in extension "lo"
Object Description
---------------------------------
function utils.lo_manage()
function utils.lo_oid(utils.lo)
type utils.lo
(3 rows)

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

On Wed, Feb 2, 2011 at 03:21, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr> wrote:
> PFA version 3 of the ALTER EXTENSION PATCH, cleaned and merged against
> recent HEAD and extension's branch from which I just produced the v30
> patch.

Excuse me for asking, but could you explain what is the purpose?
Which is true, "upgrade to 9.1 from past versions" or "upgrade
from 9.1 to future versions"? Also, how much advantage will we
have compared with uninstall_MODULE.sql + CREATE EXTENSION?

In my understanding, the patch does two things:
1. Add ALTER object SET EXTENSION
2. Add MODULE.upgrade.sql script for each contrib module

#1 seems reasonable as a supplement for CREATE EXTENSION patch,
but we might need not only "attach" but also "detach".

I guess #2 is much more difficult than expected because we might
upgrade databases from older versions. Will we need upgrade script
for each supported versions? -- if so, it would be nightmare...

--
Itagaki Takahiro

Itagaki Takahiro <itagaki(dot)takahiro(at)gmail(dot)com> writes:
> Excuse me for asking, but could you explain what is the purpose?
> Which is true, "upgrade to 9.1 from past versions" or "upgrade
> from 9.1 to future versions"? Also, how much advantage will we
> have compared with uninstall_MODULE.sql + CREATE EXTENSION?

Both are "true" use cases and supported in the code.

The goal is to be able to manage extensions upgrading. This is done by
running a script the author provides. To know which script to run, you
need to know the currently installed extension version, the available
version, and determine from that the script filename. That's what the
new control file options are about.

Now that you can upgrade extensions to their next versions, what about
migrating from an existing set of objects towards having an extension?
This use case happens either when upgrading from pre-9.1 or when you're
working on an in-house extension. At first it's not an extension, you
just CREATE FUNCTION and CREATE VIEW. The day you decide to properly
package it, you want to be able to do that without the hassle of
DROP'ing all those objects that your production is depending on.

> In my understanding, the patch does two things:
> 1. Add ALTER object SET EXTENSION
> 2. Add MODULE.upgrade.sql script for each contrib module

The patch also add new options in the control file so that it's possible
to do ALTER EXTENSION foo UPGRADE;. That's the main goal.

> #1 seems reasonable as a supplement for CREATE EXTENSION patch,
> but we might need not only "attach" but also "detach".

I didn't think about "detach", I'm not sure I see the use case…

> I guess #2 is much more difficult than expected because we might
> upgrade databases from older versions. Will we need upgrade script
> for each supported versions? -- if so, it would be nightmare...

It's not about upgrading major versions, it's about upgrading
extensions. The only time you will need to run the scripts in the patch
is e.g. when upgrading the extension hstore from NULL to 1.0. Once
done, hstore is registered as an extension, you're done. No need to
redo that or maintain the upgrade script for 9.1 to 9.2.

We will have to provide some other scripts when upgrade hstore from 1.0
to 1.1, whenever that happens (minor upgrades, major upgrades, etc).

I hope to make the case clear enough…

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

On Wed, Feb 2, 2011 at 20:29, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr> wrote:
> I didn't think about "detach", I'm not sure I see the use case…

The latest extension might drop some functions.

> It's not about upgrading major versions, it's about upgrading
> extensions.  The only time you will need to run the scripts in the patch
> is e.g. when upgrading the extension hstore from NULL to 1.0.  Once
> done, hstore is registered as an extension, you're done.  No need to
> redo that or maintain the upgrade script for 9.1 to 9.2.

I'm still not clear what "upgrade" means. if module authors wrote
functions with C, they can just replace .so to upgrade. If with
SQL or PL/pgSQL, they should execute CREATE OR REPLACE FUNCTION.

The patch seems useful to upgrade from NULL to 1.0, but I cannot
imagine how it work for cases from 1.0 to higher versions.
For example, if we have 3 versions of a module below:
NULL unmanaged functions only
v1 EXTENSION support with an additional function
v2 EXTENSION support with another function.
How do we write upgrading scripts for NULL=>v1, NULL=>v2, and v1=>v2 ?

--
Itagaki Takahiro

Itagaki Takahiro <itagaki(dot)takahiro(at)gmail(dot)com> writes:
> The latest extension might drop some functions.

Then the upgrade script contains the DROP commands.

> I'm still not clear what "upgrade" means. if module authors wrote
> functions with C, they can just replace .so to upgrade. If with
> SQL or PL/pgSQL, they should execute CREATE OR REPLACE FUNCTION.

When do you execute those statements? Certainly, you want the user to
issue ALTER EXTENSION foo UPGRADE and be done with it.

> The patch seems useful to upgrade from NULL to 1.0, but I cannot
> imagine how it work for cases from 1.0 to higher versions.
> For example, if we have 3 versions of a module below:
> NULL unmanaged functions only
> v1 EXTENSION support with an additional function
> v2 EXTENSION support with another function.
> How do we write upgrading scripts for NULL=>v1, NULL=>v2, and v1=>v2 ?

Well, you write 3 scripts.

Let's consider an example, the lo contrib, with its 3 objects:

CREATE DOMAIN lo AS pg_catalog.oid;
CREATE OR REPLACE FUNCTION lo_oid(lo) …
CREATE OR REPLACE FUNCTION lo_manage() …

Now, the upgrade script from version NULL to 1.0 is

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

The upgrade script from version 1.0 to 2.0 is, let's say:

CREATE OR REPLACE FUNCTION @extschema(at)(dot)lo_newfunc() …

So the upgrade script from version NULL to 2.0 is:

alter domain @extschema(at)(dot)lo set extension lo;
alter function @extschema(at)(dot)lo_oid(lo) set extension lo;
alter function @extschema(at)(dot)lo_manage() set extension lo;
CREATE OR REPLACE FUNCTION @extschema(at)(dot)lo_newfunc() …

If as an extension author you're kind enough to provide all those 3
scripts and the upgrade setup in the control file, then the user can
issue ALTER EXTENSION lo UPGRADE; and have all your cases covered
automatically.

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

On Feb 2, 2011, at 6:45 AM, Dimitri Fontaine wrote:

> Well, you write 3 scripts.
>
> Let's consider an example, the lo contrib, with its 3 objects:
>
> CREATE DOMAIN lo AS pg_catalog.oid;
> CREATE OR REPLACE FUNCTION lo_oid(lo) …
> CREATE OR REPLACE FUNCTION lo_manage() …
>
> Now, the upgrade script from version NULL to 1.0 is
>
> alter domain @extschema(at)(dot)lo set extension lo;
> alter function @extschema(at)(dot)lo_oid(lo) set extension lo;
> alter function @extschema(at)(dot)lo_manage() set extension lo;
>
> The upgrade script from version 1.0 to 2.0 is, let's say:
>
> CREATE OR REPLACE FUNCTION @extschema(at)(dot)lo_newfunc() …
>
> So the upgrade script from version NULL to 2.0 is:
>
> alter domain @extschema(at)(dot)lo set extension lo;
> alter function @extschema(at)(dot)lo_oid(lo) set extension lo;
> alter function @extschema(at)(dot)lo_manage() set extension lo;
> CREATE OR REPLACE FUNCTION @extschema(at)(dot)lo_newfunc() …
>
> If as an extension author you're kind enough to provide all those 3
> scripts and the upgrade setup in the control file, then the user can
> issue ALTER EXTENSION lo UPGRADE; and have all your cases covered
> automatically.

As an extension author, I can't emphasize enough how much I hate the redundancy of this approach.

Best,

David

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> On Feb 2, 2011, at 6:45 AM, Dimitri Fontaine wrote:
>
>> Well, you write 3 scripts.
>>
>> Let's consider an example, the lo contrib, with its 3 objects:
>>
>> CREATE DOMAIN lo AS pg_catalog.oid;
>> CREATE OR REPLACE FUNCTION lo_oid(lo) …
>> CREATE OR REPLACE FUNCTION lo_manage() …
>>
>> Now, the upgrade script from version NULL to 1.0 is
>>
>> alter domain @extschema(at)(dot)lo set extension lo;
>> alter function @extschema(at)(dot)lo_oid(lo) set extension lo;
>> alter function @extschema(at)(dot)lo_manage() set extension lo;
>>
>> The upgrade script from version 1.0 to 2.0 is, let's say:
>>
>> CREATE OR REPLACE FUNCTION @extschema(at)(dot)lo_newfunc() …
>>
>> So the upgrade script from version NULL to 2.0 is:
>>
>> alter domain @extschema(at)(dot)lo set extension lo;
>> alter function @extschema(at)(dot)lo_oid(lo) set extension lo;
>> alter function @extschema(at)(dot)lo_manage() set extension lo;
>> CREATE OR REPLACE FUNCTION @extschema(at)(dot)lo_newfunc() …
>>
>> If as an extension author you're kind enough to provide all those 3
>> scripts and the upgrade setup in the control file, then the user can
>> issue ALTER EXTENSION lo UPGRADE; and have all your cases covered
>> automatically.
>
> As an extension author, I can't emphasize enough how much I hate the redundancy of this approach.

Well, fair enough I suppose. Or it would be if you gave me an
alternative that provides a simpler way to support those 3 upgrades.

Of course if that's too much for you, you can also choose to only
support upgrades one versions at a time and provide only two scripts.
Note also that I don't recall of any proposal on the table that would
help with that situation, so I'm all ears.

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

On Feb 2, 2011, at 9:03 AM, Dimitri Fontaine wrote:

> Well, fair enough I suppose. Or it would be if you gave me an
> alternative that provides a simpler way to support those 3 upgrades.

I did: a naming convention with upgrade scripts that have the version number in them. You rejected it.

> Of course if that's too much for you, you can also choose to only
> support upgrades one versions at a time and provide only two scripts.
> Note also that I don't recall of any proposal on the table that would
> help with that situation, so I'm all ears.

http://archives.postgresql.org/pgsql-hackers/2011-01/msg00296.php

Best,

David

"David E. Wheeler" <david(at)kineticode(dot)com> writes:

> On Feb 2, 2011, at 9:03 AM, Dimitri Fontaine wrote:
>
>> Well, fair enough I suppose. Or it would be if you gave me an
>> alternative that provides a simpler way to support those 3 upgrades.
>
> I did: a naming convention with upgrade scripts that have the version
> number in them. You rejected it.

I'm sorry, I'm not following. You're proposing to pick one file or
another depending on its name. You're not proposing to have less than
three files to handle three upgrade setups. You still have to produce
the exact same file set.

The only difference is that the core code, in your proposal, has to know
what is a version number and where to find it in the file names, whereas
in mine the core code does not have to assume anything at all about what
version numbers look like. Nor to know how do they compare.

Oh, and in my current proposal and code, the author can reuse the same
file more than once for some upgrade setups, too.

>> Of course if that's too much for you, you can also choose to only
>> support upgrades one versions at a time and provide only two scripts.
>> Note also that I don't recall of any proposal on the table that would
>> help with that situation, so I'm all ears.
>
> http://archives.postgresql.org/pgsql-hackers/2011-01/msg00296.php

I see there no solution to your reaction here. Please take the time to
tell us more about what exactly it is that you hated, and how to make it
lovely. We won't make any progress with your current commenting style.

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

On Feb 2, 2011, at 9:19 AM, Dimitri Fontaine wrote:

> I see there no solution to your reaction here. Please take the time to
> tell us more about what exactly it is that you hated, and how to make it
> lovely. We won't make any progress with your current commenting style.

Here is your example of the two upgrade scripts:

> Now, the upgrade script from version NULL to 1.0 is
>
> alter domain @extschema(at)(dot)lo set extension lo;
> alter function @extschema(at)(dot)lo_oid(lo) set extension lo;
> alter function @extschema(at)(dot)lo_manage() set extension lo;
>
> The upgrade script from version 1.0 to 2.0 is, let's say:
>
> CREATE OR REPLACE FUNCTION @extschema(at)(dot)lo_newfunc() …
>
> So the upgrade script from version NULL to 2.0 is:
>
> alter domain @extschema(at)(dot)lo set extension lo;
> alter function @extschema(at)(dot)lo_oid(lo) set extension lo;
> alter function @extschema(at)(dot)lo_manage() set extension lo;
> CREATE OR REPLACE FUNCTION @extschema(at)(dot)lo_newfunc() …

They are identical except for the extra line in the second one. If I had, say 15 different versions of an extension, then I'd have 15 upgrade scripts. That's fine. But in your plan, the script to upgrade from version 1 to version 15 would have all the same code as the v14 script, plus any additional. The v14 script would have everything in v13. v13 would have everything in v12. With no support for the equivalent of psql's \i, that's extremely redundant and a huge PITA to maintain. Hence my hate.

My proposal would also have 15 upgrade scripts, but each one would only upgrade from the previous one. So to upgrade from v1 to v15, UPGRADE EXTENSION would run all of them. So v15 would only need to have deltas from v14. V14 would need only deltas from v13. Etc.

The number of upgrade script files is not the problem I have. It's the redundant content of those files that raises my ire.

If there was some way to get something like \i in your approach, that would satisfy me, as it would be only an extra line (at most) in each script. And then, as you note, the core wouldn't need the complexity of understanding version numbers, which I agree would be beneficial.

Best,

David

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> They are identical except for the extra line in the second one. If I
> had, say 15 different versions of an extension, then I'd have 15
> upgrade scripts. That's fine. But in your plan, the script to upgrade
> from version 1 to version 15 would have all the same code as the v14
> script, plus any additional. The v14 script would have everything in
> v13. v13 would have everything in v12. With no support for the
> equivalent of psql's \i, that's extremely redundant and a huge PITA to
> maintain. Hence my hate.

That's easy enough to manage in your Makefile, really:

upgrade.null-v15.sql: upgrade.v14.sql upgrade.v15.sql
cat upgrade.v14.sql upgrade.v15.sql > $@

There's a difference between what you maintain and what you ship.

> My proposal would also have 15 upgrade scripts, but each one would
> only upgrade from the previous one. So to upgrade from v1 to v15,
> UPGRADE EXTENSION would run all of them. So v15 would only need to
> have deltas from v14. V14 would need only deltas from v13. Etc.

What if you can reuse the later script for upgrading from any previous
version, like when the extension only contains CREATE OR REPLACE
statements (functions only extension, like adminpack).

I don't see benefits in your proposal.

> The number of upgrade script files is not the problem I have. It's the redundant content of those files that raises my ire.
>
> If there was some way to get something like \i in your approach, that would satisfy me, as it would be only an extra line (at most) in each script. And then, as you note, the core wouldn't need the complexity of understanding version numbers, which I agree would be beneficial.

It all comes down to the benefits. I don't see any in your proposal.
That might be just me though.

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

On Feb 2, 2011, at 10:04 AM, Dimitri Fontaine wrote:

> "David E. Wheeler" <david(at)kineticode(dot)com> writes:
>> They are identical except for the extra line in the second one. If I
>> had, say 15 different versions of an extension, then I'd have 15
>> upgrade scripts. That's fine. But in your plan, the script to upgrade
>> from version 1 to version 15 would have all the same code as the v14
>> script, plus any additional. The v14 script would have everything in
>> v13. v13 would have everything in v12. With no support for the
>> equivalent of psql's \i, that's extremely redundant and a huge PITA to
>> maintain. Hence my hate.
>
> That's easy enough to manage in your Makefile, really:
>
> upgrade.null-v15.sql: upgrade.v14.sql upgrade.v15.sql
> cat upgrade.v14.sql upgrade.v15.sql > $@

Sure, if you know Make really well. But then I need to add a line to the Makefile for every bloody upgrade script. Gross.

> There's a difference between what you maintain and what you ship.

Yes.

>> My proposal would also have 15 upgrade scripts, but each one would
>> only upgrade from the previous one. So to upgrade from v1 to v15,
>> UPGRADE EXTENSION would run all of them. So v15 would only need to
>> have deltas from v14. V14 would need only deltas from v13. Etc.
>
> What if you can reuse the later script for upgrading from any previous
> version, like when the extension only contains CREATE OR REPLACE
> statements (functions only extension, like adminpack).

I don't understand the question.

> I don't see benefits in your proposal.

The benefit is reduced redundancy.

> It all comes down to the benefits. I don't see any in your proposal.
> That might be just me though.

Could be.

Best,

David

On Wed, Feb 2, 2011 at 12:31 PM, David E. Wheeler <david(at)kineticode(dot)com> wrote:

>
> They are identical except for the extra line in the second one. If I had, say 15 different versions of an extension, then I'd have 15 upgrade scripts. That's fine. But in your plan, the script to upgrade from version 1 to version 15 would have all the same code as the v14 script, plus any additional. The v14 script would have everything in v13. v13 would have everything in v12. With no support for the equivalent of psql's \i, that's extremely redundant and a huge PITA to maintain. Hence my hate.
>
> My proposal would also have 15 upgrade scripts, but each one would only upgrade from the previous one. So to upgrade from v1 to v15, UPGRADE EXTENSION would run all of them. So v15 would only need to have deltas from v14. V14 would need only deltas from v13. Etc.

My concern with this approach (upgrade is forced through all
intermetiary versions) is that the shared libray now for version 15
*has* to have all the "intermediary" compatibility for *all* versions
in it. So it has to have functions with all symbols so the "CREATE
..." staements for all previous 15 versions can succeed.

With having the $old -> $new scripts, the new .so only needs to have
functions enough that the DROPs work, and the new CREATE... work.

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

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
>> upgrade.null-v15.sql: upgrade.v14.sql upgrade.v15.sql
>> cat upgrade.v14.sql upgrade.v15.sql > $@
>
> Sure, if you know Make really well. But then I need to add a line to
> the Makefile for every bloody upgrade script. Gross.

Either one line in the Makefile or a new file with the \i equivalent
lines, that would maybe look like:

SELECT pg_execute_sql_file('upgrade.v14.sql');
SELECT pg_execute_sql_file('upgrade.v15.sql');

So well… I don't see how you've made it less gross here.
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

On Feb 2, 2011, at 10:14 AM, Aidan Van Dyk wrote:

> My concern with this approach (upgrade is forced through all
> intermetiary versions) is that the shared libray now for version 15
> *has* to have all the "intermediary" compatibility for *all* versions
> in it. So it has to have functions with all symbols so the "CREATE
> ..." staements for all previous 15 versions can succeed.
>
> With having the $old -> $new scripts, the new .so only needs to have
> functions enough that the DROPs work, and the new CREATE... work.

Yeah, so that's another argument for some sort of include syntax, instead, so the upgrade scripts can include other scripts as appropriate.

Best,

David

On Feb 2, 2011, at 10:22 AM, Dimitri Fontaine wrote:

> Either one line in the Makefile or a new file with the \i equivalent
> lines, that would maybe look like:
>
> SELECT pg_execute_sql_file('upgrade.v14.sql');
> SELECT pg_execute_sql_file('upgrade.v15.sql');
>
> So well… I don't see how you've made it less gross here.

I suppose it depends on whether or not you prefer SQL to make. I know where my preferences are.

Best,

David

"David E. Wheeler" <david(at)kineticode(dot)com> writes:

> On Feb 2, 2011, at 10:14 AM, Aidan Van Dyk wrote:
>
>> My concern with this approach (upgrade is forced through all
>> intermetiary versions) is that the shared libray now for version 15
>> *has* to have all the "intermediary" compatibility for *all* versions
>> in it. So it has to have functions with all symbols so the "CREATE
>> ..." staements for all previous 15 versions can succeed.
>>
>> With having the $old -> $new scripts, the new .so only needs to have
>> functions enough that the DROPs work, and the new CREATE... work.
>
> Yeah, so that's another argument for some sort of include syntax,
> instead, so the upgrade scripts can include other scripts as
> appropriate.

That's just the opposite, really. Consider in-house extensions where
you perfectly know that you will only upgrade from the previous
version. There you only want to ship one upgrade script.

Anyway, it's high time that we see some other votes, I think both of us
explained only too many times what their own preferences are in terms of
what tools to use to maintain and package script files, and how.

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

On Wed, Feb 2, 2011 at 12:31 PM, David E. Wheeler <david(at)kineticode(dot)com> wrote:
> They are identical except for the extra line in the second one. If I had, say 15 different versions of an extension, then I'd have 15 upgrade scripts. That's fine. But in your plan, the script to upgrade from version 1 to version 15 would have all the same code as the v14 script, plus any additional. The v14 script would have everything in v13. v13 would have everything in v12. With no support for the equivalent of psql's \i, that's extremely redundant and a huge PITA to maintain. Hence my hate.

Stepping back from the implementation details and file naming
conventions a bit, it seems to me that when you do schema upgrades,
there are basically three possible things you might want to put in the
upgrade script:

1. SQL statements that you want to execute unconditionally, such as
(1a) CREATE OR REPLACE FUNCTION on something that has a compatible
signature in every prior release in which it exists, or (1b) CREATE
TABLE on a table that was added in the most recent release.
2. SQL statements that you want to execute if the version we're
upgrading *from* is older than X. For example, CREATE TABLE on a
table that was added in version 6 should be executed if we're coming
from a version less than 6, and skipped otherwise.
3. SQL statements that you want to execute if the version we're
upgrading *from* is between X and Y. This is less common, but you
sometimes need it. For example, in version 6 you added a table, but
by version 13 it wasn't needed any more so you removed it. The
upgrade script for version 17 should drop the table if we're coming
from a version between 6 and 12 (if we're coming from pre-6, it was
never created to begin with, and we don't want to drop an unrelated
table with the same name, and if we're coming from 13-16, it either
never existed or, depending on the history, some previous upgrade
dropped it).

So how could we provide this functionality? Dimitri's approach is
simple in concept, but it potentially requires a LOT of bookkeeping
when an extension has been around for a while, to make sure that all
of the upgrade files contain exactly the right combinations of stuff.
I've managed schema upgrades that went through dozens of versions, and
making sure that you can correctly upgrade from every previous version
to v48 is definitely going to be a challenge. David's approach makes
that a little simpler in some ways, but I think it falls down pretty
badly on point #3.

I'd actually be inclined to just have ONE upgrade file and invent some
kind of meta-language for controlling which statements get executed.
Just to pick a syntax that everyone will probably hate:

[..]
-- unconditional stuff

[..6]
-- stuff to do if coming from pre-7

[..]
-- some more unconditional stuff

[6..12]
-- stuff to do if coming from between 6 and 12

[..]
-- a few more unconditional things

You might all be either scoffing right now or laughing so hard there
are tears running down your face, but in my not insignificant
experience that's what real schema upgrade scripts need to cope with
in real-world situations, so I hereby pre-reject any comments of the
form "that should never be necessary in real life because..." and/or
"for that to be necessary you'd have to have done the following
bat-shit stupid thing".

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

Aidan Van Dyk <aidan(at)highrise(dot)ca> writes:
> My concern with this approach (upgrade is forced through all
> intermetiary versions) is that the shared libray now for version 15
> *has* to have all the "intermediary" compatibility for *all* versions
> in it. So it has to have functions with all symbols so the "CREATE
> ..." staements for all previous 15 versions can succeed.

Bear in mind though that the compatibility stubs only have to be stubs;
the C function needn't do anything except perhaps throw elog(ERROR).
This doesn't seem very onerous to me.

regards, tom lane

On 02/02/2011 08:22 PM, Dimitri Fontaine wrote:
> Either one line in the Makefile or a new file with the \i equivalent
> lines, that would maybe look like:
>
> SELECT pg_execute_sql_file('upgrade.v14.sql');
> SELECT pg_execute_sql_file('upgrade.v15.sql');
>
> So well… I don't see how you've made it less gross here.
Chaining the upgrade files should be relatively easy, if something like
pg_execute_sql_file would be available (actually it would need to be
pg_execute_extension_file so that @extschema@ would be substituted
correctly).

Example:

upgrade_from_1_0 = '1.0 => upgrade_from_1.0.sql'
upgrade_from_2_0 = '2.0 => upgrade_from_2.0.sql'
upgrade_from_3_0 = '3.0 => upgrade_from_3.0.sql'

upgrade_from_1.0.sql contents:
alter table foobar add column id2 integer;
pg_execute_extension_file('upgrade_from_2.0.sql');

upgrade_from_2.0.sql contents:
alter table foobar add column id3 integer;
pg_execute_extension_file('upgrade_from_3.0.sql');

...

So, when creating a new version you would need to update the main .sql
file, create a new upgrade file, and alter the
upgrade_from_previous_version.sql to include the new upgrade file. This
should be relatively easy to maintain. Also, this would give you the
freedom to not chain the files when that is not appropriate.

By the way, I saw that the character '.' is not allowed in the xxx part
of upgrade_from_xxx and this is not documented in the patch. What can be
in the xxx part, and is this documented somewhere else?

- Anssi

On 02/03/2011 12:23 AM, Robert Haas wrote:
> [..]
> -- unconditional stuff
>
> [..6]
> -- stuff to do if coming from pre-7
>
> [..]
> -- some more unconditional stuff
>
> [6..12]
> -- stuff to do if coming from between 6 and 12
>
> [..]
> -- a few more unconditional things
This might be a stupid idea, but how about introducing another
placeholder variable in addition to @extschema@ when upgrading, named
maybe @fromversion(at)(dot) Combined with do blocks and
pg_execute_extension_file this would allow doing the above stuff:

upgrade.sql contents:
[..]
-- uncoditional stuff
[..6]
DO $$
begin
if @fromversion@ matches [..6] then
pg_execute_extension_file('stuff to do if coming from pre-7-file');
end if;
end;
$$
language 'plpgsql';
...

- Anssi

Anssi Kääriäinen <anssi(dot)kaariainen(at)thl(dot)fi> writes:
> upgrade_from_2.0.sql contents:
> alter table foobar add column id3 integer;
> pg_execute_extension_file('upgrade_from_3.0.sql');
>
> ...
>
> So, when creating a new version you would need to update the main .sql file,
> create a new upgrade file, and alter the upgrade_from_previous_version.sql
> to include the new upgrade file. This should be relatively easy to
> maintain. Also, this would give you the freedom to not chain the files when
> that is not appropriate.

Again, why not, I think I can see how this would work out. What I don't
see is what is the gain compared to preparing the right files at make
time and only shipping "autonomous" files. I very much prefer to handle
a set of source SQL files and some cat a b c > ship.sql rules rather
than ship loads of files that all depends on each other.

> By the way, I saw that the character '.' is not allowed in the xxx part of
> upgrade_from_xxx and this is not documented in the patch. What can be in the
> xxx part, and is this documented somewhere else?

It has to be a valid configuration variable name, as any other GUC, and
it's not a placeholder (only those can contain dots). We're using the
same parser as for postgresql.conf and recovery.conf here. Not sure
where that is documented, though.

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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> Stepping back from the implementation details and file naming
> conventions a bit, it seems to me that when you do schema upgrades,
> there are basically three possible things you might want to put in the
> upgrade script:
[...]
> I've managed schema upgrades that went through dozens of versions, and
> making sure that you can correctly upgrade from every previous version
> to v48 is definitely going to be a challenge. David's approach makes
> that a little simpler in some ways, but I think it falls down pretty
> badly on point #3.

Where I agree with you is that we're talking about a complex problem,
and whatever tools we have to handle it will not make it any simpler.
The only thing that maybe could be made simpler is how to write the
solution, not how to design what it is.

> I'd actually be inclined to just have ONE upgrade file and invent some
> kind of meta-language for controlling which statements get executed.

I'm currently unimpressed by the idea of inventing new tools to solve a
fairly common problem. All the more when this syntax is based on SQL
when the problem space is all about composing script files. I see your
proposal is not so much SQL'ish, though.

All in all, I think we should postpone solving this problem to until we
have covered the basis, that is running 1 given determined script to
handle 1 specific upgrade that the DBA is driving. I think my proposal
covers that and avoids painting us into a corner should we ever decide
to walk the extra mile here.

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

This might be a silly idea, but I'm not sure if it was really
discussed with respect to extensions here before...

Why not use PL/pgSQL as install/upgrade script language,
specially now when it's included in the core by default?

This would allow for relatively straightforward inclusion
of conditional logic, etc in install/upgrade scripts.

All current contrib stuff runs great as PL/pgSQL, for example.

The @ placeholder syntax just seems outright hideous and doesn't
feel quite right either.

Also worth considering might be, how the proposed extension
system relates to SQL standard modules (ie. CREATE MODULE, etc),
which might be implemented in the future.
There are are some obvious overlaps (relocatable/fixed extensions vs.
modules with/without schemas, for example).

Also, consider the following case:
I have two different users, u1 and u2 with corresponding schemas, u1 and u2,
both using hstore, for example. Right now, I can load hstore.sql contrib
into both, u1 and u2 it all works great.
If I understand correctly, such an arrangement would not really be supported
with extensions as they stand now.

Ziga Kranjec,
developer, ljudmila.org

On Feb 3, 2011, at 9:28 AM, Anssi Kääriäinen wrote:

> On 02/03/2011 12:23 AM, Robert Haas wrote:
>> [..]
>> -- unconditional stuff
>>
>> [..6]
>> -- stuff to do if coming from pre-7
>>
>> [..]
>> -- some more unconditional stuff
>>
>> [6..12]
>> -- stuff to do if coming from between 6 and 12
>>
>> [..]
>> -- a few more unconditional things
> This might be a stupid idea, but how about introducing another placeholder variable in addition to @extschema@ when upgrading, named maybe @fromversion(at)(dot) Combined with do blocks and pg_execute_extension_file this would allow doing the above stuff:
>
> upgrade.sql contents:
> [..]
> -- uncoditional stuff
> [..6]
> DO $$
> begin
> if @fromversion@ matches [..6] then
> pg_execute_extension_file('stuff to do if coming from pre-7-file');
> end if;
> end;
> $$
> language 'plpgsql';
> ...
>
> - Anssi
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Feb 03, 2011 at 10:21:28AM +0200, Anssi Kääriäinen wrote:
> On 02/02/2011 08:22 PM, Dimitri Fontaine wrote:
>> Either one line in the Makefile or a new file with the \i equivalent
>> lines, that would maybe look like:
>>
>> SELECT pg_execute_sql_file('upgrade.v14.sql');
>> SELECT pg_execute_sql_file('upgrade.v15.sql');
>>
>> So well… I don't see how you've made it less gross here.
> Chaining the upgrade files should be relatively easy, if something like
> pg_execute_sql_file would be available (actually it would need to be
> pg_execute_extension_file so that @extschema@ would be substituted
> correctly).
>
> Example:
>
> upgrade_from_1_0 = '1.0 => upgrade_from_1.0.sql'
> upgrade_from_2_0 = '2.0 => upgrade_from_2.0.sql'
> upgrade_from_3_0 = '3.0 => upgrade_from_3.0.sql'

Hmm, how about allowing a list of files to execute? That allows the
developer to create modularized sql, and composite it in the config:

for a mythical version 4.0:

1.0 => add_foobar_table.sql new_method_baz.sql
2.0 => drop_method_baz.sql add_quuz_table.sql
# oops, we still needed this
3.0 => new_method_baz.sql

I know when I'm developing such upgrades, the code looks like that,
until I need to shoehorn them into the upgrade systems idea of version
numbers matching names to find scripts to run.

The advantage of this is that it keeps the logic for mapping version
to upgrades in the config: the upgrade scripts mearly handle the actual
SQL for doing a specific task, not a collection of tasks only related by
virtue of being released at the same time.

Ross
--
Ross Reedstrom, Ph.D. reedstrm(at)rice(dot)edu
Systems Engineer & Admin, Research Scientist phone: 713-348-6166
Connexions http://cnx.org fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE

On Thu, Feb 3, 2011 at 9:53 AM, Ross J. Reedstrom <reedstrm(at)rice(dot)edu> wrote:
>> Example:
>>
>> upgrade_from_1_0 = '1.0 => upgrade_from_1.0.sql'
>> upgrade_from_2_0 = '2.0 => upgrade_from_2.0.sql'
>> upgrade_from_3_0 = '3.0 => upgrade_from_3.0.sql'
>
> Hmm, how about allowing a list of files to execute? That allows the
> developer to create modularized sql, and composite it in the config:
>
> for a mythical version 4.0:
>
> 1.0 => add_foobar_table.sql new_method_baz.sql
> 2.0 => drop_method_baz.sql add_quuz_table.sql
> # oops, we still needed this
> 3.0 => new_method_baz.sql
>
> I know when I'm developing such upgrades, the code looks like that,
> until I need to shoehorn them into the upgrade systems idea of version
> numbers matching names to find scripts to run.

Good idea. My code looks that way too.

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

"Ross J. Reedstrom" <reedstrm(at)rice(dot)edu> writes:
> Hmm, how about allowing a list of files to execute? That allows the

Sure. I still don't see why doing it in the control file is better than
in the Makefile, even if it's already better than in the SQL script, at
least in terms of code to write to support the idea.

Speaking about which, using Make rules to prepare your upgrade files
from other pieces means no development at all on the backend side. You
can hardly beat that.

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

On Thu, Feb 03, 2011 at 04:31:08PM +0100, Dimitri Fontaine wrote:
> "Ross J. Reedstrom" <reedstrm(at)rice(dot)edu> writes:
> > Hmm, how about allowing a list of files to execute? That allows the
>
> Sure. I still don't see why doing it in the control file is better than
> in the Makefile, even if it's already better than in the SQL script, at
> least in terms of code to write to support the idea.

Because that's two places to touch that have to worry about mapping
versions to actions. Inside the config file I'm already going to have to
do that, and in mostly a trivial one-to-one mapping. The proposed
make rules are an example of the kind of 'make my code match what the
system wants' that I complained of.

> Speaking about which, using Make rules to prepare your upgrade files
> from other pieces means no development at all on the backend side. You
> can hardly beat that.

Yes, from the backend-developer's perspective. But not from the
extension-developer's perspective :-) And seriously, make is one of
those things that is supremely capable of doing lots of stuff, but is so
difficult to use correctly that everyone keeps reinventing newer wheels.
Seems this one isn't round enough.

In fact, doing it via make rules would still be available, if that's
what floats the particular developer's boat. more choices is good.

Ross
--
Ross Reedstrom, Ph.D. reedstrm(at)rice(dot)edu
Systems Engineer & Admin, Research Scientist phone: 713-348-6166
Connexions http://cnx.org fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE

On Thu, Feb 3, 2011 at 11:25 AM, Ross J. Reedstrom <reedstrm(at)rice(dot)edu> wrote:
>> Speaking about which, using Make rules to prepare your upgrade files
>> from other pieces means no development at all on the backend side.  You
>> can hardly beat that.
>
> Yes, from the backend-developer's perspective. But not from the
> extension-developer's perspective :-) And seriously, make is one of
> those things that is supremely capable of doing lots of stuff, but is so
> difficult to use correctly that everyone keeps reinventing newer wheels.
> Seems this one isn't round enough.

Not to mention the fact that make doesn't work on Windows, so any
extensions that rely on this will need hacks in the MSVC build system.

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

=?iso-8859-2?Q?=AEiga_Kranjec?= <ziga(at)ljudmila(dot)org> writes:
> This might be a silly idea, but I'm not sure if it was really
> discussed with respect to extensions here before...

yes, it was ...

> Why not use PL/pgSQL as install/upgrade script language,
> specially now when it's included in the core by default?

"Included by default" does not mean "required to be present".
It can be removed, and we can't have that mean breaking the extension
mechanism.

regards, tom lane

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> Yes, from the backend-developer's perspective. But not from the
>> extension-developer's perspective :-) And seriously, make is one of
>> those things that is supremely capable of doing lots of stuff, but is so
>> difficult to use correctly that everyone keeps reinventing newer wheels.
>> Seems this one isn't round enough.
>
> Not to mention the fact that make doesn't work on Windows, so any
> extensions that rely on this will need hacks in the MSVC build system.

Fair enough, so that's just me not seeing it. Now I agree that having
the right hand side of the format I proposed be an ordered list of files
rather than a single file is simple enough and comes with benefits.

The examples are using spaces as the separator, how friendly is that to
our windows users? Maybe using coma instead would be better?

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

On Feb3, 2011, at 16:31 , Dimitri Fontaine wrote:
> "Ross J. Reedstrom" <reedstrm(at)rice(dot)edu> writes:
>> Hmm, how about allowing a list of files to execute? That allows the
>
> Sure. I still don't see why doing it in the control file is better than
> in the Makefile, even if it's already better than in the SQL script, at
> least in terms of code to write to support the idea.
>
> Speaking about which, using Make rules to prepare your upgrade files
> from other pieces means no development at all on the backend side. You
> can hardly beat that.

I fully agree. The extension infrastructure should provide basic support
for upgrades, not every kind of bell and whistle one could possible think of.

The bells and whistles can then be provided by the system used to *build* the
extension. Not only does this keep the core infrastructure manageable, it also
allows different tools to generate the update scripts to exist, each catering
to the needs of different kinds of extensions.

best regards,
Florian Pflug

On Thu, Feb 3, 2011 at 11:53 AM, Dimitri Fontaine
<dimitri(at)2ndquadrant(dot)fr> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> Yes, from the backend-developer's perspective. But not from the
>>> extension-developer's perspective :-) And seriously, make is one of
>>> those things that is supremely capable of doing lots of stuff, but is so
>>> difficult to use correctly that everyone keeps reinventing newer wheels.
>>> Seems this one isn't round enough.
>>
>> Not to mention the fact that make doesn't work on Windows, so any
>> extensions that rely on this will need hacks in the MSVC build system.
>
> Fair enough, so that's just me not seeing it.  Now I agree that having
> the right hand side of the format I proposed be an ordered list of files
> rather than a single file is simple enough and comes with benefits.
>
> The examples are using spaces as the separator, how friendly is that to
> our windows users?  Maybe using coma instead would be better?

Comma would be better. There is even some backend code that will
tokenize on it, I think.

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

Florian Pflug <fgp(at)phlo(dot)org> writes:
> I fully agree. The extension infrastructure should provide basic support
> for upgrades, not every kind of bell and whistle one could possible think of.

Maybe somewhere around here we should stop and ask why we are bothering
with any of this. The original idea for an extension concept was that
(1) some collection of objects could be designated as a module
(2) pg_dump would be taught to dump "LOAD MODULE foo" instead of the
individual objects
(3) the way you'd do an upgrade is to dump and reload into a database
that has available a newer definition of the module's content.

Given that pg_upgrade is now considered a supported piece of the system,
ISTM that most real-world upgrade scenarios will be accomplished with
pg_upgrade relying on provision (3). It looks to me like we're talking
about adding a large amount of complication --- both for the core
database and for module authors --- in order to provide a duplicate
solution for that. Why should we bother? Especially, why should we
bother in version 1 of the feature? This could all be added later if
we determine there's really sufficient demand, but right now we have
no experience to show whether there is demand or not.

regards, tom lane

On Feb 3, 2011, at 9:38 AM, Tom Lane wrote:

> Given that pg_upgrade is now considered a supported piece of the system,
> ISTM that most real-world upgrade scenarios will be accomplished with
> pg_upgrade relying on provision (3). It looks to me like we're talking
> about adding a large amount of complication --- both for the core
> database and for module authors --- in order to provide a duplicate
> solution for that. Why should we bother? Especially, why should we
> bother in version 1 of the feature? This could all be added later if
> we determine there's really sufficient demand, but right now we have
> no experience to show whether there is demand or not.

Given the level of disagreement, I think that leaving upgrades aside for now may be prudent, especially since there are other ways to do it (none very convenient, but no worse than what we have right now, and in the case of pg_dump, better).

I think we will need to come back to it before, long, however, because many extensions are released far more often than major versions of PostgreSQL. So while one might run pg_upgrade, at most, about once every 12-18 months, they will often want to take advantage of the features of extensions on a much more ambitious release schedule.

Extension upgrades need to be done eventually to make it easier to manage extension release schedules independent of PostgreSQL core upgrades. Otherwise, you're just going to get more patches for contrib.

Best,

David

On Thu, Feb 3, 2011 at 12:38 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Florian Pflug <fgp(at)phlo(dot)org> writes:
>> I fully agree. The extension infrastructure should provide basic support
>> for upgrades, not every kind of bell and whistle one could possible think of.
>
> Maybe somewhere around here we should stop and ask why we are bothering
> with any of this.  The original idea for an extension concept was that
> (1) some collection of objects could be designated as a module
> (2) pg_dump would be taught to dump "LOAD MODULE foo" instead of the
> individual objects
> (3) the way you'd do an upgrade is to dump and reload into a database
> that has available a newer definition of the module's content.
>
> Given that pg_upgrade is now considered a supported piece of the system,
> ISTM that most real-world upgrade scenarios will be accomplished with
> pg_upgrade relying on provision (3).  It looks to me like we're talking
> about adding a large amount of complication --- both for the core
> database and for module authors --- in order to provide a duplicate
> solution for that.  Why should we bother?  Especially, why should we
> bother in version 1 of the feature?  This could all be added later if
> we determine there's really sufficient demand, but right now we have
> no experience to show whether there is demand or not.

I think you can pretty much take it to the bank that there will be
demand. This is an important, real-world problem.

That having been said, I'm not 100% convinced that the main extensions
patch is ready for prime-time, and I'm even less convinced that the
upgrade patch is anywhere the point where we want to commit to it
long-term. So I would have no qualms about punting it out to 9.2.

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

On Feb 3, 2011, at 9:54 AM, Robert Haas wrote:

> I think you can pretty much take it to the bank that there will be
> demand. This is an important, real-world problem.
>
> That having been said, I'm not 100% convinced that the main extensions
> patch is ready for prime-time, and I'm even less convinced that the
> upgrade patch is anywhere the point where we want to commit to it
> long-term. So I would have no qualms about punting it out to 9.2.

I think that the core extension stuff and pg_dump stuff is pretty close. It's the upgrade stuff that's thorny. And really, this is a pretty well-known problem. Surely someone has done some research to solve it?

Anyway, IMHO, try to get CREATE EXTENSION into 9.1, perhaps put off ALTER EXTENSION UPGRADE to 9.2.

Best,

David

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> I think we will need to come back to it before, long, however, because many extensions are released far more often than major versions of PostgreSQL. So while one might run pg_upgrade, at most, about once every 12-18 months, they will often want to take advantage of the features of extensions on a much more ambitious release schedule.

Well, pg_upgrade is designed to work within a major-version series, eg
you could do a 9.1-to-9.1 upgrade if you needed to install a newer
version of an extension. Admittedly, this is swinging a rather larger
hammer than "apply an upgrade script" would entail. But I'm still not
convinced that we need to expend a great deal of work on making that
process a tad more efficient.

Now having said that, it does occur to me that there is an upgrade-ish
scenario that every user is going to hit immediately, which is how to
get from an existing installation with a pile of "loose" objects created
by one or more contrib modules to a state where those objects are
understood to be parts of modules. But that is a special case that
perhaps deserves a special-case solution, rather than inventing a very
large wheel.

regards, tom lane

On Feb 3, 2011, at 10:07 AM, Tom Lane wrote:

> Well, pg_upgrade is designed to work within a major-version series, eg
> you could do a 9.1-to-9.1 upgrade if you needed to install a newer
> version of an extension. Admittedly, this is swinging a rather larger
> hammer than "apply an upgrade script" would entail.

Dude. That's a frigging piledriver!

> But I'm still not
> convinced that we need to expend a great deal of work on making that
> process a tad more efficient.

Agreed. I would handle simple extension upgrades not with pg_upgrade, but the same way I do now. Think about how one currently jumps from PostGIS 1.4 to 1.5.

Best,

David

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> Maybe somewhere around here we should stop and ask why we are bothering
> with any of this. The original idea for an extension concept was that
> (1) some collection of objects could be designated as a module
> (2) pg_dump would be taught to dump "LOAD MODULE foo" instead of the
> individual objects
> (3) the way you'd do an upgrade is to dump and reload into a database
> that has available a newer definition of the module's content.

That upgrade solution is fine in some cases for major upgrades. That's
not what we're talking about here, though. Extension release schedule
is to be quite different from PostgreSQL one.

> Given that pg_upgrade is now considered a supported piece of the system,
> ISTM that most real-world upgrade scenarios will be accomplished with
> pg_upgrade relying on provision (3). It looks to me like we're talking
> about adding a large amount of complication --- both for the core
> database and for module authors --- in order to provide a duplicate
> solution for that. Why should we bother? Especially, why should we

Think in particular about in-house extensions: a collection of PL code
that's not maintained in the database but in some SCM, typically.
You keep maintaining your application, and the PL code too, and you keep
wanting to roll out those changes to production. Having a way to
package that then ALTER EXTENSION myappplcode UPGRADE; sounds nice to
me. Nothing to do with PostgreSQL upgrades, though, really.

> bother in version 1 of the feature? This could all be added later if
> we determine there's really sufficient demand, but right now we have
> no experience to show whether there is demand or not.

The reason why I've been bothering for version 1 is so that we can
support people upgrading from a previous version of PostgreSQL with
extensions installed, and who will want that both \dx and pg_dump
behaves correctly from day-1 in 9.1.

We will need to bootstrap the process some day, first extension enabled
release sounded fine from here. Nobody will want to DROP TYPE hstore
CASCADE; then repair, all just so that they'll able to have a better
pg_dump next time.

Now, current patch to do that is rather small if you don't include the
new contrib scripts. First line is src only, second one src+doc:

22 files changed, 946 insertions(+), 69 deletions(-)
40 files changed, 1352 insertions(+), 79 deletions(-)

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

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> Now having said that, it does occur to me that there is an upgrade-ish
> scenario that every user is going to hit immediately, which is how to
> get from an existing installation with a pile of "loose" objects created
> by one or more contrib modules to a state where those objects are
> understood to be parts of modules. But that is a special case that
> perhaps deserves a special-case solution, rather than inventing a very
> large wheel.

Well a good deal of the code I've written in the UGPRADE patch is there
for this special case, that's ALTER OBJECT ... SET EXTENSION ...;

This allows to "attach" any existing object to a given existing
extension. Now what you need is a way to create an empty extension so
that you can attach objects to it. That's in the patch in the form of
the new command CREATE WRAPPER EXTENSION ...;

WRAPPER was the most convenient keyword we already have I found.

Then, there's only 2 things left in the patch. The contrib scripts that
make that happen, and the control file support so that the command ALTER
EXTENSION $contrib UPGRADE will run the upgrade script.

This mechanism has been made in a way that allows it to cover running
other scripts for other kind of upgrades. That's about it.

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

On Thu, Feb 3, 2011 at 1:10 PM, David E. Wheeler <david(at)kineticode(dot)com> wrote:
> On Feb 3, 2011, at 10:07 AM, Tom Lane wrote:
>
>> Well, pg_upgrade is designed to work within a major-version series, eg
>> you could do a 9.1-to-9.1 upgrade if you needed to install a newer
>> version of an extension.  Admittedly, this is swinging a rather larger
>> hammer than "apply an upgrade script" would entail.
>
> Dude. That's a frigging piledriver!

That's putting it mildly. It's more like sending a rocket into outer
space to tweak the orbit of a comet so that it crashes into your
barbecue grill to light a fire so you can roast marshmallows.

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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Thu, Feb 3, 2011 at 1:10 PM, David E. Wheeler <david(at)kineticode(dot)com> wrote:
>> On Feb 3, 2011, at 10:07 AM, Tom Lane wrote:
>>> Well, pg_upgrade is designed to work within a major-version series, eg
>>> you could do a 9.1-to-9.1 upgrade if you needed to install a newer
>>> version of an extension. Admittedly, this is swinging a rather larger
>>> hammer than "apply an upgrade script" would entail.

>> Dude. That's a frigging piledriver!

> That's putting it mildly. It's more like sending a rocket into outer
> space to tweak the orbit of a comet so that it crashes into your
> barbecue grill to light a fire so you can roast marshmallows.

No, it's more like using a sledgehammer to swat a fly because you don't
have a flyswatter and aren't inclined to drive ten miles to buy one.
It'll get the job done, and the added cost of building/maintaining a
more finely calibrated tool may well outweigh the value.

regards, tom lane

On Feb 3, 2011, at 11:02 AM, Tom Lane wrote:

>> That's putting it mildly. It's more like sending a rocket into outer
>> space to tweak the orbit of a comet so that it crashes into your
>> barbecue grill to light a fire so you can roast marshmallows.

LOL.

> No, it's more like using a sledgehammer to swat a fly because you don't
> have a flyswatter and aren't inclined to drive ten miles to buy one.
> It'll get the job done, and the added cost of building/maintaining a
> more finely calibrated tool may well outweigh the value.

I'd rather put down the sledgehammer and pick up a leaf or a paper bag or something.

Best,

David

On Thu, Feb 3, 2011 at 2:02 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Thu, Feb 3, 2011 at 1:10 PM, David E. Wheeler <david(at)kineticode(dot)com> wrote:
>>> On Feb 3, 2011, at 10:07 AM, Tom Lane wrote:
>>>> Well, pg_upgrade is designed to work within a major-version series, eg
>>>> you could do a 9.1-to-9.1 upgrade if you needed to install a newer
>>>> version of an extension.  Admittedly, this is swinging a rather larger
>>>> hammer than "apply an upgrade script" would entail.
>
>>> Dude. That's a frigging piledriver!
>
>> That's putting it mildly.  It's more like sending a rocket into outer
>> space to tweak the orbit of a comet so that it crashes into your
>> barbecue grill to light a fire so you can roast marshmallows.
>
> No, it's more like using a sledgehammer to swat a fly because you don't
> have a flyswatter and aren't inclined to drive ten miles to buy one

In other words, it's something no sensible person actually does?

> It'll get the job done, and the added cost of building/maintaining a
> more finely calibrated tool may well outweigh the value.

It'll get the job done for very small values of getting the job done.
Let's suppose that version 2 of hstore comes out, improving on version
1 of hstore by adding one new useful function. Your proposed solution
is that this person should initdb a new cluster, shut down their
database, pg_upgrade over to the new cluster, and start it back up
again to get that function definition. What's actually going to
happen in 99.44% of cases is that the person is going to say "this
extension mechanism sucks" and create the function definition by hand,
because even if their database is unimportant enough that they don't
mind the downtime, that's a ridiculous amount of hassle for what ought
to be a straightforward operation. The reason for possibly postponing
this to 9.2 is not that it isn't necessary but that we might not yet
be sure what the best way to do it is.

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

All,

Let me summarize the prospective solutions based on some chatting with
some potential extension authors (that is, folks who maintain in-house
stuff they're thinking of offering as extensions). Especially since I
think at this point the majority of -hackers has lost track of the argument:

(A) Writing a separate upgrade script between each two versions
This is completely unrealistic. No module maintainer will ever do this.
Imagine an extension which had 20 releases over 4 years; that would be
*190* upgrade scripts. Any solution we come up with cannot require
module maintainers to write more than one upgrade script per release, or
we will have no module maintainers.

(B) As (A), but with in-script Includes so that eariler versions of
scripts could be re-used for later version upgrades or strung together.
This is somewhat more realistic, given that it could be done
automatically and then tweaked. Especially if we supply a script to
generate version upgrade scripts.

(C) as (A), but through concatinating scripts for upgrade using Make
files. This seems like the worst of all possible solutions. First, it
prevents us from ever having a binary module release network, and
permanently requires anyone using extensions to have GNU make present
and accessible on their system, thus pretty much leaving out the Windows
users forever. Second, it's a lot harder for module maintainers to
tweak than includes would be, especially for SQL-only modules. Third,
it requires Make to check what version you currently have installed in
order to build the module, something which is unlikely to be reliable.

(D) Requires a series of ordered upgrade scripts in sortable version
numbering, each of which gets applied in order between the two versions.
This initially seems like the most attractive option -- and is the one
used by dozens of popular open source web applications -- but has some
major roadblocks for us. First, it requires module authors to subscribe
to a uniform sortable versions scheme (which isn't a bad thing, the
users would certainly appreciate it, and PGXN is liable to enforce this
anyway). Second and much more substantially, .so's installed for later
versions might be incompatible with intermediate upgrade scripts, and
intermediate .so's are unlikely to be present.

(E) Involves relying on pg_upgrade. In addition to the sledgehammer
issue, I really don't see how this would work *at all*. First, modules
would almost by definition have a release cycle which is independant of
PostgreSQL core, and many versions of modules would work with several
versions of PostgreSQL. Second, pg_upgrade is currently unable to
upgrade user-owned objects at all, so I don't see how it would be
handling modules. Thirdly, pg_upgrade does not run scripts, so required
steps for some module upgrades, like say rebuilding an index or
replacing a data type, could not be handled at all. Finally, if we
modify pg_upgrade to handle extensions, we're liable to break it.

Have I summed up the options? Did I miss anything?

Note that handling upgrades of complex applications is not a problem
which anyone in the world has solved that I know of. So it's
unsurprising that we're having difficulty with it.

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

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> (D) Requires a series of ordered upgrade scripts in sortable version
> numbering, each of which gets applied in order between the two versions.
> This initially seems like the most attractive option -- and is the one
> used by dozens of popular open source web applications -- but has some
> major roadblocks for us. First, it requires module authors to subscribe
> to a uniform sortable versions scheme (which isn't a bad thing, the
> users would certainly appreciate it, and PGXN is liable to enforce this
> anyway). Second and much more substantially, .so's installed for later
> versions might be incompatible with intermediate upgrade scripts, and
> intermediate .so's are unlikely to be present.

FWIW, I think that last objection is bogus. There's no reason that an
extension author can't leave dummy C functions in his code to support
obsolete CREATE FUNCTION calls. (As an example, I added one to
Alexander Korotkov's recent pg_trgm patch. It consists of 10 lines of
boilerplate code, and could have been less if I'd used a quick and dirty
elog() instead of ereport().) This is certainly a lot less of a problem
than the difficulties with the other approaches.

I think some of your objections to the pg_upgrade approach are equally
bogus. In particular, I don't believe any of these approaches will
usefully serve cases where indexes have to be rebuilt to be compatible
with a new .so. Those indexes won't all be in the same database, and
even if they were, no simple SQL script is going to be able to find
them. If an extension author wants to break on-disk compatibility, it's
going to be just as unfriendly to his users as such a break in the core
database will be, ie, they're going to have to do dump and reload. The
extension mechanism can't be expected to solve that.

regards, tom lane

On Feb 3, 2011, at 11:35 AM, Josh Berkus wrote:

> (D) Requires a series of ordered upgrade scripts in sortable version
> numbering, each of which gets applied in order between the two versions.
> This initially seems like the most attractive option -- and is the one
> used by dozens of popular open source web applications -- but has some
> major roadblocks for us. First, it requires module authors to subscribe
> to a uniform sortable versions scheme (which isn't a bad thing, the
> users would certainly appreciate it, and PGXN is liable to enforce this
> anyway).

PGXN does enforce Semantic Versions (http://semver.org/), but extensions wont' be limited to PGXN, of course. Might be a lot of stuff developed for internal use in organizations, and they surely won't use the same version numbers.

Agreed with your summary, well put.

Best,

David

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> Let me summarize the prospective solutions based on some chatting with
> some potential extension authors (that is, folks who maintain in-house
> stuff they're thinking of offering as extensions). Especially since I
> think at this point the majority of -hackers has lost track of the argument:

Thanks for doing that!

> (A) Writing a separate upgrade script between each two versions

> (B) As (A), but with in-script Includes so that eariler versions of
> scripts could be re-used for later version upgrades or strung together.

Well if you want to support upgrades between each two versions, that
means you have users and you don't know what they currently have
installed. Then you have this problem to solve, and it's complex the
same no matter what tools are offered.

> (C) as (A), but through concatinating scripts for upgrade using Make
> files. This seems like the worst of all possible solutions. First, it
> prevents us from ever having a binary module release network, and
> permanently requires anyone using extensions to have GNU make present
> and accessible on their system, thus pretty much leaving out the Windows
> users forever. Second, it's a lot harder for module maintainers to
> tweak than includes would be, especially for SQL-only modules. Third,
> it requires Make to check what version you currently have installed in
> order to build the module, something which is unlikely to be reliable.

You're missing something here. In this scheme, make is only used to
prepare the upgrade scripts. Then you package and ship those, and you
don't need make no more, even when the target is windows. More than
that, the tool to use would be `cat`, really, Make would just call it on
files in the right order. A perl one-liner will certainly do just fine.

So in fact this is just saying the authors to manage the situation as
they wish, then setup the control file with the produced scripts
references to use. That's it.

> (D) Requires a series of ordered upgrade scripts in sortable version
> numbering, each of which gets applied in order between the two versions.

Well, your build process is certainly powerful enough to concatenate
file content together, right?

> Note that handling upgrades of complex applications is not a problem
> which anyone in the world has solved that I know of. So it's
> unsurprising that we're having difficulty with it.

Agreed. So my proposal was, again, that we don't solve it this year but
provide a mechanism that allows extension authors to setup which script
to run when the user will ALTER EXTENSION foo UPGRADE. How they come up
with such a script, I say, is *NOT* our problem. At least for 9.1.

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

> Well if you want to support upgrades between each two versions, that
> means you have users and you don't know what they currently have
> installed. Then you have this problem to solve, and it's complex the
> same no matter what tools are offered.

How *are* we detecting which version is installed, anyway? Is that in
the pg_extenstions table?

> You're missing something here. In this scheme, make is only used to
> prepare the upgrade scripts. Then you package and ship those, and you
> don't need make no more, even when the target is windows. More than
> that, the tool to use would be `cat`, really, Make would just call it on
> files in the right order. A perl one-liner will certainly do just fine.

Ah, I see. So you're proposing a build system for the 100's of
verison-to-version upgrade scripts. That makes a lot more sense,
although I wonder what such a build script would look like in actuality.

> Agreed. So my proposal was, again, that we don't solve it this year but
> provide a mechanism that allows extension authors to setup which script
> to run when the user will ALTER EXTENSION foo UPGRADE. How they come up
> with such a script, I say, is *NOT* our problem. At least for 9.1.

So every package would include a script called upgrade.sql ( or
upgrade.c? ) which is supposed to handle the upgrade, and it's up to the
module author to power that, at least until 9.2? Seem like the most
reasonable course for February ...

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

> FWIW, I think that last objection is bogus. There's no reason that an
> extension author can't leave dummy C functions in his code to support
> obsolete CREATE FUNCTION calls. (As an example, I added one to
> Alexander Korotkov's recent pg_trgm patch. It consists of 10 lines of
> boilerplate code, and could have been less if I'd used a quick and dirty
> elog() instead of ereport().) This is certainly a lot less of a problem
> than the difficulties with the other approaches.

Well, that makes solution (D) a lot more viable then.

> I think some of your objections to the pg_upgrade approach are equally
> bogus. In particular, I don't believe any of these approaches will
> usefully serve cases where indexes have to be rebuilt to be compatible
> with a new .so. Those indexes won't all be in the same database, and
> even if they were, no simple SQL script is going to be able to find
> them. If an extension author wants to break on-disk compatibility, it's
> going to be just as unfriendly to his users as such a break in the core
> database will be, ie, they're going to have to do dump and reload. The
> extension mechanism can't be expected to solve that.

I could do it, given an extension upgrade script which could run
PL/pgSQL code. That is, I could write a script which finds all indexes
dependant on a particular data type and reindex them.

So I disagree that it can't be solved. It just can't be solved *by
pg_upgrade*.

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

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> How *are* we detecting which version is installed, anyway? Is that in
> the pg_extenstions table?

The installed version is in the pg_extenstion catalog, the version we're
upgrading to is in the control file and can be seen in the system view
pg_available_extensions or from the system SRF named the same:

~:5490=# \dx
List of extensions
Schema | Name | Version | Description
------------+-----------+----------+-----------------------------------------
pg_catalog | adminpack | 9.1devel | Administrative functions for PostgreSQL
public | lo | 9.1devel | managing Large Objects
(2 rows)

~:5490=# select oid, * from pg_extension ;
oid | extname | extnamespace | relocatable | extversion
-------+-----------+--------------+-------------+------------
16385 | lo | 2200 | t | 9.1devel
16406 | adminpack | 11 | f | 9.1devel
(2 rows)

~:5490=# select schema, name, installed, version from pg_available_extensions limit 10;
schema | name | installed | version
------------+--------------------+-----------+----------
public | lo | 9.1devel | 9.1devel
pg_catalog | adminpack | 9.1devel | 9.1devel
| citext | | 9.1devel
| chkpass | | 9.1devel
| cube | | 9.1devel
| pg_stat_statements | | 9.1devel
| pg_buffercache | | 9.1devel
| dict_xsyn | | 9.1devel
| earthdistance | | 9.1devel
| xml2 | | 9.1devel
(10 rows)

> So every package would include a script called upgrade.sql ( or
> upgrade.c? ) which is supposed to handle the upgrade, and it's up to the
> module author to power that, at least until 9.2? Seem like the most
> reasonable course for February ...

Yeah. Of course you want to support shipping upgrade files for more
than one upgrade situation, that's in my proposal and patch too. The
extension author "just" have to fill in the control file with an upgrade
setup: regexp against installed version string => upgrade script to
use. And that's about it.

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

On Thu, Feb 3, 2011 at 4:24 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> So every package would include a script called upgrade.sql ( or
> upgrade.c? ) which is supposed to handle the upgrade, and it's up to the
> module author to power that, at least until 9.2?  Seem like the most
> reasonable course for February ...

I don't think we should commit something that for 9.1 that we may need
to change incompatibly for 9.2. If we're not completely happy with
it, it gets booted. Whatever we put in place here is going to be with
us for a long, long time.

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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> I don't think we should commit something that for 9.1 that we may need
> to change incompatibly for 9.2. If we're not completely happy with
> it, it gets booted. Whatever we put in place here is going to be with
> us for a long, long time.

So, what is it specifically here that you're unhappy with?

a. ALTER EXTENSION ... UPGRADE;
b. CREATE WRAPPER EXTENSION ...; (version is then NULL)
c. upgrade rules in the control file
d. ALTER OBJECT ... SET EXTENSION ...;
e. having upgrade scripts for upgrading contribs from null
f. having those scripts named $contrib.upgrade.sql

What I think is that the end-user syntax (the SQL DDLs) that we add are
going to fall exactly into the category you're talking about: long, long
term support.

But that could well be less true of the control file, should we choose
so. I think there's enough value in being able to get extension from
what you had installed in pre-9.1; that changing some non-DLL bits in
9.2 is something we can set ourselves to consider.

But anyway, we've been doing quite a round of expectations, explaining,
detailing, and bikeshedding on the features already, so I'd like to see
a break down, because it appears clearly that some readers changed their
mind in the process.

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

On Fri, Feb 4, 2011 at 10:13 AM, Dimitri Fontaine
<dimitri(at)2ndquadrant(dot)fr> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> I don't think we should commit something that for 9.1 that we may need
>> to change incompatibly for 9.2.  If we're not completely happy with
>> it, it gets booted.  Whatever we put in place here is going to be with
>> us for a long, long time.
>
> So, what is it specifically here that you're unhappy with?

I'd like to answer this question, but I have not had enough time to
read through this patch in detail, because there are 97 patches in
this CommitFest. The point I'm trying to make, however, is
procedural. We shouldn't commit anything at the very end of a
development cycle that we're not reasonably comfortable we can live
with, because there is not a lot of time to change our minds later. I
completely believe that an extension upgrade mechanism is a good thing
to have and I'm sympathetic to your desire to get this into 9.1 - but
the fact is that we are very short on time, the prerequisite patch is
not committed yet, and this is a big piece of functionality in a
tricky area which was submitted for the last CommitFest of the cycle
and about which there is not a clear design consensus.

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

On Feb 4, 2011, at 7:48 AM, Robert Haas wrote:

> I'd like to answer this question, but I have not had enough time to
> read through this patch in detail, because there are 97 patches in
> this CommitFest. The point I'm trying to make, however, is
> procedural. We shouldn't commit anything at the very end of a
> development cycle that we're not reasonably comfortable we can live
> with, because there is not a lot of time to change our minds later. I
> completely believe that an extension upgrade mechanism is a good thing
> to have and I'm sympathetic to your desire to get this into 9.1 - but
> the fact is that we are very short on time, the prerequisite patch is
> not committed yet, and this is a big piece of functionality in a
> tricky area which was submitted for the last CommitFest of the cycle
> and about which there is not a clear design consensus.

Robert, I think that the core extension if pretty uncontroversial, modulo some minor issues. It's the upgrade process that's more controversial. I think the case can be made to accept even that part as Dim has written it, because it is pretty much the bare minimum that other solutions could be built on top of and improve upon. But if not, I think that's the only part the one might really look at as something to omit for 9.1.

Dim, I haven't followed that closely lately, but is the ALTER EXTENSION UPGRADE bit still a separate patch?

Best,

David

On Fri, Feb 4, 2011 at 11:48 AM, David E. Wheeler <david(at)kineticode(dot)com> wrote:
> Robert, I think that the core extension if pretty uncontroversial, modulo some minor issues. It's the upgrade process that's more controversial. I think the case can be made to accept even that part as Dim has written it, because it is pretty much the bare minimum that other solutions could be built on top of and improve upon. But if not, I think that's the only part the one might really look at as something to omit for 9.1.

Yeah, I understand. I believe Tom said he was going to look at the
basic functionality with an eye toward commit, and I hope to look at
it as well, either before or after it gets committed. Time
permitting, I'd then like to look at this, but I'm not sure I'm going
to be able to squeeze that into the time available, nor am I sure that
I'd get sufficient consensus to commit something even if I did.

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

On Feb 4, 2011, at 8:54 AM, Robert Haas wrote:

> Yeah, I understand. I believe Tom said he was going to look at the
> basic functionality with an eye toward commit, and I hope to look at
> it as well, either before or after it gets committed. Time
> permitting, I'd then like to look at this, but I'm not sure I'm going
> to be able to squeeze that into the time available, nor am I sure that
> I'd get sufficient consensus to commit something even if I did.

Great, thanks.

Best,

David

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> Dim, I haven't followed that closely lately, but is the ALTER
> EXTENSION UPGRADE bit still a separate patch?

Yes it is. It's an incremental that apply on top of the extension patch
and get its own patch entry on the commit fest application:

https://commitfest.postgresql.org/action/patch_view?id=472

As such it will need bitrot fixing as soon as the extension main patch
makes it in. Also I have some cleaning to do here, but given the
current open discussion about the design I'm still holding this work.
Well, it seems the discussion is slowing down to realize I only included
the bare minimum just so that we avoid having too long a discussion and
that the patch has its chances to 9.1 :)

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

I spent some time reviewing this thread. I think the major point that's
not received adequate discussion is this: the design assumes that there's
just one "current version" of any extension, and that's not good enough.
David Fetter was trying to make that point upthread but didn't seem to
convince people. I'm convinced though. I think that one of the major
selling points of extensions could be having a controlled way of exposing
different versions of an API and letting users select which one is in use
in each database. Look at how much effort we spend maintaining back
branches of the core code for people who don't want to, eg, update their
apps to avoid pre-8.3-style implicit casting. (Yeah, I know that on-disk
compatibility is another major reason for staying on a back branch, but
API changes are definitely part of it.)

So I believe that it'd be a good idea if it were possible for an extension
author to distribute a package that implements, say, versions 1.0, 1.1,
and 2.0 of hstore. Not all will choose to do the work needed for that, of
course, and that's fine. But the extension mechanism ought to permit it.
Over time we might get to a point where somebody could be running the
latest version of the core database (with all the bug fixes and other
goodness of that) but his application compatibility problems are solved
by running back-rev versions of certain extensions.

To do this, we need to remove the concept that the control file specifies
"the" version of an extension; rather the version is associated with the
SQL script file. I think we should embed the version number in the script
file name, and require one to be present (no more omitted version
numbers). So you would distribute, say,
hstore-1.0.sql
hstore-1.1.sql
hstore-2.0.sql
representing the scripts needed to install these three versions from
scratch. CREATE EXTENSION would have an option to select which
version to install. If the option is omitted, there are at least two
things we could do:
1. Choose the newest available version.
2. Let the control file specify which version is the default.
I think I prefer #2 because it avoids needing a rule for comparing
version identifiers, and it caters to the possibility that the "newest"
version isn't yet mature enough to be a good default.

As for upgrades, let's just expect upgrade scripts to be named
extension-oldversion-newversion.sql. ALTER EXTENSION UPGRADE knows the
relevant oldversion from pg_extension, and newversion can be handled the
same way as in CREATE, ie, either the user says which version to update to
or we use the default version from the control file.

I don't seriously expect most extension authors to bother preparing
upgrade scripts for any cases except adjacent pairs of versions.
That means that if a user comes along and wants to upgrade across several
versions of the extension, he'll have to do it in several steps:
ALTER EXTENSION hstore UPGRADE TO '1.1';
ALTER EXTENSION hstore UPGRADE TO '2.0';
ALTER EXTENSION hstore UPGRADE TO '2.1';
I don't see that as being a major problem --- how often will people have
the need to do that, anyway? Authors who feel that it is a big deal can
expend the work to provide shortcut scripts. I do not see adequate return
on investment from the regexp-matching complications in the currently
submitted patch.

In this scheme, all the extension scripts are independent. We spent quite
a lot of time arguing about ways to avoid duplication of code between
scripts, but frankly I'm not convinced that that's worth troubling over.
As far as the initial-install scripts go, once you've released 1.0 it's
unlikely you'll ever change it again, so the fact that you copied and
pasted it as a starting point for 1.1 isn't really a maintenance burden.
Version upgrade scripts won't share any code at all, unless the author is
trying to provide shortcut scripts for multi-version jumps, and as I said,
I doubt that many will bother. Also, it'll be some time before there's
much need for multi-version update scripts anyway, so I am not feeling
that it is necessary to solve that now. We could later on add some kind
of script inclusion capability to allow authors to avoid code duplication
in multi-version update scripts, but it's just not urgent.

So, concrete proposal is to enforce the "extension-version.sql" and
"extension-oldversion-newversion.sql" naming rules for scripts, which
means getting rid of the script name parameter in control files.
(Instead, we could have a directory parameter that tells which directory
holds all the install and upgrade scripts for the extension.) Also, the
"version" parameter should be renamed to something like "current_version"
or "default_version". We also have to be wary of whether any other
control-file parameters specify something that might be version-specific.
Looking at the current list:

comment: probably OK to consider this as a default for all versions.
We already have the ability for any script file to override it, anyway.

encoding: I don't see any big problem with insisting that all scripts for
a given extension be in the same encoding.

requires, relocatable and schema: These are problematic, because it's not
out of the question that someone might want to change these properties
from one version to another. But as things are currently set up, we must
know these things before we start to run the extension script, because
they are needed to set up the search_path correctly.

Perhaps for now it's sufficient to say that these properties can't change
across versions. Alternatively, we could allow there to be a secondary
version-specific control file that can override the main control file.
IOW, we'd read "extension.control" to get the directory and
default_version values, then determine the version we are installing or
upgrading to, then see if there's an "extension-version.control" file
in the extension's directory, and if so read that and let it replace
the remaining parameters' values.

Comments?

regards, tom lane

On Feb 10, 2011, at 10:26 AM, Tom Lane wrote:

> 1. Choose the newest available version.
> 2. Let the control file specify which version is the default.
> I think I prefer #2 because it avoids needing a rule for comparing
> version identifiers, and it caters to the possibility that the "newest"
> version isn't yet mature enough to be a good default.

+1. I assume there will be some way to build versioned shared object libraries too, then?

> In this scheme, all the extension scripts are independent. We spent quite
> a lot of time arguing about ways to avoid duplication of code between
> scripts, but frankly I'm not convinced that that's worth troubling over.
> As far as the initial-install scripts go, once you've released 1.0 it's
> unlikely you'll ever change it again, so the fact that you copied and
> pasted it as a starting point for 1.1 isn't really a maintenance burden.

I disagree with this. A lot of dynamic language libraries never get to 1.0, and even if they do can go through periods of extensive development with major changes from version to version. Just have a look at the pgTAP changes file for an example:

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

I already do a *lot* of work in the Makefile to patch things so that it works all the way back to 8.0. And I'm adding stuff now to generate other files that will contain a subset of the pgTAP functionality. I don't think I'd ever write upgrade scripts for pgTAP, but I've worked with a lot of Perl modules that have followed similar aggressive development, and can imagine times when I'd need to write upgrade scripts for aggressively-developed PostgreSQL extensions. And I quail at the idea. Lord help me if I'd need to also write create patches for my upgrade scripts to support older versions of PostgreSQL.

> Version upgrade scripts won't share any code at all, unless the author is
> trying to provide shortcut scripts for multi-version jumps, and as I said,
> I doubt that many will bother. Also, it'll be some time before there's
> much need for multi-version update scripts anyway, so I am not feeling
> that it is necessary to solve that now. We could later on add some kind
> of script inclusion capability to allow authors to avoid code duplication
> in multi-version update scripts, but it's just not urgent.

Okay, that would be a big help. And I'm fine with it being something to "maybe be added later." We'll see then what cow paths develop, and demands for pasture fences to be cut down. Or something.

> So, concrete proposal is to enforce the "extension-version.sql" and
> "extension-oldversion-newversion.sql" naming rules for scripts, which
> means getting rid of the script name parameter in control files.
> (Instead, we could have a directory parameter that tells which directory
> holds all the install and upgrade scripts for the extension.)

+1 I like this idea. I'm already putting all my scripts into an sql/ directory for PGXN distributions:

https://github.com/theory/pg-semver

> encoding: I don't see any big problem with insisting that all scripts for
> a given extension be in the same encoding.

+1. Also, can't one set client_encoding in the scripts anyway?

> requires, relocatable and schema: These are problematic, because it's not
> out of the question that someone might want to change these properties
> from one version to another. But as things are currently set up, we must
> know these things before we start to run the extension script, because
> they are needed to set up the search_path correctly.
>
> Perhaps for now it's sufficient to say that these properties can't change
> across versions. Alternatively, we could allow there to be a secondary
> version-specific control file that can override the main control file.
> IOW, we'd read "extension.control" to get the directory and
> default_version values, then determine the version we are installing or
> upgrading to, then see if there's an "extension-version.control" file
> in the extension's directory, and if so read that and let it replace
> the remaining parameters' values.

+1.

I'll need to play around with some of this stuff to see how it affects PGXN distributions. My main concern will be allowing an "extension distribution" to somehow work both on 9.1 with EXTENSIONs and in < 9.0 as PGXS-installed modules currently work, without too much pain to the developer to support previous versions of PostgreSQL.

Best,

David

On Thu, Feb 10, 2011 at 1:26 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> requires, relocatable and schema: These are problematic, because it's not
> out of the question that someone might want to change these properties
> from one version to another.  But as things are currently set up, we must
> know these things before we start to run the extension script, because
> they are needed to set up the search_path correctly.

My biggest concern with this extensions work is that these variables
are poorly designed. The extension mechanism is basically the
equivalent of RPM for inside the database. And while in theory there
is such a thing as a relocatable RPM, I don't know that I've ever used
it, at least not successfully. I'm worried this is going to be a
pretty serious rough edge that's difficult to file down later.
Forcing everything into a single schema (like pg_extension) seems a
bit too draconian, but this idea that you can install things wherever
you like and somehow it's gonna just work seems pretty optimistic.

However, that's a side point. The overall design you propose seems
reasonable to me.

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

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> +1. I assume there will be some way to build versioned shared object libraries too, then?

I'm not really addressing that in this proposal. You could imagine
supporting all the extension versions in one .so, or you could have one
per version (meaning the upgrade scripts would have to CREATE OR REPLACE
all the C functions to re-point them at a different .so), or mixed
cases. Right now the PGXS infrastructure would favor the first because
it has only limited ability to build multiple .so's in one directory;
but we could think about improving that if there's demand.

Note that you can version a function even within a single .so, for
example if hstore 1.0 defines foo() one way and hstore 1.1 defines
it another, you could make the latter point to the C function name
foo_1_1 while C function foo continues to provide the old behavior.
You have to at least provide a stub foo (that could just throw error
if called) for as long as you want to support upgrading from 1.0.

>> In this scheme, all the extension scripts are independent. We spent quite
>> a lot of time arguing about ways to avoid duplication of code between
>> scripts, but frankly I'm not convinced that that's worth troubling over.
>> As far as the initial-install scripts go, once you've released 1.0 it's
>> unlikely you'll ever change it again, so the fact that you copied and
>> pasted it as a starting point for 1.1 isn't really a maintenance burden.

> I disagree with this. A lot of dynamic language libraries never get to
> 1.0, and even if they do can go through periods of extensive development
> with major changes from version to version.

I don't see how that affects my point? You can spell "1.0" as "0.1"
and "1.1" as "0.2" if you like that kind of numbering, but I don't
see that that has any real impact. At the end of the day an author is
going to crank out a series of releases, and if he cares about people
using those releases for production, he's going to have to provide at
least a upgrade script to move an existing database from release N to
release N+1.

regards, tom lane

On Feb 10, 2011, at 11:31 AM, Tom Lane wrote:

> I'm not really addressing that in this proposal. You could imagine
> supporting all the extension versions in one .so, or you could have one
> per version (meaning the upgrade scripts would have to CREATE OR REPLACE
> all the C functions to re-point them at a different .so), or mixed
> cases. Right now the PGXS infrastructure would favor the first because
> it has only limited ability to build multiple .so's in one directory;
> but we could think about improving that if there's demand.
>
> Note that you can version a function even within a single .so, for
> example if hstore 1.0 defines foo() one way and hstore 1.1 defines
> it another, you could make the latter point to the C function name
> foo_1_1 while C function foo continues to provide the old behavior.
> You have to at least provide a stub foo (that could just throw error
> if called) for as long as you want to support upgrading from 1.0.

Good enough for me.

> I don't see how that affects my point? You can spell "1.0" as "0.1"
> and "1.1" as "0.2" if you like that kind of numbering, but I don't
> see that that has any real impact. At the end of the day an author is
> going to crank out a series of releases, and if he cares about people
> using those releases for production, he's going to have to provide at
> least a upgrade script to move an existing database from release N to
> release N+1.

Yeah, but given a rapidly-developing extension, that could create a lot of extra work. I don't know that there's much of a way around that, other than concatenating files to build migration scripts from parts (perhaps via `Make` as dim suggested). But it can get complicated pretty fast. My desire here is to keep the barrier to creating PostgreSQL extensions as low as is reasonably possible.

Best,

David

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Thu, Feb 10, 2011 at 1:26 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> requires, relocatable and schema: These are problematic, because it's not
>> out of the question that someone might want to change these properties
>> from one version to another. But as things are currently set up, we must
>> know these things before we start to run the extension script, because
>> they are needed to set up the search_path correctly.

> My biggest concern with this extensions work is that these variables
> are poorly designed.

Yeah, I didn't especially like relocatable/schema either. I thought for
awhile about redefining relocatable as a three-way switch, corresponding
to the three use cases (relocatable after the fact, relocatable only at
initial install, no relocation) but didn't pull the trigger. It is
advantageous to have an explicit notion of a particular schema
containing the extension's exported stuff, so that we can add that
schema into the search path for dependent extensions. That means that
you can't easily remove the explicit schema value for the third case,
so it's not that easy to make it look cleaner.

> The extension mechanism is basically the
> equivalent of RPM for inside the database. And while in theory there
> is such a thing as a relocatable RPM, I don't know that I've ever used
> it, at least not successfully.

General opinion around Red Hat is relocatable RPMs don't work. But
pushing a set of functions from one schema to another is a very much
narrower problem than what an RPM has to deal with, so I'm not convinced
that the analogy holds.

Now, if you want to argue that moving an extension after the fact (ALTER
EXTENSION SET SCHEMA) is so dangerous as to be useless, I wouldn't
argue very hard. Do you want to propose ripping that out? But
relocating at first install doesn't seem horrible.

regards, tom lane

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> On Feb 10, 2011, at 11:31 AM, Tom Lane wrote:
>> I don't see how that affects my point? You can spell "1.0" as "0.1"
>> and "1.1" as "0.2" if you like that kind of numbering, but I don't
>> see that that has any real impact. At the end of the day an author is
>> going to crank out a series of releases, and if he cares about people
>> using those releases for production, he's going to have to provide at
>> least a upgrade script to move an existing database from release N to
>> release N+1.

> Yeah, but given a rapidly-developing extension, that could create a lot of extra work. I don't know that there's much of a way around that, other than concatenating files to build migration scripts from parts (perhaps via `Make` as dim suggested). But it can get complicated pretty fast. My desire here is to keep the barrier to creating PostgreSQL extensions as low as is reasonably possible.

Oh, I see, you're just saying that it's not unlikely somebody could find
himself with dozens of minor releases all being supported. Yeah, he'd
then really need to provide shortcut upgrade scripts, and
building/maintaining those would be a pain.

The design as I sketched it didn't need to make any assumptions at all
about the meaning of the version identifiers. But if you were willing
to assume that the identifiers are comparable/sortable by some rule,
then it wouldn't be that hard for ALTER EXTENSION UPGRADE to figure out
how to chain a series of upgrade scripts together to get from A to B,
and then there would be no need for manual maintenance of shortcut
scripts. IIRC the main objection to doing it that way was that the
underlying .so has to be compatible (at least to the extent of allowing
CREATE OR REPLACE FUNCTION) with all the intermediate versions --- but
if you believe the use-case I'm arguing for, that would be wanted
anyway, because all the intermediate versions would be considered
potentially useful stopping points.

I'm not philosophically opposed to requiring the version numbers to be
sortable, I just didn't want to introduce the concept if we didn't have
to. But maybe automatic application of a series of upgrade scripts is
enough reason.

regards, tom lane

On Thu, Feb 10, 2011 at 2:42 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> General opinion around Red Hat is relocatable RPMs don't work.  But
> pushing a set of functions from one schema to another is a very much
> narrower problem than what an RPM has to deal with, so I'm not convinced
> that the analogy holds.
>
> Now, if you want to argue that moving an extension after the fact (ALTER
> EXTENSION SET SCHEMA) is so dangerous as to be useless, I wouldn't
> argue very hard.  Do you want to propose ripping that out?  But
> relocating at first install doesn't seem horrible.

I'm not very concerned about letting people set the schema after the
fact. If we think it's OK for them to whack the location around at
first install, I don't know why we shouldn't also let them whack it
around later. The question I have is whether it's really reasonable
to let extension-owned objects be moved around at all. It'll probably
work fine as long as there are no other extensions depending on the
one that's getting moved, but it doesn't pay to design for the trivial
case. The real issue is what happens when you want to install
extension A, which depends on extensions B, C, and D, and B, C, and D
are all in non-standard locations. Does that have any chance of
working under the system we're proposing?

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

On Thu, Feb 10, 2011 at 3:02 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "David E. Wheeler" <david(at)kineticode(dot)com> writes:
>> On Feb 10, 2011, at 11:31 AM, Tom Lane wrote:
>>> I don't see how that affects my point?  You can spell "1.0" as "0.1"
>>> and "1.1" as "0.2" if you like that kind of numbering, but I don't
>>> see that that has any real impact.  At the end of the day an author is
>>> going to crank out a series of releases, and if he cares about people
>>> using those releases for production, he's going to have to provide at
>>> least a upgrade script to move an existing database from release N to
>>> release N+1.
>
>> Yeah, but given a rapidly-developing extension, that could create a lot of extra work. I don't know that there's much of a way around that, other than concatenating files to build migration scripts from parts (perhaps via `Make` as dim suggested). But it can get complicated pretty fast. My desire here is to keep the barrier to creating PostgreSQL extensions as low as is reasonably possible.
>
> Oh, I see, you're just saying that it's not unlikely somebody could find
> himself with dozens of minor releases all being supported.  Yeah, he'd
> then really need to provide shortcut upgrade scripts, and
> building/maintaining those would be a pain.
>
> The design as I sketched it didn't need to make any assumptions at all
> about the meaning of the version identifiers.  But if you were willing
> to assume that the identifiers are comparable/sortable by some rule,
> then it wouldn't be that hard for ALTER EXTENSION UPGRADE to figure out
> how to chain a series of upgrade scripts together to get from A to B,
> and then there would be no need for manual maintenance of shortcut
> scripts.  IIRC the main objection to doing it that way was that the
> underlying .so has to be compatible (at least to the extent of allowing
> CREATE OR REPLACE FUNCTION) with all the intermediate versions --- but
> if you believe the use-case I'm arguing for, that would be wanted
> anyway, because all the intermediate versions would be considered
> potentially useful stopping points.
>
> I'm not philosophically opposed to requiring the version numbers to be
> sortable, I just didn't want to introduce the concept if we didn't have
> to.  But maybe automatic application of a series of upgrade scripts is
> enough reason.

You don't need them to be sortable. You just need them to be
comparable, and equality seems like a plenty good enough comparison
rule. You can compute the shortest chain of upgrade scripts that can
take you from the current version to the target version.

But I'd be happy to leave that for 9.2.

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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> Now, if you want to argue that moving an extension after the fact (ALTER
>> EXTENSION SET SCHEMA) is so dangerous as to be useless, I wouldn't
>> argue very hard.  Do you want to propose ripping that out?  But
>> relocating at first install doesn't seem horrible.

Either an extension is relocatable or you have to deal with what Josh
Berkus the search_path hell. Lots of databases are using a host of
schema for their own objects already, and will want to have extensions
either all in the same place or scattered around each in its own schema.

I don't think we are in a position to impose a choice to our users here.

> I'm not very concerned about letting people set the schema after the
> fact. If we think it's OK for them to whack the location around at
> first install, I don't know why we shouldn't also let them whack it
> around later. The question I have is whether it's really reasonable
> to let extension-owned objects be moved around at all. It'll probably
> work fine as long as there are no other extensions depending on the
> one that's getting moved, but it doesn't pay to design for the trivial

If your extension depends on some others and your scripts are not
prepared to deal with those being moved around, you just setup your
extension as not relocatable. That's it.

> case. The real issue is what happens when you want to install
> extension A, which depends on extensions B, C, and D, and B, C, and D
> are all in non-standard locations. Does that have any chance of
> working under the system we're proposing?

Yes. It all depends on what's in the extension and what exactly the
dependency is. You have more problem when calling another extension's
function relying on the search_path that you have when using another
extension's data type. But it boils down to which way the dependency is
setup. And if moving objects breaks the install, you move them back
then fill a bug, and the extension's author changes relocatable to false
in the next version, or fix the bug in another way.

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

On Thu, Feb 10, 2011 at 3:33 PM, Dimitri Fontaine
<dimitri(at)2ndquadrant(dot)fr> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> Now, if you want to argue that moving an extension after the fact (ALTER
>>> EXTENSION SET SCHEMA) is so dangerous as to be useless, I wouldn't
>>> argue very hard.  Do you want to propose ripping that out?  But
>>> relocating at first install doesn't seem horrible.
>
> Either an extension is relocatable or you have to deal with what Josh
> Berkus the search_path hell.  Lots of databases are using a host of
> schema for their own objects already, and will want to have extensions
> either all in the same place or scattered around each in its own schema.
>
> I don't think we are in a position to impose a choice to our users here.

Well, for that matter, the user could want to install the same SQL
objects in more than one schema, in effect installing the same
extension twice.

>> I'm not very concerned about letting people set the schema after the
>> fact.  If we think it's OK for them to whack the location around at
>> first install, I don't know why we shouldn't also let them whack it
>> around later.  The question I have is whether it's really reasonable
>> to let extension-owned objects be moved around at all.  It'll probably
>> work fine as long as there are no other extensions depending on the
>> one that's getting moved, but it doesn't pay to design for the trivial
>
> If your extension depends on some others and your scripts are not
> prepared to deal with those being moved around, you just setup your
> extension as not relocatable.  That's it.

No, you have to get *those other module authors* to make *their*
extensions not relocatable so that you can depend on them.

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

On Feb 10, 2011, at 12:02 PM, Tom Lane wrote:

> Oh, I see, you're just saying that it's not unlikely somebody could find
> himself with dozens of minor releases all being supported. Yeah, he'd
> then really need to provide shortcut upgrade scripts, and
> building/maintaining those would be a pain.

Yes, exactly.

> The design as I sketched it didn't need to make any assumptions at all
> about the meaning of the version identifiers. But if you were willing
> to assume that the identifiers are comparable/sortable by some rule,
> then it wouldn't be that hard for ALTER EXTENSION UPGRADE to figure out
> how to chain a series of upgrade scripts together to get from A to B,
> and then there would be no need for manual maintenance of shortcut
> scripts. IIRC the main objection to doing it that way was that the
> underlying .so has to be compatible (at least to the extent of allowing
> CREATE OR REPLACE FUNCTION) with all the intermediate versions --- but
> if you believe the use-case I'm arguing for, that would be wanted
> anyway, because all the intermediate versions would be considered
> potentially useful stopping points.

And that was essentially my original proposal.

> I'm not philosophically opposed to requiring the version numbers to be
> sortable, I just didn't want to introduce the concept if we didn't have
> to. But maybe automatic application of a series of upgrade scripts is
> enough reason.

I always thought it was.

Best,

David

On Feb 10, 2011, at 12:07 PM, Robert Haas wrote:

> You don't need them to be sortable. You just need them to be
> comparable, and equality seems like a plenty good enough comparison
> rule. You can compute the shortest chain of upgrade scripts that can
> take you from the current version to the target version.

You have to be able to apply them in order. Unless I'm missing something, that means you need to be able to sort them.

> But I'd be happy to leave that for 9.2.

Yeah, if necessary. The only downside to that is, if we do indeed need them to be sortable, then we'd have to mandate a versioning format. And if there were existing extensions before 9.2, that might mess with them.

Best,

David

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Thu, Feb 10, 2011 at 2:42 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Now, if you want to argue that moving an extension after the fact (ALTER
>> EXTENSION SET SCHEMA) is so dangerous as to be useless, I wouldn't
>> argue very hard. Do you want to propose ripping that out? But
>> relocating at first install doesn't seem horrible.

> I'm not very concerned about letting people set the schema after the
> fact. If we think it's OK for them to whack the location around at
> first install, I don't know why we shouldn't also let them whack it
> around later.

The argument was that whether it's safe to move it during initial
install is strictly a property of the extension's own internals. Once
it's been in the database for awhile, moving it safely depends not only
on the extension's internals but also on whether you have created any
*other* objects that depend on where the extension is; for example,
functions that have its schema name embedded in a SET search_path
property or even hardwired in their code.

However, this risk isn't really any different from when you do ALTER foo
SET SCHEMA on a "loose" object, so on reflection it's not clear to me
that we should refuse this case when we allow the latter. We're merely
allowing people to shoot themselves in the foot with a machine-gun
instead of a revolver, by providing a command that encapsulates a whole
lot of SET SCHEMA commands in one action.

> The real issue is what happens when you want to install
> extension A, which depends on extensions B, C, and D, and B, C, and D
> are all in non-standard locations. Does that have any chance of
> working under the system we're proposing?

Again, it's not really any different from the case where the dependent
objects are "loose" rather than members of an extension. It's pretty
much up to the user to be aware of the consequences. If we had a way to
mark individual functions as safe or unsafe for renames to happen, it'd
be reasonable to extend that notion to whole extensions. But we don't
have that and I don't think it's appropriate to hold extensions to a
higher standard than we do loose objects --- especially when it takes
superuser privileges to break things by moving an extension but not to
break them by moving loose objects.

regards, tom lane

On Thu, Feb 10, 2011 at 3:46 PM, David E. Wheeler <david(at)kineticode(dot)com> wrote:
> On Feb 10, 2011, at 12:07 PM, Robert Haas wrote:
>
>> You don't need them to be sortable.  You just need them to be
>> comparable, and equality seems like a plenty good enough comparison
>> rule.  You can compute the shortest chain of upgrade scripts that can
>> take you from the current version to the target version.
>
> You have to be able to apply them in order. Unless I'm missing something, that means you need to be able to sort them.

Not at all. Say the currently installed version of the "dungeon"
extension is "kobold" and you want to upgrade to "bugbear". You have
the following scripts:

dungeon-goblin-orc.sql
dungeon-hobgoblin-bugbear.sql
dungeon-kobold-goblin.sql
dungeon-orc-hobgoblin.sql

Now, it's pretty clear that the only way to get to bugbear is to come
from hobgoblin, and the only way to get to hobgoblin is to come from
orc. orc can be reached only from goblin, which can be reached only
from kobold. So it's 100% clear that you have to apply the scripts in
the following order:

dungeon-kobold-goblin.sql
dungeon-goblin-orc.sql
dungeon-orc-hobgoblin.sql
dungeon-hobgoblin-bugbear.sql

Note that this even works if the versions aren't totally ordered. For
example, suppose you release version 0.1 of a module and later you
release a 1.0, which unfortunately is incompatible: there's no upgrade
path from 0.1 to 1.0. In time, 1.0 is superseded by 1.1. And then
you make some improvements to the old 0.1 code base and release that
as 0.2. Finally, you come up with an idea for unifying the two and
release a 1.2 version, which supports upgrades from all the previous
versions. You just ship:

foo-0.1-0.2.sql
foo-0.2-1.2.sql
foo-1.0-1.1.sql
foo-1.1-1.2.sql

If the user asks to upgrade to version 1.2, we'll observe that you can
get to 1.2 from 1.1 or from 0.2. Not knowing what the version numbers
mean, we'll look a bit further and see that you can get from 1.0 to
1.1 or from 0.1 to 0.2. Thus you can get to 1.2 like this:

0.1 -> 0.2 -> 1.2
0.2 -> 1.2
1.0 -> 1.1 -> 1.2
1.1 -> 1.2

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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Thu, Feb 10, 2011 at 3:02 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> The design as I sketched it didn't need to make any assumptions at all
>> about the meaning of the version identifiers. But if you were willing
>> to assume that the identifiers are comparable/sortable by some rule,

> You don't need them to be sortable. You just need them to be
> comparable, and equality seems like a plenty good enough comparison
> rule. You can compute the shortest chain of upgrade scripts that can
> take you from the current version to the target version.

Hmm. The problem with that is that once there are large numbers of
intermediate versions, the number of potential paths grows
exponentially. I was envisioning an algorithm like this:

1. Scan directory for upgrade scripts with oldversion = version we
have, and take the one with largest newversion <= version we want.

2. Apply this script (or more likely, just remember it until we've
verified there is a chain leading to version we want).

3. If now the version is not what we want, return to step 1.

I don't see an equally efficient method if we only have equality.

regards, tom lane

Hi,

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> I spent some time reviewing this thread. I think the major point that's

Thanks for doing that, we badly needed someone without an horse in this
race to do that and finish the design.

> So I believe that it'd be a good idea if it were possible for an extension
> author to distribute a package that implements, say, versions 1.0, 1.1,
> and 2.0 of hstore. Not all will choose to do the work needed for that, of
> course, and that's fine. But the extension mechanism ought to permit it.

Agreed. I've been weary of being told I'm trying to put too much into
the first PostgreSQL release with extensions, but I'm happy to see you
want to take it this far. So well, here follows some ideas I've been
trying hard not to push too soon :)

> To do this, we need to remove the concept that the control file specifies
> "the" version of an extension; rather the version is associated with the
> SQL script file. I think we should embed the version number in the script
> file name, and require one to be present (no more omitted version
> numbers). So you would distribute, say,
> hstore-1.0.sql
> hstore-1.1.sql
> hstore-2.0.sql
> representing the scripts needed to install these three versions from

What I don't like in that is that this restrict what the version strings
can look like. In debian for example it's pretty common to use the ~
separator, because 1.0~alpha1 < 1.0~beta < 1.0 with their sorting rules.
And this trick won't work on windows filenames, AFAIK. That's one
reason why I've wanted to stay away from having the version number
strings encoded into the filename in the first place.

But if you get to sorting rules of version strings, you have to define
them properly and impose them to users. That's both a good thing and a
bad thing, but IMO requires that we provide a proper data type for that.

So my opinion here is that we should not only go with your design here
with the version string in the filename, but also imposes how to spell
out version strings in a way that we know will work for PostgreSQL on
every supported system.

> scratch. CREATE EXTENSION would have an option to select which
> version to install. If the option is omitted, there are at least two
> things we could do:
> 1. Choose the newest available version.
> 2. Let the control file specify which version is the default.
> I think I prefer #2 because it avoids needing a rule for comparing
> version identifiers, and it caters to the possibility that the "newest"
> version isn't yet mature enough to be a good default.

I like this idea. +1 for having the default version to install in the
control file. See below for some more details about that, though.

> As for upgrades, let's just expect upgrade scripts to be named
> extension-oldversion-newversion.sql. ALTER EXTENSION UPGRADE knows the
> relevant oldversion from pg_extension, and newversion can be handled the
> same way as in CREATE, ie, either the user says which version to update to
> or we use the default version from the control file.

Again, I like the idea and how simple it make things look, but I think
if we should then bite the bullet and restrict what a version string is
expected to be and offer a data type with proper sorting while at it.
And of course use that as the pg_extension.extversion column type.

That way a SQL query can check if there's a new version available on
your system. That's useful in some places to use as a monitoring alert
coupled with nagios. The sysadmin team does the apt-get install part of
the job and then the DBA team is paged to go upgrade the extensions in
the databases, or shut the alarm somehow.

> I don't seriously expect most extension authors to bother preparing
> upgrade scripts for any cases except adjacent pairs of versions.
> That means that if a user comes along and wants to upgrade across several
> versions of the extension, he'll have to do it in several steps:
> ALTER EXTENSION hstore UPGRADE TO '1.1';
> ALTER EXTENSION hstore UPGRADE TO '2.0';
> ALTER EXTENSION hstore UPGRADE TO '2.1';
> I don't see that as being a major problem --- how often will people have
> the need to do that, anyway? Authors who feel that it is a big deal can
> expend the work to provide shortcut scripts. I do not see adequate return
> on investment from the regexp-matching complications in the currently
> submitted patch.

The regexp matching reason to live is so that we don't have to know
anything about version strings at all. If you're saying that a version
string can not contain a dash and must be a valid filesystem name (often
enough, for all systems supported by PostgreSQL), and you're now saying
that ALTER EXTENSION UPGRADE could automate multi-steps upgrade, then I
think we have to provide the "version" (or "pgversion") data type and
all that jazz.

If we get to somehow, even lightly, depend on some rules, better offer
them in code and documentation rather than have them implicit.

> that it is necessary to solve that now. We could later on add some kind
> of script inclusion capability to allow authors to avoid code duplication
> in multi-version update scripts, but it's just not urgent.

I'd think that points get more or less moot with the automated
multi-step support. You could then have intermediate upgrade scripts
that matches no released version but just are there to ease the
packaging of them.

> So, concrete proposal is to enforce the "extension-version.sql" and
> "extension-oldversion-newversion.sql" naming rules for scripts, which
> means getting rid of the script name parameter in control files.

Well, just for the record, we could extend the script property to be a
key value thing that pairs a version string with an upgrade script
name. We should then maybe have hstore in core to support that.

> (Instead, we could have a directory parameter that tells which directory
> holds all the install and upgrade scripts for the extension.) Also, the

Putting all those files in a directory per extension does not seems much
an option for me. The share/contrib directory is already somewhat of a
mess now, I can't imagine what it will look like if people are to use
the extension mechanism whenever it makes sense… Another +1 here :)

> "version" parameter should be renamed to something like "current_version"
> or "default_version". We also have to be wary of whether any other

I can already hear people wanting version aliases instead. We could
support e.g. 4 or 5 aliases like 'stable', 'support', 'alpha', 'beta'
and maybe 'experimental'. Then rather than defining "current_version"
authors would define any set of those keywords here, and CREATE
EXTENSION and ALTER EXTENSION would by default only care for
resp. 'stable' and 'support'.

And of course the commands would have to support either version strings
or version aliases.

> control-file parameters specify something that might be version-specific.
> Looking at the current list:
>
> comment: probably OK to consider this as a default for all versions.
> We already have the ability for any script file to override it, anyway.

check

> encoding: I don't see any big problem with insisting that all scripts for
> a given extension be in the same encoding.

check

> requires, relocatable and schema: These are problematic, because it's not
[...]
> IOW, we'd read "extension.control" to get the directory and
> default_version values, then determine the version we are installing or
> upgrading to, then see if there's an "extension-version.control" file
> in the extension's directory, and if so read that and let it replace
> the remaining parameters' values.

I like this idea better. Like, way way better. The previous one does
not even stand a chance :)

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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> No, you have to get *those other module authors* to make *their*
> extensions not relocatable so that you can depend on them.

Just tell me exactly in which world an extension's author is setting up
the dependencies in the 'required' property and yet fails to realise
that those dependencies mean his extension is not relocatable? And
will refuse to fix the problem when bugs are filled?

I'm not following your reasonning…
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

On Thu, Feb 10, 2011 at 3:58 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Thu, Feb 10, 2011 at 3:02 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> The design as I sketched it didn't need to make any assumptions at all
>>> about the meaning of the version identifiers.  But if you were willing
>>> to assume that the identifiers are comparable/sortable by some rule,
>
>> You don't need them to be sortable.  You just need them to be
>> comparable, and equality seems like a plenty good enough comparison
>> rule.  You can compute the shortest chain of upgrade scripts that can
>> take you from the current version to the target version.
>
> Hmm.  The problem with that is that once there are large numbers of
> intermediate versions, the number of potential paths grows
> exponentially.  I was envisioning an algorithm like this:
>
> 1.  Scan directory for upgrade scripts with oldversion = version we
> have, and take the one with largest newversion <= version we want.
>
> 2.  Apply this script (or more likely, just remember it until we've
> verified there is a chain leading to version we want).
>
> 3.  If now the version is not what we want, return to step 1.
>
> I don't see an equally efficient method if we only have equality.

It's certainly not exponential i.e. O(2^n) or something of that form.
Even a naive application of Dijkstra's algorithm is only going to be
O(n^2) in the number of versions, which is likely tolerable even if
upgrades are supported for dozens of old versions. It might break
down if there are hundreds of old versions, but that doesn't seem
likely to be a real problem in practice. But if you're concerned
about it, you can replace the linked list that the naive algorithm
uses with a binary heap or (if you really want to go nuts) a fibonacci
heap. The latter approach has a runtime of O(n + m lg m), where n is
the number of versions and m is the number of upgrade scripts. You
need one heck of a lot of backward compatibility before that algorithm
breaks a sweat. Even the binary heap is only O((n + m) lg m), which
pretty darn fast.

Personally, I think we'll be lucky if people support ten back revs,
let alone three hundred, but it's a simple matter of programming - and
an afternoon with an introductory algorithms textbook - to make it as
efficient as we could ever want it to be.

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

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> I don't think it's appropriate to hold extensions to a
> higher standard than we do loose objects --- especially when it takes
> superuser privileges to break things by moving an extension but not to
> break them by moving loose objects.

FWIW, +1.

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

On Thu, Feb 10, 2011 at 4:14 PM, Dimitri Fontaine
<dimitri(at)2ndquadrant(dot)fr> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> No, you have to get *those other module authors* to make *their*
>> extensions not relocatable so that you can depend on them.
>
> Just tell me exactly in which world an extension's author is setting up
> the dependencies in the 'required' property and yet fails to realise
> that those dependencies mean his extension is not relocatable?  And
> will refuse to fix the problem when bugs are filled?

No, the problem is this. I write an extension called foo. By
default, it installs in schema foo.

You write an extension called bar. By default, it installs in schema
bar. It also depends on foo.

Now Alice wants to install foo and bar. But she already has a schema
called foo, so she installs the extension foo in foo2. Now she tries
to install bar, but it doesn't work, because it is looking for objects
in schema foo, and on this system they are in foo2.

There's no way for you, as the author of bar, to fix this problem,
other than to persuade me, as the author of foo, that I should make my
extension not relocatable. I might not want to do that.

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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Thu, Feb 10, 2011 at 3:58 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Hmm. The problem with that is that once there are large numbers of
>> intermediate versions, the number of potential paths grows
>> exponentially.

> It's certainly not exponential i.e. O(2^n) or something of that form.
> Even a naive application of Dijkstra's algorithm is only going to be
> O(n^2) in the number of versions, which is likely tolerable even if
> upgrades are supported for dozens of old versions.

Well, okay, let's go with that plan then. If we don't need to assume
anything more than equality of version names being meaningful, I think
chaining update scripts automatically should solve most of the
complaints here. People who really want to maintain shortcut scripts
still could, but I think it'd be an unusual case.

regards, tom lane

On Thu, Feb 10, 2011 at 3:52 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> The real issue is what happens when you want to install
>> extension A, which depends on extensions B, C, and D, and B, C, and D
>> are all in non-standard locations.  Does that have any chance of
>> working under the system we're proposing?
>
> Again, it's not really any different from the case where the dependent
> objects are "loose" rather than members of an extension.  It's pretty
> much up to the user to be aware of the consequences.  If we had a way to
> mark individual functions as safe or unsafe for renames to happen, it'd
> be reasonable to extend that notion to whole extensions.  But we don't
> have that and I don't think it's appropriate to hold extensions to a
> higher standard than we do loose objects --- especially when it takes
> superuser privileges to break things by moving an extension but not to
> break them by moving loose objects.

Well, the difference is that loose objects are just on my system,
whereas extensions are supposed to work on anybody's system. I'm not
clear that it's possible to write an extension that depends on a
relocatable extension in a sensible way. If it is, objection
withdrawn.

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

On Feb 10, 2011, at 1:22 PM, Tom Lane wrote:

> Well, okay, let's go with that plan then. If we don't need to assume
> anything more than equality of version names being meaningful, I think
> chaining update scripts automatically should solve most of the
> complaints here. People who really want to maintain shortcut scripts
> still could, but I think it'd be an unusual case.

Yes, I think that this is a great solution. I only have to create on upgrade script for each release, and I don't have to worry about concatenating anything or be required to change my versioning algorithm.

+1

Finally, a solution!

Best,

David

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> Well, the difference is that loose objects are just on my system,
> whereas extensions are supposed to work on anybody's system. I'm not
> clear that it's possible to write an extension that depends on a
> relocatable extension in a sensible way. If it is, objection
> withdrawn.

I proposed that in this case, we bypass the relocatable property and
just have the system work out that reverse dependencies make all those
extensions not relocatable. Tom said that he does not see the point in
trying to limit this foot gun power.

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

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> Yes, I think that this is a great solution. I only have to create on
> upgrade script for each release, and I don't have to worry about
> concatenating anything or be required to change my versioning
> algorithm.

You still have to make sure that the C code remains compatible with any
intermediate release, for the whole life of your extension. But I agree
that it's way better than what we had before.

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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Thu, Feb 10, 2011 at 3:52 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Again, it's not really any different from the case where the dependent
>> objects are "loose" rather than members of an extension.

> Well, the difference is that loose objects are just on my system,
> whereas extensions are supposed to work on anybody's system. I'm not
> clear that it's possible to write an extension that depends on a
> relocatable extension in a sensible way. If it is, objection
> withdrawn.

I don't deny that there are risks here. But I think the value of being
able to move an extension when it is safe outweighs the difficulty that
sometimes it isn't safe. I think we can leave making it safer as a
topic for future investigation.

Dimitri did suggest treating an extension as nonrelocatable if there is
any other extension installed that depends on it. But that seems like
more of a kluge than a nice solution, primarily because it does nothing
for the loose-object risks. I'd rather just document that moving an
extension post-installation might break things, and leave it at that for
now.

regards, tom lane

On Feb 10, 2011, at 1:38 PM, Tom Lane wrote:

> I don't deny that there are risks here. But I think the value of being
> able to move an extension when it is safe outweighs the difficulty that
> sometimes it isn't safe. I think we can leave making it safer as a
> topic for future investigation.
>
> Dimitri did suggest treating an extension as nonrelocatable if there is
> any other extension installed that depends on it. But that seems like
> more of a kluge than a nice solution, primarily because it does nothing
> for the loose-object risks. I'd rather just document that moving an
> extension post-installation might break things, and leave it at that for
> now.

+1

David

On Thu, Feb 10, 2011 at 9:38 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

>> Well, the difference is that loose objects are just on my system,
>> whereas extensions are supposed to work on anybody's system.  I'm not
>> clear that it's possible to write an extension that depends on a
>> relocatable extension in a sensible way.  If it is, objection
>> withdrawn.
>
> I don't deny that there are risks here.  But I think the value of being
> able to move an extension when it is safe outweighs the difficulty that
> sometimes it isn't safe.  I think we can leave making it safer as a
> topic for future investigation.

Personally, I'ld rather be able to install the *same*
extension/version in different schemas at the same time then move an
extension from 1 schema to another, although I have no problems with
extensions moving out under a function's foot (just like loose
objects).

a.

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

Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
> "David E. Wheeler" <david(at)kineticode(dot)com> writes:
>> Yes, I think that this is a great solution. I only have to create on
>> upgrade script for each release, and I don't have to worry about
>> concatenating anything or be required to change my versioning
>> algorithm.

> You still have to make sure that the C code remains compatible with any
> intermediate release, for the whole life of your extension. But I agree
> that it's way better than what we had before.

What you have to do is make sure the C code remains compatible with any
version you are shipping an install or upgrade script for. Once you
decide that versions before, say, 2.0 are dead as doornails, you remove
all the older scripts, and you can delete the .so infrastructure for
them too.

For example, suppose I have foobar 1.5 installed on my system. If you
are shipping a package that includes foobar-1.5-2.0.sql, I should
reasonably expect that I can install that package first and upgrade the
extension afterwards. If you aren't shipping any script that claims it
can upgrade from 1.5, you don't need to provide .so compatibility either
--- it's clear that I have to upgrade first and install your newer
package after.

regards, tom lane

>> I don't see how that affects my point? You can spell "1.0" as "0.1"
>> and "1.1" as "0.2" if you like that kind of numbering, but I don't
>> see that that has any real impact. At the end of the day an author is
>> going to crank out a series of releases, and if he cares about people
>> using those releases for production, he's going to have to provide at
>> least a upgrade script to move an existing database from release N to
>> release N+1.
>
> Yeah, but given a rapidly-developing extension, that could create a lot of extra work. I don't know that there's much of a way around that, other than concatenating files to build migration scripts from parts (perhaps via `Make` as dim suggested). But it can get complicated pretty fast. My desire here is to keep the barrier to creating PostgreSQL extensions as low as is reasonably possible.

I assume this has already been discussed and rejected (or it wouldn't still be an issue), but what's wrong with the equivalent of \i in the successive .sql upgrade files? Or is the server running the scripts itself and no equivalent include feature exists in raw sql?

Regards,

David
--
David Christensen
End Point Corporation
david(at)endpoint(dot)com

David Christensen <david(at)endpoint(dot)com> writes:
> I assume this has already been discussed and rejected (or it wouldn't still be an issue), but what's wrong with the equivalent of \i in the successive .sql upgrade files? Or is the server running the scripts itself and no equivalent include feature exists in raw sql?

The latter. It wouldn't be that hard to invent something that would
pull in another file, but there are some issues concerning how you
figure out where to look for the file.

In any case, if we go down that path, we're still putting the burden on
the extension author to maintain a pile of little bitty script files --
a task that will get quite un-fun once you have dozens of active
versions. Automatically applying the files in sequence should be a lot
more convenient and less bug-prone.

regards, tom lane

Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> I think we should embed the version number in the script file name,

> What I don't like in that is that this restrict what the version strings
> can look like. In debian for example it's pretty common to use the ~
> separator, because 1.0~alpha1 < 1.0~beta < 1.0 with their sorting rules.
> And this trick won't work on windows filenames, AFAIK. That's one
> reason why I've wanted to stay away from having the version number
> strings encoded into the filename in the first place.

Well, yeah, but if you accept the principle that there should be a
separate script file for each version and update combination, you're
pretty much going to have to embed the version strings into the
filenames to keep your sanity.

My feeling about this is that we should recommend that version
identifiers be limited to ASCII letters, digits, dots, and underscore,
but assume that extension authors are adults and can grasp the risks
of using other characters. We should not be in the business of trying
to force authors to write portable code whether they want to or not.

> But if you get to sorting rules of version strings, you have to define
> them properly and impose them to users.

I think we've now converged on the agreement that we don't need to use
anything but equality checks. So it doesn't matter how the author
thinks the strings sort --- the upgrade scripts he provides define what
can follow what, and that's all we need to know.

> That way a SQL query can check if there's a new version available on
> your system. That's useful in some places to use as a monitoring alert
> coupled with nagios. The sysadmin team does the apt-get install part of
> the job and then the DBA team is paged to go upgrade the extensions in
> the databases, or shut the alarm somehow.

Well, you could look to see if there is a script that can update your
current version to something else. The existing pg_available_extensions
view needs to be rethought a bit, probably, but I'm not sure how.

>> So, concrete proposal is to enforce the "extension-version.sql" and
>> "extension-oldversion-newversion.sql" naming rules for scripts, which
>> means getting rid of the script name parameter in control files.

> Well, just for the record, we could extend the script property to be a
> key value thing that pairs a version string with an upgrade script
> name.

Yeah, but that doesn't get you away from having to name the script files
somehow, and it isn't going to be pleasant for anybody to use a naming
convention that isn't basically embedding the version numbers. We could
argue about details like whether dash is the best separator, but that's
pretty far down the list of important things.

>> "version" parameter should be renamed to something like "current_version"
>> or "default_version". We also have to be wary of whether any other

> I can already hear people wanting version aliases instead. We could
> support e.g. 4 or 5 aliases like 'stable', 'support', 'alpha', 'beta'
> and maybe 'experimental'. Then rather than defining "current_version"
> authors would define any set of those keywords here, and CREATE
> EXTENSION and ALTER EXTENSION would by default only care for
> resp. 'stable' and 'support'.

Hmm. That might be worth doing, but let's leave it for later when we
find out how much demand there really is. It does strike me that what
we ought to call the default-version parameter is just "default", since
that would fit in reasonably well with such an extension later.

regards, tom lane

It seems that we've mostly got consensus on the ideas of having a separate
script file for each installable version of an extension, and for each
basic version-upgrade action, with version numbers embedded in the file
names so that the control files don't need to be involved in identifying
what's what. And the core system is expected to be able to figure out how
to chain upgrade scripts together when necessary. Therefore, I'm now
ready to start kibitzing on syntax details :-)

First off, I don't much care for the name "CREATE WRAPPER EXTENSION".
WRAPPER is a misnomer in this case --- it's not wrapping anything.
I think Dimitri stated that he chose WRAPPER just because it was an
already existing keyword, but that isn't much of an excuse.

Second, I don't like anything about the term "null version" for the
case of bootstrapping from an old-style contrib module. Null implies
unknown, which isn't what we've got here --- the upgrade script is going
to make very definite assumptions about what's already there. Also,
given that we're trying to minimize assumptions about what the version
strings mean, reserving the string "null" for this purpose doesn't seem
like a good idea. I *definitely* don't want to allow
pg_extension.extversion to ever be a real SQL NULL.

Since we've agreed that there should be a version-to-install option
in CREATE EXTENSION, it seems to me that a workable solution is to
have a special convention for an "empty extension" version name.
Let's suppose that we choose the empty string as this reserved version
name. Then you would write

CREATE EXTENSION foo VERSION '' [ SCHEMA whatever ];

as the equivalent of CREATE WRAPPER EXTENSION. This would create the
extension's entry in pg_extension, but not run any script, and the
extension would initially have no members. After that you could do

ALTER EXTENSION foo UPGRADE TO '1.0';

and this would run the upgrade script "foo--1.0.sql", which would most
likely consist of just "ALTER EXTENSION foo ADD object" commands to
absorb the objects from the old-style contrib module into the extension.

One minor objection to this idea is that "foo--1.0.sql" looks more like a
typo than anything else. We could alternatively decide that the special
reserved version name is '0', so that bootstrap script names look like
"foo-0-1.0.sql". But if you don't want to have any built-in assumptions
about what version names mean, you might not like that idea.

Third, I'm also not thrilled with the syntax "ALTER EXTENSION foo
UPGRADE". UPGRADE isn't an existing keyword (note that VERSION is).
And I don't see any strong reason to assume that the version change
is an "upgrade". Authors might well choose to support sidegrades or
downgrades, especially with experimental modules. I suggest either

ALTER EXTENSION foo UPDATE [ TO 'version' ]

ALTER EXTENSION foo VERSION [ 'version' ]

the main excuse for the latter being that it's closer to the comparable
syntax in CREATE EXTENSION.

OK, that's enough bikeshedding for today ...

Comments?

regards, tom lane

On Feb 10, 2011, at 3:50 PM, Tom Lane wrote:

> It seems that we've mostly got consensus on the ideas of having a separate
> script file for each installable version of an extension, and for each
> basic version-upgrade action, with version numbers embedded in the file
> names so that the control files don't need to be involved in identifying
> what's what. And the core system is expected to be able to figure out how
> to chain upgrade scripts together when necessary. Therefore, I'm now
> ready to start kibitzing on syntax details :-)

Damn, I thought you were going to get rid of the control file there for a sec (in favor of Makefile variables). ;-P

> First off, I don't much care for the name "CREATE WRAPPER EXTENSION".
> WRAPPER is a misnomer in this case --- it's not wrapping anything.
> I think Dimitri stated that he chose WRAPPER just because it was an
> already existing keyword, but that isn't much of an excuse.

What's the WRAPPER bit for? I've forgotten.

> One minor objection to this idea is that "foo--1.0.sql" looks more like a
> typo than anything else. We could alternatively decide that the special
> reserved version name is '0', so that bootstrap script names look like
> "foo-0-1.0.sql". But if you don't want to have any built-in assumptions
> about what version names mean, you might not like that idea.

I'm fine with either of these. "foo-0-1.0.sql" might lead to fewer questions being asked. But I otherwise have no preference.

> Third, I'm also not thrilled with the syntax "ALTER EXTENSION foo
> UPGRADE". UPGRADE isn't an existing keyword (note that VERSION is).
> And I don't see any strong reason to assume that the version change
> is an "upgrade". Authors might well choose to support sidegrades or
> downgrades, especially with experimental modules. I suggest either
>
> ALTER EXTENSION foo UPDATE [ TO 'version' ]
>
> ALTER EXTENSION foo VERSION [ 'version' ]
>
> the main excuse for the latter being that it's closer to the comparable
> syntax in CREATE EXTENSION.
>
> OK, that's enough bikeshedding for today ...

The former reads much more clearly to me.

Best,

David

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> On Feb 10, 2011, at 3:50 PM, Tom Lane wrote:
>> First off, I don't much care for the name "CREATE WRAPPER EXTENSION".

> What's the WRAPPER bit for? I've forgotten.

It's to tell it to create an empty extension in preparation for
absorbing pre-existing objects from an old-style contrib module.
See what I mean? WRAPPER is not a useful keyword here.

regards, tom lane

On Feb 10, 2011, at 4:11 PM, Tom Lane wrote:

> It's to tell it to create an empty extension in preparation for
> absorbing pre-existing objects from an old-style contrib module.
> See what I mean? WRAPPER is not a useful keyword here.

Reminds me of creating a "shell type" so you can create I/O functions before *actually* creating the type. I don't suppose "SHELL" is available.

That reminds me (OT), it's currently impossible to write an uninstall script for a custom data type because of the circular dependency between a type and its I/O functions. There's no way around that sort of DROP EXTENSION CASCADE, is there?

Best,

David

On Thu, Feb 10, 2011 at 6:50 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Since we've agreed that there should be a version-to-install option
> in CREATE EXTENSION, it seems to me that a workable solution is to
> have a special convention for an "empty extension" version name.
> Let's suppose that we choose the empty string as this reserved version
> name.  Then you would write
>
>        CREATE EXTENSION foo VERSION '' [ SCHEMA whatever ];
>
> as the equivalent of CREATE WRAPPER EXTENSION.  This would create the
> extension's entry in pg_extension, but not run any script, and the
> extension would initially have no members.  After that you could do
>
>        ALTER EXTENSION foo UPGRADE TO '1.0';
>
> and this would run the upgrade script "foo--1.0.sql", which would most
> likely consist of just "ALTER EXTENSION foo ADD object" commands to
> absorb the objects from the old-style contrib module into the extension.

You don't really need any core support for this at all. People could
simply ship an empty file called foo-.sql, and then foo--1.0.sql to
upgrade to version 1.0. (Or if you want to pick 0 or bootstrap or
null to represent the loose object situation, that works too.)

> Third, I'm also not thrilled with the syntax "ALTER EXTENSION foo
> UPGRADE".  UPGRADE isn't an existing keyword (note that VERSION is).
> And I don't see any strong reason to assume that the version change
> is an "upgrade".  Authors might well choose to support sidegrades or
> downgrades, especially with experimental modules.  I suggest either
>
>        ALTER EXTENSION foo UPDATE [ TO 'version' ]
>
>        ALTER EXTENSION foo VERSION [ 'version' ]
>
> the main excuse for the latter being that it's closer to the comparable
> syntax in CREATE EXTENSION.
>
> OK, that's enough bikeshedding for today ...
>
> Comments?

Generally, +1. Like David, I prefer the UPDATE syntax.

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

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> On Feb 10, 2011, at 4:11 PM, Tom Lane wrote:
>> It's to tell it to create an empty extension in preparation for
>> absorbing pre-existing objects from an old-style contrib module.
>> See what I mean? WRAPPER is not a useful keyword here.

> Reminds me of creating a "shell type" so you can create I/O functions before *actually* creating the type. I don't suppose "SHELL" is available.

Actually, I was having second thoughts about that while at dinner. What
is the value of separating the bootstrap-an-extension-from-old-objects
operation into two steps? It's certainly not convenient for users, and
I don't see that the intermediate state with an empty extension has any
redeeming social value for developers either. (If you need such a thing,
just make an empty creation script.)

So: let's forget the concept of a special "null version" altogether, at
least from the user's-eye viewpoint. Instead, the way to bootstrap from
loose objects is something like

CREATE EXTENSION foo [ VERSION '1.0' ] [ FROM OLD ]

When you specify FROM OLD, this runs foo--1.0.sql instead of foo-1.0.sql
as it normally would. As before, that script contains ALTER EXTENSION
ADD commands instead of CREATE commands.

I like this because (a) it's one less step, and one less concept for
users to deal with, and (b) it's much harder to screw up. If you forget
FROM OLD when you needed it, the CREATE will fail with "object already
exists" errors. If you use FROM OLD when you shouldn't have, it will
fail with "object doesn't exist" errors. There's no way for the command
to apparently succeed while not actually creating the desired state.

(I'm not wedded to the phrase "FROM OLD" in particular, but it does
reuse already existing keywords. Also, maybe it'd be better to reserve
a version string such as "old" or "bootstrap", so that the bootstrap
script could be called something more legible like foo-bootstrap-1.0.sql.)

> That reminds me (OT), it's currently impossible to write an uninstall script for a custom data type because of the circular dependency between a type and its I/O functions. There's no way around that sort of DROP EXTENSION CASCADE, is there?

Yeah, DROP TYPE CASCADE is currently the accepted way to do that, and
it's definitely a tad risky in that you might zap more than just the
type and the I/O functions. But I don't feel a need to do anything
special to fix that, because grouping the type and the functions into
an extension will take care of the problem. You will not need to say
CASCADE unless you're actually wanting to delete objects outside the
extension.

regards, tom lane

On Feb 10, 2011, at 7:05 PM, Tom Lane wrote:

> I like this because (a) it's one less step, and one less concept for
> users to deal with, and (b) it's much harder to screw up. If you forget
> FROM OLD when you needed it, the CREATE will fail with "object already
> exists" errors. If you use FROM OLD when you shouldn't have, it will
> fail with "object doesn't exist" errors. There's no way for the command
> to apparently succeed while not actually creating the desired state.

+1

> (I'm not wedded to the phrase "FROM OLD" in particular, but it does
> reuse already existing keywords. Also, maybe it'd be better to reserve
> a version string such as "old" or "bootstrap", so that the bootstrap
> script could be called something more legible like foo-bootstrap-1.0.sql.)

Well, it's not really a bootstrap, is it? FROM OLD is okay, though not great. FROM BEFORE would be better. Or IMPLICIT? (It was implicitly an extension before.) Or, hey, FROM NOTHING! :-)

>> That reminds me (OT), it's currently impossible to write an uninstall script for a custom data type because of the circular dependency between a type and its I/O functions. There's no way around that sort of DROP EXTENSION CASCADE, is there?
>
> Yeah, DROP TYPE CASCADE is currently the accepted way to do that, and
> it's definitely a tad risky in that you might zap more than just the
> type and the I/O functions. But I don't feel a need to do anything
> special to fix that, because grouping the type and the functions into
> an extension will take care of the problem. You will not need to say
> CASCADE unless you're actually wanting to delete objects outside the
> extension.

Fair enough.

Thanks,

David

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> On Feb 10, 2011, at 7:05 PM, Tom Lane wrote:
>> (I'm not wedded to the phrase "FROM OLD" in particular, but it does
>> reuse already existing keywords. Also, maybe it'd be better to reserve
>> a version string such as "old" or "bootstrap", so that the bootstrap
>> script could be called something more legible like foo-bootstrap-1.0.sql.)

> Well, it's not really a bootstrap, is it? FROM OLD is okay, though not great. FROM BEFORE would be better. Or IMPLICIT? (It was implicitly an extension before.) Or, hey, FROM NOTHING! :-)

Hmm, you're right. The word bootstrap implies that we're starting from
nothing, which is exactly what we're *not* doing (starting from nothing
is the easy "clean install" case). By the same token, FROM NOTHING
isn't the right phrase either. An accurate description would be
something like FROM UNPACKAGED OBJECTS, but I'm not seriously proposing
that ...

Other ideas anyone?

regards, tom lane

On Feb 10, 2011, at 9:15 PM, Tom Lane wrote:

> Hmm, you're right. The word bootstrap implies that we're starting from
> nothing, which is exactly what we're *not* doing (starting from nothing
> is the easy "clean install" case). By the same token, FROM NOTHING
> isn't the right phrase either. An accurate description would be
> something like FROM UNPACKAGED OBJECTS, but I'm not seriously proposing
> that ...
>
> Other ideas anyone?

Implicit was the closest I saw in the reserved word list, if you're limiting things to that list. If not then, erm, LEGACY?

Best,

David

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> My feeling about this is that we should recommend that version
> identifiers be limited to ASCII letters, digits, dots, and underscore,
> but assume that extension authors are adults and can grasp the risks
> of using other characters. We should not be in the business of trying
> to force authors to write portable code whether they want to or not.

That's a reasonable view point too, and it's less work this way. I
would have liked to be able to sort versions in SQL as a gain on this
work, but well…

> I think we've now converged on the agreement that we don't need to use
> anything but equality checks. So it doesn't matter how the author
> thinks the strings sort --- the upgrade scripts he provides define what
> can follow what, and that's all we need to know.

Check.

> Well, you could look to see if there is a script that can update your
> current version to something else. The existing pg_available_extensions
> view needs to be rethought a bit, probably, but I'm not sure how.

Maybe a SRF would do better here, returning the three columns step, from
and to. The step is the order in which to read the rows. There would
be some windowing or groups in the result set, but that should be ok.

>> I can already hear people wanting version aliases instead. We could
>> support e.g. 4 or 5 aliases like 'stable', 'support', 'alpha', 'beta'
>> and maybe 'experimental'. Then rather than defining "current_version"
>> authors would define any set of those keywords here, and CREATE
>> EXTENSION and ALTER EXTENSION would by default only care for
>> resp. 'stable' and 'support'.
>
> Hmm. That might be worth doing, but let's leave it for later when we
> find out how much demand there really is. It does strike me that what
> we ought to call the default-version parameter is just "default", since
> that would fit in reasonably well with such an extension later.

We could go as far as not requiring anything but considering any unknown
parameter as a version alias, or setup a GUC placeholder so that the
control file parsing is able to read version.defaut = '1.0' and others.

Then we would just document what the default aliases are used by the
commands CREATE EXTENSION and ALTER EXTENSION UPDATE TO. The big
advantage of doing so is that it's then easy for extension authors to
manage EOL.

ALTER EXTENSION foo UPDATE;
ERROR: there's no 'support' version available from version 1.2.3

Then you have to write ALTER EXTENSION foo UPDATE TO '2.0' or even
UPDATE TO 'stable', and you realise it's a "major" upgrade, so you need
to recheck the extension release notes etc.

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

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> Actually, I was having second thoughts about that while at dinner. What
> is the value of separating the bootstrap-an-extension-from-old-objects
> operation into two steps? It's certainly not convenient for users, and
> I don't see that the intermediate state with an empty extension has any
> redeeming social value for developers either. (If you need such a thing,
> just make an empty creation script.)

The only reason for doing it this way is that we used to only support 1
available version of an extension at a time, and the commands didn't
know zip about versions. Now that you're putting VERSION support into
CREATE and ALTER EXTENSION commands, I agree that a two steps process
here is to reconsider.

> So: let's forget the concept of a special "null version" altogether, at
> least from the user's-eye viewpoint. Instead, the way to bootstrap from
> loose objects is something like
>
> CREATE EXTENSION foo [ VERSION '1.0' ] [ FROM OLD ]
>
> When you specify FROM OLD, this runs foo--1.0.sql instead of foo-1.0.sql
> as it normally would. As before, that script contains ALTER EXTENSION
> ADD commands instead of CREATE commands.

Sounds good. The problem we have here, it seems to me, is that we don't
know what was the previous version of the extension. It certainly
existed, it's just that PostgreSQL does not know about it. That's what
drove me to think about it as a 'FROM NULL' update.

If you buy into the version alias feature, then what we can do here is
supporting any alias as the FROM argument. The control file would then
associate version.whatever = 0.9 and then the file is foo-0.9-1.0.sql in
your example.

The mechanism would be about the exact thing you described, but with
just a useful indirection in between so that you type:

CREATE EXTENSION foo VERSION stable FROM whatever;

If we require those version aliases to be accepted as GUC names I guess
we can bypass quoting them at the SQL level too, right?

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

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> First off, I don't much care for the name "CREATE WRAPPER EXTENSION".
> WRAPPER is a misnomer in this case --- it's not wrapping anything.
> I think Dimitri stated that he chose WRAPPER just because it was an
> already existing keyword, but that isn't much of an excuse.

Let's get rid of the two-stages idea now that we have proper VERSION
support in the commands, as seen in another email.

> Second, I don't like anything about the term "null version" for the
> case of bootstrapping from an old-style contrib module. Null implies
> unknown, which isn't what we've got here --- the upgrade script is going

Yes it's what we have, the way I see it at least. The version number
certainly exists, it's just that PostgreSQL had no way to know about it
until now. Certainly that concept can be called unknown…

> One minor objection to this idea is that "foo--1.0.sql" looks more like a
> typo than anything else. We could alternatively decide that the special
> reserved version name is '0', so that bootstrap script names look like
> "foo-0-1.0.sql". But if you don't want to have any built-in assumptions
> about what version names mean, you might not like that idea.

I hope you will like the version aliases proposal I've been making in
other emails, just saying it again as a loose cross-reference :)

> Third, I'm also not thrilled with the syntax "ALTER EXTENSION foo
> UPGRADE". UPGRADE isn't an existing keyword (note that VERSION is).

Fair enough.

> And I don't see any strong reason to assume that the version change
> is an "upgrade". Authors might well choose to support sidegrades or
> downgrades, especially with experimental modules. I suggest either
>
> ALTER EXTENSION foo UPDATE [ TO 'version' ]
>
> ALTER EXTENSION foo VERSION [ 'version' ]
>
> the main excuse for the latter being that it's closer to the comparable
> syntax in CREATE EXTENSION.

I somehow would prefer a mix of those two proposals:

ALTER EXTENSION foo TO VERSION 'version';
ALTER EXTENSION foo TO VERSION alias;

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

On 02/11/2011 05:05 AM, Tom Lane wrote:
> Actually, I was having second thoughts about that while at dinner. What
> is the value of separating the bootstrap-an-extension-from-old-objects
> operation into two steps? It's certainly not convenient for users, and
> I don't see that the intermediate state with an empty extension has any
> redeeming social value for developers either. (If you need such a thing,
> just make an empty creation script.)
>
> So: let's forget the concept of a special "null version" altogether, at
> least from the user's-eye viewpoint. Instead, the way to bootstrap from
> loose objects is something like
>
> CREATE EXTENSION foo [ VERSION '1.0' ] [ FROM OLD ]
The above command assumes there is only one unpackaged version from
which users might update from. Is that what is wanted? I am wondering if
FROM OLD should be FROM OLD VERSION version (or better: FROM UNPACKAGED
VERSION version). This would also solve how to name the old version(s).
Author decides.

- Anssi

On Fri, Feb 11, 2011 at 12:15 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "David E. Wheeler" <david(at)kineticode(dot)com> writes:
>> On Feb 10, 2011, at 7:05 PM, Tom Lane wrote:
>>> (I'm not wedded to the phrase "FROM OLD" in particular, but it does
>>> reuse already existing keywords.  Also, maybe it'd be better to reserve
>>> a version string such as "old" or "bootstrap", so that the bootstrap
>>> script could be called something more legible like foo-bootstrap-1.0.sql.)
>
>> Well, it's not really a bootstrap, is it? FROM OLD is okay, though not great. FROM BEFORE would be better. Or IMPLICIT? (It was implicitly an extension before.) Or, hey, FROM NOTHING! :-)
>
> Hmm, you're right.  The word bootstrap implies that we're starting from
> nothing, which is exactly what we're *not* doing (starting from nothing
> is the easy "clean install" case).  By the same token, FROM NOTHING
> isn't the right phrase either.  An accurate description would be
> something like FROM UNPACKAGED OBJECTS, but I'm not seriously proposing
> that ...

Well, you're bootstrapping the extension mechanism.

> Other ideas anyone?

I still think you might be over-designing this. Upgrading from the
pre-extension world doesn't need to be elegant; it just has to work.
And you can do that yourself, with the proposed infrastructure:

http://archives.postgresql.org/pgsql-hackers/2011-02/msg00911.php

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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> I still think you might be over-designing this. Upgrading from the
> pre-extension world doesn't need to be elegant; it just has to work.

Allow me to disagree here. The main use case is not supporting users
that upgrade with extensions to 9.1, but to allow people working on
their own applications to some day realise they could as well package
their PL code into a set of extensions.

Please check my version aliases proposal and how it fits in there.
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

On Fri, Feb 11, 2011 at 9:00 AM, Dimitri Fontaine
<dimitri(at)2ndquadrant(dot)fr> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> I still think you might be over-designing this.  Upgrading from the
>> pre-extension world doesn't need to be elegant; it just has to work.
>
> Allow me to disagree here.  The main use case is not supporting users
> that upgrade with extensions to 9.1, but to allow people working on
> their own applications to some day realise they could as well package
> their PL code into a set of extensions.

Sure, but we're talking about adding core code to accomplish two things:

1. Avoid the need for packagers to ship one empty file.

2. Possibly, allow the operation to be completed in one command instead of two.

This is not exactly cutting anyone off at the kneecaps.

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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Fri, Feb 11, 2011 at 9:00 AM, Dimitri Fontaine
> <dimitri(at)2ndquadrant(dot)fr> wrote:
>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> I still think you might be over-designing this. Upgrading from the
>>> pre-extension world doesn't need to be elegant; it just has to work.
>>
>> Allow me to disagree here.

> Sure, but we're talking about adding core code to accomplish two things:
> 1. Avoid the need for packagers to ship one empty file.
> 2. Possibly, allow the operation to be completed in one command instead of two.

The empty file might not be a big deal, but I think that the user
experience *is* a big deal. For the vast majority of users, dealing
with an upgrade for some contrib module they are already using will
be their first experience with the extension mechanism. If it's awkward
or requires them to think about strange concepts like "null versions",
it's going to leave a bad taste in their mouths. Furthermore, I
confidently predict that some people will screw it up by issuing only
the first CREATE and not the second ALTER, leaving them with a database
that still works but not in the intended fashion; from which we will get
bug reports, perhaps years later.

I agree it's a bit annoying to expend effort on something that will have
only a one-shot use in any one installation, but to my mind this is an
important "fit and finish" issue. For analogy, some might think that
all the effort we spend on message translatability is overkill, but
I think it contributes to a good user experience.

regards, tom lane

=?ISO-8859-1?Q?Anssi_K=E4=E4ri=E4inen?= <anssi(dot)kaariainen(at)thl(dot)fi> writes:
> The above command assumes there is only one unpackaged version from
> which users might update from. Is that what is wanted? I am wondering if
> FROM OLD should be FROM OLD VERSION version (or better: FROM UNPACKAGED
> VERSION version). This would also solve how to name the old version(s).
> Author decides.

Yeah, that's an interesting point. I don't think that there are any
contrib modules for which we'd bother with such a thing, but it's easily
possible that PostGIS or other third parties would be interested in
supporting direct upgrades from older versions of their modules.

I did actually have a scheme in mind whereby somebody could do that if
they had to given my proposal of yesterday, but I won't bore you with
the details because it was a crock. Thinking about the problem this
morning, I came to the same solution you did, although I was thinking
of a slightly more compact syntax:

CREATE EXTENSION foo [ VERSION targetversion ] [ FROM oldversion ]

The presence of FROM causes us to run foo-oldversion-targetversion.sql
instead of foo-targetversion.sql. As before, that script would consist
mostly of ALTER EXTENSION ADD rather than CREATE commands. What this
means is we aren't hard-wiring any specific name for "pre extension"
versions, and we aren't restricting the author to support updating from
only one old version.

The main risk factor I can see here is that users might give the wrong
old version parameter, causing the system to try to run a script that
was meant for updating some post-extensioning version instead of
pre-extensioning (ie, "CREATE EXTENSION foo FROM '1.0'" when the right
thing would have been "CREATE EXTENSION foo FROM 'old'"). I think
however that we can live with that risk, on two grounds:

1. If you pick the wrong FROM version, the upgrade script will almost
certainly fail, because the objects won't exist or won't be in the state
it expects (ie, not already members of the extension).

2. The main use for this feature will be early in the lifespan of
extensions, when there aren't going to be many post-extension upgrade
scripts around to pose a risk of confusion. By the time there's really
much risk of people making this mistake, it won't matter anymore.

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> CREATE EXTENSION foo [ VERSION targetversion ] [ FROM
oldversion ]

I came to the same conclusion but added my version aliases idea in
there so that it could maybe be easy for the user not to confuse
things.

I still think that free form version aliases and some defaults
used by the core code is a very interesting feature to have, but I
can see that it's not required for the feature to fully work.

> 1. If you pick the wrong FROM version, the upgrade script will
> almost certainly fail, because the objects won't exist or won't
> be in the state it expects (ie, not already members of the
> extension).

Is there a test somewhere that when CREATE OR REPLACE FUNCTION
runs from an extension's script at upgrade, the function must
already be attached to the extension if it exists in the system?
Ditto for views etc?

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

Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>>> I can already hear people wanting version aliases instead. We could
>>> support e.g. 4 or 5 aliases like 'stable', 'support', 'alpha', 'beta'
>>> and maybe 'experimental'. Then rather than defining "current_version"
>>> authors would define any set of those keywords here, and CREATE
>>> EXTENSION and ALTER EXTENSION would by default only care for
>>> resp. 'stable' and 'support'.

>> Hmm. That might be worth doing, but let's leave it for later when we
>> find out how much demand there really is. It does strike me that what
>> we ought to call the default-version parameter is just "default", since
>> that would fit in reasonably well with such an extension later.

> We could go as far as not requiring anything but considering any unknown
> parameter as a version alias, or setup a GUC placeholder so that the
> control file parsing is able to read version.defaut = '1.0' and others.

I think having the code do something with "any unknown parameter" is a
seriously bad idea: it removes a useful error check, and it opens a
strong likelihood that different versions of PG will interpret the same
control file differently.

After a bit of reflection I think we should stick with "default_version"
as the parameter name in 9.1. If we want to open it up to allowing
arbitrary version aliases later, we can let it accept "xxx_version" as
defining an alias "xxx". That seems a lot safer than interpreting any
old unrecognized parameter name as a version alias.

> Then we would just document what the default aliases are used by the
> commands CREATE EXTENSION and ALTER EXTENSION UPDATE TO. The big
> advantage of doing so is that it's then easy for extension authors to
> manage EOL.

> ALTER EXTENSION foo UPDATE;
> ERROR: there's no 'support' version available from version 1.2.3

> Then you have to write ALTER EXTENSION foo UPDATE TO '2.0' or even
> UPDATE TO 'stable', and you realise it's a "major" upgrade, so you need
> to recheck the extension release notes etc.

Uh, not sure how you're envisioning that working? If it fails to find
an upgrade script path from the current version to whatever is default,
it will still fail to find any path after you explicitly tell it you
want to upgrade to that version.

regards, tom lane

Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
> Is there a test somewhere that when CREATE OR REPLACE FUNCTION
> runs from an extension's script at upgrade, the function must
> already be attached to the extension if it exists in the system?
> Ditto for views etc?

IIRC, the current behavior is that C.O.R.F. on an existing function
preserves the function's existing extension membership, if any.
It doesn't matter whether you are doing it from an extension script
or not. I'm not really eager to change that, and I doubt it would
make any difference anyway to the use-case under consideration ---
if the 1.0-to-1.1 script is adding a function, it's unlikely the
function existed pre-1.0 ...

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> After a bit of reflection I think we should stick with "default_version"
> as the parameter name in 9.1. If we want to open it up to allowing
> arbitrary version aliases later, we can let it accept "xxx_version" as
> defining an alias "xxx". That seems a lot safer than interpreting any
> old unrecognized parameter name as a version alias.

That was my first idea, like I did with upgrade_from_xxx, but though you
wouldn't like it so much, so proposed the version.xxx form instead :)

>> ALTER EXTENSION foo UPDATE;
>> ERROR: there's no 'support' version available from version 1.2.3
>
>> Then you have to write ALTER EXTENSION foo UPDATE TO '2.0' or even
>> UPDATE TO 'stable', and you realise it's a "major" upgrade, so you need
>> to recheck the extension release notes etc.
>
> Uh, not sure how you're envisioning that working? If it fails to find
> an upgrade script path from the current version to whatever is default,
> it will still fail to find any path after you explicitly tell it you
> want to upgrade to that version.

That's not exactly what happens here. There would be no "support"
version alias in the control file, so no way to upgrade to it, and
"support" would happen to be what ALTER EXTENSION foo UPDATE would
consider when you don't mention explicitly the target version.

However, when you do say that you want to upgrade to '2.0' or to
'stable', now the upgrade script certainly exists and the version alias
too, so that the upgrade is possible. Only explicitly though.

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

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>>> 1. If you pick the wrong FROM version, the upgrade script will almost
>>> certainly fail, because the objects won't exist or won't be in the state
>>> it expects (ie, not already members of the extension).
> IIRC, the current behavior is that C.O.R.F. on an existing function
> preserves the function's existing extension membership, if any.

Right. But it does not catch the case when you CORF on a function that
is not already into the extension. I don't see how to distinguish that
from adding a new function into it at upgrade time. So I'm having a
hard time understanding what you meant in your point above.

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

Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> Uh, not sure how you're envisioning that working? If it fails to find
>> an upgrade script path from the current version to whatever is default,
>> it will still fail to find any path after you explicitly tell it you
>> want to upgrade to that version.

> That's not exactly what happens here. There would be no "support"
> version alias in the control file, so no way to upgrade to it, and
> "support" would happen to be what ALTER EXTENSION foo UPDATE would
> consider when you don't mention explicitly the target version.

> However, when you do say that you want to upgrade to '2.0' or to
> 'stable', now the upgrade script certainly exists and the version alias
> too, so that the upgrade is possible. Only explicitly though.

Hmm. To make that work, we'd have to have ALTER EXTENSION UPDATE use a
different default version name from what CREATE EXTENSION uses (unless
you're willing to also break use of CREATE EXTENSION without an explicit
target version). I was intending to have "default_version" identify the
default target for both cases. While we could have different parameters
for the two cases, I think it would mostly just cause confusion.

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> That's not exactly what happens here. There would be no "support"
>> version alias in the control file, so no way to upgrade to it, and
>> "support" would happen to be what ALTER EXTENSION foo UPDATE would
>> consider when you don't mention explicitly the target version.
>
>> However, when you do say that you want to upgrade to '2.0' or to
>> 'stable', now the upgrade script certainly exists and the version alias
>> too, so that the upgrade is possible. Only explicitly though.
>
> Hmm. To make that work, we'd have to have ALTER EXTENSION UPDATE use a
> different default version name from what CREATE EXTENSION uses (unless

Yes. I see that as a good feature to have. stable and support looks
like good default aliases for me, but again, IANANS (native speaker).

> you're willing to also break use of CREATE EXTENSION without an explicit
> target version). I was intending to have "default_version" identify the
> default target for both cases. While we could have different parameters
> for the two cases, I think it would mostly just cause confusion.

I happen to think it would avoid too much confusion myself. There's a
semantic difference here, that's not just playing with keywords. And
we're adding nice error checks to help stay on the safe side.

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

OK, let me see if I can summarize what I think we've agreed to:

CREATE syntax is extended to

CREATE EXTENSION extname [WITH] [SCHEMA s] [VERSION v] [FROM oldv]

If VERSION is not specified, v is taken from default_version in the
control file, or fail if that's not given either. We create the
pg_extension entry and then run the script extname-v.sql, or
extname-oldv-v.sql if FROM is present.

ALTER syntax is extended with

ALTER EXTENSION extname UPDATE [TO v]

Again, if v is not specified, it is taken from default_version in the
control file, or fail if that's not given either. Here we take oldv
from the current pg_extension.extversion field, and then run the script
extname-oldv-v.sql.

We will add logic to find a chain of update scripts leading from oldv to
v, in case that exact combination is not available in the extension's
script directory. (NOTE: maybe in the CREATE ... FROM case, it would be
a better idea to not do that search, but insist on finding exactly
extname-oldv-v.sql? That would provide at least a little bit of extra
protection against wrong FROM choice. Not sure how much it helps
though.)

Version identifiers will be ColId_or_Sconst in the grammar, ie, you can
omit quotes if they're valid SQL identifiers. I'm not sure this helps
with typical choices of version strings, but we might as well allow it.

Version strings will have no hard-wired semantics except equality; we
don't need a sorting rule. We must however forbid "-" in version
strings, to avoid ambiguity as to whether a file name represents an
install or upgrade script. (Note: "-" in extension names poses a
hazard as well; not within a single extension, but for example
foo-bar's install scripts could be confused with foo's upgrade
scripts. However, I think we need not forbid "-" in extension names
since this risk can be avoided by giving foo-bar its own script
directory.) It also seems to me to be a good idea to forbid ".." and
directory separators in both types of names, because otherwise CREATE
EXTENSION could be used to probe the file system. That's not really an
issue right now, with use of the command being restricted to superusers
anyway, but it's inevitable that we'll want to relax that restriction.

We will also add code to allow per-version control files
extname-v.control in the script directory. After determining the
version we plan to install or update to, we read the per-version control
file if any, and let it override parameters from the primary control
file. (This implies for example that a per-version control file's
encoding setting would control all update scripts read while trying to
get to that version. I'm not sure how useful that is --- given the
chaining behavior, really you're going to have to use the same encoding
throughout the extension's update files. Maybe better to disallow
encoding in per-version control files?)

Comments?

regards, tom lane

Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> Hmm. To make that work, we'd have to have ALTER EXTENSION UPDATE use a
>> different default version name from what CREATE EXTENSION uses (unless

> Yes. I see that as a good feature to have. stable and support looks
> like good default aliases for me, but again, IANANS (native speaker).

I'm not very happy with that at all, either as to the concept or the
specific version-alias names. I don't think that CREATE and ALTER
really need different default version targets. And those choices of
names carry far too much baggage. "Default" is what they are as far as
the system is concerned, but names like those imply a lot more.

Anybody else have an opinion on this detail?

regards, tom lane

On Fri, Feb 11, 2011 at 12:35 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> OK, let me see if I can summarize what I think we've agreed to:
>
> CREATE syntax is extended to
>
>        CREATE EXTENSION extname [WITH] [SCHEMA s] [VERSION v] [FROM oldv]

It strikes me that if you used the same options syntax here that we're
already using for EXPLAIN and VACUUM and COPY, you wouldn't have to
worry about adding keywords for current or future options.

i.e.

CREATE EXTENSION extname [ ( option [ , ... ] ) ]

where option can be one of:

SCHEMA blah
VERSION blah
FROM blah

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

On Fri, Feb 11, 2011 at 1:06 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
>> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>>> Hmm.  To make that work, we'd have to have ALTER EXTENSION UPDATE use a
>>> different default version name from what CREATE EXTENSION uses (unless
>
>> Yes.  I see that as a good feature to have.  stable and support looks
>> like good default aliases for me, but again, IANANS (native speaker).
>
> I'm not very happy with that at all, either as to the concept or the
> specific version-alias names.  I don't think that CREATE and ALTER
> really need different default version targets.  And those choices of
> names carry far too much baggage.  "Default" is what they are as far as
> the system is concerned, but names like those imply a lot more.
>
> Anybody else have an opinion on this detail?

I agree with you.

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