extension upgrade from 2.1.2dev to 2.2.0dev fails with st_countagg(raster,integer,boolean,double precision) does not exist

[robe]

after getting around issue #2382 by renaming my postgis-2.1 library, I get this error when trying to run the below from postgis 2.1.2dev (I cheated by copying the 2.1.1 install script — I'll add to 2.2 next)

ALTER EXTENSION postgis UPDATE TO "2.2.0dev";

ERROR: function st_countagg(raster,integer,boolean,double precision) does not exist

I didn't seem to run into this problem upgrading database using old fashioned way so might be an order issue or one of the drop extension thingy's. I thought maybe the documentation was out of date, but went and ran raster_comments.sql on my old-fashioned upgrade to verify that was not the issue.

[strk]

When the upgrade script is created you should read WARNING lines on stderr for any aggregate function which does not contain a "Changed" or "Availability" label as a previous comment. That comment, in turn, drives when the aggregate is created and when not. In this case I guess such aggregate was introduced in 2.2.0. Does it have such comment ?

[robe]

Yes it does. It only breaks on extension install, not old-fashioned one.

Also what do you do about new types? This agg depends on new type: CREATE TYPE agg_count AS (

and the new type has no Availability note.

[strk]

Same thing with types, if the type is not advertised as being intruduced it defaults to being introduced from version 0 so won't be re-created. Can it be this in turn fails to create the st_countagg function ? Shouldnt you get an error in that case ?

[robe]

I would think so. I'm just puzzled why the regular raster_upgrade.sql finishes without complaining. Maybe I'm running the wrong script or something. I'll have to check later don't have time at moment.

[robe]

Actually I think I was running the wrong raster_upgrade.sql. I was using a winnie package and must have overlaid it over my existing. I must have something hard-coded in winnie to output the named old file name, so the package she has is not packaging raster_upgrade.sql or postgis_upgrade.sql. so it's probably the same issue with the old-fashioned upgrade.

BTW we need to upgrade the docs for 2.2 to reflect this change.

[robe]

checked again using raster_upgrade.sql and while it doesn't give an error, it also doesn't install the st_countagg aggregate function. It HOWEVER does install the agg_count type. There is extra code wrapped in a custom DO that installs the agg_count type if it's not present.

The error I was seeing:

ERROR:  function st_countagg(raster, integer, boolean, double precision) does not exist

is coming from the comments install. The comments install is part of the postgis extension where as I have to manually run raster_comments.sql to trigger the error with old-fashioned.

[strk]

I'm also noticing that rtpostgis_upgrade.sql is NOT wrapped in a BEGIN/END block, very dangerous!

[dustymugs]

I'll fix the upgrade issue and update the docs. Is the same happening for ST_SummaryStatsAgg?

[robe]

Yes same happening for ST_SummaryStatsAgg. I do see a DO wrapper for both so assume the version checking is just not working right.

Has this:

IF 202 > version_from_num FROM _postgis_upgrade_info THEN

Why is that 202 and not 220?

[robe]

dustymugs,

you might want to leave it up to strk to fix as I think the issue is with his autogenerated upgrade script and not your code comment. The create type thing is a separate issue, which requires comment, but you have DODO code for that anyway.

[dustymugs]

Has this:

IF 202 > version_from_num FROM _postgis_upgrade_info THEN

Why is that 202 and not 220?

I have no ideas. It doesn't look familiar…

[strk]

202 is how pramsey initially encoded versions. It is major * 100 + minor. So it is correctly set t 202 if it's a newly added aggregate. Rather you should check _postgis_upgrade_info.version_from_num, which is supposedly set at the very beginning of the upgrade procedure by running postgis_scripts_installed()

[robe]

I think we need to differentiate between raster version script and postgis version script no? seems your raster version script is just checking postgis_scripts_installed() which well if I've already upgraded postgis I'm screwed no? Unless of course you change your check to ≥

[strk]

Please try r12290 The script was failing to recognize rtpostgis.sql was part of "postgis_raster" by early exiting the reading loop.

[strk]

I think a better fix would be to take the module name as a parameter, now that we have an UNUSED parameter… we could as well use that one

[robe]

Better — now get this error when installing via extension upgrade:

function st_astwkb_agg(geometry,integer) does not exist

I assume coming from comments install. Looks like nicklas didn't put in an availability note in the CREATE AGGREGATE def

CREATE AGGREGATE st_astwkb_agg(geometry,int) (

and the naming doesn't quite follow our new convention of only one underscore. I'll ticket these as separate issue.

Boy strk we are trapping all our sins with "Let's live by our comments" mantra.

[robe]

closing this out. regular install seems to work (e.g. raster_comments.sql installs cleanly but postgis_comments.sql does not install cleanly because of the st_astwkb_agg issue)

[strk]

Robe did you file that other ticket ? I'm still stuck with updating an extension from '2.2.0dev r12625' to current master: ERROR: function st_countagg(raster, integer, boolean, double precision) does not exist

[strk]

Lack of detail from "CREATE EXTENSION" is pretty frustrating too…

[strk]

Alright I think the problem is that ST_CountAGG(raste,integer,boolean,float8) was added somewhen during development but I've created the extension _before_ its creation. So moving from dev to devnext the AGGREGATE is documented as being already present in the source version (2.2.0dev already) and thus is not re-created. But it's _not_ present, so the upgrade fails to install it.

This is a general problem with dev-to-devnext when it comes to aggregates, or generally to objects that the upgrade procedure now refrain from installing if advertised to be already present in the version.

What we _could_ do is pretending all aggregates have to be dropped and re-created when source and target versions are the same and they end in "dev", or something like that. But I guess there would be still cases in which you'd want to avoid that.

Or we could have a special "next"-like upgrade path for a full reload ?

ALTER EXTENSION postgis UPDATE TO '2.2.0devnextfull' ?

sounds like worth another ticket…