Re: ALTER EXTENSION UPGRADE, v3

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