Need to generate upgrade script for topology

[robe]

strk,

I wanted to test out the upgrade features of extension, but in order to do that, I need an upgrade script for topology.

I assume we would build it so it has everything but the create type, create schema, create table stuff using the perl script we have for postgis. Not sure how to do that.

We'll need the same for raster, which I think I already have as a ticket.

The CREATE EXTENSION can also support non-numeric versions, so pre-relese I was just going to make the versions

postgis_topology version 2.0.0a1 etc. We'd be using ssentially the same script to upgrade for each so i'd just need to copy them to files like

postgis_topology—2.0.0a1—2.0.0a2.sql etc which I'll incorporate in the extension make.

These get daisy chained if it can't find a way to go from v1—v2 with a single file. Though there doesn't seem to be a mechanism to put in a wild card version

e.g. 2.0—2.0.0 which is a bit of a bummer.

Then when people upgrade, they would run the sql command:

ALTER EXTENSION postgis_topology UPDATE TO "2.0.0a2";

[strk]

Now you are a sed hacker you could try with that. Who needs perl anymore !

How does "ALTER EXTENSION UPDATE" know if it's possible to update w/out dump-reload ?

In other words.. how do we do when we _need_ to change something in CREATE TABLE / CREATE TYPE. For example you just droped an ALTER TABLE ADD COLUMN of mine (for topology.topology) which was there for the specific purpose of keeping the .sql file usable for upgrading since the stone-age of topology. That one was an update adding a field to topology.topology. How is that done with ALTER EXTENSION ?

[robe]

It doesn't you just don't create a migration path between if it would require a dump/reload. ALTER can never do a dump / reload. So if we created an extension for postgis version 1.5.3 for example, we would not define a script that takes it to postgis version 2.0.0 so if the user tries to toggle to 2.0.0 — it would give an error — "No migration path defined for 1.5.3 to 2.0.0"

The main benefit is that the extension functions and tables do not get backed up.

All that is added is a line in backup saying.

CREATE EXTENSION ….

So I would have to as part of the extension definition mark spatial_ref_sys as editable so it gets backed up but it wouldn't back up the structure just the data.

I haven't investigated if you have a specific version how it would upgrade. I think if it can't find the older version it upgrades to the next available. So if you deleted all your postgis 1.5 extensions, it would upgrade to 2.0 extension. If you have both available though — it would keep it at postgis 1.5

[robe]

speaking of which I guess I have to mark layer and topology tables as user editable as well so the data gets backed up.

[robe]

Note however your alter table doesn't require a dump reload and I think that is legal to put in an upgrade script.

[robe]

done at r8157. Might fine-tune it later, but think it's ready for prime time. It assumes we won't be upgrading tables, domains, casts in minor. We might need to create an additional tack on script if we do add casts etc.