[postgis-tickets] [PostGIS] #5194: SELECT postgis_extensions_upgrade() fails with pgextwlist

[PostGIS]

#5194: SELECT postgis_extensions_upgrade() fails with pgextwlist
---------------------+---------------------------
 Reporter:  robe     |      Owner:  pramsey
     Type:  defect   |     Status:  new
 Priority:  blocker  |  Milestone:  PostGIS 3.3.0
Component:  postgis  |    Version:  master
 Keywords:           |
---------------------+---------------------------
 I installed the pgextwlist extension, which is commonly used (or similar
 setup) for PostGIS cloud providers, and I think the change we put in for
 postgis_extensions_upgrade has to be refined or removed.

 When I have pgextwlist extension - https://github.com/dimitri/pgextwlist
 installed and run CREATE EXTENSION as a non-super user (but owner of a
 db).


 {{{
  SELECT postgis_extensions_upgrade();
 }}}


 I get the error:


 {{{
 ERROR:  permission denied for table pg_extension
 CONTEXT:  SQL statement "UPDATE pg_catalog.pg_extension SET extversion =
 'ANY' WHERE extname = 'postgis';ALTER EXTENSION postgis UPDATE TO
 "3.3.0dev""
 PL/pgSQL function postgis_extensions_upgrade() line 90 at EXECUTE
 }}}

 I thought this is only ever run if there is no upgrade path, it shouldn't
 be run for any other situation.

 In this particular case, to test that, I did this:


 {{{
 ALTER EXTENSION postgis UPDATE VERSION "3.3.0devnext";
 }}}

 Now there is a path going from 3.3.0devnext back to 3.3.0dev, so this
 should have not needed to do any ANY update of catalogs

 {{{
 SELECT postgis_extensions_upgrade();
 }}}

 and yet it still errors with:

 {{{
 NOTICE:  Updating extension postgis 3.3.0devnext
 ERROR:  permission denied for table pg_extension
 CONTEXT:  SQL statement "UPDATE pg_catalog.pg_extension SET extversion =
 'ANY' WHERE extname = 'postgis';ALTER EXTENSION postgis UPDATE TO
 "3.3.0dev""
 PL/pgSQL function postgis_extensions_upgrade() line 90 at EXECUTE
 testpgtwlist=> ALTER EXTENSION postgis UPDATE TO "3.3.0dev";

 }}}


 It should recognize there is a path, and only resort to try to do ANY if
 there is no PATH.

 I am able to do this just fine:

 {{{
 ALTER EXTENSION postgis UPDATE TO "3.3.0dev";
 }}}

 If it's just an issue with dev releases, can we still fix it.  It's hard
 to test dev in these kind of environments if it doesn't follow the  "If
 there is a path use it"
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5194>
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.