[postgis-users] 1.4.1 upgrade problem with existing types - box3d_extent, pgis_abs

[Ben Madin]

G'day all,

I just tried the upgrade script (from 1.4.0 to 1.4.1), but I seem to get a number of fatal errors, and if I work around them by manually dropping the types, other notices.

In a nutshell, if I go through this process, the upgrade COMMITS. If I run it again, it fails at exactly the same points.

At the end of the day, I suspect the notices are because the DROP ... CASCADE of the two types removed the Aggregates so I am OK. 

But I want to check is the issue with the types related to this part of the PostgreSQL Manual (/usr/local/pgsql/share/doc/html/sql-createtype.html):

Before PostgreSQL version 8.2, the syntax CREATE TYPE name did not exist. The way to create a new base type was to create its input function first. In this approach, PostgreSQLwill first see the name of the new data type as the return type of the input function. The shell type is implicitly created in this situation, and then it can be referenced in the definitions of the remaining I/O functions. This approach still works, but is deprecated and might be disallowed in some future release. Also, to avoid accidentally cluttering the catalogs with shell types as a result of simple typos in function definitions, a shell type will only be made this way when the input function is written in C.

so I can relax and enjoy the upgrade.

cheers

Ben



 



prices=# \i /usr/local/pgsql/share/contrib/postgis_upgrade.sql                                                                                              
BEGIN
CREATE FUNCTION
       postgis_major_version_check        
------------------------------------------
 Scripts versions checked for upgrade: ok
(1 row)

DROP FUNCTION
CREATE FUNCTION
CREATE FUNCTION

...

CREATE FUNCTION
psql:/usr/local/pgsql/share/contrib/postgis_upgrade.sql:225: ERROR:  function box3d_extent_out(box3d_extent) does not exist
psql:/usr/local/pgsql/share/contrib/postgis_upgrade.sql:229: ERROR:  current transaction is aborted, commands ignored until end of transaction block

if I drop the type and run :

gms=# drop type box3d_extent cascade;
NOTICE:  drop cascades to 9 other objects
DETAIL:  drop cascades to function box3d_extent_in(cstring)
drop cascades to function box3d_extent_out(box3d_extent)
drop cascades to function combine_bbox(box3d_extent,geometry)
drop cascades to function st_box2d(box3d_extent)
drop cascades to function st_box3d_extent(box3d_extent)
drop cascades to function st_combine_bbox(box3d_extent,geometry)
drop cascades to function st_geometry(box3d_extent)
drop cascades to function extent(geometry)
drop cascades to function st_extent(geometry)
DROP TYPE
gms=# \i /usr/local/pgsql/share/contrib/postgis_upgrade.sql                                                                                                 

...

CREATE FUNCTION
psql:/usr/local/pgsql/share/contrib/postgis_upgrade.sql:2693: ERROR:  type "pgis_abs" already exists
psql:/usr/local/pgsql/share/contrib/postgis_upgrade.sql:2697: ERROR:  current transaction is aborted, commands ignored until end of transaction block


gms=# drop type pgis_abs cascade;
NOTICE:  drop cascades to 17 other objects
DETAIL:  drop cascades to function pgis_abs_in(cstring)
drop cascades to function pgis_abs_out(pgis_abs)
drop cascades to function pgis_geometry_accum_finalfn(pgis_abs)
drop cascades to function pgis_geometry_accum_transfn(pgis_abs,geometry)
drop cascades to function accum(geometry)
drop cascades to function st_accum(geometry)
drop cascades to function pgis_geometry_collect_finalfn(pgis_abs)
drop cascades to function collect(geometry)
drop cascades to function st_collect(geometry)
drop cascades to function pgis_geometry_makeline_finalfn(pgis_abs)
drop cascades to function makeline(geometry)
drop cascades to function st_makeline(geometry)
drop cascades to function pgis_geometry_polygonize_finalfn(pgis_abs)
drop cascades to function polygonize(geometry)
drop cascades to function st_polygonize(geometry)
drop cascades to function pgis_geometry_union_finalfn(pgis_abs)
drop cascades to function st_union(geometry)
DROP TYPE
gms=# \i /usr/local/pgsql/share/contrib/postgis_upgrade.sql

...

CREATE FUNCTION
psql:/usr/local/pgsql/share/contrib/postgis_upgrade.sql:215: NOTICE:  type "box3d_extent" is not yet defined
DETAIL:  Creating a shell type definition.
CREATE FUNCTION
psql:/usr/local/pgsql/share/contrib/postgis_upgrade.sql:219: NOTICE:  argument type box3d_extent is only a shell
CREATE FUNCTION
CREATE TYPE
CREATE FUNCTION

...

CREATE FUNCTION
psql:/usr/local/pgsql/share/contrib/postgis_upgrade.sql:1218: NOTICE:  aggregate extent(geometry) does not exist, skipping
DROP AGGREGATE
CREATE AGGREGATE
psql:/usr/local/pgsql/share/contrib/postgis_upgrade.sql:1224: NOTICE:  aggregate st_extent(geometry) does not exist, skipping
DROP AGGREGATE

...

CREATE AGGREGATE
psql:/usr/local/pgsql/share/contrib/postgis_upgrade.sql:2683: NOTICE:  type "pgis_abs" is not yet defined
DETAIL:  Creating a shell type definition.
CREATE FUNCTION
psql:/usr/local/pgsql/share/contrib/postgis_upgrade.sql:2687: NOTICE:  argument type pgis_abs is only a shell
CREATE FUNCTION
CREATE TYPE

...

CREATE FUNCTION
psql:/usr/local/pgsql/share/contrib/postgis_upgrade.sql:2718: NOTICE:  aggregate accum(geometry) does not exist, skipping
DROP AGGREGATE
CREATE AGGREGATE
psql:/usr/local/pgsql/share/contrib/postgis_upgrade.sql:2725: NOTICE:  aggregate st_accum(geometry) does not exist, skipping
DROP AGGREGATE

...

CREATE FUNCTION
psql:/usr/local/pgsql/share/contrib/postgis_upgrade.sql:2756: NOTICE:  aggregate st_union(geometry) does not exist, skipping
DROP AGGREGATE
CREATE AGGREGATE
psql:/usr/local/pgsql/share/contrib/postgis_upgrade.sql:2763: NOTICE:  aggregate collect(geometry) does not exist, skipping
DROP AGGREGATE
CREATE AGGREGATE
psql:/usr/local/pgsql/share/contrib/postgis_upgrade.sql:2770: NOTICE:  aggregate st_collect(geometry) does not exist, skipping
DROP AGGREGATE
CREATE AGGREGATE
psql:/usr/local/pgsql/share/contrib/postgis_upgrade.sql:2777: NOTICE:  aggregate polygonize(geometry) does not exist, skipping
DROP AGGREGATE
CREATE AGGREGATE
psql:/usr/local/pgsql/share/contrib/postgis_upgrade.sql:2784: NOTICE:  aggregate st_polygonize(geometry) does not exist, skipping
DROP AGGREGATE
CREATE AGGREGATE
psql:/usr/local/pgsql/share/contrib/postgis_upgrade.sql:2791: NOTICE:  aggregate makeline(geometry) does not exist, skipping
DROP AGGREGATE
CREATE AGGREGATE
psql:/usr/local/pgsql/share/contrib/postgis_upgrade.sql:2798: NOTICE:  aggregate st_makeline(geometry) does not exist, skipping
DROP AGGREGATE
CREATE AGGREGATE