ALTER EXTENSION UPGRADE, v3

On Wed, Feb 2, 2011 at 03:21, Dimitri Fontaine <dimitri@2ndquadrant.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.takahiro@gmail.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@2ndquadrant.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.takahiro@gmail.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@.lo set extension lo;
alter function @extschema@.lo_oid(lo) set extension lo;
alter function @extschema@.lo_manage() set extension lo;

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

CREATE OR REPLACE FUNCTION @extschema@.lo_newfunc() …

So the upgrade script from version NULL to 2.0 is:

alter domain @extschema@.lo set extension lo;
alter function @extschema@.lo_oid(lo) set extension lo;
alter function @extschema@.lo_manage() set extension lo;
CREATE OR REPLACE FUNCTION @extschema@.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@.lo set extension lo;
alter function @extschema@.lo_oid(lo) set extension lo;
alter function @extschema@.lo_manage() set extension lo;

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

CREATE OR REPLACE FUNCTION @extschema@.lo_newfunc() …

So the upgrade script from version NULL to 2.0 is:

alter domain @extschema@.lo set extension lo;
alter function @extschema@.lo_oid(lo) set extension lo;
alter function @extschema@.lo_manage() set extension lo;
CREATE OR REPLACE FUNCTION @extschema@.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@kineticode.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@.lo set extension lo;
alter function @extschema@.lo_oid(lo) set extension lo;
alter function @extschema@.lo_manage() set extension lo;

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

CREATE OR REPLACE FUNCTION @extschema@.lo_newfunc() …

So the upgrade script from version NULL to 2.0 is:

alter domain @extschema@.lo set extension lo;
alter function @extschema@.lo_oid(lo) set extension lo;
alter function @extschema@.lo_manage() set extension lo;
CREATE OR REPLACE FUNCTION @extschema@.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@kineticode.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@.lo set extension lo;
alter function @extschema@.lo_oid(lo) set extension lo;
alter function @extschema@.lo_manage() set extension lo;

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

CREATE OR REPLACE FUNCTION @extschema@.lo_newfunc() …

So the upgrade script from version NULL to 2.0 is:

alter domain @extschema@.lo set extension lo;
alter function @extschema@.lo_oid(lo) set extension lo;
alter function @extschema@.lo_manage() set extension lo;
CREATE OR REPLACE FUNCTION @extschema@.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@kineticode.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@kineticode.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@kineticode.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@highrise.ca                                       command like a king,
http://www.highrise.ca/                                   work like a slave.

"David E. Wheeler" <david@kineticode.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@kineticode.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@kineticode.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@highrise.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