Re: ALTER EXTENSION UPGRADE, v3

On Feb 11, 2011, at 9:35 AM, Tom Lane 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]
>
> 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.

Sounds good. One nit: can't we call the line in the control file "version" rather than "default_version"? I've been thinking of the control file as describing a release of an extension, which of course has a version, not a default version.

Oh, so what should oldv be to indicate creating from a legacy extension?

> 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.

How do you determine the "script directory"? I've been using sql/ in my PGXN distributions.

> (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.)

Meh. Just goes to creating more work for the extension maintainer, who would then have to consider whether or not to make a bunch of omnibus upgrade scripts for any given release, just in case some user specified a FROM clause. Not thrilled with that. Seems to me either there's a chain or there isn't.

> 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.

I guess it's good for purely numeric versions, like 0.25 or 9.0, but not for dotted-integer versions like 1.34.0.

> 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.

Yeah. Might be worth considering using some other less common character as the delimiter. Maybe + or ^? not a big deal, though. I guess / should also be forbidden, eh?

> 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?)

+1.

Best,

David