Another count mismatch postgis 2.0.4 to 2.1.2dev one less operator

[robe]

If I do a clean install:

 CREATE EXTENSION postgis
  SCHEMA public
  VERSION "2.1.2dev";

I have 45 operators

If I do an upgrade to 2.1.2dev from 2.0.4, I only have 44 operators.

Also seem to be missing operator classes and operator families.

The difference is that the following don't get installed.

operator =(raster,raster)
operator class hash_raster_ops for access method hash
operator family hash_raster_ops for access method hash

I know the = operator was introduced in 2.1 and it's probably missing Availability info.

[robe]

dustymugs can you confirm that these are all new in 2.1.0 and put the corresponding availability note in rtpostgis.sql.in.

[Bborie Park]

Can do.

[robe]

done for 2.2 trunk at r12333, and 2.1 at r12334

[robe]

oops forgot hash_raster_ops at r12335

[robe]

Bah putting the availability in wasn't sufficient to get this to install the new ops. I confirmed it's not just an extension issue (same happens with old-fashion script way)

strk — help.

[robe]

Actually I see in the postgis 2.2 (trunk) there is logic to create the operators. So this might be just a longer standing issue with the older upgrade routine (2.1 is not quite as advanced as 2.2. script) and I think in the past we never created new operators

Unfortunately 2.2 has the issue with aggregate that its trying to drop _st_mapalgebra4unionfinal1(raster) so have to fix that one to drop from extension first before I can verify works fine in 2.2.

[strk]

Can you please give me the exact steps to take to reproduce the problem ?

[robe]

CREATE EXTENSION postgis VERSION "2.0.4";
ALTER EXTENSION postgis  UPDATE TO "2.1.2dev";

I also tried installing with the scripts and upgrading with the scripts, though its more likely I missed a step there.

I'm puzzled why I'm not getting the _st_mapalgebra4unionfinal1 drop error when upgrading to 2.1 (but get it going to 2.2) though since that was dropped in 2.1 and it looks like include script (sql_bits/remove_from_extension.sql.in) to remove functions from extensions has been removed in both the 2.1 and trunk branches

[strk]

I'm installing 2.0.4 on PostgreSQL-9.3.1 to try this (I hope it's supported!)

[strk]

So 9.3 wasn't supported byt I installed in 9.1 and run_test.pl failed to find a problem:

[strk@cdb:/usr/src/postgis/postgis/regress(svn-trunk)] ./run_test.pl --extension --upgrade-path 2.0.4--2.1.2dev -v /usr/src//postgis/postgis-2.1/regress/tickets.sql
Upgrade path: 2.0.4 --> 2.1.2dev
PATH is /home/strk/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games
Checking for shp2pgsql ... found
Checking for pgsql2shp ... found
TMPDIR is /tmp/pgis_reg
Creating database 'postgis_reg' 
Preparing db 'postgis_reg' using: CREATE EXTENSION postgis VERSION '2.0.4'
Upgrading PostGIS in 'postgis_reg' using: ALTER EXTENSION postgis UPDATE TO '2.1.2dev'
PostgreSQL 9.1.12 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.8.1-10ubuntu8) 4.8.1, 64-bit
  Postgis 2.0.4 - r12336 - 2014-02-23 15:21:31
  scripts 2.1.2dev r12336
  GEOS: 3.4.3dev-CAPI-1.8.3 r3958
  PROJ: Rel. 4.7.1, 23 September 2009

Running tests

 /usr/src//postgis/postgis-2.1/regress/tickets .. ok 
 uninstall .. ok (3857)

Run tests: 2
Failed: 0

The "uninstall" test checks that the number of objects in the database _before_ the initial install (2.0.4) is the same as the number of database objets _after_ the disinstall. How can we make run_test.pl catch the error you're talking about ? What should it check ?

[strk]

Done "manually":

pgtest=# CREATE EXTENSION postgis VERSION "2.0.4";
CREATE EXTENSION
pgtest=# ALTER EXTENSION postgis  UPDATE TO "2.1.2dev";
ALTER EXTENSION

How do I see the error ?

[strk]

Ok, a way to reproduce:

select count(*) from pg_operator where oprleft::int > 5000;

Does give 44 on update and 45 on new install, looking at how I can add support for this check in run_test.pl

[strk]

After thinking about this. It is clear that the correct way to test this would be for that missing operator to be tested in one of the regression tests.

Dustymugs: do you know of an existing regress test testing operators ? I'm trying with runnign them all but getting lots of failures:

./run_test.pl --raster --extension --upgrade-path 2.0.4--2.1.2dev -v /usr/src//postgis/postgis-2.1/raster/test/regress/*.sql

[strk]

Here's the culprit:

pgtest=# select * from operators_212_new except select * from operators_212_upd ;
 oprname |  left  | right
---------+--------+--------
 =       | raster | raster
(1 row)

[strk]

Figured: I hadn't back-ported the postgis_upgrade.pl patch that reads the availability directly from the .sql file, so the new operators had to be explicitly listed in postgis_proc_upgrade.pl itself.

Should be fixed as of r12341

Trunk should be already working fine by reading from SQL, but I haven't double-checked.

Testing this in a regression won't be easy in that lack of RASTER=RASTER would probably result in automatic cast to geometry and use of GEOMETRY=GEOMETRY instead…

[pramsey]

Debbie and I are both seeing the raster upgrade online regression test fail

TMPDIR is /tmp/pgis_reg
Creating database 'postgis_reg' 
Loading PostGIS into 'postgis_reg' 
Loading Raster into 'postgis_reg'
Upgrading PostGIS in 'postgis_reg' 
Upgrading core
Upgrading raster

Error encountered loading /Users/pramsey/Code/postgis-2.1-svn/regress/00-regress-install/share/contrib/postgis/rtpostgis_upgrade_21_minor.sql, see /tmp/pgis_reg/regress_log for details

and the log shows

psql:/Users/pramsey/Code/postgis-2.1-svn/regress/00-regress-install/share/contrib/postgis/rtpostgis_upgrade_21_minor.sql:5402: ERROR:  operator = already exists

[strk]

Please check with r12349

[strk]

robe: this would also need to re-test the upgrade for counts

[strk]

I tried this myself. Count of both operators and opclasses is the same when installing 2.1.2dev from scratch or via upgrade from 2.0.4. Closing.