upgrade issues

[robe]

If I try to upgrade 1.4.0SVN to 1.4 beta — doesn't work — again not a biggie since I presume no one will be doing this. Just thought I would mention it. Gives:

ERROR:  type "box3d_extent" already exists

********** Error **********

ERROR: type "box3d_extent" already exists
SQL state: 42710

[pramsey]

Soft upgrade or hard? (postgis_upgrade.sql or the perl script)?

[robe]

the postgis_upgrade.sql

[pramsey]

Can you be clearer about where the issues begin and end? I just make a 1.3 database, loaded data, updated to 1.4 and built an index no problem. But perhaps I was misreading the ticket.

[pramsey]

There's no issue updating (actually it's downgrading) from 1.4.0SVN to 1.4b, because we have committed to no API changes. There will never be any functions to change, right? Maybe not. Somehow this script ends up having to be version-aware.

[robe]

Paul,

Sorry about that. I'm retracing my steps by reloading my database from 1.3.6 backup.

1) This is a database that has gone thru many upgrades (form 1.1) — so loading it from a PostgreSQL 8.2 (1.3.6) to PostgreSQL 8.4 RC1

2) Then upgrading that using the postgis_upgrade.sql (first error I got was about my view vwtest — so dropped that and reran script). Script said it was fine.

3) Did select postgis_full_version() — stated I was running "POSTGIS="1.4.0b1" GEOS="3.1.1-CAPI-1.6.0" PROJ="Rel. 4.6.1, 21 August 2008" USE_STATS" (looks good)

3) Ran some queries — super slow and Hmm I noticed non of my tables got the gist index (but forgot to check if they had them before the upgrade so this might be a restore issue or my 1.3.6 install on PostgreSQL 8.3).

4) Proceeded to build index on table — bam ERROR: data type geometry has no default operator class for access method "gist" HINT: You must specify an operator class for the index or define a default operator class for the data type.

(Looked at my Operator Classes section and gist for geometry is missing).

I suspect this may be a false alarm that maybe there is something wrong with my 1.3.6 install on PostgreSQL 8.4

[pramsey]

Trying to replicate your situation (table with existing index, do upgrade) I'm not seeing the same effect, so I'm going to alter the description of this issue to exclude everything except the final case, which is the (upcoming) issue of 1.4.X upgrades, which won't work because the script is hard-coded to expect a 1.3→1.4 upgrade.

[mcayland]

Well, we don't need to worry about 1.4.x to 1.4.x upgrades because we're promising not to change the SQL API until 1.5

[pramsey]

Right, but we might change something behind the API. Or we might fix a bug in the PL/PgSQL scripts.

[robe]

Not to mention it gives that annoying comment (proc upgrade needed) when you run the postgis_full_version()

[kneufeld]

I understood "not change the API" to mean that we're not going to change the behaviour of current functions - the expected output won't change. Bugs may be removed and enhancement possibly made, but code that works on 1.4.0 will continue to work on 1.4.x till the death of 1.4.

Paul, are you saying that this precludes the possibility of adding new functions to the mix? ie. ST_MakeEnvelope. This doesn't the API, just adds new functionality.

Won't we need an upgrade script to add such enhancement methods? —Kevin

[robe]

WE WILL NOT BE ADDING NEW FUNCTIONS. No API changes means NO NEW FUNCTIONS.

You can change what the functions do behind the scenes, but no new functions and no change in arguments that functions take.

[kneufeld]

Okey Dokey. Got it.

[robe]

FYI my prior problem was a false alarm. I dumped and reloaded using the PostgreSQL 8.4 dump reload and it worked fine this time after upgrading (and my indexes were intact). So maybe I had used 8.2 to dump before or something. Anyrate not sure whatever was amiss before but upgrade seems to work well as far as I can tell. Except for the aggregate issue (when you use aggs in views). Have to drop the view first before you can upgrade.

[strk]

If we promise not to ever change those types (or at least postpone this problem until we do) we could define a temporary function (in the postgis_upgrade.sql) to create the type only if not existant. Or we should check the effects of a DROP TYPE.

[robe]

Now that I've been experimenting with upgrading one of my 1.4 databases to 1.5 and next 1.3 to 1.5 and of course the CREATE TYPES are getting in the way of using the postgis_upgrade.sql without editing. I have come up with a possibly hair-brained idea along the ideas of creating a temp plpgsql function we discussed.

Seems our upgrade problems are mostly caused by CREATE TYPE and CREATE CASTS, the fact that we don't have a CREATE IF NOT EXISTS (until PostgreSQL 8.5). The other issue which maybe is caused because of this, is why we even need a postgis upgrade.sql to begin with.

My thought — create a plpgsql function — which I'm fussing with at the moment. That compartmentalizes all our CREATE TYPE, CREATE CASTS (some stuff that may have a drop we may not want to if its used in views and we don't want the upgrade to fail because of it) stuff that takes two arguments —> object_type, type name.

We create this function first as part of postgis.in.sql.c and plop in all our CREATE TYPE …. wrapped with IF conditions that check the pg_catalog for the existence of these things and if they are used etc..

Then where we currently have

CREATE TYPE something — we replace with SELECT postgis_create('TYPE', 'something');

And if Mark is bothered with having this function around — and also the fact that it would be introducing a function to PostGIS so we can't back port it (unless it doesn't exist when we are done), we can simply drop it at the end of the postgis.sql.

So in short. We have just one postgis.sql (no soft upgrade anything). Our Soft Upgrade instructions are simply to say rerun postgis.sql or if you are coming from a really old version of PostGIS you have to do a dump restore.

[mcayland]

I'm afraid I'm still -1 on this. I would still like static .sql files for upgrading between different minor versions, and drop anything like this. My fear is that doing this makes upgrades non-deterministic, since the script will have different behaviours depending on the current state of the database it is being run on, and I'm not convinced that's a good thing

ATB,

Mark.

[robe]

Mark, I fail to see why its non-deterministic. The plpgsql call will be doing exactly the same thing as our CREATE … is doing now except possibly doing more inspection to see that we have remnants of other installs in there. Like observer that the type that exists is incompatible with what we are loading etc.

I'm more concerned that we are leaving things out in the postgis_upgrade.sql. As I mentioned before we left out a cast in postgis_upgrade.sql that exists in the regular postgis.sql file.

The way I see it. We have to double input stuff to generate postgis.sql and postgis_upgrade.sql which is more time consuming and prone to error. I'm not so much against having 2 files as I am about this problem.

If we can put some flags in for postgis.sql.in.c so we can replace the create etc. with a plpgsql function, I would be very happy. Then we can have just one script to generate the postgis.sql and postgis_upgrade.sql file. As it is I basically just use the postgis.sql and wipe out all the create stuff to guarantee I am getting everything.

[robe]

Mark et al. Attached is the script I was thinking of

I was trying to figure out how to get the postgis_upgrade.pl to generate something like this file, but couldn't figure that out. In theory as I said if we use a

SELECT postgis_create.. command for creating objects that can't be dropped then this would make life easier.

Right now the upgrade scripts generated by the .pl miss creating operators and CASTS and always create new types which means you can never really use the postgis_upgrade.sql for micro to micro upgrade for PostGIS 1.4. Ideally it should only drop an aggregate if the aggregate structure was changed since the version that is installed. All this can be checked in my proposed postgis_create function.

In writing this up I also see we forgot to delete those st_union_old … etc. aggregates, which the postgis_create I have set to just drop if it sees its existence.

Attached is the script. Right now its just the postgis.sql.in.c with the non-function create stuff moved to postgis_create calls.

[robe]

proposed upgrade like script

[robe]

Here is a revised patch to trunk postgis.in.sql.c.

This does both upgrade as well as doing a fresh install. Unfortunately it seems that the way I did it totally confuses the postgis_upgrade.sql generator.

This does what I described above, but I tested it by upgrading a postgis 1.4 to 1.5 and creating a postgis 1.5 fresh. Have to find my old 1.3 data to test upgrading, but should work since it recreates aggregates, but only creates types if they don't already exist.

I was thinking we can make it smarter by when we decide to rename everything check which functions are used in a type and if we don't like the name, rename the function to the new name (of course dropping the new name).. Haven't attempted that.

[robe]

patch to do both fresh install and upgrade

[pramsey]

Untested version specific upgrade scripts committed to trunk at r4804

[pramsey]

Untested version specific upgrade scripts committed to 1.4 branch at r4805

[pramsey]

Tested 1.4 and 1.5 upgrade scripts from 1.3 and 1.4. Removed deprecated functions in 1.5 upgrade process. Committed to trunk at r4806

Closing this ticket, new issues with upgrade scripts, in a new, version-appropriate ticket please.

[mcayland]

I've had a brief look at this, and it seems fine on the surface. My only minor quibble is that there are no documentation updates to reflect the change in the upgrade process.

ATB,

Mark.