Hi,

PFA version 3 of the ALTER EXTENSION PATCH, cleaned and merged against
recent HEAD and extension's branch from which I just produced the v30
patch.

It includes a way to upgrade "from null", that is from pre-9.1, and the
specific upgrade files to achieve that for all contribs. That goes like
this:

dim=# \i ~/pgsql/exts/share/contrib/lo.sql
CREATE DOMAIN
CREATE FUNCTION
CREATE FUNCTION
dim=# create wrapper extension lo;
CREATE EXTENSION
dim=# alter extension lo upgrade;
ALTER EXTENSION
dim=# alter extension lo set schema utils;
ALTER EXTENSION
dim=# \dx lo
Objects in extension "lo"
Object Description
---------------------------------
function utils.lo_manage()
function utils.lo_oid(utils.lo)
type utils.lo
(3 rows)

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

Search Discussions

  • Itagaki Takahiro at Feb 2, 2011 at 10:35 am

    On Wed, Feb 2, 2011 at 03:21, Dimitri Fontaine wrote:
    PFA version 3 of the ALTER EXTENSION PATCH, cleaned and merged against
    recent HEAD and extension's branch from which I just produced the v30
    patch.
    Excuse me for asking, but could you explain what is the purpose?
    Which is true, "upgrade to 9.1 from past versions" or "upgrade
    from 9.1 to future versions"? Also, how much advantage will we
    have compared with uninstall_MODULE.sql + CREATE EXTENSION?

    In my understanding, the patch does two things:
    1. Add ALTER object SET EXTENSION
    2. Add MODULE.upgrade.sql script for each contrib module

    #1 seems reasonable as a supplement for CREATE EXTENSION patch,
    but we might need not only "attach" but also "detach".

    I guess #2 is much more difficult than expected because we might
    upgrade databases from older versions. Will we need upgrade script
    for each supported versions? -- if so, it would be nightmare...

    --
    Itagaki Takahiro
  • Dimitri Fontaine at Feb 2, 2011 at 11:29 am

    Itagaki Takahiro writes:
    Excuse me for asking, but could you explain what is the purpose?
    Which is true, "upgrade to 9.1 from past versions" or "upgrade
    from 9.1 to future versions"? Also, how much advantage will we
    have compared with uninstall_MODULE.sql + CREATE EXTENSION?
    Both are "true" use cases and supported in the code.

    The goal is to be able to manage extensions upgrading. This is done by
    running a script the author provides. To know which script to run, you
    need to know the currently installed extension version, the available
    version, and determine from that the script filename. That's what the
    new control file options are about.

    Now that you can upgrade extensions to their next versions, what about
    migrating from an existing set of objects towards having an extension?
    This use case happens either when upgrading from pre-9.1 or when you're
    working on an in-house extension. At first it's not an extension, you
    just CREATE FUNCTION and CREATE VIEW. The day you decide to properly
    package it, you want to be able to do that without the hassle of
    DROP'ing all those objects that your production is depending on.
    In my understanding, the patch does two things:
    1. Add ALTER object SET EXTENSION
    2. Add MODULE.upgrade.sql script for each contrib module
    The patch also add new options in the control file so that it's possible
    to do ALTER EXTENSION foo UPGRADE;. That's the main goal.
    #1 seems reasonable as a supplement for CREATE EXTENSION patch,
    but we might need not only "attach" but also "detach".
    I didn't think about "detach", I'm not sure I see the use case…
    I guess #2 is much more difficult than expected because we might
    upgrade databases from older versions. Will we need upgrade script
    for each supported versions? -- if so, it would be nightmare...
    It's not about upgrading major versions, it's about upgrading
    extensions. The only time you will need to run the scripts in the patch
    is e.g. when upgrading the extension hstore from NULL to 1.0. Once
    done, hstore is registered as an extension, you're done. No need to
    redo that or maintain the upgrade script for 9.1 to 9.2.

    We will have to provide some other scripts when upgrade hstore from 1.0
    to 1.1, whenever that happens (minor upgrades, major upgrades, etc).

    I hope to make the case clear enough…

    Regards,
    --
    Dimitri Fontaine
    http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
  • Itagaki Takahiro at Feb 2, 2011 at 1:12 pm

    On Wed, Feb 2, 2011 at 20:29, Dimitri Fontaine wrote:
    I didn't think about "detach", I'm not sure I see the use case…
    The latest extension might drop some functions.
    It's not about upgrading major versions, it's about upgrading
    extensions.  The only time you will need to run the scripts in the patch
    is e.g. when upgrading the extension hstore from NULL to 1.0.  Once
    done, hstore is registered as an extension, you're done.  No need to
    redo that or maintain the upgrade script for 9.1 to 9.2.
    I'm still not clear what "upgrade" means. if module authors wrote
    functions with C, they can just replace .so to upgrade. If with
    SQL or PL/pgSQL, they should execute CREATE OR REPLACE FUNCTION.

    The patch seems useful to upgrade from NULL to 1.0, but I cannot
    imagine how it work for cases from 1.0 to higher versions.
    For example, if we have 3 versions of a module below:
    NULL unmanaged functions only
    v1 EXTENSION support with an additional function
    v2 EXTENSION support with another function.
    How do we write upgrading scripts for NULL=>v1, NULL=>v2, and v1=>v2 ?

    --
    Itagaki Takahiro
  • Dimitri Fontaine at Feb 2, 2011 at 2:45 pm

    Itagaki Takahiro writes:
    The latest extension might drop some functions.
    Then the upgrade script contains the DROP commands.
    I'm still not clear what "upgrade" means. if module authors wrote
    functions with C, they can just replace .so to upgrade. If with
    SQL or PL/pgSQL, they should execute CREATE OR REPLACE FUNCTION.
    When do you execute those statements? Certainly, you want the user to
    issue ALTER EXTENSION foo UPGRADE and be done with it.
    The patch seems useful to upgrade from NULL to 1.0, but I cannot
    imagine how it work for cases from 1.0 to higher versions.
    For example, if we have 3 versions of a module below:
    NULL unmanaged functions only
    v1 EXTENSION support with an additional function
    v2 EXTENSION support with another function.
    How do we write upgrading scripts for NULL=>v1, NULL=>v2, and v1=>v2 ?
    Well, you write 3 scripts.

    Let's consider an example, the lo contrib, with its 3 objects:

    CREATE DOMAIN lo AS pg_catalog.oid;
    CREATE OR REPLACE FUNCTION lo_oid(lo) …
    CREATE OR REPLACE FUNCTION lo_manage() …

    Now, the upgrade script from version NULL to 1.0 is

    alter domain @extschema@.lo set extension lo;
    alter function @extschema@.lo_oid(lo) set extension lo;
    alter function @extschema@.lo_manage() set extension lo;

    The upgrade script from version 1.0 to 2.0 is, let's say:

    CREATE OR REPLACE FUNCTION @extschema@.lo_newfunc() …

    So the upgrade script from version NULL to 2.0 is:

    alter domain @extschema@.lo set extension lo;
    alter function @extschema@.lo_oid(lo) set extension lo;
    alter function @extschema@.lo_manage() set extension lo;
    CREATE OR REPLACE FUNCTION @extschema@.lo_newfunc() …

    If as an extension author you're kind enough to provide all those 3
    scripts and the upgrade setup in the control file, then the user can
    issue ALTER EXTENSION lo UPGRADE; and have all your cases covered
    automatically.

    Regards,
    --
    Dimitri Fontaine
    http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
  • David E. Wheeler at Feb 2, 2011 at 4:51 pm

    On Feb 2, 2011, at 6:45 AM, Dimitri Fontaine wrote:

    Well, you write 3 scripts.

    Let's consider an example, the lo contrib, with its 3 objects:

    CREATE DOMAIN lo AS pg_catalog.oid;
    CREATE OR REPLACE FUNCTION lo_oid(lo) …
    CREATE OR REPLACE FUNCTION lo_manage() …

    Now, the upgrade script from version NULL to 1.0 is

    alter domain @extschema@.lo set extension lo;
    alter function @extschema@.lo_oid(lo) set extension lo;
    alter function @extschema@.lo_manage() set extension lo;

    The upgrade script from version 1.0 to 2.0 is, let's say:

    CREATE OR REPLACE FUNCTION @extschema@.lo_newfunc() …

    So the upgrade script from version NULL to 2.0 is:

    alter domain @extschema@.lo set extension lo;
    alter function @extschema@.lo_oid(lo) set extension lo;
    alter function @extschema@.lo_manage() set extension lo;
    CREATE OR REPLACE FUNCTION @extschema@.lo_newfunc() …

    If as an extension author you're kind enough to provide all those 3
    scripts and the upgrade setup in the control file, then the user can
    issue ALTER EXTENSION lo UPGRADE; and have all your cases covered
    automatically.
    As an extension author, I can't emphasize enough how much I hate the redundancy of this approach.

    Best,

    David
  • Dimitri Fontaine at Feb 2, 2011 at 5:03 pm

    "David E. Wheeler" <david@kineticode.com> writes:
    On Feb 2, 2011, at 6:45 AM, Dimitri Fontaine wrote:

    Well, you write 3 scripts.

    Let's consider an example, the lo contrib, with its 3 objects:

    CREATE DOMAIN lo AS pg_catalog.oid;
    CREATE OR REPLACE FUNCTION lo_oid(lo) …
    CREATE OR REPLACE FUNCTION lo_manage() …

    Now, the upgrade script from version NULL to 1.0 is

    alter domain @extschema@.lo set extension lo;
    alter function @extschema@.lo_oid(lo) set extension lo;
    alter function @extschema@.lo_manage() set extension lo;

    The upgrade script from version 1.0 to 2.0 is, let's say:

    CREATE OR REPLACE FUNCTION @extschema@.lo_newfunc() …

    So the upgrade script from version NULL to 2.0 is:

    alter domain @extschema@.lo set extension lo;
    alter function @extschema@.lo_oid(lo) set extension lo;
    alter function @extschema@.lo_manage() set extension lo;
    CREATE OR REPLACE FUNCTION @extschema@.lo_newfunc() …

    If as an extension author you're kind enough to provide all those 3
    scripts and the upgrade setup in the control file, then the user can
    issue ALTER EXTENSION lo UPGRADE; and have all your cases covered
    automatically.
    As an extension author, I can't emphasize enough how much I hate the redundancy of this approach.
    Well, fair enough I suppose. Or it would be if you gave me an
    alternative that provides a simpler way to support those 3 upgrades.

    Of course if that's too much for you, you can also choose to only
    support upgrades one versions at a time and provide only two scripts.
    Note also that I don't recall of any proposal on the table that would
    help with that situation, so I'm all ears.

    Regards.
    --
    Dimitri Fontaine
    http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
  • David E. Wheeler at Feb 2, 2011 at 5:10 pm

    On Feb 2, 2011, at 9:03 AM, Dimitri Fontaine wrote:

    Well, fair enough I suppose. Or it would be if you gave me an
    alternative that provides a simpler way to support those 3 upgrades.
    I did: a naming convention with upgrade scripts that have the version number in them. You rejected it.
    Of course if that's too much for you, you can also choose to only
    support upgrades one versions at a time and provide only two scripts.
    Note also that I don't recall of any proposal on the table that would
    help with that situation, so I'm all ears.
    http://archives.postgresql.org/pgsql-hackers/2011-01/msg00296.php

    Best,

    David
  • Dimitri Fontaine at Feb 2, 2011 at 5:19 pm

    "David E. Wheeler" <david@kineticode.com> writes:
    On Feb 2, 2011, at 9:03 AM, Dimitri Fontaine wrote:

    Well, fair enough I suppose. Or it would be if you gave me an
    alternative that provides a simpler way to support those 3 upgrades.
    I did: a naming convention with upgrade scripts that have the version
    number in them. You rejected it.
    I'm sorry, I'm not following. You're proposing to pick one file or
    another depending on its name. You're not proposing to have less than
    three files to handle three upgrade setups. You still have to produce
    the exact same file set.

    The only difference is that the core code, in your proposal, has to know
    what is a version number and where to find it in the file names, whereas
    in mine the core code does not have to assume anything at all about what
    version numbers look like. Nor to know how do they compare.

    Oh, and in my current proposal and code, the author can reuse the same
    file more than once for some upgrade setups, too.
    Of course if that's too much for you, you can also choose to only
    support upgrades one versions at a time and provide only two scripts.
    Note also that I don't recall of any proposal on the table that would
    help with that situation, so I'm all ears.
    http://archives.postgresql.org/pgsql-hackers/2011-01/msg00296.php
    I see there no solution to your reaction here. Please take the time to
    tell us more about what exactly it is that you hated, and how to make it
    lovely. We won't make any progress with your current commenting style.

    Regards,
    --
    Dimitri Fontaine
    http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
  • David E. Wheeler at Feb 2, 2011 at 5:32 pm

    On Feb 2, 2011, at 9:19 AM, Dimitri Fontaine wrote:

    I see there no solution to your reaction here. Please take the time to
    tell us more about what exactly it is that you hated, and how to make it
    lovely. We won't make any progress with your current commenting style.
    Here is your example of the two upgrade scripts:
    Now, the upgrade script from version NULL to 1.0 is

    alter domain @extschema@.lo set extension lo;
    alter function @extschema@.lo_oid(lo) set extension lo;
    alter function @extschema@.lo_manage() set extension lo;

    The upgrade script from version 1.0 to 2.0 is, let's say:

    CREATE OR REPLACE FUNCTION @extschema@.lo_newfunc() …

    So the upgrade script from version NULL to 2.0 is:

    alter domain @extschema@.lo set extension lo;
    alter function @extschema@.lo_oid(lo) set extension lo;
    alter function @extschema@.lo_manage() set extension lo;
    CREATE OR REPLACE FUNCTION @extschema@.lo_newfunc() …
    They are identical except for the extra line in the second one. If I had, say 15 different versions of an extension, then I'd have 15 upgrade scripts. That's fine. But in your plan, the script to upgrade from version 1 to version 15 would have all the same code as the v14 script, plus any additional. The v14 script would have everything in v13. v13 would have everything in v12. With no support for the equivalent of psql's \i, that's extremely redundant and a huge PITA to maintain. Hence my hate.

    My proposal would also have 15 upgrade scripts, but each one would only upgrade from the previous one. So to upgrade from v1 to v15, UPGRADE EXTENSION would run all of them. So v15 would only need to have deltas from v14. V14 would need only deltas from v13. Etc.

    The number of upgrade script files is not the problem I have. It's the redundant content of those files that raises my ire.

    If there was some way to get something like \i in your approach, that would satisfy me, as it would be only an extra line (at most) in each script. And then, as you note, the core wouldn't need the complexity of understanding version numbers, which I agree would be beneficial.

    Best,

    David
  • Dimitri Fontaine at Feb 2, 2011 at 6:04 pm

    "David E. Wheeler" <david@kineticode.com> writes:
    They are identical except for the extra line in the second one. If I
    had, say 15 different versions of an extension, then I'd have 15
    upgrade scripts. That's fine. But in your plan, the script to upgrade
    from version 1 to version 15 would have all the same code as the v14
    script, plus any additional. The v14 script would have everything in
    v13. v13 would have everything in v12. With no support for the
    equivalent of psql's \i, that's extremely redundant and a huge PITA to
    maintain. Hence my hate.
    That's easy enough to manage in your Makefile, really:

    upgrade.null-v15.sql: upgrade.v14.sql upgrade.v15.sql
    cat upgrade.v14.sql upgrade.v15.sql > $@


    There's a difference between what you maintain and what you ship.
    My proposal would also have 15 upgrade scripts, but each one would
    only upgrade from the previous one. So to upgrade from v1 to v15,
    UPGRADE EXTENSION would run all of them. So v15 would only need to
    have deltas from v14. V14 would need only deltas from v13. Etc.
    What if you can reuse the later script for upgrading from any previous
    version, like when the extension only contains CREATE OR REPLACE
    statements (functions only extension, like adminpack).

    I don't see benefits in your proposal.
    The number of upgrade script files is not the problem I have. It's the redundant content of those files that raises my ire.

    If there was some way to get something like \i in your approach, that would satisfy me, as it would be only an extra line (at most) in each script. And then, as you note, the core wouldn't need the complexity of understanding version numbers, which I agree would be beneficial.
    It all comes down to the benefits. I don't see any in your proposal.
    That might be just me though.

    --
    Dimitri Fontaine
    http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
  • David E. Wheeler at Feb 2, 2011 at 6:08 pm

    On Feb 2, 2011, at 10:04 AM, Dimitri Fontaine wrote:

    "David E. Wheeler" <david@kineticode.com> writes:
    They are identical except for the extra line in the second one. If I
    had, say 15 different versions of an extension, then I'd have 15
    upgrade scripts. That's fine. But in your plan, the script to upgrade
    from version 1 to version 15 would have all the same code as the v14
    script, plus any additional. The v14 script would have everything in
    v13. v13 would have everything in v12. With no support for the
    equivalent of psql's \i, that's extremely redundant and a huge PITA to
    maintain. Hence my hate.
    That's easy enough to manage in your Makefile, really:

    upgrade.null-v15.sql: upgrade.v14.sql upgrade.v15.sql
    cat upgrade.v14.sql upgrade.v15.sql > $@
    Sure, if you know Make really well. But then I need to add a line to the Makefile for every bloody upgrade script. Gross.
    There's a difference between what you maintain and what you ship.
    Yes.
    My proposal would also have 15 upgrade scripts, but each one would
    only upgrade from the previous one. So to upgrade from v1 to v15,
    UPGRADE EXTENSION would run all of them. So v15 would only need to
    have deltas from v14. V14 would need only deltas from v13. Etc.
    What if you can reuse the later script for upgrading from any previous
    version, like when the extension only contains CREATE OR REPLACE
    statements (functions only extension, like adminpack).
    I don't understand the question.
    I don't see benefits in your proposal.
    The benefit is reduced redundancy.
    It all comes down to the benefits. I don't see any in your proposal.
    That might be just me though.
    Could be.

    Best,

    David
  • Dimitri Fontaine at Feb 2, 2011 at 6:22 pm

    "David E. Wheeler" <david@kineticode.com> writes:
    upgrade.null-v15.sql: upgrade.v14.sql upgrade.v15.sql
    cat upgrade.v14.sql upgrade.v15.sql > $@
    Sure, if you know Make really well. But then I need to add a line to
    the Makefile for every bloody upgrade script. Gross.
    Either one line in the Makefile or a new file with the \i equivalent
    lines, that would maybe look like:

    SELECT pg_execute_sql_file('upgrade.v14.sql');
    SELECT pg_execute_sql_file('upgrade.v15.sql');

    So well… I don't see how you've made it less gross here.
    --
    Dimitri Fontaine
    http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
  • David E. Wheeler at Feb 2, 2011 at 6:29 pm

    On Feb 2, 2011, at 10:22 AM, Dimitri Fontaine wrote:

    Either one line in the Makefile or a new file with the \i equivalent
    lines, that would maybe look like:

    SELECT pg_execute_sql_file('upgrade.v14.sql');
    SELECT pg_execute_sql_file('upgrade.v15.sql');

    So well… I don't see how you've made it less gross here.
    I suppose it depends on whether or not you prefer SQL to make. I know where my preferences are.

    Best,

    David
  • Anssi Kääriäinen at Feb 3, 2011 at 8:06 am

    On 02/02/2011 08:22 PM, Dimitri Fontaine wrote:
    Either one line in the Makefile or a new file with the \i equivalent
    lines, that would maybe look like:

    SELECT pg_execute_sql_file('upgrade.v14.sql');
    SELECT pg_execute_sql_file('upgrade.v15.sql');

    So well… I don't see how you've made it less gross here.
    Chaining the upgrade files should be relatively easy, if something like
    pg_execute_sql_file would be available (actually it would need to be
    pg_execute_extension_file so that @extschema@ would be substituted
    correctly).

    Example:

    upgrade_from_1_0 = '1.0 => upgrade_from_1.0.sql'
    upgrade_from_2_0 = '2.0 => upgrade_from_2.0.sql'
    upgrade_from_3_0 = '3.0 => upgrade_from_3.0.sql'

    upgrade_from_1.0.sql contents:
    alter table foobar add column id2 integer;
    pg_execute_extension_file('upgrade_from_2.0.sql');

    upgrade_from_2.0.sql contents:
    alter table foobar add column id3 integer;
    pg_execute_extension_file('upgrade_from_3.0.sql');

    ...

    So, when creating a new version you would need to update the main .sql
    file, create a new upgrade file, and alter the
    upgrade_from_previous_version.sql to include the new upgrade file. This
    should be relatively easy to maintain. Also, this would give you the
    freedom to not chain the files when that is not appropriate.

    By the way, I saw that the character '.' is not allowed in the xxx part
    of upgrade_from_xxx and this is not documented in the patch. What can be
    in the xxx part, and is this documented somewhere else?

    - Anssi
  • Dimitri Fontaine at Feb 3, 2011 at 8:57 am

    Anssi Kääriäinen writes:
    upgrade_from_2.0.sql contents:
    alter table foobar add column id3 integer;
    pg_execute_extension_file('upgrade_from_3.0.sql');

    ...

    So, when creating a new version you would need to update the main .sql file,
    create a new upgrade file, and alter the upgrade_from_previous_version.sql
    to include the new upgrade file. This should be relatively easy to
    maintain. Also, this would give you the freedom to not chain the files when
    that is not appropriate.
    Again, why not, I think I can see how this would work out. What I don't
    see is what is the gain compared to preparing the right files at make
    time and only shipping "autonomous" files. I very much prefer to handle
    a set of source SQL files and some cat a b c > ship.sql rules rather
    than ship loads of files that all depends on each other.
    By the way, I saw that the character '.' is not allowed in the xxx part of
    upgrade_from_xxx and this is not documented in the patch. What can be in the
    xxx part, and is this documented somewhere else?
    It has to be a valid configuration variable name, as any other GUC, and
    it's not a placeholder (only those can contain dots). We're using the
    same parser as for postgresql.conf and recovery.conf here. Not sure
    where that is documented, though.

    Regards,
    --
    Dimitri Fontaine
    http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
  • Ross J. Reedstrom at Feb 3, 2011 at 2:53 pm

    On Thu, Feb 03, 2011 at 10:21:28AM +0200, Anssi Kääriäinen wrote:
    On 02/02/2011 08:22 PM, Dimitri Fontaine wrote:
    Either one line in the Makefile or a new file with the \i equivalent
    lines, that would maybe look like:

    SELECT pg_execute_sql_file('upgrade.v14.sql');
    SELECT pg_execute_sql_file('upgrade.v15.sql');

    So well… I don't see how you've made it less gross here.
    Chaining the upgrade files should be relatively easy, if something like
    pg_execute_sql_file would be available (actually it would need to be
    pg_execute_extension_file so that @extschema@ would be substituted
    correctly).

    Example:

    upgrade_from_1_0 = '1.0 => upgrade_from_1.0.sql'
    upgrade_from_2_0 = '2.0 => upgrade_from_2.0.sql'
    upgrade_from_3_0 = '3.0 => upgrade_from_3.0.sql'
    Hmm, how about allowing a list of files to execute? That allows the
    developer to create modularized sql, and composite it in the config:

    for a mythical version 4.0:

    1.0 => add_foobar_table.sql new_method_baz.sql
    2.0 => drop_method_baz.sql add_quuz_table.sql
    # oops, we still needed this
    3.0 => new_method_baz.sql

    I know when I'm developing such upgrades, the code looks like that,
    until I need to shoehorn them into the upgrade systems idea of version
    numbers matching names to find scripts to run.

    The advantage of this is that it keeps the logic for mapping version
    to upgrades in the config: the upgrade scripts mearly handle the actual
    SQL for doing a specific task, not a collection of tasks only related by
    virtue of being released at the same time.

    Ross
    --
    Ross Reedstrom, Ph.D. reedstrm@rice.edu
    Systems Engineer & Admin, Research Scientist phone: 713-348-6166
    Connexions http://cnx.org fax: 713-348-3665
    Rice University MS-375, Houston, TX 77005
    GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
  • Robert Haas at Feb 3, 2011 at 3:30 pm

    On Thu, Feb 3, 2011 at 9:53 AM, Ross J. Reedstrom wrote:
    Example:

    upgrade_from_1_0 = '1.0 => upgrade_from_1.0.sql'
    upgrade_from_2_0 = '2.0 => upgrade_from_2.0.sql'
    upgrade_from_3_0 = '3.0 => upgrade_from_3.0.sql'
    Hmm, how about allowing a list of files to execute? That allows the
    developer to create modularized sql, and composite it in the config:

    for a mythical version 4.0:

    1.0 => add_foobar_table.sql new_method_baz.sql
    2.0 => drop_method_baz.sql add_quuz_table.sql
    # oops, we still needed this
    3.0 => new_method_baz.sql

    I know when I'm developing such upgrades, the code looks like that,
    until I need to shoehorn them into the upgrade systems idea of version
    numbers matching names to find scripts to run.
    Good idea. My code looks that way too.

    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise PostgreSQL Company
  • Dimitri Fontaine at Feb 3, 2011 at 3:31 pm

    "Ross J. Reedstrom" <reedstrm@rice.edu> writes:
    Hmm, how about allowing a list of files to execute? That allows the
    Sure. I still don't see why doing it in the control file is better than
    in the Makefile, even if it's already better than in the SQL script, at
    least in terms of code to write to support the idea.

    Speaking about which, using Make rules to prepare your upgrade files
    from other pieces means no development at all on the backend side. You
    can hardly beat that.

    Regards,
    --
    Dimitri Fontaine
    http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
  • Ross J. Reedstrom at Feb 3, 2011 at 4:26 pm

    On Thu, Feb 03, 2011 at 04:31:08PM +0100, Dimitri Fontaine wrote:
    "Ross J. Reedstrom" <reedstrm@rice.edu> writes:
    Hmm, how about allowing a list of files to execute? That allows the
    Sure. I still don't see why doing it in the control file is better than
    in the Makefile, even if it's already better than in the SQL script, at
    least in terms of code to write to support the idea.
    Because that's two places to touch that have to worry about mapping
    versions to actions. Inside the config file I'm already going to have to
    do that, and in mostly a trivial one-to-one mapping. The proposed
    make rules are an example of the kind of 'make my code match what the
    system wants' that I complained of.
    Speaking about which, using Make rules to prepare your upgrade files
    from other pieces means no development at all on the backend side. You
    can hardly beat that.
    Yes, from the backend-developer's perspective. But not from the
    extension-developer's perspective :-) And seriously, make is one of
    those things that is supremely capable of doing lots of stuff, but is so
    difficult to use correctly that everyone keeps reinventing newer wheels.
    Seems this one isn't round enough.

    In fact, doing it via make rules would still be available, if that's
    what floats the particular developer's boat. more choices is good.

    Ross
    --
    Ross Reedstrom, Ph.D. reedstrm@rice.edu
    Systems Engineer & Admin, Research Scientist phone: 713-348-6166
    Connexions http://cnx.org fax: 713-348-3665
    Rice University MS-375, Houston, TX 77005
    GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
  • Robert Haas at Feb 3, 2011 at 4:29 pm

    On Thu, Feb 3, 2011 at 11:25 AM, Ross J. Reedstrom wrote:
    Speaking about which, using Make rules to prepare your upgrade files
    from other pieces means no development at all on the backend side.  You
    can hardly beat that.
    Yes, from the backend-developer's perspective. But not from the
    extension-developer's perspective :-) And seriously, make is one of
    those things that is supremely capable of doing lots of stuff, but is so
    difficult to use correctly that everyone keeps reinventing newer wheels.
    Seems this one isn't round enough.
    Not to mention the fact that make doesn't work on Windows, so any
    extensions that rely on this will need hacks in the MSVC build system.

    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise PostgreSQL Company
  • Dimitri Fontaine at Feb 3, 2011 at 4:54 pm

    Robert Haas writes:
    Yes, from the backend-developer's perspective. But not from the
    extension-developer's perspective :-) And seriously, make is one of
    those things that is supremely capable of doing lots of stuff, but is so
    difficult to use correctly that everyone keeps reinventing newer wheels.
    Seems this one isn't round enough.
    Not to mention the fact that make doesn't work on Windows, so any
    extensions that rely on this will need hacks in the MSVC build system.
    Fair enough, so that's just me not seeing it. Now I agree that having
    the right hand side of the format I proposed be an ordered list of files
    rather than a single file is simple enough and comes with benefits.

    The examples are using spaces as the separator, how friendly is that to
    our windows users? Maybe using coma instead would be better?

    Regards,
    --
    Dimitri Fontaine
    http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
  • Robert Haas at Feb 3, 2011 at 5:15 pm

    On Thu, Feb 3, 2011 at 11:53 AM, Dimitri Fontaine wrote:
    Robert Haas <robertmhaas@gmail.com> writes:
    Yes, from the backend-developer's perspective. But not from the
    extension-developer's perspective :-) And seriously, make is one of
    those things that is supremely capable of doing lots of stuff, but is so
    difficult to use correctly that everyone keeps reinventing newer wheels.
    Seems this one isn't round enough.
    Not to mention the fact that make doesn't work on Windows, so any
    extensions that rely on this will need hacks in the MSVC build system.
    Fair enough, so that's just me not seeing it.  Now I agree that having
    the right hand side of the format I proposed be an ordered list of files
    rather than a single file is simple enough and comes with benefits.

    The examples are using spaces as the separator, how friendly is that to
    our windows users?  Maybe using coma instead would be better?
    Comma would be better. There is even some backend code that will
    tokenize on it, I think.

    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise PostgreSQL Company
  • Florian Pflug at Feb 3, 2011 at 5:02 pm

    On Feb3, 2011, at 16:31 , Dimitri Fontaine wrote:
    "Ross J. Reedstrom" <reedstrm@rice.edu> writes:
    Hmm, how about allowing a list of files to execute? That allows the
    Sure. I still don't see why doing it in the control file is better than
    in the Makefile, even if it's already better than in the SQL script, at
    least in terms of code to write to support the idea.

    Speaking about which, using Make rules to prepare your upgrade files
    from other pieces means no development at all on the backend side. You
    can hardly beat that.
    I fully agree. The extension infrastructure should provide basic support
    for upgrades, not every kind of bell and whistle one could possible think of.

    The bells and whistles can then be provided by the system used to *build* the
    extension. Not only does this keep the core infrastructure manageable, it also
    allows different tools to generate the update scripts to exist, each catering
    to the needs of different kinds of extensions.

    best regards,
    Florian Pflug
  • Tom Lane at Feb 3, 2011 at 5:38 pm

    Florian Pflug writes:
    I fully agree. The extension infrastructure should provide basic support
    for upgrades, not every kind of bell and whistle one could possible think of.
    Maybe somewhere around here we should stop and ask why we are bothering
    with any of this. The original idea for an extension concept was that
    (1) some collection of objects could be designated as a module
    (2) pg_dump would be taught to dump "LOAD MODULE foo" instead of the
    individual objects
    (3) the way you'd do an upgrade is to dump and reload into a database
    that has available a newer definition of the module's content.

    Given that pg_upgrade is now considered a supported piece of the system,
    ISTM that most real-world upgrade scenarios will be accomplished with
    pg_upgrade relying on provision (3). It looks to me like we're talking
    about adding a large amount of complication --- both for the core
    database and for module authors --- in order to provide a duplicate
    solution for that. Why should we bother? Especially, why should we
    bother in version 1 of the feature? This could all be added later if
    we determine there's really sufficient demand, but right now we have
    no experience to show whether there is demand or not.

    regards, tom lane
  • David E. Wheeler at Feb 3, 2011 at 5:50 pm

    On Feb 3, 2011, at 9:38 AM, Tom Lane wrote:

    Given that pg_upgrade is now considered a supported piece of the system,
    ISTM that most real-world upgrade scenarios will be accomplished with
    pg_upgrade relying on provision (3). It looks to me like we're talking
    about adding a large amount of complication --- both for the core
    database and for module authors --- in order to provide a duplicate
    solution for that. Why should we bother? Especially, why should we
    bother in version 1 of the feature? This could all be added later if
    we determine there's really sufficient demand, but right now we have
    no experience to show whether there is demand or not.
    Given the level of disagreement, I think that leaving upgrades aside for now may be prudent, especially since there are other ways to do it (none very convenient, but no worse than what we have right now, and in the case of pg_dump, better).

    I think we will need to come back to it before, long, however, because many extensions are released far more often than major versions of PostgreSQL. So while one might run pg_upgrade, at most, about once every 12-18 months, they will often want to take advantage of the features of extensions on a much more ambitious release schedule.

    Extension upgrades need to be done eventually to make it easier to manage extension release schedules independent of PostgreSQL core upgrades. Otherwise, you're just going to get more patches for contrib.

    Best,

    David
  • Tom Lane at Feb 3, 2011 at 6:07 pm

    "David E. Wheeler" <david@kineticode.com> writes:
    I think we will need to come back to it before, long, however, because many extensions are released far more often than major versions of PostgreSQL. So while one might run pg_upgrade, at most, about once every 12-18 months, they will often want to take advantage of the features of extensions on a much more ambitious release schedule.
    Well, pg_upgrade is designed to work within a major-version series, eg
    you could do a 9.1-to-9.1 upgrade if you needed to install a newer
    version of an extension. Admittedly, this is swinging a rather larger
    hammer than "apply an upgrade script" would entail. But I'm still not
    convinced that we need to expend a great deal of work on making that
    process a tad more efficient.

    Now having said that, it does occur to me that there is an upgrade-ish
    scenario that every user is going to hit immediately, which is how to
    get from an existing installation with a pile of "loose" objects created
    by one or more contrib modules to a state where those objects are
    understood to be parts of modules. But that is a special case that
    perhaps deserves a special-case solution, rather than inventing a very
    large wheel.

    regards, tom lane
  • David E. Wheeler at Feb 3, 2011 at 6:10 pm

    On Feb 3, 2011, at 10:07 AM, Tom Lane wrote:

    Well, pg_upgrade is designed to work within a major-version series, eg
    you could do a 9.1-to-9.1 upgrade if you needed to install a newer
    version of an extension. Admittedly, this is swinging a rather larger
    hammer than "apply an upgrade script" would entail.
    Dude. That's a frigging piledriver!
    But I'm still not
    convinced that we need to expend a great deal of work on making that
    process a tad more efficient.
    Agreed. I would handle simple extension upgrades not with pg_upgrade, but the same way I do now. Think about how one currently jumps from PostGIS 1.4 to 1.5.

    Best,

    David
  • Robert Haas at Feb 3, 2011 at 6:47 pm

    On Thu, Feb 3, 2011 at 1:10 PM, David E. Wheeler wrote:
    On Feb 3, 2011, at 10:07 AM, Tom Lane wrote:

    Well, pg_upgrade is designed to work within a major-version series, eg
    you could do a 9.1-to-9.1 upgrade if you needed to install a newer
    version of an extension.  Admittedly, this is swinging a rather larger
    hammer than "apply an upgrade script" would entail.
    Dude. That's a frigging piledriver!
    That's putting it mildly. It's more like sending a rocket into outer
    space to tweak the orbit of a comet so that it crashes into your
    barbecue grill to light a fire so you can roast marshmallows.

    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise PostgreSQL Company
  • Tom Lane at Feb 3, 2011 at 7:02 pm

    Robert Haas writes:
    On Thu, Feb 3, 2011 at 1:10 PM, David E. Wheeler wrote:
    On Feb 3, 2011, at 10:07 AM, Tom Lane wrote:
    Well, pg_upgrade is designed to work within a major-version series, eg
    you could do a 9.1-to-9.1 upgrade if you needed to install a newer
    version of an extension.  Admittedly, this is swinging a rather larger
    hammer than "apply an upgrade script" would entail.
    Dude. That's a frigging piledriver!
    That's putting it mildly. It's more like sending a rocket into outer
    space to tweak the orbit of a comet so that it crashes into your
    barbecue grill to light a fire so you can roast marshmallows.
    No, it's more like using a sledgehammer to swat a fly because you don't
    have a flyswatter and aren't inclined to drive ten miles to buy one.
    It'll get the job done, and the added cost of building/maintaining a
    more finely calibrated tool may well outweigh the value.

    regards, tom lane
  • David E. Wheeler at Feb 3, 2011 at 7:03 pm

    On Feb 3, 2011, at 11:02 AM, Tom Lane wrote:

    That's putting it mildly. It's more like sending a rocket into outer
    space to tweak the orbit of a comet so that it crashes into your
    barbecue grill to light a fire so you can roast marshmallows.
    LOL.
    No, it's more like using a sledgehammer to swat a fly because you don't
    have a flyswatter and aren't inclined to drive ten miles to buy one.
    It'll get the job done, and the added cost of building/maintaining a
    more finely calibrated tool may well outweigh the value.
    I'd rather put down the sledgehammer and pick up a leaf or a paper bag or something.

    Best,

    David
  • Robert Haas at Feb 3, 2011 at 7:16 pm

    On Thu, Feb 3, 2011 at 2:02 PM, Tom Lane wrote:
    Robert Haas <robertmhaas@gmail.com> writes:
    On Thu, Feb 3, 2011 at 1:10 PM, David E. Wheeler wrote:
    On Feb 3, 2011, at 10:07 AM, Tom Lane wrote:
    Well, pg_upgrade is designed to work within a major-version series, eg
    you could do a 9.1-to-9.1 upgrade if you needed to install a newer
    version of an extension.  Admittedly, this is swinging a rather larger
    hammer than "apply an upgrade script" would entail.
    Dude. That's a frigging piledriver!
    That's putting it mildly.  It's more like sending a rocket into outer
    space to tweak the orbit of a comet so that it crashes into your
    barbecue grill to light a fire so you can roast marshmallows.
    No, it's more like using a sledgehammer to swat a fly because you don't
    have a flyswatter and aren't inclined to drive ten miles to buy one
    In other words, it's something no sensible person actually does?
    It'll get the job done, and the added cost of building/maintaining a
    more finely calibrated tool may well outweigh the value.
    It'll get the job done for very small values of getting the job done.
    Let's suppose that version 2 of hstore comes out, improving on version
    1 of hstore by adding one new useful function. Your proposed solution
    is that this person should initdb a new cluster, shut down their
    database, pg_upgrade over to the new cluster, and start it back up
    again to get that function definition. What's actually going to
    happen in 99.44% of cases is that the person is going to say "this
    extension mechanism sucks" and create the function definition by hand,
    because even if their database is unimportant enough that they don't
    mind the downtime, that's a ridiculous amount of hassle for what ought
    to be a straightforward operation. The reason for possibly postponing
    this to 9.2 is not that it isn't necessary but that we might not yet
    be sure what the best way to do it is.

    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise PostgreSQL Company
  • Dimitri Fontaine at Feb 3, 2011 at 6:27 pm

    Tom Lane writes:
    Now having said that, it does occur to me that there is an upgrade-ish
    scenario that every user is going to hit immediately, which is how to
    get from an existing installation with a pile of "loose" objects created
    by one or more contrib modules to a state where those objects are
    understood to be parts of modules. But that is a special case that
    perhaps deserves a special-case solution, rather than inventing a very
    large wheel.
    Well a good deal of the code I've written in the UGPRADE patch is there
    for this special case, that's ALTER OBJECT ... SET EXTENSION ...;

    This allows to "attach" any existing object to a given existing
    extension. Now what you need is a way to create an empty extension so
    that you can attach objects to it. That's in the patch in the form of
    the new command CREATE WRAPPER EXTENSION ...;

    WRAPPER was the most convenient keyword we already have I found.

    Then, there's only 2 things left in the patch. The contrib scripts that
    make that happen, and the control file support so that the command ALTER
    EXTENSION $contrib UPGRADE will run the upgrade script.

    This mechanism has been made in a way that allows it to cover running
    other scripts for other kind of upgrades. That's about it.

    Regards,
    --
    Dimitri Fontaine
    http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
  • Robert Haas at Feb 3, 2011 at 5:55 pm

    On Thu, Feb 3, 2011 at 12:38 PM, Tom Lane wrote:
    Florian Pflug <fgp@phlo.org> writes:
    I fully agree. The extension infrastructure should provide basic support
    for upgrades, not every kind of bell and whistle one could possible think of.
    Maybe somewhere around here we should stop and ask why we are bothering
    with any of this.  The original idea for an extension concept was that
    (1) some collection of objects could be designated as a module
    (2) pg_dump would be taught to dump "LOAD MODULE foo" instead of the
    individual objects
    (3) the way you'd do an upgrade is to dump and reload into a database
    that has available a newer definition of the module's content.

    Given that pg_upgrade is now considered a supported piece of the system,
    ISTM that most real-world upgrade scenarios will be accomplished with
    pg_upgrade relying on provision (3).  It looks to me like we're talking
    about adding a large amount of complication --- both for the core
    database and for module authors --- in order to provide a duplicate
    solution for that.  Why should we bother?  Especially, why should we
    bother in version 1 of the feature?  This could all be added later if
    we determine there's really sufficient demand, but right now we have
    no experience to show whether there is demand or not.
    I think you can pretty much take it to the bank that there will be
    demand. This is an important, real-world problem.

    That having been said, I'm not 100% convinced that the main extensions
    patch is ready for prime-time, and I'm even less convinced that the
    upgrade patch is anywhere the point where we want to commit to it
    long-term. So I would have no qualms about punting it out to 9.2.

    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise PostgreSQL Company
  • David E. Wheeler at Feb 3, 2011 at 6:06 pm

    On Feb 3, 2011, at 9:54 AM, Robert Haas wrote:

    I think you can pretty much take it to the bank that there will be
    demand. This is an important, real-world problem.

    That having been said, I'm not 100% convinced that the main extensions
    patch is ready for prime-time, and I'm even less convinced that the
    upgrade patch is anywhere the point where we want to commit to it
    long-term. So I would have no qualms about punting it out to 9.2.
    I think that the core extension stuff and pg_dump stuff is pretty close. It's the upgrade stuff that's thorny. And really, this is a pretty well-known problem. Surely someone has done some research to solve it?

    Anyway, IMHO, try to get CREATE EXTENSION into 9.1, perhaps put off ALTER EXTENSION UPGRADE to 9.2.

    Best,

    David
  • Josh Berkus at Feb 3, 2011 at 7:35 pm
    All,

    Let me summarize the prospective solutions based on some chatting with
    some potential extension authors (that is, folks who maintain in-house
    stuff they're thinking of offering as extensions). Especially since I
    think at this point the majority of -hackers has lost track of the argument:

    (A) Writing a separate upgrade script between each two versions
    This is completely unrealistic. No module maintainer will ever do this.
    Imagine an extension which had 20 releases over 4 years; that would be
    *190* upgrade scripts. Any solution we come up with cannot require
    module maintainers to write more than one upgrade script per release, or
    we will have no module maintainers.

    (B) As (A), but with in-script Includes so that eariler versions of
    scripts could be re-used for later version upgrades or strung together.
    This is somewhat more realistic, given that it could be done
    automatically and then tweaked. Especially if we supply a script to
    generate version upgrade scripts.

    (C) as (A), but through concatinating scripts for upgrade using Make
    files. This seems like the worst of all possible solutions. First, it
    prevents us from ever having a binary module release network, and
    permanently requires anyone using extensions to have GNU make present
    and accessible on their system, thus pretty much leaving out the Windows
    users forever. Second, it's a lot harder for module maintainers to
    tweak than includes would be, especially for SQL-only modules. Third,
    it requires Make to check what version you currently have installed in
    order to build the module, something which is unlikely to be reliable.

    (D) Requires a series of ordered upgrade scripts in sortable version
    numbering, each of which gets applied in order between the two versions.
    This initially seems like the most attractive option -- and is the one
    used by dozens of popular open source web applications -- but has some
    major roadblocks for us. First, it requires module authors to subscribe
    to a uniform sortable versions scheme (which isn't a bad thing, the
    users would certainly appreciate it, and PGXN is liable to enforce this
    anyway). Second and much more substantially, .so's installed for later
    versions might be incompatible with intermediate upgrade scripts, and
    intermediate .so's are unlikely to be present.

    (E) Involves relying on pg_upgrade. In addition to the sledgehammer
    issue, I really don't see how this would work *at all*. First, modules
    would almost by definition have a release cycle which is independant of
    PostgreSQL core, and many versions of modules would work with several
    versions of PostgreSQL. Second, pg_upgrade is currently unable to
    upgrade user-owned objects at all, so I don't see how it would be
    handling modules. Thirdly, pg_upgrade does not run scripts, so required
    steps for some module upgrades, like say rebuilding an index or
    replacing a data type, could not be handled at all. Finally, if we
    modify pg_upgrade to handle extensions, we're liable to break it.

    Have I summed up the options? Did I miss anything?

    Note that handling upgrades of complex applications is not a problem
    which anyone in the world has solved that I know of. So it's
    unsurprising that we're having difficulty with it.

    --
    -- Josh Berkus
    PostgreSQL Experts Inc.
    http://www.pgexperts.com
  • Tom Lane at Feb 3, 2011 at 7:53 pm

    Josh Berkus writes:
    (D) Requires a series of ordered upgrade scripts in sortable version
    numbering, each of which gets applied in order between the two versions.
    This initially seems like the most attractive option -- and is the one
    used by dozens of popular open source web applications -- but has some
    major roadblocks for us. First, it requires module authors to subscribe
    to a uniform sortable versions scheme (which isn't a bad thing, the
    users would certainly appreciate it, and PGXN is liable to enforce this
    anyway). Second and much more substantially, .so's installed for later
    versions might be incompatible with intermediate upgrade scripts, and
    intermediate .so's are unlikely to be present.
    FWIW, I think that last objection is bogus. There's no reason that an
    extension author can't leave dummy C functions in his code to support
    obsolete CREATE FUNCTION calls. (As an example, I added one to
    Alexander Korotkov's recent pg_trgm patch. It consists of 10 lines of
    boilerplate code, and could have been less if I'd used a quick and dirty
    elog() instead of ereport().) This is certainly a lot less of a problem
    than the difficulties with the other approaches.

    I think some of your objections to the pg_upgrade approach are equally
    bogus. In particular, I don't believe any of these approaches will
    usefully serve cases where indexes have to be rebuilt to be compatible
    with a new .so. Those indexes won't all be in the same database, and
    even if they were, no simple SQL script is going to be able to find
    them. If an extension author wants to break on-disk compatibility, it's
    going to be just as unfriendly to his users as such a break in the core
    database will be, ie, they're going to have to do dump and reload. The
    extension mechanism can't be expected to solve that.

    regards, tom lane
  • Josh Berkus at Feb 3, 2011 at 11:29 pm

    FWIW, I think that last objection is bogus. There's no reason that an
    extension author can't leave dummy C functions in his code to support
    obsolete CREATE FUNCTION calls. (As an example, I added one to
    Alexander Korotkov's recent pg_trgm patch. It consists of 10 lines of
    boilerplate code, and could have been less if I'd used a quick and dirty
    elog() instead of ereport().) This is certainly a lot less of a problem
    than the difficulties with the other approaches.
    Well, that makes solution (D) a lot more viable then.
    I think some of your objections to the pg_upgrade approach are equally
    bogus. In particular, I don't believe any of these approaches will
    usefully serve cases where indexes have to be rebuilt to be compatible
    with a new .so. Those indexes won't all be in the same database, and
    even if they were, no simple SQL script is going to be able to find
    them. If an extension author wants to break on-disk compatibility, it's
    going to be just as unfriendly to his users as such a break in the core
    database will be, ie, they're going to have to do dump and reload. The
    extension mechanism can't be expected to solve that.
    I could do it, given an extension upgrade script which could run
    PL/pgSQL code. That is, I could write a script which finds all indexes
    dependant on a particular data type and reindex them.

    So I disagree that it can't be solved. It just can't be solved *by
    pg_upgrade*.

    --
    -- Josh Berkus
    PostgreSQL Experts Inc.
    http://www.pgexperts.com
  • David E. Wheeler at Feb 3, 2011 at 7:56 pm

    On Feb 3, 2011, at 11:35 AM, Josh Berkus wrote:

    (D) Requires a series of ordered upgrade scripts in sortable version
    numbering, each of which gets applied in order between the two versions.
    This initially seems like the most attractive option -- and is the one
    used by dozens of popular open source web applications -- but has some
    major roadblocks for us. First, it requires module authors to subscribe
    to a uniform sortable versions scheme (which isn't a bad thing, the
    users would certainly appreciate it, and PGXN is liable to enforce this
    anyway).
    PGXN does enforce Semantic Versions (http://semver.org/), but extensions wont' be limited to PGXN, of course. Might be a lot of stuff developed for internal use in organizations, and they surely won't use the same version numbers.

    Agreed with your summary, well put.

    Best,

    David
  • Dimitri Fontaine at Feb 3, 2011 at 8:09 pm

    Josh Berkus writes:
    Let me summarize the prospective solutions based on some chatting with
    some potential extension authors (that is, folks who maintain in-house
    stuff they're thinking of offering as extensions). Especially since I
    think at this point the majority of -hackers has lost track of the argument:
    Thanks for doing that!
    (A) Writing a separate upgrade script between each two versions
    (B) As (A), but with in-script Includes so that eariler versions of
    scripts could be re-used for later version upgrades or strung together.
    Well if you want to support upgrades between each two versions, that
    means you have users and you don't know what they currently have
    installed. Then you have this problem to solve, and it's complex the
    same no matter what tools are offered.
    (C) as (A), but through concatinating scripts for upgrade using Make
    files. This seems like the worst of all possible solutions. First, it
    prevents us from ever having a binary module release network, and
    permanently requires anyone using extensions to have GNU make present
    and accessible on their system, thus pretty much leaving out the Windows
    users forever. Second, it's a lot harder for module maintainers to
    tweak than includes would be, especially for SQL-only modules. Third,
    it requires Make to check what version you currently have installed in
    order to build the module, something which is unlikely to be reliable.
    You're missing something here. In this scheme, make is only used to
    prepare the upgrade scripts. Then you package and ship those, and you
    don't need make no more, even when the target is windows. More than
    that, the tool to use would be `cat`, really, Make would just call it on
    files in the right order. A perl one-liner will certainly do just fine.

    So in fact this is just saying the authors to manage the situation as
    they wish, then setup the control file with the produced scripts
    references to use. That's it.
    (D) Requires a series of ordered upgrade scripts in sortable version
    numbering, each of which gets applied in order between the two versions.
    Well, your build process is certainly powerful enough to concatenate
    file content together, right?
    Note that handling upgrades of complex applications is not a problem
    which anyone in the world has solved that I know of. So it's
    unsurprising that we're having difficulty with it.
    Agreed. So my proposal was, again, that we don't solve it this year but
    provide a mechanism that allows extension authors to setup which script
    to run when the user will ALTER EXTENSION foo UPGRADE. How they come up
    with such a script, I say, is *NOT* our problem. At least for 9.1.

    Regards,
    --
    Dimitri Fontaine
    http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
  • Josh Berkus at Feb 3, 2011 at 9:25 pm

    Well if you want to support upgrades between each two versions, that
    means you have users and you don't know what they currently have
    installed. Then you have this problem to solve, and it's complex the
    same no matter what tools are offered.
    How *are* we detecting which version is installed, anyway? Is that in
    the pg_extenstions table?
    You're missing something here. In this scheme, make is only used to
    prepare the upgrade scripts. Then you package and ship those, and you
    don't need make no more, even when the target is windows. More than
    that, the tool to use would be `cat`, really, Make would just call it on
    files in the right order. A perl one-liner will certainly do just fine.
    Ah, I see. So you're proposing a build system for the 100's of
    verison-to-version upgrade scripts. That makes a lot more sense,
    although I wonder what such a build script would look like in actuality.
    Agreed. So my proposal was, again, that we don't solve it this year but
    provide a mechanism that allows extension authors to setup which script
    to run when the user will ALTER EXTENSION foo UPGRADE. How they come up
    with such a script, I say, is *NOT* our problem. At least for 9.1.
    So every package would include a script called upgrade.sql ( or
    upgrade.c? ) which is supposed to handle the upgrade, and it's up to the
    module author to power that, at least until 9.2? Seem like the most
    reasonable course for February ...

    --
    -- Josh Berkus
    PostgreSQL Experts Inc.
    http://www.pgexperts.com
  • Dimitri Fontaine at Feb 4, 2011 at 9:04 am

    Josh Berkus writes:
    How *are* we detecting which version is installed, anyway? Is that in
    the pg_extenstions table?
    The installed version is in the pg_extenstion catalog, the version we're
    upgrading to is in the control file and can be seen in the system view
    pg_available_extensions or from the system SRF named the same:

    ~:5490=# \dx
    List of extensions
    Schema | Name | Version | Description
    ------------+-----------+----------+-----------------------------------------
    pg_catalog | adminpack | 9.1devel | Administrative functions for PostgreSQL
    public | lo | 9.1devel | managing Large Objects
    (2 rows)

    ~:5490=# select oid, * from pg_extension ;
    oid | extname | extnamespace | relocatable | extversion
    -------+-----------+--------------+-------------+------------
    16385 | lo | 2200 | t | 9.1devel
    16406 | adminpack | 11 | f | 9.1devel
    (2 rows)

    ~:5490=# select schema, name, installed, version from pg_available_extensions limit 10;
    schema | name | installed | version
    ------------+--------------------+-----------+----------
    public | lo | 9.1devel | 9.1devel
    pg_catalog | adminpack | 9.1devel | 9.1devel
    citext | | 9.1devel
    chkpass | | 9.1devel
    cube | | 9.1devel
    pg_stat_statements | | 9.1devel
    pg_buffercache | | 9.1devel
    dict_xsyn | | 9.1devel
    earthdistance | | 9.1devel
    xml2 | | 9.1devel (10 rows)
    So every package would include a script called upgrade.sql ( or
    upgrade.c? ) which is supposed to handle the upgrade, and it's up to the
    module author to power that, at least until 9.2? Seem like the most
    reasonable course for February ...
    Yeah. Of course you want to support shipping upgrade files for more
    than one upgrade situation, that's in my proposal and patch too. The
    extension author "just" have to fill in the control file with an upgrade
    setup: regexp against installed version string => upgrade script to
    use. And that's about it.

    Regards,
    --
    Dimitri Fontaine
    http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
  • Robert Haas at Feb 4, 2011 at 2:53 pm

    On Thu, Feb 3, 2011 at 4:24 PM, Josh Berkus wrote:
    So every package would include a script called upgrade.sql ( or
    upgrade.c? ) which is supposed to handle the upgrade, and it's up to the
    module author to power that, at least until 9.2?  Seem like the most
    reasonable course for February ...
    I don't think we should commit something that for 9.1 that we may need
    to change incompatibly for 9.2. If we're not completely happy with
    it, it gets booted. Whatever we put in place here is going to be with
    us for a long, long time.

    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise PostgreSQL Company
  • Dimitri Fontaine at Feb 4, 2011 at 3:14 pm

    Robert Haas writes:
    I don't think we should commit something that for 9.1 that we may need
    to change incompatibly for 9.2. If we're not completely happy with
    it, it gets booted. Whatever we put in place here is going to be with
    us for a long, long time.
    So, what is it specifically here that you're unhappy with?

    a. ALTER EXTENSION ... UPGRADE;
    b. CREATE WRAPPER EXTENSION ...; (version is then NULL)
    c. upgrade rules in the control file
    d. ALTER OBJECT ... SET EXTENSION ...;
    e. having upgrade scripts for upgrading contribs from null
    f. having those scripts named $contrib.upgrade.sql

    What I think is that the end-user syntax (the SQL DDLs) that we add are
    going to fall exactly into the category you're talking about: long, long
    term support.

    But that could well be less true of the control file, should we choose
    so. I think there's enough value in being able to get extension from
    what you had installed in pre-9.1; that changing some non-DLL bits in
    9.2 is something we can set ourselves to consider.

    But anyway, we've been doing quite a round of expectations, explaining,
    detailing, and bikeshedding on the features already, so I'd like to see
    a break down, because it appears clearly that some readers changed their
    mind in the process.

    Regards,
    --
    Dimitri Fontaine
    http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
  • Robert Haas at Feb 4, 2011 at 3:49 pm

    On Fri, Feb 4, 2011 at 10:13 AM, Dimitri Fontaine wrote:
    Robert Haas <robertmhaas@gmail.com> writes:
    I don't think we should commit something that for 9.1 that we may need
    to change incompatibly for 9.2.  If we're not completely happy with
    it, it gets booted.  Whatever we put in place here is going to be with
    us for a long, long time.
    So, what is it specifically here that you're unhappy with?
    I'd like to answer this question, but I have not had enough time to
    read through this patch in detail, because there are 97 patches in
    this CommitFest. The point I'm trying to make, however, is
    procedural. We shouldn't commit anything at the very end of a
    development cycle that we're not reasonably comfortable we can live
    with, because there is not a lot of time to change our minds later. I
    completely believe that an extension upgrade mechanism is a good thing
    to have and I'm sympathetic to your desire to get this into 9.1 - but
    the fact is that we are very short on time, the prerequisite patch is
    not committed yet, and this is a big piece of functionality in a
    tricky area which was submitted for the last CommitFest of the cycle
    and about which there is not a clear design consensus.

    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise PostgreSQL Company
  • David E. Wheeler at Feb 4, 2011 at 4:48 pm

    On Feb 4, 2011, at 7:48 AM, Robert Haas wrote:

    I'd like to answer this question, but I have not had enough time to
    read through this patch in detail, because there are 97 patches in
    this CommitFest. The point I'm trying to make, however, is
    procedural. We shouldn't commit anything at the very end of a
    development cycle that we're not reasonably comfortable we can live
    with, because there is not a lot of time to change our minds later. I
    completely believe that an extension upgrade mechanism is a good thing
    to have and I'm sympathetic to your desire to get this into 9.1 - but
    the fact is that we are very short on time, the prerequisite patch is
    not committed yet, and this is a big piece of functionality in a
    tricky area which was submitted for the last CommitFest of the cycle
    and about which there is not a clear design consensus.
    Robert, I think that the core extension if pretty uncontroversial, modulo some minor issues. It's the upgrade process that's more controversial. I think the case can be made to accept even that part as Dim has written it, because it is pretty much the bare minimum that other solutions could be built on top of and improve upon. But if not, I think that's the only part the one might really look at as something to omit for 9.1.

    Dim, I haven't followed that closely lately, but is the ALTER EXTENSION UPGRADE bit still a separate patch?

    Best,

    David
  • Robert Haas at Feb 4, 2011 at 4:54 pm

    On Fri, Feb 4, 2011 at 11:48 AM, David E. Wheeler wrote:
    Robert, I think that the core extension if pretty uncontroversial, modulo some minor issues. It's the upgrade process that's more controversial. I think the case can be made to accept even that part as Dim has written it, because it is pretty much the bare minimum that other solutions could be built on top of and improve upon. But if not, I think that's the only part the one might really look at as something to omit for 9.1.
    Yeah, I understand. I believe Tom said he was going to look at the
    basic functionality with an eye toward commit, and I hope to look at
    it as well, either before or after it gets committed. Time
    permitting, I'd then like to look at this, but I'm not sure I'm going
    to be able to squeeze that into the time available, nor am I sure that
    I'd get sufficient consensus to commit something even if I did.

    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise PostgreSQL Company
  • David E. Wheeler at Feb 4, 2011 at 5:02 pm

    On Feb 4, 2011, at 8:54 AM, Robert Haas wrote:

    Yeah, I understand. I believe Tom said he was going to look at the
    basic functionality with an eye toward commit, and I hope to look at
    it as well, either before or after it gets committed. Time
    permitting, I'd then like to look at this, but I'm not sure I'm going
    to be able to squeeze that into the time available, nor am I sure that
    I'd get sufficient consensus to commit something even if I did.
    Great, thanks.

    Best,

    David
  • Dimitri Fontaine at Feb 4, 2011 at 5:03 pm

    "David E. Wheeler" <david@kineticode.com> writes:
    Dim, I haven't followed that closely lately, but is the ALTER
    EXTENSION UPGRADE bit still a separate patch?
    Yes it is. It's an incremental that apply on top of the extension patch
    and get its own patch entry on the commit fest application:

    https://commitfest.postgresql.org/action/patch_view?id=472

    As such it will need bitrot fixing as soon as the extension main patch
    makes it in. Also I have some cleaning to do here, but given the
    current open discussion about the design I'm still holding this work.
    Well, it seems the discussion is slowing down to realize I only included
    the bare minimum just so that we avoid having too long a discussion and
    that the patch has its chances to 9.1 :)

    Regards,
    --
    Dimitri Fontaine
    http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
  • Tom Lane at Feb 10, 2011 at 6:26 pm
    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 ability for any script file to override it, anyway.

    encoding: I don't see any big problem with insisting that all scripts for
    a given extension be in the same encoding.

    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.

    Comments?

    regards, tom lane
  • David E. Wheeler at Feb 10, 2011 at 6:52 pm

    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

Related Discussions

People

Translate

site design / logo © 2022 Grokbase