[postgis-tickets] [PostGIS] #3956: Upgrade does not upgrade geog_brin_inclusion_add_value function to use the updated module

[PostGIS]

#3956: Upgrade does not upgrade geog_brin_inclusion_add_value function to use the
updated module
----------------------+---------------------------
 Reporter:  keiko713  |      Owner:  pramsey
     Type:  defect    |     Status:  new
 Priority:  medium    |  Milestone:  PostGIS 2.4.3
Component:  postgis   |    Version:  2.4.x
 Keywords:            |
----------------------+---------------------------
 Hello,

 I'm seeing the issue that geog_brin_inclusion_add_value function is not
 upgraded to use the updated module under the certain condition.
 Particularly, it has Postgis 2.3.2, and run {{{ALTER EXTENSION postgis
 UPDATE}}} (with the instance that has 2.4.2 in it), it upgrade all the
 functions to use {{{$libdir/postgis-2.4}}} **except**
 geog_brin_inclusion_add_value.

 For example, this is something I can see (check probin of
 geog_brin_inclusion_add_value):

 {{{
 > select proname, proowner, prosrc, probin from pg_proc where probin like
 '%postgis%';
                   proname                  | proowner |
 prosrc                   |        probin
 -------------------------------------------+----------+-------------------------------------------+-----------------------
  st_flipcoordinates                        |       10 | ST_FlipCoordinates
 | $libdir/postgis-2.4
  geog_brin_inclusion_add_value             |       10 |
 geog_brin_inclusion_add_value             | $libdir/postgis-2.3
  box2df_in                                 |       10 | box2df_in
 | $libdir/postgis-2.4
  box2df_out                                |       10 | box2df_out
 | $libdir/postgis-2.4
 ...
 }}}


 Looking at the script, there is {{{CREATE OR REPLACE FUNCTION}}} for
 geog_brin_inclusion_add_value using the correct {{{MODULE_PATHNAME}}}
 ({{{$libdir/postgis-2.4}}} in this case), but this part of code is
 underneath of the {{{IF}}} statement and if it doesn't go inside of that
 {{{IF}}} statement, it won't run such thing, therefore upgrade itself
 finishes without going through {{{CREATE OR REPLACE FUNCTION}}} for
 geog_brin_inclusion_add_value.

 https://github.com/postgis/postgis/blob/2.3.2/postgis/geography_brin.sql.in#L73-L76

 Regardless the missing operator, {{{CREATE OR REPLACE FUNCTION}}} should
 happen with geog_brin_inclusion_add_value so that it can be loaded with
 the upgraded version's module.

 This is causing the problem with Postgres that went through Postgis
 upgrade to 2.4, and Postgres version upgrade from Postgres 9.6 to Postgres
 10 -- since Postgres 10 still tries to load {{{$libdir/postgis-2.3}}} due
 to geog_brin_inclusion_add_value but it's unable to because the machine
 doesn't have postgis-2.3 with Postgres 10.

 As a workaround, I was able to finish the Postgres version upgrade from
 9.6 to 10 by manually running following, but would be really nice if it's
 fixed within Postgis itself (also I'm afraid if I'm missing something else
 inside of that {{{IF}}} statement, I'm not really familiar with Postgis).

 {{{
 CREATE OR REPLACE FUNCTION geog_brin_inclusion_add_value(internal,
 internal, internal, internal) RETURNS boolean
         AS '$libdir/postgis-2.4','geog_brin_inclusion_add_value'
         LANGUAGE 'c';
 }}}

 Please let me know if there is anything I can add to this report. Thanks!!

 Keiko

 Similar issue in StackOverflow:
 https://stackoverflow.com/questions/43226275/can-not-find-postgis-
 extensions-installed-in-a-different-directory-and-libd

--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/3956>
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.