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

Tom Lane t...@sss.pgh.pa.us 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

Tom Lane t...@sss.pgh.pa.us 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 t...@sss.pgh.pa.us 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

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

On Fri, Feb 11, 2011 at 12:15 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 David E. Wheeler da...@kineticode.com 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

Robert Haas robertmh...@gmail.com 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 9:00 AM, Dimitri Fontaine
dimi...@2ndquadrant.fr wrote:
 Robert Haas robertmh...@gmail.com 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 robertmh...@gmail.com writes:
 On Fri, Feb 11, 2011 at 9:00 AM, Dimitri Fontaine
 dimi...@2ndquadrant.fr wrote:
 Robert Haas robertmh...@gmail.com 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

=?ISO-8859-1?Q?Anssi_K=E4=E4ri=E4inen?= anssi.kaariai...@thl.fi 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


Tom Lane t...@sss.pgh.pa.us 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

Dimitri Fontaine dimi...@2ndquadrant.fr writes:
 Tom Lane t...@sss.pgh.pa.us 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

Dimitri Fontaine dimi...@2ndquadrant.fr 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

Tom Lane t...@sss.pgh.pa.us 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

Tom Lane t...@sss.pgh.pa.us 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

Dimitri Fontaine dimi...@2ndquadrant.fr writes:
 Tom Lane t...@sss.pgh.pa.us 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 t...@sss.pgh.pa.us 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

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

Dimitri Fontaine dimi...@2ndquadrant.fr writes:
 Tom Lane t...@sss.pgh.pa.us 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

On Fri, Feb 11, 2011 at 12:35 PM, Tom Lane t...@sss.pgh.pa.us 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

On Fri, Feb 11, 2011 at 1:06 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Dimitri Fontaine dimi...@2ndquadrant.fr writes:
 Tom Lane t...@sss.pgh.pa.us 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 Feb 11, 2011, at 10:06 AM, Tom Lane wrote:

 Dimitri Fontaine dimi...@2ndquadrant.fr writes:
 Tom Lane t...@sss.pgh.pa.us 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

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

Robert Haas robertmh...@gmail.com writes:
 On Fri, Feb 11, 2011 at 12:35 PM, Tom Lane t...@sss.pgh.pa.us 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

David E. Wheeler da...@kineticode.com 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

On Fri, Feb 11, 2011 at 1:30 PM, Tom Lane t...@sss.pgh.pa.us 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

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

=?iso-8859-1?Q?K=E4=E4ri=E4inen_Anssi?= anssi.kaariai...@thl.fi 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

Robert Haas robertmh...@gmail.com writes:
 On Fri, Feb 11, 2011 at 1:06 PM, Tom Lane t...@sss.pgh.pa.us 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

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

Aidan Van Dyk ai...@highrise.ca 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

2011-02-11 Thread Aidan Van Dyk

On Fri, Feb 11, 2011 at 6:30 PM, Tom Lane t...@sss.pgh.pa.us 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

2011-02-11 Thread Aidan Van Dyk

On Fri, Feb 11, 2011 at 7:19 PM, Tom Lane t...@sss.pgh.pa.us 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

Aidan Van Dyk ai...@highrise.ca writes:
 On Fri, Feb 11, 2011 at 7:19 PM, Tom Lane t...@sss.pgh.pa.us 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

Tom Lane t...@sss.pgh.pa.us 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

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

2011-02-11 Thread Aidan Van Dyk

On Fri, Feb 11, 2011 at 7:49 PM, Tom Lane t...@sss.pgh.pa.us 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

David E. Wheeler da...@kineticode.com 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

Dimitri Fontaine dimi...@2ndquadrant.fr writes:
 Tom Lane t...@sss.pgh.pa.us 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

Tom Lane t...@sss.pgh.pa.us 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

On Fri, Feb 11, 2011 at 3:15 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Dimitri Fontaine dimi...@2ndquadrant.fr writes:
 Tom Lane t...@sss.pgh.pa.us 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

Robert Haas robertmh...@gmail.com writes:
 On Fri, Feb 11, 2011 at 3:15 PM, Tom Lane t...@sss.pgh.pa.us 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

Tom Lane t...@sss.pgh.pa.us 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

Aidan Van Dyk ai...@highrise.ca 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 t...@sss.pgh.pa.us writes:
 Dimitri Fontaine dimi...@2ndquadrant.fr 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

Dimitri Fontaine dimi...@2ndquadrant.fr writes:
 Tom Lane t...@sss.pgh.pa.us 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 t...@sss.pgh.pa.us 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 dimi...@2ndquadrant.fr writes:
 Tom Lane t...@sss.pgh.pa.us 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 t...@sss.pgh.pa.us 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

Tom Lane t...@sss.pgh.pa.us 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 t...@sss.pgh.pa.us 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

David E. Wheeler da...@kineticode.com 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

2011-02-11 Thread marcin mank

On Fri, Feb 11, 2011 at 8:15 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 =?iso-8859-1?Q?K=E4=E4ri=E4inen_Anssi?= anssi.kaariai...@thl.fi 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

marcin mank marcin.m...@gmail.com writes:
 On Fri, Feb 11, 2011 at 8:15 PM, Tom Lane t...@sss.pgh.pa.us 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

I spent some time reviewing this thread.  I think the major point that's
not received adequate discussion is this: the design assumes that there's
just one current version of any extension, and that's not good enough.
David Fetter was trying to make that point upthread but didn't seem to
convince people.  I'm convinced though.  I think that one of the major
selling points of extensions could be having a controlled way of exposing
different versions of an API and letting users select which one is in use
in each database.  Look at how much effort we spend maintaining back
branches of the core code for people who don't want to, eg, update their
apps to avoid pre-8.3-style implicit casting.  (Yeah, I know that on-disk
compatibility is another major reason for staying on a back branch, but
API changes are definitely part of it.)

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.
Over time we might get to a point where somebody could be running the
latest version of the core database (with all the bug fixes and other
goodness of that) but his application compatibility problems are solved
by running back-rev versions of certain extensions.

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

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.

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.

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

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.)  Also, the
version parameter should be renamed to something like current_version
or default_version.  We also have to be wary of whether any other
control-file parameters specify something that might be version-specific.
Looking at the current list:

comment: probably OK to consider this as a default for all versions.
We already have the

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

On Thu, Feb 10, 2011 at 1:26 PM, Tom Lane t...@sss.pgh.pa.us 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

David E. Wheeler da...@kineticode.com 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 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

Robert Haas robertmh...@gmail.com writes:
 On Thu, Feb 10, 2011 at 1:26 PM, Tom Lane t...@sss.pgh.pa.us 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

David E. Wheeler da...@kineticode.com 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

On Thu, Feb 10, 2011 at 2:42 PM, Tom Lane t...@sss.pgh.pa.us 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

On Thu, Feb 10, 2011 at 3:02 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 David E. Wheeler da...@kineticode.com 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

Robert Haas robertmh...@gmail.com 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:33 PM, Dimitri Fontaine
dimi...@2ndquadrant.fr wrote:
 Robert Haas robertmh...@gmail.com 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

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

Robert Haas robertmh...@gmail.com writes:
 On Thu, Feb 10, 2011 at 2:42 PM, Tom Lane t...@sss.pgh.pa.us 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 Thu, Feb 10, 2011 at 3:46 PM, David E. Wheeler da...@kineticode.com 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 robertmh...@gmail.com writes:
 On Thu, Feb 10, 2011 at 3:02 PM, Tom Lane t...@sss.pgh.pa.us 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

Hi,

Tom Lane t...@sss.pgh.pa.us 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.

 that it is necessary to

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

Robert Haas robertmh...@gmail.com 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

On Thu, Feb 10, 2011 at 3:58 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@gmail.com writes:
 On Thu, Feb 10, 2011 at 3:02 PM, Tom Lane t...@sss.pgh.pa.us 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

Tom Lane t...@sss.pgh.pa.us 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 4:14 PM, Dimitri Fontaine
dimi...@2ndquadrant.fr wrote:
 Robert Haas robertmh...@gmail.com 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

Robert Haas robertmh...@gmail.com writes:
 On Thu, Feb 10, 2011 at 3:58 PM, Tom Lane t...@sss.pgh.pa.us 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 3:52 PM, Tom Lane t...@sss.pgh.pa.us 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

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

Robert Haas robertmh...@gmail.com 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

David E. Wheeler da...@kineticode.com 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 robertmh...@gmail.com writes:
 On Thu, Feb 10, 2011 at 3:52 PM, Tom Lane t...@sss.pgh.pa.us 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

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

2011-02-10 Thread Aidan Van Dyk

On Thu, Feb 10, 2011 at 9:38 PM, Tom Lane t...@sss.pgh.pa.us 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

Dimitri Fontaine dimi...@2ndquadrant.fr writes:
 David E. Wheeler da...@kineticode.com 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

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

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

Dimitri Fontaine dimi...@2ndquadrant.fr writes:
 Tom Lane t...@sss.pgh.pa.us 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

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

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

David E. Wheeler da...@kineticode.com 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 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

On Thu, Feb 10, 2011 at 6:50 PM, Tom Lane t...@sss.pgh.pa.us 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

David E. Wheeler da...@kineticode.com 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 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 da...@kineticode.com 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