Dimitri Fontaine <dimi...@2ndquadrant.fr> writes: > On to your question about the upgrade design, in order not to paint > ourselves into a corner. What I now have in mind is the following:
> When there's an extension upgrade the user will have to install the new > files (.so, .sql, .control) and run an upgrade command in his databases: > ALTER EXTENSION pair UPGRADE; > The version we upgrade from is known from the catalog, the version we > upgrade to is read in the control file. So we are able to call the sql > script and offer a way for it to know about the versions. The simplest > way seems to be a new pair of functions: > pg_extension_upgrade() returns bool > pg_extension_versions() returns table(current text, next text) > Those are to be run only from the extension's script. > The first returns false when the user did CREATE EXTENSION and true when > the user did ALTER EXTENSION UPGRADE, which are the only two commands > that will run the script. > The second will return the versions we detailed above, and the > extension's author is free to compare them however he wants to and > decide what to do now. It's cool that we have DO blocks here, and > pg_execute_sql_file() to offer the same facility as \i for psql scripts. Hmm ... I don't believe that extension SQL scripts should rely on DO blocks. There is no requirement that plpgsql be installed, and we're not going to create one as part of this feature. What this means is that the design you offer above doesn't work at all, since it fundamentally assumes that the SQL script can do conditional logic. What's more, it fundamentally assumes that the script WILL do conditional logic and support (in one lump) every possible combination of versions. That's going to turn into buggy spaghetti-code very quickly. I think that something that could work is more along the lines of the extension containing different upgrade scripts for whatever set of cases the author feels like supporting; for example the foo extension might provide both foo_upgrade.11.13.sql foo_upgrade.12.13.sql if the author is willing to support one-step upgrades from two preceding versions to version 13. It would then be the responsibility of the ALTER EXTENSION code to select and execute the correct upgrade script. A missing script would be reported as an upgrade failure by ALTER EXTENSION. (Actually, we could probably assume that the target version is implicitly "the current version", as identified from the control file, and omit that from the script file names. That would avoid ambiguity if version numbers can have more than one part.) 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