Re: ALTER EXTENSION UPGRADE, v3

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
ofextensive 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
stuffnow to generate other files that will contain a subset of the pgTAP functionality. I don't think I'd ever write
upgradescripts for pgTAP, but I've worked with a lot of Perl modules that have followed similar aggressive development,
andcan imagine times when I'd need to write upgrade scripts for aggressively-developed PostgreSQL extensions. And I
quailat the idea. Lord help me if I'd need to also write create patches for my upgrade scripts to support older
versionsof 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
pathsdevelop, 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
allowingan "extension distribution" to somehow work both on 9.1 with EXTENSIONs and in < 9.0 as PGXS-installed modules
currentlywork, without too much pain to the developer to support previous versions of PostgreSQL. 

Best,

David