Re: ALTER EXTENSION UPGRADE, v3

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