subject:"\[HACKERS\] ALTER EXTENSION UPGRADE, v3"

marcin mank  writes:
> On Fri, Feb 11, 2011 at 8:15 PM, Tom Lane  wrote:
>> Hmm. Â That seems like it would require a rather pathological collection
>> of upgrade scripts. Â In particular why would you have a one-step upgrade
>> from 1.1 to 2.0 but no short path from 1.2?

> Say we have 20 versions, with up- and downgrade scripts between
> consecutive versions, and a fast path from 5 to 20.
> if we are at version 6, it would go 6->5->20. if 6->5 drops a table,
> we`re in trouble.

So basically, to get into trouble you need all three of these elements:

1. A downgrade script;

2. A fast-path upgrade script that reverses the effect of the downgrade
and skips at least two versions further than that;

3. An irreversible action in the downgrade script.

That seems sufficiently far-fetched to me that documenting the hazard
ought to be enough (and I've done so).

If we could identify downgrade scripts, it would be easy enough to
modify the shortest-path algorithm to not use them unless necessary
(by assigning them a very large weight instead of weight 1).  However,
I'm still not excited about defining a version comparison rule just for
that.  One possibility is to invent a file naming rule that marks
downgrade scripts, for example an extra dash:

extension-oldversion-newversion-.sql

I'm really not convinced it's worth the trouble, though.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

2011-02-11 Thread marcin mank

On Fri, Feb 11, 2011 at 8:15 PM, Tom Lane  wrote:
> =?iso-8859-1?Q?K=E4=E4ri=E4inen_Anssi?=  writes:
>> This has the side effect that you can also have downgrade scripts. I
>> don't know if this is designed or just coincidental, so thought it
>> would be worth mentioning.
>> The worst case is that if you are upgrading from 1.2 to 2.0 the path
>> is 1.2 -> 1.1 -> 2.0, even if there exists a path 1.2 -> 1.8 -> 1.9 ->
>> 2.0. This could potentially result in data loss, if the downgrade
>> drops some columns or something like that.
>
> Hmm.  That seems like it would require a rather pathological collection
> of upgrade scripts.  In particular why would you have a one-step upgrade
> from 1.1 to 2.0 but no short path from 1.2?
>


Say we have 20 versions, with up- and downgrade scripts between
consecutive versions, and a fast path from 5 to 20.
if we are at version 6, it would go 6->5->20. if 6->5 drops a table,
we`re in trouble.

Greetings
Marcin Mańk

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

"David E. Wheeler"  writes:
> On Feb 11, 2011, at 10:30 AM, Tom Lane wrote:
>> It can be specified by a "directory" parameter in the control file,
>> and defaults to the same place the control file is.  Right now, that's
>> $PREFIX/share/contrib/.

> Frankly, given the likely proliferation of upgrade scripts, I think it ought 
> to be $PREFIX/share/contrib/$extension/

I think it should be up to the extension author to decide to do that,
just as it is now.  However I do see a bug in the current PGXS coding:
if MODULEDIR is set, that should affect DATA and DOCS files but *not*
the primary extension control file, because the place where that must
be is hard-wired into extension.c.  MODULEDIR should just affect the
files whose location will be determined by the directory parameter in
the control file.  Then, if an extension author wants to put his stuff
in his own subdirectory, he sets something like

MODULEDIR = extension/hstore

in the makefile and

directory = hstore

in the control file.

>> One other thing that ought to be discussed is
>> whether to stick with that choice or change it.  Given that some people
>> have great antipathy to the word "contrib", I suspect there will be
>> argument to change it --- but to do so, I think we'd have to change the
>> default MODULEDIR in PGXS, and I'm not sure that's a good idea.

> Add EXTENSIONDIR and make it "extensions".

Well, it's not exactly that easy, because we don't want to break the
file layout that an old-style module is expecting PGXS to produce.
But I guess what we could do is make the default definition of MODULEDIR
depend on whether or not EXTENSION has been defined: "extension" if so,
and backwards-compatible "contrib" if not.

BTW, I'm inclined to make it $PREFIX/share/extension not extensions,
but I have to admit I'm hard-pressed to explain exactly why that feels
better.  Anybody else care about that detail?

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Tom Lane  writes:
> I don't see what that does for you.  This is still all being examined by
> a particular major release of PG, so what will it do with a require that
> specifies some other major release?  Nothing useful.  And there's a very
> significant downside, which is that this takes us right back to the
> make-work of having to change all the contrib modules' control files in
> every release cycle.

Mmm, yes we're missing the | operator for dependencies here.  I didn't
expect extensions that support more than one major version at a time to
use the feature, but obviously that's not good enough.

> Once again, I see the version numbers as being specifiers for versions
> of the install script files.  Not the Postgres version those files are
> being run in.  Confusing the two is a bad idea.  Confusing the install
> script version numbers with minor release numbers (bugfix level
> identifiers) is even worse.  You *don't* want to change these numbers if
> you're just fixing a bug at the C code level.

Agreed on the C side maintenance and releasing.  What if your extension
is PL/pgSQL only and you just fixed a bug in one of the functions?

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Tom Lane  writes:
> Anything that got kicked out to pgfoundry would presumably start acting
> that way.  Anything that's part of core git is going to stay on the same
> release cycle as the core, thank you very much.  Release engineering is
> a big enough headache around here already.

Yeah, I should have inquired before to propose.  I see two solutions
here, one is to just do as you say, the other one would be to have a
separate git repository for extensions.  You can ignore this if only the
default option (your proposal) is sensible…

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Tom Lane  writes:
>> The worst case is that if you are upgrading from 1.2 to 2.0 the path
>> is 1.2 -> 1.1 -> 2.0, even if there exists a path 1.2 -> 1.8 -> 1.9 ->
>> 2.0. This could potentially result in data loss, if the downgrade
>> drops some columns or something like that.
>
> Hmm.  That seems like it would require a rather pathological collection
> of upgrade scripts.  In particular why would you have a one-step upgrade
> from 1.1 to 2.0 but no short path from 1.2?

I think it just mean that we have to provide a function for extension
authors to check and validate their upgrade chains.  We have to have a
way to check that without having to replay all the possible and
supported upgrade situations provided in the script, because it's a pain
to defend against cycles made up by the system that you didn't intend to
support.

Maybe something like:
=# SELECT * FROM pg_available_extension_upgrades('foo');
 installed | available |  chain   
---+---+--
 1.2   | 2.0   | 1.2 -> 1.1 -> 2.0
 1.2   | 1.9   | 1.2 -> 1.8 -> 1.9 -> 2.0
 1.2   | 1.8   | 1.2 -> 1.8
 1.2   | 1.1   | 1.2 -> 1.1
(4 rows)

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Dimitri Fontaine  writes:
> Tom Lane  writes:
>> I think it'd likely be sufficient to bump them only once per release
>> cycle, ie, there's no need to distinguish versions that never appeared
>> in the wild.  But if we forgot and created 1.1 early in the 9.2 release
>> cycle and 1.2 late in the cycle, there's no great harm done either.
>> What I don't want to be doing is creating artificial version bumps with
>> empty upgrade scripts in every release cycle --- that's make-work for
>> us, and make-work for our users too.

> I would favor different release cycles for extensions than for the core
> product.  It's a technical fact that a single extension source can and
> do support more than one major core version.  And as soon as the code is
> maintained, next extension release would happen at next minor upgrade
> release.

Anything that got kicked out to pgfoundry would presumably start acting
that way.  Anything that's part of core git is going to stay on the same
release cycle as the core, thank you very much.  Release engineering is
a big enough headache around here already.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Tom Lane  writes:
> I think it'd likely be sufficient to bump them only once per release
> cycle, ie, there's no need to distinguish versions that never appeared
> in the wild.  But if we forgot and created 1.1 early in the 9.2 release
> cycle and 1.2 late in the cycle, there's no great harm done either.
> What I don't want to be doing is creating artificial version bumps with
> empty upgrade scripts in every release cycle --- that's make-work for
> us, and make-work for our users too.

I would favor different release cycles for extensions than for the core
product.  It's a technical fact that a single extension source can and
do support more than one major core version.  And as soon as the code is
maintained, next extension release would happen at next minor upgrade
release.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Dimitri Fontaine  writes:
> Tom Lane  writes:
>> I don't see that this proposal changes anything about that.  It's still
>> the case that the underlying .so is tied to a major PG version.  What
>> you'll ship is a control file and assorted .sql files that represent the
>> user APIs you are interested in supporting on that major PG version.

> That's why I proposed that the require control field would contain the
> PostgreSQL release against which the extension is built.

>   require = 'postgresql-9.0'

I don't see what that does for you.  This is still all being examined by
a particular major release of PG, so what will it do with a require that
specifies some other major release?  Nothing useful.  And there's a very
significant downside, which is that this takes us right back to the
make-work of having to change all the contrib modules' control files in
every release cycle.

Once again, I see the version numbers as being specifiers for versions
of the install script files.  Not the Postgres version those files are
being run in.  Confusing the two is a bad idea.  Confusing the install
script version numbers with minor release numbers (bugfix level
identifiers) is even worse.  You *don't* want to change these numbers if
you're just fixing a bug at the C code level.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Tom Lane  writes:
> Dimitri Fontaine  writes:
>> Will we have to provide different upgrade scripts for different past
>> major versions of PostgreSQL?  If so, I would say "9.0" or "8.4" would
>> be better names.  hstore at least is an example that would need this
>> treatment I guess.
>
> I don't foresee us bothering with that.  We will only be trying to
> upgrade installations that got to 9.1 legitimately.

Shops that upgrade at each new releases are the exception, not the
rule.  Very few people will have the luxury of upgrading their
production from 9.0 to 9.1, most will jump right from 8.3 or 8.4
straight to 9.1.  Don't we want to support them, or I am not
understanding your words?

> I should also make clear that I intend to start out all the contrib
> modules at version 1.0.  *NOT* 9.1.  These things are going to get
> version number bumps only when the contents of their install scripts
> change, not whenever the surrounding database changes version.  If we
> number them at 9.1 to start with, it will just promote confusion.

Agreed.  But we don't have any sorting, so upgrading from 8.4 to 1.0 is
no problem for us.  Just apply the hstore:8.4:1.0.sql script.

I don't see wrapping back up to 8.4 happening soon enough for us to
regret it, we won't ship hstore with upgrade support from
8.4-pre-extensions to 8.4-wrapped, will we?

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Aidan Van Dyk  writes:
> So, I like that the attempt is to support multiple versions.  But
> unless you can manage the files (both shared libraries, and any
> scripts to create/update SQL objects) for different version
> independently, I can't see the "multiple versions at once" capabilites
> that are being discussed being actually being used by anything more
> than the most basic extensions...

No, you're missing the use case here I think.  It's all about releasing
minor upgrades (of extensions) and allowing users to jump through more
than one of them at a time.  Like upgrading from 1.1.0 to 1.3.5.

> Just like if I need a bugfix of PostgreSQL 8.4, I'm not forced to
> *install* 9.0, because PG has decide that the proper way to release
> ist o make a single release of all versions.

If you have extension which needs multiple major version releases, then
yes, as PostgreSQL packages, you need to put the extension major version
number into its name.  I don't see that as a problem of the mechanisms
proposed here.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Tom Lane  writes:
> I don't see that this proposal changes anything about that.  It's still
> the case that the underlying .so is tied to a major PG version.  What
> you'll ship is a control file and assorted .sql files that represent the
> user APIs you are interested in supporting on that major PG version.

That's why I proposed that the require control field would contain the
PostgreSQL release against which the extension is built.

  require = 'postgresql-9.0'

Then, we have to separate multi-major version support, that almost all
extensions have, with extension release schedule and extension new major
versions.

My proposal here was to distinguish between a "support" update and a
"stable" update, so that users are warned and helped somehow.

Other than that, I don't see any reason not to rename the extension in
such cases, like we have postgis-1.4 and postgis-1.5.  That's also
another good reason not to use dash as a version separator in upgrade
scripts, too.

Note that debian uses the semicolon to represent epoch, as a way to fix
upgrades that break their sorting rules.  But we don't have no sorting
rules.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Robert Haas  writes:
> On Fri, Feb 11, 2011 at 3:15 PM, Tom Lane  wrote:
>> I should also make clear that I intend to start out all the contrib
>> modules at version 1.0.

> What happens if their contents change several times during a major
> release cycle?

I think it'd likely be sufficient to bump them only once per release
cycle, ie, there's no need to distinguish versions that never appeared
in the wild.  But if we forgot and created 1.1 early in the 9.2 release
cycle and 1.2 late in the cycle, there's no great harm done either.
What I don't want to be doing is creating artificial version bumps with
empty upgrade scripts in every release cycle --- that's make-work for
us, and make-work for our users too.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

On Fri, Feb 11, 2011 at 3:15 PM, Tom Lane  wrote:
> Dimitri Fontaine  writes:
>> Tom Lane  writes:
>>> However, we're going to have to make a choice for the contrib modules,
>>> and I'll bet lunch that most people will follow whatever precedent we
>>> set with those.  I was thinking about using either "old" or "unpackaged".
>>> Thoughts?
>
>> Will we have to provide different upgrade scripts for different past
>> major versions of PostgreSQL?  If so, I would say "9.0" or "8.4" would
>> be better names.  hstore at least is an example that would need this
>> treatment I guess.
>
> I don't foresee us bothering with that.  We will only be trying to
> upgrade installations that got to 9.1 legitimately.
>
> I should also make clear that I intend to start out all the contrib
> modules at version 1.0.  *NOT* 9.1.  These things are going to get
> version number bumps only when the contents of their install scripts
> change, not whenever the surrounding database changes version.  If we
> number them at 9.1 to start with, it will just promote confusion.

What happens if their contents change several times during a major
release cycle?

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Tom Lane  writes:
> 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]

Agreed.

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

Check.

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

Well I don't think it's the same default, but I'm in the minority, so
you got your votes here already.  Just for the record and summary.

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

Chaining in all cases is better.  Less documentation, less code, less
burden on authors.  Better :)

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

That allows to get prepared for version aliases if we ever get there
too. Good.

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

Check.  We could use : as the version separator too.

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

I would think that if we have the extname-v.control facility, which I
think we must have, we should check for this file at each steps of the
chain, and override each time.  Encodings are not what I'm worried about
here, 'required' is.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Dimitri Fontaine  writes:
> Tom Lane  writes:
>> However, we're going to have to make a choice for the contrib modules,
>> and I'll bet lunch that most people will follow whatever precedent we
>> set with those.  I was thinking about using either "old" or "unpackaged".
>> Thoughts?

> Will we have to provide different upgrade scripts for different past
> major versions of PostgreSQL?  If so, I would say "9.0" or "8.4" would
> be better names.  hstore at least is an example that would need this
> treatment I guess.

I don't foresee us bothering with that.  We will only be trying to
upgrade installations that got to 9.1 legitimately.

I should also make clear that I intend to start out all the contrib
modules at version 1.0.  *NOT* 9.1.  These things are going to get
version number bumps only when the contents of their install scripts
change, not whenever the surrounding database changes version.  If we
number them at 9.1 to start with, it will just promote confusion.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

"David E. Wheeler"  writes:
> On Feb 11, 2011, at 11:50 AM, Dimitri Fontaine wrote:
>> It would be good to avoid regexp and globing pattern characters, I would
>> say.
>> 
>> There's the coma, as in "foo,1.0,1.1.sql", so ugly that it's unused :) I
>> wonder if : would be good? "foo:1.0:1.1.sql".  A very quick test seems
>> to show that macosx is ok with that scheme.

> I like comma and :. The latter is used on Mac OS classic, so I don't think 
> that's an issue. Does PostgreSQL run on VMS?

Uh ... colon is a special character in Windows filenames still, no?

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

2011-02-11 Thread Aidan Van Dyk

On Fri, Feb 11, 2011 at 7:49 PM, Tom Lane wrote:

> If you were expecting this proposal to make things easier as far as
> dealing with multiple major releases, sorry, our ambitions don't extend
> that far yet.

Sorry, I might have been confusing here... I'm not talking about *PG*
major releases.

I'm talking about "major release" of my extensions. So, assoming I
only care about PG 9.1, but I have afoo-1.x and afoo-2.x that I
develop and release (much like PostgreSQL has 8.4.x and 9.0.x it
releases), I want to be able to provide a bug-fix of my afoo-1.x
extension, and not require that for them to get that bug fix, they
also need to get the latest 2.x installed as well (which may or may
not be in use elsewhere in the cluster, or by a 2nd cluster on the
same machine).

Or, similarly, if I have a "master" type branch of an extension in use
in my qa DB, upgrading it requires forcing an upgrade of the old 8.4
branch extension in use in my prod database, simply because the
extension infrastructure has forced extension authors to only be able
to release a single "extension" that alwyas packages the lastest of
all back branches...

Of course, it won't, because just like the RPM/DPKG situation,
packagers are going to put the "major version" number into the
extension name to avoid that.

So, I like that the attempt is to support multiple versions. But
unless you can manage the files (both shared libraries, and any
scripts to create/update SQL objects) for different version
independently, I can't see the "multiple versions at once" capabilites
that are being discussed being actually being used by anything more
than the most basic extensions...

Just like if I need a bugfix of PostgreSQL 8.4, I'm not forced to
*install* 9.0, because PG has decide that the proper way to release
ist o make a single release of all versions.

--
Aidan Van Dyk Create like a god,
ai...@highrise.ca command like a king,
http://www.highrise.ca/ work like a slave.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

On Feb 11, 2011, at 11:50 AM, Dimitri Fontaine wrote:

> It would be good to avoid regexp and globing pattern characters, I would
> say.
> 
> There's the coma, as in "foo,1.0,1.1.sql", so ugly that it's unused :) I
> wonder if : would be good? "foo:1.0:1.1.sql".  A very quick test seems
> to show that macosx is ok with that scheme.

I like comma and :. The latter is used on Mac OS classic, so I don't think 
that's an issue. Does PostgreSQL run on VMS?

Best,

David


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Tom Lane  writes:
> In principle we are leaving it to the extension author to choose that.

Most extensions already have a version number.  ip4r is 1.05, prefix is
1.1.0, dbi-link is 2.0.0, temporal is 20091213, tablelog is 0.4.4, etc.
All those extensions will need a newer 'extension' release to ship with
the control file, at least.  So those are some of the old version
numbers that we will find.

> However, we're going to have to make a choice for the contrib modules,
> and I'll bet lunch that most people will follow whatever precedent we
> set with those.  I was thinking about using either "old" or "unpackaged".
> Thoughts?

Will we have to provide different upgrade scripts for different past
major versions of PostgreSQL?  If so, I would say "9.0" or "8.4" would
be better names.  hstore at least is an example that would need this
treatment I guess.

Now we could trick and prepend a "0." so that it's easy to break the old
version naming system and get to use a proper per-extension numbering
here.  That would mean that core provided extension could have a
different release cycle than the core product.  Do we want that?

Thinking about that, maybe what we want to do with contrib is separate
that in several directories, like e.g. "examples", "extensions", "tools"
and such.  Tools would fit contribs that do not ship with SQL level
support, like pg_archivecleanup or pg_standby and some others.  Each
time contrib quality is talked about we're explained that most of them
are examples only, not production ready quality code, hence my proposal.

> It can be specified by a "directory" parameter in the control file,
> and defaults to the same place the control file is.  Right now, that's
> $PREFIX/share/contrib/.  One other thing that ought to be discussed is
> whether to stick with that choice or change it.  Given that some people
> have great antipathy to the word "contrib", I suspect there will be
> argument to change it --- but to do so, I think we'd have to change the
> default MODULEDIR in PGXS, and I'm not sure that's a good idea.

I don't readily grasp the consequences of that.

>>> 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.
>
>> 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?
>
> I could go with + ... anyone know if that is problematic in filenames on
> Windows or elsewhere?

It would be good to avoid regexp and globing pattern characters, I would
say.

There's the coma, as in "foo,1.0,1.1.sql", so ugly that it's unused :) I
wonder if : would be good? "foo:1.0:1.1.sql".  A very quick test seems
to show that macosx is ok with that scheme.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Aidan Van Dyk  writes:
> On Fri, Feb 11, 2011 at 7:19 PM, Tom Lane  wrote:
>> No, you ship *one* package that supports both 1.1 and 2.0.

> Hm...  As an example of a project that generally has pretty good
> software release practices, I'm glat that the PostgreSQL project
> doesn't operate this way.

> Having to download/install/upgrade a package with all of pg
> 9.1.$lateset and 9.0.$latest just to get a fix for 8.4.$latest would
> be a bit of a bummer...

I don't see that this proposal changes anything about that.  It's still
the case that the underlying .so is tied to a major PG version.  What
you'll ship is a control file and assorted .sql files that represent the
user APIs you are interested in supporting on that major PG version.

For systems like Debian that support concurrent installation of multiple
major PG versions, you would be installing all these files into a
version-specific share/ directory.

If you don't feel like supporting multiple API versions on a given PG
major release, then nothing much changes from the way you packaged
stuff before.  The only real change is that you have a fairly clean way
to package scripts that fix bugs in the extension's catalog entries,
which was something that could only be done in a very ad-hoc way before.

If you were expecting this proposal to make things easier as far as
dealing with multiple major releases, sorry, our ambitions don't extend
that far yet.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

2011-02-11 Thread Aidan Van Dyk

On Fri, Feb 11, 2011 at 7:19 PM, Tom Lane  wrote:

>> This gives my first problem.  I can't package afoo-2.x seperately from
>> afoo-1.x, because they both want to write the afoo control file.
>
> No, you ship *one* package that supports both 1.1 and 2.0.

Hm...  As an example of a project that generally has pretty good
software release practices, I'm glat that the PostgreSQL project
doesn't operate this way.

Having to download/install/upgrade a package with all of pg
9.1.$lateset and 9.0.$latest just to get a fix for 8.4.$latest would
be a bit of a bummer...

And a hopefull extension author/packages/user, I *want* to be able to
release/distribute different versions seperately, just like PostgreSQL
does.  And I'll do that by packaging my extension with a "major"
version in the name, much like the packages for PostgreSQL does.  But
once I've done that, I don't need the multiple extension versions, all
I need is the ability to run $something when I upgrade an extension,
once the files under it have been upgraded.

;-)

>> But now, let's make it harder.  I've found a grave bug in 1.1, which
>> causes the PG backend to segfault.  Easy fix, good thing, so now I
>> release 1.2:
>
> Unless the bug is such that you have to change the installation script
> file, there is no reason to bump the version number at all.  These
> version numbers apply to the install SQL script, not the underlying .so.

Right.  If everything is exactly binary compatible and it's just a .so
fix, I don't need to.  But let's assume something like slonly (or
bucardo or longdiste, or PyQ, or PostGIS) start's trying to make use
of extensions.  I can very much see a "bug fix" minor version upgrade
changing things that might need trigers/etc to be altered to take
advantage of the fixed way of doing things.  Or a SQL view/function
had a bug with an  null handling joins that needs fixing, etc.  Lots
of reasons for an "upgrade" to need to change an SQL object.

And of course, if I have slony 1.2.$x replicating one of my databases,
I'ld love to be able to try slony 2 and have it packaged on my system
too to test somethign else.   And not have to upgrade my slony 2
instance just to get the critical bugfix for my production slony
1.2$x+1.

a.

-- 
Aidan Van Dyk                                             Create like a god,
ai...@highrise.ca                                       command like a king,
http://www.highrise.ca/                                   work like a slave.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

2011-02-11 Thread Aidan Van Dyk

On Fri, Feb 11, 2011 at 6:30 PM, Tom Lane wrote:

> No --- in the current vision, a control file may describe a whole
> collection of versions of the same extension, and the parameter in
> question is selecting the default or preferred version to install.
> I'm not wedded to "default_version", but I think just plain "version"
> is a misnomer.

As someone who wants to use extensions and packages (rpm/dpkg)
together to distribute PG database pieces, I think this multi-version
approach is going to be problematic.

Here's why.

I release exetension "afoo", initial as version 1.0. From my
understanding, it's going to contain:
afoo control file, named something particular)
- default_version = 1.0
- encoding utf8
foo-1.0.sql installstion script
and any requried shared libraries

And I now release and updated version 1.1 which fixes a problem. No problem:
afoo control file:
- default_version = 1.1
- encoding utf8
afoo-1.1.sql installation
afoo-upgrade-1.0-1.1.sql upgrade script
any required shared libraries for afoo-1.

Now, I decide to add some major new changes to my afoo for version 2.
I'ld like to package it up:
afoo control file
- default_version = 2.0
- encoding utf8
afoo-2.0.sql installation
afoo-upgrade-1.1-2.0-sql upgrade script
Any ne shared libreries for afoo-2.

This gives my first problem. I can't package afoo-2.x seperately from
afoo-1.x, because they both want to write the afoo control file.
RPM/DPKG will cause me grief here.

But now, let's make it harder. I've found a grave bug in 1.1, which
causes the PG backend to segfault. Easy fix, good thing, so now I
release 1.2:
afoo control file
- default_version = 1.2
- encoding utf8
afoo-1.2.sql installation
afoo-upgrade-1.0-1.1.sql upgrade
afoo-upgrade-1.1-1.2.sql upgrade
any shared libraries for afoo-1

So, this is not a problem for upgrading 1.0/1.1 -> 1.2. But if I have
1.1 on my system, and let's say I forced a 2.0 into the system
(telling dpkg/rpm to overwrite the common file), I'm going to do that
again here now with 1.2, and my afoo control file will have
default_version = 1.2 instead of the 2.0

So, I'm not even working about the in-database side of the
multi-versions (alhthough I definately want the ability to have
multiple versions in the same database), but we're not even going to
be able to get the files onto the system to support multiple versions
nicely.

So this is going to drive me the same direction the same problem drove
packages for rpm/dpkg. I'm going to have to name my extension
"afoo-1" and "afoo-2" to be able to have them both co-exist on the
filesystem independantly, and at that point, *I* don't need multiple
versions of it anymore. I'm going to keep the same extension
objects/libraries backwards compatible, and I just need a way to tell
PG to run something after I've replaced the shared libraries to
perform any upgrade tweeks.

--
Aidan Van Dyk Create like a god,
ai...@highrise.ca command like a king,
http://www.highrise.ca/ work like a slave.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Aidan Van Dyk  writes:
> And I now release and updated version 1.1 which fixes a problem.  No problem:
>afoo control file:
>  - default_version = 1.1
>  - encoding utf8
>afoo-1.1.sql installation
>afoo-upgrade-1.0-1.1.sql upgrade script
>any required shared libraries for afoo-1.

> Now, I decide to add some major new changes to my afoo for version 2.
> I'ld like to package it up:
>afoo control file
> - default_version = 2.0
> - encoding utf8
>afoo-2.0.sql installation
>afoo-upgrade-1.1-2.0-sql upgrade script
>Any ne shared libreries for afoo-2.

> This gives my first problem.  I can't package afoo-2.x seperately from
> afoo-1.x, because they both want to write the afoo control file.

No, you ship *one* package that supports both 1.1 and 2.0.

> But now, let's make it harder.  I've found a grave bug in 1.1, which
> causes the PG backend to segfault.  Easy fix, good thing, so now I
> release 1.2:

Unless the bug is such that you have to change the installation script
file, there is no reason to bump the version number at all.  These
version numbers apply to the install SQL script, not the underlying .so.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

On Feb 11, 2011, at 10:58 AM, Aidan Van Dyk wrote:

> I release exetension "afoo", initial as version 1.0.  From my
> understanding, it's going to contain:
>afoo control file, named something particular)
>  - default_version = 1.0
>  - encoding utf8
>foo-1.0.sql installstion script
>and any requried shared libraries
> 
> And I now release and updated version 1.1 which fixes a problem.  No problem:
>   afoo control file:
> - default_version = 1.1
> - encoding utf8
>   afoo-1.1.sql installation
>   afoo-upgrade-1.0-1.1.sql upgrade script
>   any required shared libraries for afoo-1.

Oh. Would be nice if default_version assumed that an unversioned file was the 
default, actually. That way I don't have to rename the file in my repository 
every time I want to make a release. That will mess with my Git version history.

> Now, I decide to add some major new changes to my afoo for version 2.
> I'ld like to package it up:
>   afoo control file
>- default_version = 2.0
>- encoding utf8
>   afoo-2.0.sql installation
>   afoo-upgrade-1.1-2.0-sql upgrade script
>   Any ne shared libreries for afoo-2.
> 
> This gives my first problem.  I can't package afoo-2.x seperately from
> afoo-1.x, because they both want to write the afoo control file.
> RPM/DPKG will cause me grief here.

1.x would have its own control file. 1 control file per version (at most).

> But now, let's make it harder.  I've found a grave bug in 1.1, which
> causes the PG backend to segfault.  Easy fix, good thing, so now I
> release 1.2:
>  afoo control file
>- default_version = 1.2
>- encoding utf8
>  afoo-1.2.sql installation
>  afoo-upgrade-1.0-1.1.sql upgrade
>  afoo-upgrade-1.1-1.2.sql upgrade
>  any shared libraries for afoo-1
> 
> So, this is not a problem for upgrading 1.0/1.1 -> 1.2.  But if I have
> 1.1 on my system, and let's say I forced a 2.0 into the system
> (telling dpkg/rpm to overwrite the common file), I'm going to do that
> again here now with 1.2, and my afoo control file will have
> default_version = 1.2 instead of the 2.0

Why wouldn't it have 2.1? You'd have added afoo-upgrade-1.1-1.2.sql and 
afoo-upgrade-2.0-2.2.sql.

> So, I'm not even working about the in-database side of the
> multi-versions (alhthough I definately want the ability to have
> multiple versions in the same database), but we're not even going to
> be able to get the files onto the system to support multiple versions
> nicely.

I'm not following why not.

> So this is going to drive me the same direction the same problem drove
> packages for rpm/dpkg.  I'm going to have to name my extension
> "afoo-1" and "afoo-2" to be able to have them both co-exist on the
> filesystem independantly, and at that point, *I* don't need multiple
> versions of it anymore.  I'm going to keep the same extension
> objects/libraries backwards compatible, and I just need a way to tell
> PG to run something after I've replaced the shared libraries to
> perform any  upgrade tweeks.

Oh, I think I see. You want to distribute 1.2 and 2.1 as separate downloads. I 
think the idea here is that you'd still have only one distribution download, 
but it would contain both 1.2 and 2.1. Then you have no conflicts.

Best,

David



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Robert Haas  writes:
> On Fri, Feb 11, 2011 at 1:06 PM, Tom Lane  wrote:
>> 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.

Ok, I'm in the minority here.  That happened before :)

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

=?iso-8859-1?Q?K=E4=E4ri=E4inen_Anssi?=  writes:
> This has the side effect that you can also have downgrade scripts. I
> don't know if this is designed or just coincidental, so thought it
> would be worth mentioning.

Yeah, that's intentional and IMO worth supporting.

We do have to be sure that the chain-finding algorithm doesn't choke on
loops in the graph, but AFAICS Dijkstra's algorithm doesn't have a
problem with that.  As long as we consider that each step has positive
cost, it won't execute a loop.

> The worst case is that if you are upgrading from 1.2 to 2.0 the path
> is 1.2 -> 1.1 -> 2.0, even if there exists a path 1.2 -> 1.8 -> 1.9 ->
> 2.0. This could potentially result in data loss, if the downgrade
> drops some columns or something like that.

Hmm.  That seems like it would require a rather pathological collection
of upgrade scripts.  In particular why would you have a one-step upgrade
from 1.1 to 2.0 but no short path from 1.2?

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

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

> No --- in the current vision, a control file may describe a whole
> collection of versions of the same extension, and the parameter in
> question is selecting the default or preferred version to install.
> I'm not wedded to "default_version", but I think just plain "version"
> is a misnomer.

current_version, then.

>> Oh, so what should oldv be to indicate creating from a legacy extension?
> 
> In principle we are leaving it to the extension author to choose that.
> However, we're going to have to make a choice for the contrib modules,
> and I'll bet lunch that most people will follow whatever precedent we
> set with those.  I was thinking about using either "old" or "unpackaged".
> Thoughts?

unpackaged++

> It can be specified by a "directory" parameter in the control file,
> and defaults to the same place the control file is.  Right now, that's
> $PREFIX/share/contrib/.

Frankly, given the likely proliferation of upgrade scripts, I think it ought to 
be $PREFIX/share/contrib/$extension/

>  One other thing that ought to be discussed is
> whether to stick with that choice or change it.  Given that some people
> have great antipathy to the word "contrib", I suspect there will be
> argument to change it --- but to do so, I think we'd have to change the
> default MODULEDIR in PGXS, and I'm not sure that's a good idea.

Add EXTENSIONDIR and make it "extensions".

Best,

David


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

2011-02-11 Thread Kääriäinen Anssi

From: pgsql-hackers-ow...@postgresql.org [pgsql-hackers-ow...@postgresql.org] 
On Behalf Of Tom Lane [t...@sss.pgh.pa.us]
Sent: Friday, February 11, 2011 7:35 PM
To: Dimitri Fontaine
Cc: David E. Wheeler; Robert Haas; Josh Berkus; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

"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 strings will have no hard-wired semantics except equality; we
don't need a sorting rule."

This has the side effect that you can also have downgrade scripts. I don't know 
if this is designed or just coincidental, so thought it would be worth 
mentioning. It can have some impact on how to find the update chain to the 
desired version (loops in the graph), although standard graph traversal 
algorithms should handle this just fine. The worst case is that if you are 
upgrading from 1.2 to 2.0 the path is 1.2 -> 1.1 -> 2.0, even if there exists a 
path 1.2 -> 1.8 -> 1.9 -> 2.0. This could potentially result in data loss, if 
the downgrade drops some columns or something like that.

All this can of course be avoided by documenting that even if it is possible to 
define downgrade script, don't do it...

 - Anssi
 PS. I hope this mail comes out somewhat sanely formatted, using our lovely 
OWA-webmail here...

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

On Fri, Feb 11, 2011 at 1:30 PM, Tom Lane  wrote:
> In principle we are leaving it to the extension author to choose that.
> However, we're going to have to make a choice for the contrib modules,
> and I'll bet lunch that most people will follow whatever precedent we
> set with those.  I was thinking about using either "old" or "unpackaged".
> Thoughts?

I like unpackaged.

>>> 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.
>
>> 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?
>
> I could go with + ... anyone know if that is problematic in filenames on
> Windows or elsewhere?

I'd rather stick with -.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

"David E. Wheeler"  writes:
> 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.

No --- in the current vision, a control file may describe a whole
collection of versions of the same extension, and the parameter in
question is selecting the default or preferred version to install.
I'm not wedded to "default_version", but I think just plain "version"
is a misnomer.

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

In principle we are leaving it to the extension author to choose that.
However, we're going to have to make a choice for the contrib modules,
and I'll bet lunch that most people will follow whatever precedent we
set with those.  I was thinking about using either "old" or "unpackaged".
Thoughts?

> How do you determine the "script directory"?

It can be specified by a "directory" parameter in the control file,
and defaults to the same place the control file is.  Right now, that's
$PREFIX/share/contrib/.  One other thing that ought to be discussed is
whether to stick with that choice or change it.  Given that some people
have great antipathy to the word "contrib", I suspect there will be
argument to change it --- but to do so, I think we'd have to change the
default MODULEDIR in PGXS, and I'm not sure that's a good idea.

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

Fair enough.

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

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

I could go with + ... anyone know if that is problematic in filenames on
Windows or elsewhere?

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Robert Haas  writes:
> On Fri, Feb 11, 2011 at 12:35 PM, 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]

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

Hmm.  You have a point, and there's some precedent for this in our other
non-standard CREATE commands such as CREATE OPERATOR and CREATE
AGGREGATE.  On the other hand, we have no precedent for handling ALTER
syntaxes that way.  Also, I think most people feel that the CREATE
OPERATOR and CREATE AGGREGATE syntaxes are ugly, not-very-SQL-ish beasts
carried over from PostQUEL days.

On the whole I have a weak preference for leaving it as above, but would
readily yield to a consensus to do the other.

One minor point is that I was planning to drop the opt_equals from the
syntax --- it doesn't fit at all with the FROM case.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] 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



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

2011-02-11 Thread Josh Berkus


> CREATE EXTENSION extname [ ( option [ , ... ] ) ]
> 
> where option can be one of:
> 
> SCHEMA blah
> VERSION blah
> FROM blah

+1

This also means that users don't have to remember the specific ordering
of the syntax, which is a big plus.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

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

> Dimitri Fontaine  writes:
>> Tom Lane  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 think they should be the same. Anything else seems confusing and weird.

Best,

David


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

On Fri, Feb 11, 2011 at 1:06 PM, Tom Lane  wrote:
> Dimitri Fontaine  writes:
>> Tom Lane  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

On Fri, Feb 11, 2011 at 12:35 PM, 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]

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Dimitri Fontaine  writes:
> Tom Lane  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Tom Lane  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Dimitri Fontaine  writes:
> Tom Lane  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Tom Lane  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Tom Lane  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Dimitri Fontaine  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Dimitri Fontaine  writes:
> Tom Lane  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3


Tom Lane  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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

=?ISO-8859-1?Q?Anssi_K=E4=E4ri=E4inen?=  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Robert Haas  writes:
> On Fri, Feb 11, 2011 at 9:00 AM, Dimitri Fontaine
>  wrote:
>> Robert Haas  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

On Fri, Feb 11, 2011 at 9:00 AM, Dimitri Fontaine
 wrote:
> Robert Haas  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Robert Haas  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

On Fri, Feb 11, 2011 at 12:15 AM, Tom Lane  wrote:
> "David E. Wheeler"  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

2011-02-11 Thread Anssi Kääriäinen


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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Tom Lane  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Tom Lane  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Tom Lane  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

"David E. Wheeler"  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

"David E. Wheeler"  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

On Thu, Feb 10, 2011 at 6:50 PM, Tom Lane  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

"David E. Wheeler"  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

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



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Dimitri Fontaine  writes:
> Tom Lane  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

2011-02-10 Thread David Christensen

>> 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
da...@endpoint.com





-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

David Christensen  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Dimitri Fontaine  writes:
> "David E. Wheeler"  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

2011-02-10 Thread Aidan Van Dyk

On Thu, Feb 10, 2011 at 9:38 PM, Tom Lane  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,
ai...@highrise.ca                                       command like a king,
http://www.highrise.ca/                                   work like a slave.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Robert Haas  writes:
> On Thu, Feb 10, 2011 at 3:52 PM, Tom Lane  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

"David E. Wheeler"  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Robert Haas  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

On Thu, Feb 10, 2011 at 3:52 PM, Tom Lane  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Robert Haas  writes:
> On Thu, Feb 10, 2011 at 3:58 PM, Tom Lane  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

On Thu, Feb 10, 2011 at 4:14 PM, Dimitri Fontaine
 wrote:
> Robert Haas  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Tom Lane  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

On Thu, Feb 10, 2011 at 3:58 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Thu, Feb 10, 2011 at 3:02 PM, Tom Lane  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Robert Haas  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Hi,

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

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Robert Haas  writes:
> On Thu, Feb 10, 2011 at 3:02 PM, Tom Lane  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

On Thu, Feb 10, 2011 at 3:46 PM, David E. Wheeler  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Robert Haas  writes:
> On Thu, Feb 10, 2011 at 2:42 PM, Tom Lane  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

On Thu, Feb 10, 2011 at 3:33 PM, Dimitri Fontaine
 wrote:
> Robert Haas  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Robert Haas  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

On Thu, Feb 10, 2011 at 3:02 PM, Tom Lane  wrote:
> "David E. Wheeler"  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

On Thu, Feb 10, 2011 at 2:42 PM, Tom Lane  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

"David E. Wheeler"  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Robert Haas  writes:
> On Thu, Feb 10, 2011 at 1:26 PM, Tom Lane  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

"David E. Wheeler"  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

On Thu, Feb 10, 2011 at 1:26 PM, Tom Lane  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] 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 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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3