&&& can't be upgraded from 2.0 to 2.1

[robe]

As noted in ticket #2279 since the definition of &&& was changed from 2.0

CREATE OPERATOR &&&(
  PROCEDURE = geometry_overlaps_nd,
  LEFTARG = geometry,
  RIGHTARG = geometry,
  COMMUTATOR = &&&,
  RESTRICT = contsel,
  JOIN = contjoinsel);

to 2.1

CREATE OPERATOR &&&(
  PROCEDURE = geometry_overlaps_nd,
  LEFTARG = geometry,
  RIGHTARG = geometry,
  COMMUTATOR = &&&,
  RESTRICT = gserialized_gist_sel_nd,
  JOIN = gserialized_gist_joinsel_nd);

I presume this means 3D geometry users using &&& will not be able to benefit from the fruits of pramsey's labor.

The problem is you can't drop the operator without dropping the

gist_geometry_ops_nd class

and if you drop the opclass you have to drop the 3D indexes.

The good news is not that many people are using that index and I suspect even know about it so probably doesn't affect that many people.

Options that come to mind:

1) drop if we see in use and also try to drop the operator class and rebuild.  users who have indexes will get an error during upgrade, but new users who have not started using this feature will get a clean upgrade

2) Do nothing except document the issue, tell people they need to dump /restore if they are or plan to use 3D and let them fend for themselves

3) Provide a script that drops and rebuilds the indexes (would have to itemize)

2 is the easiest for us, meanest for users, but at least we warned if they new where to look in the docs. 3) sounds complicated

1) is a good balance that is not too hard and yet is nice to future users.

Given we don't have too many people using 3D yet, I'm leaning toward 1.

[strk]

An issue in upgrading from 2.0 to 2.1 makes no sense to have milestone 2.2. Was the issue fixed in any 2.1.x release ? Latest was 2.0.7, when was operator &&& changed exactly ? Is there any way to have run_test catch the partial upgrade as an error ?

[strk]

robe, did you mention in IRC that you had a way to fix these cases ? Was this specific one fixed already ?

[strk]

Regina could you please check if this bug is still present ? Also, as the subject is about 2.0 to 2.1 upgrade, shoudn't the milestone be in the 2.1 branch ?

[strk]

in any case, this belongs to Paul, I'd say.

[strk]

HINT: we'll need a testcase showing the problem

[strk]

The update call, which shoudl always work (already-updated or not), and assuming there are no schema conflicts:

update pg_operator set oprrest = 'gserialized_gist_sel_nd', oprjoin = 'gserialized_gist_joinsel_nd' where oprname = '&&&' and oprleft = oprright and oprright = ( select oid from pg_type where typname = 'geometry' );

[pramsey]

So a more precise wording of this would be that it's not upgradeable if nd indexes exist in the database to be upgraded? I will try to confirm. Upgrade certainly works fine on a blank database.

[pramsey]

Unless someone can provide a SQL script that demonstrates the issue, I'm closing this. I just did an upgrade w/ no problem, on a database with an existing ND index, so…?