Opened 6 years ago

Closed 6 years ago

#2674 closed defect (fixed)

Another count mismatch postgis 2.0.4 to 2.1.2dev one less operator

Reported by: robe Owned by: strk
Priority: blocker Milestone: PostGIS 2.1.2
Component: build/upgrade/install Version: 2.1.x
Keywords: Cc:

Description

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.

Change History (21)

comment:1 Changed 6 years ago by robe

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

comment:2 Changed 6 years ago by Bborie Park

Status: newassigned

Can do.

comment:3 Changed 6 years ago by robe

Resolution: fixed
Status: assignedclosed

done for 2.2 trunk at r12333, and 2.1 at r12334

comment:4 Changed 6 years ago by robe

oops forgot hash_raster_ops at r12335

comment:5 Changed 6 years ago by robe

Resolution: fixed
Status: closedreopened

comment:6 Changed 6 years ago by robe

Component: rasterbuild/upgrade/install

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.

comment:7 Changed 6 years ago by robe

Owner: changed from Bborie Park to strk
Status: reopenednew

comment:8 Changed 6 years ago by 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.

comment:9 Changed 6 years ago by strk

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

comment:10 Changed 6 years ago by 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

comment:11 Changed 6 years ago by strk

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

comment:12 Changed 6 years ago by 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 ?

comment:13 Changed 6 years ago by 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 ?

comment:14 Changed 6 years ago by 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

comment:15 Changed 6 years ago by 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

comment:16 Changed 6 years ago by strk

Here's the culprit:

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

comment:17 Changed 6 years ago by 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...

comment:18 Changed 6 years ago by 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

comment:19 Changed 6 years ago by strk

Please check with r12349

comment:20 Changed 6 years ago by strk

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

comment:21 Changed 6 years ago by strk

Resolution: fixed
Status: newclosed

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.

Note: See TracTickets for help on using tickets.