Can't upgrade PostGIS from 2.0

[robe]

UPDATE: on closer inspection this makes it IMPOSSIBLE to do a soft upgrade from 2.0 to 2.1 with or without extensions.

---

This I suspect is just an extension issue and I have to put in extra logic for this. I'll double check but I did my usual micro-micro upgrade

Upgrading from 2.1.0 SVN r11142 to r11306

with

ALTER EXTENSION postgis UPDATE TO "2.1.0SVNnext";

and got this

ERROR:  cannot drop function geometry_gist_sel_2d(internal,oid,internal,integer) because other objects depend on it

[robe]

okay this might be something wrong with my installation. This is my oldest database ever that was restored from 1.5 using postgis_restore.pl and that I have been happily upgrading from 2.0 to 2.1s.

I tried creating a database with a postgis 2.1 from February and then upgraded it to latest and it was fine. Funny thing about this database is that when I list functions in the extension, geometry_gist_sel_2d is not one of them and yet the extension depends on it and I don't see any other reference and I can't drop it from the extension because well it's not part of it.

ERROR:  function geometry_gist_sel_2d(internal,oid,internal,integer) is not a member of extension "postgis"

Could be a loose thread in PostgreSQL extension machinery.

Same issue with all these:

DROP FUNCTION IF EXISTS geometry_gist_sel_2d (internal, oid, internal, int4);
DROP FUNCTION IF EXISTS geometry_gist_joinsel_2d(internal, oid, internal, smallint);
DROP FUNCTION IF EXISTS geography_gist_selectivity (internal, oid, internal, int4);
DROP FUNCTION IF EXISTS geography_gist_join_selectivity(internal, oid, internal, smallint);

[robe]

Back to blocker. Just tried to do the following on a new database.

CREATE EXTENSION postgis VERSION 2.0.3;
ALTER EXTENSION postgis UPDATE TO "2.1.0SVN";

and got the same error. so somewhere the extension is not seeing these functions as being part of PostGIS extension but yet won't allow them to be dropped because some how they are part of the extension or something in the extension depends on them.

This is running on PostgreSQL 9.2.4. So might be very well a bug in the PostgreSQL extension machinery rather than PostGIS scripts.

[robe]

This is now SUPER SERIOUS. To rule out extension as the culprit, I decided to install and upgrade the old-fashioned strk way.

1) Create a new database and run the numerous scripts to install 2.0.3 (both postgis.sql and rtpostgis.sql) 2) then try to run our postgis_upgrade_20_21.sql

and to my dismay — I got the same error, was slightly more informative

ERROR:  cannot drop function geometry_gist_sel_2d(internal,oid,internal,integer) because other objects depend on it
DETAIL:  operator &&(geometry,geometry) depends on function geometry_gist_sel_2d(internal,oid,internal,integer)
operator class gist_geometry_ops_2d for access method gist depends on operator &&(geometry,geometry)
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

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

ERROR: cannot drop function geometry_gist_sel_2d(internal,oid,internal,integer) because other objects depend on it
SQL state: 2BP01
Detail: operator &&(geometry,geometry) depends on function geometry_gist_sel_2d(internal,oid,internal,integer)
operator class gist_geometry_ops_2d for access method gist depends on operator &&(geometry,geometry)
Hint: Use DROP ... CASCADE to drop the dependent objects too.

HELP pramsey.

Even worse my old db where I had replaced binaries had to swap back because it literally broke all my mapping apps since && is so critical.

[robe]

Fore reference completeness I believe we are talking about these 2 commits:

r11288 r11289

[robe]

I looked into this problem more. I can salvage your dropping of

DROP FUNCTION IF EXISTS geometry_gist_sel_2d (internal, oid, internal, int4);
DROP FUNCTION IF EXISTS geometry_gist_joinsel_2d(internal, oid, internal, smallint);

simply by putting in do logic to rename the original and then updating with the new C function. Unfortunately with your geography ones and 3D &&&, I can't do that since both share the same set of functions so I can only rename one.

So which would you rather have:

different names pointing to same C functions

or

partial duplicates (e.g. salvage geometry2d and live with geography/geometry 3d having slightly different named casts in 2 ugrade

or back to duplicate naming for everything.

[robe]

hmm I realize we have another problem that I don't think is resolvable without a dump restore for people who want to use 3D &&& selectivity benefits:

-- 2.0 &&& looked like this --
CREATE OPERATOR &&&(
  PROCEDURE = geometry_overlaps_nd,
  LEFTARG = geometry,
  RIGHTARG = geometry,
  COMMUTATOR = &&&,
  RESTRICT = contsel,
  JOIN = contjoinsel);

but new operator looks like this:

CREATE OPERATOR &&&(
  PROCEDURE = geometry_overlaps_nd,
  LEFTARG = geometry,
  RIGHTARG = geometry,
  COMMUTATOR = &&&,
  RESTRICT = gserialized_gist_sel_nd,
  JOIN = gserialized_gist_joinsel_nd);

So I guess the good news is I can get rid of the geography ( RESTRICT = geography_gist_selectivity,

JOIN = geography_gist_join_selectivity) name simply by renaming it to gserialized_gist_sel_nd etc and rolling over it with a steam iron.

The bad news is the 3D case seems hopeless however you look at it.

[robe]

Okay I think I came up with a solution that:

1) isolates my changes in 1 section for easy backout if you don't like 2) makes 2.0 names convert to new anal retentive names 3) for those who happened to have started out with 2.1, they'll still be finesince I didn't bother redefining your operators to old name.

this is at r11327 . I just tested with upgardeing a clean 2.0.3 to 2.1.0SVN. The real test will be when I try to upgrade my install with lots of data that thinks its at 2.1 already.

We are still left with the smaller problem of 2.1 &&& is different from 2.0 &&& and may require people to rebuild their 3D indexes if they want better selectivity. I'll ticket that as a separate issue.

[robe]

made a typo. Fixed at r11328

[robe]

Well sadly the upgrade did not work on my 2.1 micro, but I think it might just be me and a few other like dusty because I was upgrading early.

fails with these:

ERROR:  cannot drop function geometry_gist_sel_2d(internal,oid,internal,integer) because other objects depend on it
DETAIL:  extension postgis depends on function geometry_gist_sel_2d(internal,oid,internal,integer)
function _st_samealignment_finalfn(agg_samealignment) depends on type agg_samealignment
composite type agg_samealignment column refraster depends on type raster

I'll verify its not just because I have data.

[robe]

nevermind. User error. I copied an older set of files. It installs just fine. Now to make sure all my apps still work.

[robe]

okay apps seem to still work and they feel way faster (like at least twice). Is this switch supposed to speed things up a lot? I suppose it might be just time of day and unfortunately I haven't been benchmarking these so the speed improvement might just be in my head.

Just testing some of my queries with buttloads of joins (just geometry spatial joins and using a lot of ST_DWithin no geography) of all sorts that used to (as I recall take around 500ms are all finishing in under 60ms)