Cannot upgrade from 2.0 to 2.1 (non-extension upgrade)

[Bborie Park]

Upgrading a test database from 2.0 to 2.1 using postgis_upgrade_20_21.sql, I get the following error message…

psql:postgis_upgrade_20_21.sql:4323: 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.

If postgis/postgis_drop_before.sql is used before calling postgis_upgrade_20_21.sql, the error goes away.

[robe]

the postgis_drop_before.sql should be included in that script — no.

I had fixed this in a different ticket (can't remember number off hand) but admittedly I was only testing with extensions.

[robe]

ah here is the ticket #2279

[robe]

Looking at the extension make script I guess it does explicitly include the postgis_drop_before.sql right before the generated postgis_upgrade_20_21.sql. So I guess the question is whether we have this script included as part of the make of postgis_upgrade_20_21.sql or move this logic to another file that always gets included, but then what would be the purpose of postgis_drop_before.sql aside from 1.5 users.

[robe]

Just to clarify extension upgrading is fine this is just specific to using manual .sql scripts.

[strk]

Supposedly drop_before.sql is already included in postgis_upgrade_20_21.sql, according to this rule in Makefile:

postgis_upgrade_20_21.sql.in: postgis_drop_before.sql postgis.sql postgis_drop_after.sql
  cat $^ > $@
postgis_upgrade_20_21.sql: postgis_upgrade_20_21.sql.in ../utils/postgis_proc_upgrade.pl
  $(PERL) ../utils/postgis_proc_upgrade.pl $< 2.0 > $@

Dustymugs, are you in a position to try reproducing this bug after checking generation of that file ?

[robe]

strk,

I suspect your script is stripping out my commands. I thought it was included as well but was puzzled when I had to explicitly add it to the extension script again.

I think we need to move the drop_before and after stuff after the generation so they don't get mangled by your perl script. those should always be included regardless of what they contain (even if they CREATE/DROP things we normally don't do in upgrade scripts)

[Bborie Park]

strk,

Yes, I should be able to reproduce the bug. I just need to go back to before r11288 where geometry_gist_sel_2d() is deleted.

I'd guess that postgis_upgrade_21_minor.sql also has the same issue.

[strk]

You're right, the perl script was stripping out the DO construct. Should be fixed by r11518 — dustymug can you check that ?

[Bborie Park]

Yay. It works!