[postgis-tickets] [PostGIS] #4405: Index over ST_Transform(constant, SRID) breaks pg_upgrade

[PostGIS]

#4405: Index over ST_Transform(constant, SRID) breaks pg_upgrade
-----------------------------------+----------------------------
 Reporter:  Algunenano             |      Owner:  Algunenano
     Type:  defect                 |     Status:  assigned
 Priority:  medium                 |  Milestone:  PostGIS 2.3.10
Component:  build/upgrade/install  |    Version:  2.3.x
 Keywords:                         |
-----------------------------------+----------------------------
 As <subject> says, having an index which uses a ST_Transform(constant,
 XXXX) will break pg_upgrade.

 Test case.

 ## Create the table

 {{{

 # create database test95;

 # \c test95

 # create extension postgis;

 # create table t95 ( the_geom geometry );

 # insert into t95 values ('SRID=4326; POINT(-133 40.2)'::geometry);

 # CREATE INDEX "idx_transf" ON "public"."t95" USING "btree"
 ("st_intersects"("the_geom",
 "st_transform"('0101000020E61000000000000000A060C0FAEDEBC0395B4440'::"geometry",
 3857)));
 }}}


 ## Run pg_upgrade

 I've tested this doing:
 - PG9.5, postgis 2.2 -> PG10, postgis 2.4
 - PG10, postgis 2.4  -> PG11, postgis 2.5

 {{{
 pg_restore: creating CONSTRAINT "public.spatial_ref_sys
 spatial_ref_sys_pkey"
 pg_restore: creating INDEX "public.idx_transf"
 pg_restore: [archiver (db)] Error while PROCESSING TOC:
 pg_restore: [archiver (db)] Error from TOC entry 3342; 1259 17766 INDEX
 idx_transf postgres
 pg_restore: [archiver (db)] could not execute query: ERROR:
 GetProj4StringSPI: Cannot find SRID (4326) in spatial_ref_sys
     Command was:
 -- For binary upgrade, must preserve pg_class oids
 SELECT
 pg_catalog.binary_upgrade_set_next_index_pg_class_oid('17766'::pg_catalog.oid);
 }}}

 Any function that calls ST_Intersects is also affected, e.g.
 ST_Buffer(geography).

 AFAIK, it affects all stable postgis releases, although I'd expect that if
 the destination installation has PROJ 6 it'll do fine as it likely won't
 need the lookup of `spatial_ref_sys`.

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4405>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.